Thanks for the question, sunil.
Asked: July 19, 2017 - 11:52 am UTC
Last updated: July 21, 2017 - 1:16 am UTC
Version: Oracle 11g r2
Viewed 1000+ times
You Asked
We are planning to migrate Oracle 11g DB from HPUX environment to Vmware infrastructure. but while doing performance testing we are not getting expected performance while executing batch job.benchmark is 200k transaction per minute but on linux it is processing 75k per minutes
Client is HPUX11iv3 16 core 256 GB RAM and DB server is RHEL 6.7 ( 16 core 64 GB RAM)
SGA 3,058.3
TOP events
Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DB CPU 8773.6 53.9
db file sequential read 4,749,119 4226.3 1 25.9 User I/O
SQL*Net break/reset to client 19,740,519 2824.5 0 17.3 Application
kksfbc child completion 36,712 1794 49 11.0 Other
CPU utilisation on DB is hardly 20%
and Connor said...
So assuming this data is specific to the task at hand, you're dropping a big chunk of time on:
1) db file sequential read
This is indexed access to a table. So look for poor optimization plans for SQL's. Statistics might be out of data, or some other reason. The *average* time there looks like 1ms, so that suggests the I/O performance is adequate.
2) SQL*Net break/reset to client
You get this when something is presented to the database which crashes. For example, if your app sends over: "select * from NON_EXISTENT_TABLE", then since the table does not exist, the subsequent parsing error will result in this event. It's the database saying "Whoa...something didnt work, lets all get back in sync and start again"
3) kksfbc child completion
Related to parsing of SQL.
So if I had to make an guess - you've a got an app doing potentially lots of parsing, and in many of those cases, the parsing is failing (causing the reset).
Sounds like a long road ahead .... sorry.
Is this answer out of date? If it is, please let us know via a Comment