Home » RDBMS Server » Performance Tuning » Performance issue (9.2.0.8 and 11.2.0.3)
Performance issue [message #605525] Fri, 10 January 2014 05:10 Go to next message
Amine
Messages: 265
Registered: March 2010
Senior Member

Hi all,
I have a performance issue when moving from a server that holds a 9iR2 Database to a new server that holds a 11gR2 database.
Export was done successfully and all the data was moved from 9i to 11g.

When I launch a query on a table that holds 7800 rows. in 9i, the query finishes on 35 seconds. On the new server it finishes on only 2 seconds !
Ok,that's good. Now, on the 9i server we have a really complex view. And when I launch a query on it, 'select 1 from <th_complex_view> where id = 504', it returns a result after only 6 seconds in 9i. In 11g, it has never finished.

I did DBMS_STATS.gather_schema_stats(<user>) on all schemas but no improvement was done.

Any advice, any tip, link will be appreciated.

Thanks in advance,
Amine
Re: Performance issue [message #605528 is a reply to message #605525] Fri, 10 January 2014 05:27 Go to previous messageGo to next message
John Watson
Messages: 4686
Registered: January 2010
Location: Global Village
Senior Member
The quick fix may be to create a stored outline in your 9.x instance and transfer it to your 11.x instance. Or if you don't want to do that, your could try
alter session set optimizer_features_enable=9.something
and then query the view.

Then you have to do some real work to find the problem and solution.
Re: Performance issue [message #605555 is a reply to message #605528] Fri, 10 January 2014 08:28 Go to previous messageGo to next message
Kevin Meade
Messages: 1943
Registered: December 1999
Location: Connecticut USA
Senior Member
The non-quick fix would be to actually do some work and determine the reason why the 11g query is going slow.

What would you do if this was not an upgrade situation and the query was going slow on 9i?  That is what you do on 11g.


I would suggest you also consider DYNAMIC SAMPLING. It behaves differently on 11g than 9i and has been a culprit in some slowedowns.

That being said, I refer you back to my original suggestion, that a basic tuning exercise is in order. There is no easy lunch when it comes to tuning. Since you have both databases, you have the advantage of being able to review two different query plans. This might be a place to start.

Kevin

[Updated on: Fri, 10 January 2014 08:28]

Report message to a moderator

Re: Performance issue [message #605572 is a reply to message #605555] Fri, 10 January 2014 14:57 Go to previous messageGo to next message
Amine
Messages: 265
Registered: March 2010
Senior Member

Will bring the two plans once at work.
Re: Performance issue [message #605692 is a reply to message #605572] Mon, 13 January 2014 12:27 Go to previous message
Amine
Messages: 265
Registered: March 2010
Senior Member

I launched the complex query at the end of the day in 11g and the day after I noticed that it had returned a result after 30 minutes.
I did then this :
dbms_stats.gather_system_stats;


The query then was executed in 9 seconds !

How incredible stats are ! I saw this and it is said that "Oracle recommends to run GATHER_SYSTEM_STATS ('noworkload') after creation of the database and tablespaces".

Does some one have more recommendations of this type to help the optimizer more and more ??

Thanks in advance,

Amine
Previous Topic: Checking Lock history
Next Topic: Query Regarding, Average Active Session
Goto Forum:
  


Current Time: Thu Oct 23 08:50:21 CDT 2014

Total time taken to generate the page: 0.09404 seconds