Whenever an SQL statement is executed, Oracle (that is the RBO or CBO) designs an execution plan for it. This execution plan is basically a step by step instruction for how the statement must be executed. That is, the order in which tables are read, if indexes are used, which join methods are used to join tables and so on. The execution plan for an SQL statement can be viewed with the explain plan statement. The query execution plan is elaborated during an SQL statement's parse phase.
For generating execution plan or explain plan we can use different method depends upon the condition,
1. By using explain plan for command
2. By using the package DBMS_XPLAN
3. You can use the V$SQL_PLAN views to display the execution plan of a SQL statement
4. Using SQL*Plus Autotrace
You can execute the explain plan for command from sql*plus , however it will not show the plan but save it into a table named PLAN_TABLE.
SQL>CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
SQL>GRANT ALL ON sys.plan_table TO public;
SQL> set head off
SQL> @?/rdbms/admin/utlxpls.sql
To obtain a formatted execution plan for parallel plans:
SQL> set lines 150
SQL> set head off
SQL> @?/rdbms/admin/utlxplp.sql
For example we can use utlxpls.sql to display the formatted explain plan after executing explain plan for command , The output should be like this ,
SQL> EXPLAIN PLAN
SQL> EXPLAIN PLAN
You can also specify a statement Id when using the INTO clause.
2. By using the package DBMS_XPLAN
From Oracle 9i onward, you should display execution plans using the DBMS_XPLAN package. The
2.1 Display function:-
The
Example:-
SQL> EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';
explained.
SQL>
You can use dbms_xplan with display option to generate the execution plan ,
You can specify the format parameter to give different look to the explain plan , here i used BASIC parameter.
Basic usage of display function
To display the result of the last EXPLAIN PLAN command stored in the plan table:
SELECT * FROM table (DBMS_XPLAN.DISPLAY);
To display from other than the default plan table, "my_plan_table":
SELECT * FROM table (DBMS_XPLAN.DISPLAY('my_plan_table'));
To display the minimum plan information:
SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));
To display the plan for a statement identified by 'foo', such as statement_id='foo':
SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', 'foo'));
2.2 Display_cursor function:-
In Oracle 10g Release 1 Oracle introduced the
The following example show the advanced output from a query on the SCOTT schema.
SQL> CONN / AS SYSDBA
Connected.
SQL> GRANT SELECT ON v_$session TO scott;
Grant succeeded.
SQL> GRANT SELECT ON v_$sql TO scott;
Grant succeeded.
SQL> GRANT SELECT ON v_$sql_plan TO scott;
Grant succeeded.
SQL> GRANT SELECT ON v_$sql_plan_statistics_all TO scott;
Grant succeeded.
SQL> CONN scott/scott
Connected.
SQL> SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH';
--- out put omitted ---
SQL> SET LINESIZE 130
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID am2k9vaqsq9c9, child number 0
-------------------------------------
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename =
'SMITH'
Plan hash value: 351108634
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 57 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 37 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / E@SEL$1
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
3 - SEL$1 / D@SEL$1
4 - SEL$1 / D@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
USE_NL(@"SEL$1" "D"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
2 - filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."DEPTNO"[NUMBER,22],
"D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
3 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
60 rows selected.
SQL>
Basic usage of display_cursor function
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 'atfwcg8anrykp':
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));
To display runtime statistics for the cursor included in the preceding statement:
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ALLSTATS_ LAST');
2.3 Other Functions
There are some other useful functions in this package, but they are not using very often, so they are summarized below.
DISPLAY_AWR - Introduced in Oracle 10g Release 1, this function displays an execution plan of stored sql statement in the Advanced Workload Repository (AWR).
DISPLAY_SQLSET - Introduced in Oracle 10g Release 2, this function displays the execution plan of a given statement stored in a SQL tuning set.
DISPLAY_SQL_PLAN_BASELINE - Introduced in Oracle 11g Release 1, this function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.
DISPLAY_PLAN - Introduced in Oracle 11g Release 2, this function displays the contents of the plan table in a variety of formats.
Out of which display_awr is commonly used rather than others,
2.4 Display_awr function:-
This table function displays the contents of an execution plan stored in the AWR, and its syntax is ,
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);
Basic usage of display_awr function
To display the different execution plans associated with the SQL ID 'atfwcg8anrykp':
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));
To display all execution plans of all stored SQL statements containing the string 'TOTO':
SQL>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%';
3.By using V$SQL_PLAN view (commonly used in 9i and earlier )
For generating execution plan or explain plan we can use different method depends upon the condition,
1. By using explain plan for command
2. By using the package DBMS_XPLAN
3. You can use the V$SQL_PLAN views to display the execution plan of a SQL statement
4. Using SQL*Plus Autotrace
You can execute the explain plan for command from sql*plus , however it will not show the plan but save it into a table named PLAN_TABLE.
The PLAN_TABLE Output Table
Starting with release 10g, this table is automatically available as a global temporary table. With previous releases, you have to create it in each schema as needed. You can use the SQL script utlxplan.sql to manually create a local PLAN_TABLE in your schema. The exact name and location of this script depends on your operating system. On Unix, it is located in the $ORACLE_HOME/rdbms/admin directory.
SQL>conn sys/password as sysdba -- or mention your schema/password
SQL@$ORACLE_HOME/rdbms/admin/utlxplan.sqlSQL>conn sys/password as sysdba -- or mention your schema/password
SQL>CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;
SQL>GRANT ALL ON sys.plan_table TO public;
PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. Oracle Corporation recommends 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.
1. By using explain plan for command
The
The
EXPLAIN PLAN method doesn't require the query to be run, greatly reducing the time it takes to get an execution plan for long-running queries compared to AUTOTRACE. First the query must be explained.To explain a SQL statement,we can use the EXPLAIN PLAN FOR clause immediately before the statement. For example:
SQL> EXPLAIN PLAN FOR
2 SELECT *
3 FROM emp e, dept d
4 WHERE e.deptno = d.deptno
5 AND e.ename = 'SMITH';
Explained.
SQL>
Displaying PLAN_TABLE Output
After you have explained the plan, use the following method to retrieve the explain plan.
The explain plan for command explains the plan into the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE. For that you have to query PLAN_TABLE
SQL> rem Show plan_table execution from Plan table.
col COL1 format A6 head 'Id-Pid'
col parent_id format 99 head pnt
col operation format A18
col object_name format A18 truncate
col object_type format A18
col options format A20 truncate
col optimizer format A15 head 'Optmzer'
select id||'-'||parent_id COL1
,operation
,object_name
,object_type
,options
,optimizer
from plan_table
order by id
;
The output should be like this,
Querying PLAN_TABLE gives too much headache as we have to know each field in details and it is rarely used. From version 8.1.5, Oracle has supplied 2 scripts to extract formatted explain plans from plan_tables. One is for serial plans and the other is for parallel plans. They can be found under $ORACLE_HOME/rdbms/admin. Examples of their usage are below.
To obtain a formatted execution plan for serial plans:
SQL> set lines 150SQL> set head off
SQL> @?/rdbms/admin/utlxpls.sql
To obtain a formatted execution plan for parallel plans:
SQL> set lines 150
SQL> set head off
SQL> @?/rdbms/admin/utlxplp.sql
For example we can use utlxpls.sql to display the formatted explain plan after executing explain plan for command , The output should be like this ,
Identifying Statements for EXPLAIN PLAN
With multiple statements, you can specify a statement identifier and use that to identify your specific execution plan. Before using SET STATEMENT ID, remove any existing rows for that statement ID. The above example we can rewrite as follow ,
SQL> EXPLAIN PLAN
SET STATEMENT_ID = 'st1' FOR
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';
AND e.ename = 'SMITH';
Explained.
SQL>
Specifying Different Tables for EXPLAIN PLAN
You can specify the INTO clause to specify a different table. Here is the example,
SQL> EXPLAIN PLAN
INTO my_plan_table
FOR
SELECT last_name FROM employees;
Explained.
Explained.
SQL>
You can also specify a statement Id when using the INTO clause.
SQL>EXPLAIN PLAN
SET STATEMENT_ID = 'st1'
INTO my_plan_table
FOR
SELECT last_name FROM employees;
Explained.
Explained.
SQL>
From Oracle 9i onward, you should display execution plans using the DBMS_XPLAN package. The
DBMS_XPLAN package is used to format the output of an explain plan. It was introduced in Oracle 9i as a replacement for the "utlxpls.sql" script or custom queries of the plan table. Subsequent database versions have increased the functionality of the package.2.1 Display function:-
The
DISPLAY function allows us to display the execution plan stored in the plan table. First we explain an SQL statement by using explain plan for command. The DBMS_XPLAN.DISPLAY function will accept mainly 3 optional parameters in the following order.- 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.
Example:-
SQL> EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';
explained.
SQL>
You can use dbms_xplan with display option to generate the execution plan ,
You can specify the format parameter to give different look to the explain plan , here i used BASIC parameter.
Basic usage of display function
To display the result of the last EXPLAIN PLAN command stored in the plan table:
SELECT * FROM table (DBMS_XPLAN.DISPLAY);
To display from other than the default plan table, "my_plan_table":
SELECT * FROM table (DBMS_XPLAN.DISPLAY('my_plan_table'));
To display the minimum plan information:
SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', null, 'basic'));
To display the plan for a statement identified by 'foo', such as statement_id='foo':
SELECT * FROM table (DBMS_XPLAN.DISPLAY('plan_table', 'foo'));
2.2 Display_cursor function:-
In Oracle 10g Release 1 Oracle introduced the
DISPLAY_CURSOR function. Rather than displaying an execution plan from the PLAN_TABLE, it displays the actual execution plan used to run a query stored in the cursor cache. This information is gathered from the V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN views, so the user must have access to these. It accepts three optional parameters in the following order- sql_id - The
SQL_IDof the statement in the cursor cache. TheSQL_IDas available from theV$SQLandV$SQLAREAviews, or from theV$SESSIONview using thePREV_SQL_IDcolumn. If omitted, the last cursor executed by the session is displayed. - child_number - The child number of the cursor specified by the
SQL_IDparameter. If not specified, all cursors for the specifiedSQL_IDare displayed. - format - In addition to the setting available for the
DISPLAYfunction, this function also has 'RUNSTATS_LAST' and 'RUNSTATS_TOT' to display the last and total runtime statistics respectively. These additional format options require "STATISTICS_LEVEL=ALL".
The following example show the advanced output from a query on the SCOTT schema.
SQL> CONN / AS SYSDBA
Connected.
SQL> GRANT SELECT ON v_$session TO scott;
Grant succeeded.
SQL> GRANT SELECT ON v_$sql TO scott;
Grant succeeded.
SQL> GRANT SELECT ON v_$sql_plan TO scott;
Grant succeeded.
SQL> GRANT SELECT ON v_$sql_plan_statistics_all TO scott;
Grant succeeded.
SQL> CONN scott/scott
Connected.
SQL> SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename = 'SMITH';
--- out put omitted ---
SQL> SET LINESIZE 130
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
SQL_ID am2k9vaqsq9c9, child number 0
-------------------------------------
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename =
'SMITH'
Plan hash value: 351108634
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 57 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 37 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / E@SEL$1
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
3 - SEL$1 / D@SEL$1
4 - SEL$1 / D@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
USE_NL(@"SEL$1" "D"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
2 - filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."DEPTNO"[NUMBER,22],
"D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
3 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
60 rows selected.
SQL>
Basic usage of display_cursor function
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 'atfwcg8anrykp':
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp'));
To display runtime statistics for the cursor included in the preceding statement:
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('atfwcg8anrykp', NULL, 'ALLSTATS_ LAST');
2.3 Other Functions
There are some other useful functions in this package, but they are not using very often, so they are summarized below.
DISPLAY_AWR - Introduced in Oracle 10g Release 1, this function displays an execution plan of stored sql statement in the Advanced Workload Repository (AWR).
DISPLAY_SQLSET - Introduced in Oracle 10g Release 2, this function displays the execution plan of a given statement stored in a SQL tuning set.
DISPLAY_SQL_PLAN_BASELINE - Introduced in Oracle 11g Release 1, this function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.
DISPLAY_PLAN - Introduced in Oracle 11g Release 2, this function displays the contents of the plan table in a variety of formats.
Out of which display_awr is commonly used rather than others,
2.4 Display_awr function:-
This table function displays the contents of an execution plan stored in the AWR, and its syntax is ,
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);
sql_id |
Specifies the
SQL_ID of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the column SQL_ID in DBA_HIST_SQLTEXT. |
plan_hash_value |
Specifies the
PLAN_HASH_VALUE of a SQL statement. This parameter is optional. If omitted, the table function will return all stored execution plans for a given SQL_ID. |
db_id |
Specifies the
database_id for which the plan of the SQL statement, identified by SQL_ID should be displayed. If not supplied, thedatabase_id of the local database will be used, as shown in V$DATABASE. |
format |
Controls the level of details for the plan. It accepts four values:
|
To display the different execution plans associated with the SQL ID 'atfwcg8anrykp':
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('atfwcg8anrykp'));
To display all execution plans of all stored SQL statements containing the string 'TOTO':
SQL>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%';
3.By using V$SQL_PLAN view (commonly used in 9i and earlier )
Many DBAs query views such as V$SQL or V$SQLAREA to find queries to tune. They then take that query and run it through explain plan. The problem with the explain plan statement, used in this manner, is that it determines how Oracle would execute the given SQL statement. It does not tell you how Oracle has executed that SQL statement.
Oracle 9i introduced the V$SQL_PLAN dynamic performance view. The purpose of this view is to tell you how Oracle has executed a specific SQL statement. With this view, the DBA can be sure they know how the SQL statement was executed. This view is commonly used in oracle 9i and earlier as dbms_xplan.display_Cursor later introduced in 10g can display the execution plan from v$Sql_plan and associated structure in a better format .
4. Using SQL*Plus Autotrace
The autotrace utility is a very underutilized feature of SQL*Plus. It offers statement tracing and instant feedback on any successful SELECT, INSERT, UPDATE or DELETE statement. The utility requires a plan table (for the Explain Plan) under the current user’s schema. In addition, it requires the plustrace or DBA role for the user executing the trace.
The AUTOTRACE facility is only available in SQL*Plus. Unlike the EXPLAIN PLAN command, the AUTOTRACE facility must use a table called PLAN_TABLE. The EXPLAIN PLAN command can use a plan table of any name.
Setting up AUTOTRACE
Follow these steps to set up the AUTOTRACE facility to display an explain plan.
1. Ensure that the PLAN_TABLE table exists. To see if your schema has a
PLAN_TABLE, try to describe it:
SQL> desc plan_table;
If the PLAN_TABLE doesn’t exist, you need to create one. Run this script to create the PLAN_TABLE in
your schema:
SQL> @?/rdbms/admin/utlxplan.sql
2. Your schema also needs access to the PLUSTRACE role. You can verify access to
the PLUSTRACE role using the following:
select username,granted_role from user_role_privs
where granted_role='PLUSTRACE';
If you don’t have access to the PLUSTRACE role, run steps 3 and 4 as the SYS schema:
3. Connect as SYS and run the plustrce.sql script:
SQL> conn / as sysdba
SQL> @?/sqlplus/admin/plustrce.sql
4. Grant the PLUSTRACE role to developers (or to a specific role) who want to use
the AUTOTRACE facility:
SQL> grant plustrace to star1;
AUTOTRACE OPTIONS: -
1) SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default. Queries are run as normal.
2) SET AUTOTRACE ON - The query execution takes place and it gives the output
and the AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
3) SET AUTOTRACE ON EXPLAIN - The query is run as normal and it gives the output
and the AUTOTRACE report shows only the optimizer execution path.
4) SET AUTOTRACE ON STATISTICS - The query is run as normal and it gives the output
and the AUTOTRACE report shows only the SQL statement execution statistics.
5) SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the query output, if any.
6) SET AUTOTRACE TRACEONLY EXPLAIN - Like SET AUTOTRACE TRACEONLY,but suppresses the display of the execution statistics,showing only the query plan. This setting does not actually execute the query. It only parses and explains the query.
7) SET AUTOTRACE TRACEONLY STATISTICS - Like SET AUTOTRACE TRACEONLY,but suppresses the display of the query plan. It shows only the execution statistics.
Note:- Just SET AUTOTRACE ON, returns all the rows of the query followed by the execution
plan and statistics of the query run. Now, SET AUTOTRACE TRACE which is synonymous to SET AUTOTRACE TRACEONLY does not return the rows of the query but returns the count of rows (like it always does at the end of every SELECT query) along with the execution plan and the statistics of the query.
This implies that SET AUTOTRACE ON is really of no use for us, DBAs, since it returns the result set of the query before it shows up the explain plan and the statistics of the query - If the result set is of say 10 million records then one has to wait for all the records to come up before (s)he gets a chance to look at the explain plan and statistics. It is suggested to use SET AUTOTRACE TRACE(ONLY) as it gives us the information of number of rows returned, explain plan and statistics.
No comments:
Post a Comment