 |
|
Oracle Tips by Burleson |
Segment Information for an
object
Notice that this table is about 1 MB in
size. Theorize that a KEEP pool of at least 1 MB in size is
created, and mark this table to be kept in that pool, the physical
I/Os should drop and performance should improve. The fact that the
hit ratio for the KEEP pool would be high is of no importance to the
user, so it is regarded as a collateral benefit to keep monitoring
tools happy.
Reconfigure the database so that it has a
KEEP pool of 2 MB. This will require a database restart unless the
application is at least version 9. Configure table “foo” to put it
into the KEEP pool that was just created. The following command
will accomplish this goal:
alter table foo storage (buffer_pool KEEP);
In order to examine the results, query the
user and run another STATSPACK report for a similar period of time
to look for the code seen earlier while accessing this table.
Reviewing the new report, it is evident that while this SQL
statement is still there, it no longer impacts the user as much
since it can almost always get the data from the buffer cache. As a
side benefit, notice that the Buffer Hit ratio is significantly
higher now as shown in Figure 7.14. This does not necessarily mean
that the performance is better, but it is back to what is considered
“normal” for this database.
Instance Efficiency Percentages (Target
100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo
NoWait %: 100.00
Buffer Hit %: 99.58 In-memory Sort
%: 98.64
Library Hit %: 100.00 Soft
Parse %: 100.00
Execute to Parse %: 37.68 Latch
Hit %: 100.00
Parse CPU to Parse Elapsed%:
119.05 % Non-Parse CPU: 98.32
Figure 7.14 – SPREPORT with higher buffer
hit ratio
Another area to consider is whether the SQL
statement in question is really needed. If this SQL statement is a
scheduled job that runs on a regular basis, it may be possible that
there is no longer a business need to continue running this job.
For example, a daily report could have been rendered obsolete by a
new screen that provides up-to-the-minute data.
If the SQL statement does pass that test,
then another question to ask is whether or not it can be optimized.
Optimizing could reduce the load on the database and make overall
performance better.
The above book excerpt is from:
Oracle Wait Event Tuning
High Performance with Wait
Event Iinterface Analysis
ISBN 0-9745993-7-9
Stephen Andert
http://www.rampant-books.com/book_2004_2_wait_tuning.htm |
