Do you have the tuning pack licensed? If so, create a tuning task and see if
Oracle comes up with the right plan. As a last resort, you can always
create an outline for the query.
On 6/13/07, MVR <yoursraju007_at_gmail.com> wrote:
>
> Hello everyone,
>
> Select Query has got many outer joins and it runs very good in 9i
> (9.2.0.5) and the same query crawls after upgrade to 10.2.0.2.0. Index
> are same in both the cases and statistics are good.
>
> 9i explain plan has got lots of "NESTED LOOPS OUTER" , one hash join
> and its selecting an index on a huge table, with AND-EQUAL.
>
> | 11 | NESTED LOOPS |
> | 1 | 90 | 2798 | | |
> |* 12 | INDEX RANGE SCAN |
> T_BO_SEC_NEW_U01 | 349 | 13611 | 6 | | |
> |* 13 | TABLE ACCESS BY INDEX ROWID | T_PLAN_EMP
> | 1 | 51 | 2798 | | |
> | 14 | AND-EQUAL |
> | | | | | |
> |* 15 | INDEX RANGE SCAN |
> NU_T_PLAN_EMP_DR_ORG_ID | | | | | |
> |* 16 | INDEX RANGE SCAN |
> XT_PLAN_EMP_PLAN_ID | 1885 | | 5 | | |
> |* 17 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_LOOKUP
> | 1 | 33 | 1 | ROWID | ROW L |
> |* 18 | INDEX UNIQUE SCAN | SYS_C003844
> | 1 | | | | |
>
> 10g explain has got lots of HASH JOIN RIGHT OUTER, HASH JOIN OUTER and
> full tablescans.. Its not picking the index on big table.
>
>
> | 5 | TABLE ACCESS FULL | T_CURRENCY
> | 202 | 2020 | | 2 (0)| 00:00:01 | | |
> |* 6 | HASH JOIN OUTER |
> | 2146 | 542K| | 61589 (3)| 00:14:23 | | |
> |* 7 | HASH JOIN RIGHT OUTER |
> | 1664 | 390K| | 59903 (3)| 00:13:59 | | |
> | 8 | TABLE ACCESS FULL | T_CURRENCY
> | 202 | 2020 | | 2 (0)| 00:00:01 | | |
> |* 9 | HASH JOIN OUTER |
> | 1664 | 373K| | 59901 (3)| 00:13:59 | | |
> |* 10 | HASH JOIN OUTER |
> | 1324 | 272K| | 58215 (3)| 00:13:36 | | |
> | 11 | NESTED LOOPS OUTER |
> | 1324 | 230K| | 58063 (3)| 00:13:33 | | |
> |* 12 | HASH JOIN RIGHT OUTER |
> | 1324 | 193K| | 57779 (3)| 00:13:29 | | |
> | 13 | TABLE ACCESS FULL | T_CURRENCY
> | 202 | 4444 | | 2 (0)| 00:00:01 | | |
> |* 14 | HASH JOIN |
> | 1324 | 165K| | 57777 (3)| 00:13:29 | | |
> |* 15 | HASH JOIN |
> | 1324 | 112K| 3024K| 57474 (3)| 00:13:25 | | |
> |* 16 | INDEX RANGE SCAN | T_BO_SEC_NEW_U01
> | 63025 | 2277K| | 775 (1)| 00:00:11 | | |
> |* 17 | TABLE ACCESS FULL | T_PLAN_EMP
> | 307K| 14M| | 55783 (3)| 00:13:01 | | |
>
>
> If I alter optimizer_features_enable to 9.0.1 at session level. It
> does get almost same explain plan and same results. But customer does
> not want to do that.
>
> There is no solution yet from Oracle on Bug# 6072579 , 6068009. Is
> there any CBO guru out here?
>
> I will send SQL, Explain plans and 10053 traces to personal email, if you
> want.
>
> Thanks,
> Raj
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 14 2007 - 17:35:35 CDT
