gather_plan_statistics提示的使用,获取详细的执行统计信息
来源:互联网 发布:华为云 阿里云 分析 编辑:程序博客网 时间:2024/05/16 09:55
SQL> set linesize 150
SQL> set pagesize 2000
SQL> set autotrace traceonly exp
SQL> select avg(SALARY),DEPARTMENT_NAME from
employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;
Execution Plan
----------------------------------------------------------
Plan hash value: 3294250112
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 |621 | 5 (20)| 00:00:01 |
| 1 | HASH GROUP BY | | 27 |621 | 5 (20)| 00:00:01 |
| 2 | NESTED LOOPS | |106 | 2438 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMPLOYEES |107 |749 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 16 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL> set autotrace off;
SQL> select /*+ gather_plan_statistics */ avg(SALARY),DEPARTMENT_NAME from
employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;
SQL> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
或 select * from table(dbms_xplan.display_cursor(sql_id => 'd9ayadktcs0g2',format => 'ALLSTATS LAST'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_IDbctzu9xuxay18, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ avg(SALARY),DEPARTMENT_NAME from employees e,departments d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME
Plan hash value: 3294250112
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 27 | 11 |00:00:00.01 | 219 |
| 2 | NESTED LOOPS | | 1 | 106 | 106 |00:00:00.01 | 219 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 7 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 107 | 1 | 106 |00:00:00.01 | 212 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 107 | 1 | 106 |00:00:00.01 | 106 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
/* 可以从starts列看到某种操作执行了多少次,例如这里的INDEX UNIQUE SCAN为107次 */
/*也可以通过SQL_ID来定位计划信息 */
SQL> select t.*
from v$sql s
, table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID' ;
Enter value for sql_id: bctzu9xuxay18
old 3: , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID'
new 3: , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = 'bctzu9xuxay18'
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_IDbctzu9xuxay18, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ avg(SALARY),DEPARTMENT_NAME from
employees e,departments d where
e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME
Plan hash value: 3294250112
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 27 | 621 | 5 (20)| 00:00:01 | 11 |00:00:00.01 | 219 |
| 2 | NESTED LOOPS | | 1 | 106 | 2438 | 4(0)| 00:00:01 | 106 |00:00:00.01 | 219 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 749 | 3(0)| 00:00:01 | 107 |00:00:00.01 | 7 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 107 | 1 | 16 | 1(0)| 00:00:01 | 106 |00:00:00.01 | 212 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 107 | 1 | | 0(0)| | 106 |00:00:00.01 | 106 |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / E@SEL$1
4 - SEL$1 / D@SEL$1
5 - SEL$1 / D@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEPARTMENT_NAME"[VARCHAR2,30], AVG("SALARY")[22]
2 - "SALARY"[NUMBER,22], "DEPARTMENT_NAME"[VARCHAR2,30]
3 - "SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
4 - "DEPARTMENT_NAME"[VARCHAR2,30]
5 - "D".ROWID[ROWID,10]
SQL> alter session set statistics_level=ALL; ---不推荐
Session altered.
/* 在session级别设置statistics_level为ALL,可以为我们提供更为详尽的执行统计信息 */
- gather_plan_statistics提示的使用,获取详细的执行统计信息
- 使用 traceback 获取详细的异常信息
- 飘逸的python - 使用traceback获取详细的异常信息
- sqlplus得到执行计划的统计信息
- oracle -执行计划的统计信息
- python使用traceback获取详细的异常信息
- mysql innodb 如何获取用于 生成执行计划的 数据表统计信息
- 使用待定的统计信息
- ios获取系统信息的详细api
- java获取详细的异常链信息
- 统计一条sql_id的信息,详细的运行情况
- oracle执行计划与统计信息的一些总结
- 解决scott用户执行计划看不到统计信息的问题
- oracle数据库SQL语句的一种优化手段 gather_plan_statistics hint
- 页面执行需要长时间时向用户的提示信息
- 使用PLSQL,第一次执行表的select操作的时候,提示"动态执行表不可访问,本会话的自动统计被禁止"
- multi2sim 编译与使用(2)-详细运行时信息的获取
- Python语言学习讲解七:使用traceback获取详细的异常信息
- 机器人仿真或者开发平台
- H264实时编码及NALU,RTP传输(续)(ZZ)
- Bash 使用技巧
- 2011-06-10
- ERROR: Removing 'hello': Device or resource busy //关于使用系统定义的模块加载和卸载函数
- gather_plan_statistics提示的使用,获取详细的执行统计信息
- PHP根据文件头信息准确判断上传的文件类型
- 13个字节判断IE浏览器
- CMD List
- oracle常用表和视图
- EBS的Receipts Form 打开Window Help时显示无法找到页面(404 error)错误解决办法
- printk打印问题
- opencv零散
- 限制通过SSH远程连接的用户帐号