Statspack Report Data Results explanation

Data
This article, from the Oracle China User Group (www.oracle.com.cn) article, finds it helpful to tune in to your learning performance:

Original link: http://www.cnoug.org/viewthread.php?tid=25353

Statspack Report Data Results explanation

I will recently in learning performance tuning, the use of notes summarized below, welcome to criticize
This article will be constantly updated, welcome to add. (The data listed is only for illustration purposes, no real
Inter-significance)

10 items that must be viewed in statspack output results

1, load between files (load profile)
2, instance efficiency click Rate (Instance efficiency hit ratios)
3, the first 5 waiting events (Top 5)
4, Waiting for events (wait event)
5. Latch-Lock wait
6, the first SQL (top SQL)
7. Example activities (Instance activity)
8. Document I/O (file I/O)
9, memory allocation (Memory allocation)
10, buffer Waiting (waits)

Ii. Interpretation of output results

1. Report Header information
Database instance related information, including database name, ID, version number, and host information


Quote:statspack for

DB Name db Id Instance Inst Num release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
Pormals 3874352951 pormals 1 9.2.0.4.0 NO njlt-server1

Snap Id snap time Sessions curs/sess Comment
------- ------------------ -------- --------- -------------------
Begin snap:36 1 August-July-04 20:41:02 29 19.2

End snap:37 1 September-July-04 08:18:27 24 15.7

elapsed:697.42 (mins)

Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer cache:240m STD Block size:8k
Shared Pool size:96m Log buffer:512k

2, load between files
This section provides statistical information for each second and every thing, and is an important part of monitoring system throughput and load changes


Quote:load profile
~~~~~~~~~~~~ per Second (sec)/Transaction Things
---------------       ---------------
Redo size:148.46 3,702.15
Logical reads:1,267.94 31,619.12
Block changes:1.01 25.31
Physical reads:4.04 100.66
Physical writes:4.04 100.71
User calls:13.95 347.77
parses:4.98 124.15
Hard parses:0.02 0.54
sorts:1.33 33.25
logons:0.00 0.02
executes:2.46 61.37
transactions:0.04

% Blocks changed per read:0.08 recursive call%: 30.38
Rollback per transaction%: 0.42 Rows/sort:698.23

Description
Redo Size: The log size (in bytes) generated per second, which can flag the heavy workload of the database task
Logical reads: The logical reading generated per second, Unit is block
Block changes: The number of blocks per second, the number of changes in the database things
Physical reads: Average number of blocks per second that the database reads from disk
Physical writes: Average number of blocks per second for database write disks
User calls: Number of call times per second
Parses: number of parse times per second, approximate response number of statements per second
Soft parsing more than 300 times per second means your "application" effect
Rate is not high, do not use soft soft parse, adjust Session_cursor_cache
Hard parses: Number of hard resolutions produced per second
Sorts: Number of orders generated per second
Executes: Number of executions per second
Transactions: The number of transactions generated per second, reflecting the heavy workload of the database task

3. Instance hit rate
It is important to find out in advance the potential performance issues that Oracle will take.


Quote:instance efficiency percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer nowait%: 100.00 Redo nowait%: 100.00
Buffer Hit%: 99.96 in-memory Sort%: 99.14
Library Hit%: 99.53 Soft Parse%: 99.57
Execute to Parse%: -102.31 Latch Hit%: 100.00
Parse CPU to Parse elapsd%: 81.47% Non-parse

Description
Buffer nowait%: The wait rate in the buffer to get buffer
Redo nowait%: Not waiting ratio for buffer to get buffer in Redo
Buffer Hit%: Data blocks in the data buffer hit, usually should be more than 90%, otherwise, you need to adjust
In-memory Sort%: sort rate in memory
Library Hit%: mainly represents the SQL in the share hit rate, usually above 95%, no, need to consider add
Large sharing pool, binding variables, modifying cursor_sharing parameters.
Soft Parse%: Approximate as SQL in the share hit rate, less than <95%, need to consider binding, if less than 80%,
Then it's possible that SQL is largely not reused
The number of times that the execute to Parse%:sql statement is repeated after parsing, and if it is too low, you can consider setting
Session_cached_cursors parameters
Parse CPU to Parse elapsd%: Parse actual run event/(parse actual run time + parse in wait resource time)
As high as possible.
% non-parse CPU: Query actual run time/(query actual run time +sql resolution Time), too low to indicate that the resolution consumes too much time.


quote:shared Pool Statistics Begin End
------  ------
Memory Usage%: 33.79 57.02
% SQL with executions>1:62.62 73.24
% Memory for SQL w/exec>1:64.55 78.72

Shared Pool Related statistics

Memory Usage%: Shared pool memory usage, should be stable between 75%-90%, too small waste of memory, too large is not enough memory.

% SQL with executions>1: SQL ratio with execution times greater than 1, if too small may not be using bind variables.

% Memory for SQL W/exec>1: Also that is Memory for SQL with execution > 1: SQL that executes more than 1 times
Consumes memory/all SQL consumed memory

4, the first waiting event


Common Wait Event Description:
Oracle Wait events are an important basis and indication of Oracle's health, with idle wait events and non idle Wait events
Idle wait event is Oracle waiting for some kind of work, not paying too much attention to this part of the event while diagnosing and optimizing the database.
Non-idle wait events are specific to Oracle's activities, which are the waiting events that occur during database tasks or applications running, which we should focus on in the tuning database.

Compare impact performance Common wait events
DB file Scattered read
This event is usually associated with a full table scan. Because the full table scan is carried out in memory,
In general, it cannot be put into a contiguous buffer, so it is scattered in the buffer cache. The amount of the index is too large to explain
Missing indexes or restricting use of indexes. This may also be normal, since performing a full table scan may be more efficient than an index scan.
When the system has these waits, it needs to be checked to determine if the full table scan is required to adjust. You can try to put a smaller table into the
Cache Keep, avoid reading them over and over again.
DB file Sequential read
This event indicates a large number of waits on a single block of data that is too high, usually due to poor connection order between tables, or the use of a non-selective
Index of choice. By linking this wait to known other issues in the Statspack report (such as inefficient SQL), check
The index scan is required and ensures that the connection order of the multiple table connections is adjusted
Buffer Busy Wait
The wait occurs when the buffer is in a unshared way or if it is being read into the buffer. The value should not be greater than 1%, confirming
Is it due to a hotspot block (if you can use a reverse index, or a smaller block size)
Latch FREE
Latches are the underlying queue mechanism (a more accurate name should be a mutex) to protect the system global Area (SGA) shared memory structure
。 Latches are used to prevent parallel access to the memory structure. If the latch is not available, a latch loss is logged. Most latch problems
are associated with failure to use bound variables (library cache latches), build redo issues (redo allocation latches), cached contention issues (cached LRU chain) to
And the cached hot data block (cache chain). This problem needs to be adjusted when the latch loss rate is above 0.5%.
Log Buffer Space
Log buffers are written faster than LGWR write redofile, you can increase the log file size, increase the size of the log buffer, or
People use faster disk to write data.
LogFile Switch
Usually because the archive is not fast enough, you need to increase the redo log
Log file Sync
When a user submits or rolls back the data, LGWR fills the session redo operation from the log buffer into the log file, and the user's process
You must wait for this fill to complete. To reduce this wait event, you need to submit more records at one time, or you will redo logs redo log files
Visit a different physical disk.