statspack report

[Dahmir]

Hi DBA gurus!

I need your help with "reading" of a statspack report. I do not have enough experience to do that.
The database type is OLTP. This is a snapshot for one of the peak hours (60 mins), the number of sessions is 200.

My conclusions:
-- Cache sizes are OK
-- Load profile, physical/logical reads, hard parses/totla parses ratios and total number of hard parses (one hard parse in 6-7 sec) are OK
-- Instance Efficiency Percentages OK except Execute to Parse %" ratio
-- Shared pool Memory usage is too high

The questions I do not have answers for:
-- How to improve "Execute to Parse %" ratio?
-- Shared pool Memory usage is too high, but it is 575Mb already - does it make sense to increase it further?
-- Is it worthwile to spend time trying to tune "enqueue" wait (which is all about TX lock - I checked in stats$enqueuestat), "db file sequential read", "db file scattered read" and "log file sync"?

Any comments would be greatly appreciated.
Thank you,

Here is the first page of the report:

Cache Sizes
~~~~~~~~~~~
db_block_buffers: 12500 log_buffer: 780000
db_block_size: 8192 shared_pool_size: 575000000

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 15,540.82 4,532.53
Logical reads: 1,375.44 401.15
Block changes: 87.13 25.41
Physical reads: 164.05 47.84
Physical writes: 7.30 2.13
User calls: 149.36 43.56
Parses: 25.98 7.58
Hard parses: 0.08 0.02
Sorts: 5.98 1.74
Logons: 0.05 0.02
Executes: 55.84 16.29
Transactions: 3.43

% Blocks changed per Read: 6.33 Recursive Call %: 30.17
Rollback per transaction %: 3.57 Rows per Sort: 57.74

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 88.07 In-memory Sort %: 99.90
Library Hit %: 99.89 Soft Parse %: 99.68
Execute to Parse %: 53.48 Latch Hit %: 99.95
Parse CPU to Parse Elapsd %: 88.61 % Non-Parse CPU: 99.91

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 95.00 95.01
% SQL with executions>1: 71.26 73.80
% Memory for SQL w/exec>1: 82.66 84.09

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
enqueue 456 139,700 72.65
db file sequential read 59,951 20,953 10.90
db file scattered read 28,748 10,737 5.58
PL/SQL lock timer 25 6,759 3.52
log file sync 14,014 6,499 3.38
------------------------------------------------------------

[marist89]

My observations would be:
1. The buffer hit ratio looks low, maybe increase db_block_buffers.
2. The execute to parse ratio may be low, but it depends on your system. If you have lots of queries executed once and never executed again, you would get a lower e/p ratio. The more important ratio IMHO is the soft parse ratio. In your case 99.68% of your statements are reused.
3. The enqueue waits most likely indicate a locking problem with your application.
4. db file sequential read and db file scattered read are I/O events. I would check for an overburdened device and possibly spread out the load.

[Dahmir]

Hello Jeff,

Thank you for your responce.

Originally posted by marist89
2. The execute to parse ratio may be low, but it depends on your system. If you have lots of queries executed once and never executed again, you would get a lower e/p ratio. The more important ratio IMHO is the soft parse ratio. In your case 99.68% of your statements are reused.

What is over my head here is: if almost half of our queries executed only once and never again then why we have such a good soft parse ratio? Shuold it be lower in this case (every time I execute a new statement, I have to hard parse it?)?

Originally posted by marist89

3. The enqueue waits most likely indicate a locking problem with your application.
4. db file sequential read and db file scattered read are I/O events. I would check for an overburdened device and possibly spread out the load.

Ok, but my question is: are all these wait events really a problem in our system? Will we notice a visible improvement if I reduce the wait events time, say, two times less? Is there a way to estimate it.

Thank you,
Dahmir

[marist89]

Originally posted by Dahmir
What is over my head here is: if almost half of our queries executed only once and never again then why we have such a good soft parse ratio? Shuold it be lower in this case (every time I execute a new statement, I have to hard parse it?)?

This typically happens in a system where a session connects, does something, and then disconnects (web apps not using connection pools, for example). The connection is established, the sql statement is parsed and executed, and then the session disconnects. Because you are explicitly parsing the statement every time your parse/execute ratio gets out of whack. However, Oracle is "smart" enough to find your parsed statement already in memory, hence a soft parse.

Ok, but my question is: are all these wait events really a problem in our system? Will we notice a visible improvement if I reduce the wait events time, say, two times less? Is there a way to estimate it.

Er, that kind of depends. If one of your disks is 100% busy, then you will probably see some improvement on the I/O events. Since you are waiting on enqueue resources the most, I would suspect you would see improvement by reducing these. However, you might be running on a super fast computer that is oversized for your app and you won't notice any improvement in response time.

[Dahmir]

It is much clear now.