|
|
select swjg.mc_j, zspm.mc_j, count(distinct bb.nsgrnbm)
from db_ttxx.t_sxx_dwjfdjxx djxx,
DB_djxx.t_dj_jgnxx jg,
db_ttxx.t_sxx_fxzmxsbb bb,
db_xtwh.t_dm_gy_zspm zspm,
db_xtwh.t_dm_gy_swjg swjg
where djxx.nsrxxx = jg.nsrxxx
and djxx.jfdwnbm = bb.jfdwnbm
and jg.hsjg_dm = '244010300'
and bb.lr_sj >= to_date('2011-5-6','YYYY-MM-DD')
and bb.lr_sj < to_date ('2011-5-7','YYYY-MM-DD')
and bb.zsxm_dm = '66'
and zspm.zsxm_dm = '66'
and bb.zspm_dm = zspm.zspm_dm
and djxx.swgljg_dm = swjg.swjg_dm
group by swjg.mc_j, zspm.mc_j;
这是10046 level 12后得出的结果：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 19 34.89 1214.91 216187 326475 0 256
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 34.89 1214.91 216187 326475 0 256
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
| 0 |
| 1 | 256 SORT GROUP BY
|* 2 | 64097 FILTER
|* 3 | 64097 HASH JOIN
|* 4 | 72724 HASH JOIN
|* 5 | 161 TABLE ACCESS FULL T_DM_GY_ZSPM
|* 6 | 72724 HASH JOIN
|* 7 | 136082 HASH JOIN
| 8 | 243190 TABLE ACCESS BY INDEX ROWID T_DJ_jgnxx
|* 9 | 243190 INDEX RANGE SCAN I_DJ_jgnxx_HSJ (object id 94776)
| 10 | 3613241 TABLE ACCESS FULL T_sxx_DWJFDJXX
|* 11 | 4212491 TABLE ACCESS BY GLOBAL INDEX ROWID T_sxx_FXZMXSBB PARTITION: ROW LOCATION ROW LOCATION
|* 12 | 4212569 INDEX RANGE SCAN I_sxx_FXZMXSBB_LRSJ (object id 7457699)
| 13 | 5864 TABLE ACCESS FULL T_DM_GY_SWJG
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 19 0.00 0.00
db file sequential read 167747 0.88 1044.85
buffer busy waits 1 0.00 0.00
latch free 1 0.00 0.00
db file scattered read 8479 0.23 137.93
SQL*Net message from client 19 0.00 0.09
********************************************************************************
这是explain plan for 得出的结果：
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2346 | 309K| 136K| | |
| 1 | SORT GROUP BY | | 2346 | 309K| 136K| | |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN | | 2346 | 309K| 136K| | |
|* 4 | HASH JOIN | | 2333 | 223K| 136K| | |
|* 5 | TABLE ACCESS FULL | T_DM_GY_ZSPM | 19 | 494 | 5 | | |
|* 6 | HASH JOIN | | 12594 | 885K| 136K| | |
|* 7 | HASH JOIN | | 36083 | 1444K| 20434 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | T_DJ_jgnxx | 35823 | 629K| 16064 | | |
|* 9 | INDEX RANGE SCAN | I_DJ_jgnxx_HSJ | 35823 | | 176 | | |
| 10 | TABLE ACCESS FULL | T_sxx_DWJFDJXX | 3554K| 77M| 3387 | | |
|* 11 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_sxx_FXZMXSBB | 1240K| 36M| 115K| ROWID | ROW L |
|* 12 | INDEX RANGE SCAN | I_sxx_FXZMXSBB_LRSJ | 229 | | 8600 | | |
| 13 | TABLE ACCESS FULL | T_DM_GY_SWJG | 5860 | 211K| 13 | | |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:Z,:Z)<TO_DATE(:Z,:Z))
3 - access("DJXX"."SWGLJG_DM"="SWJG"."SWJG_DM")
4 - access("BB"."ZSPM_DM"="ZSPM"."ZSPM_DM")
5 - filter("ZSPM"."ZSXM_DM"=:Z)
6 - access("DJXX"."JFDWNBM"="BB"."JFDWNBM")
7 - access("DJXX"."nsrxxx"="JG"."nsrxxx")
9 - access("JG"."HSJG_DM"=:Z)
11 - filter("BB"."ZSXM_DM"=:Z)
12 - access("BB"."LR_SJ">=TO_DATE(:Z,:Z) AND "BB"."LR_SJ"<TO_DATE(:Z,:Z))
耗时大概在20分钟左右, T_sxx_FXZMXSBB 数据量大概四亿多,分区表.
T_sxx_DWJFDJXX 的主键是nsrxxx ,搞不明白,为什么
7 - access("DJXX"."nsrxxx"="JG"."nsrxxx")
做的hash join 却利用不了 T_sxx_FXZMXSBB 的主键索引
T_DJ_jgnxx 的主键也是nsrxxx
6 - access("DJXX"."JFDWNBM"="BB"."JFDWNBM")
这个字段也是这两个表的主键
我用hint强行让他们走主键的索引,反而三个多小时都出不了结果,得到ora-01555
请问如何优化? |
|
IP卡
狗仔卡
发表于 2011-5-13 10:40
收藏
提升卡
置顶卡
变色卡
显身卡
楼主
发表于 2011-5-13 13:24
