Re: disabling use of VW_SNO_1

I'm certain Siebel comes with documentation on how to use an Oracle database "their" way but here's an additional document for you: "Tuning Siebel on Oracle"
(http://www.hotsos.com/downloads/registered/00000025.pdf).

It's really a wonder why that ubiquitous application still prefers or even insists on using the RBO.

HTH. maks70_at_comcast.net (MAK) wrote in message news:<b7178504.0408252151.56ec3166_at_posting.google.com>...
> 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
Received on Thu Aug 26 2004 - 12:42:14 CDT