【最佳化】SPM(上）自動捕獲sql執行計劃並演進
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'
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
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
| 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
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
| 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"
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2126495/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【最佳化】SPM(下）手工捕獲sql執行計劃並檢視演進情況SQL
- 使用SPM和STA進行固定執行計劃
- 【SPM】Oracle如何固定執行計劃Oracle
- 獲取SQL執行計劃SQL
- 微課sql最佳化(9)、如何獲取執行計劃SQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- SQL最佳化 —— 讀懂執行計劃SQL
- RMAN自動執行計劃
- Oracle 獲取SQL執行計劃方法OracleSQL
- 獲取SQL執行計劃的方式：SQL
- 透過SPM手動新增執行計劃到baseLine
- 介紹幾種獲取SQL執行計劃的方法（上）SQL
- 執行計劃-1：獲取執行計劃
- 控制執行計劃之-SPM BASELINE（六）
- 控制執行計劃之-SPM BASELINE（五）
- 控制執行計劃之-SPM BASELINE（四）
- 控制執行計劃之-SPM BASELINE（三）
- 控制執行計劃之-SPM BASELINE（二）
- 控制執行計劃之-SPM BASELINE（一）
- ORACLE 11G 使用SPM來調整SQL語句的執行計劃OracleSQL
- sql 執行計劃SQL
- Oracle手動固定SQL執行計劃OracleSQL
- 分析執行計劃最佳化SQLORACLE的執行計劃(轉)SQLOracle
- SQL最佳化案例-從執行計劃定位SQL問題（三）SQL
- oracle sqlprofile 固定執行計劃，並遷移執行計劃OracleSQL
- MySQL 5.7獲取指定執行緒正在執行SQL的執行計劃資訊MySql執行緒
- 根據SQL Id獲得SQL語句的執行計劃SQL
- oracle explain plan for獲取執行計劃並不可靠.OracleAI
- Oracle sql執行計劃OracleSQL
- SQL Server執行計劃SQLServer
- SQL的執行計劃SQL
- SQL執行計劃分析SQL
- sql最佳化：使用儲存提綱穩定sql執行計劃SQL
- 微課sql最佳化(11) 、如何檢視執行計劃SQL
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- 分割槽索引（Partition Index）與SQL執行計劃（上）索引IndexSQL
- 透過分析SQL語句的執行計劃最佳化SQL(總結)SQL
- 獲取oracle sql語句詳細些執行計劃OracleSQL