oracle利用INLIST ITERATOR优化SQL

来源：互联网发布：sql server是什么软件编辑：程序博客网时间：2024/06/06 07:08

开发人员反应一支查看bom的sql反应很慢，需要dba协助优化。

SQL如下：

SELECT A0.idA2A2,A0.updateCountA2,A0.classnameA2A2,ABS(NULL),A0.branchIdA2typeDefinitionRefe,A1.idA2A2,A1.updateCountA2,A1.idA3A6,A1.value FROM WTPartUsageLink A0,BooleanValue A1 WHERE (((A1.idA3A6 = :1 ) AND (A1.idA3A4 = A0.idA2A2) AND ((A1.idA3A5 = :2 ) OR (A1.idA3A5 IS NULL )))) AND (A0.markForDeleteA2 = 0) AND (A0.idA2A2 IN (: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 ,:86 ,:87 ,:88 ,:89 ,:90 ,:91 ,:92 ,:93 ,:94 ,:95 ,:96 ,:97 ,:98 ,:99 ,:100 ,:101 ,:102 ,:103 ,:104 ,:105 ,:106 ,:107 ,:108 ,:109 ,:110 ,:111 ,:112 ,:113 ,:114 ,:115 ,:116 ,:117 ))

利用以下脚本，查看到sql执行情况，确认很慢。

Select inst_id, sql_id, child_number chld, plan_hash_value plan_hash, users_executing exe, executions exes,
        parsing_schema_id user_id,
        buffer_gets/executions gets,
        disk_reads/executions reads,
        rows_processed/executions rows_, cpu_time/executions/1000/1000 cpu_ms,
        Elapsed_time/executions/1000/1000 elaps_ms,
        USER_IO_WAIT_TIME/executions/1000/1000 io_ms,
        last_load_time,
        to_char(sysdate,'hh24:mi:ss') chk_date
From gv$sql
Where sql_id='dm5fk9c92c7yk'
and executions>0
order by last_load_time

原执行计划如下：

---------------------------------------------------------------------------------------------------------
| Id | Operation                     | Name                    | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                         |       |       |     2 (100)|          |
|   1 | NESTED LOOPS                 |                         |     1 |    70 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |                         |     1 |    70 |     2   (0)| 00:00:01 |
|* 3 |    TABLE ACCESS BY INDEX ROWID| BOOLEANVALUE            |     1 |    28 |     1   (0)| 00:00:01 |
|* 4 |     INDEX RANGE SCAN          | BOOLEANVALUE$COMPOSITE2 |     1 |       |     1   (0)| 00:00:01 |
|* 5 |    INDEX UNIQUE SCAN          | PK_WTPARTUSAGELINK      |     1 |       |     1   (0)| 00:00:01 |
|* 6 |   TABLE ACCESS BY INDEX ROWID | WTPARTUSAGELINK         |     1 |    42 |     1   (0)| 00:00:01 |

其实这个问题主要是in后面的参数值有100多个，执行计划中并没有走INLIST ITERATOR(迭代)操作，迭代是指in后有多个常量时，只执行一次，而不走迭代时，有多少个常量就要执行多少次。显然，执行计划走迭代性能要好。

手工尝试输入三个参数值代入in中时，发现执行计划有走迭代，而v$sql中只有一个执行计划，无法从cursor中去选择一个好的执行计划来加以固化，询问过开发人员，这是一支由系统自动生成的sql，无法手工去修改SQL，而oracle也没有提供INLIST ITERATOR的hint，手工调整已无法实现，只能依靠oracle本身的优化工具。

通过自动sql tuning和sql profile调整执行计划:

1.针对sql_id建立自动sql tuning任务：
declare
tuning_task_name varchar2(30);
tuning_sqltext clob;
begin
tuning_task_name :=dbms_sqltune.create_tuning_task(
sql_id => 'dm5fk9c92c7yk',
scope =>'COMPREHENSIVE',
time_limit =>2000,
task_name =>'plmsq2',
description=>'Tune T1 count');
end;

2.执行自动sql tuing任务
begin
dbms_sqltune.execute_tuning_task(task_name=>'plmsq2');
end;

3.查询tuing任务完成报告
select dbms_sqltune.report_tuning_task('plmsq2')
from dual

4.比较报告中执行计划好坏，并接受profile:

execute dbms_sqltune.accept_sql_profile(task_name => 'plmsq2', task_owner
=> 'ZENGXUEWEN',force_match=>true,replace => TRUE);

5.查询profile情况
select * from dba_sql_profiles order by created

查得已针对sql建立的profile为SYS_SQLPROF_01560c5445fd0001

调整后的执行计划：

----------------------------------------------------------------------------------------------------------
| Id | Operation                      | Name                    | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |       |       |    47 (100)|          |
|   1 | NESTED LOOPS                  |                         |    62M| 4147M|    47   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                 |                         |    62M| 4147M|    47   (3)| 00:00:01 |
|   3 |    INLIST ITERATOR             |                         |       |       |            |          |
|* 4 |     TABLE ACCESS BY INDEX ROWID| BOOLEANVALUE            |    96 | 2688 |    18   (0)| 00:00:01 |
|* 5 |      INDEX RANGE SCAN          | BOOLEANVALUE$COMPOSITE0 |   128 |       |    15   (0)| 00:00:01 |
|* 6 |    INDEX UNIQUE SCAN           | PK_WTPARTUSAGELINK      |     1 |       |     1   (0)| 00:00:01 |
|* 7 |   TABLE ACCESS BY INDEX ROWID | WTPARTUSAGELINK         |   648K|    25M|     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

优化前后性能对比：

调整前(单次执行)：
所花时间：5458.77s cpu时间:630.589s 逻辑读：3914415.7 物理读：680683.438

执行计划调整后(单次执行)：
所花时间：39.82ms cpu时间：13.86 ms 逻辑读:309 物理读：5.6

调整执行计划后，开发人员反馈变快了。

以下是QQ聊天记录：

代言人 15:25:40

调整了

代言人 15:25:49

退出系统，重新再试下

xx 15:25:52

好

xx 15:31:07

速度快了

0 0