Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45

    Unanswered: optimaze 38 queries for better performance

    hello,
    how can I optimaze my queries for better performance.
    Is there a scrip that I can run ? I have 38 reports that build up by using the queris. Sometimes theya re running so slow. Is there anything that I can do to improve it???

    Thank you.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    enable SQL_TRACE and run the results through TKPROF to see where the time is being spent.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    There is so much you can do to improve your queries, from analyzing the tables involved in the queries.. to using sql hints. Would help if you follow what anacedent suggested you as well.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try:
    Code:
    SET AUTOTRACE ON
    And look at the explain plan and statistics.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    You have to go query by query and look at the explain plan for each one. If you are using CBO - make sure you have the recent statistics for the tables involved in the query.

    Once you have the explian plans - look for full table scans and high cost. Check if you have the required indexes for the columns used in the predicares.

    If you don't want to do the manual work - get yourself a OEM and run the SQL Analyzer - it will get you the index recommendations and some tuning tips.


    Hope that helps,

    clio_usa - OCP DBA 8/8i/9i

  6. #6
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45
    Thank you, I did the explain and I create few indexes, so some of the queries are running much faster, working on others.

Posting Permissions

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