| Performance issue [message #605525] |
Fri, 10 January 2014 05:10  |
Amine
Messages: 376
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  |
John Watson
Messages: 8960
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
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 |
 |
Kevin Meade
Messages: 2103
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 #605692 is a reply to message #605572] |
Mon, 13 January 2014 12:27 |
Amine
Messages: 376
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
