本文主要介绍通过hint生成执行计划，然后添加到SQL plan baseline。使用hint方式添加执行计划具体步骤可以参考MOS文章（文档 ID 787692.1），此方法需要你保证SPM中已经存在此SQL语句的基线，才能添加成功，添加之后可以再删除原来的计划，只使用我们用hint生成的计划。
这里我们使用oracle提供的脚本coe_load_sql_baseline.sql来将调整后的计划添加到sql基线，coe_load_sql_baseline.sql脚本执行需要三个参数，源SQL的sql_id，调整后的sql_id，调整后的plan_hash_value，执行过程中会让你输入密码，以此生成stage表并且导出到当前目录，可以不必理会，因为执行计划已经添加到基线。
以简单例子来解释coe_load_sql_baseline.sql使用（使用test用户）
1、创建表spm_test，并建立索引
--SQL> create table spm_test as select * from dba_objects;Table created.SQL> create index idx_spm_test on spm_test(object_id);Index created.
2、执行简单查询，查看sql_id以及执行计划，如下所示SQL使用INDEX RANGE SCAN
SQL> select owner,object_name,created,status from spm_test where object_id = 9589;OWNER OBJECT_NAME CREATED STATUS------------------------------ ------------------------------ ------------ -------PUBLIC DBA_ADVISOR_TEMPLATES 26-NOV-15 VALIDSQL> select * from table(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------SQL_ID 0j3wdgnj1kbkt, child number 0-------------------------------------select owner,object_name,created,status from spm_test where object_id =9589Plan hash value: 3008474106--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 (100)| || 1 | TABLE ACCESS BY INDEX ROWID| SPM_TEST | 1 | 110 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_SPM_TEST | 1 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("OBJECT_ID"=9589)Note------ dynamic sampling used for this statement (level=2)24 rows selected.SQL>
3、调整SQL，添加hint使其走全表扫描
SQL> select /*+full(spm_test)*/ owner,object_name,created,status from spm_test where object_id = 9589;OWNER OBJECT_NAME CREATED STATUS------------------------------ ------------------------------ ------------ -------PUBLIC DBA_ADVISOR_TEMPLATES 26-NOV-15 VALIDSQL> select * from table(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------SQL_ID ak836bfx3z56z, child number 0-------------------------------------select /*+full(spm_test)*/ owner,object_name,created,status fromspm_test where object_id = 9589Plan hash value: 278342638------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 61 (100)| ||* 1 | TABLE ACCESS FULL| SPM_TEST | 1 | 110 | 61 (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("OBJECT_ID"=9589)Note------ dynamic sampling used for this statement (level=2)23 rows selected.SQL>
4、查看此SQL发现无基线
SQL> select * from dba_sql_plan_baselines where sql_text like '%spm_test%';no rows selectedSQL>
5、通过coe_load_sql_baseline.sql将使用全表扫描的计划添加到基线，如下所示，脚本添加完成基线后会将生成的计划导出stage表，并生成dmp文件
SQL> @coe_load_sql_baseline.sqlParameter 1:ORIGINAL_SQL_ID (required)Enter value for 1: 0j3wdgnj1kbkt -- 原始sql的sql_idParameter 2:MODIFIED_SQL_ID (required)Enter value for 2: ak836bfx3z56z --添加hint后的sql_idPLAN_HASH_VALUE AVG_ET_SECS-------------------- --------------------278342638 .012Parameter 3:PLAN_HASH_VALUE (required)Enter value for 3: 278342638 -- 调整后的plan_hash_valueValues passed to coe_load_sql_baseline:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ORIGINAL_SQL_ID: "0j3wdgnj1kbkt"MODIFIED_SQL_ID: "ak836bfx3z56z"PLAN_HASH_VALUE: "278342638"SQL>BEGIN2 IF :sql_text IS NULL THEN3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for original SQL_ID &&original_sql_id. was not found in memory (gv$sqlarea) or AWR (dba_hist_sqltext).');4 END IF;5 END;6 /SQL>SQL>-- check phv is foundSQL>DECLARE2 l_count NUMBER;3 BEGIN4 SELECT COUNT(*)5 INTO l_count6 FROM gv$sql7 WHERE sql_id = TRIM('&&modified_sql_id.')8 AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'));910 IF l_count = 0 THEN11 RAISE_APPLICATION_ERROR(-20110, 'PHV &&plan_hash_value. for modified SQL_ID &&modified_sql_id. was not be found in memory (gv$sql).');12 END IF;13 END;14 /SQL>SQL>SET ECHO OFF;Plans Loaded: 1sys_sql_handle: "SQL_e7ac5d98129ed187"sys_plan_name: "SQL_PLAN_fgb2xm099xnc7f2fc655a"1 plan(s) modified description: "ORIGINAL:0J3WDGNJ1KBKT MODIFIED:AK836BFX3Z56Z PHV:278342638 CREATED BY COE_LOAD_SQL_BASELINE.SQL"dropping staging table "STGTAB_BASELINE_0J3WDGNJ1KBKT"staging table "STGTAB_BASELINE_0J3WDGNJ1KBKT" did not existcreating staging table "STGTAB_BASELINE_0J3WDGNJ1KBKT"packaging new sql baseline into staging table "STGTAB_BASELINE_0J3WDGNJ1KBKT"1 pla(s) packagedSQL>REMSQL>REM SQL Plan BaselineSQL>REM ~~~~~~~~~~~~~~~~~SQL>REMSQL>SELECT signature, sql_handle, plan_name, enabled, accepted, fixed--, reproduced (avail on 11.2.0.2)2 FROM dba_sql_plan_baselines WHERE plan_name = :plan_name;SIGNATURE SQL_HANDLE PLAN_NAME ENA ACC FIX-------------------- ------------------------------ ------------------------------ --- --- ---16693820826484396423 SQL_e7ac5d98129ed187 SQL_PLAN_fgb2xm099xnc7f2fc655a YES YES NOSQL>SELECT description2 FROM dba_sql_plan_baselines WHERE plan_name = :plan_name;DESCRIPTION--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ORIGINAL:0J3WDGNJ1KBKT MODIFIED:AK836BFX3Z56Z PHV:278342638 CREATED BY COE_LOAD_SQL_BASELINE.SQLSQL>SET ECHO OFF;***************************************************************************** Enter TEST password to export staging table STGTAB_BASELINE_0j3wdgnj1kbkt****************************************************************************Export: Release 11.2.0.4.0 - Production on Fri Feb 19 18:30:10 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.Password:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setNote: grants on tables/views/sequences/roles will not be exportedNote: indexes on tables will not be exportedNote: constraints on tables will not be exportedAbout to export specified tables via Conventional Path .... . exporting table STGTAB_BASELINE_0J3WDGNJ1KBKT 1 rows exportedExport terminated successfully without warnings.If you need to implement this SQL Plan Baseline on a similar system,import and unpack using these commands:imp TEST file=STGTAB_BASELINE_0j3wdgnj1kbkt.dmp tables=STGTAB_BASELINE_0j3wdgnj1kbkt ignore=YSET SERVEROUT ON;DECLAREplans NUMBER;BEGINplans := DBMS_SPM.UNPACK_STGTAB_BASELINE('STGTAB_BASELINE_0j3wdgnj1kbkt', 'TEST');DBMS_OUTPUT.PUT_LINE(plans||' plan(s) unpackaged');END;/adding: coe_load_sql_baseline_0j3wdgnj1kbkt.log (deflated 72%)adding: STGTAB_BASELINE_0j3wdgnj1kbkt.dmp (deflated 87%)adding: coe_load_sql_baseline.log (deflated 63%)deleting: coe_load_sql_baseline.logcoe_load_sql_baseline completed.SQL>
6、查看源SQL基线时，添加了两个执行计划，其中一个未accepted=yes另一个为no。
进一步查看发现，使用hint的执行计划为accepted=yes状态，原来的执行计划accepted=no。
SQL>select sql_handle,sql_text,plan_name,enabled,accepted from dba_sql_plan_baselines where sql_text like '%spm_test%';SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC------------------------------ -------------------------------------------------------------------------------- ------------------------------ --- ---SQL_e7ac5d98129ed187 select owner,object_name,created,status from spm_test where object_id = 9589 SQL_PLAN_fgb2xm099xnc79d6ec45b YES NOSQL_e7ac5d98129ed187 select owner,object_name,created,status from spm_test where object_id = 9589 SQL_PLAN_fgb2xm099xnc7f2fc655a YES YES
原始执行计划accepted=no
SQL>select *from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_e7ac5d98129ed187',plan_name => 'SQL_PLAN_fgb2xm099xnc79d6ec45b')); 2 3PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL handle: SQL_e7ac5d98129ed187SQL text: select owner,object_name,created,status from spm_test where object_id =9589----------------------------------------------------------------------------------------------------------------------------------------------------------------Plan name: SQL_PLAN_fgb2xm099xnc79d6ec45b Plan id: 2641282139Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE--------------------------------------------------------------------------------PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------Plan hash value: 3008474106--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 180 | 19800 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| SPM_TEST | 180 | 19800 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_SPM_TEST | 72 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("OBJECT_ID"=9589)26 rows selected.SQL>
添加hint后的执行计划，accepted=yes
SQL>select *from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_e7ac5d98129ed187',plan_name => 'SQL_PLAN_fgb2xm099xnc7f2fc655a')); 2 3PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL handle: SQL_e7ac5d98129ed187SQL text: select owner,object_name,created,status from spm_test where object_id =9589----------------------------------------------------------------------------------------------------------------------------------------------------------------Plan name: SQL_PLAN_fgb2xm099xnc7f2fc655a Plan id: 4076627290Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD--------------------------------------------------------------------------------PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------Plan hash value: 278342638------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 180 | 19800 | 61 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| SPM_TEST | 180 | 19800 | 61 (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1 - filter("OBJECT_ID"=9589)25 rows selected.SQL>
7、查看现在SQL的执行计划，已经使用基线，走全表扫描
SQL> select owner,object_name,created,status from spm_test where object_id = 9589;OWNER OBJECT_NAME CREATED STATUS------------------------------ ------------------------------ ------------ -------PUBLIC DBA_ADVISOR_TEMPLATES 26-NOV-15 VALIDSQL>select * from table(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------SQL_ID 6sha96kkuk9pa, child number 1-------------------------------------select owner,object_name,created,status from spm_test where object_id= 9589Plan hash value: 278342638------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 61 (100)| |PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------|* 1 | TABLE ACCESS FULL| SPM_TEST | 180 | 19800 | 61 (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("OBJECT_ID"=9589)Note------ SQL plan baseline SQL_PLAN_fgb2xm099xnc7f2fc655a used for this statementPLAN_TABLE_OUTPUT----------------------------------------------------------------------------------23 rows selected.
1784
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
