Databases Reference
In-Depth Information
Is it good to use ratios?
Throughout this chapter, we calculate and use various ratios as a simple way
to see if there is a problem in a database which we can investigate further
using other tools or reports.
The values suggested (as the 99.9 percent for the Library Cache hit ratio,
see
http://raj_oracle90.tripod.com/sitebuildercontent/
sitebuilderfiles/whya99percentbuffercacheratioisnot
ok-carymillsap.pdf
) have to be seen as optimal values to reach, but
we can experience systems running slow even when we reach these ratios—or
systems running very well with ratios far from the provided values.
Using ratios for a first estimate of problems is a fast and good way, but you
should base your decisions also on more sophisticated reports and tools.
We can obtain the same information by executing a
statspack
report. In the following
screenshot, you can see an excerpt from the report:
In this report, the
PctMiss
percentage values should be very low, because they identify
how often the requested objects are not found in Library Cache.
In step 4, we execute a sample query to illustrate how to find the executions of a specific
query using the
V$SQLAREA
dynamic performance view in step 5.
Using the results collected earlier, we query the
V$SQL_PLAN
dynamic performance view,
in step 6, to see the cached execution plan.
