Oracle檢視執行計劃（四）

如果目標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報告，就可以看到執行計劃了，而且還有物理讀、邏輯讀等統計資訊。