On Nov 16, 5:18*am, Mick <mjms... (AT) gmail (DOT) com> wrote:
Well, it looks like my initial comment about running a unpatched
10.2.0.1 is particularly relevant in this case. You're probably
hitting bug 5065418 that has been fixed in version 10.2.0.4 and is
about slow performance if the CONNECT BY has to spill to disk which is
happening here.

You can try several things:

- Upgrade to a recent 10.2 release (currently 10.2.0.5 on all major
platforms I think) or 11.x to have the bug fixed, which is of course
not a straightforward task in terms of preparation / testing

Less intrusive ideas are:

- Increase PGA_AGGREGATE_TARGET or switch to manual PGA workarea
management using SORT_AREA_SIZE to avoid the spill to disk. This might
also explain why you sometimes get better performance: When the
instance has been restarted you are probably not competing with other
sessions for PGA and therefore the task might complete in memory,
whereas at a later point in time your workarea sizes get reduced due
to other sessions also requiring PGA

- Try the NO_FILTERING hint to switch to the CONNECT_BY_NO_FILTERING
operation of the CONNECT BY operator

- Try a different execution plan: The optimizer estimates are not very
good, therefore you generate a lot of data only to throw most of it
away in the final join operation. However since the filter predicate
on PHY_ALL_OBJECTS includes a '%xxx%' like operator, different filter
values might lead to significant number of rows returned by this
operation.

Still if the number of rows returned from this operation is usually
small, you could try a CARDINALITY hint on this alias, e.g. /*+
CARDINALITY(objecttype, 10) */ to see if the optimizer is able to come
up with an execution plan using predicate pushing and the
PHY_ALL_OBJECTS as driving row source. Since this is a NOT IN operator
I doubt however that this transformation is available / supported by
the optimizer.

In that case the subquery containing the CONNECT BY would be executed
as many times as rows are generated by the driving row source but
additional filter / access predicates would be pushed into the
subquery which ideally reduced the number of rows to be processed by
the CONNECT BY operator - given the NOT IN this probably won't work...

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684

On Mon, 15 Nov 2010 20:18:48 -0800, Mick wrote:

So what made you post it here, if you don't have a control over it? Off
hand, the first two things to do would be to replace "not in" with "not
exist" and create a functional index on

OBJECTIVE.PHY_ALL_OBJECTS(upper(name))

That is the condition which requires resolution by the full table scan.
Depending on the selectivity of the condition "date_delete is null", I
would consider replacing NULL dates with 1.1.4001 or creating a
functional index on NVL(date_delete, to_date('01/01/4001','MM/DD/YYYY')).
The best "tuning method" for bad SQL is to rewrite it. If you can't do
that, your options are limited. Very limited.


--
http://mgogala.byethost5.com