Get the Execution Plan from/using Event 10053 trace file

The event 10053, which is used to dump cost-based optimizer (CBO) computations may include a plan.

To capture an Optimizer trace (10053) for a SQL statement you need to issue an alter session command to switch on a 10053 trace for that entire session, and then issue the SQL statement you wanted to capture the trace for. Once the statement completed you need to exit the session to disable the trace. Then you can look into SER_DUMP_DEST directory for the trace file.

To the following and check the trace for execution details....

SQL> alter session set events '10053 trace name context forever, level 1';
.
.
SQL> select * from emp;
.
.
SQL> exit;

To trace specific SQLID , do this.....

atler session set events 'trace[rdbms.SQL_Optimizer.*][sql:interested-sqlid]';

Reference Link:  Check Maria Colgan blog post for more details...

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

Get the Execution Plan from/using Trace files generated by DBMS_MONITOR

Tracing can be used to debug performance problems. Trace-enabling procedures have been implemented as part of the DBMS_MONITOR package. These procedures enable tracing globally for a database.

You can use the DATABASE_TRACE_ENABLE procedure to enable session level SQL tracing instance-wide. The procedure has the following parameters:

    • WAITS: Specifies whether wait information is to be traced
    • BINDS: Specifies whether bind information is to be traced
    • INSTANCE_NAME: Specifies the instance for which tracing is to be enabled. Omitting INSTANCE_NAME means that the session-level tracing is enabled for the whole database.

Use the DATABASE_TRACE_DISABLE procedure to disable SQL tracing for the whole database or a specific instance.

Similarly, you can use the SESSION_TRACE_ENABLE procedure to enable tracing for a given database session identifier on the local instance. The SID and SERIAL# information can be found from V$SESSION.

All trace files will be created in USER_DUMP_DEST (i.e. $ADR_HOME/trace) location.

Interpret the trace result using tkprof utility. This utility will generate output file which will be human readable, and which will have detailed information about execution plan.

Translate trace file: 

tkprof <trace-file> <output-file> explain=user/password@service waits=yes sys=no


Example :

• For all sessions in the database:
      EXEC dbms_monitor.DATABASE_TRACE_ENABLE(TRUE,TRUE);
      EXEC dbms_monitor.DATABASE_TRACE_DISABLE();
• For a particular session:
      EXEC dbms_monitor.SESSION_TRACE_ENABLE(session_id=>20, serial_num=>10, waits=>TRUE, binds=>FALSE);
      EXEC dbms_monitor.SESSION_TRACE_DISABLE(session_id=>20, serial_num=>10);

Get the Execution Plan from/using SQL Tuning Set (STS)

A SQL tuning set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. You can load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the shared SQL area, or customized SQL provided by the user. An STS includes:

• A set of SQL statements
• Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment
• Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type
• Associated execution plans and row source statistics for each SQL statement (optional)

A SQL Tuning Set can be used as input to the SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other input parameters specified by the user. SQL Tuning Sets are transportable across databases and can be exported from one system to another, allowing for the transfer of SQL workloads between databases for remote performance diagnostics and tuning.

Manage SQL tuning sets using Enterprise Manager. If Enterprise Manager is unavailable, then you can manage SQL tuning sets using the DBMS_SQLTUNE package procedures.

DISPLAY_SQLSET:

DBMS_XPLAN.DISPLAY_SQLSET fuction provides the way to display the plan of a statement stored in the SQL tuning set. To use the DISPLAY_SQLSET functionality, the calling user must have SELECT privilege on ALL_SQLSET_STATEMENTS and ALL_SQLSET_PLANS. All these privileges are automatically granted as part of the SELECT_CATALOG role.

Syntax:

DBMS_XPLAN.DISPLAY_SQLSET(

   sqlset_name        IN  VARCHAR2, 

   sql_id                 IN  VARCHAR2,

   plan_hash_value  IN NUMBER := NULL,

   format                IN  VARCHAR2  := 'TYPICAL', 

   sqlset_owner       IN  VARCHAR2  := NULL)

  RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

Examples:

To display the execution plan for the SQL statement associated with SQL ID 'gwp663cqhqbf' and PLAN HASH 3693697075 in the SQL Tuning Set called 'OLAP_optimization_001":

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
       'OLTP_optimization_001','gwp663cqhqbf', 3693697075));

