谓词推入引发的惨案

原创 2013年12月04日 17:11:50
  早上某数据库服务器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|
———————————————————————————————————

表tb_event e和tb_event_track et关联

通过e 和 tb_callback cb关联

通过 e和 tb_event_marker em,

 

     调整后整个执行速度有了明显提升,15秒之内可以返回结果,可以看到view结果集与第六步产生的结果集进行hash join outer,然后得到的结果集合与最大的表tb_event_log再次进行hash join outer。当然这个执行计划可能还有完善的余地,需进一步根据数据情况调整表的关联顺序.

一个 视图 作为 NL 被驱动表 ,基本凶多吉少

为什么视图 能作为 NL 被驱动表???  根据链接列推入到视图



 

强制让SQL走谓词推入

SELECT * FROM STORESUM WHERE (ORG_ID IN (SELECT EP_LOC.ORG_ID FROM EP_LOC, E...

Oracle谓词推入

Oracle谓词推入:所谓推入,是把库表中判断的条件推入进视图中进行判断。后续不再对库表进行筛选。 创建视图 create table hao1 as select object_id ,...

VIEW PUSHED PREDICATE(谓词推入)引发的惨剧

帮网友调SQL http://www.itpub.net/forum.php?mod=viewthread&tid=1492997&extra=pageD1%3D&page=1 原SQL如下(要跑...

视图合并和谓词推入

------------------------------《海量数据库解决方案》读书笔记-----视图合并(View Merging)------------------------ 一、视图合并...

多重不利因素或将经济推入滞胀泥潭

  近期,推高通胀的新因素纷纷浮出水面。人民币非理性升值、南方罕见大旱、油价电价提高、猪肉价格上涨、蔬菜等农副产品价格大举反弹。对此,资深财经评论人余丰慧今日发表博文表示,以上因素叠加在一起,对通胀助...

将消息推入消息队列并从消息队列中取出------利用异质链表和C++的多态性

前面我们介绍了异质链表, 现在, 我们继续来深究其应用, 我们可以把Integer,  Point, Rectange的对象看成是包装好的消息, 下面我们来看看如何将消息塞入消息队列, 并从消息队列中...
  • stpeace
  • stpeace
  • 2014年12月04日 23:10
  • 957

一辆停在广场上的献血车引发的惨案

事情的起因,是我和朋友出去溜达,看到广场上停了一辆献血车, 然后,矛盾就此产生了。那货非得去献血,说是积极向上,为人民服务。。。(等等,一堆被洗脑的话......) 当然,我是二话不说,拉着就走, 我...

【Webview相关问题】"控制字符"引发的惨案及Base64

问题描述webview.loadurl 去执行一段js语句来回调前端方法来回传数据,但是发现偶尔出现没有回调的情况,参照物 将该语句在前端环境中执行完美通过。 iOS执行该操作也是正常的。 掌握了一定...

iOS回顾笔记( 02 ) -- 由九宫格布局引发的一系列“惨案”

iOS回顾笔记( 02 ) -- 由九宫格布局引发的一系列“惨案” 前言(扯几句淡先) 回顾到学习UI过程中的九宫格布局时,发现当时学的东西真是不少。 这个阶段最大的特点就是:知识点繁多且琐碎。...

listview 最后一个item向上推入动画

  • 2016年05月27日 17:40
  • 3.07MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:谓词推入引发的惨案
举报原因:
原因补充:

(最多只允许输入30个字)