In Part 1, we discussed
|
Displays the contents of the plan
table
|
|
|
Displays the contents of an
execution plan stored in the AWR
|
|
|
Displays the execution plan of any
cursor in the cursor cache
|
Here is the link Explain Plan : Part 1
Now we will check some hints and
|
Displays the contents of the plan table
in a variety of formats with CLOB output type
|
|
|
Displays one or more execution
plans for the specified SQL handle of a SQL plan baseline
|
|
|
Displays the execution plan of a
given statement stored in a SQL tuning set
|
DISPLAY_PLAN
Function
This table function displays the
contents of the plan table in a variety of formats with CLOB
output type.
Syntax
DBMS_XPLAN.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;
|
Parameter
|
Description
|
|
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
command. If NULL is specified it also defaults to PLAN_TABLE.
|
|
statement_id
|
Specifies the statement_id of the plan to be displayed. This parameter defaults to NULL, which is the default when the EXPLAIN PLAN command is executed without a set
statement_id clause.If no statement_id
is specified, the function shows you the plan of the most recent explained
statement.
|
|
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'
Can reference any column of the
table where the plan is stored and can contain any SQL construct (for
example, sub-query, function calls (see WARNING under
Usage Notes)
|
|
format
|
Controls the level of details for
the plan. It accepts four values:
For finer control on the display
output, the following keywords can be added to the above three standard
format options to customize their default behavior. Each keyword either
represents a logical group of plan table columns (such as PARTITION) or logical additions to the base plan table output (such
as PREDICATE). Format keywords must be separated by either a comma or
a space:
Format keywords can be prefixed by
the sign '-' to exclude the specified information. For example, '-PROJECTION' excludes projection information.
If the target plan table (see table_name parameter) also stores plan statistics columns (for
example, it is a table used to capture the content of the fixed view V$SQL_PLAN_STATISTICS_ALL), additional format keywords can be used to specify which
class of statistics to display when using the DISPLAY Function. These additional format keywords are IOSTATS, MEMSTATS, ALLSTATS and LAST (see the DISPLAY_CURSOR Function or the DISPLAY_SQLSET Function for a full description of these four keywords).
|
|
type
|
Output type, one of: 'TEXT', 'ACTIVE', 'HTML', or 'XML' (see Usage Notes regarding type ACTIVE). '
|
Return Values:
Returns the requested report as CLOB
Usage Notes
Active reports have a rich,
interactive user interface akin to that found in Enterprise Manager while not
requiring any EM installation. The report file built is in HTML format, so it
can be interpreted by most modern browsers. The code powering the active report
is downloaded transparently by the web browser when the report is first viewed,
hence viewing it requires outside connectivity.
WARNING:
Application developers should expose
the filter_preds parameter to end-users only after careful consideration
because this 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.
DISPLAY_SQL_PLAN_BASELINE
Function
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;
|
Parameter
|
Description
|
|
sql_handle
|
SQL statement handle. It
identifies a SQL statement whose plan(s) are to be displayed.
|
|
plan_name
|
Plan name. It identifies a
specific plan. Default NULL means all plans associated with identified SQL
statement are explained and displayed.
|
|
format
|
Format string determines what
information stored in the plan displayed. One of three format values
('BASIC', 'TYPICAL', 'ALL') can be used, each representing a common use case.
|
Return Values
A PL/SQL type table
Usage Notes
This procedure uses plan information
stored in the plan baseline to explain and display the plans.It is possible
that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan. A mismatch between stored plan_id and generated plan_id means that
it is a non-reproducible plan. Such a plan is deemed invalid and is bypassed by
the optimizer during SQL compilation.
Examples
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;
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;
|
Parameter
|
Description
|
|
sqlset_name
|
Name of the SQL Tuning Set
|
|
sql_id
|
Specifies the sql_id value for a
SQL statement having its plan stored in the SQL tuning set. You can find all
stored SQL statements by querying table function DBMS_SQLTUNE.SELECT_SQLSET
|
|
plan_hash_value
|
Optional parameter. Identifies a
specific stored execution plan for a SQL statement. If suppressed, all stored
execution plans are shown.
|
|
format
|
Controls the level of details for
the plan. It accepts four values:
|
|
|
For finer control on the display
output, the following keywords can be added to the above three standard
format options to customize their default behavior. Each keyword either
represents a logical group of plan table columns (such as PARTITION) or logical additions to the base plan table output (such
as PREDICATE). Format keywords must be separated by either a comma or
a space:
The following two formats are
deprecated but supported for backward compatibility:
Format keywords can be prefixed by
the sign '-' to exclude the specified information. For example, '-PROJECTION' excludes projection information.
|
|
sqlset_owner
|
The owner of the SQL tuning set.
The default is the current user.
|
Usage Notes
Here are some ways you might use
variations on the format parameter:
- 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.
Examples
To display the execution plan for
the SQL statement associated with SQL ID 'gwp663cqh5qbf'
and PLAN HASH 3693697075 in the SQL Tuning Set called 'OLTP_optimization_0405":
SELECT
* FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'OLTP_optimization_0405','gwp663cqh5qbf', 3693697075));
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','gwp663cqh5qbf'));
To display runtime statistics for
the SQL statement included in the preceding statement:
SELECT
* FROM table (
DBMS_XPLAN.DISPLAY_SQLSET(
'OLTP_optimization_0405',
'gwp663cqh5qbf', NULL, 'ALLSTATS LAST');
QB_NAME Hint
Sometimes the same table is included in a query multiple times, so it is hard to know which operation in the execution plan refers to which reference to the table. TheQB_NAME
solves this problem by allowing you to name, or alias, individual query blocks.
The alias information is displayed when the FORMAT
parameter of the DISPLAY%
functions is set to "ALL", or the " +ALIAS" value is added
to the FORMAT parameter in
10gR2 onwards.The following query references the same table twice, so we cannot easily tell from the execution plan which reference is which.
SELECT (SELECT COUNT(*) FROM emp WHERE job = 'SALESMAN') AS salesman_count,
(SELECT COUNT(*) FROM emp WHERE job = 'MANAGER') AS manager_count
FROM dual;
SET LINESIZE 100
SET PAGESIZE 50
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 8 | | |
|* 4 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Next, we add the
QB_NAME
hint to the subqueries, giving each a recognizable alias. When the correct
format is selected, the output from the DISPLAY% functions now includes a table
giving us the relevant alias for each operation.SELECT (SELECT /*+ QB_NAME(salesman) */ COUNT(*) FROM emp WHERE job = 'SALESMAN') AS salesman_count,
(SELECT /*+ QB_NAME(manager) */ COUNT(*) FROM emp WHERE job = 'MANAGER') AS manager_count
FROM dual;
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALL));
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 8 | | |
|* 4 | TABLE ACCESS FULL| EMP | 3 | 24 | 3 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SALESMAN
2 - SALESMAN / EMP@SALESMAN
3 - MANAGER
4 - MANAGER / EMP@MANAGER
5 - SEL$1 / DUAL@SEL$1
GATHER_PLAN_STATISTICS Hint
Using theGATHER_PLAN_STATISTICS
hint makes the optimizer gather the actual cardinalities in addition to the
expected cardinalities in the execution plan. This can then be reported by the DISPLAY_CURSOR function if the format is
set to 'ALLSTATS'.CONN scott/tiger
SELECT /*+ GATHER_PLAN_STATISTICS */
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';
SET LINESIZE 130
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 9 |
| 1 | NESTED LOOPS | | 1 | | 1 |00:00:00.01 | 9 |
| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 1 | 1 |00:00:00.01 | 7 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 |00:00:00.01 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------
Now the "Rows" column has been replaced by the original cardinality estimate (E-Rows) and the actual cardinality (A-Rows).
No comments:
Post a Comment