今天接到一个工单,是一个抽取数据工单,语句如下,初看语法没有问题,并且两张表的数据量差不多都是100多万,
就在数据库上跑了起来,半天还没运行完,当时就查看了这个会话,显示该会话的等待事件 gr cr request。
select count(*)
from t_cmp_type_bookitem bk,t_cmp_type_content ctc
where
bk.objectid=ctc.objectid
and ctc.status = 13
and bk.itemtype = '1' or bk.itemtype = '5';
当时也觉得很正常,毕竟这是一个4 节点的race, 当时想,看下有关这两张表的sql 主要在哪个节点,然后
就把该sql 放在哪个节点上跑,听取了意见,一查发现4个节点上都有,这下犯难了
select inst_id,SQL_TEXT from gv$sql where executions > 1
and PARSING_SCHEMA_NAME='MREAD'
--AND LAST_LOAD_TIME >SYSDATE - 1
AND LAST_ACTIVE_TIME > SYSDATE -1
AND ( SQL_TEXT LIKE '%t_cmp_type_bookitem%' OR SQL_TEXT LIKE '%t_cmp_type_content%')
想想,使用CTAS 方法创建 两张临时表,然后用临时表代替原表,这样就不会出现 gr cr request 等待事件了
如是就按这种方法做了,可以登了很久还没跑完,看了一下执行计划,执行计划评估的结果有 179G,不至于
这么大啊,当时请教了开发DBA。
SQL> select * from table(dbms_xplan.display_cursor('8rkakfj35d2jr', null, 'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 8rkakfj35d2jr, child number 0
-------------------------------------
select count(*) from t_cmp_type_bookitemhlb
bk,t_cmp_type_contenthlb ctc where
bk.objectid=ctc.objectid and ctc.status = :"SYS_B_0"
and bk.itemtype = :"SYS_B_1" or bk.itemtype = :"SYS_B_2"
Plan hash value: 2230377177
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 | 21
| 2 | CONCATENATION | | |
| 3 | MERGE JOIN CARTESIAN | | 10G| 197G
|* 4 | TABLE ACCESS FULL | T_CMP_TYPE_BOOKITEMHLB | 10096 | 108K
| 5 | BUFFER SORT | | 998K| 9753K
| 6 | INDEX FAST FULL SCAN| IDX_T_CMP_TYPE_CONTENT_OSHLB | 998K| 9753K
|* 7 | HASH JOIN | | 9995 | 204K
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 8 | TABLE ACCESS FULL | T_CMP_TYPE_BOOKITEMHLB | 9995 | 107K
|* 9 | INDEX FAST FULL SCAN | IDX_T_CMP_TYPE_CONTENT_OSHLB | 90793 | 886K
--------------------------------------------------------------------------------
开发DBA看了会,果断在最后一个and 条件后加了括号,问题立马解决。看下执行计划,这次执行
计划评估的结果不到1 M ,仔细看是消除了 笛卡尔集。
select count(*)
from t_cmp_type_bookitemhlb bk,t_cmp_type_contenthlb ctc
where
bk.objectid=ctc.objectid
and ctc.status = 13
and( bk.itemtype = '1' or bk.itemtype = '5');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 601123799
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 |
| 1 | SORT AGGREGATE | | 1 | 21 |
|* 2 | HASH JOIN | | 10096 | 207K|
|* 3 | TABLE ACCESS FULL | T_CMP_TYPE_BOOKITEMHLB | 10096 | 108K|
|* 4 | INDEX FAST FULL SCAN| IDX_T_CMP_TYPE_CONTENT_OSHLB | 440K| 4302K|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BK"."OBJECTID"="CTC"."OBJECTID")
3 - filter("BK"."ITEMTYPE"='1' OR "BK"."ITEMTYPE"='5')
4 - filter("CTC"."STATUS"=13)