【DB筆試面試610】在Oracle中，SPM的使用有哪些步驟？
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在Oracle中，SPM的使用有哪些步驟？
♣
答案部分
取消自動捕獲，也可以不取消自動捕捉：
show parameter baselines ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;
執行：
[ZHLHRSPMDB2:oracle]:/oracle>ORACLE_SID=dlhr [ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 15:47:55 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SYS@dlhr> conn lhr/lhr Connected. LHR@dlhr> LHR@dlhr> LHR@dlhr> LHR@dlhr> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production LHR@dlhr> show parameter baselines NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean TRUE optimizer_use_sql_plan_baselines boolean TRUE LHR@dlhr> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE; System altered.
–創建表並插入數據，腳本：
CREATE TABLE tb_spm_test_lhr ( id NUMBER, description VARCHAR2(50) ); DECLARE TYPE t_tab IS TABLE OF tb_spm_test_lhr%ROWTYPE; l_tab t_tab := t_TAB(); BEGIN FOR i IN 1 .. 10000 LOOP l_tab.extend; l_tab(l_tab.last).id := i; l_tab(l_tab.last).description := 'Description for ' || i; END LOOP; FORALL i IN l_tab.first .. l_tab.last INSERT INTO tb_spm_test_lhr VALUES l_tab(i); COMMIT; END; / EXEC DBMS_STATS.gather_table_stats(USER, 'tb_spm_test_lhr', cascade=>TRUE); set autot trace SELECT description FROM tb_spm_test_lhr WHERE id = 100;
開始執行：
LHR@dlhr> CREATE TABLE tb_spm_test_lhr ( 2 id NUMBER, 3 description VARCHAR2(50) 4 ); Table created. LHR@dlhr> LHR@dlhr> DECLARE 2 TYPE t_tab IS TABLE OF tb_spm_test_lhr%ROWTYPE; 3 l_tab t_tab := t_TAB(); 4 BEGIN 5 FOR i IN 1 .. 10000 LOOP 6 l_tab.extend; 7 l_tab(l_tab.last).id := i; 8 l_tab(l_tab.last).description := 'Description for ' || i; 9 END LOOP; 10 11 FORALL i IN l_tab.first .. l_tab.last 12 INSERT INTO tb_spm_test_lhr VALUES l_tab(i); 13 14 COMMIT; 15 END; 16 / PL/SQL procedure successfully completed. LHR@dlhr> set autot trace LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 2196561629 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 40 | 13 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 40 | 13 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 4 recursive calls 0 db block gets 94 consistent gets 0 physical reads 0 redo size 546 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
獲取剛才查詢的SQL_ID:
set autot off col SQL_TEXT format a100 select distinct a.SQL_ID,a.SQL_TEXT from v$sql a WHERE a.SQL_TEXT like '%SELECT description FROM tb_spm_test_lhr WHERE id = 100%' and a.SQL_TEXT not like '%v$sql%' AND sql_text NOT LIKE '%EXPLAIN%'; LHR@dlhr> set autot off LHR@dlhr> col SQL_TEXT format a100 LHR@dlhr> select distinct a.SQL_ID,a.SQL_TEXT from v$sql a 2 WHERE a.SQL_TEXT like '%SELECT description FROM tb_spm_test_lhr WHERE id = 100%' 3 and a.SQL_TEXT not like '%v$sql%' 4 AND sql_text NOT LIKE '%EXPLAIN%'; SQL_ID SQL_TEXT ------------- ---------------------------------------------------------------------------------------------------- garkwg3yy2ram SELECT description FROM tb_spm_test_lhr WHERE id = 100 ----使用SQL_ID 從cursor cache中手工捕獲執行計劃： SET SERVEROUTPUT ON DECLARE l_plans_loaded PLS_INTEGER; BEGIN l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache( sql_id => '&sql_id'); DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded); END; / -- --使用DBA_SQL_PLAN_BASELINES視圖查看SPM 資訊： col sql_handle for a35 col plan_name for a35 set lin 300 SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE '%tb_spm_test_lhr%' AND sql_text NOT LIKE'%dba_sql_plan_baselines%'; --刷新Share Pool，使下次SQL 執行時必須進行硬解析： ALTER SYSTEM FLUSH SHARED_POOL; LHR@dlhr> SET SERVEROUTPUT ON LHR@dlhr> DECLARE 2 l_plans_loaded PLS_INTEGER; 3 BEGIN 4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache( 5 sql_id => '&sql_id'); 6 DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded); 7 END; 8 / Enter value for sql_id: garkwg3yy2ram old 5: sql_id => '&sql_id'); new 5: sql_id => 'garkwg3yy2ram'); Plans Loaded: 1 PL/SQL procedure successfully completed. LHR@dlhr> col sql_handle for a35 LHR@dlhr> col plan_name for a35 LHR@dlhr> set lin 300 LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted 2 FROM dba_sql_plan_baselines 3 WHERE sql_text LIKE '%tb_spm_test_lhr%' 4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%'; SQL_HANDLE PLAN_NAME ENA ACC ----------------------------------- ----------------------------------- --- --- SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES LHR@dlhr> ALTER SYSTEM FLUSH SHARED_POOL; System altered. LHR@dlhr> set autot trace SELECT description FROM tb_spm_test_lhr WHERE id = 100; LHR@dlhr> Execution Plan ---------------------------------------------------------- Plan hash value: 2196561629 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 40 | 13 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 40 | 13 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) Note ----- - dynamic sampling used for this statement (level=2) - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement Statistics ---------------------------------------------------------- 555 recursive calls 16 db block gets 667 consistent gets 0 physical reads 3056 redo size 546 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 32 sorts (memory) 0 sorts (disk) 1 rows processed --創建索引，收集統計資訊，並查詢相同的SQL: CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id); EXEC DBMS_STATS.gather_table_stats(USER,'tb_spm_test_lhr', cascade=>TRUE); set autot trace SELECT description FROM tb_spm_test_lhr WHERE id = 100; LHR@dlhr> CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id); Index created. LHR@dlhr> EXEC DBMS_STATS.gather_table_stats(USER,'tb_spm_test_lhr', cascade=>TRUE); PL/SQL procedure successfully completed. LHR@dlhr> LHR@dlhr> LHR@dlhr> LHR@dlhr> set autot trace LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 2196561629 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 25 | 13 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) Note ----- - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement Statistics ---------------------------------------------------------- 640 recursive calls 39 db block gets 493 consistent gets 2 physical reads 12268 redo size 546 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 10 sorts (memory) 0 sorts (disk) 1 rows processed --這裡我們創建了索引，但是這裡還是走的全表掃描，這裡使用索引明顯才是最優的方案。 --查看SPM 視圖： set autot off col sql_handle for a35 col plan_name for a35 set lin 300 SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE '%tb_spm_test_lhr%' AND sql_text NOT LIKE'%dba_sql_plan_baselines%'; LHR@dlhr> set autot off LHR@dlhr> col sql_handle for a35 LHR@dlhr> col plan_name for a35 LHR@dlhr> set lin 300 LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted 2 FROM dba_sql_plan_baselines 3 WHERE sql_text LIKE '%tb_spm_test_lhr%' 4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%'; SQL_HANDLE PLAN_NAME ENA ACC ----------------------------------- ----------------------------------- --- --- SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 YES NO --通過BASELINES查詢的結果，可以看到SQL產生了兩條執行計劃，但是最優的執行計劃並沒有被標記為ACCEPT，所以沒有使用。 --下邊我們演化執行計劃：演化就是將cost低的執行計劃標記為accept： LHR@dlhr> SET LONG 10000 LHR@dlhr> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual; Enter value for sql_handle: SQL_4f19d3cf57be7303 old 1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual new 1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_4f19d3cf57be7303') FROM dual DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_4F19D3CF57BE7303') -------------------------------------------------------------------------------- ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- SQL_HANDLE = SQL_4f19d3cf57be7303 PLAN_NAME = TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT = YES Plan: SQL_PLAN_4y6fmtxbvwws38b725570 ------------------------------------ Plan was verified: Time used .018 seconds. Plan passed performance criterion: 15 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): .308 .025 12.32 CPU Time(ms): .164 .015 10.93 Buffer Gets: 45 3 15 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 --再次查看DBA_SQL_PLAN_BASELINES視圖： set autot off col sql_handle for a35 col plan_name for a35 set lin 300 SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE '%tb_spm_test_lhr%' AND sql_text NOT LIKE'%dba_sql_plan_baselines%'; LHR@dlhr> set autot off LHR@dlhr> col sql_handle for a35 LHR@dlhr> col plan_name for a35 LHR@dlhr> set lin 300 LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted 2 FROM dba_sql_plan_baselines 3 WHERE sql_text LIKE '%tb_spm_test_lhr%' 4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%'; SQL_HANDLE PLAN_NAME ENA ACC ----------------------------------- ----------------------------------- --- --- SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 YES YES --再次執行SQL: set autot trace SELECT description FROM tb_spm_test_lhr WHERE id = 100; LHR@dlhr> set autot trace LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 2587945646 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR | 1 | 25 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=100) Note ----- - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws38b725570" used for this statement Statistics ---------------------------------------------------------- 13 recursive calls 14 db block gets 18 consistent gets 0 physical reads 3048 redo size 553 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
這次正確的使用了索引。因為只有標記為ENABLE和ACCEPT的plan才可以被使用。
下面示例將我們的第一個走全表掃描的執行計劃標記為fixed。標記為fixed的執行計劃會被優先使用。FIXED表示優化程式僅考慮標記為FIXED的計劃，而不考慮其它計劃。例如，如果有10個基準線計劃，其中的三個計劃被標記為FIXED，則優化程式將僅使用這三個計劃中的最佳計劃，而忽略其它所有計劃。如果某個SQL計劃基準線至少包含一個已啟用的已修復計劃，則該SQL計劃基準線就是FIXED的。如果在修復的SQL計劃基準線中添加了新計劃，則在手動將這些新計劃聲明為FIXED之前，無法使用這些新計劃。
set autot off select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic')); SET SERVEROUTPUT ON DECLARE l_plans_altered PLS_INTEGER; BEGIN l_plans_altered := DBMS_SPM.alter_sql_plan_baseline( sql_handle => '&sql_handle', plan_name => '&plan_name', attribute_name => 'fixed', attribute_value => 'YES'); DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered); END; / LHR@dlhr> SET SERVEROUTPUT ON LHR@dlhr> DECLARE 2 l_plans_altered PLS_INTEGER; 3 BEGIN 4 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline( 5 sql_handle => '&sql_handle', 6 plan_name => '&plan_name', 7 attribute_name => 'fixed', 8 attribute_value => 'YES'); 9 10 DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered); 11 END; 12 / Enter value for sql_handle: SQL_4f19d3cf57be7303 old 5: sql_handle => '&sql_handle', new 5: sql_handle => 'SQL_4f19d3cf57be7303', Enter value for plan_name: SQL_PLAN_4y6fmtxbvwws3184920d2 old 6: plan_name => '&plan_name', new 6: plan_name => 'SQL_PLAN_4y6fmtxbvwws3184920d2', Plans Altered: 1 PL/SQL procedure successfully completed. --驗證： set autot off col sql_handle for a35 col plan_name for a35 set lin 300 SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE '%tb_spm_test_lhr%' AND sql_text NOT LIKE'%dba_sql_plan_baselines%'; LHR@dlhr> set autot off LHR@dlhr> select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic')); Enter value for sql_handle: SQL_4f19d3cf57be7303 old 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic')) new 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => 'SQL_4f19d3cf57be7303', format => 'basic')) PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SQL handle: SQL_4f19d3cf57be7303 SQL text: SELECT description FROM tb_spm_test_lhr WHERE id = 100 -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_4y6fmtxbvwws3184920d2 Plan id: 407445714 Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD -------------------------------------------------------------------------------- Plan hash value: 2196561629 --------------------------------------------- | Id | Operation | Name | --------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | --------------------------------------------- -------------------------------------------------------------------------------- Plan name: SQL_PLAN_4y6fmtxbvwws38b725570 Plan id: 2339526000 Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE -------------------------------------------------------------------------------- Plan hash value: 2587945646 -------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR | | 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | -------------------------------------------------------- 34 rows selected. LHR@dlhr> set autot off LHR@dlhr> col sql_handle for a35 LHR@dlhr> col plan_name for a35 LHR@dlhr> set lin 300 LHR@dlhr> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed 2 FROM dba_sql_plan_baselines 3 WHERE sql_text LIKE '%tb_spm_test_lhr%' 4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%'; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX ----------------------------------- ----------------------------------- -------------- --- --- --- SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 MANUAL-LOAD YES YES YES SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 AUTO-CAPTURE YES YES NO --再次查看我們之前的SQL: set autot trace SELECT description FROM tb_spm_test_lhr WHERE id = 100; LHR@dlhr> set autot trace LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 2196561629 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 25 | 13 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID"=100) Note ----- - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement Statistics ---------------------------------------------------------- 6 recursive calls 8 db block gets 46 consistent gets 0 physical reads 0 redo size 546 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
這裡已經走了全表掃描，根據前邊的示例，我們知道這裡走索引會更優，但因為我們將走全表掃描的執行計劃設置為fixed，所以優先使用這個執行計劃。
& 說明：
有關固定執行計劃的具體過程及更多的知識可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2107604/
本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。
