Hint gather_plan_statistics + allstats last 收集额外的统计信息

Hint gather_plan_statistics 用于目标SQL执行时收集一些额外的统计信息，比如每一个具体执行步骤的实际返回结果集、每一个具体执行步骤的实际执行时间、每一个具体执行步骤所实际消耗的逻辑及物理读等。

 

不加Hint gather_plan_statistics ，即使指定allstats last也不会收集额外的统计信息

select * from t where object_id<100;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  9fz3n10pawxkv, child number 0
-------------------------------------
select *  from t where object_id<100

Plan hash value: 4182247035

---------------------------------------------------------
| Id  | Operation                   | Name     | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT            |          |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |     98 |
|*  2 |   INDEX RANGE SCAN          | IND_T_ID |     98 |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<100)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level

gather_plan_statistics  + allstats last

select /*+ gather_plan_statistics */ *  from t where object_id<100;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
SQL_ID  6xdrnmv6xk3mn, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ *  from t where object_id<100

Plan hash value: 4182247035

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |     98 |00:00:00.01 |       5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |      1 |     98 |     98 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN          | IND_T_ID |      1 |     98 |     98 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<100)

Note
-----
   - dynamic sampling used for this statement (level=2)

gather_plan_statistics  + allstats last 增强版

select /*+ gather_plan_statistics */ *  from t where object_id<100;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID  6xdrnmv6xk3mn, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ *  from t where object_id<100

Plan hash value: 4182247035

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |       |     6 (100)|     98 |00:00:00.01 |       5 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |      1 |     98 | 20286 |     6   (0)|     98 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN          | IND_T_ID |      1 |     98 |       |     2   (0)|     98 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<100)

Note
-----
   - dynamic sampling used for this statement (level=2)

gather_plan_statistics  对于advanced没啥用

select /*+ gather_plan_statistics */ *  from t where object_id<100;

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  6xdrnmv6xk3mn, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ *  from t where object_id<100

Plan hash value: 4182247035

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |       |       |     6 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T        |    98 | 20286 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_ID |    98 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"<100)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T"."OWNER"[VARCHAR2,30], "T"."OBJECT_NAME"[VARCHAR2,128],
       "T"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
       "T"."DATA_OBJECT_ID"[NUMBER,22], "T"."OBJECT_TYPE"[VARCHAR2,19],
       "T"."CREATED"[DATE,7], "T"."LAST_DDL_TIME"[DATE,7],
       "T"."TIMESTAMP"[VARCHAR2,19], "T"."STATUS"[VARCHAR2,7],
       "T"."TEMPORARY"[VARCHAR2,1], "T"."GENERATED"[VARCHAR2,1],
       "T"."SECONDARY"[VARCHAR2,1], "T"."NAMESPACE"[NUMBER,22],
       "T"."EDITION_NAME"[VARCHAR2,30]
   2 - "T".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

Note
-----
   - dynamic sampling used for this statement (level=2)