某省电信在做批扣（批销）时，出现严重的性能问题，发现下面这一条SQL性能非常低下：
SELECT A.ACCT_BALANCE_ID,
A.BALANCE_TYPE_ID,
A.ACCT_ID,
NVL(A.SERV_ID, -1) SERV_ID,
NVL(A.ITEM_GROUP_ID, -1) ITEM_GROUP_ID,
A.OBJECT_TYPE_ID,
F.PRIORITY,
A.BALANCE,
NVL(A.CYCLE_UPPER, -1) CYCLE_UPPER,
NVL(A.CYCLE_LOWER, -1) CYCLE_LOWER,
NVL(A.CYCLE_UPPER_TYPE, ' ') CYCLE_UPPER_TYPE,
NVL(A.CYCLE_LOWER_TYPE, ' ') CYCLE_LOWER_TYPE,
B.ADJUST_FLAG ADJUST_FLAG,
B.ALLOW_TRANS ALLOW_TRANS,
B.CORPUS_FLAG,
NVL(TO_CHAR(A.EFF_DATE, 'YYYYMMDDHH24MISS'), ' ') EFF_DATE,
NVL(TO_CHAR(A.EXP_DATE, 'YYYYMMDDHH24MISS'), ' ') EXP_DATE,
A.STATE,
TO_CHAR(A.STATE_DATE, 'YYYYMMDDHH24MISS') STATE_DATE,
B.BALANCE_TYPE_NAME,
NVL(C.ACCT_NAME, ' ') ACCT_NAME,
NVL(D.ACC_NBR, ' ') SERV_NAME,
NVL(E.ITEM_GROUP_NAME, ' ') ITEM_GROUP_NAME
FROM (SELECT ACCT_BALANCE_ID,
BALANCE_TYPE_ID,
ACCT_ID,
NVL(SERV_ID, -1) SERV_ID,
NVL(ITEM_GROUP_ID, -1) ITEM_GROUP_ID,
OBJECT_TYPE_ID,
BALANCE,
NVL(CYCLE_UPPER, -1) CYCLE_UPPER,
NVL(CYCLE_LOWER, -1) CYCLE_LOWER,
NVL(CYCLE_UPPER_TYPE, ' ') CYCLE_UPPER_TYPE,
NVL(CYCLE_LOWER_TYPE, ' ') CYCLE_LOWER_TYPE,
EFF_DATE,
EXP_DATE,
STATE,
STATE_DATE,
0 SHARE_RULE_PRIORITY
FROM ACCT_BALANCE
WHERE ACCT_ID = :LACCTID
AND BALANCE > 0
AND BALANCE_TYPE_ID != 1
AND STATE = '10A'
UNION
SELECT A1.ACCT_BALANCE_ID,
A1.BALANCE_TYPE_ID,
B1.ACCT_ID,
NVL(B1.SERV_ID, -1) SERV_ID,
NVL(B1.ITEM_GROUP_ID, -1) ITEM_GROUP_ID,
A1.OBJECT_TYPE_ID,
A1.BALANCE,
NVL(B1.UPPER_AMOUNT, -1) CYCLE_UPPER,
NVL(B1.LOWER_AMOUNT, -1) CYCLE_LOWER,
NVL(A1.CYCLE_UPPER_TYPE, ' ') CYCLE_UPPER_TYPE,
NVL(A1.CYCLE_LOWER_TYPE, ' ') CYCLE_LOWER_TYPE,
A1.EFF_DATE,
A1.EXP_DATE,
A1.STATE,
A1.STATE_DATE,
B1.PRIORITY SHARE_RULE_PRIORITY
FROM ACCT_BALANCE A1, BALANCE_SHARE_RULE B1
WHERE A1.ACCT_BALANCE_ID = B1.ACCT_BALANCE_ID
AND B1.ACCT_ID = :LACCTID
AND A1.BALANCE > 0
AND A1.BALANCE_TYPE_ID != 1
AND A1.STATE = '10A'
AND NVL(B1.EFF_DATE, SYSDATE) < = SYSDATE AND NVL(B1.EXP_DATE, SYSDATE) >= SYSDATE) A,
BALANCE_TYPE B,
ACCT C,
SERV D,
A_BALANCE_ITEM_GROUP E,
A_BALANCE_OBJECT_TYPE F
WHERE A.BALANCE_TYPE_ID = B.BALANCE_TYPE_ID
AND A.OBJECT_TYPE_ID = F.OBJECT_TYPE_ID
AND A.ACCT_ID = C.ACCT_ID
AND A.SERV_ID = D.SERV_ID(+)
AND A.ITEM_GROUP_ID = E.ITEM_GROUP_ID(+)
ORDER BY F.PRIORITY,
B.PRIORITY,
A.SHARE_RULE_PRIORITY ASC,
A.EXP_DATE ASC,
A.EFF_DATE ASC,
A.BALANCE ASC
查看执行计划：
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2723G| 696T| | 7776M| | |
| 1 | SORT ORDER BY | | 2723G| 696T| 1503T| 7776M| | |
|* 2 | HASH JOIN | | 2723G| 696T| 59M| 39355 | | |
|* 3 | HASH JOIN | | 228K| 56M| | 23918 | | |
| 4 | TABLE ACCESS FULL | BALANCE_TYPE | 8 | 184 | | 7 | | |
|* 5 | HASH JOIN | | 228K| 51M| | 23907 | | |
| 6 | TABLE ACCESS FULL | A_BALANCE_OBJECT_TYPE | 4 | 16 | | 7 | | |
|* 7 | HASH JOIN OUTER | | 228K| 50M| 43M| 23896 | | |
|* 8 | HASH JOIN OUTER | | 228K| 40M| 38M| 23199 | | |
| 9 | VIEW | | 228K| 36M| | 2043 | | |
| 10 | SORT UNIQUE | | 228K| 11M| 38M| 2043 | | |
| 11 | UNION-ALL | | | | | | | |
|* 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACCT_BALANCE | 228K| 11M| | 50 | ROWID | ROW L |
|* 13 | INDEX RANGE SCAN | IDX_ACCT_BALANCE_ACCT_ID42 | 121K| | | 3 | | |
| 14 | NESTED LOOPS | | 1 | 146 | | 4 | | |
|* 15 | TABLE ACCESS FULL | BALANCE_SHARE_RULE | 1 | 109 | | 2 | | |
|* 16 | TABLE ACCESS BY GLOBAL INDEX ROWID| ACCT_BALANCE | 1 | 37 | | 2 | ROWID | ROW L |
|* 17 | INDEX UNIQUE SCAN | PK_P_ACCT_BALANCE2 | 1 | | | 1 | | |
| 18 | PARTITION RANGE ALL | | | | | | 1 | 63 |
| 19 | TABLE ACCESS FULL | SERV | 12M| 258M| | 14070 | 1 | 63 |
| 20 | TABLE ACCESS FULL | A_BALANCE_ITEM_GROUP | 244 | 11224 | | 7 | | |
| 21 | PARTITION RANGE ALL | | | | | | 1 | 63 |
| 22 | TABLE ACCESS FULL | ACCT | 11M| 239M| | 8505 | 1 | 63 |
------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("A"."ACCT_ID"="C"."ACCT_ID")
3 - access("A"."BALANCE_TYPE_ID"="B"."BALANCE_TYPE_ID")
5 - access("A"."OBJECT_TYPE_ID"="F"."OBJECT_TYPE_ID")
7 - access("A"."ITEM_GROUP_ID"="E"."ITEM_GROUP_ID"(+))
8 - access("A"."SERV_ID"="D"."SERV_ID"(+))
12 - filter("ACCT_BALANCE"."BALANCE">0 AND "ACCT_BALANCE"."BALANCE_TYPE_ID"<>1 AND "ACCT_BALANCE"."STATE"='10A')
13 - access("ACCT_BALANCE"."ACCT_ID"=TO_NUMBER(:Z))
15 - filter("B1"."ACCT_ID"=TO_NUMBER(:Z) AND NVL("B1"."EFF_DATE",SYSDATE@!)< =SYSDATE@! AND NVL("B1"."EXP_DATE",SYSDATE@!)>=SYSDATE@!)
16 - filter("A1"."BALANCE">0 AND "A1"."BALANCE_TYPE_ID"<>1 AND "A1"."STATE"='10A')
17 - access("A1"."ACCT_BALANCE_ID"="B1"."ACCT_BALANCE_ID")
