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.

See here http://www.oracle-base.com/articles/10g/automatic-workload-repository-10g.php
for detailed explanation.  

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 .......

Queries for DD tables in Oracle

Find the queries for various DD tables in the document https://docs.google.com/open?id=180eCMrfSn-SkDRA-eXEAFIQCi85wdiugzlt9jzwZSjqoCLHyVA_HQnqnqzTa

What is DENSE_RANK analytic function? When it can be used?

Let's assume we want to assign a sequential order, or rank, to the payments made by customers based on paid date, we might use the DENSE_RANK function like:


with t
 as
 (
 select 1 id, 'A' name, to_date('05-Sep-11','dd-mon-rr') dt, 10 paid from dual union all
 select 1, 'A', to_date('06-Sep-11','dd-mon-rr'), 12 from dual union all
 select 1, 'A', to_date('07-Sep-11','dd-mon-rr'), 15 from dual union all
 select 1, 'A', to_date('07-Sep-11','dd-mon-rr'), 13 from dual union all
 select 2, 'B', to_date('06-Sep-11','dd-mon-rr'), 13 from dual union all
 select 2, 'B', to_date('07-Sep-11','dd-mon-rr'), 16 from dual union all
 select 2, 'B', to_date('08-Sep-11','dd-mon-rr'), 05  from dual union all
 select 3, 'C', to_date('08-Sep-11','dd-mon-rr'), 12 from dual union all
 select 3, 'C', to_date('09-Sep-11','dd-mon-rr'), 11 from dual
 )
 select id, name, max(paid) keep(dense_rank last order by dt)  last_paid
   from t
   group by id,name


ID     NAME LAST_PAID                   
-------- ---- ---------------------- 
1       A      15                     
2       B      5                      
3       C      11

Dense_Rank should be used along with FIRST or LAST functions. The KEEP keyword is for semantic clarity. It qualifies aggregate_function, indicating that only the FIRST or LAST values of aggregate_function will be returned.

Logic:
  1) Rows are grouped by ID and Name.
  2) Sorts the rows within the group. 
  3) Dense_Rank ranks the rows within the group with respect to sorted
order,say it ranks first row as "1". When there is two same dates within
the same group then Dense_Rank ranks them with the same number.
  4) Then it returns the maximum paid from the group.When there is no two rows with the same ranks within the group max/min works similar. When there is two rows with same rank then max/min fetches the corresponding value.
      
Other example: Select salary which is earned by the most employees+how often
                
with testdata as (
select 1000 as salary from dual
union all
select 1000 as salary from dual
union all
select 1000 as salary from dual
union all
select 20000 as salary from dual
union all
select 2000 as salary from dual
union all
select 10 as salary from dual
)


CNT                    SALARY                 
---------------------- ---------------------- 
3                      1000                   

Query 1

select max(count_emps) as Cnt, max(salary) keep (dense_rank first order by count_emps desc) as Salary
from (SELECT salary, COUNT(*) as count_emps
        FROM testdata
       group by salary )

Query 2

select max(COUNT(*)) cnt, max(salary) keep (dense_rank first order by COUNT(*) desc) salary
FROM testdata
group by salary;