| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: disabling use of VW_SNO_1
As a quick and dirty workaround, you could try setting
_unnest_subquery = false
The default value changed from FALSE in 8.1 to TRUE in 9.2. Initially you can set with
alter session set "_unnest_subquery"=true;
As ever, when messing with undocumented parameters you should check with Oracle support before putting it into your parameter file on a production system.
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated July 27th "MAK" <maks70_at_comcast.net> wrote in message news:b7178504.0408252151.56ec3166_at_posting.google.com...Received on Sat Aug 28 2004 - 06:46:15 CDT
> Hello!
>
> Please review the following SQL and Explain plan. How can I disable
> use of VW_NSO_1? Oracle implicitly create this view to unnest the
> subquery.
>
> I am trying to tune this query for OLTP app which would be happy to
> see First row ASAP. This view has SORT operation prevents that as it
> will not pass all the all the rows in subquery are sorted... Subquery
> in most cases returns 100K rows..So its expensive..
>
> I am using Oracle 9.2.0.4 and with RBO. Please note that changing
> query is not an option as this query is from Vendor app. I might be
> able to use the hints (Outline ) to tune this.
>
> Any help would be highly appreciated...
>
> SELECT
> T4.CONFLICT_ID,
> T5.INCOME_CURCY_CD,
> T2.FST_NAME,
> ----
> ---
> T2.LAST_NAME,
> T3.ROW_ID,
> T1.ROW_ID
> FROM
> SIEBEL7.S_PARTY T1,
> SIEBEL7.S_CONTACT T2,
> SIEBEL7.S_GROUP_CONTACT T3,
> SIEBEL7.S_PARTY T4,
> SIEBEL7.S_ORG_GROUP T5
> WHERE
> T4.ROW_ID = T5.PAR_ROW_ID AND
> T5.PR_CON_ID = T3.CON_ID (+) AND T5.ROW_ID = T3.GROUP_OU_ID (+)
> AND
> T5.PR_CON_ID = T1.ROW_ID (+) AND
> T5.PR_CON_ID = T2.PAR_ROW_ID (+) AND
> ((T4.ROW_ID IN (
> SELECT SQ1_T1.GROUP_OU_ID
> FROM SIEBEL7.S_GROUP_CONTACT SQ1_T1,
> SIEBEL7.S_CONTACT_X SQ1_T2, SIEBEL7.S_PARTY SQ1_T3, SIEBEL7.S_CONTACT
> SQ1_T4
> WHERE
> (SQ1_T3.ROW_ID = SQ1_T4.PAR_ROW_ID AND SQ1_T3.ROW_ID
> = SQ1_T2.PAR_ROW_ID (+) AND SQ1_T1.CON_ID = SQ1_T3.ROW_ID) AND
> (SQ1_T4.X_DOMCL_BRCH_CD = :1 AND
> SQ1_T4.X_FIN_TOT_NWORTH_AM > :2))) AND
> (T5.GROUP_TYPE_CD = 'Household'))
>
>
> EXPLAIN PLAN:
>
> SELECT STATEMENT Cost =
> NESTED LOOPS OUTER
> NESTED LOOPS OUTER
> NESTED LOOPS OUTER
> NESTED LOOPS
> NESTED LOOPS
> VIEW
> VW_NSO_1
> SORT UNIQUE
> NESTED LOOPS OUTER
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS BY INDEX ROWID
> S_CONTACT
> INDEX RANGE SCAN
> TEMP_S_CONTACT_DMCL_CD_F
>
> NTOT_X
>
> INDEX UNIQUE SCAN
> S_PARTY_P1
> TABLE ACCESS BY INDEX ROWID
> S_GROUP_CONTACT
> INDEX RANGE SCAN
> S_GROUP_CONTACT_F2
> INDEX RANGE SCAN
> S_CONTACT_X_U1
> TABLE ACCESS BY INDEX ROWID
> S_PARTY
> INDEX UNIQUE SCAN
> S_PARTY_P1
> TABLE ACCESS BY INDEX ROWID
> S_ORG_GROUP
> INDEX UNIQUE SCAN
> S_ORG_GROUP_U2
> TABLE ACCESS BY INDEX ROWID
> S_CONTACT
> INDEX UNIQUE SCAN
> S_CONTACT_U2
> INDEX UNIQUE SCAN
> S_PARTY_P1
> TABLE ACCESS BY INDEX ROWID
> S_GROUP_CONTACT
> INDEX RANGE SCAN
> S_GROUP_CONTACT_F2
 |
 |