如果目标SQL的执行计划被刷出了shared_pool,就需要使用dbms_xplan.display_awr来查看历史执行计划了。
语法:
select * from table(dbms_xplan.display_awr('sql_id'));
SQL> conn hr/hr
Connected.
--先清空共享池
SQL> alter system flush shared_pool;
System altered.
SQL> select employee_id,last_name,salary from employees where employee_id=100;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
100 King 24000
SQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select employee_id,last_name%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER
--------------------------------------------------------------------------- ------------- ---------- ------------
select employee_id,last_name,salary from employees where employee_id=100 ftsw1cm8qh6nq 3513260694 0
--创建AWR快照
SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
--再清空共享池
SQL> alter system flush shared_pool;
System altered.
SQL> select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select employee_id,last_name%';
no rows selected
--找不到该SQL的执行计划
SQL> select * from table(dbms_xplan.display_cursor('ftsw1cm8qh6nq',0,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID: ftsw1cm8qh6nq, child number: 0 cannot be found
--这个时候就需要使用dbms_xplan.display_awr('sql_id')来查看目标SQL的历史执行计划了
SQL> select * from table(dbms_xplan.display_awr('ftsw1cm8qh6nq'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID ftsw1cm8qh6nq
--------------------
select employee_id,last_name,salary from employees where employee_id=100
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 40 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SQL_ID ftsw1cm8qh6nq
--------------------
select employee_id,last_name,salary from employees where employee_id=100
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 16 | 1 (0)| 00:00:01 |
| 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------
31 rows selected.
但是使用dbms_xplan.display_awr('sql_id')获取的执行计划缺少谓词信息。
当然也可以调用awr脚本来生成目标SQL的执行计划:
SQL> conn hr/hr
Connected.
SQL> alter system flush shared_pool;
System altered.
SQL> select employee_id,last_name,salary from employees where employee_id=100;
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
100 King 24000
SQL> select sql_text,sql_id from v$sql where sql_text like 'select employee_id,last_name%';
SQL_TEXT SQL_ID
--------------------------------------------------------------------------- -------------
select employee_id,last_name,salary from employees where employee_id=100 ftsw1cm8qh6nq
--将目标SQL标记为AWR必须抓取的SQL
SQL> exec dbms_workload_repository.add_colored_sql('ftsw1cm8qh6nq');
PL/SQL procedure successfully completed.
SQL> select * from dba_hist_colored_sql;
DBID SQL_ID CREATE_TIME
---------- ------------- ------------------
3518858099 ftsw1cm8qh6nq 19-MAY-17
SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
--生成目标SQL的AWR报告
SQL> @?/rdbms/admin/awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3518858099 JHSITGG1 1 jhsitgg1
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 3518858099 1 JHSITGG1 jhsitgg1 D2-LZY245
4261324778 1 JHSIT01 jhsit01 D2-CIRCSIT51
Using 3518858099 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
jhsitgg1 JHSITGG1 3280 19 May 2017 00:00 1
3291 19 May 2017 18:24 2
3292 19 May 2017 18:47 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3291
Begin Snapshot Id specified: 3291
Enter value for end_snap: 3292
End Snapshot Id specified: 3292
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: ftsw1cm8qh6nq
SQL ID specified: ftsw1cm8qh6nq
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_3291_3292.html. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name awrsqlrpt_1_3291_3292.html
打开生成的AWR报告,就可以看到执行计划了,而且还有物理读、逻辑读等统计信息。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-2139640/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28536251/viewspace-2139640/