Database Reference
In-Depth Information
If you continue the analysis at the system or session level, your goal is to find whether a small number of SQL
statements (let's say, a dozen) are responsible for most of the load. For example, if you see seven SQL statements that
cause 85% of the load, you have clearly identified the top SQL statements you should focus on. However, if the top 10
SQL statements are responsible for only 25% of the load, focusing only on them is probably not worth your time.
You should also check whether there are a small number of “components” (for example, sessions, modules, or
clients) that are responsible for most of the load. If that's the case, you can continue the analysis by focusing on those
“components” only. In any case, if the analysis at the system level doesn't lead to the identification of the top SQL
statements, you have to question the application and, therefore, start reviewing it to check whether the processing it
does has been implemented in an efficient way. If the application code can't be analyzed (or modified), or the review
doesn't lead to satisfying results, the only thing you can still consider is resource management. Simply put, you have
two options. First, define parts of the application (for example, some sessions or users) to receive more resources than
the others. Second, provide more resources (hardware) to the application. The latter is of course the last thing you
should consider!
A special case to consider is when either the system or the session you're analyzing is (almost) idle. By
idle
,
I mean that most of the processing time is spent outside the database system. For example, if you see a report that
takes 13 minutes to run and, during that time, only 42 seconds are spent by the database engine processing some SQL
statements related to it, independently of how many SQL statements were processed, focusing on the database tier
is useless. Clearly, the bottleneck isn't to be located in the database tier. Again, the application or other parts of the
infrastructure supporting the application should be reviewed.
Independently of whether you focus on a single SQL statement, a single session, or the whole system, for each
top SQL statement you find, you have to gather the execution plan, key runtime statistics like number of processed
rows and amount of CPU utilization, and the experienced wait events. To describe how to find this data, the next
section provides basic information about important dynamic performance views that you need to know. Then the
chapter continues by explaining in detail how to carry out a real-time analysis of a performance problem based on the
information provided by the dynamic performance views.
Dynamic Performance Views
Oracle Database provides dynamic performance views to externalize the content of some data structures that reside
either in memory or in database files. In other words, even though they seem to be regular tables, the underlying
structures that contain the data are completely different. Those structures are exposed in views to give easy access to
their data through SQL.
Because the data structures, which the dynamic performance views rely on, are constantly changed by the
database engine, the data provided through the dynamic performance views can constantly change as well. Be aware
that not every dynamic performance view is updated in the same way. For example, some of them are continuously
updated, and others are only updated every 5 seconds.
■
For queries that access dynamic performance views, read consistency isn't guaranteed. So don't let small
errors or inconsistencies confuse you.
Caution
When describing dynamic performance views, I typically reference the views with the
v$
prefix. If you're working
on a RAC environment, be aware that the
v$
views show only information about the database instance you're
connected to. If you require information about other database instances, you have to use the corresponding global
views that have the
gv$
prefix. The structure of the
gv$
views is equivalent to the one of the
v$
views. In general, the
only difference is that
gv$
views have an additional column (
inst_id
) identifying the database instance.
Some of the statistics provided by dynamic performance views depend on the
timed_statistics
initialization
parameter, which can be set to either
TRUE
or
FALSE
. If it's set to
TRUE
, timing information is available. If it's set to
