DBMS_XPLAN
The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN , from AWR , from Cursor ,from SQL Tuning Sets, from SLQ Baselines in predefined or modified formats.
The DBMS_XPLAN package supplies below table functions.
These functions are listed below:
DISPLAY - to format and display the contents of a plan table.
DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.
DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor.
DISPLAY_PLAN - to format and display the contents of a plan table in CLOB format.
COMPARE_PLANS - Compares each plan in a list with a reference plan and returns the report.
DISPLAY_SQL_PLAN_BASELINE - to display one or more execution plans for the SQL statement identified by SQL handle
DISPLAY_SQLSET - to format and display the contents of the execution plan of statements stored in a SQL tuning set.
Let we elaborate Every Functions with example as below.
1=> DISPLAY Function
This table function displays the contents of the plan table.
You can apply a predicate on the specified table to select rows of the plan to display.
DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL);
Description of Parameters
table_name -> Specifies the table name where the plan is stored. This parameter defaults to PLAN_TABLE.
statement_id -> Specifies the statement_id of the plan to be displayed.
This parameter defaults to NULL,If no statement_id is specified,the function shows you the plan of the most recent explained statement.
format-> Controls the level of details for the plan. It accepts the following values:
BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.
TYPICAL: This is the default.
SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.
ALL: Maximum user level.
ROWS,BYTES,COST,PARTITION,PARALLEL,PREDICATE,PROJECTION,ALIAS,REMOTE,NOTE. Additional format keywords are IOSTATS, MEMSTATS, ALLSTATS, and LAST.
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'
Examples
=> Using Default as Typical format
SQL> EXPLAIN PLAN FOR SELECT * FROM EMPLOYEE_DATA where EMP_NO >550;
Explained.
SQL> SET LINES 400
SQL> SET PAGES 1000
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
---------------------------------
Plan hash value: 1598958544
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4448 | 35584 | 606 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 4448 | 35584 | 606 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 4448 | 35584 | 606 (0)| 00:00:01 | 1 | 4 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| EMPLOYEE_DATA | 4448 | 35584 | 606 (0)| 00:00:01 | 1 | 4 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("EMP_NO">550)
Note
-----
- Degree of Parallelism is 2 because of table property
20 rows selected.
SQL>
=> Using format as ALL.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=> 'ALL'));
PLAN_TABLE_OUTPUT
-------------------------------
Plan hash value: 1598958544
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4448 | 35584 | 606 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 4448 | 35584 | 606 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 4448 | 35584 | 606 (0)| 00:00:01 | 1 | 4 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| EMPLOYEE_DATA | 4448 | 35584 | 606 (0)| 00:00:01 | 1 | 4 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / EMPLOYEE_DATA@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("EMP_NO">550)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP_NO"[NUMBER,22], "EMPLOYEE_DATA"."EMP_AGE"[NUMBER,22]
2 - (#keys=0) "EMP_NO"[NUMBER,22], "EMPLOYEE_DATA"."EMP_AGE"[NUMBER,22]
3 - (rowset=256) "EMP_NO"[NUMBER,22], "EMPLOYEE_DATA"."EMP_AGE"[NUMBER,22]
4 - (rowset=256) "EMP_NO"[NUMBER,22], "EMPLOYEE_DATA"."EMP_AGE"[NUMBER,22]
Note
-----
- Degree of Parallelism is 2 because of table property
34 rows selected.
SQL>
=>Removing Notes section from ALL format clause.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=> 'ALL -NOTE'));
PLAN_TABLE_OUTPUT
------------------------------------
Plan hash value: 1598958544
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4448 | 35584 | 606 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 4448 | 35584 | 606 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 4448 | 35584 | 606 (0)| 00:00:01 | 1 | 4 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| EMPLOYEE_DATA | 4448 | 35584 | 606 (0)| 00:00:01 | 1 | 4 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / EMPLOYEE_DATA@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("EMP_NO">550)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "EMP_NO"[NUMBER,22], "EMPLOYEE_DATA"."EMP_AGE"[NUMBER,22]
2 - (#keys=0) "EMP_NO"[NUMBER,22], "EMPLOYEE_DATA"."EMP_AGE"[NUMBER,22]
3 - (rowset=256) "EMP_NO"[NUMBER,22], "EMPLOYEE_DATA"."EMP_AGE"[NUMBER,22]
4 - (rowset=256) "EMP_NO"[NUMBER,22], "EMPLOYEE_DATA"."EMP_AGE"[NUMBER,22]
30 rows selected.
SQL>
=>Using Basic format with only ROWS,COST clause
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format=> 'BASIC +ROWS +COST'));
PLAN_TABLE_OUTPUT
-------------------------------------
Plan hash value: 1598958544
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4448 | 606 (0)|
| 1 | PX COORDINATOR | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 4448 | 606 (0)|
| 3 | PX BLOCK ITERATOR | | 4448 | 606 (0)|
| 4 | TABLE ACCESS FULL| EMPLOYEE_DATA | 4448 | 606 (0)|
-------------------------------------------------------------------
11 rows selected.
SQL>
2=> DISPLAY_AWR Function
This table function displays the contents of an execution plan stored in AWR.
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);
Description of Parameters
sql_id --> Specifies the SQL_ID of the SQL statement. Appropriate value for the SQL statement of interest can be fetched by querying the column SQL_ID in DBA_HIST_SQLTEXT.
plan_hash_value --> Specifies the PLAN_HASH_VALUE of a SQL statement.
db_id --> Specifies the database_id for which the plan of the SQL statement
format --> Controls the level of details for the plan. It accepts four values:
BASIC,TYPICAL,SERIAL,ALL. Additional keywords ROWS,BYTES,COST,PARTITION,PARALLEL,PREDICATE,PROJECTION,ALIAS,REMOTE,NOTE
Examples
For setting this Example we will be doing Cartisian Join which will be reported in AWR snapshot manually as below. -> Run Query with Cartisian Join which will use INDEX -> Flush cache and generate AWR Snapshot -> Drop Index and Run Cartisian Query again which will NOT use INDEX now -> Run Query with Cartisian Join which will use INDEX [oracle@ace2oracledb Desktop]$ cat display_Awr.sql select /* cartijoin */ * from EMPLOYEE_LOCATION a ,EMPLOYEE_LOCATION b,EMPLOYEE_LOCATION C where a.EMP_ID < 10; [oracle@ace2oracledb Desktop]$ [oracle@ace2oracledb Desktop]$ nohup sqlplus abhi_test/Oracle_4U @display_Awr.sql & [1] 24783 [oracle@ace2oracledb Desktop]$ nohup: ignoring input and appending output to ‘nohup.out’ -> Flush cache and generate AWR Snapshot SQL> get generat_awr_flush_shared.sql 1 EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; 2 alter system flush buffer_cache; 3* alter system flush shared_pool; SQL> @generat_awr_flush_shared.sql PL/SQL procedure successfully completed. System altered. System altered. SQL> exit -> Drop Index and Run Cartisian Query again which will NOT use INDEX now SQL> drop index IDX_FULL_SCAN; Index dropped. SQL> purge recyclebin; Recyclebin purged. SQL> exit SQL> SELECT DISTINCT sql_id, plan_hash_value FROM dba_hist_sqlstat dhs WHERE dhs.sql_id IN ( 'anmbr988033ws'); 2 3 SQL_ID PLAN_HASH_VALUE -------------------- --------------- anmbr988033ws 1902333133 anmbr988033ws 1621238963 Our setup is completed now .Let we check with DISPLAY_AWR Function now. => Using default format with sql_id clause SQL> set line 300 SQL> set pages 200 SQL> select * from table(dbms_xplan.display_awr(sql_id=>'anmbr988033ws')); PLAN_TABLE_OUTPUT ------------------------------- SQL_ID anmbr988033ws -------------------- select /* cartijoin */ * from EMPLOYEE_LOCATION a ,EMPLOYEE_LOCATION b,EMPLOYEE_LOCATION C where a.EMP_ID < 10 Plan hash value: 1621238963 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4358K(100)| | | 1 | MERGE JOIN CARTESIAN | | 1943M| 271G| 4358K (1)| 00:02:51 | | 2 | MERGE JOIN CARTESIAN| | 161K| 15M| 407 (1)| 00:00:01 | | 3 | TABLE ACCESS FULL | EMPLOYEE_LOCATION | 14 | 700 | 29 (0)| 00:00:01 | | 4 | BUFFER SORT | | 11998 | 585K| 378 (1)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMPLOYEE_LOCATION | 11998 | 585K| 27 (0)| 00:00:01 | | 6 | BUFFER SORT | | 11998 | 585K| 4358K (1)| 00:02:51 | | 7 | TABLE ACCESS FULL | EMPLOYEE_LOCATION | 11998 | 585K| 27 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- SQL_ID anmbr988033ws -------------------- select /* cartijoin */ * from EMPLOYEE_LOCATION a ,EMPLOYEE_LOCATION b,EMPLOYEE_LOCATION C where a.EMP_ID < 10 Plan hash value: 1902333133 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4358K(100)| | | 1 | MERGE JOIN CARTESIAN | | 1943M| 271G| 4358K (1)| 00:02:51 | | 2 | MERGE JOIN CARTESIAN | | 161K| 15M| 390 (1)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEE_LOCATION | 14 | 700 | 12 (0)| 00:00:01 | | 4 | INDEX RANGE SCAN | IDX_FULL_SCAN | 14 | | 2 (0)| 00:00:01 | | 5 | BUFFER SORT | | 11998 | 585K| 378 (1)| 00:00:01 | | 6 | TABLE ACCESS FULL | EMPLOYEE_LOCATION | 11998 | 585K| 27 (0)| 00:00:01 | | 7 | BUFFER SORT | | 11998 | 585K| 4358K (1)| 00:02:51 | | 8 | TABLE ACCESS FULL | EMPLOYEE_LOCATION | 11998 | 585K| 27 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- 41 rows selected. SQL> SQL> SQL> => Using default format with sql_id & plan_hash_value clause SQL> select * from table(dbms_xplan.display_awr(sql_id=>'anmbr988033ws',plan_hash_value=>'1902333133')); PLAN_TABLE_OUTPUT --------------------------------------------- SQL_ID anmbr988033ws -------------------- select /* cartijoin */ * from EMPLOYEE_LOCATION a ,EMPLOYEE_LOCATION b,EMPLOYEE_LOCATION C where a.EMP_ID < 10 Plan hash value: 1902333133 ----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4358K(100)| | | 1 | MERGE JOIN CARTESIAN | | 1943M| 271G| 4358K (1)| 00:02:51 | | 2 | MERGE JOIN CARTESIAN | | 161K| 15M| 390 (1)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEE_LOCATION | 14 | 700 | 12 (0)| 00:00:01 | | 4 | INDEX RANGE SCAN | IDX_FULL_SCAN | 14 | | 2 (0)| 00:00:01 | | 5 | BUFFER SORT | | 11998 | 585K| 378 (1)| 00:00:01 | | 6 | TABLE ACCESS FULL | EMPLOYEE_LOCATION | 11998 | 585K| 27 (0)| 00:00:01 | | 7 | BUFFER SORT | | 11998 | 585K| 4358K (1)| 00:02:51 | | 8 | TABLE ACCESS FULL | EMPLOYEE_LOCATION | 11998 | 585K| 27 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------- 21 rows selected. SQL> => Using Basic format with sql_id & plan_hash_value & Other clause SQL> select * from table(dbms_xplan.display_awr(sql_id=>'anmbr988033ws',plan_hash_value=>'1902333133',format=>'BASIC +ROWS,+COST')); PLAN_TABLE_OUTPUT --------------------------------------- SQL_ID anmbr988033ws -------------------- select /* cartijoin */ * from EMPLOYEE_LOCATION a ,EMPLOYEE_LOCATION b,EMPLOYEE_LOCATION C where a.EMP_ID < 10 Plan hash value: 1902333133 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 4358K(100)| | 1 | MERGE JOIN CARTESIAN | | 1943M| 4358K (1)| | 2 | MERGE JOIN CARTESIAN | | 161K| 390 (1)| | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEE_LOCATION | 14 | 12 (0)| | 4 | INDEX RANGE SCAN | IDX_FULL_SCAN | 14 | 2 (0)| | 5 | BUFFER SORT | | 11998 | 378 (1)| | 6 | TABLE ACCESS FULL | EMPLOYEE_LOCATION | 11998 | 27 (0)| | 7 | BUFFER SORT | | 11998 | 4358K (1)| | 8 | TABLE ACCESS FULL | EMPLOYEE_LOCATION | 11998 | 27 (0)| ---------------------------------------------------------------------------------------- 21 rows selected. SQL>
3=> DISPLAY_CURSOR Function
This table function displays the explain plan of any cursor loaded in the cursor cache.
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
cursor_child_no IN NUMBER DEFAULT 0,
format IN VARCHAR2 DEFAULT 'TYPICAL');
Description of Parameter
sql_id --> Specifies the SQL_ID of the SQL statement in the cursor cache. Retrieve SQL_ID from V$SQL or V$SQLAREA.
Defaults to NULL in which case the plan of the last cursor executed by the session is displayed.
cursor_child_no --> Child number of the cursor to display. If not supplied, the execution plan of the child_number=0 cursor matching the supplied sql_id parameter are displayed.
format --> Controls the level of details for the plan. It accepts five values:
BASIC: Displays the minimum information in the plan—the operation ID, the operation name and its option.
TYPICAL: This is the default. Displays the most relevant information in the plan
SERIAL: Like TYPICAL except that the parallel information is not displayed, even if the plan executes in parallel.
ALL: Maximum user level.
ADAPTIVE: For Adaptive levels below are outcomes
-> Displays the final plan, or the current plan if the execution has not completed.
This section includes notes about runtime optimizations that affect the plan, such as switching from a Nested Loops join to a Hash join.
-> Plan lineage. This section shows the plans that were run previously due to automatic reoptimization.
It also shows the default plan, if the plan changed due to dynamic plans.
-> Recommended plan. In reporting mode, the plan is chosen based on execution statistics displayed. Note that displaying the recommended plan for automatic reoptimization
requires re-compiling the query with the optimizer adjustments collected in the child cursor. Displaying the recommended plan for a dynamic plan does not require this.
-> Dynamic plans. This summarizes the portions of the plan that differ from the default plan chosen by the optimizer.
Format keywords must be separated by either a comma or a space:
ROWS ,BYTES,COST,PARTITION,PARALLEL,PREDICATE,PROJECTION,ALIAS,REMOTE,NOTE - if relevant, shows the note section of the explain plan
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 shows IO statistics for ALL.
MEMSTATS - Assuming that PGA memory management is enabled (that is, pga_aggregate_target parameter is set to a non 0 value), this format allows to display memory management
statistics (for example, execution mode of the operator, how much memory was used, number of bytes spilled to disk, and so on). 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.
Examples
=> Using Default format
SQL> desc DEMO_TAB
Name Null? Type
----------------------------------------- -------- ----------------------------
PRODUCT VARCHAR2(9)
SHOP_ID NUMBER
CONS_ID NUMBER
SQL> SELECT PRODUCT,COUNT(*) FROM DEMO_tAB GROUP BY PRODUCT;
PRODUCT COUNT(*)
--------- ----------
WHEELS 29000
DABUR 100
COLGATE 10000
PATANJALI 3000
SQL> select * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID <5;
PRODUCT SHOP_ID CONS_ID
--------- ---------- ----------
WHEELS 0 0
WHEELS 0 0
29 rows selected.
SQL> set lines 500
SQL> set pages 500
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
------------------------------------------
SQL_ID 0m3785pjpsdjw, child number 1
-------------------------------------
select * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID <5
Plan hash value: 76495054
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 35 (100)| |
|* 1 | TABLE ACCESS FULL| DEMO_TAB | 29 | 406 | 35 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CONS_ID"<5 AND "SHOP_ID"=0 AND "PRODUCT"='WHEELS'))
Note
-----
- statistics feedback used for this statement
23 rows selected.
SQL>
=> Using ALL
SQL>
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALL'));
PLAN_TABLE_OUTPUT
------------------------------------------------------
SQL_ID 5hmrbjrqmt8cw, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics */ * from DEMO_TAB where
PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID <3
Plan hash value: 76495054
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 35 (100)| |
|* 1 | TABLE ACCESS FULL| DEMO_TAB | 29 | 406 | 35 (0)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / DEMO_TAB@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CONS_ID"<3 AND "SHOP_ID"=0 AND "PRODUCT"='WHEELS'))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "PRODUCT"[VARCHAR2,9], "SHOP_ID"[NUMBER,22], "CONS_ID"[NUMBER,22]
Note
-----
- statistics feedback used for this statement
33 rows selected.
SQL>
=> Using ALLSTATS LASTS (To see Estimated and Actual Rows use below gather_plan_statistics )
SQL> SELECT /*+ gather_plan_statistics */ * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID <3;
PRODUCT SHOP_ID CONS_ID
--------- ---------- ----------
WHEELS 0 0
WHEELS 0 0
29 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(format=>'ADAPTIVE ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5km46nmwfpp0w, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID <3
Plan hash value: 76495054
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 29 |00:00:00.01 | 117 |
|* 1 | TABLE ACCESS FULL| DEMO_TAB | 1 | 6 | 29 |00:00:00.01 | 117 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CONS_ID"<3 AND "SHOP_ID"=0 AND "PRODUCT"='WHEELS'))
19 rows selected
=> TO check BIND Variable value passed in SQL use “PEEKED_BINDS” as below.
SQL> variable Cons_var number=0
SQL> SELECT /*+ gather_plan_statistics */ * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID=:Cons_var;
PRODUCT SHOP_ID CONS_ID
--------- ---------- ----------
WHEELS 0 0
WHEELS 0 0
WHEELS 0 0
WHEELS 0 0
WHEELS 0 0
29 rows selected.
SQL>
SQL> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST COST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
------------------------------------------
SQL_ID 3aqu4vz7tuw6r, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID=:Cons_var
Plan hash value: 76495054
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 35 (100)| 29 |00:00:00.01 | 117 |
|* 1 | TABLE ACCESS FULL| DEMO_TAB | 1 | 2 | 35 (0)| 29 |00:00:00.01 | 117 |
-----------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 0
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CONS_ID"=:CONS_VAR AND "SHOP_ID"=0 AND "PRODUCT"='WHEELS'))
24 rows selected.
SQL>
=> Using SQL_ID
SQL> select /* cur_data */ * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID <3;
PRODUCT SHOP_ID CONS_ID
--------- ---------- ----------
WHEELS 0 0
WHEELS 0 0
29 rows selected.
SQL>
col SQL_ID for a20
col CHILD_NUMBER FOR 9
COL SQL_TEXT FOR A50
COL IS_REOPTIMIZABLE FOR A30
set lines 500
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE
FROM V$SQL
WHERE SQL_TEXT LIKE '%cur_data%' AND SQL_TEXT NOT LIKE '%V$SQL%';SQL> SQL> SQL> SQL> SQL> SQL> 2 3
SQL_ID CHILD_NUMBER SQL_TEXT IS_REOPTIMIZABLE
-------------------- ------------ -------------------------------------------------- ------------------------------
7jppayz6urgq4 0 select /* cur_data */ * from DEMO_TAB where PRODU Y
CT='WHEELS' AND SHOP_ID=0 AND CONS_ID <3
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'7jppayz6urgq4',format=>'ALLSTATS'));
PLAN_TABLE_OUTPUT
------------------------------------
SQL_ID 7jppayz6urgq4, child number 0
-------------------------------------
select /* cur_data */ * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID <3
Plan hash value: 76495054
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 29 |00:00:00.01 | 117 |
|* 1 | TABLE ACCESS FULL| DEMO_TAB | 1 | 6 | 29 |00:00:00.01 | 117 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CONS_ID"<3 AND "SHOP_ID"=0 AND "PRODUCT"='WHEELS'))
19 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'7jppayz6urgq4',format=>'ADAPTIVE ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------
SQL_ID 7jppayz6urgq4, child number 0
-------------------------------------
select /* cur_data */ * from DEMO_TAB where PRODUCT='WHEELS' AND
SHOP_ID=0 AND CONS_ID <3
Plan hash value: 76495054
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 29 |00:00:00.01 | 117 |
|* 1 | TABLE ACCESS FULL| DEMO_TAB | 1 | 6 | 29 |00:00:00.01 | 117 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("CONS_ID"<3 AND "SHOP_ID"=0 AND "PRODUCT"='WHEELS'))
19 rows selected.
4=> DISPLAY_PLAN Function
This table function displays the contents of the plan table in a variety of formats with CLOB output type.
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;
Description of Parameter
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.
statement_id --> Specifies the statement_id of the plan to be displayed.
filter_preds --> SQL filter predicate(s) to restrict the set of rows selected from the table where the plan is stored. For example: filter_preds=>'plan_id = 10'
format --> Controls the level of details for the plan. It accepts five values:
BASIC: & TYPICAL: & SERIAL: & ALL:
ADAPTIVE: Displays the default plan, and for each dynamic subplan (if stipulated):
- A list of the rowsources from the original which may be replaced, and the rowsources to replace them
- If outline display is specified in the format argument, the hints for each option in the dynamic subplan are displayed
Other following keywords
BYTES,COST,PARTITION,PARALLEL,PREDICATE,PROJECTION,ALIAS,REMOTE,NOTE - if relevant, shows the note section of the explain plan
Additional format keywords are IOSTATS, MEMSTATS, ALLSTATS and LAST
type --> Output type, one of: 'TEXT', 'ACTIVE', 'HTML', or 'XML' (see Usage Notes regarding type ACTIVE).
Example
=> Using Type for HTML as below SQL> explain plan for select * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID=0; Explained. SQL> set pages 0 head off SQL> set lines 200 SQL> set long 1000000 SQL> col disp_plan for a100 SQL> spool display_plans.html SQL> select DBMS_XPLAN.DISPLAY_PLAN(TYPE=>'HTML') AS disp_plan FROM DUAL; [oracle@ace2oracledb ~]$ ls -lrt display_plans.html -rw-r--r--. 1 oracle oinstall 14166 Jan 4 14:09 display_plans.html [oracle@ace2oracledb ~]$
5=> COMPARE_PLANS Function
This function compares each plan in a list with a reference plan and returns the report. DBMS_XPLAN.COMPARE_PLANS( reference_plan IN generic_plan_object, compare_plan_list IN plan_object_list, type IN VARCHAR2 := 'TEXT', level IN VARCHAR2 := 'TYPICAL', section IN VARCHAR2 := 'ALL') RETURN CLOB; Description of Parameter reference_plan --> The reference plan. This plan should always evaluate to a single plan. compare_plan_list --> List of plans to compare with reference plan. type --> Possible values are: TEXT,HTML,XML level --> Possible values are: BASIC,TYPICAL,ALL section --> Possible values are: SUMMARY,FINDINGS,PLANS,INFORMATION,ERRORS
Example
=> We will compare 1 SQL_ID having 2 Childs with and without INDEX as below.
SQL> desc DEMO_tAB;
Name Null? Type
----------------------------------------- -------- ----------------------------
PRODUCT VARCHAR2(9)
SHOP_ID NUMBER
CONS_ID NUMBER
SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='DEMO_TAB';
no rows selected
SQL> select /* compare */ * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID =0;
PRODUCT SHOP_ID CONS_ID
--------- ---------- ----------
WHEELS 0 0
WHEELS 0 0
WHEELS 0 0
WHEELS 0 0
WHEELS 0 0
29 rows selected.
SQL>
col SQL_ID for a20
col CHILD_NUMBER FOR 9
COL SQL_TEXT FOR A50
COL IS_REOPTIMIZABLE FOR A30
set lines 500
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE
FROM V$SQL
WHERE SQL_TEXT LIKE '%compare%' AND SQL_TEXT NOT LIKE '%V$SQL%';SQL> SQL> SQL> SQL> SQL> SQL> 2 3
SQL_ID CHILD_NUMBER SQL_TEXT IS_REOPTIMIZABLE
-------------------- ------------ -------------------------------------------------- ------------------------------
2nhvn3vpjadzv 0 select /* compare */ * from DEMO_TAB where PRODUC Y
T='WHEELS' AND SHOP_ID=0 AND CONS_ID =0
SQL> CREATE INDEX COMP_IDX2 on DEMO_TAB(PRODUCT,SHOP_ID,CONS_ID);
Index created.
SQL> select /* compare */ * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID =0;
PRODUCT SHOP_ID CONS_ID
--------- ---------- ----------
WHEELS 0 0
WHEELS 0 0
WHEELS 0 0
WHEELS 0 0
WHEELS 0 0
WHEELS 0 0
WHEELS 0 0
WHEELS 0 0
...
WHEELS 0 0
29 rows selected.
SQL> col SQL_ID for a20
col CHILD_NUMBER FOR 9
COL SQL_TEXT FOR A50
COL IS_REOPTIMIZABLE FOR A30
set lines 500
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT, IS_REOPTIMIZABLE
FROM V$SQL
WHERE SQL_TEXT LIKE '%compare%' AND SQL_TEXT NOT LIKE '%V$SQL%';SQL> SQL> SQL> SQL> SQL> 2 3
SQL_ID CHILD_NUMBER SQL_TEXT IS_REOPTIMIZABLE
-------------------- ------------ -------------------------------------------------- ------------------------------
2nhvn3vpjadzv 0 select /* compare */ * from DEMO_TAB where PRODUC Y
T='WHEELS' AND SHOP_ID=0 AND CONS_ID =0
2nhvn3vpjadzv 1 select /* compare */ * from DEMO_TAB where PRODUC N
T='WHEELS' AND SHOP_ID=0 AND CONS_ID =0
Let we compare mentioned Childs as below
SQL> var report clob;
exec : report := dbms_xplan.compare_plans(cursor_cache_object('2nhvn3vpjadzv',0),plan_object_list(cursor_cache_object('2nhvn3vpjadzv',1)));SQL>
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> SET PAGESIZE 50000
SET LONG 100000
SET LINESIZE 210
COLUMN report FORMAT a200
SELECT :report REPORT FROM DUAL;SQL> SQL> SQL> SQL>
REPORT
-----------------------------
COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
Current user : ABHI_TEST
Total number of plans : 2
Number of findings : 1
---------------------------------------------------------------------------------------------
COMPARISON DETAILS
---------------------------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 2nhvn3vpjadzv
Child Number : 0
Plan Database Version : 19.0.0.0
Parsing Schema : "ABHI_TEST"
SQL Text : select /* compare */ * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID =0
Plan
-----------------------------
Plan Hash Value : 76495054
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 35 | |
| * 1 | TABLE ACCESS FULL | DEMO_TAB | 2 | 28 | 35 | 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(("CONS_ID"=0 AND "SHOP_ID"=0 AND "PRODUCT"='WHEELS'))
---------------------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Cursor Cache
SQL ID : 2nhvn3vpjadzv
Child Number : 1
Plan Database Version : 19.0.0.0
Parsing Schema : "ABHI_TEST"
SQL Text : select /* compare */ * from DEMO_TAB where PRODUCT='WHEELS' AND SHOP_ID=0 AND CONS_ID =0
Plan
-----------------------------
Plan Hash Value : 1506817081
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 | |
| * 1 | INDEX RANGE SCAN | COMP_IDX2 | 14 | 196 | 1 | 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("PRODUCT"='WHEELS' AND "SHOP_ID"=0 AND "CONS_ID"=0)
Comparison Results (1):
-----------------------------
1. Query block SEL$1, Alias "DEMO_TAB"@"SEL$1": Some columns (OPERATION,
OPTIONS, OBJECT_NAME) do not match between the reference plan (id: 1) and
the current plan (id: 1).
---------------------------------------------------------------------------------------------
6=>DISPLAY_SQLSET Function
This table function displays the execution plan of a given statement stored in a SQL tuning set.
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;
Step 1=> Get SQL ID in STS from below query.
SQL > SELECT SQL_ID FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'SQLT_FROM_TRACE' ) ) ;
bunvx480ynf57
Step 2=> Fetch details from DISPLAY_SQLSET
SQL> SELECT * FROM table (DBMS_XPLAN.DISPLAY_SQLSET('SQLT_FROM_TRACE','bunvx480ynf57',NULL,'ALL'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL Tuning Set Name: SQLT_FROM_TRACE
SQL Tuning Set Owner: ABHI_TEST
SQL_ID: bunvx480ynf57
SQL Text: SELECT 1 FROM DUAL
--------------------------------------------------------------------------------
-----------------------------------------
| Id | Operation | Name | Cost |
-----------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | FAST DUAL | | 2 |
-----------------------------------------
Note
-----
- cpu costing is off (consider enabling it)
18 rows selected.
SQL>
6=>DISPLAY_SQL_PLAN_BASELINE Function
This table function displays one or more execution plans for the specified SQL handle of a SQL plan baseline.
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 plans 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. The following format values are possible, each representing a common use case: BASIC, TYPICAL, and ALL.
Setup
For Demo let we check if we have SQL PLAN Basline for any Query and from there we will get SQL_HANDLE as below,
SQL> SELECT SQL_HANDLE,PLAN_NAME, SQL_TEXT
FROM DBA_SQL_PLAN_BASELINES
WHERE SQL_TEXT LIKE 'SELECT job_title%';
2 3
SQL_HANDLE PLAN_NAME SQL_TEXT
------------------------------ ---------------------------------------- ----------------------------------------
SQL_fb21d94e54d9612c SQL_PLAN_gq8ft9tadks9c47639e46 SELECT job_title FROM hr.jobs WHERE job_id = 'AD_PRES'
Now let we get information about the same from DBMS_XPLAN as below.
SQL> SET LINESIZE 300
SQL> SET PAGESIZE 2000
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE('SQL_fb21d94e54d9612c',NULL,'ALL'));SQL> SQL>
PLAN_TABLE_OUTPUT
--------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_fb21d94e54d9612c
SQL text: SELECT job_title FROM hr.jobs WHERE job_id = 'AD_PRES'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_gq8ft9tadks9c47639e46 Plan id: 1197710918
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 1302208962
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 27 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | JOB_ID_PK | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / JOBS@SEL$1
2 - SEL$1 / JOBS@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='AD_PRES')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "JOB_TITLE"[VARCHAR2,35]
2 - "JOBS".ROWID[ROWID,10]
38 rows selected.
SQL>
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444