Slow db performance on Oracle 11.2.0.3 [message #559254] |
Sat, 30 June 2012 21:14 |
|
u570744
Messages: 2 Registered: June 2012 Location: TX
|
Junior Member |
|
|
I installed and created a Oracle 11.2.0.3 database about two weeks ago.
I broght over data from Oracle 1.2.0.2 using expdp/impdp full=y
Our developer complains his batch test process ran much slower on Oracle 11.2.0.3 than on Oracl e 11.2.0.2.
eg.
it took only 11 second on Oracle 11.2.0.2 , but it took 18 second on 11.2.0.3
I opened a TAR with Oracle , Oracle said optimizer is different from version to version, tune the query.
Of course, our deleloper does not like this answer.
I tried to set the following parameter on Oracle 11.2.0.3
optimizer_features_enable=11.2.0.2
But, no performance gain at all
I compared the Oracle parameters among these two databases and I noticed the difference as following:
Oracle 11.2.0.2 Oracle 11.2.0.3
aq_tm_processes 0 aq_tm_proces 1
audit_trail none audit_trail db
db_file_multiblock_read_count 16 db_file_multiblock_read_conunt 82
memory_max_target 0 memory_max_target 16Gb
memory_target 0 memory_target 15GB
sga_max_size 7GB sga_max_size 16GB
sga_target 7GB sga_target 0
shared_pool_reserved_size 22439526 shared_pool_reserved_size 12582912
Do you know the above parameters cause the issue?
Thanks
Hope
|
|
|
|
|
|
Re: Slow db performance on Oracle 11.2.0.3 [message #559258 is a reply to message #559254] |
Sun, 01 July 2012 02:19 |
John Watson
Messages: 8937 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:I opened a TAR with Oracle , Oracle said optimizer is different from version to version, tune the query.
Of course, our deleloper does not like this answer.
Better replace the developer. Or at least tell him to do this:
Test Black Swan's suspicions regarding execution plans with the AWR SQL report: run awrsqrpt.sql on both systems. Then if the plans are different, investigate the use of SQL Plan Baselining: capture a baseline on 11.2.0.2, transfer it to 11.2.0.3, and fix it there.
I don't like "fixing" problems like this. The real answer is to tune the SQL.
|
|
|
|
Re: Slow db performance on Oracle 11.2.0.3 [message #559333 is a reply to message #559254] |
Mon, 02 July 2012 11:28 |
|
LNossov
Messages: 318 Registered: July 2011 Location: Germany
|
Senior Member |
|
|
The parameter db_file_multiblock_read_count can influence performance. You should not have set it at all.
Did you notice, that you reduced memory for your instance in 11.2.0.3? (memory_max_target includes also pga memory)
I don't think, that these parameter settings caused the issue, because of small runtime of the batch process.
So you should really check your sql's.
|
|
|