dbms_xplan.display_cursor with gather_plan_statistics hint
gather_plan_statistics hint
-- Table Create ----
create table teststats as select * from all_objects;
select /*+ gather_plan_statistics ANUJTEST */ count(*) from teststats where object_id between 100 and 200;
col sql_text for a100 wrap
select inst_id,sql_id,child_number,sql_fulltext FROM gv$sql
WHERE 1=1
and sql_text LIKE '%ANUJTEST%'
and SQL_TEXT not like '%from gv$sql%';
INST_ID SQL_ID CHILD_NUMBER SQL_FULLTEXT
---------- ------------- ------------ ----------------------------------------------------------------------------------------------------
1 5jv62xqkdd1tz 0 SELECT x.* FROM v$sql s, TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_num
1 c69d8vn5pdn8t 0 SELECT x.*
FROM v$sql s,
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.ch
1 6wbxyxvnhqfsh 0 SELECT x.*
FROM v$sql s,
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.ch
1 2197z7183vsgc 0 select /*+ gather_plan_statistics ANUJTEST */ count(*) from teststats where obje <<<<<<< this one !!!
SELECT x.* FROM gv$sql s, TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) x
WHERE 1=1
and s.sql_text LIKE '%ANUJTEST%'
and SQL_TEXT not like '%from gv$sql';
set line 300 pagesize 300
col PLAN_TABLE_OUTPUT for a150
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '2197z7183vsgc',cursor_child_no => 0, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE +PROJECTION +REMOTE +NOTE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2197z7183vsgc, child number 0
-------------------------------------
select /*+ gather_plan_statistics ANUJTEST */ count(*) from teststats
where object_id between 100 and 200
Plan hash value: 1317213715
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 392 (100)| | 1 |00:00:00.11 | 1443 | 1111 |
| 1 | SORT AGGREGATE | | 1 | 1 | 13 | | | 1 |00:00:00.11 | 1443 | 1111 |
|* 2 | TABLE ACCESS FULL| TESTSTATS | 1 | 16 | 208 | 392 (1)| 00:00:01 | 96 |00:00:00.01 | 1443 | 1111 |
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TESTSTATS@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TESTSTATS"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OBJECT_ID">=100 AND "OBJECT_ID"<=200))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - (rowset=1019)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
50 rows selected.
===========================================================================================
or
set linesize 300 pagesize 300
alter session set statistics_level='ALL';
col PLAN_TABLE_OUTPUT for a150
select * from table(dbms_xplan.display_cursor(sql_id => '2197z7183vsgc', cursor_child_no => 0,FORMAT =>'allstats'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2197z7183vsgc, child number 0
-------------------------------------
select /*+ gather_plan_statistics ANUJTEST */ count(*) from teststats
where object_id between 100 and 200
Plan hash value: 1317213715
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.11 | 1443 | 1111 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.11 | 1443 | 1111 |
|* 2 | TABLE ACCESS FULL| TESTSTATS | 1 | 16 | 96 |00:00:00.01 | 1443 | 1111 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OBJECT_ID">=100 AND "OBJECT_ID"<=200))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
24 rows selected.
====================
select inst_id,sql_id,child_number from gv$sql
WHERE sql_id = '&sql_id'
;
INST_ID SQL_ID CHILD_NUMBER
---------- ------------- ------------
1 74b220rfs400g 0
SELECT
t.plan_table_output
FROM
gv$sql v,
TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all'
, NULL
, 'ADVANCED ALLSTATS LAST +COST +PARALLEL +PARTITION +IOSTATS'
, 'inst_id = '||v.inst_id||'
AND sql_id = '''||v.sql_id||''' AND child_number = '||v.child_number )
) t
WHERE
v.sql_id = '&sql_id'
AND v.loaded_versions > 0
;
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&&sql_id', cursor_child_no => 0, FORMAT => 'TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));
PRO
PRO GV$SQL_PLAN_STATISTICS_ALL LAST (ordered by inst_id and child_number)
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COL inst_child FOR A21
BREAK ON inst_child SKIP 2;
SET PAGES 0;
WITH v AS (
SELECT /*+ MATERIALIZE */
DISTINCT sql_id, inst_id, child_number
FROM gv$sql
WHERE sql_id = '&&sql_id.'
AND loaded_versions > 0
ORDER BY 1, 2, 3 )
SELECT /*+ ORDERED USE_NL(t) */
RPAD('Inst: '||v.inst_id, 9)||' '||RPAD('Child: '||v.child_number, 11) inst_child, t.plan_table_output FROM v, TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all', NULL, 'ADVANCED ALLSTATS LAST',
'inst_id = '||v.inst_id||' AND sql_id = '''||v.sql_id||''' AND child_number = '||v.child_number)) t
/
define sql_id='74b220rfs400g'
set linesize 300 pagesize 300
col PLAN_TABLE_OUTPUT for a180
COL inst_child FOR A21
BREAK ON inst_child SKIP 2
SELECT RPAD('Inst: '||v.inst_id, 9)||' '||RPAD('Child: '||v.child_number, 11) inst_child, t.plan_table_output
FROM gv$sql v,
TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all', NULL, 'ADVANCED ALLSTATS LAST', 'inst_id = '||v.inst_id||' AND sql_id = '''||v.sql_id||''' AND child_number = '||v.child_number)) t
WHERE v.sql_id = '&&sql_id.'
AND v.loaded_versions > 0
;
set linesize 300 pagesize 300
WITH v AS (
SELECT /*+ MATERIALIZE */
DISTINCT sql_id, inst_id, child_number
FROM gv$sql
WHERE sql_id = '&sql_id' and child_number = '&child_number'
AND loaded_versions > 0
ORDER BY 1, 2, 3 )
,u
as (
SELECT /*+ ORDERED USE_NL(t) */
rank() over(order by v.inst_id) rn,
RPAD('Inst: '||v.inst_id, 9)||' '||RPAD('Child: '||v.child_number, 11) inst_child,
t.plan_table_output
FROM v, TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all', NULL, 'ALL ALLSTATS LAST -PROJECTION', --'ALLSTATS LAST alias partition cost',
'inst_id = '||v.inst_id||' AND sql_id = '''||v.sql_id||''' AND child_number = '||v.child_number)) t
)
select u.plan_table_output
from u
where
rn = 1 -- to avoid multiple occurences per instance
set linesize 300 pagesize 300
WITH v AS (
SELECT /*+ MATERIALIZE */
DISTINCT sql_id, inst_id, child_number
FROM gv$sql
WHERE sql_id = '&sql_id' and child_number = '&child_number'
AND loaded_versions > 0
ORDER BY 1, 2, 3 )
,u
as (
SELECT /*+ ORDERED USE_NL(t) */
rank() over(order by v.inst_id) rn,
RPAD('Inst: '||v.inst_id, 9)||' '||RPAD('Child: '||v.child_number, 11) inst_child,
t.plan_table_output
FROM v, TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all', NULL, 'ADVANCED ALLSTATS LAST',
'inst_id = '||v.inst_id||' AND sql_id = '''||v.sql_id||''' AND child_number = '||v.child_number)) t
)
select u.plan_table_output
from u
where
rn = 1 -- to avoid multiple occurences per instance
/
set linesize 500 pagesize 500
select * from table( dbms_xplan.display_awr('&sql_id', plan_hash_value => '&plan_hash_value', format => 'ADVANCED ALLSTATS LAST') ) --'ADVANCED +PEEKED_BINDS +ALLSTATS LAST +MEMSTATS LAST partition cost') )
/
