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

we have a plsql package which calls some java classes within the database to perform a search. After upgrading from to 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
Messages: 25133
Registered: January 2009
Location: SoCal
Senior Member
I don't do Java so this may be a nonsensical idea.

-- invoke the problem procedure here

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
Messages: 6
Registered: April 2009
Junior Member
unfortunately I'm restrited in what I can give you regarding this. I'll log a call with Oracle.

[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: Tue Jan 17 11:50:03 CST 2017

Total time taken to generate the page: 0.05391 seconds