Re: statement tuning ?

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:

    1. 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

    2. 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

    3. 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

    4. 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@xxxxxxxxxxxxxxxx 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: