Chinese

事发问题，测试环境程序测试完后第二天，发布于生产环境，结果出现了大量用户堵塞问题，查看数据库为某一条语句效率很低，紧急调整后事故解决，事后开总结会，开发的提出在测试环境语句效率很高，生产环境却很低，询问本人
本人经拙劣测试把语句摘到最小化，发现确实效率差距巨大，以下为语句
SELECT count(*) FROM PAY_DTL T WHERE
(T.MER_ID = '0000043686' OR T.PAY_CUST_ID = '0000043686' OR T.RECV_CUST_ID= '0000043686' OR T.ORDER_ID IN (SELECT I.ORDER_ID FROM AUDIT_GOPAY_ORDER_ITEM I WHERE I.RECV_EMAIL = 'yanshi_gfb@126.com'))
AND T.EXT_TXN_DT >= '20120923' AND T.EXT_TXN_DT <= '20121023';

当时生产库PAY表有480W行，测试库有350W行，差距很小，但执行计划结果如下：
生产库：
Execution Plan
----------------------------------------------------------
Plan hash value: 4060007937

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 1608 (1)| 00:00:20 |
| 1 | SORT AGGREGATE | | 1 | 49 | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| PAY_DTL | 3255 | 155K| 1608 (1)| 00:00:20 |
|* 4 | INDEX RANGE SCAN | IDX_PAY_DTL2 | 3255 | | 69 (2)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | AUDIT_GOPAY_ORDER_ITEM | 1 | 35 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("T"."PAY_CUST_ID"='0000043686' OR "T"."MER_ID"='0000043686' OR
"T"."RECV_CUST_ID"='0000043686' OR EXISTS (SELECT 0 FROM "AUDIT_GOPAY_ORDER_ITEM" "I" WHERE
"I"."RECV_EMAIL"='tukevin003@163.com' AND "I"."ORDER_ID"=:B1))
4 - access("T"."EXT_TXN_DT">='20120923' AND "T"."EXT_TXN_DT"<='20121023')
filter((("T"."TXN_TYPE"='00' OR "T"."TXN_TYPE"='01' OR "T"."TXN_TYPE"='02' OR
"T"."TXN_TYPE"='03' OR "T"."TXN_TYPE"='06' OR "T"."TXN_TYPE"='25' OR "T"."TXN_TYPE"='27' OR
"T"."TXN_TYPE"='45' OR "T"."TXN_TYPE"='49' OR "T"."TXN_TYPE"='64' OR "T"."TXN_TYPE"='71' OR
"T"."TXN_TYPE"='83' OR "T"."TXN_TYPE"='90' OR "T"."TXN_TYPE"='91' OR "T"."TXN_TYPE"='93' OR
"T"."TXN_TYPE"='96') OR "T"."TXN_TYPE"='24' AND ("T"."TXN_STA_CD"='2' OR "T"."TXN_STA_CD"='6'))
AND "T"."TXN_STA_CD"<>'0' AND "T"."TXN_STA_CD"<>'x' AND "T"."TXN_STA_CD"<>'y' AND
"T"."TXN_STA_CD"<>'z')
5 - filter("I"."RECV_EMAIL"='tukevin003@163.com' AND "I"."ORDER_ID"=:B1)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9257155 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

测试库：
Execution Plan
----------------------------------------------------------
Plan hash value: 4060007937

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 55 | 787 (0)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 55 | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| PAY_DTL | 1914 | 102K| 787 (0)| 00:00:10 |
|* 4 | INDEX RANGE SCAN | IDX_PAY_DTL2 | 1914 | | 18 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | AUDIT_GOPAY_ORDER_ITEM | 1 | 37 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("T"."RECV_CUST_ID"='0000043686' OR "T"."PAY_CUST_ID"='0000043686' OR
"T"."MER_ID"='0000043686' OR EXISTS (SELECT 0 FROM "AUDIT_GOPAY_ORDER_ITEM" "I" WHERE
"I"."RECV_EMAIL"='tukevin003@163.com' AND "I"."ORDER_ID"=:B1))
4 - access("T"."EXT_TXN_DT">='20120923' AND "T"."EXT_TXN_DT"<='20121023')
filter("T"."TXN_STA_CD"<>'0' AND "T"."TXN_STA_CD"<>'z' AND "T"."TXN_STA_CD"<>'x' AND
"T"."TXN_STA_CD"<>'y' AND (("T"."TXN_TYPE"='00' OR "T"."TXN_TYPE"='01' OR "T"."TXN_TYPE"='02'
OR "T"."TXN_TYPE"='03' OR "T"."TXN_TYPE"='06' OR "T"."TXN_TYPE"='25' OR "T"."TXN_TYPE"='27' OR
"T"."TXN_TYPE"='45' OR "T"."TXN_TYPE"='49' OR "T"."TXN_TYPE"='64' OR "T"."TXN_TYPE"='71' OR
"T"."TXN_TYPE"='83' OR "T"."TXN_TYPE"='90' OR "T"."TXN_TYPE"='91' OR "T"."TXN_TYPE"='93' OR
"T"."TXN_TYPE"='96') OR "T"."TXN_TYPE"='24' AND ("T"."TXN_STA_CD"='2' OR "T"."TXN_STA_CD"='6')))
5 - filter("I"."RECV_EMAIL"='tukevin003@163.com' AND "I"."ORDER_ID"=:B1)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3581 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

事后我怀疑是因为语句嵌套的问题，导致100W的数据差异导致逻辑读和效率差距很大，事后我将生产库的数据完全拷贝到测试库，2个库数据完全一样的时候，执行同样的语句，测试库逻辑读只上升到170W，效率还是很快，生产库为1000多W
生产库为RAC，测试库为单机，怀疑结构问题，将语句在生产库的物理DG端（单机，本地磁盘和测试库配置一样的服务器）执行，仍为1000W逻辑读，效率很低
数据库版本都为11.2.0.3