简单的调整几秒与几个小时的差别

简单的调整，执行几个小时的语句的执行时间缩短为几秒。

[@more@]

一、问题提出
通过对数据物理读的监测，发现如下的语句在监测时已经耗用了600多万次物理读，而且还在执行中。故需对这部分查询做出调整。

语句如下：
SELECT A.TRADETYPECODE 业务类型编码,
A.ACPTTIME 受理时间,
A.ACPTSITEID 受理部门编码,
A.ACPTOPTRID 受理员工编码
FROM CBS_HLJ.TF_B_ TRADE_HIS_HIS A, CBS_HLJ.TF_B_TRADE_SUBSCRB_HIS_HIS B
WHERE A.TRADEID = B.TRADEID
AND B.SVCNUM = '13159858483'

注：CBS_HLJ.TF_B_ TRADE_HIS_HIS,CBS_HLJ.TF_B_TRADE_SUBSCRB_HIS_HIS这两个表都是千万行数据级的大表。

二、问题初步处理
如上的两个表都是千万级数据量级的表，通过对这条语句关联的两个表的检查，发现A表的TRADEID,B表的TRADEID及SVCNUM并没有索引，按开发人员的想法，建上索引后这个问题就会有所改善，我对相关列都建立了索引。

SQL>SELECT index_name,table_owner,table_name,column_name
2 FROM DBA_IND_COLUMNS
3 WHERE TABLE_NAME IN ('TF_B_TRADE_HIS_HIS', 'TF_B_TRADE_SUBSCRB_HIS_HIS')
4 /

INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME
---------- ----------- ---------- -----------
I1_TF_B_TRADE_HIS_HIS CBS_HLJ TF_B_TRADE_HIS_HIS TRADEID
I1_TF_B_TRADE_HIS_HIS CBS_HLJ TF_B_TRADE_HIS_HIS ACPTOPTRID
I1_TF_B_TRADE_HIS_HIS CBS_HLJ TF_B_TRADE_HIS_HIS ACPTTIME
IDX2_TF_B_TRADE_SUBSCRB_HIS_H CBS_HLJ TF_B_TRADE_SUBSCRB_HIS_HIS SVCNUM
IDX2_TF_B_TRADE_SUBSCRB_H_H CBS_HLJ TF_B_TRADE_SUBSCRB_HIS_HIS SUBSCRBID
IDX1_TF_B_TRADE_SUBSCRB_HIS_H CBS_HLJ TF_B_TRADE_SUBSCRB_HIS_HIS TRADEID

三、看一下有索引状态下这个语句的速度
1、测一下有索引情形下的速度
08:44:30 SQL> SELECT
08:44:43 2 A.TRADETYPECODE,
08:44:43 3 A.ACPTTIME,
08:44:43 4 A.ACPTSITEID,
08:44:43 5 A.ACPTOPTRID
08:44:43 6 FROM CBS_HLJ.TF_B_TRADE_HIS_HIS A, CBS_HLJ.TF_B_TRADE_SUBSCRB_HIS_HIS B
08:44:43 7 WHERE A.TRADEID = B.TRADEID
08:44:43 8 AND B.SVCNUM = '13159858483'
08:44:44 9 /

ORA-01013: user requested cancel of current operation

10:16:46 SQL>

2、看一下语的执行计划
10:18:18 SQL> set autotrace traceonly explain
10:18:26 SQL> SELECT
10:18:42 2 A.TRADETYPECODE,
10:18:42 3 A.ACPTTIME,
10:18:42 4 A.ACPTSITEID,
10:18:43 5 A.ACPTOPTRID
10:18:43 6 FROM CBS_HLJ.TF_B_TRADE_HIS_HIS A, CBS_HLJ.TF_B_TRADE_SUBSCRB_HIS_HIS B
10:18:43 7 WHERE A.TRADEID = B.TRADEID
10:18:43 8 AND B.SVCNUM = '13159858483'
10:18:43 9 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=25153 Card=4175414 B
ytes=313156050)

