Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

patch upgradation from 9.2.0.6 to 9.2.0.8

661050Apr 16 2009 — edited Apr 22 2009
Dear All,
Recently we have upgraded our database from 9.2.0.6 to 9.2.0.8. every thing went smooth.
After everything was over we have analyzed all the tables and indexes with sample size 5%.

Now we are facing performance issues with the optimizer. The optimizer for 9.2.0.8 is not picking the correct query execution plan and the query is running for long hours.

The problem is with the quries having the subqueries, and the subquery contains date column.
we have picked some queries which are having subqueries and with out date column in the subqueries. Those queries are working fine but the problem is with the subqueries having the date column.

is there any thing that we have to do for the optimiser so that optimiser can pick the corect execution plan.

Thanks in advance,
Raju

Comments

damorgan
Posting explain plan outputs created with DBMS_XPLAN so we can do more than guess would be a good start.

A couple of other points:

1. 5% of what? Do the tables have 5 rows or 500,000,000?

2. 9.2.0.8 is an improvement but it is still 8 year old technology in desupport. Why not move to 11.1.0.7?

Edited by: damorgan on Apr 16, 2009 9:59 PM
Srini Chavali-Oracle
Need more info :-)

When your query takes too long:

1814597

865295

HTH
Srini
661050
Hi,
The 5% was the sample sample size that we mention with the Gather stats.
The affected table has 120,000,000 rows

The query as such is following the normal subquery format

select * from
t1,t2
where t1.col1=t2.col2
and t1.col4 = 'true';
and t2.col3 in
(
select max(t3.date)
from t3
where t1.col1=t3.col1
);

We have an index in t1 with col1 as leading column and col4 as subsequent column.

The plan in 9206 has range scan on index of t1 using t1.col1.

the plan in 9208 uses fast full scan on t1 using t1.col4.

Please help.

Thanks
Raju
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 20 2009
Added on Apr 16 2009
#database-installation
3 comments
883 views