简单门户网站模板,网站建设 流程 域名申请,郑州专业网页模板制作公司,没有网站可以做网络推广吗二、稳定执行计划 #xff08;一#xff09;sql profile的好处 稳定执行计划 在不能修改目标sql的sql文本的情况下使目标sql语句按照指定的执行计划运行。 1、automatic类型的sql profile 本质是针对目标sql的一些额外的调整信息#xff0c;这些额外的调整信息需要与原目标s…二、稳定执行计划 一sql profile的好处 稳定执行计划 在不能修改目标sql的sql文本的情况下使目标sql语句按照指定的执行计划运行。 1、automatic类型的sql profile 本质是针对目标sql的一些额外的调整信息这些额外的调整信息需要与原目标sql的相关统计信息等内容一起作用才能得到新的执行计划即原始sql的统计信息等内容一旦发生变化即使原有的automatic类型的sql profile并没有改变该sql的执行计划也可能发生变化。 SQL exec dbms_sqltune.accept_sql_profile(task_name my_sql_tuning_task_4,task_owner SCOTT,replace TRUE,force_matchtrue); 说明 force_matchtrue 相当于绑定变量 1t1表原本有索引idx_t1使用hint让它不走索引 SQL grant select on v_$session to scott; SQL grant select on v_$sql_plan_statistics_all to scott; SQL grant select on v_$sql_plan to scott; SQL connect scott/tiger; SQL create table t1(n number); SQL declare 2 begin 3 for i in 1 .. 10000 4 loop 5 insert into t1 values(i); 6 commit; 7 end loop; 8 end; 9 / SQL select count(*) from t1; COUNT(*) ---------- 10000 SQL create index idx_t1 on t1(n); SQL exec dbms_stats.gather_table_stats(ownnameSCOTT,tabnameT1,method_optfor all columns size 1,CASCADEtrue); SQL select /* no_index(t1 idx_t1) */ * from t1 where n1 SQL select * from table(dbms_xplan.display_cursor(null,null,advanced));
该执行计划会走全表扫描
2建立一个自动调整任务my_sql_tuning_task_4 SQL declare 2 my_task_name VARCHAR2(30); 3 my_sqltext CLOB; 4 begin 5 my_sqltext : select /* no_index(t1 idx_t1) */ * from t1 where n1; 6 my_task_name : dbms_sqltune.create_tuning_task( 7 sql_text my_sqltext, 8 user_name SCOTT, 9 scope COMPREHENSIVE, 10 time_limit 60, 11 task_name my_sql_tuning_task_4, 12 description Task to tune a query on table t1); 13 END; 14 /
(备注删除自动任务 exec dbms_sqltune.drop_tuning_task(my_sql_tuning_task_4);
补充删除sql_profile : exec dbms_sqltune.drop_sql_profile(name my_sql_tuning_task_4); 3执行上述自动调整任务 SQL begin 2 dbms_sqltune.execute_tuning_task(task_name my_sql_tuning_task_4); 3 end; 4 / 4查看该自动调整任务的结果确实走了索引。 SQL set long 900 SQL set longchunksize 1000 SQL set linesize 800 SQL select dbms_sqltune.report_tuning_task(my_sql_tuning_task_4) from dual; 5接受这个sql profile SQL exec dbms_sqltune.accept_sql_profile(task_name my_sql_tuning_task_4,task_owner SCOTT,replace TRUE,force_matchtrue); 补充删除sql_profile : exec dbms_sqltune.drop_sql_profile(name my_sql_tuning_task_4); 6接着再执行如下sql时会走索引 SQL select /* no_index(t1 idx_t1) */ * from t1 where n20 SQL select * from table(dbms_xplan.display_cursor(null,null,advanced));
2、manual类型的sql profile 本质是一堆hint的组合。这一堆hint的组合实际上来源于执行计划中outline data部分的hint组合。manual类型的sql profile可以起到很好的稳定目标sql的执行计划的作用。
使用脚本coe_xfr_sql_profile.sql针对目标sql生成manual类型的sql profile,并通过偷梁换柱的方式在不修改目标sql的sql文本的情况下调整其执行计划。 步骤如下 1针对目标sql使用脚本coe_xfr_sql_profile.sql产生能生成其manual类型的sql profile的脚本A. 2改写目标sql的文本在其中使用合适的hint直到加入hint后的sql能走出我们想要的执行计划。然后对加入合适hint后的sql使用脚本coe_xfr_sql_profile.sql产生能生成其manual类型的sql profile的脚本B 3用脚本B中的Outline Data部分的hint组合替换掉脚本A中的Outline Data部分的hint组合。 4执行脚本A生成针对原目标sql的manual类型的sql profile. SQL select /* no_index(t1 idx_t1) */ * from t1 where n20; 因为上一步automatic类型的sql profile使用了自动调整任务所以该sql的执行计划会走索引 SQL select * from table(dbms_xplan.display_cursor(null,null,advanced)); INDEX RANGE SCAN PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- - SQL profile coe_gkdzg1fk71sc0_3617692013 used for this statement
删除sql profile SQL exec dbms_sqltune.drop_sql_profile(coe_gkdzg1fk71sc0_3617692013);
执行走索引的profile SQL select /* index(t1 idx_t1) */ * from t1 where n20; SQL select * from table(dbms_xplan.display_cursor(null,null,advanced)); PLAN_TABLE_OUTPUT SQL_ID 2a4g5h03mm877, child number 0 Plan hash value: 1369807930 |* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
SQL col SQL_TEXT for a85 SQL set pagesize 900; SQL set linesize 900; SQL select sql_text,sql_id,version_count from v$sqlarea where sql_text like %n20% SQL_TEXT SQL_ID VERSION_COUNT ---------------------------------------------------------------------------------- ------------- ------------- select /* no_index(t1 idx_t1) */ * from t1 where n20 fyrgtpxw26btv 1 select /* index(t1 idx_t1) */ * from t1 where n20 2a4g5h03mm877 1 select sql_text,sql_id,version_count from v$sqlarea where sql_text like %n20% gtfnngky67bj6 1
原sql对应的plan_hash_value SQL select plan_hash_value from v$sql where sql_idfyrgtpxw26btv; PLAN_HASH_VALUE 3617692013 加了强制索引的sql对应的plan_hash_value SQL select plan_hash_value from v$sql where sql_id2a4g5h03mm877; PLAN_HASH_VALUE --------------- 1369807930 针对原sql使用脚本coe_xfr_sql_profile.sql产生能生产其manual类型sql profile脚本。 SQL ?/rdbms/admin/coe_xfr_sql_profile.sql; oracle官方文档ID 215187.1 Parameter 1: SQL_ID (required) Enter value for 1: fyrgtpxw26btv PLAN_HASH_VALUE AVG_ET_SECS --------------- ----------- 3617692013 .006 Parameter 2: PLAN_HASH_VALUE (required) Enter value for 2: 3617692013 Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID : fyrgtpxw26btv PLAN_HASH_VALUE: 3617692013 SQLBEGIN 2 IF :sql_text IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20100, SQL_TEXT for SQL_ID sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).); 4 END IF; 5 END; 6 / SQLSET TERM OFF; SQLBEGIN 2 IF :other_xml IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20101, PLAN for SQL_ID sql_id. and PHV plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).); 4 END IF; 5 END; 6 / SQLSET TERM OFF; Execute coe_xfr_sql_profile_fyrgtpxw26btv_3617692013.sql on TARGET system in order to create a custom SQL Profile with plan 3617692013 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed. SQL
针对强制索引的sqlsql使用脚本coe_xfr_sql_profile.sql产生能生产其manual类型sql profile脚本。 SQL?/rdbms/admin/coe_xfr_sql_profile.sql; Parameter 1: SQL_ID (required) Enter value for 1: 2a4g5h03mm877 PLAN_HASH_VALUE AVG_ET_SECS --------------- ----------- 1369807930 .007 Parameter 2: PLAN_HASH_VALUE (required) Enter value for 2: 1369807930 Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID : 2a4g5h03mm877 PLAN_HASH_VALUE: 1369807930 SQLBEGIN 2 IF :sql_text IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20100, SQL_TEXT for SQL_ID sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).); 4 END IF; 5 END; 6 / SQLSET TERM OFF; SQLBEGIN 2 IF :other_xml IS NULL THEN 3 RAISE_APPLICATION_ERROR(-20101, PLAN for SQL_ID sql_id. and PHV plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).); 4 END IF; 5 END; 6 / SQLSET TERM OFF; Execute coe_xfr_sql_profile_2a4g5h03mm877_1369807930.sql on TARGET system in order to create a custom SQL Profile with plan 1369807930 linked to adjusted sql_text. COE_XFR_SQL_PROFILE completed. SQL
参考coe_xfr_sql_profile_2a4g5h03mm877_1369807930.sql修改coe_xfr_sql_profile_fyrgtpxw26btv_3617692013.sql脚本内容再执行coe_xfr_sql_profile_fyrgtpxw26btv_3617692013.sql脚本
h : SYS.SQLPROF_ATTR( q[BEGIN_OUTLINE_DATA], q[IGNORE_OPTIM_EMBEDDED_HINTS], q[OPTIMIZER_FEATURES_ENABLE(12.1.0.2)], q[DB_VERSION(12.1.0.2)], q[ALL_ROWS], q[OUTLINE_LEAF(SEL$1)], q[FULL(SEL$1 T1SEL$1)], q[END_OUTLINE_DATA]);
改为 h : SYS.SQLPROF_ATTR( q[BEGIN_OUTLINE_DATA], q[IGNORE_OPTIM_EMBEDDED_HINTS], q[OPTIMIZER_FEATURES_ENABLE(12.1.0.2)], q[DB_VERSION(12.1.0.2)], q[ALL_ROWS], q[OUTLINE_LEAF(SEL$1)], q[INDEX(SEL$1 T1SEL$1 (T1.N))], q[END_OUTLINE_DATA]); force_matchFALSE 改为 force_matchTRUE
执行coe_xfr_sql_profile_fyrgtpxw26btv_3617692013.sql脚本 SQL coe_xfr_sql_profile_fyrgtpxw26btv_3617692013.sql SQL connect scott/tiger; Connected.
( 补充删除sql_profile : exec DBMS_SQLTUNE.DROP_SQL_PROFILE(name my_sql_tuning_task_4); )
最后可以走索引。 SQL select /* no_index(t1 idx_t1) */ * from t1 where n20; SQL select * from table(dbms_xplan.display_cursor(null,null,advanced)); |* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
二、spm稳定执行计划 oracle 11g以及以上版本上有如下2种方法产生目标sql的sql plan baseline: 自动捕获 手工生成/批量导入批量导入尤其适用于oracle数据库大版本升级它可以确保升级后原有系统所有的sql的执行计划不会发生改变
手工生成单个sql plan baseline的步骤 1针对目标sql使用dbms_spm.load_plans_from_cursor_cache手工生成其初始执行计划所对应的sql plan baseline,此时使用dbms_spm.load_plans_from_cursor_cache传入的参数为 dbms.spm.load_plans_from_cursor_cache ( sql_id 原目标sql的sql_id, plan_hash_value 原目标sql的plan hash value ) (2)改写目标sql的sql文本在其中加入合适的hint,直到加入hint后的所该写的sql能走出我们想要的执行计划然后对改写后的sql使用dbms_spm.load_plans_from_cursor_cache手工生成新的执行计划所对应的sql plan baseline.此时使用dbms_spm.load_plans_from_cursor_cache传入的参数为如下所示 dbms.spm.load_plans_from_cursor_cache ( sql_id 加入合适hint后的改写sql的sql_id, plan_hash_value 加入合适hint后的改写sql的plan hash value, sql_handle原目标sql在步骤1中所产生的sql plan baseline的sql_handle ) (3)使用dbms_spm.drop_sql_plan_baseline删除步骤1中手工生成的原目标sql的初始执行计划所对应的sql plan baseline.此时使用dbms_spm.drop_sql_plan_baseline传入的参数为如下所示 dbms_spm.drop_sql_plan_baseline ( sql_handle原目标sql在步骤1中所产生的sql plan baseline的sql_handle, plan_name原目标sql在步骤1中所产生的sql plan baseline的plan_name )
var temp varchar2(10000); exec:temp:dbms_spm.drop_sql_plan_baseline(sql_handleSQL_75b06ae056223f5f,plan_nameSQL_PLAN_7bc3aw1b24guzb55f43d8); exec:temp:dbms_spm.drop_sql_plan_baseline(sql_handleSQL_75b06ae056223f5f,plan_nameSQL_PLAN_7bc3aw1b24guzb860bcf2);
创建测试表建立索引收集统计信息执行查询 SQL create table t2 as select * from dba_objects; SQL create index idx_t2 on t2(object_id); SQL exec dbms_stats.gather_table_stats(ownnameSCOTT,tabnameT2,estimate_percent100,cascadetrue); SQL select /* no_index(t2 idx_t2) */object_name,object_id from t2 where object_id4; SQL select * from table(dbms_xplan.display_cursor(null,null,advanced)); SQL_ID 76td2qkxcrn9v, child number 0 Plan hash value: 1513984157 |* 1 | TABLE ACCESS FULL| T2 | 1 | 30 | 430 (1)| 00:00:01 | --------------------------------------------------------------------------
没有开启sql_plan_baseline自动捕获所以没有sql_plan_baseline SQL set pagesize 900; SQL set linesize 900; SQL col PLAN_NAME for a30; SQL col SQL_HANDLE for a25; SQL col SQL_TEXT for a80; SQL select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like select /* no_index(t2 idx_t2) */object%; no rows selected SQL
1 使用目标sql的初始执行计划即对T2表的全表扫描所对应的sql id 和plan hash_value 来手工生成对应的sql plan baseline SQL var temp number; SQL exec :temp : dbms_spm.load_plans_from_cursor_cache(sql_id 76td2qkxcrn9v,plan_hash_value1513984157); PL/SQL procedure successfully completed SQL select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like select /* no_index(t2 idx_t2) */object%; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT ------------------------- ------------------------------ -------------- --- --- -------------------------------------------------------------------------------- SQL_75b06ae056223f5f SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD YES YES select /* no_index(t2 idx_t2) */object_name,object_id from t2 where object_id4 SQL
改写目标sql强制索引hint SQL select /* index(t2 idx_t2) */object_name,object_id from t2 where object_id4; SQL select * from table(dbms_xplan.display_cursor(null,null,advanced)); SQL_ID 795jg57h9tryx, child number 0 Plan hash value: 1306670842 |* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 |
原目标sql现在依然只有其原执行计划即对T2表的全表扫描所对应的sql plan baseline SQL select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like select /* no_index(t2 idx_t2) */object%; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT ------------------------- ------------------------------ -------------- --- --- -------------------------------------------------------------------------------- SQL_75b06ae056223f5f SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD YES YES select /* no_index(t2 idx_t2) */object_name,object_id from t2 where object_id4 SQL
2 使用改写后的sql的新执行计划强制索引hint所对应的sql id 和plan hash_value 以及原目标sql的sql plan baseline的sql_handle来手工生成对应的sql plan baseline SQL exec :temp :dbms_spm.load_plans_from_cursor_cache(sql_id 795jg57h9tryx,plan_hash_value1306670842,sql_handleSQL_75b06ae056223f5f); SQL select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like select /* no_index(t2 idx_t2) */object%; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT ------------------------- ------------------------------ -------------- --- --- -------------------------------------------------------------------------------- SQL_75b06ae056223f5f SQL_PLAN_7bc3aw1b24guzb55f43d8 MANUAL-LOAD YES YES select /* no_index(t2 idx_t2) */object_name,object_id from t2 where object_id4 SQL_75b06ae056223f5f SQL_PLAN_7bc3aw1b24guzb860bcf2 MANUAL-LOAD YES YES select /* no_index(t2 idx_t2) */object_name,object_id from t2 where object_id4 SQL 3 drop 掉原执行计划即对T2表的全表扫描所对应的sql plan baseline SQL exec:temp:dbms_spm.drop_sql_plan_baseline(sql_handleSQL_75b06ae056223f5f,plan_nameSQL_PLAN_7bc3aw1b24guzb860bcf2); SQL select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like select /* no_index(t2 idx_t2) */object%; SQL_HANDLE PLAN_NAME ORIGIN ENA ACC SQL_TEXT ------------------------- ------------------------------ -------------- --- --- -------------------------------------------------------------------------------- SQL_75b06ae056223f5f SQL_PLAN_7bc3aw1b24guzb55f43d8 MANUAL-LOAD YES YES select /* no_index(t2 idx_t2) */object_name,object_id from t2 where object_id4 SQL
再次执行原目标sql SQL select /* no_index(t2 idx_t2) */object_name,object_id from t2 where object_id4; SQL select * from table(dbms_xplan.display_cursor(null,null,advanced)); SQL_ID 76td2qkxcrn9v, child number 1 Plan hash value: 1306670842 |* 2 | INDEX RANGE SCAN | IDX_T2 | 1 | | 1 (0)| 00:00:01 | Note - SQL plan baseline SQL_PLAN_7bc3aw1b24guzb55f43d8 used for this statement