To display all execution plans of the SQL ID 'atfwcg8anrykp' stored in the SQL tuning set:

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
      'OLAP_optimization_001','gwp663cqhqbf'));

To display runtime statistics for the SQL statement included in the preceding statement:

SELECT * FROM table (
   DBMS_XPLAN.DISPLAY_SQLSET(
      'OLAP_optimization_001', 'gwp663cqhqbf', NULL, 'ALLSTATS LAST');

-Asifkhan P.

Get the Execution Plan from/using SQL management base (SQL plan baselines)

It is good thing, if you know what is SMB/SPB/SPM before you actually jump into execution plan extraction.  Some pointers ......

The SQL Management Base (SMB) is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement log, plan histories, and SQL plan baselines, as well as SQL profiles.

SQL plan baselines are the key objects that SQL Plan Management uses to prevent unverified change to SQL execution plans. When SQL Plan Management is active, there will not be drastic changes in performance even as the statistics change or as the database version changes. Until a new plan is verified to produce better performance than the current plan, it will not be considered by the optimizer.

SQL Outlines have been used in past versions. They are still available for backward compatibility, but Outlines are deprecated in favor of SQL Plan Management.

A SQL plan change can occur due to a variety of reasons such as optimizer version, optimizer statistics, optimizer parameters, schema definitions, system settings, and SQL profile creation. Various plan control techniques are available in the Oracle Database to address performance regressions due to plan changes. The oldest is the use of hints in the SQL code to force a specific access path. Stored outlines allowed the hints to be stored separate from the code and modified.

SQL Plan Management automatically controls SQL plan evolution by maintaining what is
called “SQL plan baselines.” With this feature enabled, a newly generated SQL plan can join a SQL plan baseline only if it has been proven that doing so will not result in performance regression. So, during the execution of a SQL statement, only a plan that is part of the SQL plan baseline can be used.

For SQL statements that are executed more than once, the optimizer maintains a history of plans for individual SQL statements. The optimizer recognizes a repeatable SQL statement by maintaining a statement log. A SQL statement is recognized as repeatable when it is parsed or executed again after it has been logged. After a SQL statement is recognized as repeatable, various plans generated by the optimizer are maintained as a plan history containing relevant information (such as SQL text, outline, bind variables, and compilation environment) that is used by the optimizer to reproduce an execution plan.

The main benefit of the SQL Plan Management feature is performance stability of the system by avoiding plan regressions.

A plan history contains different plans generated by the optimizer for a SQL statement over time. However, only some of the plans in the plan history may be accepted for use. For example, a new plan generated by the optimizer is not normally used until it has been verified not to cause a performance regression. Plan verification is done by default as part of the Automatic SQL Tuning task running as an automated task in a maintenance window.

An Automatic SQL Tuning task targets only high-load SQL statements.For those statements, it automatically implements actions such as making a successfully verified plan an accepted plan. A set of acceptable plans constitutes a SQL plan baseline. The very first plan generated for a SQL statement is obviously acceptable for use; therefore, it forms the original plan baseline. Any new plans subsequently found by the optimizer are part of the plan history but not part of the plan baseline initially.

The DISPLAY_SQL_PLAN_BASELINE table function displays formatted information about a specific plan, or all plans in the SQL plan baseline in one of three formats (BASIC, TYPICAL or ALL). The following example displays the default format (TYPICAL) report for a specific plan. Or you can query DBA_SQL_PLAN_BASELINES view.

SET LONG 10000

SELECT *
FROM   TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_d90440b9ed3324'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

SQL handle: SYS_SQL_7b76323ad90440b9
SQL text: SELECT description FROM   spm_test_tab WHERE  id = 99
--------------------------------------------------------------------------------

Plan name: SQL_PLAN_d90440b9ed3324
Enabled: YES     Fixed: YES     Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3121206333

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    24 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SPM_TEST_TAB     |     1 |    24 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SPM_TEST_TAB_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   2 - access("ID"=99)
25 rows selected.

SQL>

-Asifkhan P.

Get the Execution Plan from/using DBA_HIST_SQL_PLAN (AWR)

The Automatic Workload Repository (AWR) infrastructure and Statspack store execution plans of top SQL statements. Plans are recorded into DBA_HIST_SQL_PLAN. This view captures information from V$SQL_PLAN and is used with the DBA_HIST_SQLSTAT view.

Sample Script :

col operation for a75
SELECT
      LPAD(' ',depth)||
      OPERATION||'_'||
      OPTIONS||' '||
      OBJECT_NAME  operation
FROM
       DBA_HIST_SQL_PLAN
WHERE
        sql_id='&SQL_ID'
    and DBID=&DBID
order by  ID,PLAN_HASH_VALUE;




DISPLAY_AWR :

This table function displays the contents of an execution plan stored in the AWR. This is give you old execution plans.

Syntax :

DBMS_XPLAN.DISPLAY_AWR( 

   sql_id                  IN      VARCHAR2,

   plan_hash_value   IN      NUMBER DEFAULT NULL,

   db_id                  IN      NUMBER DEFAULT NULL,

   format                IN      VARCHAR2 DEFAULT TYPICAL);

Examples :

To display the different execution plans associated with the SQL ID 'atfwcg8anrykp':

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

Get the Execution Plan from/using V$SQL_PLAN_MONITOR

Starting with 11g, Oracle introduced a new tool to add to the performance optimizer’s toolkit called “Real Time SQL Monitoring”.  Real Time SQL Monitoring, or just “SQL Monitoring”, enables you to measure the performance of SQL statements while they are executing.  

The SQL monitoring feature is enabled by default when the STATISTICS_LEVEL initialization parameter is either set to ALL or TYPICAL (the default value). Additionally, the CONTROL_MANAGEMENT_PACK_ACCESS parameter must be set to DIAGNOSTIC+TUNING (the default value).

SQL monitoring is automatically started when a SQL statement runs parallel, or when it has consumed at least five seconds of the CPU or I/O time in a single execution.

SQL monitoring is active by default. However, to force SQL monitoring, use the MONITOR hint. To stop monitored, use the NO_MONITOR hint.

We can monitor the statistics for SQL statement execution using the V$SQL_MONITOR and V$SQL_PLAN_MONITOR views.

Monitoring information is not deleted immediately, it will be kept in the V$SQL_MONITOR view for at least one minute. The entry is eventually deleted so its space can be reclaimed as new statements are monitored.


The V$SQL_MONITOR and V$SQL_PLAN_MONITOR views can be used in conjunction with the following views to get additional information about the execution that is monitored:V$SQL, V$SQL_PLAN, V$ACTIVE_SESSION_HISTORY, V$SESSION_LONGOPS, and V$SESSION.

Instead, you can use the SQL monitoring report to view SQL monitoring data. The SQL monitoring report is also available in a GUI version through Enterprise Manager and
SQL Developer.

The DBMS_SQLTUNE.REPORT_SQL_MONITOR function accepts several input parameters to specify the execution, the level of detail in the report, and the report type (TEXT, HTML, or XML). By default, a text report is generated for the last execution that was monitored if no parameters are specified.

Syntax

DBMS_SQLTUNE.REPORT_SQL_MONITORK(
   sql_id                   IN VARCHAR2  := NULL,
   session_id             IN NUMBER    := NULL,
   session_serial        IN NUMBER    := NULL,
   sql_exec_start       IN DATE      := NULL,
   sql_exec_id           IN NUMBER    := NULL,
   inst_id                  IN NUMBER    := -1,
   start_time_filter    IN DATE      := NULL,
   end_time_filter     IN DATE      := NULL,
   instance_id_filter  IN NUMBER    := NULL,
   parallel_filter        IN VARCHAR2  := NULL,
   event_detail         IN VARCHAR2  := 'YES',
   report_level          IN VARCHAR2  := 'TYPICAL',
   type                     IN VARCHAR2  := 'TEXT')

 RETURN CLOB;


Example:

Generate report in text format for recently captured sql....

SQL> set long 10000000
SQL> set longchunksize 10000000
SQL> set linesize 200
SQL> select dbms_sqltune.report_sql_monitor from dual;

Generate a report in html format.......

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SPOOL /home/report_sql_monitor.htm

SELECT DBMS_SQLTUNE.report_sql_monitor(

  sql_id       => '123mvcc67nfy4',

  type         => 'HTML',

  report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

REPORT_SQL_MONITOR_LIST:

This function will give you list of already monitered sql's and other details.

SET LONG 1000000

SET LONGCHUNKSIZE 1000000

SET LINESIZE 1000

SET PAGESIZE 0

SET TRIM ON

SET TRIMSPOOL ON

SET ECHO OFF

SET FEEDBACK OFF

SPOOL /home/report_sql_monitor_list.htm

SELECT DBMS_SQLTUNE.report_sql_monitor_list(

  type         => 'HTML',

  report_level => 'ALL') AS report

FROM dual;

SPOOL OFF

Reference Link:  This page has more stuff.

• http://www.oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1.php#report_sql_detail

Get the Execution Plan from/using V$SQL_PLAN

V$SQL_PLAN contains the execution plan information for each child cursor loaded in the library cache. V$SQL_PLAN provides a way to examine the execution plan for cursors that were recently executed. Information in V$SQL_PLAN is very similar to the output of an EXPLAIN PLAN statement. It contains the execution plan of every cursor in the library cache (including child).  The ADDRESS, HASH_VALUE, and CHILD_NUMBER columns can be used to join with V$SQL to add the child cursor–specific information.

The information in this view is very similar to the information in PLAN_TABLE. However, V$SQL_PLAN contains the actual plan used. The execution plan obtained by the EXPLAIN PLAN statement can be different from the execution plan used to execute the cursor. This is because the cursor might have been compiled with different values of session parameters or bind variables.

V$SQL_PLAN shows the plan for a cursor rather than for all cursors associated with a SQL statement. The difference is that a SQL statement can have more than one cursor associated with it, with each cursor further identified by a CHILD_NUMBER. For example, the same statement executed by different users has different cursors associated with it if the object that is referenced is in a different schema. Similarly, different hints can cause different cursors. The V$SQL_PLAN table can be used to see the different plans for different child cursors of the same statement. 

You can query V$SQL_PLAN using the DBMS_XPLAN.DISPLAY_CURSOR() function to display the current or last executed statement (as shown in the example). You can pass the value of SQL_ID for the statement as a parameter to obtain the execution plan for a given statement. SQL_ID is the SQL_ID of the SQL statement in the cursor cache. 

You can retrieve the appropriate value by querying the SQL_ID column in V$SQL or V$SQLAREA. Alternatively, you could select the PREV_SQL_ID column for a specific session out of V$SESSION. This parameter defaults to null in which case the plan of the last cursor executed by the session is displayed. 

To obtain 47ju6102uvq5q SQL_ID execution plan, execute the following query:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('47ju6102uvq5q'));

Get the Execution Plan from/using DBMS_XPLAN

The DBMS_XPLAN package is used to format the output of an explain plan. The DBMS_XPLAN package supplies five table functions:

• DISPLAY: To format and display the contents of a plan table
• DISPLAY_AWR: To format and display the contents of the execution plan of a stored SQL statement in the AWR.
• DISPLAY_CURSOR: To format and display the contents of the execution plan of any loaded cursor.
• DISPLAY_SQL_PLAN_BASELINE: To display one or more execution plans for the SQL statement identified by SQL handle.
• DISPLAY_SQLSET: To format and display the contents of the execution plan of statements stored in a SQL tuning set

An advantage of using the DBMS_XPLAN package table functions is that the output is
formatted consistently without regard to the source.


DISPLAY :

This table function displays the contents of the plan table. The DBMS_XPLAN.DISPLAY function can accept 3 optional parameters:

• table_name - Name of the PLAN_TABLE, default value 'PLAN_TABLE'.
• statement_id - Statement id of the plan to be displayed. The default value is NULL, which displays the most recent execution plan in the PLAN_TABLE.
• format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL'. There is also an undocumented 'ADVANCED' setting.

Syntax :

DBMS_XPLAN.DISPLAY(

   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',

   statement_id  IN  VARCHAR2  DEFAULT  NULL, 

   format           IN  VARCHAR2  DEFAULT  'TYPICAL',

   filter_preds    IN  VARCHAR2  DEFAULT NULL);

Example:

       SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);

       SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','id','BASIC'));

