Re: subselect results in poor performance

Benny wrote:
> This is 9207 and dbms_stats is always used. In this case even with
> columns stats on indexed columns skewonly. Stats are fresh.
>
> select vw_rrp00010.*,rrp0.tb_rrp00115.*
> from rrp0.mv_rrp00010 vw_rrp00010, rrp0.tb_rrp00115
> where vw_rrp00010.load_dt = tb_rrp00115.load_dt
> and vw_rrp00010.load_type_cd = tb_rrp00115.load_type_cd
> and vw_rrp00010.fuse_cty_id = tb_rrp00115.fuse_cty_id
> and (vw_rrp00010.iso2_cty_cd = tb_rrp00115.iso2_cty_cd or ' ' =
> tb_rrp00115.iso2_cty_cd)
> and tb_rrp00115.load_dt = (select load_dt from rrp0.mv_rrp00104 where
> '1' = vsn_cd and 'D' = load_type_cd)
>
>
>
> ---------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes
> |TempSpc| Cost | Pstart| Pstop
> ---------------------------------------------------------------------------------------------------------
>
>
> | 0 | SELECT STATEMENT | | 64674 |
> 10M| | 2891 | | |
> |* 1 | HASH JOIN | | 64674 |
> 10M| 7104K| 2891 | | |
> | 2 | PARTITION RANGE SINGLE| | |
> | | | KEY | KEY |
> |* 3 | TABLE ACCESS FULL | TB_RRP00115 | 48811 |
> 6530K| | 1290 | KEY | KEY |
> |* 4 | INDEX RANGE SCAN | IDX__MV_RRP00104_OPT | 2 | 18
> | | 1 | | |
> | 5 | PARTITION RANGE ALL | | |
> | | | 1 | 35 |
> | 6 | TABLE ACCESS FULL | MV_RRP00010 | 1100K|
> 33M| | 754 | 1 | 35 |
> ---------------------------------------------------------------------------------------------------------
>
>
>
>
>
> Predicate Information (identified by operation id):
>
>
> ---------------------------------------------------
>
>
>
>
>
> 1 - access("VW_RRP00010"."LOAD_DT"="TB_RRP00115"."LOAD_DT" AND
> "VW_RRP00010"."LOAD_TYPE_CD"="TB_RRP00115"."LOAD_TYPE_CD" AND
> "VW_RRP00010"."FUSE_CTY_ID"="TB_RRP00115"."FUSE_CTY_ID")
>
> filter("VW_RRP00010"."ISO2_CTY_CD"="TB_RRP00115"."ISO2_CTY_CD"
> OR "TB_RRP00115"."ISO2_CTY_CD"=' ')
>
> 3 - filter("TB_RRP00115"."LOAD_DT"= (SELECT /*+ */
> "MV_RRP00104"."LOAD_DT" FROM "RRP0"."MV_RRP00104"
>
>
> "MV_RRP00104" WHERE "MV_RRP00104"."LOAD_TYPE_CD"='D' AND
> "MV_RRP00104"."VSN_CD"='1'))
>
> 4 - access("MV_RRP00104"."VSN_CD"='1' AND
> "MV_RRP00104"."LOAD_TYPE_CD"='D')
>
>
>
>
>
> Note: cpu costing is off

Have you tested w/o the histograms? How the histograms are gathered can make a big difference.
http://groups.google.com/group/comp.databases.oracle.server/msg/3d4e86835611df5b?hl=en& Received on Wed Jan 17 2007 - 14:41:03 CST