Specific implications of the indicators in Oracle Statspack reports

Data Buffer Hit ratio#< #90 #

The hit rate of the data block in the data buffer, usually should be above 90%, otherwise consider increase db_block_buffers (9i above but db_cache_size)

Buffer nowait ratio#< #99 #

The wait rate in the buffer to get buffer

Library Hit ratio#< #98 #

The main representation of SQL in the share hit rate, usually more than 98%

In Memory Sort ratio#< #0 #

If it is too low to indicate that a large amount of sorting is done in a temporary tablespace, try adding sort_area_size

Redo nowait ratio#< #98 #

Write log not waiting ratio, too low adjustable log_buffer (Increase) and _log_io_size (decrease, default to 1/3*log_buffer/log_block_size, make _log_io_size for appropriate values, such as 128k/log_ Block_size)

Soft Parse ratio#< #90 #

Approximate as SQL in the share hit ratio, usually high represents the use of binding variables, too low to adjust the application using binding variables, or reference cursor_sharing = Force (added similar in 9i)

Latch Hit ratio#< #99 #

Internal structure maintenance lock hit rate, higher than 99%, usually low because shared_pool_size is too large and does not use binding variables to cause too much hard parsing, can refer to _spin_count parameter settings

Percent non-parse cpu#< #95 #

Query actual run time/(query actual run time +sql resolution Time), too low to indicate that the resolution consumption time is too long

Percent Parse CPU to Parse elapsed#< #90 #

Parse the actual time used/(parse the actual time used + parsing wait resource time), the higher the better

Execute to Parse percent#< #10 #

The higher the value, the more times it is repeated after parsing, and if it is too low to consider setting

Session_cached_cursors > 0

Memory Usage percent#< #75 #

Shared pool usage, should be stable between 75%--90%, too small waste of memory, too large to show insufficient memory

Percent of Sqls with execution>1#< #40 #

Ratio of SQL with execution times greater than 1 (if it is too small to be using a bound variable)

Percent of Memory for SQl with execution>1#< #0 #

SQL consuming memory with execution times greater than 1/(all SQL consuming memory)

Instance Load profile Redo size/sec#> #100000 #

The log size (in bytes) generated per second, which can flag the heavy workload of the database task

Redo size/tx#> #0 #

Average number of log builds per transaction

Logical reads/sec (logical reading) #> #0 #

Average logical reads per second, unit is block

Logical reads/tx#> #0 #

Average per transaction generated logical read, Unit is block

Block Changes/sec#> #100 #

Number of block changes per second, number of blocks changed for database transactions

Block Changes/tx#> #0 #

Average number of blocks per transaction-induced change

Physical Reads/sec#> #100 #

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

Physical Reads/tx#> #0 #

Average number of blocks per transaction read from disk

Physical Write/sec#> #50 #

The average number of disk writes per second

Physical Write/tx#> #0 #

Average number of blocks per transaction write disk

User calls/sec#> #0 #

User call times per second

User calls/tx#> #0 #

Call times per transaction user

Parses/sec#> #100 #

The number of parse times per second, approximately the number of executions per second of statements

Parses/tx#> #0 #

Number of resolutions generated per transaction

Hard parses/sec#> #10 #

Number of hard resolutions generated per second

Hard parses/tx#> #0 #

Number of hard resolutions generated per transaction

Sorts/sec#> #20 #

Number of sorting occurrences per second

Sorts/tx#> #5 #

Number of sorts per transaction generated

Transactions/sec#> #0 #

Number of transactions generated per second

Rows/sort#> #0 #

The number of rows involved in each sort

Percent of Block changed/read#> #0 #

The number of blocks/reads that vary, and the changed blocks need to be data from the rollback segment

Recursive call percent#> #0 #

Recursive operations as a percentage of all operations

Rollback/tx percent#> #5 #

Transaction rollback rate (large rollback cost)

Executes/sec#> #0 #

Number of executions per second

Execute/tx#> #0 #

Number of executions per transaction

Logons/sec--46:LOGONS/TX

I/o Statistics (I/O statistics) Table space i/o#> #0 #

Represents the distribution of each table space on Io, and if there is a serious imbalance, reconsider the storage planning of the object and the disk planning of the data file

DataFile i/o#> #0 #

Indicates the IO distribution of each data file, and the storage planning of the object needs to be reconsidered if unbalanced

Table I/O (table I/O) #> #0 #

For these large IO tables, consider placing them on a high speed disk and distributing them to as many disks as possible.

Top SQL up SQL with high Buffer gets#> #0 #

This kind of SQL for a large number of block read, to check whether the SQL used in the index, or whether there is a reasonable index on the table, for the full table scan can be considered recycle buffer, for frequent full table scan small table can consider keep buffer, There is also a need to pay attention to the situation is that if the index to get the data proportion of the table data ratio is too large, such as 20% (for example data), can cause the buffer gets too large

Top SQL physical reads#> #0 #

This type of SQL causes a lot of data to be obtained from the disk, possibly because the data buffer is too small, or it may be too many full table scans, it is necessary to examine whether the index is reasonable or not.

Top SQL Execution count#> #0 #

This type of SQL is something that needs to be focused on, and maybe the SQL itself does not consume a lot of time or space at once, but because frequent execution has a great impact on the system, so long as there are optimizations that are likely to be optimized for those SQL. There are other situations where some programs may use a dual table frequently to get some information (such as time calculations, etc.). As much as possible to convert this kind of SQL to the application of locally resolved functions, or there are some due to the design of the defects caused unnecessary queries, are designed to avoid these queries

Top SQL and High Shared memory#> #0 #

This kind of SQL uses a lot of memory, not necessarily executed frequently, but it may be executed by the frequent SQL involved in some data squeeze buffer, which will also cause a lot of problems, so also need to optimize from as much as possible

Top SQL with high Version count#> #20 #

The SQL that represents multiple users is literally the same, or SQL is the same but some of the parameters of the session have changed (e.g. Sort_area_size changed)

Wait events (waiting event) alter system set mts_dispatcher#> #0 #

Wait for dispatchers to start when the session decides to execute "ALTER SYSTEM SET mts_dispatchers = <string>"

BFILE Check if exists#> #0 #

Check the presence of external bfile files

BFILE Check if open#> #0 #

Check to see if the external bfile file is open

BFILE closure#> #0 #

Wait for the external bfile file to close

BFILE get length#> #0 #

To get the size of an external bfile file

BFILE Get name object#> #0 #

Get the name of the external bfile file

BFILE Get path object#> #0 #

Get the path to the external bfile file

BFILE Internal Seek#> #0 #

BFILE open#> #0 #

Waiting for external bfile to be opened

BFILE read#> #0 #

Wait for the external bfile file to finish reading

Buffer busy due to global cache#> #0 #

Buffer Busy Waits#> #0 #

The block is being read into a buffer or the buffer is being used by another session, which can often be adjusted in several ways: increasing data buffer, increasing freelist, reducing pctused, increasing the number of rollback segments, increasing initrans, and considering using LMT

Buffer deadlock#> #0 #

Deadlock caused by slow system rather than application

Buffer latch#> #0 #

Session waiting ' buffer hash chain latch '

Buffer Read Retry#> #0 #

OPS reads the buffer in the process of reading the contents of the changes and then re-read

Cache simulator heap#> #0 #

Checkpoint Completed#> #0 #