DISPLAY_AWR :

This table function displays the contents of an execution plan stored in the AWR.

Syntax

DBMS_XPLAN.DISPLAY_AWR( 

   sql_id                  IN      VARCHAR2,

   plan_hash_value   IN      NUMBER DEFAULT NULL,

   db_id                  IN      NUMBER DEFAULT NULL,

   format                IN      VARCHAR2 DEFAULT TYPICAL);

Examples

To display the different execution plans associated with the SQL ID 'atfwcg8anrykp':

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

To display all execution plans of all stored SQL statements containing the string 'TOTO':

SELECT tf.* FROM DBA_HIST_SQLTEXT ht, table     (DBMS_XPLAN.DISPLAY_AWR(ht.sql_id,null, null,  'ALL' )) tf 

 WHERE ht.sql_text like '%TOTO%';

DISPLAY_CURSOR :

This table function displays the explain plan of any cursor loaded in the cursor cache.

Syntax: 

DBMS_XPLAN.DISPLAY_CURSOR(

   sql_id             IN  VARCHAR2  DEFAULT  NULL,

   child_number  IN  NUMBER     DEFAULT  NULL, 

   format            IN  VARCHAR2  DEFAULT  'TYPICAL');

Examples:

To display the execution plan of the last SQL statement executed by the current session:

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR);

