DBMS_XPLAN 包的解释和关键点
DBMS_XPLAN 包是 Oracle 数据库中一个重要的工具，它允许数据库管理员和开发人员以各种方式显示 SQL 语句的执行计划，这对于 SQL 优化和性能诊断至关重要。以下是主要函数及其描述：
用于显示执行计划的主要函数
- display
-
- 目的: 显示存储在指定计划表中的执行计划。
- 参数:
-
-
table_name(默认值'PLAN_TABLE'): 计划表的名称。statement_id(可选): 标识 SQL 语句的 ID。format(默认值'TYPICAL'): 输出计划的格式。filter_preds(可选): 用于过滤计划表内容的谓词。
-
- display_cursor
-
- 目的: 显示当前或最后执行的 SQL 语句的执行计划。
- 参数:
-
-
sql_id(可选): 指定 SQL 语句的sql_id。cursor_child_no(默认值0): 指定游标的子号。format(默认值'TYPICAL'): 输出计划的格式。
-
- display_awr
-
- 目的: 显示存储在 AWR（自动工作负载库）中的执行计划。
- 参数:
-
-
sql_id: 指定 SQL 语句的sql_id。plan_hash_value(可选): 指定特定的执行计划。db_id(可选): 指定特定数据库的 ID。format(默认值'TYPICAL'): 输出计划的格式。
-
- display_sqlset
-
- 目的: 显示存储在 SQL 调优集中 SQL 语句的执行计划。
- 参数:
-
-
sqlset_name: SQL 调优集的名称。sql_id: 指定 SQL 语句的sql_id。plan_hash_value(可选): 指定特定的执行计划。format(默认值'TYPICAL'): 输出计划的格式。sqlset_owner(可选): SQL 调优集的拥有者，默认是当前用户。
-
- display_sql_plan_baseline
-
- 目的: 显示 SQL 计划基线中的执行计划。
- 参数:
-
-
sql_handle(可选): 标识 SQL 语句的句柄。plan_name(可选): 指定特定计划的名称。format(默认值'TYPICAL'): 输出计划的格式。
-
内部使用的私有函数和过程
这些函数和过程用于内部用途，主要用于数据准备和格式验证，不需要公开文档。
- prepare_records: 准备记录的内部过程。
- validate_format: 验证用户格式的内部函数。
- format_size, format_number: 格式化数字和大小的函数。
- format_time_s: 格式化时间（秒）的函数。
- prepare_plan_xml_query: 为查询构建 XML 版本的辅助函数。
- build_plan_xml: 生成 XML 版本的执行计划。
- display_plan: 以 CLOB 格式返回执行计划。
比较执行计划的支持函数
这些函数用于比较不同执行计划，通常返回任务 ID，供后续报告生成使用。
- diff_plan_outline: 比较两个通过指定轮廓生成的 SQL 计划。
- diff_plan: 比较参考计划和目标计划。
- diff_plan_sql_baseline: 比较 SQL 计划基线中的两个计划。
- diff_plan_cursor: 比较从指定游标子号生成的两个 SQL 计划。
- diff_plan_awr: 比较通过指定计划哈希值生成的两个 SQL 计划。
- get_plandiff_report_xml: 构建差异报告的 XML 版本。
示例代码
显示存储在计划表中的执行计划
SET LINESIZE 150
SET PAGESIZE 2000
SELECT * FROM TABLE(dbms_xplan.display);显示当前或最后执行的 SQL 语句的执行计划
SET LINESIZE 150
SET PAGESIZE 2000
SELECT * FROM TABLE(dbms_xplan.display_cursor);显示存储在 AWR 中的执行计划
SET LINESIZE 150
SET PAGESIZE 2000
SELECT t.*
FROM dba_hist_sqltext ht,
TABLE(dbms_xplan.display_awr(ht.sql_id, NULL, NULL, '-PREDICATE +ALIAS')) t
WHERE ht.sql_text LIKE '%sAleS%';函数
create or replace package dbms_xplan AUTHID CURRENT_USER as
--- ------------------------------------------------------------------------
--- DBMS_XPLAN CONSTANTS SECTION
--- ------------------------------------------------------------------------
--- The following constants designate the flags returned in the bit vector
--- from the COMPARE_QUERY_PLANS function.
UNKNOWN_DIFF_CLASS CONSTANT NUMBER := POWER(2,31);
--- ------------------------------------------------------------------------
--- DBMS_XPLAN PUBLIC FUNCTIONS SECTION
--- ------------------------------------------------------------------------
---
--- OVERVIEW
---
--- This package defines several table functions which can be used to
--- display execution plans.
---
--- - DISPLAY is generally used to display the execution plan produced
--- by an EXPLAIN PLAN command; you can either display the most
--- recent explained statement, or the statement for a specific
--- statement id.
---
--- In addition, this table function can also be used to display
--- any plan (with or without statistics) stored in a table as
--- long as the columns of this table are named the same as
--- columns of the plan_table (or v$sql_plan_statistics_all if
--- statistics are included).
--- A predicate on the specified table can be used to select rows
--- of the plan to display.
---
--- - DISPLAY_CURSOR displays the execution plans for one or several
--- cursors in the shared SQL area, depending on a filter
--- criteria. It can display the plan for the last executed
--- statement, the current (if session is active) or last
--- executed statement (if session is inactive) of a specific
--- session, or all cursors matching an arbitrary criteria
--- defined via SQL. In addition to the explain plan, various
--- plan statistics (e.g. io, memory and timing) can be
--- reported (based on the v$sql_plan_statistics_all views).
---
--- Specific cursors are identified by SQL_ID and optionally a
--- SQL_CHILD_NUMBER.
---
--- The DEFAULT without any parameters shows the last executed
--- statement of the session.
---
--- NOTE: To use the DISPLAY_CURSOR functionality, the calling
--- user must have SELECT privilege on V$SQL_PLAN_STATISTICS_ALL,
--- V$SQL, and V$SQL_PLAN. By default, only the select_catalog
--- role has the SELECT privilege on these views.
---
--- - DISPLAY_AWR displays the execution plans for SQL statements stored in
--- the Automatic Workload Repository (AWR).
--- NOTE: To use the DISPLAY_AWR functionality, the calling user
--- must have SELECT prvilege on DBA_HIST_SQL_PLAN and
--- DBA_HIST_SQLTEXT. By default, select privilige for these
--- views is granted to the select_catalog role.
---
--- - DISPLAY_SQLSET displays the execution plans for SQL statements stored
--- in a SQL tuning set.
--- NOTE: To use the DISPLAY_SQLSET functionality, the calling
--- user must have SELECT prvilege on ALL_SQLSET_PLANS and
--- ALL_SQLSET_STATEMENTS. By default, select privilige for these
--- views is granted to the public role.
---
--- - DISPLAY_SQL_PLAN_BASELINE displays one or more execution plans for
--- the specified sql_handle of a SQL statement. If plan_name is
--- specified denoting a single plan then that plan is displayed.
--- The plan information stored in the SQL management base is
--- used to generate and display the plan. It is possible that
--- the stored plan id may not match up with the plan id of the
-- generated plan. A plan id mismatch means that the stored plan
--- is not reproducible. Such a plan is deemed invalid by the
--- optimizer and ignored when the corresponding SQL statement is
-- compiled and a cursor is built. When plan id mismatch occurs
--- a note saying 'the plan is invalid' is shown in the notes
--- section of the plan.
--- NOTE: To use DISPLAY_SQL_PLAN_BASELINE function, the calling
--- user must have SELECT prvilege on DBA_SQL_PLAN_BASELINES
--- view. By default, SELECT privilege on this view is granted
--- to the SELECT_CATALOG_ROLE.
---
---
--- For example:
--- To show the last explained statement
--- explain plan for select ename, deptno
--- from emp e, dept d
--- where e.deptno = d.deptno;
--- select * from table(dbms_xplan.display);
---
--- To show the last executed statement of your session
--- select * from table(dbms_xplan.display_cursor);
---
--- See more detailed examples below
---
--- SECURITY
---
--- The execution privilege on this package is granted to PUBLIC.
--- The display procedures of this package are run under the caller
--- security.
---
---
--- PROCEDURES AND FUNCTIONS
---
--- function display(table_name varchar2 default 'PLAN_TABLE',
--- statement_id varchar2 default null,
--- format varchar2 default 'TYPICAL',
--- filter_preds varchar2 default null)
---
--- - 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. If NULL is specified,
--- the default of 'PLAN_TABLE' will be taken into account.
--- The parameter is case insensitive.
---
--- - statement_id:
--- specifies the statement id of the plan to display. This
--- parameter defaults to NULL. If no statement_id is defined,
--- the most recent explained statement in <table_name> will
--- be displayed, assuming that the "filter_preds" parameter is
--- NULL (its default).
---
--- - format:
--- Determines what information stored in the plan will be
--- shown. The format string can use the following predefined
--- three formats, each representing a common use case:
---
--- 'BASIC': Display only the minimum set of information, 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. Excludes only PROJECTION,
--- ALIAS and REMOTE SQL information (see below).
---
--- 'ALL': Maximum user level, like typical with additional
--- informations (PROJECTION, ALIAS and information about
--- REMOTE SQL if the operation is distributed).
---
--- For finer control on the display output, the following keywords
--- can be added to the above three standard format to customize their
--- default behavior. Each keyword either represents a logical group
--- of plan table columns (e.g. PARTITION) or logical additions to the
--- base plan table output (e.g. PREDICATE). Format keywords must
--- be separated by either a comma or a space:
---
--- ROWS: if relevant, shows number of rows estimated by the optimizer
---
--- BYTES: if relevant, shows number of bytes estimated by the
--- optimizer
---
--- COST: if relevant, shows optimizer cost information
---
--- PARTITION: If relevant, shows partition pruning information
---
--- PARALLEL: If relevant, shows PX information (distribution method
--- and table queue information)
---
--- PREDICATE: If relevant, shows the predicate section
---
--- PROJECTION: If relevant, shows the projection section
---
--- ALIAS: If relevant, shows the "Query Block Name / Object Alias"
--- section
---
--- REMOTE: If relevant, shows the information for distributed query
--- (e.g. remote from serial distribution and remote SQL)
---
--- NOTE: If relevant, shows the note section of the explain plan.
---
--- Format keywords can be prefixed by the sign '-' to exclude the
--- specified information. For example, '-PROJECTION' exclude
--- projection information.
---
--- Finally, if the target plan table (see "table_name" parameter) also
--- stores plan statistics columns (e.g. it is a table used to capture
--- the content of the fixed view v$sql_plan_statistics_all), then
--- additional format keywords can be used to specify which class of
--- statistics to display. These additionnal format keywords are IOSTATS,
--- MEMSTATS, ALLSTATS and LAST described along with the display_cursor()
--- table function (see below).
---
--- Example:
--- - use 'ALL -PROJECTION -NOTE' to display everything except the
--- projection and note sections.
---
--- - use 'TYPICAL PROJECTION' to display using the typical format
--- with the additional projection section (which is normally excluded
--- under the typical format). Since typical is default, using
--- simply 'PROJECTION' is equivalent.
---
--- - use '-BYTES -COST -PREDICATE' to display using the typical
--- format but excluding optimizer cost and byte estimates
--- as well as the predicate section.
---
--- - use 'BASIC ROWS' to display basic information with the
--- additional number of rows estimated by the optimizer.
---
---
--- - filter_preds: SQL filter predicate(s) to restrict the set of rows
--- selected from the table where the plan is stored. When
--- value is NULL (the default), the plan displayed
--- corresponds to the last executed explain plan.
---
--- For example:
---
--- filter_preds=>'plan_id = 10'
---
--- "filter_preds" can reference any column of the table
--- where the plan is stored and can contain any SQL
--- construct (e.g. sub-query, function calls...).
---
--- WARNING: Application developers should expose this
--- parameter to end-users only after careful
--- consideration since it could expose the application
--- to SQL injection. Indeed, "filter_preds" can
--- potentially reference any table or execute any server
--- function for which the database user invoking the
--- table function has privileges.
---
--- ------------------------------------------------------------------------
--- function display_cursor(sql_id varchar2 default null,
--- cursor_child_no integer default 0,
--- format varchar2 default 'TYPICAL')
---
--- - sql_id:
--- specifies the sql_id value for a specific SQL statement, as
--- shown in V$SQL.SQL_ID, V$SESSION.SQL_ID, or
--- V$SESSION.PREV_SQL_ID. If no sql_id is specified, the last
--- executed statement of the current session is shown.
---
--- - cursor_child_no:
--- specifies the child number for a specific sql cursor, as shown in
--- V$SQL.CHILD_NUMBER or in V$SESSION.SQL_CHILD_NUMBER,
--- V$SESSION.PREV_CHILD_NUMBER. This input parameter is only
--- considered when sql_id is set.
---
--- If not specified, all child cursors for the specified sql_id are
--- displayed.
---
--- - format:
--- The format string has the same meaning as that for the regular
--- display() table function (see format description above). In
--- addition, the following four format keywords are introduced
--- to support the various plan statistics columns available
--- in v$sql_plan_statistics_all.
---
--- These keywords can also be used by the display() table function
--- assuming that the specified table has the same statistics columns
--- available in v$sql_plan_statistics_all.
---
--- IOSTATS: Assuming that basic plan statistics are
--- collected when SQL statements are executed (either by
--- using the gather_plan_statistics hint or by setting the
--- parameter statistics_level to ALL), this format will show
--- IO statistics for all (or only for the last as shown below)
--- executions of the cursor.
---
--- MEMSTATS: Assuming that PGA memory management is enabled (i.e
--- pga_aggregate_target parameter is set to a non 0 value),
--- this format allows to display memory management
--- statistics (e.g. execution mode of the operator, how
--- much memory was used, number of bytes spilled to
--- disk, ...). These statistics only apply to memory
--- intensive operations like hash-joins, sort or some bitmap
--- operators.
---
--- ALLSTATS: A shortcut for 'IOSTATS MEMSTATS'
---
--- LAST: By default, plan statistics are shown for all executions of
--- the cursor. The keyword LAST can be specified to see only
--- the statistics for the last execution.
---
---
--- Also, the following two formats are still supported for backward
--- compatibility:
---
--- 'RUNSTATS_TOT': Same as 'IOSTATS', i.e. displays IO statistics
--- for all executions of the specified cursor.
--- 'RUNSTATS_LAST': Same as 'IOSTATS LAST', i.e. displays the runtime
--- statistics for the last execution of the cursor.
---
---
--- PRIVILEGES:
--- - To use the DISPLAY_CURSOR functionality, the calling
--- user must have SELECT privilege on V$SQL_PLAN_STATISTICS_ALL,
--- V$SQL, and V$SQL_PLAN, otherwise it will show an appropriate
--- error message.
---
--- - Unless used in DEFAULT mode to display the last executed
--- statement, all internal SQL statements of this package and
--- the calling SQL statement using this table function will be
--- suppressed.
---
--- ------------------------------------------------------------------------
--- function display_awr(sql_id varchar2,
--- plan_hash_value integer default null,
--- db_id integer default null,
--- format varchar2 default 'TYPICAL')
---
--- - sql_id:
--- specifies the sql_id value for a SQL statement having its plan(s)
--- stored in the AWR. You can find all stored SQL statements by
--- querying DBA_HIST_SQL_PLAN.
---
--- - plan_hash_value:
--- identifies a specific stored execution plan for a SQL statement.
--- Optional parameter. If suppressed, all stored execution plans are
--- shown.
---
--- - db_id:
--- identifies the plans for a specific dabatase. If this parameter is
--- omitted, it will be defaulted to the local database identifier.
---
--- - format:
--- The format string has the same meaning as that for the regular
--- display() table function (see format description above).
---
--- ------------------------------------------------------------------------
--- function display_sqlset(sqlset_name varchar2,
--- sql_id varchar2,
--- plan_hash_value integer default null,
--- format varchar2 default 'TYPICAL',
--- sqlset_owner varchar2 default null)
---
--- - sqlset_name:
--- specified the name of the SQL tuning set.
---
--- - sql_id:
--- specifies the sql_id value for a SQL statement having its plan(s)
--- stored in the SQL tuning set. You can find all stored SQL
--- statements by querying USER/DBA/ALL_SQLSET_PLANS or table function
--- SELECT_SQLSET from package dbms_sqltune.
---
--- - plan_hash_value:
--- identifies a specific stored execution plan for a SQL statement.
--- Optional parameter. If suppressed, all stored execution plans are
--- shown.
---
--- - format:
--- The format string has the same meaning as that for the regular
--- display() table function (see format description above).
---
--- - sqlset_owner:
--- Specifies the owner of the SQL tuning set. The default is the
--- name of the current user.
---
--- ------------------------------------------------------------------------
--- function display_sql_plan_baseline(
--- sql_handle varchar2 default NULL,
--- plan_name varchar2 default NULL,
--- format varchar2 default 'TYPICAL')
---
--- - sql_handle:
--- SQL statement handle. It identifies the SQL statement whose plans
--- are to be explained and displayed. If NULL then PLAN_NAME must be
--- specified.
--- You can find SQL plan baselines created for various SQL statements
--- by querying DBA_SQL_PLAN_BASELINES catalog view.
---
--- - plan_name:
--- Plan name. It identifies a specific plan to be explained and
--- displayed. Default NULL means all plans associated with identified
--- SQL statement to be explained and displayed. If NULL then
--- sql_handle must be specified.
---
--- - format:
--- The format string has the same meaning as that for the regular
--- display() table function (see format description above).
---
--- ------------------------------------------------------------------------
--- Examples DBMS_XPLAN.DISPLAY():
---
--- 1/ display the last explain plan stored in the plan table:
---
--- set linesize 150
--- set pagesize 2000
--- select * from table(dbms_xplan.display);
---
---
--- 2/ display from the plan table "my_plan_table":
---
--- set linesize 150
--- set pagesize 2000
--- select * from table(dbms_xplan.display('my_plan_table'));
---
---
--- 3/ display minimum plan table:
---
--- set linesize 150
--- set pagesize 2000
--- select * from table(dbms_xplan.display(null, null,'basic'));
---
---
--- 4/ display all information in plan table, excluding projection:
---
--- set linesize 150
--- set pagesize 2000
--- select * from table(dbms_xplan.display(null, null,
--- 'all -projection'));
---
---
--- 5/ display the plan whose statement_id is 'foo':
---
--- set linesize 150
--- set pagesize 2000
--- select * from table(dbms_xplan.display('plan_table', 'foo'));
---
---
--- 6/ display statpack plan for hash_value=76725 and snap_id=245
---
--- set linesize 150
--- set pagesize 2000
--- select * from table(dbms_xplan.display('stats$sql_plan', null,
--- 'all', 'hash_value=76725 and snap_id=245'));
---
--- ------------------------------------------------------------------------
--- Examples DBMS_XPLAN.DISPLAY_CURSOR():
---
--- 1/ display the currently or last executed statement
--- (this will also show the usage of this package)
---
--- set linesize 150
--- set pagesize 2000
--- select * from table(dbms_xplan.display_cursor);
---
---
--- 2/ display the currently or last executed statement of session id 9
--- (it will return 'no rows selected' for any SQL statement using
--- this package)
---
--- - Identify the sql_id and the child_number in
--- a separate SQL statement and use them as parameters for
--- DISPLAY_CUSRSOR()
---
--- SQL> select prev_sql_id, prev_child_number
--- from v$session where sid=9;
---
--- PREV_SQL_ID PREV_CHILD_NUMBER
--- ------------- -----------------
--- f98t6zufy04g5 0
---
--- set linesize 150
--- set pagesize 2000
--- select *
--- from table(dbms_xplan.display_cursor('f98t6zufy04g5', 0));
---
--- - Alternatively, you can combine the two statements into one
---
--- set linesize 150
--- set pagesize 2000
--- select t.*
--- from v$session s,
--- table(dbms_xplan.display_cursor(s.prev_sql_id,
--- s.prev_child_number)) t
--- where s.sid=9;
---
--- NOTE: the table deriving the input parameters for
--- DBMS_XPLAN.DISPLAY_CURSOR() must be the FIRST (left-side)
--- table(s) in the select statement relative to the table function
---
---
--- 3/ display all cursors containing the case sensisitve string 'FoOoO',
--- excluding SQL parsed by SYS
---
--- set linesize 150
--- set pagesize 2000
--- select t.*
--- from v$sql s,
--- table(dbms_xplan.display_cursor(s.sql_id,
--- s.child_number)) t
--- where s.sql_text like '%FoOoO%' and s.parsing_user_id <> 0;
---
---
--- 4/ display all information about all cursors containing the case
--- insensitive string 'FOO', including SQL parsed by SYS
---
--- set linesize 150
--- set pagesize 2000
--- select t.*
--- from v$sql s,
--- table(dbms_xplan.display_cursor(s.sql_id,
--- s.child_number, 'ALL')) t
--- where upper(s.sql_text) like '%FOO%';
---
---
--- 5/ display the last executed runtime statistics for all cursors
--- containing the case insensitive string 'sales', including SQL
--- parsed by SYS
---
--- set linesize 150
--- set pagesize 2000
--- select t.*
--- from v$sql s,
--- table(dbms_xplan.display_cursor(s.sql_id, s.child_number,
--- 'ALLSTATS LAST')) t
--- where lower(s.sql_text) like '%sales%';
---
---
--- 6/ display the aggregated runtime statistics for all cursors containing
--- the case sensitive string 'sAleS' and were parsed by user SH
---
--- set linesize 150
--- set pagesize 2000
--- select t.*
--- from v$sql s, dba_users u,
--- table(dbms_xplan.display_cursor(s.sql_id, s.child_number,
--- 'RUNSTATS_TOT')) t
--- where s.sql_text like '%sAleS%'
--- and u.user_id=s.parsing_user_id
--- and u.username='SH';
---
--- ------------------------------------------------------------------------
--- Examples DBMS_XPLAN.DISPLAY_AWR():
---
--- 1/ display all stored plans in the AWR containing
--- the case sensitive string 'sAleS'. Don't display predicate
--- information but add the query block name / alias section.
---
--- set linesize 150
--- set pagesize 2000
--- select t.*
--- from dba_hist_sqltext ht,
--- table(dbms_xplan.display_awr(ht.sql_id, null, null,
--- '-PREDICATE +ALIAS')) t
--- where ht.sql_text like '%sAleS%';
---
--- NOTE: the table deriving the input parameters for
--- DBMS_XPLAN.DISPLAY_AWR() must be the FIRST (left-side)
--- table(s) in the select statement relative to the table
--- function.
---
--- ------------------------------------------------------------------------
--- Examples DBMS_XPLAN.DISPLAY_SQLSET():
---
--- 1/ display all stored plans for a given statement in the SQL tuning set
--- named 'my_sts' owner by the current user (the caller).
---
--- set linesize 150
--- set pagesize 2000
--- select *
--- from table(dbms_xplan.display_sqlset('my_sts',
--- 'gcfysssf6hykh',
--- null,
--- 'ALL -NOTE -PROJECTION')) t
---
--- ------------------------------------------------------------------------
--- Examples DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE():
---
--- 1/ display all plans of a SQL statement identified by the sql handle
--- 'SYS_SQL_b1d49f6074ab95af' using TYPICAL format.
---
--- set linesize 150
--- set pagesize 2000
--- select t.*
--- from table(dbms_xplan.display_sql_plan_baseline(
--- 'SYS_SQL_b1d49f6074ab95af')) t;
---
--- 2/ 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;
---
--- NOTE: the table deriving the input parameters for
--- DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE() must be the first
--- (left-side) table in the select statement relative to the
--- table function.
---
--- -------------------------------------------------------------------------
-- display from PLAN_TABLE
function 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;
-- display from V$SQL_PLAN (or V$SQL_PLAN_STATISTICS_ALL)
function display_cursor(sql_id varchar2 default null,
cursor_child_no integer default 0,
format varchar2 default 'TYPICAL')
return dbms_xplan_type_table
pipelined;
-- display from AWR
function display_awr(sql_id varchar2,
plan_hash_value integer default null,
db_id integer default null,
format varchar2 default 'TYPICAL')
return dbms_xplan_type_table
pipelined;
-- display from SQL tuning set
function 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;
-- display from SQL plan baseline
function display_sql_plan_baseline(sql_handle varchar2 default null,
plan_name varchar2 default null,
format varchar2 default 'TYPICAL')
return dbms_xplan_type_table
pipelined;
----------------------------------------------------------------------------
-- ---------------------------------------------------------------------- --
-- --
-- The folloing section of this package contains functions and procedures --
-- which are for INTERNAL use ONLY. PLEASE DO NO DOCUMENT THEM. --
-- --
-- ---------------------------------------------------------------------- --
----------------------------------------------------------------------------
-- private procedure, used internally
function prepare_records(plan_cur IN sys_refcursor,
i_format_flags IN binary_integer)
return dbms_xplan_type_table
pipelined;
-- private function to validate the user format (used internally)
function validate_format(hasPlanStats IN boolean,
format IN VARCHAR2,
format_flags OUT BINARY_INTEGER)
return boolean;
FUNCTION format_size(num number)
RETURN varchar2;
FUNCTION format_number(num number)
RETURN varchar2;
FUNCTION format_size2(num number)
RETURN varchar2;
FUNCTION format_number2(num number)
RETURN varchar2;
--
-- formats a number representing time in seconds using the format HH:MM:SS.
-- This function is internal to this package
--
function format_time_s(num number)
return varchar2;
--
-- This is a helper function to build the XML version of the text of the
-- select query that is run before the display display function to retrieve
-- and display the execution plan of a SQL.
-- All this function does it to wrap a given query, used to fetch a plan, by
-- XML construct. The goal is to maintain ONE and SINGLE version of the XML
-- format we use for the plan table.
--
-- This function is also called by prvtspai.sql in sqltune directory.
-- table_query : query to fetch plan from a plan table
--
FUNCTION prepare_plan_xml_query(
plan_query IN VARCHAR2) -- query to fetch plan table
RETURN VARCHAR2;
--
-- This function builds the xml version of an explain plan.
-- The function queries the caller specified plan table and format the
-- resulting plan lines into XML before returning them to the caller.
--
-- table_name : name of the table or view that stores the plan
-- statement_id: identifier of the sql statement in the plan table
-- plan_id : identifier of the sql plan in the plan table. Currently
-- used by sql replay only. SQL replay is used to produce plans
-- for SQL stored in sql tuning set using plan_ids and so
-- we need to be able to share the query we use to get the plans.
-- format : format of the plan output. See description in
-- function display
-- filter_preds: predicate to filter the content of the plan table
-- plan_tag : caller specified name of the root element in the plan xml
-- tree. by default it is set to 'xplan'
-- report_ref : optional report reference. Needed only to generate
-- xml of the servelet.
--
function build_plan_xml(
table_name in varchar2 default 'PLAN_TABLE',
statement_id in varchar2 default NULL,
plan_id in number default NULL,
format in varchar2 default 'TYPICAL',
filter_preds in varchar2 default NULL,
plan_tag in varchar2 default 'plan',
report_ref in varchar2 default NULL)
return xmltype;
--
-- This function returns an explain plan in a CLOB format.
-- The function queries the caller specified plan table, generate the
-- resulting plan lines into XML and then calls the XML reporting framework
-- the produce and return the plan as a CLOB.
--
-- table_name : name of the table or view that stores the plan
-- statement_id: identifier of the sql statement in the plan table
-- format : format of the plan output. See description in
-- function display
-- filter_preds: predicate to filter the content of the plan table
-- type : type of output. Possible values are:
-- TEXT (default), HTML, ACTIVE, or XML.
--
function display_plan(
table_name in varchar2 default 'PLAN_TABLE',
statement_id in varchar2 default NULL,
format in varchar2 default 'TYPICAL',
filter_preds in varchar2 default NULL,
type in varchar2 default 'TEXT')
return clob;
----------------------------- diff_plan_outline ------------------------------
--
-- This function compares two sql plans generated by the given outlines
-- The job is done via a SQLDiag task and the function returns the task_id
--
-- PARAMETERS:
-- sql_text (IN) - text of the SQL statement
-- outline1 (IN) - outline - for base plan
-- outline2 (IN) - outline - for target plan
-- user_name (IN) - the parsing schema name
-- default to current user
--
-- RETURN:
-- task_id: can be used to retrieve the report of findings later
------------------------------------------------------------------------------
function diff_plan_outline(
sql_text in clob,
outline1 in clob,
outline2 in clob,
user_name in varchar2 := NULL)
return varchar2;
----------------------------- diff_plan -------------------------------------
--
-- This function compares two sql plans
-- reference plan: implicitly defined
-- target plan: a plan generated by the given outline
--
-- The job is done via a SQLDiag task and the function returns the task_id
--
-- PARAMETERS:
-- sql_text (IN) - text of the SQL statement
-- outline (IN) - used to generate the target plan
-- user_name (IN) - the parsing schema name
-- default to current user
--
-- RETURN:
-- task_id: can be used to retrieve the report of findings later
------------------------------------------------------------------------------
function diff_plan(
sql_text in clob,
outline in clob,
user_name in varchar2 := NULL)
return varchar2;
----------------------------- diff_plan_sql_baseline -------------------------
--
-- This function compares two given sql plans (specified via plan_names)
-- The job is done via a SQLDiag task and the function returns the task_id
--
-- PARAMETERS:
-- baseline_plan_name1 (IN) - plan name - base
-- baseline_plan_name2 (IN) - plan name - target
--
-- RETURN:
-- task_id: can be used to retrieve the report of findings later
------------------------------------------------------------------------------
function diff_plan_sql_baseline(
baseline_plan_name1 in varchar2,
baseline_plan_name2 in varchar2)
return varchar2;
----------------------------- diff_plan_cursor -------------------------------
--
-- This function compares two sql plans derived from the given cursor child #
-- The job is done via a SQLDiag task and the function returns the task_id
--
-- PARAMETERS:
-- sql_id (IN) - sql id to specify a SQL statement
-- cursor_child_num1 (IN) - child number - for base plan
-- cursor_child_num2 (IN) - child number - for target plan
--
-- RETURN:
-- task_id: can be used to retrieve the report of findings later
------------------------------------------------------------------------------
function diff_plan_cursor(
sql_id IN VARCHAR2,
cursor_child_num1 IN NUMBER,
cursor_child_num2 IN NUMBER)
return varchar2;
----------------------------- diff_plan_awr ----------------------------------
--
-- This function compares two sql plans specified by the given plan hash ids
-- The job is done via a SQLDiag task and the function returns the task_id
--
-- PARAMETERS:
-- sql_id (IN) - sql id to specify a SQL statement
-- plan_hash_value1 (IN) - base plan
-- plan_hash_value1 (IN) - target plan
--
-- RETURN:
-- task_id: can be used to retrieve the report of findings later
------------------------------------------------------------------------------
function diff_plan_awr(
sql_id IN VARCHAR2,
plan_hash_value1 IN NUMBER,
plan_hash_value2 IN NUMBER)
return varchar2;
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
-- ------------------------------------------ --
-- PLAN DIFF SUPPORT FUNCTIONS --
-- ------------------------------------------ --
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
------------------------------ get_report_xml --------------------------------
-- NAME:
-- get_report_xml
--
-- DESCRIPTION:
-- This function builds the entire report in XML.
--
-- PARAMETERS:
-- report_ref (IN) - the report reference string that
-- identifies this report
-- tid (IN) - task ID
-- method (IN) - method of comparing, eg, 'outline'
-- RETURN:
-- the report in XML
------------------------------------------------------------------------------
FUNCTION get_plandiff_report_xml(
report_ref IN VARCHAR2 := NULL,
tid IN NUMBER,
method IN VARCHAR2)
RETURN XMLTYPE;
end dbms_xplan;
本站资源均来自互联网，仅供研究学习，禁止违法使用和商用，产生法律纠纷本站概不负责！如果侵犯了您的权益请与我们联系！
转载请注明出处： 免费源码网-免费的源码资源网站 » Oracle DBMS_XPLAN包
发表评论 取消回复