我们在awr report中，往往能看到多个版本的执行计划，用不同的PLAN_HASH_VALUE表示。那么。有时候，我们发现sql生产了一个新的执行计划，如何让在不改变sql，不加hint的情况下，使用老的执行计划呢？
我们知道spm可以实现这样的功能，spm除了可以设置optimizer_capture_sql_plan_baselines为true（session级）和dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE从library cache中load到baseline外，我们还可以从sqlset中load，我们可以用LOAD_PLANS_FROM_SQLSET。
看下面一个例子：
1.创建用于测试的表：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
SQL> create table t1 as select * from dba_objects; Table created. SQL> SQL> insert into t1 select * from t1; 65439 rows created. Elapsed: 00:00:02.65 SQL> / 130878 rows created. Elapsed: 00:00:04.55 SQL> commit; Commit complete. Elapsed: 00:00:00.00 SQL> SQL> update t1 set object_id=rownum; 261756 rows updated. Elapsed: 00:00:13.35 SQL> SQL> SQL> exec dbms_workload_repository.create_snapshot(); PL/SQL procedure successfully completed. Elapsed: 00:00:05.54 SQL> SQL> SQL> select /*+ HJM */ object_name from t1 where object_id=20; OBJECT_NAME -------------------------------------------------------------------------------- IND$ 1 row selected. Elapsed: 00:00:00.07 SQL> exec dbms_workload_repository.create_snapshot(); PL/SQL procedure successfully completed. Elapsed: 00:00:01.67 SQL> SQL> SQL> SQL> select sql_id,sql_text from dba_hist_sqltext where sql_text like '%HJM%'; SQL_ID SQL_TEXT ------------- -------------------------------------------------------------------------------- chmc32x4rt821 select /*+ HJM */ object_name from t1 where object_id=20 1 row selected. Elapsed: 00:00:00.08 SQL> SQL> select sql_id,plan_hash_value from dba_hist_sql_plan where sql_id='chmc32x4rt821' 2 group by sql_id,plan_hash_value; SQL_ID PLAN_HASH_VALUE ------------- --------------- chmc32x4rt821 3617692013 1 row selected. Elapsed: 00:00:00.04 SQL> SQL> select sql_id,plan_hash_value,operation,options from dba_hist_sql_plan where sql_id='chmc32x4rt821'; SQL_ID PLAN_HASH_VALUE OPERATION OPTIONS ------------- --------------- ------------------------------ ------------------------------ chmc32x4rt821 3617692013 SELECT STATEMENT chmc32x4rt821 3617692013 TABLE ACCESS FULL 2 rows selected. Elapsed: 00:00:00.16 SQL> |
我们看到sql是走全表扫描的，当前只有一个执行计划。
2.然后我们加上索引，产生第二个执行计划：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
SQL> create index idx on t1(object_id); Index created. Elapsed: 00:00:00.82 SQL> SQL> SQL> !date Mon Dec 3 21:32:56 CST 2012 SQL> exec dbms_workload_repository.create_snapshot(); PL/SQL procedure successfully completed. Elapsed: 00:00:00.90 SQL> select /*+ HJM */ object_name from t1 where object_id=20; OBJECT_NAME ------------------------------ IND$ 1 row selected. Elapsed: 00:00:00.00 SQL> exec dbms_workload_repository.create_snapshot(); PL/SQL procedure successfully completed. Elapsed: 00:00:00.96 SQL> SQL> select sql_id,plan_hash_value from dba_hist_sql_plan where sql_id='chmc32x4rt821'; SQL_ID PLAN_HASH_VALUE ------------- --------------- chmc32x4rt821 997549881 chmc32x4rt821 997549881 chmc32x4rt821 997549881 chmc32x4rt821 3617692013 chmc32x4rt821 3617692013 5 rows selected. Elapsed: 00:00:00.00 SQL> SQL> select sql_id,plan_hash_value from dba_hist_sql_plan where sql_id='chmc32x4rt821' 2 group by sql_id,plan_hash_value; SQL_ID PLAN_HASH_VALUE ------------- --------------- chmc32x4rt821 997549881 chmc32x4rt821 3617692013 2 rows selected. Elapsed: 00:00:00.01 SQL> --我们看到除了刚刚的一个3617692013外，有了另外一个997549881的执行计划。我们来看看这个执行计划。 SQL> select sql_id,plan_hash_value,operation,options from dba_hist_sql_plan where plan_hash_value='997549881'; SQL_ID PLAN_HASH_VALUE OPERATION OPTIONS ------------- --------------- ------------------------------ ------------------------------ chmc32x4rt821 997549881 SELECT STATEMENT chmc32x4rt821 997549881 TABLE ACCESS BY INDEX ROWID chmc32x4rt821 997549881 INDEX RANGE SCAN 3 rows selected. Elapsed: 00:00:00.00 SQL> |
因此，问题是，如果我们希望还是用之前的第一个的执行计划，还是走全表扫描，（如exadata中走FTS的smart scan）但是又不可以改变SQL语句，我们应怎么做？
3. 答案是，我们可以用spm，将awr中的历史hash plan，load到baseline中。
3.1 首先，临时创建一个sqlset。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
--先清理一下，看看原来有没有已经存在的叫mysts01的sqlset SQL> exec DBMS_SQLTUNE.DROP_SQLSET('mysts01'); BEGIN DBMS_SQLTUNE.DROP_SQLSET('mysts01'); END; * ERROR at line 1: ORA-13754: "SQL Tuning Set" "mysts01" does not exist for user "TEST". ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 13171 ORA-06512: at "SYS.DBMS_SQLTUNE", line 4409 ORA-06512: at line 1 Elapsed: 00:00:00.22 --语句报错，说明没有，我们来建立这个临时的sqlset： SQL> SQL> exec DBMS_SQLTUNE.CREATE_SQLSET('mysts01','SQL Tuning Set for loading plan into SQL Plan Baseline'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.06 SQL> |
3.2 从awr报告中的执行计划，load进sqlset。
3.2.1 首先，看看awr报告中的snap和对应的时间点
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. Elapsed: 00:00:00.00 SQL> select sql_id,plan_hash_value,timestamp from dba_hist_sql_plan where sql_id='chmc32x4rt821'; SQL_ID PLAN_HASH_VALUE TIMESTAMP ------------- --------------- ------------------- chmc32x4rt821 997549881 2012-12-03 21:33:09 chmc32x4rt821 997549881 2012-12-03 21:33:09 chmc32x4rt821 997549881 2012-12-03 21:33:09 chmc32x4rt821 3617692013 2012-12-03 21:27:54 chmc32x4rt821 3617692013 2012-12-03 21:27:54 5 rows selected. Elapsed: 00:00:00.01 SQL> SQL> SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM dba_hist_snapshot ORDER BY END_INTERVAL_TIME DESC; SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME ---------- -------------------------------------------------- -------------------------------------------------- 47 03-DEC-12 09.32.59.029 PM 03-DEC-12 09.33.34.392 PM 46 03-DEC-12 09.29.30.697 PM 03-DEC-12 09.32.59.029 PM 45 03-DEC-12 09.28.01.295 PM 03-DEC-12 09.29.30.697 PM 44 03-DEC-12 09.27.35.200 PM 03-DEC-12 09.28.01.295 PM 43 03-DEC-12 09.24.33.000 PM 03-DEC-12 09.27.35.200 PM 42 18-NOV-12 04.00.54.374 PM 18-NOV-12 05.00.06.798 PM 41 18-NOV-12 03.00.41.415 PM 18-NOV-12 04.00.54.374 PM 40 18-NOV-12 02.00.29.394 PM 18-NOV-12 03.00.41.415 PM 39 18-NOV-12 01.05.17.745 PM 18-NOV-12 02.00.29.394 PM 38 18-NOV-12 12.53.55.000 PM 18-NOV-12 01.05.17.745 PM 10 rows selected. Elapsed: 00:00:00.00 SQL> …… |
我们看到全表扫描的那个hash value 3617692013 大约时间是在21:27:54，也就是END_INTERVAL_TIME的snap id 43到44之间。
3.2.2 根据对应的snap id，将awr中的执行计划，load到sqlset
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
-- load之前先检查一下sqlset的情况，我们看到，load之前，是空。 SQL> SELECT first_load_time, 2 executions as execs, 3 parsing_schema_name, 4 elapsed_time / 1000000 as elapsed_time_secs, 5 cpu_time / 1000000 as cpu_time_secs, 6 buffer_gets, 7 disk_reads, 8 direct_writes, 9 rows_processed, 10 fetches, 11 optimizer_cost, 12 sql_plan, 13 plan_hash_value, 14 sql_id, 15 sql_text 16 FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'mysts01')); no rows selected Elapsed: 00:00:01.43 SQL> --利用dbms_sqltune.select_workload_repository，将awr的数据，load到sqlset中。 SQL> DECLARE 2 cur sys_refcursor; 3 BEGIN 4 OPEN cur FOR 5 SELECT VALUE(P) 6 FROM TABLE( 7 dbms_sqltune.select_workload_repository 8 (begin_snap=>43, --老执行计划起始的snap id 9 end_snap=>44, --老执行计划结束的snap id 10 basic_filter=>'sql_id = ''chmc32x4rt821''', --老执行计划起始的sql id 11 attribute_list=>'ALL') 12 ) p; 13 DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'mysts01', populate_cursor=>cur); 14 CLOSE cur; 15 END; 16 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.36 SQL> --load 完之后，检查一下，发现已经存在。load成功。而且大致的我们也可以看到是'TABLE ACCESS', 'FULL'什么什么的。 SQL> SELECT first_load_time, 2 executions as execs, 3 parsing_schema_name, 4 elapsed_time / 1000000 as elapsed_time_secs, 5 cpu_time / 1000000 as cpu_time_secs, 6 buffer_gets, 7 disk_reads, 8 direct_writes, 9 rows_processed, 10 fetches, 11 optimizer_cost, 12 sql_plan, 13 plan_hash_value, 14 sql_id, 15 sql_text 16 FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'mysts01')); FIRST_LOAD_TIME EXECS PARSING_SCHEMA_NAME ELAPSED_TIME_SECS CPU_TIME_SECS BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES OPTIMIZER_COST ------------------- ---------- ------------------------------ ----------------- ------------- ----------- ---------- ------------- -------------- ---------- -------------- SQL_PLAN(STATEMENT_ID, PLAN_ID, TIMESTAMP, REMARKS, OPERATION, OPTIONS, OBJECT_NODE, OBJECT_OWNER, OBJECT_NAME, OBJECT_ALIAS, OBJECT_INSTANCE, OBJECT_TYPE, OPTIMIZER, SEARCH_COLUMNS, ID, PARENT_ID, DEPTH, POSITION, COST, CARDINALITY, BYTES, OTHER_TAG, PARTITION_START, PARTITION_STOP, PARTITION_ID, DISTRIBUTION, CPU_COST, IO_COST, TEMP_SPACE, ACCESS_PREDICATES, FILTER_PREDICATES, PROJECTION, TIME, QBLOCK_NAME, OTHER_XML) ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PLAN_HASH_VALUE SQL_ID SQL_TEXT --------------- ------------- -------------------------------------------------------------------------------- 1 TEST .028703 .015997 3848 0 01 2 1036 SQL_PLAN_TABLE_TYPE(SQL_PLAN_ROW_TYPE(NULL, NULL, '2012-12-03 21:27:54', NULL, 'SELECT STATEMENT', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'ALL_ROWS', 0, 0, NULL, 0, 1036, 1036, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), SQL_PLAN_ROW_TYPE(NULL, NULL, '2012-12-03 21:27:54', NULL, 'TABLE ACCESS', 'FULL', NULL, 'TEST', 'T1', 'T1@SEL$1', NULL, 'TABLE', NULL, 0, 1, 0, 1, 1, 1036, 42, 3318, NULL, NULL, NULL, NULL, NULL, 102872359, 1030, NULL, NULL, NULL, NULL, 13, 'SEL$1', '<other_xml><info type="db_version">11.2.0.1</info><info type="parse_schema"><![C')) 3617692013 chmc32x4rt821 select /*+ HJM */ object_name from t1 where object_id=20 1 row selected. Elapsed: 00:00:00.01 SQL> |
3.3 将sqlset导入到baseline：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
--先检查一下baseline中的信息，load之前，我们发现为空。 SQL> SELECT * FROM dba_sql_plan_baselines ; no rows selected Elapsed: 00:00:00.08 SQL> --利用DBMS_SPM.LOAD_PLANS_FROM_SQLSET，将sqlset导入到baseline。 SQL> DECLARE 2 my_plans pls_integer; 3 BEGIN 4 my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( 5 sqlset_name => 'mysts01', 6 basic_filter=>'plan_hash_value = ''3617692013''' 7 ); 8 END; 9 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.71 SQL> --导入完成后，发现已经存在。 SQL> select sql_text,enabled,accepted,fixed from dba_sql_plan_baselines; SQL_TEXT ENA ACC FIX -------------------------------------------------------------------------------- --- --- --- select /*+ HJM */ object_name from t1 where object_id=20 YES YES NO 1 row selected. Elapsed: 00:00:00.01 SQL> |
4. 我们现在可以来试试，发现语句已经是走之前第一个执行计划了，而且，我们没有改语句。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SQL> set autotrace trace exp stat SQL> select /*+ HJM */ object_name from t1 where object_id=20; 1 row selected. Elapsed: 00:00:00.16 Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 783 | 61857 | 263 (1)| 00:00:04 | |* 1 | TABLE ACCESS FULL| T1 | 783 | 61857 | 263 (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OBJECT_ID"=20) Note ----- - SQL plan baseline "SQL_PLAN_dxzdvaabzc29vdbd90e8e" used for this statement <<<<<注意此处，说明sql用到了是某baseline。 Statistics ---------------------------------------------------------- 428 recursive calls 40 db block gets 3928 consistent gets 1 physical reads 6460 redo size 427 bytes sent via SQL*Net to client 420 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SQL> SQL> set autotrace off SQL> |
5.打扫战场。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
--我们发现，刚刚用set autotrace检查之后,baseline中出现了2行信息,数据库AUTO-CAPTURE了一行(估计是为了以后演化只用): SQL> select sql_text,enabled,accepted,fixed,ORIGIN,SQL_HANDLE,PLAN_NAME from dba_sql_plan_baselines 2 / SQL_TEXT ENA ACC FIX ORIGIN SQL_HANDLE PLAN_NAME ------------------------------------------------------------ --- --- --- -------------- ------------------------------ ------------------------------ select /*+ HJM */ object_name from t1 where object_id=20 YES NO NO AUTO-CAPTURE SYS_SQL_defdbb5297f6093b SQL_PLAN_dxzdvaabzc29vcbb4c70d select /*+ HJM */ object_name from t1 where object_id=20 YES YES NO MANUAL-LOAD SYS_SQL_defdbb5297f6093b SQL_PLAN_dxzdvaabzc29vdbd90e8e 2 rows selected. Elapsed: 00:00:00.00 SQL> --我们可以将这条不要的删除，另外，将原来的那条fixed设置成yes。便于永久固化。 SQL> DECLARE 2 i NATURAL; 3 BEGIN 4 i := dbms_spm.alter_sql_plan_baseline( 5 'SYS_SQL_defdbb5297f6093b', 6 'SQL_PLAN_dxzdvaabzc29vdbd90e8e', 7 attribute_name => 'FIXED', 8 attribute_value => 'YES'); 9 dbms_output.put_line(i); 10 END; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.04 SQL> SQL> declare 2 v number; 3 begin 4 v:=dbms_spm.DROP_SQL_PLAN_BASELINE('SYS_SQL_defdbb5297f6093b','SQL_PLAN_dxzdvaabzc29vcbb4c70d'); 5 end; 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.02 SQL> SQL> select sql_text,enabled,accepted,fixed,ORIGIN,SQL_HANDLE,PLAN_NAME from dba_sql_plan_baselines; SQL_TEXT ENA ACC FIX ORIGIN SQL_HANDLE PLAN_NAME ------------------------------------------------------------ --- --- --- -------------- ------------------------------ ------------------------------ select /*+ HJM */ object_name from t1 where object_id=20 YES YES YES MANUAL-LOAD SYS_SQL_defdbb5297f6093b SQL_PLAN_dxzdvaabzc29vdbd90e8e 1 row selected. Elapsed: 00:00:00.00 SQL> --另外，还可以清理到之前临时建立的sqlset： SQL> exec DBMS_SQLTUNE.DROP_SQLSET('mysts01'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.07 SQL> |
参考：How to Load SQL Plans into SQL Plan Management (SPM) from the Automatic Workload Repository (AWR) (Doc ID 789888.1)
3条评论
好帖，学习了！
试验了一遍，结果还是走的第2个执行计划
自己再消化消化。。。
re 無限追云：cursor sharing是否设置为exact了？如果是force，你需要导绑定变量的那个，且在测试执行计划的时候，也用绑定变量的方式测试。btw，similar在11g中过期。