Donald K. Burleson
Oracle Bottleneck Analysis
How does one correctly practice bottleneck or wait-based analysis? First, it
is imperative that the timed_statistics initialization parameter be set to
TRUE, if the Oracle wait events are to be examined. By default, this
parameter is set to FALSE, which disallows the collection of wait times for
each wait event defined in the Oracle engine.
For one to really understand the impact of wait events on database
performance, the DBA needs to not only discover what the database is or has
been waiting on but the durations of the waits. Having both allows a
complete picture to be formed regarding the magnitude of wait-initiated
Almost all Oracle experts now agree that collecting time statistics adds
little, if anything, to database overhead, so setting timed_statistics to
TRUE should not be a worry. The parameter can be dynamically altered at both
the system and session levels, so the database does not have to be shutdown
and then restarted for the change to take effect. A simple alter system set
timed_statistics= true should do the trick.
The next prerequisite to using bottleneck analysis is that certain wait
events should be filtered out of any metrics used to diagnose performance
bottlenecks. For example, Oracle will record a wait statistic that
represents how long a particular user sits at their SQL*Plus prompt between
each issued database request.
Such a statistic provides no real value to a DBA who is trying to figure out
where a database bottleneck exists. Any SQL scripts that are used to collect
database wait statistics should exclude such events. A listing of these
Oracle events (normally dubbed idle events) to eliminate includes:
lock element cleanup
rdbms ipc message
SQL*Net message from client
SQL*Net break/reset to client
SQL*Net message to client
SQL*Net more data to client
parallel query dequeue wait
parallel query idle wait - Slaves
PL/SQL lock timer
virtual circuit status
The above is an excerpt from
Oracle Performance Troubleshooting by Robin
It's only $19.95 and you can order it
and get instant access to the Oracle scripts here:
||Need an Oracle Health Check?
Does your boss blame you for an Oracle performance problem?
Need to prove that your database is properly optimized?
BC Oracle performance guru's can quickly verify every aspect of your
Oracle database and provide a complete certification that your database
is fully optimized.