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.