To display the execution plan of all children associated with the SQL ID 'atfwcg8anryp':

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anryp'));

To display runtime statistics for the cursor included in the preceding statement:

SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anryp', NULL, 'ALLSTATS LAST');

DISPLAY_SQL_PLAN_BASELINE:

This table function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.

Syntax

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (

   sql_handle       IN VARCHAR2 := NULL,

   plan_name       IN VARCHAR2 := NULL,

   format            IN VARCHAR2 := 'TYPICAL')

 RETURN dbms_xplan_type_table;

Examples

Display all plans of a SQL statement identified by the SQL handle 'SYS_SQL_b1d49f6074ab9af' using TYPICAL format

SET LINESIZE 150

SET PAGESIZE 2000

SELECT t.*

  FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(

                 'SYS_SQL_b1d49f6074ab9af')) t;

Display all plans of one or more SQL statements containing the string 'HR2' using BASIC format

SET LINESIZE 150

SET PAGESIZE 2000

SELECT t.*

   FROM (SELECT DISTINCT sql_handle FROM dba_sql_plan_baselines

         WHERE sql_text like '%HR2%') pb,

        TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(pb.sql_handle, NULL,

                                                   'BASIC')) t;

DISPLAY_SQLSET Function:

This table function displays the execution plan of a given statement stored in a SQL tuning set.

