GATHER_PLAN_STATISTICS 不生成基本计划统计信息 [英] GATHER_PLAN_STATISTICS does does not generate basic plan statistics

问题描述

全部，

当我运行以下命令时，我现在正在学习调整查询:

I am learning to tune query now, when I ran the following:

select /*+ gather_plan_statistics */ * from emp;

select * from table(dbms_xplan.display(FORMAT=>'ALLSTATS LAST'));

结果总是说:

• 警告:基本计划统计数据不可用.这些仅在以下情况下收集:
  • 提示gather_plan_statistics"用于语句或
  • 参数statistics_level"设置为ALL"，在会话或系统级别

  我在 sqlplus 中也尝试了 alter session set statistics_level = ALL;，但这并没有改变结果.

  I tried the alter session set statistics_level = ALL; too in sqlplus, but that did not change anything in the result.

  谁能告诉我我可能遗漏了什么?

  Could anyone please let me know what I might have missed?

  非常感谢.

  推荐答案

  DISPLAY Function 显示由 解释计划 命令.因此，您可以使用 EXPLAIN PLAN FOR 命令使用它来生成和显示(理论)计划，例如以这种方式:

  DISPLAY Function displays a content of PLAN_TABLE generated (filled) by EXPLAIN PLAN FOR command. So you can use it to generate and display an (theoretical) plan using EXPLAIN PLAN FOR command, for example in this way:

  create table emp as select * from all_objects;
  
  explain plan for
  select /*+ gather_plan_statistics */ count(*) from emp where object_id between 100 and 150;
  
  select * from table(dbms_xplan.display );
  
  Plan hash value: 2083865914
  
  ---------------------------------------------------------------------------
  | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |      |     1 |     5 |   351   (1)| 00:00:01 |
  |   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
  |*  2 |   TABLE ACCESS FULL| EMP  |    12 |    60 |   351   (1)| 00:00:01 |
  ---------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - filter("OBJECT_ID"<=150 AND "OBJECT_ID">=100)
  

  <小时>

  /*+ gather_plan_statistics */提示不会将数据保存到 PLAN_TABLE 中，而是将执行统计信息保存在 V$SQL_PLAN 性能视图中.
  要显示这些数据，您可以使用此处描述的方法:http://www.dba-oracle.com/t_gather_plan_statistics.htm，但这并不总是有效，因为您必须在 SQL 查询之后立即执行第二个命令.
  
  更好的方法是查询V$SQL视图获取查询的SQL_ID，然后使用DISPLAY_CURSOR 函数，例如这样:

  ---

  /*+ gather_plan_statistics */ hint does not save data into PLAN_TABLE, but it stores execution statistics in V$SQL_PLAN performance view.
  To display these data you can use a method described here: http://www.dba-oracle.com/t_gather_plan_statistics.htm, but this not always work, because you must execute the second command immediately after the SQL query.
  
  The better method is to query V$SQL view to obtain SQL_ID of the query, and then use DISPLAY_CURSOR function, for example in this way:

  select /*+ gather_plan_statistics */ count(*) from emp where object_id between 100 and 150;
  
  select sql_id, plan_hash_value, child_number, executions, fetches, cpu_time, elapsed_time, physical_read_requests, physical_read_bytes
  from v$sql s
  where sql_fulltext like 'select /*+ gather_plan_statistics */ count(*)%from emp%'
    and sql_fulltext not like '%from v$sql' ;
  
  SQL_ID        PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS    FETCHES   CPU_TIME ELAPSED_TIME PHYSICAL_READ_REQUESTS PHYSICAL_READ_BYTES
  ------------- --------------- ------------ ---------- ---------- ---------- ------------ ---------------------- -------------------
  9jjm288hx7buz      2083865914            0          1          1      15625        46984                     26            10305536
  

  上述查询返回SQL_ID=9jjm288hx7buz 和CHILD_NUMBER=0(子编号只是一个游标编号).使用这些值来查询收集的计划:

  The above query returns SQL_ID=9jjm288hx7buz and CHILD_NUMBER=0(child number is just a cursor number). Use these values to query the colledted plan:

  SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('9jjm288hx7buz', 0, 'ALLSTATS'));
  
  SQL_ID  9jjm288hx7buz, child number 0
  -------------------------------------
  select /*+ gather_plan_statistics */ count(*) from emp where object_id 
  between 100 and 150
  
  Plan hash value: 2083865914
  
  -------------------------------------------------------------------------------------
  | Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
  -------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |      |      2 |        |      2 |00:00:00.05 |   10080 |
  |   1 |  SORT AGGREGATE    |      |      2 |      1 |      2 |00:00:00.05 |   10080 |
  |*  2 |   TABLE ACCESS FULL| EMP  |      2 |     47 |     24 |00:00:00.05 |   10080 |
  -------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - filter(("OBJECT_ID"<=150 AND "OBJECT_ID">=100))
  

  这篇关于GATHER_PLAN_STATISTICS 不生成基本计划统计信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！