DBMS_XPLAN.DISPLAY_CURSOR

DBMS_XPLAN.DISPLAY_CURSOR show actual execution plan from dynamic performance views.

DBMS_XPLAN:  The DBMS_XPLAN package provides an easy way to display the output of
the EXPLAIN PLAN command in several, predefined formats.You can also use the DBMS_XPLAN
package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or
stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution run time statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and SQL_PLAN_STATISTICS_ALL fixed views.

DISPLAY_CURSOR: To format and display the contents of the execution plan of any loaded cursor.

Parameter for DBMS_XPLAN.DISPLAY_CURSOR:
SQL_ID: sql_id we will get from V$SQL OR V$SQLAREA. Default this value as NULL.

STEPS:
1. Execute the query

select e.ename , e.sal, d.dname/*sql_execution2*/ 
 from emp e ,dept d where d.deptno = e.deptno; 

2. Then search SQL ID for the query that is executed

select sql_id,sql_text from v$sqlarea where sql_text like '%sql_execution2%';

OUTPUT:
SQL_ID                 SQL_TEXT
-----------------       ------------------------------------------------------------------------------------------

bt7bfjp0525vu      select e.ename , e.sal, d.dname/*sql_execution2*/ from emp e ,dept d where                       
3. Chose the correct SQL_ID write to your query, then execute the below query to get actual plan

select * from table(dbms_xplan.display_cursor('bt7bfjp0525vu'));

DBMS_XPLAN

One more way we can see the Explain Plan by using DBMS_XPLAN package.

• DBMS_XPLAN automatically queries the last plan in PLAN_TABLE.
• It uses a TABLE() function with another pipelined function.

Example of DBMS_XPLAN:

select * from table(dbms_xplan.display);    

PLAN_TABLE Detils

In PLAN TABLE some of the important columns descriptions are as follows :

 

 

what to look for to help performance

Identify the solution for what problem by looking the TKPROF.

If parsing numbers are high: The SHARED_POOL_SIZE may need to be increased.

If Disk reads are very high: Indexes are not being used or may not exist

If the QUERY or CURRENT memory reads are very high: Indexes may be on columns with low cardinality (columns where an individual value generally makes up a large percentage of the table; like a y/n field). Removing/suppressing the index or using histograms or a bitmap index may increase performance. A poor join order of tables or bad order in a concatenated index may also cause this.

If parse elapse time is high: There may be a problem with the number of open cursors.

If number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This could be a sign of an index with a poor distribution of distinct keys (unique values for a column). This could also be a sign of a poorly written statement.

If the number of rows processed by a row in the EXPLAIN PLAN is high compared to the other rows: This indicates that the statement had to be reloaded. You may need to increase the SHARED_POOL_SIZE in the init.ora file or do a better job of sharing SQL.

Details Of TRACE and TKPROF Output

SQL TRACE has multiple sections including SQL Statements, Statistics, information and EXPLAIN PLAN.

 

1. SQL Statements: The first part of a TKPROF statement is the SQL  Statement . This statement will be exactly the same as the statement that was executed. If there were any hints or comments in the statement, they would be retained in this output.

2. Statistics:  It has all the Statistics for this SQL Statements. It has eight columns 

• call Statistics for each cursor's activity are divided in to 3 areas: Parse,Execute and Fetch. A total is also calculated.

      Parse: statistics from parsing the cursor. This includes information for plan generation etc.

Execute: statistics for the execution phase of a cursor

Fetch: statistics for actually fetching the rows

• Count number of times each individual activity has been performed on this particular CALL.
• CPU time used by this CALL.
• ELAPSED time for this CALL(includes the CPU time).
• DISK this indicates the number of blocks read from disk. Generally it would be preferable for blocks to be read from the buffer cache rather than disk.
• QUERY the total number of data buffers retrieved from memory for this type of call. SELECT statements usually retrieve buffers in this mode. This is the number of consistent gets.
• CURRENT the total number of data buffers retrieved from memory for this type of call. UPDATE, INSERT, or DELETE usually access buffers in this mode, although SELECT statements may use a small number of buffers in this mode also. This is the number of db block gets.
• ROWS the total number of rows processed by this statement. The rows processed for SELECT statements will appear in the row of Fetch statistics. Inserts, updates, and deletes will appear in the Execute row.

