October 1st, 2012, 09:21 AM
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
- - 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?
October 1st, 2012, 09:33 AM
Originally Posted by dunkindecaf
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
Tips on how to ask better questions:
October 3rd, 2012, 05:20 AM