Re: Help interpreting TKPROF output

Thanx mcs for the ideas. S_EVT_ACT indeed appears twice, but note that T10 is an outer join, which is never ever populated (can you see rows = 0 in the plan?), and it is accessed with a unique index. This looks quite efficient to me (in fact, using a unique index is the best option for an access path under RBO). As for T1, I could live with a FTS, as long as it ran in a reasonable time. Since this query is generated for the display of all the activities (activities are stored in S_EVT_ACT), I now don't see how we could avoid this FTS, actually (the reason the end result is 0 rows returned is probably because the user defined a "pre-defined query", which just adds an additional condition before returning the rows).

Daniel

> the full table scan of S_EVT_ACT deep down in the nested loops does not look
> very nice...
> this table is included as T1 and T10 -- T10 is a self-join to T1, it's
> access appears later in the plan
>
> so you're first problem that's evident is that you must have very poor
> selectivity for T1 -- this means Oracle's scanning it to get something like
> 873,290 rows, then joining it S_ASSET's 770,325 rows, then joining to every
> other table in the database (well, not every one, but lots of them) to
> return 1,745,637 rows before filtering (without indexes) down to 15 rows.
>
> if you examine the non-join conditions on T1 you find (reformatted):
>
> AND ( (
> (
> t1.template_flg != 'Y'
> AND
> t1.template_flg != 'P'
> OR
> t1.template_flg IS NULL
> )
> AND
> (
> t1.opty_id IS NULL
> OR
> t3.secure_flg = 'N'
> OR
> t1.opty_id IN (
> SELECT sq1_t1.opty_id
> FROM siebel.s_opty_postn sq1_t1
> ,siebel.s_party sq1_t2
> ,siebel.s_contact sq1_t3
> ,siebel.s_postn sq1_t4
> WHERE
> (
> sq1_t4.pr_emp_id = sq1_t3.par_row_id(+)
> AND
> sq1_t2.row_id = sq1_t4.par_row_id
> AND
> sq1_t1.position_id = sq1_t2.row_id
> )
> AND (sq1_t3.row_id = '1-29WRP')
> )
> ))
> AND (t6.party_id = '1-2J-5235')
> AND
> (
> t1.priv_flg = 'N'
> OR
> t1.priv_flg IS NULL
> OR
> t1.owner_per_id = '1-2L-5407'
> )
> AND
> (
> t1.appt_rept_repl_cd IS NULL
> )
>
> which has quite a fiew OR and IS NULL conditions that make it very
> understandable why Oracle would not be able to use indexes, but would opt
> for a full table scan
>
> i would be really surprised if this same sql statement ever ran in 20
> seconds with the same data and same indexes -- do you have anything to
> verify the prior case?
>
> anyway, bottom line is that the query needs to be rewritten to have better
> selectivity on 'T1', with supporting indexes -- how that needs to be done is
> hard to say without understaning the business logic behind this selection
> criteria
>
> -- mcs

> Received on Sun Nov 09 2003 - 11:09:59 CST