Menu Search Sign up

Oracle SQL Query Tuning

Identifying High-Load SQL

High-load SQL are poorly-performing, resource-intensive SQL statements that impact the performance of the Oracle database. High-load SQL statements can be identified by:

  1. Automatic Database Diagnostic Monitor
  2. Automatic Workload Repository
  3. V$SQL view
  4. Custom Workload
  5. SQL Trace

V$SQL view https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2113.htm#REFRN30246 

select * from V$SQL and then look for queries with high cpu_time or elapsed_time

EXPLAIN PLAN

https://blogs.oracle.com/optimizer/entry/displaying_and_reading_the_execution_plans_for_a_sql_statement 

Example 1 Uses the EXPLAIN PLAN command and the dbms_xplan.display function.

SQL> EXPLAIN PLAN FOR
2 select prod_category, avg(amount_sold)
3 from sales s, products p
4 where p.prod_id = s.prod_id
5 group by prod_category;

Explained. 

SQL> select plan_table_output
2 from table(dbms_xplan.display('plan_table',null,'basic'));

------------------------------------------
Id Operation Name
------------------------------------------
0 SELECT STATEMENT
1 HASH GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCTS
4 PARTITION RANGE ALL
5 TABLE ACCESS FULL SALES
------------------------------------------

Example 2:

1. Prefix your SQL statement with "explain plan for", like below:

EXPLAIN PLAN FOR
SELECT audit_src_id, count(*)
FROM audit_src
GROUP BY audit_src_id;

2. Select from the "plan_table", where the SQL execution plan data will be stored:

select * from plan_table;

3. View the data in plan_table to spot places for optimization.

4. After you are done, delete the execution plan data from plan_table:

delete from plan_table;
commit;

SQL Tuning Advisor

https://docs.oracle.com/cd/B19306_01/server.102/b14211/sql_tune.htm#i34782 

The recommended interface for running the SQL Tuning Advisor is the Oracle Enterprise Manager. If Oracle Enterprise Manager is unavailable, you can run the SQL Tuning Advisor using procedures in the DBMS_SQLTUNE package. To use the APIs, the user must be granted specific privileges.

Running SQL Tuning Advisor using DBMS_SQLTUNE package is a multi-step process:

  1. Create a SQL Tuning Set (if tuning multiple SQL statements)
  2. Create a SQL tuning task
  3. Execute a SQL tuning task
  4. Display the results of a SQL tuning task
  5. Implement recommendations as appropriate