Home » RDBMS Server » Performance Tuning » 9i -> 10g causes JAVA performance problems (10.2.0.3 RDBMS, AIX 3 5)
9i -> 10g causes JAVA performance problems [message #411757] Mon, 06 July 2009 10:26 Go to next message
jmoran
Messages: 6
Registered: April 2009
Junior Member
Hello,

we have a plsql package which calls some java classes within the database to perform a search. After upgrading from 9.2.0.8 to 10.2.0.3 the search is taking approximately double the time to complete. I have done a trace on the search and generated an AWR report which shows where the time is spent (see below), however I cannot use the sql tuning advisor on this code (presumably because it's JAVA). There are no other problems reported by the user for this system and no problem reported by ADDM. As a DBA is there anything else I might try to improve JAVA performance? I've tried various values for java_pool and keep_pool but they're hardly being used when the search is run. Are there any other parameters or anything else I could try changing from the DBA perspective? I can upload trc file, awr report, addm, and statspack reports if required.
Any help / suggestion appreciated. I've already passed back to the development team who wrote the code and they inform me 'it's a 10g issue', I guess at the moment it's in that grey area between DBA and Dev.

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 202 98.7
db file parallel write 76 2 27 1.0 System I/O
control file parallel write 36 1 16 0.3 System I/O
log file parallel write 30 1 17 0.2 System I/O
db file sequential read 57 0 9 0.2 User I/O
-------------------------------------------------------------
Time Model Statistics DB/Inst: RBTEST/RBTEST Snaps: 6099-6100
-> Total time in database user-calls (DB Time): 204.9s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name

Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 204.8 100.0
DB CPU 202.2 98.7
Java execution elapsed time 195.3 95.3
PL/SQL execution elapsed time 0.5 .2
parse time elapsed 0.2 .1
hard parse (sharing criteria) elapsed time 0.1 .1
hard parse elapsed time 0.1 .1
sequence load elapsed time 0.0 .0
repeated bind elapsed time 0.0 .0
DB time 204.9 N/A
background elapsed time 5.0 N/A
background cpu time 1.7 N/A
-------------------------------------------------------------
Re: 9i -> 10g causes JAVA performance problems [message #411806 is a reply to message #411757] Mon, 06 July 2009 20:22 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I don't do Java so this may be a nonsensical idea.

ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the problem procedure here
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

post the contents of trace_results.txt back here
Re: 9i -> 10g causes JAVA performance problems [message #411890 is a reply to message #411806] Tue, 07 July 2009 04:38 Go to previous message
jmoran
Messages: 6
Registered: April 2009
Junior Member
Hi,
unfortunately I'm restrited in what I can give you regarding this. I'll log a call with Oracle.
Thanks

[Updated on: Tue, 07 July 2009 06:31]

Report message to a moderator

Previous Topic: Performance question
Next Topic: SYSTEM TS is increasing
Goto Forum:
  


Current Time: Thu Dec 08 14:26:35 CST 2016

Total time taken to generate the page: 0.08674 seconds