用DBMS_XPLAN來展示執行計劃

luckyfriends發表於2012-12-27
轉自：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
 
另外一個具體參考:
General Information
Source{ORACLE_HOME}/rdbms/admin/dbmsxpln.sql
First Availability9.2
Constants
NameData TypeValue
UNKNOWN_DIFF_CLASSNUMBERPOWER(2,31)
Dependencies
DBMS_ASSERTDBMS_XPLAN_LIBGV$DATABASE
DBMS_SQLDBMS_XPLAN_TYPEPLAN_VIEW
DBMS_SQLTUNE_INTERNALDBMS_XPLAN_TYPE_TABLEPLITBLM
DBMS_STATS_LIBEXTRACTXMLSEQUENCE
DBMS_SWRF_REPORT_INTERNALEXTRACTVALUEXMLTYPE
DISPLAY
Display the last plan explaineddbms_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 nameEXPLAIN 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 OutputCREATE 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 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 internallydbms_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 internallydbms_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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章