Syntax

DBMS_XPLAN.DISPLAY_SQLSET(

   sqlset_name      IN  VARCHAR2, 

   sql_id               IN  VARCHAR2,

   plan_hash_value  IN NUMBER := NULL,

   format              IN  VARCHAR2  := 'TYPICAL', 

   sqlset_owner     IN  VARCHAR2  := NULL)

  RETURN DBMS_XPLAN_TYPE_TABLE PIPELINED;

Examples

To display the execution plan for the SQL statement associated with SQL ID 'gwp663cqhqbf' and PLAN HASH 369369707 in the SQL Tuning Set called 'OLTP_optimization_0405":

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_SQLSET(

       'OLTP_optimization_0405','gwp663cqhqbf', 369369707));

To display all execution plans of the SQL ID 'atfwcg8anrykp' stored in the SQL tuning set:

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_SQLSET(

      'OLTP_optimization_0405','gwp663cqhqbf'));

To display runtime statistics for the SQL statement included in the preceding statement:

SELECT * FROM table (

   DBMS_XPLAN.DISPLAY_SQLSET(

      'OLTP_optimization_0405', 'gwp663cqhqbf', NULL, 'ALLSTATS LAST');

Get the Execution Plan from/using EXPLAIN PLAN

The EXPLAIN PLAN command enables you to view the execution plan that the optimizer might use to execute a SQL statement. This command is very useful because it outlines the plan that the optimizer may use and inserts it in a table called PLAN_TABLE without executing the SQL statement. This command is available from SQL*Plus or SQL Developer.It does not execute the statement, but simply produces the plan that may be used, and inserts this plan into a table.

If you execute the EXPLAIN PLAN in SQL*Plus command, you can then SELECT from the PLAN_TABLE to view the execution plan.

Command:  

This command inserts the execution plan of the SQL statement in the plan table and adds the optional demo name tag for future reference.

SQL> EXPLAIN PLAN SET STATEMENT_ID='demo' FOR select * from emp;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
---------------------------------------------------------
| Id  | Operation                           |   Name  |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |             |
|   1 |  TABLE ACCESS FULL            | EMP      |
---------------------------------------------------------
8 rows selected.
SQL>

Formatted Select of PLAN_TABLE for EXPLAIN  PLAN command.........

set echo off

column operation   format a20

column options     format a16

column object_name format a20

column id          format 99

column query       heading "Query Plan" format a80

select lpad(' ',2*(level-1))||operation||' '||options||' '

       ||object_name||' '

       ||decode(object_node,'','','['||object_node||'] ')

       ||decode(optimizer,'','','['||optimizer||'] ')

       ||decode(id,0,'Cost = '||position) query

from   plan_table

start with id = 0

connect by prior id = parent_id;

set echo on

GATHER_PLAN_STATISTICS hint

To gather execution statistics, set statistics_level = ALL at session level or add hint /*+ gather_plan_statistics */ into SQL. 

GATHER_PLAN_STATISTICS hint tells Oracle to collect execution statistics for a SQL statement. These execution statistics are then shown next to the original Optimizer estimates in the execution plan if you use the function DBMS_XPLAN.DISPLAY_CURSOR to display the plan. You also have to set the  FORMAT parameter to 'ALLSTATS LAST' (DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST')). The original Optimizer estimates are shown in the E-Rows column while the actual statistics gathered during execution are shown in the A-Rows column.

If we add the GATHER_PLAN_STATISTICS hint to our simple SQL statement we should be able to see the actual cardinality of each operation at execution time alongside the Optimizer estimates for each cardinality in the plan.

SELECT /*+ GATHER_PLAN_STATISTICS */ p.prod_name, SUM(s.quantity_sold)
FROM         sales s, products p
WHERE      s.prod_id =p.prod_id
AND            p.prod_desc = 'Envoy Ambassador'
GROUP By p.prod_name ;

The execution plan for the query is as follows:

 

The statistics in V$SQL_PLAN_STATISTICS are available for cursors that have been compiled with the STATISTICS_LEVEL initialization parameter set to ALL or using the GATHER_PLAN_STATISTICS hint.

DRIVING_SITE Hint

If we are joining local table with remote table, then by default, execution will happens at locally, and optimizer wont utilize remote table statistics, it will do full table scan.

  

The DRIVING_SITE hint lets you specify the site where the query execution is performed.  It is best to let cost-based optimization determine where the execution should be performed, but if you prefer to override the optimizer, you can specify the execution site manually.

Example: 

SELECT /*+ DRIVING_SITE(dept)*/ * FROM emp, dept@remote.com dept

WHERE emp.deptno = dept.deptno;

Get the Execution Plan from/using PLAN_TABLE

PLAN_TABLE is automatically created as a global temporary table in the SYS  schema, creating a public synonym for it, and then granting all necessary privileges to PUBLIC.

PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans.

PLAN_TABLE just shows you a plan that might not be the one chosen by the optimizer.

• PLAN_TABLE:

      – Is automatically created to hold the EXPLAIN PLAN output.

      – You can create your own using utlxplan.sql script if you want to keep the execution plan information for a long term..

      – Advantage: SQL is not executed

      – Disadvantage: May not be the actual execution plan

• PLAN_TABLE is hierarchical.

• Hierarchy is established with the ID and PARENT_ID columns.

It is recommended that you drop and rebuild your local PLAN_TABLE table after upgrading the version of the database because the columns might change. This can cause scripts to fail or cause TKPROF to fail, if you are specifying the table.

Example:

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'demo' FOR SELECT * FROM emp
2 WHERE ename = 'KING';
Explained.
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> select * from table(DBMS_XPLAN.DISPLAY());
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ENAME"='KING')

Different instruments to get Execution Plan?

Generating and displaying the execution plan of a SQL statement is a common task for most DBAs, SQL developers, and preformance experts as it provides them information on the performance characteristics of a SQL statement. An execution plan shows the detailed steps necessary to execute a SQL statement. These steps are expressed as a set of database operators that consumes and produces rows. The order of the operators and their implentation is decided by the query optimizer using a combination of query transformations and physical optimization techniques.

There are different instruments/methods you can use to look at the execution plan of a SQL statement:

• PLAN_TABLE (SQL Developer or SQL*Plus)
• EXPLAIN PLAN

• DBMS_XPLAN

• V$SQL_PLAN (Library Cache)

• V$SQL_PLAN_MONITOR (11g)

• DBA_HIST_SQL_PLAN (AWR)

• SQL management base (SQL plan baselines)

• SQL Tuning Set

• Trace files generated by DBMS_MONITOR

• Event 10053 trace file

• STATS$SQL_PLAN (Statspack)

For clarity I am going to write different post for each of methods, so stay tuned :)