Oracle Statspack Analysis Report detailed

Statspack 10 items that must be viewed in the output result

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 Waits

1. Report Header information

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

Statspack for

DB Name db Id Instance Inst Num release Cluster Host

Blissdb 4196236801 blissdb 1 9.2.0.4.0 NO BLISS

Snap Id snap time Sessions curs/sess Comment

Begin snap:4 2 March-June-05 17:43:32 10 3.3

End Snap:5 2 March-June-05 18:01:32 12 6.1

elapsed:18.00 (mins)

Cache Sizes (end)

Buffer cache:24m STD Block size:8k

Shared Pool size:48m Log buffer:512k

2. Load between files

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

Load profile

~~~~~~~~~~~~

Per Second per Transaction

Redo size:431,200.16 18,627,847.04z

Logical reads:4,150.76 179,312.72

Block changes:2,252.52 97,309.00

Physical reads:23.93 1,033.56

Physical writes:68.08 2,941.04

User calls:0.96 41.36

parses:1.12 48.44

Hard parses:0.04 1.92

sorts:0.77 33.28

logons:0.00 0.20

executes:2.36 102.12

transactions:0.02

Redo Size: The amount of redo log size (in bytes) generated per second, which flags the frequency of data changes and the heavy workload of database tasks. In this example, the average of 430K per second produces a redo, each transaction produces a 18M

Redo.

Logical reads: The logical reading generated per second, the unit is block.

Block changes: The number of blocks changed per second, and the number of chunks of database things that bring change.

Physical reads: The average number of blocks per second that the database reads from the disk.

Logical reads and physical reads comparison: About 0.55% of the logical readings resulted in physical I/O, with an average of about 180,000 logical reads per transaction, in this case some of the larger transactions are executed and therefore very

The high number of reads is acceptable.

Physical writes: The average number of blocks per second database write disk.

User calls: Number of call times per second.

Parses and hard parses: about 1.12 resolutions per second, 4% of which are hard parsing, and the system analyzes some SQL every 25 seconds, all right. For the optimized system, after several days of operation, this column should reach 0, all

SQL should be in a shared pool after a period of time.

Sorts: The number of sorting times produced per second.

Executes: Number of executions per second.

Transactions: The number of transactions generated per second, reflecting the heavy workload of database tasks.

% Blocks changed per read:54.27 recursive call%: 86.94

Rollback per transaction%: 12.00 Rows/sort:32.59

% Blocks changed per read: note 46% logical reads are for read-only rather than modifiable blocks, and the system only updates 54% of blocks.

Rollback per transaction%: Percentage of transaction rollback. The formula is: Round (user rollbacks/(user commits + user rollbacks), 4) * 100%. In this example, every 8.33 transactions

Causes a rollback. If the rollback rate is too high, it may indicate that the database has experienced too many invalid operations. Excessive rollback may also bring the undo block competition.

3. Instance hit rate

It is important that this section proactively identify potential performance issues that Oracle will have to take.

Instance efficiency percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~

Buffer nowait%: 100.00 Redo nowait%: 100.00

Buffer Hit%: 99.42 in-memory Sort%: 100.00

Library Hit%: 98.11 Soft Parse%: 96.04

Execute to Parse%: 52.57 Latch Hit%: 100.00

Parse CPU to Parse elapsd%: 11.40% Non-parse

Buffer nowait%: In the buffer to obtain the waiting ratio of the buffers, buffer nowait<99%, there may be a hot block (find X$bh tch and V$latch_children cache buffers Chains)

。

Redo nowait%: The wait ratio for buffer is obtained in the Redo buffer.

Buffer Hit%: Data block in the data buffer hit rate, usually should be more than 90%, otherwise, less than 95%, need to adjust important parameters, less than 90% may be to add db_cache_size, but a large number of optional

The index also causes the value to be high (a large number of DB file sequential read). If the index on a frequently accessed column is deleted, the buffer hit may be significantly reduced. If you add an index, but it shadows

The driver sequence when Oracle correctly chooses the table connection may cause the buffer hit to increase significantly. If the hit rate changes a lot, you need to change the SQL mode.

In-memory Sort%: the sort rate in memory.

Library Hit%: The main representative of SQL in the share of the hit rate, usually more than 95%, otherwise need to consider increasing the sharing pool, binding variables, modify cursor_sharing and other parameters.

Soft Parse%: Approximate as SQL in the share hit rate, less than <95%, need to take into account binding, if less than 80%, then the SQL may be basically not reused.

Execute to Parse%: A statement executes and analyzes how many times the metric. In a system that analyzes and then executes a statement and no longer executes it in the same session, this ratio is 0. The calculation formula is:

Execute to Parse =100 * (1-parses/executions). Therefore, if the system parses > executions, this ratio may appear to be less than 0. In this case, for each analysis, approximately the

2.1 Times. This value <0 usually indicates a problem with shared pool settings or efficiencies, resulting in repeated parsing, reparse may be more serious, or snapshot, if the value is negative or extremely low, it usually indicates that the database

There is a problem with performance.

Latch Hit%: To ensure >99%, there are serious performance issues, such as binding, which can affect this parameter.

Parse CPU to Parse elapsd%: The calculation formula is: Parse CPU to Parse elapsd%=-100* (Parse time Cpu/parse time elapsed). That is: parsing the actual running time/(parsing the actual

Run time + parse in wait resource time). Here is 11.4%, very low, which takes about 8.77 seconds of wall clock time to parse each CPU second, which means that it takes a lot of time to wait for a resource. If the

The ratio is 100%, which means that the CPU time equals the elapsed time, without any waiting.

% Non-parse CPU: The calculation formula is:% Non-parse CPU =round (100*1-PARSE_CPU/TOT_CPU), 2). Too low indicates that parsing consumes too much time. Compared with PARSE_CPU, if the tot_cpu is high, this

The ratio will be close to 100%, which is good, stating that most of the work that the computer performs is the work of executing the query, not the work of analyzing the query.

4.Shared Pool related statistical data

Shared Pool Statistics Begin End

------ ------

Memory Usage%: 60.45 62.42

% SQL with executions>1:81.38 78.64

% Memory for SQL w/exec>1:70.36 68.02

Memory Usage%: Percentage of shared pools in use. This number should be stable for a long time in 75%~90%. If this percentage is too low, the memory is wasted. If this percentage is too high, the group outside the shared pool

Aging, if the SQL statement is executed again, this will cause the SQL statement to be hard parsed. In a properly sized system, the utilization rate of the shared pool will be within 75% to a slightly lower 90%.

% SQL with executions>1: This is the metric of how many execution times in a shared pool is greater than the first SQL statement. This number must be carefully considered in a system that tends to circulate. In this circulatory system

, a different set of SQL statements is executed in part of the day relative to another part of the time. In a shared pool, there will be a set of SQL statements that have not been executed during the observation, simply because the statement to execute them

Not run during the observation period. This number is close to 100% only if the system runs the same group of SQL statements continuously. This shows that almost 80% of the SQL statements in this shared pool run more than 18 minutes in the observation window