Re: Help interpreting TKPROF output

right -- the t10 join is not a problem -- none of the joins appear to be a problem in themselves, but the fact that they seem to be done before filtering the resultset indicates a whole lot of extra work for throw-away rows

i'm wondering, if the 'same' query was 'faster' in the past -- could it be that the prior run of the 'same' query did not filter as restrictively, and it was actually just the initial response (first set of rows) that was faster? if basically the same rows are being processed, but we aren't filtering the joined result set down to a very very small set, then we'd get a chunk of a larger result set returned a lot quicker, wouldn 't we?

• mcs

"Daniel Roy" <danielroy10junk_at_hotmail.com> wrote in message news:3722db.0311090909.5a2df129_at_posting.google.com...
> 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 - 12:00:13 CST