Oracle - Execution Plans , Explain Plans, AWR Reports, Tuning - A handy Guide

There is an inherent different between explain plans and execution plans , and I almost got confused that they were the same.

In order to capture an execution plan , we need to follow the steps:

The user who will be running the sql for which you want the execution plan , should have permissions to alter session in order to enable trace.(This is not mandatory , if you can run the sqls via the user who has permissions then that is also fine)

Grant permission:

grant alter session to ;
revoke alter session from ;

Enable SQL TRACING

To enable Trace run the following DCL:

ALTER SESSION SET sql_trace = true;

To specify trace file identifier which helps to locate specific trace files easily

ALTER SESSION SETtracefile_identifier =tracing_example;

To specify the trace destination other than the oracle default , we could perform below


ALTER SESSION SET user_dump_dest=

Default vaues for the various parameters above can also be found using TOAD->Database->Administer->Parameters

Explain Plan

Using toad , this can be achieved by following the steps below:

1.  Connect to the Oracle SID
2. Open a SQL editor, and write the SQL query for which the explain plan is required.
3. CTRL+E will produce the explain plan for the query - which basically means , this is the most likely path oracle will chose while executing the SQL. 
4. Analyze the cost of the query , and identify the areas which are causing the cost to grow high.
5. Mostly this happens when full table access is performed, or hashed joins are used , instead of full index scans and nested loops.
6. This is the fastest way to identify if a query you have written has some tuning gaps and can be rewritten to perform better in distributed and scalable high volume environments.

Enable SNAPSHOTS

By enabling snapshots we will be able to capture the Oracle activity between regular periods identified by snapshot ids. To view the current snapshot interval we can run the following:

select * from DBA_HIST_WR_CONTROL

To enable snapshots for every 15 minutes , we can run the following procedure. We can change the snapshot interval based on approximately how much time would a process(which we are interested in profiling / tuning ) takes to run .

BEGIN dbms_workload_repository.modify_snapshot_settings(interval => 15, retention => null); END;

Generate AWR Report

An AWR report captures all Oracle activity between given snapshots. This can be helpful to monitor the queries which are taking longer than expected and also identify tuning paramters.

To generate an AWR report we can perform the following steps:

1. Invoke @awrrpt.sql from ..\oracle\product\\dbhome_1\RDBMS\ADMIN
2. Choose report type as html
3. Provide number of days you want the report to span for snapshot ids
4. Provide the snapshots which we want to capture in our report. 
5. Provide a valid report name.
6. AWR report is generated in the ..\oracle\product\\dbhome_1\RDBMS\ADMIN folder with the name provided.

Many of the above options can be chosen as default as well.

Capturing SQL ID

If we do not have a AWR report , but still want to identify the the SQL id based on the query content the following can be used:

SELECT sql_id, child_number, substr(sql_text, 1, 300) sqltxt from v$sql WHERE sql_text LIKE %%' ;

Also the various execution plans used by oracle for this SQL can be generated using:

set linesize 150

set pagesize 2000

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('SQL_ID'));

Execution Plan

Execution plan on the other hand is the actual path /plan used by oracle to execute a query.

To extract an execution plan, you will have to perform a few more steps as below:

1. Enable snapshots for the SID which you will be executing the queries from.Set the snapshot interval based on your specific enviroment , sql process running
2. Execute the SQL procedure, process or DBMS transaction which you want to tune.
3. Generate AWR report for the snapsots we are interested in.
4. Browse the sql statistics to identify which queries are taking up the most time , in terms of execution , io waits , cpu etc
5. Each SQL will have a unique SQL_ID , capture that.
6. Invoke @awrsqrpt.sql from ..\oracle\product\\dbhome_1\RDBMS\ADMIN
7. Provide the sql id for which we want to generate the execution plan
8. Provide snapshot ids
9. Provide a name for the plan report
10. Execution plan is generated in the ..\oracle\product\\dbhome_1\RDBMS\ADMIN folder with the name provided.

This plan will provide the exact path , and can be used to identify whats causing the costs for SQL runtime to go high.

Tuning Options:

• Disable tracing
• Disable all SQL profilers
• Perform Database connections monitoring - if connections are being returned once transactions complete
• reduce redo logs
• add hints wherever costs of queries are more due to joins and full table scans
• add appropriate indexes 
• Check Init.ORA for Oracle settings ,and modify based on the needs
• The list if huge .......