1 | | |
|* 31 | TABLE ACCESS FULL | TBL_USER_STORES | 1 | 10 | | 175 (1)| 00:00:03 | | |
|* 32 | TABLE ACCESS FULL | TBL_USER_STORES | 1 | 10 | | 175 (1)| 00:00:03 | | |
|* 33 | TABLE ACCESS BY INDEX ROWID | TBL_STORES | 1 | 18 | | 2 (0)| 00:00:01 | | |
|* 34 | INDEX UNIQUE SCAN | XPK_TBL_STORES | 1 | | | 1 (0)| 00:00:01 | | |
|* 35 | TABLE ACCESS BY INDEX ROWID | TBL_STORES | 1 | 18 | | 2 (0)| 00:00:01 | | |
|* 36 | INDEX UNIQUE SCAN | XPK_TBL_STORES | 1 | | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
27716859 consistent gets
1 physical reads
0 redo size
16407 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
20 rows processed
2771万次逻辑读。
在该SQL涉及到的所有表中，TBL_BILL是主要的一张大表，此处对TBL_BILL的访问都做了分区裁剪，两个执行计划的主要差异是A SCHEMA以TBL_BILL为驱动表进行NESTED LOOPS OUTER，
而B Schema则以TBL_BUSINESS_TYPE表作为驱动表对TBL_BILL进行HASH JOIN，怀疑是因为A走了错误的执行计划导致，于是使用hint使A对TBL_BILL走HASH JOIN，发现没有任何改善，
于是看看时间具体消耗在哪：
SELECT /*+gather_plan_statistics */ *
FROM (SELECT A.*, ROWNUM RN
FROM (select
t3.check_show,
t1.*,
t2.storesid,
to_char(rdate, 'yyyy-mm-dd') as to_rdate,
to_char(submitdate, 'yyyy-mm-dd') as to_submitdate,
to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate,
to_char(expected_shipdate, 'yyyy-mm-dd') as to_expected_shipdate2,
to_char(billingdate, 'yyyy-mm-dd hh24:mi:ss') as to_billingdate,
to_char(dealtotal, 'fm9999999990.00') as to_dealtotal,
(nvl(cashreceiptssum, 0) + nvl(received, 0)) as to_cashreceiptssum,
to_char((nvl(debtsum, 0) - nvl(received, 0)),
'fm9999999990.00') to_debtsum,
nvl(amounttotal, 0) as to_amounttotal,
t4.reduce_points
from SchemaA.v0bill t1,
SchemaA.tbl_stores t2
,SchemaA.TBL_BILL_CHECKSTATE_SHOW t3,
SchemaA.m_mempoint_logs t4
where ((posbillno is not null and BCOMPLETE = 1) or
posbillno is null)
and t1.StoreRoomID = t2.storesid
and t1.billsubcase = t3.billsubcase
and t1.check_status = t3.check_status
and (instoreroomid in
(select storesid
from SchemaA.tbl_user_stores
where employeeid = 3945) or
outstoreroomid in
(select storesid
from SchemaA.tbl_user_stores
where employeeid = 3945))
and servicStatus = 1
and rdate >= to_date('2015-06-04', 'yyyy-mm-dd')
and rdate <=
to_date('2015-06-11 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and (t1.billsubcase in ('75' ,'711') or
(instoreroomid in
(select storesid
from SchemaA.tbl_stores
where areacode like '001022%') or
outstoreroomid in
(select storesid
from SchemaA.tbl_stores
where areacode like '001022%')))
and t1.billno=t4.billno(+)
and T4.billtype (+)= 4
ORDER by rdate desc, billingdate desc, t1.billno desc) A
WHERE ROWNUM <= (1 * 20))
WHERE RN > ((1 - 1)* 20);
发现主要时间都消耗在对TBL_USER_STORES上（对该表会有2次filter操作（全表扫描），占总耗时的99%），如下图：
![\]()
再看看2家企业该表（TBL_USER_STORES）的数据量不是一个量级，如下：
A Schema记录数：136057
B Schema记录数：7439
原来这就是原因所在了，于是优化表TBL_USER_STORES的访问路径，创建一个组合索引：
create index SchemaA.i_TBL_USER_STORESon SchemaA.TBL_USER_STORES(employeeid,storesid);
创建索引后，在SchemaA上执行该SQL，执行时长在3秒以内:
?
![\]()