Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Location
    India
    Posts
    3

    Question Unanswered: Query Optimizing

    Hi,
    I have a huge procedure that takes almost an hour to run. I need to optimize the complex queries used in it. How should I go about?

  2. #2
    Join Date
    Jan 2004
    Posts
    99
    first place to start is by running a SQL trace on the job, then use TKPROF to format the output!! then you can start looking at the code, indexes etc.

  3. #3
    Join Date
    Apr 2004
    Location
    India
    Posts
    3
    Hi,
    I dont get you. Can you be little more explanatory please.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

  5. #5
    Join Date
    Apr 2004
    Posts
    27
    Originally posted by sivaraman_g
    Hi,
    I dont get you. Can you be little more explanatory please.
    write
    sql trace on
    then run ur query
    after the sql query completes its execution, use TKPROF utility tool to analyse the output and then tune accordingly.

  6. #6
    Join Date
    Oct 2003
    Posts
    6

    Re: Query Optimizing

    Originally posted by sivaraman_g
    Hi,
    I have a huge procedure that takes almost an hour to run. I need to optimize the complex queries used in it. How should I go about?

    Steps for optimize the query.

    1. On Trace file for user
    2. Check the explain plan
    3. Check if the table scan full then create index.

  7. #7
    Join Date
    Apr 2004
    Location
    India
    Posts
    3

    Thumbs up

    This may help me. Thanks.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    once you tkprof your trace file attach that file here so we can take a look.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •