Spm的作用及應用場合：
1、 Spm的主要作用：透過管理sql執行計劃， 提高和穩定系統效能。
2、應用場合：資料庫升級；不間斷的系統資料改變；業務系統新模組的開發。

自動捕獲sql執行計劃並演進

1）查詢baseline資訊：

SYS@ORA11GR2>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines ;

no rows selected

2）查詢引數optimizer_capture_sql_plan_baselines顯示是否開啟自動捕獲sql_plan

修改引數開啟自動捕獲執行計劃（自動捕獲特指重複執行的sql語句，單次執行捕獲不了）

SYS@ORA11GR2>show parameter optimizer_capture_sql_plan_baselines

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_capture_sql_plan_baselines boolean FALSE

更改引數：

SYS@ORA11GR2>alter system set optimizer_capture_sql_plan_baselines=true;

System altered.

SYS@ORA11GR2>show parameter optimizer_capture_sql_plan_baselines

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

optimizer_capture_sql_plan_baselines Boolean TRUE

3）建立表：

HR@ORA11GR2>create table employees_bak as select distinct last_name from employees;

Table created.

HR@ORA11GR2>

查詢sql模擬業務（一）

1. 第一次執行

HR@ORA11GR2>select * from employees_bak where last_name='Vargas';

LAST_NAME

-------------------------

Vargas

2.查詢shared pool中的關於這條sql的資訊：（以sys使用者執行）

SYS@ORA11GR2>select sql_id,sql_text from v$sql where sql_text like 'select * from employees_bak where last_name=%';

SQL_ID SQL_TEXT

------------- --------------------------------------------------

99yvub515tr63 select * from employees_bak where last_name='Varga s'

3. 查詢baseline資訊

SYS@ORA11GR2>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';

no rows selected

查詢sql模擬業務（二）

1.第二次執行：

SYS@ORA11GR2>conn hr/hr

Connected.

HR@ORA11GR2>select * from employees_bak where last_name='Vargas';

LAST_NAME

-------------------------

Vargas

HR@ORA11GR2>conn / as sysdba

Connected.

2.查詢shared pool中的關於這條sql的資訊：（以sys使用者執行）

SYS@ORA11GR2>select sql_id,sql_text from v$sql where sql_text like 'select * from employees_bak where last_name=%';

SQL_ID SQL_TEXT

------------- -----------------------------------------------------

99yvub515tr63 select * from employees_bak where last_name='Vargas'

同樣的sql，其sql_sid相同；

3.查詢baseline中的資訊

SYS@ORA11GR2>col sql_handle for a20

SYS@ORA11GR2>col sql_text for a15

SYS@ORA11GR2>col plan_name for a15

SYS@ORA11GR2>col created for a10

SYS@ORA11GR2>col last_modified for a10

SYS@ORA11GR2>col last_executed for a10

SYS@ORA11GR2>col last_verified for a10

SYS@ORA11GR2>col version for a10

SYS@ORA11GR2>set pagesize 1000

SYS@ORA11GR2>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';

SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION CREATED LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX

-------------------- ----------------------------------------------------- --------------- -------------- ---------- ----------

SQL_1c32abf260291c48 select * from employees_bak where last_name='Vargas' SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0 13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO

9h2k728c6c83284 04.26.02.0 04.26.02.0 04.26.02.0

00000 PM 00000 PM 00000 PM

4.驗證檢視到的baseline裡執行計劃

HR@ORA11GR2>set autot trace

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

HR@ORA11GR2>conn / as sysdba

Connected.

SYS@ORA11GR2>grant PLUSTRACE to hr;

grant PLUSTRACE to hr

*

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

解決：

SYS@ORA11GR2>@$ORACLE_HOME/sqlplus/admin/plustrce.sql

SYS@ORA11GR2>

SYS@ORA11GR2>drop role plustrace;

drop role plustrace

*

ERROR at line 1:

ORA-01919: role 'PLUSTRACE' does not exist

SYS@ORA11GR2>create role plustrace;

Role created.

SYS@ORA11GR2>grant select on v_$sesstat to plustrace;

Grant succeeded.

SYS@ORA11GR2>grant select on v_$statname to plustrace;

Grant succeeded.

SYS@ORA11GR2>grant select on v_$mystat to plustrace;

Grant succeeded.

SYS@ORA11GR2>grant plustrace to dba with admin option;

Grant succeeded.

SYS@ORA11GR2>set echo off

再授權給hr使用者：

SYS@ORA11GR2>grant plustrace to hr;

Grant succeeded.

SYS@ORA11GR2>conn hr/hr

Connected.

HR@ORA11GR2>set autot trace

HR@ORA11GR2>select * from employees_bak where last_name='Vargas';

Execution Plan

----------------------------------------------------------

Plan hash value: 3604099949

--------------------------------------------------------------------------------

|

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:0

1 |

|* 1 | TABLE ACCESS FULL| EMPLOYEES_BAK | 1 | 14 | 3 (0)| 00:00:0

1 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("LAST_NAME"='Vargas')

Note

-----

- dynamic sampling used for this statement (level=2)

- SQL plan baseline "SQL_PLAN_1scpby9h2k728c6c83284" used for this statement

Statistics

----------------------------------------------------------

39 recursive calls

0 db block gets

32 consistent gets

0 physical reads

0 redo size

531 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

1 rows processed

5.對employees_bak表進行最佳化，建立索引並重新執行sql

HR@ORA11GR2>set autot off;

HR@ORA11GR2>desc employees_bak

Name Null? Type

----------------------------------------- -------- ----------------------------

LAST_NAME NOT NULL VARCHAR2(25)

HR@ORA11GR2>create index idx_employees_bak on employees_bak(last_name);

Index created.

HR@ORA11GR2>select * from employees_bak where last_name='Vargas';

LAST_NAME

-------------------------

Vargas

6.查詢shared pool及baseline中資訊

HR@ORA11GR2>conn / as sysdba

Connected.

——Shared pool中資訊：

SYS@ORA11GR2>select sql_id,sql_text from v$sql where sql_text like 'select * from employees_bak where last_name=%';

SQL_ID SQL_TEXT

--------------------------------------------------------------------------------

99yvub515tr63 select * from employees_bak where last_name='Vargas'

——Baseline資訊：

SYS@ORA11GR2>col sql_handle for a20

SYS@ORA11GR2>col sql_text for a15

SYS@ORA11GR2>col plan_name for a15

SYS@ORA11GR2>col created for a10

SYS@ORA11GR2>col last_modified for a10

SYS@ORA11GR2>col last_executed for a10

SYS@ORA11GR2>col last_verified for a10

SYS@ORA11GR2>col version for a10

SYS@ORA11GR2>set pagesize 1000

SYS@ORA11GR2>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';

SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION

-------------------- --------------- --------------- -------------- ----------

CREATED LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX

---------- ---------- ---------- ---------- --- --- ---

SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0

mployees_bak wh 9h2k728c6c83284

ere last_name='

Vargas'

13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO

04.26.02.0 04.26.02.0 04.26.02.0

00000 PM 00000 PM 00000 PM

SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0

mployees_bak wh 9h2k728d8e34edf

ere last_name='

Vargas'

13-OCT-16 13-OCT-16 YES NO NO

06.53.12.0 06.53.12.0

00000 PM 00000 PM

檢視新的執行計劃SQL_PLAN_1scpby9h2k728d8e34edf並沒有被演進，即還沒有被放到baseline裡供以後複用。

7.演進新的執行計劃

SYS@ORA11GR2>set serveroutput on

SYS@ORA11GR2>set long 10000

SYS@ORA11GR2>declare

2 report clob;

3 begin

4 report := dbms_spm.evolve_sql_plan_baseline(

5 sql_handle => 'SQL_1c32abf260291c48');

6 dbms_output.put_line(report);

7 end;

8 /

-------------------------------------------------------------------------------

Evolve SQL Plan Baseline

Report

-------------------------------------------------------------------------

Inputs:

SQL_HANDLE = SQL_1c32abf260291c48

PLAN_NAME =

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY = YES

COMMIT = YES

Plan:

SQL_PLAN_1scpby9h2k728d8e34edf

------------------------------------

Plan was

verified: Time used .02 seconds.

Plan passed performance criterion: 3 times

better than baseline plan.

Plan was changed to an accepted plan.

Baseline Plan Test Plan Stats Ratio

------------- --------- -----------

Execution Status:

COMPLETE COMPLETE

Rows Processed: 1

1

Elapsed Time(ms): .022 .013 1.69

CPU Time(ms): 0 0

Buffer Gets:

3 1 3

Physical Read Requests: 0

0

Physical Write Requests: 0 0

Physical Read

Bytes: 0 0

Physical Write Bytes:

0 0

Executions: 1

1

-----------------------------------------------------------------------------

Report

Summary

------------------------------------------------------------------------

Number of plans verified: 1

Number of plans accepted: 1

PL/SQL procedure successfully completed.

8.再次檢視baseline裡的執行計劃資訊

SYS@ORA11GR2>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';

SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION

-------------------- --------------- --------------- -------------- ----------

CREATED LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX

---------- ---------- ---------- ---------- --- --- ---

SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0

mployees_bak wh 9h2k728c6c83284

ere last_name='

Vargas'

13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO

04.26.02.0 04.26.02.0 04.26.02.0

00000 PM 00000 PM 00000 PM

SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0

mployees_bak wh 9h2k728d8e34edf

ere last_name='

Vargas'

13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO

06.53.12.0 07.05.24.0 07.05.24.0

00000 PM 00000 PM 00000 PM

檢視執行計劃SQL_PLAN_1scpby9h2k728d8e34edf已經被演進到baseline了

9.再透過執行sql語句驗證實際執行的執行計劃是否是baseline裡最優的執行計劃:

SYS@ORA11GR2>conn hr/hr

Connected.

HR@ORA11GR2>set autot trace

HR@ORA11GR2>select * from employees_bak where last_name='Vargas';

Execution Plan

----------------------------------------------------------

Plan hash value: 3689813232

--------------------------------------------------------------------------------

|

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 14 | 1 (0)| 00:0

0:01 |

|* 1 | INDEX RANGE SCAN| IDX_EMPLOYEES_BAK | 1 | 14 | 1 (0)| 00:0

0:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("LAST_NAME"='Vargas')

Note

-----

- dynamic sampling used for this statement (level=2)

- SQL plan baseline "SQL_PLAN_1scpby9h2k728d8e34edf" used for this statement

Statistics

----------------------------------------------------------

29 recursive calls

15 db block gets

25 consistent gets

0 physical reads

2984 redo size

531 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

HR@ORA11GR2>set autot off;

驗證sql語句執行計劃已經走索引了，且對應了baseline裡最優的執行計劃SQL plan baseline "SQL_PLAN_1scpby9h2k728d8e34edf"

【最佳化】SPM(上）自動捕獲sql執行計劃並演進

自動捕獲sql執行計劃並演進

相關文章