首页 > 代码库 > 替换SQL执行计划
替换SQL执行计划
<style></style>
Switching two different SQL Plan with SQL Profile in Oracle...
当SQL是业务系统动态生成的，或者是第三方系统产生的，在数据库层面分析发现性能问题时，可能难以实现及时修改业务程序改善执行计划和性能；但可以在数据库层面找到有问题的SQL，调整和改造该SQL，然后将执行计划应用到原始的SQL语句中，步骤如下:
- 在数据库层面找到性能问题的SQL相关信息;
- 重构和优化SQL;
- 对比旧的和优化后的SQL性能信息;
- 将最优的SQL执行计划应用到原始SQL语句上;
通过这样的思路，在数据库层面优化SQL性能。来看看下面的示例；
SQL> @iUSERNAME INST_NAME HOST_NAME SID SERIAL# VERSION STARTED SPID OPID CPID SADDR PADDR-------------------- -------------------- ------------------------- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------OPS$SYWU sydb sywu.com 154 23601 11.2.0.4.0 20160421 23407 25 4144:3660 0000000071E1CC20 0000000072134028有下面的2个SQL，SQL 1;
select e.first_name,e.email,e.salary,d.department_name,j.job_titlefrom employees e,departments d,jobs jwhere e.department_id=d.department_id and e.job_id=j.job_id and e.salary>8500order by j.job_title;-------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |-------------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 40 (100)| | 29 |00:00:00.01 | 44 | | | || 1 | SORT ORDER BY | | 1 | 28 | 2072 | 40 (5)| 00:00:01 | 29 |00:00:00.01 | 44 | 4096 | 4096 | 4096 (0)||* 2 | HASH JOIN | | 1 | 28 | 2072 | 39 (3)| 00:00:01 | 29 |00:00:00.01 | 44 | 600K| 600K| 802K (0)|| 3 | MERGE JOIN | | 1 | 28 | 1624 | 21 (5)| 00:00:01 | 29 |00:00:00.01 | 23 | | | || 4 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 19 | 513 | 2 (0)| 00:00:01 | 17 |00:00:00.01 | 2 | | | || 5 | INDEX FULL SCAN | JOB_ID_PK | 1 | 19 | | 1 (0)| 00:00:01 | 17 |00:00:00.01 | 1 | | | ||* 6 | SORT JOIN | | 17 | 28 | 868 | 19 (6)| 00:00:01 | 29 |00:00:00.01 | 21 | 2048 | 2048 | 2048 (0)||* 7 | TABLE ACCESS FULL | EMPLOYEES | 1 | 28 | 868 | 18 (0)| 00:00:01 | 29 |00:00:00.01 | 21 | | | || 8 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 27 | 432 | 18 (0)| 00:00:01 | 27 |00:00:00.01 | 21 | | | |-------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL 1是原始SQL；下面是调整后的SQL 2;
select /*+ ordered index(d DEPT_ID_PK) index(j JOB_ID_PK) */ e.first_name,e.email,e.salary,d.department_name,j.job_titlefrom employees e,departments d,jobs jwhere e.department_id=d.department_id and e.job_id=j.job_id and e.salary>8500order by j.job_title;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 29 |00:00:00.02 | 6 | 2 | | | || 1 | SORT ORDER BY | | 1 | 28 | 2072 | 8 (25)| 00:00:01 | 29 |00:00:00.02 | 6 | 2 | 4096 | 4096 | 4096 (0)||* 2 | HASH JOIN | | 1 | 28 | 2072 | 7 (15)| 00:00:01 | 29 |00:00:00.02 | 6 | 2 | 639K| 639K| 881K (0)|| 3 | MERGE JOIN | | 1 | 28 | 1316 | 5 (20)| 00:00:01 | 29 |00:00:00.02 | 4 | 2 | | | ||* 4 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 28 | 868 | 2 (0)| 00:00:01 | 29 |00:00:00.01 | 2 | 1 | | | || 5 | INDEX FULL SCAN | EMP_DEPARTMENT_IX | 1 | 106 | | 1 (0)| 00:00:01 | 106 |00:00:00.01 | 1 | 1 | | | ||* 6 | SORT JOIN | | 29 | 27 | 432 | 3 (34)| 00:00:01 | 29 |00:00:00.01 | 2 | 1 | 2048 | 2048 | 2048 (0)|| 7 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 432 | 2 (0)| 00:00:01 | 27 |00:00:00.01 | 2 | 1 | | | || 8 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | | 1 (0)| 00:00:01 | 27 |00:00:00.01 | 1 | 1 | | | || 9 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 19 | 513 | 2 (0)| 00:00:01 | 19 |00:00:00.01 | 2 | 0 | | | || 10 | INDEX FULL SCAN | JOB_ID_PK | 1 | 19 | | 1 (0)| 00:00:01 | 19 |00:00:00.01 | 1 | 0 | | | |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------需要做的是在不调整和修改系统代码的情况下使原始的SQL 1使用第二个调整后(SQL 2)的执行计划，so 我们通过创建SQL profile来实现这个目的;
def v_sqlid=‘01h5fh3dhccyf‘declare l_sql clob; begin select t.SQL_FULLTEXT into l_sql from v$sql t where sql_id=‘&v_sqlid‘; dbms_sqltune.import_sql_profile(sql_text => l_sql,name => ‘pro_&v_sqlid‘,profile => sqlprof_attr(‘IGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE(‘‘11.2.0.4‘‘)DB_VERSION(‘‘11.2.0.4‘‘)ALL_ROWSOUTLINE_LEAF(@"SEL$1")INDEX(@"SEL$1" "E"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))INDEX(@"SEL$1" "D"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))INDEX(@"SEL$1" "J"@"SEL$1" ("JOBS"."JOB_ID"))LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1" "J"@"SEL$1")USE_MERGE(@"SEL$1" "D"@"SEL$1")USE_HASH(@"SEL$1" "J"@"SEL$1")‘ )); dbms_output.put_line(‘SQL Profile:pro_&v_sqlid imported...‘);end;/SQL Profile:pro_01h5fh3dhccyf imported...PL/SQL procedure successfully completed.当重新加载SQL时，执行计划改变，SQL profile被使用;
select e.first_name,e.email,e.salary,d.department_name,j.job_titlefrom employees e,departments d,jobs jwhere e.department_id=d.department_id and e.job_id=j.job_id and e.salary>8500order by j.job_title;-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 29 |00:00:00.02 | 6 | 2 | | | || 1 | SORT ORDER BY | | 1 | 28 | 2072 | 8 (25)| 00:00:01 | 29 |00:00:00.02 | 6 | 2 | 4096 | 4096 | 4096 (0)||* 2 | HASH JOIN | | 1 | 28 | 2072 | 7 (15)| 00:00:01 | 29 |00:00:00.02 | 6 | 2 | 639K| 639K| 869K (0)|| 3 | MERGE JOIN | | 1 | 28 | 1316 | 5 (20)| 00:00:01 | 29 |00:00:00.01 | 4 | 2 | | | ||* 4 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 28 | 868 | 2 (0)| 00:00:01 | 29 |00:00:00.01 | 2 | 1 | | | || 5 | INDEX FULL SCAN | EMP_DEPARTMENT_IX | 1 | 106 | | 1 (0)| 00:00:01 | 106 |00:00:00.01 | 1 | 1 | | | ||* 6 | SORT JOIN | | 29 | 27 | 432 | 3 (34)| 00:00:01 | 29 |00:00:00.01 | 2 | 1 | 2048 | 2048 | 2048 (0)|| 7 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 432 | 2 (0)| 00:00:01 | 27 |00:00:00.01 | 2 | 1 | | | || 8 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | | 1 (0)| 00:00:01 | 27 |00:00:00.01 | 1 | 1 | | | || 9 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 19 | 513 | 2 (0)| 00:00:01 | 19 |00:00:00.01 | 2 | 0 | | | || 10 | INDEX FULL SCAN | JOB_ID_PK | 1 | 19 | | 1 (0)| 00:00:01 | 19 |00:00:00.01 | 1 | 0 | | | |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Note----- - SQL profile pro_01h5fh3dhccyf used for this statement原始的SQL使用了调整后的执行计划。
替换SQL执行计划
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。