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') )
/