3. Information: It contains information about the number of library cache misses from parse and execute calls. If the number of misses is high, there may be a problem with the size of the shared pool. It also contains the username of  the last user to parse this statement.   

4. EXPLAIN PLAN : This most useful section of  the TKPROF. The first column of this section is the number of rows processed by each line of the execution plan. We can identify how slow a statement is. If the total number of rows in the fetch statistics is low compared to the number of rows being processed by each line of the EXPLAIN PLAN.

Oracle SQL TRACE & TKPROF utility

Oracle SQL TRACE utility to measure timing statistics for a given query.
SQL TRACE records many vital information regarding actual query execution into a trace file.
SQL TRACE helps developers analyze every section of a query.
It writes query execution statistics like number of logical I/O, physical I/O, the  CPU and elapsed timings, number of rows processed, query plans with row counts at each levels,information in wait events etc.
Its  bit difficult  to read the trace file .Using TKPROF we can generate readable report from trace file.

Steps for SQL TRACE 

---

1. We need to set the below parameters.

ALTER SESSION SET TIMED_STATISTICS=TRUE;

This enables and disables the collection of timed statistics , such as CPU  and elapsed time etc. The value  can be TRUE or FALSE.

Below is the default destination of trace file.

USER_DUMP_DEST = \oracle\product\10.2.0\admin\tsm\udump

2. Enable the SQL TRACE for a session.

ALTER SESSION SET SQL_TRACE=TRUE;

3. Run the query(run your SQL).

SELECT ENAME,EMPNO,DEPTNO,SAL FROM EMP WHERE EMPNO=10;

4. Disable the SQL TRACE .

ALTER SESSION SET SQL_TRACE=FALSE;

Steps for TKPROF

---

Once your trace file is ready then we need to run the TKPROF at the command line to generate the report.TKPROF accepts input as a trace file and  it produces a formatted output file(report).

Syntax:

tkprof tracefile output_file [sort = parameters] [print=number]
[explain=username/password] [waits=yes|no] [aggregate=yes|no] [insert=filename]
[sys=yes|no] [table=schema.table] [record=filename]

Details of important parameters:
Tracefile: This is the name of the SQL TRACE file containing the statistics by SQL_TRACE.
Output_file: This is the name of the file where TKPROF writes its output.
sort = parameters: A multiple number of sorting options are available.

• FCHCPU (CPU time of fetch); 
• FCHDSK (disk reads for fetch); 
• FCHCU and FCHQRY (memory reads forfetch); 
• FCHROW (number of rows fetched); 
• EXEDSK (disk reads during execute); 
• EXECU and EXEQRY (memory reads during execute); 
• EXEROW (rows processed during execute); 
• EXECPU (execute CPU time); PRSCPU (parse CPU); and 
• PRSCNT (times parsed).

print = number: This is the number of statements to include in the output. If this statement is not included, TKPROF will list all statements in the output.

Explain = username/password: Run the EXPLAIN PLAN on the user’s SQL statements in the TRACE file. This option creates a plan_table of its own,so the user will need to have privileges to create the table and space in which to create it. When TKPROF is finished,this table is dropped. Ensure that you use the username/password of the user that parsed the cursor (ran the query) to ensure the explain is by the correct user.

waits=yes/no: Record summary for any wait events.

aggregate=yes|no: If no, then tkprof does not combine multiple users of the same SQL text.

insert=filename: This option creates a script to create a table and store the TRACE file statistics for each SQL statement traced.

table=schema.table: The table in which tkprof temporarily put execution plans before writing them to the output file.

Once your trace file is ready then we need to run the below command:

5. Go by command line to (cmd) .

oracle\product\10.2.0\admin\oracle\udump

find your trace file with your SPID

6. Run TKPROF command .

 TKPROF <trace_file> <output_report.prf>