1 0 MERGE JOIN (Cost=25153 Card=4175414 Bytes=313156050)
2 1 PARTITION RANGE (ITERATOR)
3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TF_B_TRADE_HIS_HIS' (Cost=826 Card=417541387 Bytes=17954279641)

4 3 INDEX (FULL SCAN) OF 'I2_TF_B_TRADE_HIS_HIS' (NON-UNIQUE) (Cost=26 Card=417541387)

5 1 SORT (JOIN) (Cost=24327 Card=4111375 Bytes=131564000)
6 5 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TF_B_TRADE_SUBSCRB_HIS_HIS' (Cost=2 Card=4111375 Bytes=131564000)

7 6 INDEX (RANGE SCAN) OF 'IDX2_TF_B_TRADE_SUBSCRB_HIS_H' (NON-UNIQUE) (Cost=1 Card=1)

10:18:52 SQL>

3、分析
可以看到，这条语句自08:44分开始执行，至10:16:46时我CANCEL掉时已经用掉了92分钟的时间，这条语句还没有执行出来。而通过执行计划也可以看出，相关的列都是走索引的，走索引为什么还会这么慢呢？
原因是因为Oracle根据目前系统的状态(版本、初始化参数、statistics)采用了INDEX (FULL SCAN)及MERGE JOIN的方式，并不能说是这种方式不好，只能说是对这条语句不适合。

四、调整语句的执行方式

因语句对B表的svcnum做了限定，这样B返回的结果集只有很少的几行，同时A,B的TRADEID都是选择性很高的索引，所以用NESTED LOOP连接更为适合

加Hint影响语句走nested loop方式

08:43:07 SQL> SELECT /*+ USE_NL(B A)*/
08:43:08 2 A.TRADETYPECODE,
08:43:08 3 A.ACPTTIME,
08:43:08 4 A.ACPTSITEID,
08:43:08 5 A.ACPTOPTRID
08:43:08 6 FROM CBS_HLJ.TF_B_TRADE_HIS_HIS A, CBS_HLJ.TF_B_TRADE_SUBSCRB_HIS_HIS B
08:43:08 7 WHERE A.TRADEID = B.TRADEID
08:43:08 8 AND B.SVCNUM = '13159858483'
08:43:09 9 /

TR ACPTTIME ACPTSITEID ACPTOPTRID
-- --------- --------------- --------------------
86 09-OCT-04 10 100000000
81 09-OCT-04 10 10AY5YC09
05 18-OCT-04 1000A1 10A004703
08 27-NOV-03 1005GX 10A05X305
01 11-SEP-02 1005GX 10A05X305
10 03-SEP-03 1005GX 10A05X305
04 25-JAN-03 10Y5YC 10AY5YC09
04 25-JAN-03 10Y5YC 10AY5YC09

8 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8222752 Card=4175414
Bytes=313156050)

1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TF_B_TRADE_HIS_HIS
' (Cost=2 Card=1 Bytes=43)

2 1 NESTED LOOPS (Cost=8222752 Card=4175414 Bytes=313156050)
3 2 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TF_B_TRADE_SU
BSCRB_HIS_HIS' (Cost=2 Card=4111375 Bytes=131564000)

4 3 INDEX (RANGE SCAN) OF 'IDX2_TF_B_TRADE_SUBSCRB_HIS_H
' (NON-UNIQUE) (Cost=1 Card=1644550)

5 2 PARTITION RANGE (ITERATOR)
6 5 INDEX (RANGE SCAN) OF 'I2_TF_B_TRADE_HIS_HIS' (NON-U
NIQUE) (Cost=1 Card=1)

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

08:43:16 SQL>

可见只用了仅仅7秒的时间就完成了查询。几个小时与几秒的差别!

五、总结

本文只是一个抛砖引玉的作用，关于表连接等高级的调整技术写起来得有几十页，Oracle提供的5种表数据连接方法不能说哪种好哪种坏，只能说是对你的应用哪种更适合。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/717880/viewspace-888439/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/717880/viewspace-888439/