转自:http://blog.chinaunix.net/uid-3634-id-2130055.html
平时都喜欢用set autotrace,有日子没用DBMS_XPLAN都快忘了。找了两个说明文档,记录下。
metalink:
PURPOSE
-------
To explain the new features of dbms_xplan.display
SCOPE & APPLICATION
-------------------
Support analysts and DBAs.
The function dbms_xplan.display
---------------------------------
In 9.2 we introduced a new function called dbms_xplan.display
to show execution plans. This function has different option and
can display plans for serial and parallel queries.
This function is defiened in dbmsutil.sql, where you can find all
the different options.
In the scripts utlxpls.sql and utlxplp.sql we call this new function.
Arguments of the function display
-----------------------------------
function display (table_name varchar2 default 'PLAN_TABLE',
statement_id varchar2 default null,
format varchar2 default 'TYPICAL')
- table_name:
specifies the table name where the plan is stored. This
parameter defaults to "PLAN_TABLE" which is the default
plan table for the explain plan.
- statement_id:
specifies the statement id of the plan to display. This
parameter defaults to NULL.
- format:
'BASIC': Display only the minimum, i.e. the operation id,
the operation name and its option
'TYPICAL': This is the default. Display most information
of the explain plan (operation id, name and option,
#rows, #bytes and optimizer cost. Pruning,
parallel and predicate information are only
displayed when applicable.
'ALL': Maximum level. Like typical with additional display
of the SQL statements generated for PX servers
(only if parallel).
'SERIAL': Like typical except that parallel information are
not displayed, even if the plan is parallel
In utlxpls.sql we use the command:
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
In utlxplp.sql we have
SQL> select * from table(dbms_xplan.display());
This means we are not shown the SQL Slave information with this script.
Some examples
--------------
SQL> explain plan for SELECT /*+ PARALLEL(e) PARALLEL(d) */ dname,
MAX(sal), AVG(sal) FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY
dname;
SQL> set linesize 150
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 7 | | | |
| 1 | SORT GROUP BY | | 4 | 72 | 7 | 07,01 | P->S | QC (RAND) |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 5 | 1 | 07,01 | PCWC | |
| 3 | NESTED LOOPS | | 6 | 108 | 3 | 07,00 | P->P | HASH |
| 4 | TABLE ACCESS FULL | DEPT | 4 | 52 | 1 | 07,00 | PCWP | |
|* 5 | INDEX RANGE SCAN | EMP_ID1 | 5 | | | 07,00 | PCWP | |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPTNO"="D"."DEPTNO")
Note: cpu costing is off
With optinon ALL we see the SQL Slave information
SQL> set linesize 150
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 7 | | | |
| 1 | SORT GROUP BY | | 4 | 72 | 7 | 07,01 | P->S | QC (RAND) |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 5 | 1 | 07,01 | PCWC | |
| 3 | NESTED LOOPS | | 6 | 108 | 3 | 07,00 | P->P | HASH |
| 4 | TABLE ACCESS FULL | DEPT | 4 | 52 | 1 | 07,00 | PCWP | |
|* 5 | INDEX RANGE SCAN | EMP_ID1 | 5 | | | 07,00 | PCWP | |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPTNO"="D"."DEPTNO")
PX Slave SQL Information (identified by operation id):
------------------------------------------------------
1 - SELECT A1.C0,AVG(A1.C1),MAX(A1.C1) FROM :Q207000 A1 GROUP BY A1.C0
3 - SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDEX(A2 "EMP_ID1") */ A1.C1 C0,A2."SAL" C1,A2."SAL" C2
-------
To explain the new features of dbms_xplan.display
SCOPE & APPLICATION
-------------------
Support analysts and DBAs.
The function dbms_xplan.display
---------------------------------
In 9.2 we introduced a new function called dbms_xplan.display
to show execution plans. This function has different option and
can display plans for serial and parallel queries.
This function is defiened in dbmsutil.sql, where you can find all
the different options.
In the scripts utlxpls.sql and utlxplp.sql we call this new function.
Arguments of the function display
-----------------------------------
function display (table_name varchar2 default 'PLAN_TABLE',
statement_id varchar2 default null,
format varchar2 default 'TYPICAL')
- table_name:
specifies the table name where the plan is stored. This
parameter defaults to "PLAN_TABLE" which is the default
plan table for the explain plan.
- statement_id:
specifies the statement id of the plan to display. This
parameter defaults to NULL.
- format:
'BASIC': Display only the minimum, i.e. the operation id,
the operation name and its option
'TYPICAL': This is the default. Display most information
of the explain plan (operation id, name and option,
#rows, #bytes and optimizer cost. Pruning,
parallel and predicate information are only
displayed when applicable.
'ALL': Maximum level. Like typical with additional display
of the SQL statements generated for PX servers
(only if parallel).
'SERIAL': Like typical except that parallel information are
not displayed, even if the plan is parallel
In utlxpls.sql we use the command:
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
In utlxplp.sql we have
SQL> select * from table(dbms_xplan.display());
This means we are not shown the SQL Slave information with this script.
Some examples
--------------
SQL> explain plan for SELECT /*+ PARALLEL(e) PARALLEL(d) */ dname,
MAX(sal), AVG(sal) FROM emp e, dept d WHERE e.deptno = d.deptno GROUP BY
dname;
SQL> set linesize 150
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 7 | | | |
| 1 | SORT GROUP BY | | 4 | 72 | 7 | 07,01 | P->S | QC (RAND) |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 5 | 1 | 07,01 | PCWC | |
| 3 | NESTED LOOPS | | 6 | 108 | 3 | 07,00 | P->P | HASH |
| 4 | TABLE ACCESS FULL | DEPT | 4 | 52 | 1 | 07,00 | PCWP | |
|* 5 | INDEX RANGE SCAN | EMP_ID1 | 5 | | | 07,00 | PCWP | |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPTNO"="D"."DEPTNO")
Note: cpu costing is off
With optinon ALL we see the SQL Slave information
SQL> set linesize 150
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 72 | 7 | | | |
| 1 | SORT GROUP BY | | 4 | 72 | 7 | 07,01 | P->S | QC (RAND) |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 5 | 1 | 07,01 | PCWC | |
| 3 | NESTED LOOPS | | 6 | 108 | 3 | 07,00 | P->P | HASH |
| 4 | TABLE ACCESS FULL | DEPT | 4 | 52 | 1 | 07,00 | PCWP | |
|* 5 | INDEX RANGE SCAN | EMP_ID1 | 5 | | | 07,00 | PCWP | |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPTNO"="D"."DEPTNO")
PX Slave SQL Information (identified by operation id):
------------------------------------------------------
1 - SELECT A1.C0,AVG(A1.C1),MAX(A1.C1) FROM :Q207000 A1 GROUP BY A1.C0
3 - SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDEX(A2 "EMP_ID1") */ A1.C1 C0,A2."SAL" C1,A2."SAL" C2
另外一个具体参考:
General Information | ||||||||||||||||
Source | {ORACLE_HOME}/rdbms/admin/dbmsxpln.sql | |||||||||||||||
First Availability | 9.2 | |||||||||||||||
Constants |
| |||||||||||||||
Dependencies |
| |||||||||||||||
DISPLAY | ||||||||||||||||
Display the last plan explained | dbms_xplan.display( table_name VARCHAR2 DEFAULT 'PLAN_TABLE', statement_id VARCHAR2 DEFAULT NULL, format VARCHAR2 DEFAULT 'TYPICAL', filter_preds VARCHAR2 DEFAULT NULL) RETURN dbms_xplan_type_table PIPELINED; Format choices are: BASIC ..... displays minimum information TYPICAL ... displays most relevant information SERIAL .... like TYPICAL but without parallel information ALL ....... displays all information Follow the link to dbms_stats.gather_system_statistics for information on CPU costing. | |||||||||||||||
EXPLAIN PLAN FOR SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id; set pagesize 25 set linesize 121 SELECT * FROM TABLE(dbms_xplan.display); | ||||||||||||||||
Display a specific plan by name | EXPLAIN PLAN SET STATEMENT_ID = 'abc' FOR SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id; set pagesize 25 set linesize 121 SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','BASIC')); SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','TYPICAL')); SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL')); | |||||||||||||||
Using A View To Display The DBMS_XPLAN Output | CREATE OR REPLACE VIEW plan_view AS SELECT * FROM TABLE(dbms_xplan.display); SELECT * FROM plan_view; | |||||||||||||||
Predicate Display | EXPLAIN PLAN FOR SELECT a.program_id, b.line_number FROM airplanes a, airplanes b WHERE a.program_id = b.program_id AND a.line_number = b.line_number; SELECT * FROM TABLE(dbms_xplan.display); EXPLAIN PLAN FOR SELECT a.program_id, b.line_number FROM airplanes a, airplanes b WHERE a.program_id = b.program_id AND and a.line_number = b.line_number AND a.program_id = '777'; SELECT * FROM TABLE(dbms_xplan.display); | |||||||||||||||
DISPLAY_AWR | ||||||||||||||||
Format and display the contents of the execution plan of a stored SQL statement in the AWR | dbms_xplan.display_awr(sql_id VARCHAR2, plan_hash_value INTEGER DEFAULT NUKK, db_id INTEGER DEFAULT NULL, format VARCHAR2 DEFAULT 'TYPICAL') RETURN dbms_xplan_type_table PIPELINED; | |||||||||||||||
conn / as sysdba GRANT SELECT ON dba_hist_sql_plan TO uwclass; GRANT SELECT ON dba_hist_sqltext TO uwclass; GRANT SELECT ON v_$database TO uwclass; conn uwclass/uwclass desc dba_hist_sql_plan SELECT MAX(io_cost) FROM dba_hist_sql_plan; SELECT sql_id FROM dba_hist_sql_plan WHERE io_cost = 142775; SELECT * FROM TABLE(dbms_xplan.display_awr('24033vh7b098h')); or SELECT /* XPLAN_CURSOR */ DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id; SELECT tf.* FROM dba_hist_sqltext ht, TABLE(dbms_xplan.display_awr(ht.sql_id,NULL,NULL, 'ALL')) tf WHERE ht.sql_text LIKE '%XPLAN_CURSOR%'; | ||||||||||||||||
DISPLAY_CURSOR | ||||||||||||||||
Display from GV$SQL_PLAN (or GV$SQL_PLAN_STATISTICS_ALL). Formats and display the contents of the execution plan of any loaded cursor | dbms_xplan.display_cursor( sql_id VARCHAR2 DEFAULT NULL, cursor_child_no INTEGER DEFAULT 0, format VARCHAR2 DEFAULT 'TYPICAL') RETURN dbms_xplan_type_table PIPELINED; | |||||||||||||||
conn / as sysdba GRANT SELECT ON v_$sql_plan TO uwclass; GRANT SELECT ON gv_$sql TO uwclass; GRANT SELECT ON v_$session TO uwclass; conn uwclass/uwclass SELECT COUNT(*) FROM plan_table; -- most recent cursor SELECT * FROM TABLE(dbms_xplan.display_cursor); -- named statement SELECT /* XPLAN_CURSOR */ DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id; SELECT sql_id, child_number FROM gv$sql WHERE sql_text LIKE '%XPLAN_CURSOR%'; SELECT * FROM TABLE(dbms_xplan.display_cursor('cpm9ss48qd32f', 0)); SELECT * FROM TABLE(dbms_xplan.display_cursor('dpcugg8dz3y5k')); or SELECT t.* FROM gv$sql s, TABLE(dbms_xplan.display_cursor(s.sql_id, s.child_number)) t WHERE sql_text LIKE '%XPLAN_CURSOR%'; | ||||||||||||||||
DISPLAY_SQLSET | ||||||||||||||||
Format and display the contents of the execution plan of statements stored in a SQL tuning set | dbms_xplan.display_sqlset( sqlset_name VARCHAR2, sql_id VARCHAR2, plan_hash_value INTEGER DEFAULT NULL, format VARCHAR2 DEFAULT 'TYPICAL', sqlset_owner VARCHAR2 DEFAULT NULL) RETURN dbms_xplan_type_table PIPELINED; | |||||||||||||||
conn / as sysdba GRANT SELECT ON all_sqlset_statements TO uwclass; GRANT SELECT ON all_sqlset_plans TO uwclass; conn uwclass/uwclass -- create a SQL tuning set set linesize 121 SELECT s.snap_id SNAP_ID, TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT FROM dba_hist_snapshot s, dba_hist_database_instance di WHERE di.dbid = s.dbid AND di.instance_number = s.instance_number AND di.startup_time = s.startup_time ORDER BY snap_id; DECLARE l_cursor dbms_sqltune.sqlset_cursor; x VARCHAR2(30); BEGIN -- create a sqlset dbms_sqltune.create_sqlset('UW Set', 'Test Set'); -- load the sqlset OPEN l_cursor FOR SELECT VALUE(p) FROM TABLE(dbms_sqltune.select_workload_repository( 15782,15792,NULL,NULL,NULL,NULL,NULL,NULL,10)) p; dbms_sqltune.load_sqlset(sqlset_name => 'UW Set', populate_cursor => l_cursor); -- create a tuning task from the sqlset x := dbms_sqltune.create_tuning_task(sqlset_name=>'UW Set'); -- run the tuning task dbms_sqltune.execute_tuning_task(x); END; / SELECT sql_id, plan_hash_value FROM TABLE(dbms_sqltune.select_sqlset ('UW Set')); desc all_sqlset_statements SELECT sqlset_name, sqlset_owner, sqlset_id, sql_id, plan_hash_value FROM all_sqlset_statements; desc all_sqlset_plans SELECT sqlset_name, sqlset_owner, sqlset_id, sql_id, plan_hash_value FROM all_sqlset_plans; SELECT sql_id, plan_hash_value FROM TABLE(dbms_sqltune.select_sqlset ('UW Set')); /* display the execution plan for the SQL statement associated with SQL ID '6hwjmjgrpsuaa' and PLAN HASH 2721822575 in the SQL Tuning Set called 'OLTP_optimization_0405" */ SELECT * FROM TABLE(dbms_xplan.display_sqlset( 'UW Set','6hwjmjgrpsuaa', 2721822575)); /* To display all execution plans of the SQL ID 'atfwcg8anrykp' stored in the SQL tuning set */ SELECT * FROM TABLE(dbms_xplan.display_sqlset('UW Set', 'dwssdqx28tzf5')); /* To display runtime statistics for the SQL statement included in the preceding statement */ SELECT * FROM TABLE(dbms_xplan.display_sqlset( 'UW Set', 'dwssdqx28tzf5', NULL, 'ALLSTATS LAST')); | ||||||||||||||||
PREPARE_RECORDS | ||||||||||||||||
Private procedure: used internally | dbms_xplan.repare_records(plan_cur IN sys_refcursor, i_format_flags IN binary_integer) RETURN dbms_xplan_type_table PIPELINED; | |||||||||||||||
TBD | ||||||||||||||||
VALIDATE_FORMAT | ||||||||||||||||
Private function to validate the user format: used internally | dbms_xplan.validate_format( hasPlanStats IN BOOLEAN, format IN VARCHAR2, format_flags OUT BINARY_INTEGER) RETURN BOOLEAN; | |||||||||||||||
TBD |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14710393/viewspace-751762/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14710393/viewspace-751762/