今天跟踪程序，无意中发现一条不良的SQL语句
今天跟踪程序，无意中发现一条不良的SQL语句
分析如下：
SELECT fee_stat_name, NVL (t1.b, 0)
FROM (SELECT fee_code, NVL (SUM (tot_cost), 0) b
FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
"IF_BALANCELIST"."TOT_COST", '0', balance_dtime,
"IF_BALANCELIST"."OPER_CODE"
FROM "IF_BALANCELIST"
WHERE NVL (waste_flag, '0') '2'
UNION ALL
SELECT "IF_BALANCELIST"."FEE_CODE",
-"IF_BALANCELIST"."TOT_COST", '1', waste_dtime,
"OPER_CODE"
FROM "IF_BALANCELIST"
WHERE waste_flag = '1') ti1,
his_report_arg
WHERE balance_dtime BETWEEN his_report_arg.start_dt
AND his_report_arg.end_dt
AND ti1.oper_code = his_report_arg.oper_code
AND his_report_arg.SID = USERENV ('sessionid')
GROUP BY fee_code) t1,
(SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
"IA_FEESTATNEXUS"."FEE_STAT_CATE",
"IA_FEESTATNEXUS"."PRINT_ORDER"
FROM "IA_FEESTATNEXUS"
WHERE report_code = 'ZY01') t2
WHERE t1.fee_code(+) = t2.fee_stat_cate
ORDER BY t2.print_order
执行计划如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=54 Bytes=2268)
1 0 SORT (ORDER BY) (Cost=44 Card=54 Bytes=2268)
2 1 HASH JOIN (OUTER) (Cost=40 Card=54 Bytes=2268)
3 2 VIEW (Cost=5 Card=54 Bytes=1404)
4 3 SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
6 5 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card=54)
7 2 VIEW (Cost=35 Card=4 Bytes=64)
8 7 SORT (GROUP BY) (Cost=35 Card=4 Bytes=216)
9 8 NESTED LOOPS (Cost=31 Card=4 Bytes=216)
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
11 10 INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
12 9 VIEW
13 12 UNION-ALL
14 13 TABLE ACCESS (FULL) OF 'IF_BALANCELIST' (Cost=15 Card=3047 Bytes=63987)
15 13 TABLE ACCESS (FULL) OF 'IF_BALANCELIST' (Cost=15 Card=20313 Bytes=304695)
Statistics
----------------------------------------------------------
0 recursive calls
10 db block gets
1059 consistent gets
0 physical reads
0 redo size
1713 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
23 rows processed
主要的问题在于IF_BALANCELIST要执行两次全表扫描。而判断条件中使用balance_dtime BETWEEN
his_report_arg.start_dt AND his_report_arg.end_dt，没有很好地利用IF_BALANCELIST建立的
balance_dtime与waste_dtime索引，应该修改如下：
SELECT fee_stat_name, NVL (SUM (c), 0)
FROM (SELECT fee_stat_name, NVL (t1.b, 0) c, t2.print_order
FROM (SELECT fee_code, NVL (SUM (tot_cost), 0) b
FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
"IF_BALANCELIST"."TOT_COST", '0',
balance_dtime,
"IF_BALANCELIST"."OPER_CODE"
FROM "IF_BALANCELIST"
WHERE NVL (waste_flag, '0') '2') ti1,
his_report_arg
WHERE balance_dtime BETWEEN his_report_arg.start_dt
AND his_report_arg.end_dt
AND ti1.oper_code = his_report_arg.oper_code
AND his_report_arg.SID = USERENV ('sessionid')
GROUP BY fee_code) t1,
(SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
"IA_FEESTATNEXUS"."FEE_STAT_CATE",
"IA_FEESTATNEXUS"."PRINT_ORDER"
FROM "IA_FEESTATNEXUS"
WHERE report_code = 'ZY01') t2
WHERE t1.fee_code(+) = t2.fee_stat_cate
UNION ALL
SELECT fee_stat_name, NVL (t1.b, 0) c, t2.print_order
FROM (SELECT fee_code, NVL (SUM (tot_cost), 0) b
FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
-"IF_BALANCELIST"."TOT_COST" tot_cost, '1',
waste_dtime, "OPER_CODE"
FROM "IF_BALANCELIST"
WHERE waste_flag = '1') ti1,
his_report_arg
WHERE waste_dtime BETWEEN his_report_arg.start_dt
AND his_report_arg.end_dt
AND ti1.oper_code = his_report_arg.oper_code
AND his_report_arg.SID = USERENV ('sessionid')
GROUP BY fee_code) t1,
(SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
"IA_FEESTATNEXUS"."FEE_STAT_CATE",
"IA_FEESTATNEXUS"."PRINT_ORDER"
FROM "IA_FEESTATNEXUS"
WHERE report_code = 'ZY01') t2
WHERE t1.fee_code(+) = t2.fee_stat_cate)
GROUP BY fee_stat_name, print_order
ORDER BY print_order
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=108 Bytes=3888)
1 0 SORT (GROUP BY) (Cost=26 Card=108 Bytes=3888)
2 1 VIEW (Cost=22 Card=108 Bytes=3888)
3 2 UNION-ALL
4 3 HASH JOIN (OUTER) (Cost=11 Card=54 Bytes=2268)
5 4 VIEW (Cost=5 Card=54 Bytes=1404)
6 5 SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
8 7 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card =54)
9 4 VIEW (Cost=6 Card=1 Bytes=16)
10 9 SORT (GROUP BY) (Cost=6 Card=1 Bytes=45)
11 10 NESTED LOOPS (Cost=2 Card=1 Bytes=45)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
13 12 INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
14 11 TABLE ACCESS (BY INDEX ROWID) OF 'IF_BALANCELIST' (Cost=1 Card=3047 Bytes=63987)
15 14 INDEX (RANGE SCAN) OF 'I_IF_BLANCELIST_BALANCE_DTIME' (NON-UNIQUE)
16 3 HASH JOIN (OUTER) (Cost=11 Card=54 Bytes=2268)
17 16 VIEW (Cost=5 Card=54 Bytes=1404)
18 17 SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
19 18 TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
20 19 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card =54)
21 16 VIEW (Cost=6 Card=3 Bytes=48)
22 21 SORT (GROUP BY) (Cost=6 Card=3 Bytes=117)
23 22 NESTED LOOPS (Cost=2 Card=3 Bytes=117)
24 23 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
25 24 INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
26 23 TABLE ACCESS (BY INDEX ROWID) OF 'IF_BALANCELIST' (Cost=1 Card=20313 Bytes=304695)
27 26 INDEX (RANGE SCAN) OF 'I_IF_BALANCELIST_WASTE_DTIME' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
1715 bytes sent via SQL*Net to client
1131 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
23 rows processed
这样就能够充分利用IF_BALANCELIST建立的balance_dtime与waste_dtime索引。
今天跟踪程序，无意中发现一条不良的SQL语句
分析如下：
SELECT fee_stat_name, NVL (t1.b, 0)
FROM (SELECT fee_code, NVL (SUM (tot_cost), 0) b
FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
"IF_BALANCELIST"."TOT_COST", '0', balance_dtime,
"IF_BALANCELIST"."OPER_CODE"
FROM "IF_BALANCELIST"
WHERE NVL (waste_flag, '0') '2'
UNION ALL
SELECT "IF_BALANCELIST"."FEE_CODE",
-"IF_BALANCELIST"."TOT_COST", '1', waste_dtime,
"OPER_CODE"
FROM "IF_BALANCELIST"
WHERE waste_flag = '1') ti1,
his_report_arg
WHERE balance_dtime BETWEEN his_report_arg.start_dt
AND his_report_arg.end_dt
AND ti1.oper_code = his_report_arg.oper_code
AND his_report_arg.SID = USERENV ('sessionid')
GROUP BY fee_code) t1,
(SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
"IA_FEESTATNEXUS"."FEE_STAT_CATE",
"IA_FEESTATNEXUS"."PRINT_ORDER"
FROM "IA_FEESTATNEXUS"
WHERE report_code = 'ZY01') t2
WHERE t1.fee_code(+) = t2.fee_stat_cate
ORDER BY t2.print_order
执行计划如下：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=54 Bytes=2268)
1 0 SORT (ORDER BY) (Cost=44 Card=54 Bytes=2268)
2 1 HASH JOIN (OUTER) (Cost=40 Card=54 Bytes=2268)
3 2 VIEW (Cost=5 Card=54 Bytes=1404)
4 3 SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
6 5 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card=54)
7 2 VIEW (Cost=35 Card=4 Bytes=64)
8 7 SORT (GROUP BY) (Cost=35 Card=4 Bytes=216)
9 8 NESTED LOOPS (Cost=31 Card=4 Bytes=216)
10 9 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
11 10 INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
12 9 VIEW
13 12 UNION-ALL
14 13 TABLE ACCESS (FULL) OF 'IF_BALANCELIST' (Cost=15 Card=3047 Bytes=63987)
15 13 TABLE ACCESS (FULL) OF 'IF_BALANCELIST' (Cost=15 Card=20313 Bytes=304695)
Statistics
----------------------------------------------------------
0 recursive calls
10 db block gets
1059 consistent gets
0 physical reads
0 redo size
1713 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
23 rows processed
主要的问题在于IF_BALANCELIST要执行两次全表扫描。而判断条件中使用balance_dtime BETWEEN
his_report_arg.start_dt AND his_report_arg.end_dt，没有很好地利用IF_BALANCELIST建立的
balance_dtime与waste_dtime索引，应该修改如下：
SELECT fee_stat_name, NVL (SUM (c), 0)
FROM (SELECT fee_stat_name, NVL (t1.b, 0) c, t2.print_order
FROM (SELECT fee_code, NVL (SUM (tot_cost), 0) b
FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
"IF_BALANCELIST"."TOT_COST", '0',
balance_dtime,
"IF_BALANCELIST"."OPER_CODE"
FROM "IF_BALANCELIST"
WHERE NVL (waste_flag, '0') '2') ti1,
his_report_arg
WHERE balance_dtime BETWEEN his_report_arg.start_dt
AND his_report_arg.end_dt
AND ti1.oper_code = his_report_arg.oper_code
AND his_report_arg.SID = USERENV ('sessionid')
GROUP BY fee_code) t1,
(SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
"IA_FEESTATNEXUS"."FEE_STAT_CATE",
"IA_FEESTATNEXUS"."PRINT_ORDER"
FROM "IA_FEESTATNEXUS"
WHERE report_code = 'ZY01') t2
WHERE t1.fee_code(+) = t2.fee_stat_cate
UNION ALL
SELECT fee_stat_name, NVL (t1.b, 0) c, t2.print_order
FROM (SELECT fee_code, NVL (SUM (tot_cost), 0) b
FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
-"IF_BALANCELIST"."TOT_COST" tot_cost, '1',
waste_dtime, "OPER_CODE"
FROM "IF_BALANCELIST"
WHERE waste_flag = '1') ti1,
his_report_arg
WHERE waste_dtime BETWEEN his_report_arg.start_dt
AND his_report_arg.end_dt
AND ti1.oper_code = his_report_arg.oper_code
AND his_report_arg.SID = USERENV ('sessionid')
GROUP BY fee_code) t1,
(SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
"IA_FEESTATNEXUS"."FEE_STAT_CATE",
"IA_FEESTATNEXUS"."PRINT_ORDER"
FROM "IA_FEESTATNEXUS"
WHERE report_code = 'ZY01') t2
WHERE t1.fee_code(+) = t2.fee_stat_cate)
GROUP BY fee_stat_name, print_order
ORDER BY print_order
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=108 Bytes=3888)
1 0 SORT (GROUP BY) (Cost=26 Card=108 Bytes=3888)
2 1 VIEW (Cost=22 Card=108 Bytes=3888)
3 2 UNION-ALL
4 3 HASH JOIN (OUTER) (Cost=11 Card=54 Bytes=2268)
5 4 VIEW (Cost=5 Card=54 Bytes=1404)
6 5 SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
8 7 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card =54)
9 4 VIEW (Cost=6 Card=1 Bytes=16)
10 9 SORT (GROUP BY) (Cost=6 Card=1 Bytes=45)
11 10 NESTED LOOPS (Cost=2 Card=1 Bytes=45)
12 11 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
13 12 INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
14 11 TABLE ACCESS (BY INDEX ROWID) OF 'IF_BALANCELIST' (Cost=1 Card=3047 Bytes=63987)
15 14 INDEX (RANGE SCAN) OF 'I_IF_BLANCELIST_BALANCE_DTIME' (NON-UNIQUE)
16 3 HASH JOIN (OUTER) (Cost=11 Card=54 Bytes=2268)
17 16 VIEW (Cost=5 Card=54 Bytes=1404)
18 17 SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
19 18 TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
20 19 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card =54)
21 16 VIEW (Cost=6 Card=3 Bytes=48)
22 21 SORT (GROUP BY) (Cost=6 Card=3 Bytes=117)
23 22 NESTED LOOPS (Cost=2 Card=3 Bytes=117)
24 23 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
25 24 INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
26 23 TABLE ACCESS (BY INDEX ROWID) OF 'IF_BALANCELIST' (Cost=1 Card=20313 Bytes=304695)
27 26 INDEX (RANGE SCAN) OF 'I_IF_BALANCELIST_WASTE_DTIME' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
1715 bytes sent via SQL*Net to client
1131 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
16 sorts (memory)
0 sorts (disk)
23 rows processed
这样就能够充分利用IF_BALANCELIST建立的balance_dtime与waste_dtime索引。
