Re: This is a resubmission on an earlier enquiry
Date: Tue, 16 Nov 2010 03:15:57 -0800 (PST)
Message-ID: <64937150-c23c-4da9-bde1-bc9273290882_at_q14g2000yqe.googlegroups.com>
On Nov 16, 5:18 am, Mick <mjms..._at_gmail.com> wrote:
> I welcome any suggestions on how I can improve the performance of this
> statement.
> Please note that I have not control of the SQL code because it is
> vendor generated.
> Having said that, if anyone can show a change to the code that can
> cause a significant improvement then
> the vendor may be open to some changing to the coding.
> I am also open to other suggestions that may provide some performance
> improvements, i.e.
>
> - Changing of SGA parameters, sort_area_size, etc
> - Upgrading to Oracle 11
> - Suggestions for a materialized view.
>
> Many thanks for considering this request.
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-Practices-Database-Administration/dp/1430226684 Received on Tue Nov 16 2010 - 05:15:57 CST