利用AWR 查看SQL 执行计划
来源:互联网 发布:android app源码 编辑:程序博客网 时间:2024/05/29 04:11
在AWR中定位到问题SQL语句后想要了解该SQL statement的具体执行计划,于是就用AWR报告中得到的SQL ID去V$SQL等几个动态性能视图中查询,但发现V$SQL或V$SQL_PLAN视图都已经找不到对应SQL ID的记录,一般来说这些语句已经从shared pool共享池中被替换出去了。
这个时候我们可以尝试使用DBMS_XPLAN.DISPLAY_AWR存储过程来将Oracle Automatic Workload Repository自动负载仓库中记录的SQL语句相关信息抽取出来,如:
SYS@ orcl> select /* extrace_awr */ count(*) from redo_test;
COUNT(*)
----------
213495
SYS@ orcl> select sql_id from v$sql where sql_text like '%extrace_awr%' and sql_text not like '%like%';
SQL_ID
-------------
7d0r6vd8yccuy
SYS@ orcl> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
SYS@ orcl> select * from table(dbms_xplan.display_cursor('7d0r6vd8yccuy',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7d0r6vd8yccuy, child number 0
-------------------------------------
select /* extrace_awr */ count(*) from redo_test
Plan hash value: 4170294667
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 874 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| REDO_TEST | 189K| 874 (1)| 00:00:11 |
------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
# 冲刷共享池后v$SQL/V$SQL_PLAN等动态视图内的记录会消失,
# 需要注意的是如果在AWR快照自动生成之前冲刷了共享池,那么可能丢失SQL的执行统计信息
SYS@ orcl> alter system flush shared_pool;
System altered.
SYS@ orcl> select * from table(dbms_xplan.display_cursor('7d0r6vd8yccuy',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID: 7d0r6vd8yccuy, child number: 0 cannot be found
# 此时就可以利用dbms_xplan.display_awr存储过程来抓取该SQL_ID对应的执行计划
SYS@ orcl> select * from table(dbms_xplan.display_awr('7d0r6vd8yccuy'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7d0r6vd8yccuy
--------------------
select /* extrace_awr */ count(*) from redo_test
Plan hash value: 4170294667
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 874 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| REDO_TEST | 189K| 874 (1)| 00:00:11 |
------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SYS@ orcl> select * from table(dbms_xplan.display_awr('7d0r6vd8yccuy',null,null,'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7d0r6vd8yccuy
--------------------
select /* extrace_awr */ count(*) from redo_test
Plan hash value: 4170294667
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 874 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| REDO_TEST | 189K| 874 (1)| 00:00:11 |
------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / REDO_TEST@SEL$1
Note
-----
- dynamic sampling used for this statement (level=2)
# 也可以从 dba_hist_sql_plan/dba_hist_sqltext等awr历史视图中直接观察该SQL ID对应的语句
SYS@ orcl> select operation, options, object_name from dba_hist_sql_plan where sql_id='7d0r6vd8yccuy';
OPERATION OPTIONS OBJECT_NAME
------------------------------ ------------------------------ -------------------------------
SELECT STATEMENT
SORT AGGREGATE
TABLE ACCESS FULL REDO_TEST
这个时候我们可以尝试使用DBMS_XPLAN.DISPLAY_AWR存储过程来将Oracle Automatic Workload Repository自动负载仓库中记录的SQL语句相关信息抽取出来,如:
SYS@ orcl> select /* extrace_awr */ count(*) from redo_test;
COUNT(*)
----------
213495
SYS@ orcl> select sql_id from v$sql where sql_text like '%extrace_awr%' and sql_text not like '%like%';
SQL_ID
-------------
7d0r6vd8yccuy
SYS@ orcl> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
SYS@ orcl> select * from table(dbms_xplan.display_cursor('7d0r6vd8yccuy',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7d0r6vd8yccuy, child number 0
-------------------------------------
select /* extrace_awr */ count(*) from redo_test
Plan hash value: 4170294667
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 874 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| REDO_TEST | 189K| 874 (1)| 00:00:11 |
------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
# 冲刷共享池后v$SQL/V$SQL_PLAN等动态视图内的记录会消失,
# 需要注意的是如果在AWR快照自动生成之前冲刷了共享池,那么可能丢失SQL的执行统计信息
SYS@ orcl> alter system flush shared_pool;
System altered.
SYS@ orcl> select * from table(dbms_xplan.display_cursor('7d0r6vd8yccuy',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID: 7d0r6vd8yccuy, child number: 0 cannot be found
# 此时就可以利用dbms_xplan.display_awr存储过程来抓取该SQL_ID对应的执行计划
SYS@ orcl> select * from table(dbms_xplan.display_awr('7d0r6vd8yccuy'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7d0r6vd8yccuy
--------------------
select /* extrace_awr */ count(*) from redo_test
Plan hash value: 4170294667
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 874 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| REDO_TEST | 189K| 874 (1)| 00:00:11 |
------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SYS@ orcl> select * from table(dbms_xplan.display_awr('7d0r6vd8yccuy',null,null,'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7d0r6vd8yccuy
--------------------
select /* extrace_awr */ count(*) from redo_test
Plan hash value: 4170294667
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 874 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| REDO_TEST | 189K| 874 (1)| 00:00:11 |
------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / REDO_TEST@SEL$1
Note
-----
- dynamic sampling used for this statement (level=2)
# 也可以从 dba_hist_sql_plan/dba_hist_sqltext等awr历史视图中直接观察该SQL ID对应的语句
SYS@ orcl> select operation, options, object_name from dba_hist_sql_plan where sql_id='7d0r6vd8yccuy';
OPERATION OPTIONS OBJECT_NAME
------------------------------ ------------------------------ -------------------------------
SELECT STATEMENT
SORT AGGREGATE
TABLE ACCESS FULL REDO_TEST
- 利用AWR 查看SQL 执行计划
- 利用从awr中查找好的执行计划来优化SQL
- 查看SQl执行计划
- SQL执行计划查看
- 查看sql执行计划
- 查看SQL执行计划
- 查看SQL执行计划
- 查看SQL执行计划
- 结合AWR报告生成HTML格式的SQL执行计划
- Scrips:打印AWR中执行计划的改变awr_plan_change.sql
- Scripts:显示AWR中执行计划的脚本dplan_allstats.sql
- 查看SQL的执行计划
- 查看oracle sql 执行计划
- 查看sql执行计划方式
- ORACLE 查看SQL执行计划
- MySQL数据库性能优化-利用sql执行计划预先查看执行性能(五)
- Scripts:打印执行计划中AWR的执行计划信息awr_plan_stats.sql
- 如何查看oracle sql执行计划
- PLSQL Developer 在Win7 64bit上连接 Oracle 11g
- libvirt hugepage 配置
- HDU 1874 畅通工程续
- Qt 设置全局变量
- C++数据结构--.哈希表线性探测开放寻址法与独立表链地址法
- 利用AWR 查看SQL 执行计划
- asp操作sql存储过程,枚举值常量(常用)
- backbone学习笔记二(框架简介)
- Java 类型转换
- ant 环境变量配置
- 03.Android之多媒体——使用ContentResolver获取音频文件
- 移植I2C-EEPROM驱动和看门狗驱动
- NYOJ-640 Geometric sum
- endomodo 网站