Home » RDBMS Server » Performance Tuning » Slow db performance on Oracle 11.2.0.3 (Oracle 11.2.0..3, Linux)
Slow db performance on Oracle 11.2.0.3 [message #559254] Sat, 30 June 2012 21:14 Go to next message
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 #559255 is a reply to message #559254] Sat, 30 June 2012 21:21 Go to previous messageGo to next message
BlackSwan
Messages: 21982
Registered: January 2009
Senior Member
>Do you know the above parameters cause the issue?
I doubt those parameters are the cause.

post EXPLAIN PLAN from same SQL against both versions

we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Slow db performance on Oracle 11.2.0.3 [message #559256 is a reply to message #559255] Sat, 30 June 2012 22:01 Go to previous messageGo to next message
u570744
Messages: 2
Registered: June 2012
Location: TX
Junior Member
Thank you for you quick reply!

The problem is the SQL are generated by other application ORM tool (Hibernate).

I am not able to run these SQL on SQLPLUS to get explain plan.

I took awrrpt(awrrpt_oobdcd01.html is from 11.2.0.3, awrrpt_oobdcd03.html from 11.2.0.2) from both server based on the time interval when he ran his test.

we do see his SQL modle 'JDBC Thin Client' on Oracle 11.2.0.2.


For some reason, we don't see his SQL modle'JDBC Thin Client' under SQL modle column on 11.2.0.3 on secion SQL ordered by Elapsed Time
We see many 'OEM.SystemPool','emagent_SQL_oracle_database','Realtime Connection' under SQL model, we are wonder if these modles slow down the db performance?

Would you please review both awrrpts and let me know if you see any DB bottlnecks on 11.2.0.3 ?

Thanks
Hope

Re: Slow db performance on Oracle 11.2.0.3 [message #559257 is a reply to message #559256] Sat, 30 June 2012 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 21982
Registered: January 2009
Senior Member
>The problem is the SQL are generated by other application ORM tool (Hibernate).
>I am not able to run these SQL on SQLPLUS to get explain plan.

within LOGON trigger you can do as below
ALTER SESSION SET SQL_TRACE=TRUE
process trace file with TKPROF; which can produce EXPLAIN PLAN
does new version have all same indexes & current statistics on both tables indexes involved?
Re: Slow db performance on Oracle 11.2.0.3 [message #559258 is a reply to message #559254] Sun, 01 July 2012 02:19 Go to previous messageGo to next message
John Watson
Messages: 4102
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 #559265 is a reply to message #559258] Sun, 01 July 2012 05:43 Go to previous messageGo to next message
Flyby
Messages: 139
Registered: March 2011
Location: Belgium
Senior Member
Did you regather statistics?
Good source on upgrading: Slides Download Center
Re: Slow db performance on Oracle 11.2.0.3 [message #559333 is a reply to message #559254] Mon, 02 July 2012 11:28 Go to previous message
LNossov
Messages: 283
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.
Previous Topic: SGA related queries
Next Topic: partition pruning
Goto Forum:
  


Current Time: Thu Apr 24 20:27:17 CDT 2014

Total time taken to generate the page: 0.08982 seconds