9.2 performance issue - runs great on 7.3.4

I have a query:

SELECT *
FROM ss_vwap.order_wrapper_srv ows, ss_vwap.orders_srv os, ss_vwap.exec_report_view erv
WHERE ows.vwap_order_id = os.vwap_order_id
AND os.vwap_order_id = erv.erv_vwap_order_id(+)
AND os.order_id = erv.erv_order_id(+)
AND (ows.wrapper_status not in ('Fill', 'Expd', 'Fill-Canc', 'Canc Cnfrm', 'Busted', 'Snd', 'rejected', 'Schd Fail')
OR TRUNC(ows.record_timestamp) = TRUNC(sysdate) ) ORDER BY ows.vwap_order_id, erv.erv_exerptkeyseqnum

which run in a 7.3.4 database in 3 mins, but takes 18+ hours on 9.2.0.1.0.

I have analyzed the 3 tables, re-created the indexes, deleted the statistics, but cannot get the performance to improve.

I do not want to re-write the query, since it runs just fine in 7.3.4.

I did run explain plans, trace, before and after the changes, and I do see it doing full scan of one table, sorting, and has a cost of 14,000,000 !!, but I cannot understand why. The explain plan is the same on 7.3.4 db also. BTW, all three tables only have about 90K rows each.

Does anyone know of a 9.2 specific issue related to my performace problem?

I just got it to work in 1 min, but don't know how I can make the permanent change without hard-coding the fix:

I used the /+* RULE */ hint - that's all !!!

Any ideas ?

If the RBO is faster than the CBO, that tends to suggest to me that the CBO is not picking up on some useful indexes. Have you ...

i) computed statistics on the indexes?
ii) computed statistics on indexed columns?
iii) Tried using a functiomn-based index on TRUNC(ows.record_timestamp) -- feature that wasn't available in your old version, so read the documentation cause there's a couple of parameters you might have to change to get it to activate.

Also, there's a couple of init parameters to look at. Reduce the value on "optimizer_index_cost_adj" to something in the region of 10-20 (which you can do dynamically with an "alter session" command to test it's effect, and an "alter system" command once you think you have the right value), and "optimizer_index_caching" can be set to around 50 (also dynamic). Both of these changes will promote the use of indexes by the CBO.

Actually, when the instance has been up for a while run the following SQL ...

select 'Actual is '
||LTrim(to_char(seq_read_wait/scattered_wait*100,'9990.0'))
||', system is set to '||init_value message
from (
select average_wait seq_read_wait
from v$system_event
where event ='db file sequential read'),
(
select average_wait scattered_wait
from v$system_event
where event ='db file scattered read'),
(
select value init_value from v$parameter
where name = 'optimizer_index_cost_adj')

... and you'll get a result something like ...

"Actual is 5.5, system is set to 12"

... which will tell you what a suitable value of "optimizer_index_cost_adj" would be.

Good luck, and don't give up by using the RBO.

Thanks slimdave !

The last thing I want to do is hard code the RULE hint, or something similar...but

I tried the optimizer... params, but with no luck. The rule hint is amazingly fast !! Why cant the optimizer figure it out !!

As I stated initially, I already did analyze...compute, re-created the indexes, so stale stats cannot be the issue.

Anyway, please let me know of anything else I can try or look into, to get more info as to what may be going on.

Can you post the explain plans for the RBO and CBO versions?

And you're sure that the compute statistics was run after the indexes were rebuilt? (I only say that because of the order in which you list the steps you carried out)

And how many rows are acutally returned by the query?

How you are analyzing? Are you using DBMS_STATS? If not try to use DBMS_STATS package.

Thanks,
OraMan

MY BAD !!!

I mis-read one of the object names, and therefore, did not analyze all the tables used by the query.

Once I deleted the statistics from the 4 tables, everything was great !

If you deleted all the statistics, you are back to using the RBO, I believe.