EXPLAIN PLAN

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer  to execute a SQL statement.
EXPLAIN PLAN takes less than a minute to EXPLAIN a query that takes four hours to run because it does not actually execute the SQL statement, it only outlines the plan to use and inserts this execution plan in an Oracle table (PLAN_TABLE).

Why we will use EXPLAIN PLAN without TRACE?
The statement is not executed; it only shows what will happen if the statement is executed. 
When do you use EXPLAIN without TRACE?
When the query will take exceptionally long to run.

How to use EXPLAIN PLAN?

1. Create PLAN TABLE: Execute the script "utlxplan.sql". File location is below

oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql    

2. EXPLAIN Query: Run the EXPLAIN PLAN for the query to be optimized 

EXPLAIN PLAN FOR
select ename,sal,empno,deptno
from emp
where deptno=10;    

Using Tag:

EXPLAIN  PLAN FOR

SET STATEMENT_ID='SQL1'

select ename,sal,empno,deptno
from emp
where deptno=10;

3. PLAN Table is populated: Select the output from PLAN TABLE

select operation, options, object_name, id, parent_id
from plan_table
where statement_id = 'SQL1'   

OR we can use below query to see the output in proper format 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);    

It shows the following information: 

• The row source tree is the core of the execution plan. 
• An ordering of the tables referenced by the statement
• An access method for each table mentioned in the statement
• A join method for tables affected by join operations in the statement
• Data operations like filter, sort, or aggregation.

In addition to the row source tree, the plan table contains information about the following: 

• Optimization, such as the cost and cardinality of each operation
• Partitioning, such as the set of accessed partitions
• Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query. 

Query processing can be divided into 7 phases :

• Syntactic          : Checks the syntax of the query
• Semantic          : Checks that all objects exist and are accessible
• View Merging  : Rewrites query as join on base tables as opposed to using views
• Statement Transformation : Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
• Optimization  : Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path.  With the Cost Based  Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
• QEP Generation    : QEP = Query Evaluation Plan.
• QEP Execution      : QEP = Query Evaluation Plan.

In Toad how we will work on Explain plan :

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

• Connect to the Oracle SID
• Open a SQL editor, and write the SQL query for which the explain plan is required.
• 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. 
• Analyze the cost of the query , and identify the areas which are causing the cost to grow high.Mostly this happens when full table access is performed, or hashed joins are used , instead of full index scans and nested loops.
• 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.

SQL Tuning Advisor

The SQL Tuning Advisor takes one or more SQL statements as input to perform SQL tuning. Use of SQL Tuning Advisor

• Automatic Tuning Optimizer using  the SQL Tuning Advisor.
• Reactive Tuning Using the  SQL Tuning Advisor.

 Automatic Tuning Optimizer using  SQL Tuning Advisor: 

Oracle Database automatically runs the SQL Tuning Advisor on a selected high loaded SQL statements which is identified from AWR.

Reactive Tuning Using the  SQL Tuning Advisor:
We can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic. We run the SQL Tuning Advisor against the SQL statements or SQL_ID to improve the query performance.

Steps to SQL Tuning Advisor against SQL_ID:
I have one SQL_ID='8tf5zwqu4z40s'

 1. Create Tuning Task:

DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => '8tf5zwqu4z40s',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 500,
                          task_name   => '8tf5zwqu4z40s_tuning_task11',
                          description => 'Tuning task1 for statement 8tf5zwqu4z40s');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

 2. Execute Tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '8tf5zwqu4z40s_tuning_task11');
/

 3. Get the Tuning advisor report:

select dbms_sqltune.report_tuning_task('8tf5zwqu4z40s_tuning_task11') from dual;
/

 4. Get list of tuning task present in database:

EXECUTE dbms_sqltune.drop_tuning_task('8tf5zwqu4z40s_tuning_task11');
/

 5. Drop a tuning task:

SELECT TASK_NAME, STATUS
FROM DBA_ADVISOR_LOG
WHERE TASK_NAME= '8tf5zwqu4z40s_tuning_task11';
/