Re: statement tuning ?

From: Tim Gorman <tim_at_sagelogix.com>
Date: Sat, 01 May 2004 12:46:44 -0600
Message-ID: <BCB94CB4.14274%tim@sagelogix.com>

David,

I figured I might as well respond according to expectations and respond with a short novel... :-) My apologies in advance for my deplorable lack of brevity...

>From simply looking at the query, it is clear that the T11 table-alias (i.e.
S_ORG_EXT table) is likely the "driving table" of this query, as most of the outer-joins come out of it. If you were to graph this query, then you would see the T11 alias as centrally important.

So, looking at the WHERE clause, you would then want to be looking for opportunities to restrict the set of rows coming from the T11 alias. Four such opportunities present themselves:

joining to T11 from the alias T5 (i.e. S_ACCNT_POSTN table) via the join-predicates "T11.PR_POSTN_ID = T5.POSITION_ID" and "T11.ROW_ID = T5.OU_EXT_ID". However, since there are no filtering/search predicates against the T5 alias at all, this would only work if the S_ACCNT_POSTN table is really small
joining to T11 from the alias T3 (i.e. S_POSTN table) via the join-predicate "T11.PR_POSTN_ID = T3.ROW_ID". However, just as with the T5 alias, there are no filtering/search predicates on the T3 alias so this would only work if the S_POSTN table were really small
using the filtering/search predicate on the T11.CUST_STAT_CD column, which would work well if the column were indexed and the value of "Active" is relatively unpopular
using the OR'd group of filtering/search predicates on the T11.LOC column

Options #1, #2, and #3 should be relatively easy to verify, I think?

Option #4 is the complicated one, and it presents another damning argument against continued use of the rule-based optimizer (RBO). In this situation (i.e. multiple binding filtering/search predicates against the same column glued together with OR clauses), the RBO cannot use an index, so it doesn't.

