How to read statspack report

Jan 11 posted in Oracle (RSS), 411 views.

Generate a statspack report is relatively simple, but how to read statspack report is not so easy, the need for Oracle architecture, memory structure, and wait for events, and applications have a full understanding, coupled with constant practice, in order to read and understand the basic statspack report and from the report to find ways to adjust and optimize the Oracle.
The following bonding a practical statspack report, broadly analysis.

1. Basic Information Analysis

DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ------- ---- --- --------- ---
RES 2749170756 res 1 8.1.7.0.0 NO res

Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 2 26-Jul-03 16:37:08 38
End Snap: 3 26-Jul-03 17:03:23 38
Elapsed: 26.25 (mins)

Statspack report first describes the basic situation of the database, such as database name, instance name, instance number, oracle version number, etc.; and then the beginning of the report snapshot and end snapshot of information, including snap id, snap time, and so ; Finally, after the report, the time span, in units of minutes (mins).

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 200M Std Block Size: 8K
Shared Pool Size: 48M Log Buffer: 512K

2. Memory Information Analysis

Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 4,554.09 23,654.93
Logical reads: 151.64 787.66
Block changes: 31.75 164.90
Physical reads: 13.92 72.31
Physical writes: 1.77 9.22
User calls: 0.11 0.59
Parses: 3.82 19.85
Hard parses: 0.17 0.90
Sorts: 1.31 6.81
Logons: 0.03 0.17
Executes: 9.72 50.46

Transactions: 0.19

% Blocks changed per Read: 20.94 Recursive Call%: 99.86
Rollback per transaction%: 0.00 Rows per Sort: 526.77

Redo size:

Is the amount of log generated is divided into second and each firm produces, usually in a very busy system, the amount of log generated could reach hundreds of k, even hundreds, of k;

Logical reads:

Logical reading is actually a logical IO = buffer gets that meaning, we may not think so, block in memory, we read every time a memory, the equivalent to a logic of time;

Parses and Hard parses:

Parse and hard parse is usually very easy for some problems, 80% of the system are due to this reason the slow-caused. The so-called soft parse and parse sub-hard parse, soft parse is when a pass came in sql, you need to find it in the shared pool in the same sql, if found, it is soft parse, if not discovered, it began to hard parse In fact, hard parse is to check the sql mainly involved the validity of all the objects, as well as the relationship between rights and so on, hard parse before under the rule / cost models produce an execution plan, and then implementation of the sql. The hard parse the source are basically because they do not use bind var caused by, do not use bind var against the oracle of the shared pool of design principles, a violation of this is designed to share ideas, this has led to decline in hit rate inside shared_pool_size . Therefore, do not use bind var, will lead to cpu usage problem, making the performance very dramatically. There is to maintain internal structure, need to use the latch, latch is a low-level structure of Oracle are used to protect memory resources, is a very short life cycle of the internal lock, heavy use of latch will consume a lot of cpu resources.

Sorts:

That sort of quantity;

Executes:

Indicated that the number of times;

Transactions:

Indicated that the number of transactions;

Rollback per transaction%:

Indicated that the database transaction rollback rate. If not for the business itself, the reasons should normally be less than 10% of the good, back to back is a very resource-consuming operation.

Instance Efficiency Percentages

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait%: 99.99 Redo NoWait%: 99.85
Buffer Hit%: 93.92 In-memory Sort%: 99.99
Library Hit%: 96.87 Soft Parse%: 95.45
Execute to Parse%: 60.66 Latch Hit%: 99.98
Parse CPU to Parse Elapsd%: 80.04% Non-Parse CPU: 92.24

Buffer Hit%:

Data buffer hit ratio should preferably be greater than 90%. If you had to consider increasing the DB_CACHE_SIZE or DB_BLOCK_BUFFERS Hsiao Ke

Library Hit%:

libaray cache hit ratio should preferably be greater than 98%. If you had Hsiao Ke consider increasing SHARED_POOL_SIZE.

In-memory Sort%:

The proportion of in-memory sort, if this ratio is too small, you can consider increasing sort_area_size, in Oracle9i can consider adjusting pga_aggregate_target, makes the sort carried out in memory rather than in the temp table space carried out;

Soft Parse%:

The percentage of soft parsing, this percentage should be great we go, because we want to minimize the hard parse. soft parse Percentage = soft / (soft + hard);

Execute to Parse%:

This figure should also be the bigger the better, close to 100% the best. Some reports in this value is negative, and looked very strange. In fact that is a problem, sql if age out if such a situation might arise, that is, sql aging, or perform alter system flush shared_pool so.

Shared Pool Statistics Begin End
------------
Memory Usage%: 23.40 53.45
% SQL with executions> 1: 50.61 39.22
% Memory for SQL w / exec> 1: 73.57 53.89

% SQL with executions> 1:

This indicated that the number of SQL to be executed more than one ratio should be much better, small is that many sql be executed only once, indicating not use bind variables.

3, wait event analysis

Waiting for the event (Wait Events) is the Oracle of the more complex and difficult to understand concepts. Oracle wait events is a measure of the status of Oracle to run an important basis and targets. The concept of waiting for the event is Oracle7.0.1.2 introduced, there are roughly 100 to wait for the event. In Oracle 8.0 in this number increased to about 150, in the Oracle8i approximately 200 events, in the Oracle9i approximately 360 to wait for the event. There are two main types of wait events, that is idle (idle) to wait for events and non-idle (non-idle) to wait for the event. Free event that Oracle is waiting for some kind of work, in the diagnosis and optimization of the database, we do not have too much attention to this part of the event. Common idle events are: