【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程序员面试笔试宝典》，作者：李华荣。