However, if the cost-based optimizer (CBO) were in use, then the functionality exposed by the USE_CONCAT hint (MetaLink note #17214.1) would enable the CBO to transparently transform the query to expand the OR'ed predicates into multiple UNION'd query blocks. So in effect, the query which is currently written as:

    SELECT  ...
    FROM    ...
    WHERE   ...
    AND     (T11.LOC = xxx OR T11.LOC = yyy OR T11.LOC like '%zzz' ...)
    AND     ...

Could be transparently transformed to operate as:

    SELECT  ...
    FROM    ...
    WHERE   ...
    AND     T11.LOC = xxx
    AND     ...

UNION

    SELECT  ...
    FROM    ...
    WHERE   ...
    AND     T11.LOC = yyy
    AND     ...

UNION

    SELECT  ...
    FROM    ...
    WHERE   ...
    AND     T11.LOC like '%zzz'
    AND     ...

UNION
...

And, if the CBO didn't choose to do this transparently, it could be easily forced using the USE_CONCAT hint.

The possible advantage of this expansion is that, if the T11.LOC column is indexed, then each UNION'd subquery can utilize that index as appropriate.

However, since you are using RBO and the CBO-based "USE_CONCAT" hint is not available for use, then your only option is test an explicit rewrite of the query to perform the expansion. If the query cannot be explicitly rewritten, then the story ends right there.

Not to fret, as there is a very good chance that this expansion will not help, as it is possible that a FULL table scan of the T11 alias (i.e. S_ORG_EXT table) is appropriate if the data values used in the predicates are very "popular". Also, it is likely that the predicate of "T11.LOC LIKE '%COUNCIL'" would not be able to use any index on T11.LOC anyway, so expansion of the OR clauses would result in at least multiple FULL table scans of the S_ORG_EXT table anyway, but you ought to test it to find out.

Anyway, I'd advise investigating each of these four options presented above.

Hope this helps...

-Tim

on 4/30/04 1:51 PM, David Green at thump_at_cosmiccooler.org wrote:

> Does anyone have any general recommendations for how to tune this query or
> improve how it does what it does in 8174 in RBO mode?
> SELECT
> T5.POSITION_ID,
> T3.ROW_ID,
> T11.PR_POSTN_ID,
> T11.ASGN_USR_EXCLD_FLG,
> ...
> ...(list of columns edited for brevity)...
> ...
> T5.ASGN_MANL_FLG,
> T3.NAME
> FROM
> SIEBEL.S_ADDR_ORG T1,
> SIEBEL.S_ORG_EXT T2,
> SIEBEL.S_POSTN T3,
> SIEBEL.S_ORG_SYN T4,
> SIEBEL.S_ACCNT_POSTN T5,
> SIEBEL.S_ASGN_GRP T6,
> SIEBEL.S_ORG_INT T7,
> SIEBEL.S_INDUST T8,
> SIEBEL.S_EMPLOYEE T9,
> SIEBEL.S_ORG_EXT_X T10,
> SIEBEL.S_ORG_EXT T11
> WHERE
> T11.BU_ID = T7.ROW_ID (+) AND
> T11.PAR_OU_ID = T2.ROW_ID (+) AND
> T11.ROW_ID = T10.PAR_ROW_ID (+) AND
> T11.PR_INDUST_ID = T8.ROW_ID (+) AND
> T11.PR_SYN_ID = T4.ROW_ID (+) AND
> T11.PR_ADDR_ID = T1.ROW_ID (+) AND
> T11.PR_TERR_ID = T6.ROW_ID (+) AND
> T11.PR_POSTN_ID = T3.ROW_ID AND
> T11.PR_POSTN_ID = T5.POSITION_ID AND T11.ROW_ID = T5.OU_EXT_ID AND
> T3.PR_EMP_ID = T9.ROW_ID (+) AND
> ((T11.LOC = 'WGQIWG' OR T11.LOC = 'WGAIWG' OR T11.LOC = 'WGFIWG' OR
> T11.LOC LIKE '%COUNCIL') AND T11.CUST_STAT_CD = 'Active')
> ORDER BY
> T11.NAME, T11.LOC
>
> 16 rows selected
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=RULE
> 1 0 NESTED LOOPS (OUTER)
> 2 1 NESTED LOOPS
> 3 2 NESTED LOOPS (OUTER)
> 4 3 NESTED LOOPS (OUTER)
> 5 4 NESTED LOOPS (OUTER)
> 6 5 NESTED LOOPS
> 7 6 NESTED LOOPS (OUTER)
> 8 7 NESTED LOOPS (OUTER)
> 9 8 NESTED LOOPS (OUTER)
> 10 9 NESTED LOOPS (OUTER)
> 11 10 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT'
> 12 11 INDEX (FULL SCAN) OF 'S_ORG_EXT_U1'(UNIQUE)
> 13 10 TABLE ACCESS (BY INDEX ROWID) OF 'S_INDUST'
> 14 13 INDEX (UNIQUE SCAN) OF 'S_INDUST_P1'(UNIQUE)
> 15 9 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_INT'
> 16 15 INDEX (UNIQUE SCAN) OF 'S_ORG_INT_P1'(UNIQUE)
> 17 8 TABLE ACCESS (BY INDEX ROWID) OF 'S_ASGN_GRP'
> 18 17 INDEX (UNIQUE SCAN) OF 'S_ASGN_GRP_P1' (UNIQUE)
> 19 7 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_SYN'
> 20 19 INDEX (UNIQUE SCAN) OF 'S_ORG_SYN_P1' (UNIQUE)
> 21 6 TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN'
> 22 21 INDEX (UNIQUE SCAN) OF 'S_POSTN_P1' (UNIQUE)
> 23 5 TABLE ACCESS (BY INDEX ROWID) OF 'S_EMPLOYEE'
> 24 23 INDEX (UNIQUE SCAN) OF 'S_EMPLOYEE_P1' (UNIQUE)
> 25 4 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT'
> 26 25 INDEX (UNIQUE SCAN) OF 'S_ORG_EXT_P1' (UNIQUE)
> 27 3 TABLE ACCESS (BY INDEX ROWID) OF 'S_ADDR_ORG'
> 28 27 INDEX (UNIQUE SCAN) OF 'S_ADDR_ORG_P1' (UNIQUE)
> 29 2 TABLE ACCESS (BY INDEX ROWID) OF 'S_ACCNT_POSTN'
> 30 29 INDEX (RANGE SCAN) OF 'S_ACCNT_POSTN_U1' (UNIQUE)
> 31 1 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT_X'
> 32 31 INDEX (RANGE SCAN) OF 'S_ORG_EXT_X_U1' (UNIQUE)
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 731716 consistent gets
> 5505 physical reads
> 0 redo size
> 12214 bytes sent via SQL*Net to client
> 1468 bytes received via SQL*Net from client
> 3 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 16 rows processed

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Sat May 01 2004 - 13:43:27 CDT