替换SQL执行计划
Switching two different SQL Plan with SQL Profile in Oracle...
当SQL是业务系统动态生成的，或者是第三方系统产生的，在数据库层面分析发现性能问题时，可能难以实现及时修改业务程序改善执行计划和性能；但可以在数据库层面找到有问题的SQL，调整和改造该SQL，然后将执行计划应用到原始的SQL语句中，步骤如下:
- 在数据库层面找到性能问题的SQL相关信息;
- 重构和优化SQL;
- 对比旧的和优化后的SQL性能信息;
- 将最优的SQL执行计划应用到原始SQL语句上;
通过这样的思路，在数据库层面优化SQL性能。来看看下面的示例；
SQL> @i
USERNAME 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_title
from
employees e,departments d,jobs j
where
e.department_id=d.department_id and e.job_id=j.job_id
and e.salary>8500
order 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_title
from
employees e,departments d,jobs j
where
e.department_id=d.department_id and e.job_id=j.job_id
and e.salary>8500
order 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_HINTS
OPTIMIZER_FEATURES_ENABLE(''11.2.0.4'')
DB_VERSION(''11.2.0.4'')
ALL_ROWS
OUTLINE_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_title
from
employees e,departments d,jobs j
where
e.department_id=d.department_id and e.job_id=j.job_id
and e.salary>8500
order 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使用了调整后的执行计划。
Hope is personal,hope is something that no one can give to you,you have to choose to believe in hope,you have to make it yourself..
浙公网安备 33010602011771号