#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0

    Oracle Query Optimization - steps, tools, and resources?


    1. For tuning SQL queries, is the following the general approach:
    - Determine which SQL queries are the most active (V$SQLAREA and V$SQL, stats$sql_summary, dba_hist_sql_summary table)
    - For the most active:
    - - Add statistics to the tables to help with the CBO (Cost-Based Optimizer)
    - - Determine EXECUTION PLAN using Oracle’s EXPLAIN utility
    - - Interpret the EXECUTION PLAN and use rules-of-thumb based on the information in the PLAN to determine what you need to do (e.g. a) re-ordering tables in join, enabling indexes to eliminate the need for full table scans , b) reducing the number of sorts, merges, and filtering operations required, c) reducing the number of rows that need to be sorted, filtered, or merged, d) add SQL hints to modify the plan)
    - - Re-run the EXECUTION PLAN and see if it’s better
    - Other:
    - - Re-write SQL with Global Temporary Tables
    - - Put the SQL into PL/SQL
    2. What are the differences are in the following?
    - SQL Tuning Wizard (guides you through the whole tuning process, uses Oracle SQL Analyze)
    - Oracle Tuning Pack: Oracle Expert (replaces SQL Tuning Wizard?), Oracle Query Tuning (replaces Oracle SQL Analyze?), Oracle Index Tuning Wizard
    - Oracle 11g SQL Performance Analyzer (SPA
    3. How do we access the above?
    4. Does anyone have any recommendations for best documentation (books, online sites) for oracle query tuning, including EXPLAIN PLAN interpretation?

    Thanks --
    Bill
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by dunkindecaf
    4. Does anyone have any recommendations for best documentation (books, online sites) for oracle query tuning, including EXPLAIN PLAN interpretation?l
    The manual?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. Humble Learner
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Location
    Bangalore, India
    Posts
    280
    Rep Power
    120
    check this.

    http://books.google.co.in/books?id=lkD7vgygSJEC&printsec=frontcover#v=onepage&q&f=false

IMN logo majestic logo threadwatch logo seochat tools logo