今天遇到一个SQL的性能问题。在使用拉平维时，关联了多张表，有A B C D E F G这些表，其中只有A表的数据量很大，并且是按月分区的分区表，其他表都是维度参照表，数据量较小，从几十条到几百条不等。
这条关联多张表的SQL语句，每天早上8点都会执行一次，将数据加载到数据集市。平日都挺快的，10秒以内就出结果（A表每天的数据量在5万左右）。但是今天执行了7月1日的数据，居然执行了861秒才返回结果。SQL与之前相比，除了日期有改变，其他无任何改变。
排除了数据本身的错误。
现在分析，可能是因为数据在新的分区中，没有收集信息。解决步骤：
1.查询dba_tab_partitions视图，查看该分区是不是没有收集信息
SQL> SELECT table_owner,table_name,partition_name,num_rows,blocks FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=UPPER('bam_cbbs_plf_t_gd') ORDER BY partition_name
|
ROWNUM |
TABLE_OWNER |
TABLE_NAME |
PARTITION_NAME |
NUM_ROWS |
BLOCKS |
|
14 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200901 |
1244048 |
6067 |
|
15 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200902 |
1235399 |
6067 |
|
16 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200903 |
1351962 |
6577 |
|
17 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200904 |
1225338 |
6067 |
|
18 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200905 |
1297705 |
6577 |
|
19 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200906 |
1211950 |
6067 |
|
20 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200907 |
1212195 |
6067 |
|
21 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200908 |
1291216 |
6067 |
|
22 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200909 |
1146086 |
5557 |
|
23 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200910 |
1236304 |
6067 |
|
24 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200911 |
1149690 |
5557 |
|
25 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200912 |
1146842 |
5557 |
|
26 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201001 |
1268502 |
6577 |
|
27 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201002 |
1248750 |
6067 |
|
28 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201003 |
1319896 |
6577 |
|
29 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201004 |
1259619 |
6577 |
|
30 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201005 |
1314548 |
6577 |
|
31 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201006 |
1263444 |
6067 |
|
32 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201007 |
0 |
0 |
|
33 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201008 |
0 |
0 |
|
34 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201009 |
0 |
0 |
果然，在m_201007这个分区上，没有统计信息
2. 手工收集信息
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'bam', tabname=>'bam_cbbs_plf_t_gd',partname=>'M_201007',cascade=>true);
PL/SQL procedure successfully completed
PS:如果运行速度较慢，半天没有结果，又急不可耐的话，可以使用以下SQL查看该过程执行的剩余时间：
SQL> SELECT * FROM V$SESSION_LONGOPS WHERE TIME_REMAINING<>0;
3. 验证是否成功
SQL> SELECT table_owner,table_name,partition_name,num_rows,blocks FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=UPPER('bam_cbbs_plf_t_gd') ORDER BY partition_name
|
ROWNUM |
TABLE_OWNER |
TABLE_NAME |
PARTITION_NAME |
NUM_ROWS |
BLOCKS |
|
14 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200901 |
1244048 |
6067 |
|
15 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200902 |
1235399 |
6067 |
|
16 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200903 |
1351962 |
6577 |
|
17 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200904 |
1225338 |
6067 |
|
18 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200905 |
1297705 |
6577 |
|
19 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200906 |
1211950 |
6067 |
|
20 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200907 |
1212195 |
6067 |
|
21 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200908 |
1291216 |
6067 |
|
22 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200909 |
1146086 |
5557 |
|
23 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200910 |
1236304 |
6067 |
|
24 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200911 |
1149690 |
5557 |
|
25 |
BAM |
BAM_CBBS_PLF_T_GD |
M_200912 |
1146842 |
5557 |
|
26 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201001 |
1268502 |
6577 |
|
27 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201002 |
1248750 |
6067 |
|
28 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201003 |
1319896 |
6577 |
|
29 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201004 |
1259619 |
6577 |
|
30 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201005 |
1314548 |
6577 |
|
31 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201006 |
1263444 |
6067 |
|
32 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201007 |
51535 |
247 |
|
33 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201008 |
0 |
0 |
|
34 |
BAM |
BAM_CBBS_PLF_T_GD |
M_201009 |
0 |
0 |
如图，现在已经成功生成了统计信息。那么来验证一下SQL的执行计划吧！
3. 运行SQL。发现效率还是不高。分析执行计划
SQL> set autotrace traceonly;
SQL> SELECT a.curr_deal
2 , b.dr_type, b.src_dr_type, b.dr_name
3 , c.user_id, c.user_type, c.user_name
4 , d.bill_id, d.bill_flag, d.bill_name
, e.prov_id, e.prov_code, e.prov_name
5 6 , f.prov_id, f.prov_code, f.prov_name
7 , g.prov_id, g.prov_code, g.prov_name
8 , h.sp_id, h.sp_code, h.sp_name
9 , i.oper_id, i.oper_code, i.oper_name
10 , a.sheet_cnt
11 , a.bal_fee
12 , a.outcome1
13 , a.income1
14 , a.income2
15 , a.income3
16 FROM bam_cbbs_plf_t_gd a,bam_t01_dr_type_def b
17 , bam_t01_user_type_def c, bam_t01_bill_flag_def d
18 , bam_t01_province_def e, bam_t01_province_def f
19 , bam_t01_province_def g, bam_t01_sp_code_def h
20 , bam_t01_sp_oper_code_def i
21 WHERE a.curr_deal = '20100701'
22 AND a.dr_type = b.src_dr_type
23 AND a.user_type = c.user_type
24 AND a.bill_flag = d.bill_flag
25 AND a.chrg_prov = e.prov_code
26 AND a.up_prov = f.prov_code
27 AND a.visit_prov = g.prov_code
28 AND a.sp_code = h.sp_code
29 AND b.dr_type = h.dr_type
30 AND get_char2date(a.curr_deal) >= h.valid_date
31 AND get_char2date(a.curr_deal) < h.expire_date
32 AND a.oper_code = i.oper_code
33 AND h.sp_id = i.sp_code
34 AND b.dr_type = i.dr_type
35 AND get_char2date(a.curr_deal) >= i.valid_date
36 AND get_char2date(a.curr_deal) < i.expire_date;
51339 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 12255921
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 317 | 397 (2)| 00:00:06 | | |
|* 1 | HASH JOIN | | 1 | 317 | 397 (2)| 00:00:06 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | BAM_T01_PROVINCE_DEF | 1 | 18 | 1 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 253 | 101 (3)| 00:00:02 | | |
| 4 | NESTED LOOPS | | 1 | 235 | 100 (3)| 00:00:02 | | |
| 5 | NESTED LOOPS | | 1 | 217 | 99 (4)| 00:00:02 | | |
| 6 | NESTED LOOPS | | 1 | 199 | 98 (4)| 00:00:02 | | |
| 7 | NESTED LOOPS | | 1 | 175 | 97 (4)| 00:00:02 | | |
|* 8 | HASH JOIN | | 1 | 149 | 96 (4)| 00:00:02 | | |
|* 9 | HASH JOIN | | 2873 | 230K| 11 (10)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | BAM_T01_DR_TYPE_DEF | 63 | 1575 | 3 (0)| 00:00:01 | | |
| 11 | TABLE ACCESS FULL | BAM_T01_SP_CODE_DEF | 2919 | 162K| 7 (0)| 00:00:01 | | |
| 12 | PARTITION RANGE SINGLE | | 51530 | 3371K| 84 (2)| 00:00:02 | 32 | 32 |
|* 13 | TABLE ACCESS FULL | BAM_CBBS_PLF_T_GD | 51530 | 3371K| 84 (2)| 00:00:02 | 32 | 32 |
| 14 | TABLE ACCESS BY INDEX ROWID| BAM_T01_USER_TYPE_DEF | 1 | 26 | 1 (0)| 00:00:01 | | |
|* 15 | INDEX RANGE SCAN | IDX_T01_USER_TYPE | 1 | | 0 (0)| 00:00:01 | | |
| 16 | TABLE ACCESS BY INDEX ROWID | BAM_T01_BILL_FLAG_DEF | 1 | 24 | 1 (0)| 00:00:01 | | |
|* 17 | INDEX RANGE SCAN | IDX_T01_BILL_FLAG | 1 | | 0 (0)| 00:00:01 | | |
| 18 | TABLE ACCESS BY INDEX ROWID | BAM_T01_PROVINCE_DEF | 1 | 18 | 1 (0)| 00:00:01 | | |
|* 19 | INDEX RANGE SCAN | IDX_T01_PROV_CODE | 1 | | 0 (0)| 00:00:01 | | |
| 20 | TABLE ACCESS BY INDEX ROWID | BAM_T01_PROVINCE_DEF | 1 | 18 | 1 (0)| 00:00:01 | | |
|* 21 | INDEX RANGE SCAN | IDX_T01_PROV_CODE | 1 | | 0 (0)| 00:00:01 | | |
|* 22 | INDEX RANGE SCAN | IDX_T01_PROV_CODE | 1 | | 0 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS FULL | BAM_T01_SP_OPER_CODE_DEF | 137K| 8611K| 295 (2)| 00:00:05 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OPER_CODE"="I"."OPER_CODE" AND "H"."SP_ID"="I"."SP_CODE" AND "B"."DR_TYPE"="I"."DR_TYPE")
filter("I"."VALID_DATE"<="GET_CHAR2DATE"("A"."CURR_DEAL") AND
"I"."EXPIRE_DATE">"GET_CHAR2DATE"("A"."CURR_DEAL"))
8 - access("A"."DR_TYPE"="B"."SRC_DR_TYPE" AND "A"."SP_CODE"="H"."SP_CODE")
filter("H"."VALID_DATE"<="GET_CHAR2DATE"("A"."CURR_DEAL") AND
"H"."EXPIRE_DATE">"GET_CHAR2DATE"("A"."CURR_DEAL"))
9 - access("B"."DR_TYPE"="H"."DR_TYPE")
10 - filter("B"."SRC_DR_TYPE" IS NOT NULL)
13 - filter("A"."CURR_DEAL"='20100701')
15 - access("A"."USER_TYPE"="C"."USER_TYPE")
17 - access("A"."BILL_FLAG"="D"."BILL_FLAG")
19 - access("A"."CHRG_PROV"="E"."PROV_CODE")
21 - access("A"."UP_PROV"="F"."PROV_CODE")
22 - access("A"."VISIT_PROV"="G"."PROV_CODE")
Statistics
----------------------------------------------------------
205437 recursive calls
0 db block gets
218655 consistent gets
0 physical reads
0 redo size
5187289 bytes sent via SQL*Net to client
38134 bytes received via SQL*Net from client
3424 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
51339 rows processed
从执行计划上看，BAM_CBBS_PLF_T_GD表（A表），使用了全表扫描。但是6月份的执行计划中，该表都使用了分区索引，因此，想通过使用hint的方式，强制他使用索引。
--附：6月份的执行计划
SQL> SELECT a.curr_deal
2 , b.dr_type, b.src_dr_type, b.dr_name
3 , c.user_id, c.user_type, c.user_name
4 , d.bill_id, d.bill_flag, d.bill_name
5 , e.prov_id, e.prov_code, e.prov_name
6 , f.prov_id, f.prov_code, f.prov_name
7 , g.prov_id, g.prov_code, g.prov_name
, h.sp_id, h.sp_code, h.sp_name
8 9 , i.oper_id, i.oper_code, i.oper_name
10 , a.sheet_cnt
11 , a.bal_fee
12 , a.outcome1
13 , a.income1
14 , a.income2
15 , a.income3
16 FROM bam_cbbs_plf_t_gd a,bam_t01_dr_type_def b
17 , bam_t01_user_type_def c, bam_t01_bill_flag_def d
18 , bam_t01_province_def e, bam_t01_province_def f
19 , bam_t01_province_def g, bam_t01_sp_code_def h
20 , bam_t01_sp_oper_code_def i
21 WHERE a.curr_deal = '20100630'
22 AND a.dr_type = b.src_dr_type
23 AND a.user_type = c.user_type
24 AND a.bill_flag = d.bill_flag
25 AND a.chrg_prov = e.prov_code
26 AND a.up_prov = f.prov_code
27 AND a.visit_prov = g.prov_code
28 AND a.sp_code = h.sp_code
29 AND b.dr_type = h.dr_type
30 AND get_char2date(a.curr_deal) >= h.valid_date
31 AND get_char2date(a.curr_deal) < h.expire_date
32 AND a.oper_code = i.oper_code
33 AND h.sp_id = i.sp_code
34 AND b.dr_type = i.dr_type
35 AND get_char2date(a.curr_deal) >= i.valid_date
36 AND get_char2date(a.curr_deal) < i.expire_date;
46116 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 898124513
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 | 424 (2)| 00:00:06 | | |
| 1 | TABLE ACCESS BY INDEX ROWID | BAM_T01_USER_TYPE_DEF | 1 | 26 | 1 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 316 | 424 (2)| 00:00:06 | | |
| 3 | NESTED LOOPS | | 1 | 290 | 423 (2)| 00:00:06 | | |
| 4 | NESTED LOOPS | | 1 | 266 | 422 (2)| 00:00:06 | | |
| 5 | NESTED LOOPS | | 1 | 248 | 421 (2)| 00:00:06 | | |
| 6 | NESTED LOOPS | | 1 | 230 | 420 (2)| 00:00:06 | | |
| 7 | NESTED LOOPS | | 1 | 212 | 419 (2)| 00:00:06 | | |
|* 8 | HASH JOIN | | 1 | 187 | 418 (2)| 00:00:06 | | |
|* 9 | HASH JOIN | | 45 | 5850 | 411 (2)| 00:00:06 | | |
| 10 | PARTITION RANGE SINGLE | | 16049 | 1034K| 115 (1)| 00:00:02 | 31 | 31 |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID| BAM_CBBS_PLF_T_GD | 16049 | 1034K| 115 (1)| 00:00:02 | 31 | 31 |
|* 12 | INDEX RANGE SCAN | IDX_CBBS_PLF_T_GD_000001 | 873 | | 38 (0)| 00:00:01 | 31 | 31 |
| 13 | TABLE ACCESS FULL | BAM_T01_SP_OPER_CODE_DEF | 137K| 8611K| 295 (2)| 00:00:05 | | |
| 14 | TABLE ACCESS FULL | BAM_T01_SP_CODE_DEF | 2919 | 162K| 7 (0)| 00:00:01 | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | BAM_T01_DR_TYPE_DEF | 1 | 25 | 1 (0)| 00:00:01 | | |
|* 16 | INDEX UNIQUE SCAN | SYS_C0010382 | 1 | | 0 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS BY INDEX ROWID | BAM_T01_PROVINCE_DEF | 1 | 18 | 1 (0)| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | IDX_T01_PROV_CODE | 1 | | 0 (0)| 00:00:01 | | |
| 19 | TABLE ACCESS BY INDEX ROWID | BAM_T01_PROVINCE_DEF | 1 | 18 | 1 (0)| 00:00:01 | | |
|* 20 | INDEX RANGE SCAN | IDX_T01_PROV_CODE | 1 | | 0 (0)| 00:00:01 | | |
| 21 | TABLE ACCESS BY INDEX ROWID | BAM_T01_PROVINCE_DEF | 1 | 18 | 1 (0)| 00:00:01 | | |
|* 22 | INDEX RANGE SCAN | IDX_T01_PROV_CODE | 1 | | 0 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS BY INDEX ROWID | BAM_T01_BILL_FLAG_DEF | 1 | 24 | 1 (0)| 00:00:01 | | |
|* 24 | INDEX RANGE SCAN | IDX_T01_BILL_FLAG | 1 | | 0 (0)| 00:00:01 | | |
|* 25 | INDEX RANGE SCAN | IDX_T01_USER_TYPE | 1 | | 0 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("A"."SP_CODE"="H"."SP_CODE" AND "H"."SP_ID"="I"."SP_CODE")
filter("H"."VALID_DATE"<="GET_CHAR2DATE"("A"."CURR_DEAL") AND "H"."EXPIRE_DATE">"GET_CHAR2DATE"("A"."CURR_DEAL"))
9 - access("A"."OPER_CODE"="I"."OPER_CODE")
filter("I"."VALID_DATE"<="GET_CHAR2DATE"("A"."CURR_DEAL") AND "I"."EXPIRE_DATE">"GET_CHAR2DATE"("A"."CURR_DEAL"))
12 - access("A"."CURR_DEAL"='20100630')
15 - filter("B"."SRC_DR_TYPE" IS NOT NULL AND "A"."DR_TYPE"="B"."SRC_DR_TYPE")
16 - access("B"."DR_TYPE"="I"."DR_TYPE")
filter("B"."DR_TYPE"="H"."DR_TYPE")
18 - access("A"."VISIT_PROV"="G"."PROV_CODE")
20 - access("A"."UP_PROV"="F"."PROV_CODE")
22 - access("A"."CHRG_PROV"="E"."PROV_CODE")
24 - access("A"."BILL_FLAG"="D"."BILL_FLAG")
25 - access("A"."USER_TYPE"="C"."USER_TYPE")
Statistics
----------------------------------------------------------
217342 recursive calls
0 db block gets
267123 consistent gets
0 physical reads
0 redo size
4495585 bytes sent via SQL*Net to client
34306 bytes received via SQL*Net from client
3076 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
46116 rows processed
对SQL增加HINT，查看加了HINT以后的执行计划
--附：加了HINT以后的执行计划
idle> SELECT /*+index(bam_cbbs_plf_t_gd idx_cbbs_plf_t_gd_000001)*/ a.curr_deal
2 , b.dr_type, b.src_dr_type, b.dr_name
3 , c.user_id, c.user_type, c.user_name
4 , d.bill_id, d.bill_flag, d.bill_name
5 , e.prov_id, e.prov_code, e.prov_name
6 , f.prov_id, f.prov_code, f.prov_name
7 , g.prov_id, g.prov_code, g.prov_name
8 , h.sp_id, h.sp_code, h.sp_name
9 , i.oper_id, i.oper_code, i.oper_name
, a.sheet_cnt
10 11 , a.bal_fee
12 , a.outcome1
13 , a.income1
14 , a.income2
15 , a.income3
16 FROM bam_cbbs_plf_t_gd a,bam_t01_dr_type_def b
17 , bam_t01_user_type_def c, bam_t01_bill_flag_def d
18 , bam_t01_province_def e, bam_t01_province_def f
19 , bam_t01_province_def g, bam_t01_sp_code_def h
20 , bam_t01_sp_oper_code_def i
21 WHERE a.curr_deal = '20100701'
22 AND a.dr_type = b.src_dr_type
23 AND a.user_type = c.user_type
24 AND a.bill_flag = d.bill_flag
25 AND a.chrg_prov = e.prov_code
26 AND a.up_prov = f.prov_code
27 AND a.visit_prov = g.prov_code
28 AND a.sp_code = h.sp_code
29 AND b.dr_type = h.dr_type
30 AND get_char2date(a.curr_deal) >= h.valid_date
31 AND get_char2date(a.curr_deal) < h.expire_date
32 AND a.oper_code = i.oper_code
33 AND h.sp_id = i.sp_code
34 AND b.dr_type = i.dr_type
35 AND get_char2date(a.curr_deal) >= i.valid_date
36 AND get_char2date(a.curr_deal) < i.expire_date;
51339 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 12255921
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 317 | 397 (2)| 00:00:06 | | |
|* 1 | HASH JOIN | | 1 | 317 | 397 (2)| 00:00:06 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | BAM_T01_PROVINCE_DEF | 1 | 18 | 1 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 253 | 101 (3)| 00:00:02 | | |
| 4 | NESTED LOOPS | | 1 | 235 | 100 (3)| 00:00:02 | | |
| 5 | NESTED LOOPS | | 1 | 217 | 99 (4)| 00:00:02 | | |
| 6 | NESTED LOOPS | | 1 | 199 | 98 (4)| 00:00:02 | | |
| 7 | NESTED LOOPS | | 1 | 175 | 97 (4)| 00:00:02 | | |
|* 8 | HASH JOIN | | 1 | 149 | 96 (4)| 00:00:02 | | |
|* 9 | HASH JOIN | | 2873 | 230K| 11 (10)| 00:00:01 | | |
|* 10 | TABLE ACCESS FULL | BAM_T01_DR_TYPE_DEF | 63 | 1575 | 3 (0)| 00:00:01 | | |
| 11 | TABLE ACCESS FULL | BAM_T01_SP_CODE_DEF | 2919 | 162K| 7 (0)| 00:00:01 | | |
| 12 | PARTITION RANGE SINGLE | | 51530 | 3371K| 84 (2)| 00:00:02 | 32 | 32 |
|* 13 | TABLE ACCESS FULL | BAM_CBBS_PLF_T_GD | 51530 | 3371K| 84 (2)| 00:00:02 | 32 | 32 |
| 14 | TABLE ACCESS BY INDEX ROWID| BAM_T01_USER_TYPE_DEF | 1 | 26 | 1 (0)| 00:00:01 | | |
|* 15 | INDEX RANGE SCAN | IDX_T01_USER_TYPE | 1 | | 0 (0)| 00:00:01 | | |
| 16 | TABLE ACCESS BY INDEX ROWID | BAM_T01_BILL_FLAG_DEF | 1 | 24 | 1 (0)| 00:00:01 | | |
|* 17 | INDEX RANGE SCAN | IDX_T01_BILL_FLAG | 1 | | 0 (0)| 00:00:01 | | |
| 18 | TABLE ACCESS BY INDEX ROWID | BAM_T01_PROVINCE_DEF | 1 | 18 | 1 (0)| 00:00:01 | | |
|* 19 | INDEX RANGE SCAN | IDX_T01_PROV_CODE | 1 | | 0 (0)| 00:00:01 | | |
| 20 | TABLE ACCESS BY INDEX ROWID | BAM_T01_PROVINCE_DEF | 1 | 18 | 1 (0)| 00:00:01 | | |
|* 21 | INDEX RANGE SCAN | IDX_T01_PROV_CODE | 1 | | 0 (0)| 00:00:01 | | |
|* 22 | INDEX RANGE SCAN | IDX_T01_PROV_CODE | 1 | | 0 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS FULL | BAM_T01_SP_OPER_CODE_DEF | 137K| 8611K| 295 (2)| 00:00:05 | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."OPER_CODE"="I"."OPER_CODE" AND "H"."SP_ID"="I"."SP_CODE" AND "B"."DR_TYPE"="I"."DR_TYPE")
filter("I"."VALID_DATE"<="GET_CHAR2DATE"("A"."CURR_DEAL") AND
"I"."EXPIRE_DATE">"GET_CHAR2DATE"("A"."CURR_DEAL"))
8 - access("A"."DR_TYPE"="B"."SRC_DR_TYPE" AND "A"."SP_CODE"="H"."SP_CODE")
filter("H"."VALID_DATE"<="GET_CHAR2DATE"("A"."CURR_DEAL") AND
"H"."EXPIRE_DATE">"GET_CHAR2DATE"("A"."CURR_DEAL"))
9 - access("B"."DR_TYPE"="H"."DR_TYPE")
10 - filter("B"."SRC_DR_TYPE" IS NOT NULL)
13 - filter("A"."CURR_DEAL"='20100701')
15 - access("A"."USER_TYPE"="C"."USER_TYPE")
17 - access("A"."BILL_FLAG"="D"."BILL_FLAG")
19 - access("A"."CHRG_PROV"="E"."PROV_CODE")
21 - access("A"."UP_PROV"="F"."PROV_CODE")
22 - access("A"."VISIT_PROV"="G"."PROV_CODE")
Statistics
----------------------------------------------------------
205437 recursive calls
0 db block gets
218655 consistent gets
0 physical reads
0 redo size
5187289 bytes sent via SQL*Net to client
38134 bytes received via SQL*Net from client
3424 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
51339 rows processed
奇怪的是，使用了HINT，并没有任何改变，执行计划和以前还是一样的。并没有用到索引，奇怪啊。但是发现SQL的运行速度突然提高了。这条语句现在只需要6秒就可以返回结果，达到了预期的目标。然后我又尝试去掉了HINT后的效率，发现效率确实已经上来了，和HINT无关，难道是收集信息起作用了？为什么刚开始收集完就重跑数据却没效果呢？这是个疑问，难道说，统计信息得一定时间以后才起作用啊？
查阅了有关收集信息的文档：ORACLE每天晚上会自动收集信息，但是由于我在当天晚上的时候，7月分区并无数据，所以没有还没有产生收集信息，然后当第二天清晨突然增加了很多数据以后，ORACLE并没有及时收集信息，因此SQL运行较慢。采用了手工的方式来收集，便解决了问题。