Unnesting Subqueries in an Outline

From: Luke Davies <luke.davies_at_hansard.com>
Date: Thu, 23 Aug 2007 12:15:05 +0100
Message-id: <46CD6C39.6070608@hansard.com>

Hello

We are moving our application from Rule Based Optimisation to Cost Based Optimisation on 9.2.0.8.0 Standard Edition and have hit a show-stopper for us. There is one particular query that performs fine (around 10s - which is acceptable) in RBA but as soon as statistics are gathered then doesn't come back in under an hour (when we killed it!). The query is as follows

SELECT f.fund_code

, fp.bid_price
FROM fund_prices fp, funds f
WHERE fp.unp_number = (

       SELECT MAX (fp2.unp_number)
       FROM   fund_prices fp2
       WHERE  fp2.fund_code = f.fund_code
       AND    fp2.company_flag = f.company_flag
       AND    fp2.unit_code = 'B'
       AND    fp2.mfp_status = 'C')
AND    f.fund_code    = fp.fund_code

AND f.company_flag = fp.company_flag
AND f.company_flag = 'ISA'

We have got it to work by using a NO_UNNEST hint in the subquery. We then set the undocumented parameter _unnest_subquery in the parameter file but this caused everything else to slow down too much.

As it is a bit of a business to get the code changed and we were therefore wanting to try to use Outlines. However we cannot get them to work with this query.

We set the OPTIMIZATION_MODE to RULE; created the outline; set the OPTIMIZATION_MODE back to CHOOSE and then ran the query.

The outline was used - the column USED changed from 'UNUSED' to 'USED' and V$SQL.OUTLINE_CATEGORY was set to 'DEFAULT' but the query never returned.

We have logged a call with Oracle who can re-create the problem but we have had no response other than WIP. The problem does not exist in 10g.

Has anybody got any ideas how we can get this query to perform without changing the SQL?

Thanks
Luke

The contents of this message and any attachments are confidential and are intended for the use of the persons to whom it is addressed. If you are not the intended recipient, you should not copy, forward, use or alter the message in any way, nor disclose its contents to any other person. Please notify the sender immediately and delete the e-mail from your system. The sender is not responsible for any alterations that may have occurred without authorisation. Any files attached to this email will have been checked by us with virus detection software before transmission. You should carry out your own virus checks before opening any attachments, as we do not accept any liability for loss or damage which may be caused by viruses.

For information regarding company registration please visit the contact page at www.hansard.com

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 23 2007 - 06:15:05 CDT