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
andCHILD_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屋!