关于VIEW PUSHED PREDICATE的一个优化案例
根据这个案例需要的一些知识，后面会跟进关于外联结、视图合并、VIEW PUSHED PREDICATE的相关知识。早上某数据库服务器CPU不断报警，应用系统管理员同时反馈应用响应明显变慢。登陆数据库主机查看，应用连接上来的几个进程占用了大量的CPU资源，造成CPU空闲率很低。登陆数据库查询，发现有不少buffer cache chains的等待，初步判断是应用上出现了某些性能糟糕的SQL语句。
通过进程捕获了几条耗资源的SQL语句，发现大部分都是类似同一条语句造成的。手工执行一下，需要2分多钟才能出结果。
捕获到的SQL语句如下：
SELECT *
FROM (SELECT DISTINCT e.n_event_id,
e.n_parent_id,
e.v_event_num,
em.n_req_type_1,
em.n_req_type_2,
em.v_title,
em.v_description,
e.n_priority,
cb.n_time_limit,
cb.n_status,
e.n_process_way,
e.n_maintaince_event_id,
e.v_maintaince_flag,
e.v_replacedevice_flag,
et.d_acbept_date,
et.d_finish_date,
et.v_exempt_status,
et.n_callback_status,
et.n_delay_time,
erpl.n_creator,
erpl.d_creation_date,
e.n_req_id,
el.v_res_notice_msg,
el.v_res_notice_email,
el.v_res_notice_im,
vd.v_valid_status,
vd.v_related_org_id,
e.n_dev_process_way,
e.v_over_time_reason,
e.v_confirm_person,
e.v_new_device_num
FROM tb_event e,
tb_event_related_person erpl,
vorg_department vd,
tb_callback cb,
tb_event_log el,
tb_event_marker em,
tb_event_track et
WHERE e.n_event_id = et.n_event_id(+)
AND e.n_event_id = em.n_event_id(+)
AND e.n_event_id = el.n_event_id(+)
AND e.n_event_id = cb.n_event_id(+)
AND erpl.n_dept_id = vd.recid(+)
AND e.n_event_id = erpl.n_event_id(+)
ORDER BY e.d_creation_date DESC)
WHERE rownum <= 40;
vorg_department为一个view，具体定义如下：
create or replace view vorg_department as
select d.recid,
r.v_valid_status,
d.v_related_org_id,
r.org_parent,
r.tree_no,
d.dept_kind,
d.dept_type,
d.dept_name,
d.status,
d.dept_code,
d.area_code,
d.dept_prof,
d.sort_num,
d.link_addr,
r.layer
from ORG_DEPARTMENT d, ORG_DEPRELATION r
where d.recid = r.org_child
and r.relation_type = 1
该语句的执行计划如下：
PLAN_TABLE_OUTPUT
———————————————————————————————————–
| Id| Operation |Name | Rows| Bytes|TempSpc|Cost|
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 40| 15404| |23M|
|*1 |COUNT STOPKEY | | | | | |
| 2 | VIEW | | 110M|395G| |23M|
|*3 | SORT UNIQUE STOPKEY | | 110M| 27G| 58G|11M|
| 4 | NESTED LOOPS OUTER | | 110M| 27G| | 2797|
|*5 | HASH JOIN OUTER | |1255K|308M| 39M| 2797|
|*6 | HASH JOIN OUTER | | 153K| 37M| 28M| 1873|
|*7 | HASH JOIN OUTER | | 115K| 27M| 24M| 1384|
|*8 | HASH JOIN OUTER | | 101K| 23M| 23M|990|
|*9 | HASH JOIN OUTER | | 100K| 22M| 17M|571|
|10 | TABLE ACCESS FULL | TB_EVENT |77044| 16M| |256|
|11 | INDEX FAST FULL SCAN| IDX_EVENT_TRACK_N_E_ID| 100K|491K| | 4|
|12 | INDEX FAST FULL SCAN |IDX_TB_CALLBAK_E_ID |75959|296K| | 21|
|13 | TABLE ACCESS FULL | TB_EVENT_MARKER | 3686| 18430| | 3|
|* 14 | INDEX FAST FULL SCAN | IDX_TB_EVENT_RP_DUP1| 101K|895K| | 4|
|15 | INDEX FAST FULL SCAN |IDX_TB_EVENT_LOG_N_E_ID| 628K| 2455K| |149|
|16 | VIEW PUSHED PREDICATE | VORG_DEPARTMENT | 88| 1144 | | |
|17 | NESTED LOOPS | | 1 | 15 | | 2|
|* 18 | INDEX UNIQUE SCAN | PK_DEPARTMENT | 1 | 6| | 1|
|* 19 | INDEX RANGE SCAN | ASSOCIATION8_FK | 1 | 9| | 1|
———————————————————————————————————–
首先了解了一下各表的统计信息情况和表的数据情况，统计信息都是最新的，除了tb_event_log表略微大点，有60多万数据外，其余表均10万左右或更小，执行计划里面的小表table full sacn和一些索引的access情况也并无严重问题。接下来关注表与表之间的关联顺序和方式。
继续分析这个执行计划，第四行评估出了一个超级大的结果集，找到这个结果集的产生方式，第16行引起了我的注意。VIEW PUSHED PREDICATE,在存在out join和view的情况下，CBO自动选择了谓词推进到了视图VORG_DEPARTMENT中以过滤更多的数据，也算无可厚非。但继续往上找，找到这个谓词的基数，第5行，发现评估出来的基数竟然有100多万行之多，而视图本身的结果集，只有不到3000行！
看来这是一个“愚蠢的”执行计划，CBO先选择视图之外的表做关联,tb_event虽然本身很小，但由于与其他表做多次外连接，因此最终得到一个庞大的基数，而拿着这个结果集推进到视图中，想想是多么恐怖的事情！
一般来说，大集合的结果集合并不适合进行PUSHED PREDICATE。如果只是少量，会起到比较好的效果。
既然知道了原因，那就尝试着去改变这个执行计划。Oracle提供了no_push_pred和push_pred来改变pushed predication行为，在上述语句中添加no_push_pred提示：
SELECT *
FROM (SELECT /*+ no_push_pred(vd) */ DISTINCT e.n_event_id,
e.n_parent_id,
e.v_event_num,
….
FROM tb_event e,
tb_event_related_person erpl,
vorg_department vd,
tb_callback cb,
tb_event_log el,
tb_event_marker em,
tb_event_track et
WHERE e.n_event_id = et.n_event_id(+)
AND e.n_event_id = em.n_event_id(+)
AND e.n_event_id = el.n_event_id(+)
AND e.n_event_id = cb.n_event_id(+)
AND erpl.n_dept_id = vd.recid(+)
AND e.n_event_id = erpl.n_event_id(+)
ORDER BY e.d_creation_date DESC)
WHERE rownum <= 40;
修改后的执行计划如下：
———————————————————————————————————–
| Id| Operation | Name | Rows | Bytes|TempSpc|Cost|
———————————————————————————————————–
| 0 | SELECT STATEMENT | | 40 | 11553| |181K|
|*1 |COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1470K| 5398M| |181K|
|*3 | SORT UNIQUE STOPKEY | | 1470K|370M| 765M| 92546|
|*4 | HASH JOIN OUTER | | 1470K|370M|46M|3546|
|*5 | HASH JOIN OUTER | |180K| 44M|39M|2499|
|*6 | HASH JOIN OUTER | |153K| 37M|28M|1873|
|*7 | HASH JOIN OUTER | |115K| 27M|24M|1384|
|*8 | HASH JOIN OUTER | |101K| 23M|23M| 990|
|*9 | HASH JOIN OUTER | |100K| 22M|17M| 571|
|10 | TABLE ACCESS FULL | TB_EVENT | 77044| 16M| | 256|
|11 | INDEX FAST FULL SCAN| IDX_EVENT_TRACK_N_E_ID |100K|491K| | 4|
|12 | INDEX FAST FULL SCAN | IDX_TB_CALLBAK_E_ID | 75959|296K| | 21|
|13 | TABLE ACCESS FULL | TB_EVENT_MARKER |3686| 18430| | 3|
|* 14 | INDEX FAST FULL SCAN | IDX_TB_EVENT_RP_DUP1 |101K|895K| | 4|
|15 | VIEW | VORG_DEPARTMENT |3355| 20130| | 3|
|16 | NESTED LOOPS | |3355| 50325| | 3|
|* 17 | INDEX FAST FULL SCAN| ASSOCIATION8_FK |3356| 30204| | 3|
|* 18 | INDEX UNIQUE SCAN | PK_DEPARTMENT | 1| 6| | |
|19 | INDEX FAST FULL SCAN | IDX_TB_EVENT_LOG_N_E_ID|628K| 2455K| | 149|
———————————————————————————————————
调整后整个执行速度有了明显提升，15秒之内可以返回结果，可以看到view结果集与第六步产生的结果集进行hash join outer，然后得到的结果集合与最大的表tb_event_log再次进行hash join outer。当然这个执行计划可能还有完善的余地，需进一步根据数据情况调整表的关联顺序.
个人思路：
1处返回了125万行数据，占用308M数据量，而2处只返回88行数据，从这个方面上来看1和2如果使用nested loops是合适的，但是走NESTED LOOPS OUTER后行数由1255K->110M,数据量也由308M->27G，110M差不多等于1255K*88，这是一个笛卡尔积的代价，所以我觉得这个SQL语句有两个地方可能有优化的余地，一是添加一些必要的条件，避免SQL语句和视图之间笛卡尔积关联查询，二是分析有没有方法可以减少除视图外的其他各表关联查询所返回的数据（添加谓词条件）
另外请教老师“PUSHED PREDICATE”的含义是什么？ VIEW PUSHED PREDICATE:这个内容上课讲过，就是Oracle将一些外围的条件根据一些关联关系push到了视图中，论坛中有专门的帖子。
这个就涉及到了一个out join的问题。
我会做一个专门的外联结的帖子。
页:
[1]