Description of statspack

Instance efficiency percentages

Data Buffer hit ratio #<#90 # hit rate of data blocks in the data buffer, which should be above 90%. Otherwise, increase db_block_buffers (db_cache_size for 9i or above)
Buffer Nowait ratio #<#99 # obtain the unwaited ratio of the buffer in the buffer zone
Library hit ratio #<#98 # mainly represents the SQL hit rate in the sharing area, usually more than 98%
In memory sort ratio #<#0 # if it is too low, a large number of sorting operations are performed in the temporary tablespace. You can try to increase sort_area_size.
Redo Nowait ratio #<#98 # Do not wait ratio for log writing. If it is too low, you can adjust the log_buffer (Increase) and _ log_io_size (decrease. The default value is 1/3 * log_buffer/log_block_size, make _ log_io_size an appropriate value, such as 128 K/log_block_size)
Soft parse ratio #<#90 # It is similar to the hit rate of SQL in the shared area. The high value usually indicates that the bound variable is used. If it is too low, you need to adjust the application to use the bound variable, or refer to cursor_sharing = force (similar added in 9i)
Latch hit ratio #<#99 # Internal Structure Maintenance lock hit rate, higher than 99%, usually because the shared_pool_size is too large and hard Parsing is too large because no bound variable is used. For details, refer to _ spin_count parameter settings.
Percent non-Parse CPU #<#95 # query the actual running time/(query the actual running time + SQL parsing time). Too low indicates that the resolution consumption time is too long.
Percent parse CPU to parse elapsed #<#90 # actual time used for parsing/(actual time used for parsing + resource waiting time in parsing), the higher the better
Execute to parse percent #<#10 # The higher the value, the higher the number of repeated executions after a resolution. If the value is too low, you can set session_cached_cursors> 0.
Memory usage percent #<#75 # usage of the shared pool, which should be stable between 75% and 90%. If it is too small to waste memory, the memory is insufficient.
Percent of sqls with execution> 1 #<#40 # ratio of SQL statements with execution times greater than 1 (if it is too small, it is possible that no bound variable is used)
Percent of memory for SQL with execution> 1 #<#0 # memory consumption for SQL statements with execution times greater than 1/(memory consumption for all SQL statements)

Instance load Profile

Redo size/sec #># 100000 # the size of logs generated per second (in bytes), which indicates whether the database task is heavy or not
Redo size/TX #> #0 # average log generation for each transaction
Logical reads/sec (logical read) #> #0 # average logical read per second, in block
Logical reads/TX #># 0 # average logical reads generated by each transaction, in the unit of Block
Block changes/sec #># 100 # Number of block changes per second, the number of blocks that database transactions change
Block changes/TX #> #0 # average number of changed blocks caused by each transaction
Physical reads/sec #># 100 # average number of blocks read from the disk by the database per second
Physical reads/TX #># 0 # average number of blocks read by each transaction from the disk
Physical write/sec #># 50 # average number of blocks written to the disk per second
Physical write/TX #> #0 # average number of blocks written to the disk by each transaction
User CILS/sec #> #0 # Number of user calls per second
User CILS/TX #> #0 # Number of user calls per transaction
Parses/sec #> #100 # number of resolutions per second, reflecting the number of statements executed per second
Parses/TX #> #0 # number of resolutions per transaction
Hard parses/sec #># 10 # Number of hard resolutions generated per second
Hard parses/TX #> #0 # Number of hard resolutions per transaction
Sorts/sec #> #20 # Number of sorting times generated per second
Sorts/TX #> #5 # Number of sorting times generated by each transaction
Transactions/sec #> #0 # number of transactions generated per second
Rows/sort #># 0 # number of rows involved in each sort
Percent of block changed/read #># 0 # Number of changed blocks/number of reads. The changed blocks must be retrieved from the rollback segment.
Recursive call percent #># 0 # ratio of recursive operations to all operations
Rollback/TX percent #># 5 # transaction rollback rate (large rollback sales)
Executes/sec #> #0 # number of executions per second
Execute/TX #> #0 # number of executions of each transaction
-- 45: logons/sec
-- 46: logons/TX

I/O statistics (I/O statistics)

Table space I/O #># 0 # indicates the IO distribution of each tablespace. If serious imbalance occurs, you need to reconsider the Storage Planning of objects and the disk planning of data files in the tablespace.
Datafile I/O #># 0 # indicates the IO distribution of each data file. If the data file is not balanced, you need to re-consider the storage plan of the object.
Table I/O (Table I/O) #> #0 # For tables with large I/O sizes, consider placing them on high-speed disks and distributing them on different disks as much as possible.

Top SQL

Top SQL with high buffer gets #># 0 # This type of SQL reads a large number of blocks. Check whether indexes are used for this SQL statement, or whether there are reasonable indexes on the table, recycle buffer can be considered for large tables that must be fully scanned. For small tables that frequently perform full table scan, keep buffer can be considered, note that if the proportion of data in the table obtained through the index is too large, such as 20% (for example, data), the buffer gets is too large.
Top SQL with high physical reads #># 0 # This type of SQL causes a large amount of data to be obtained from the disk. It may be because the data buffer is too small or too many full table scans, check whether the index is reasonable and whether the index is used
Top SQL with high execution count #># 0 # This type of SQL requires special attention. Maybe one execution of these SQL statements does not consume a large amount of time or space, however, frequent execution has a great impact on the system, so as long as there is a possibility that these SQL statements will be optimized. In other cases, some programs may frequently use dual tables to obtain some information (such as time calculation ), try to convert such SQL statements into functions that can be solved locally by the application, or avoid unnecessary queries due to design defects.
Top SQL with high shared memory #># 0 # This type of SQL uses a large amount of memory and may not be executed frequently, however, it may squeeze some data involved in frequently executed SQL statements out of the buffer zone, which will also lead to many problems, so we also need to optimize it as much as possible.
Top SQL with High Version count #># 20 # indicates that the SQL statements of multiple users are literally the same, or the SQL statement is the same, but some session parameters have changed (for example, sort_area_size has changed)

Wait events (wait for 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 whether the external bfile exists
Bfile check if open #># 0 # Check whether the external bfile file is open
Bfile closure #># 0 # Wait to close the external bfile
Bfile get length #> #0 # obtain the size of an external bfile
Bfile get name object #># 0 # obtain the name of the external bfile File
Bfile get path object #># 0 # obtain the path of the external bfile File
Bfile internal seek #> #0 #
Bfile open #># 0 # Wait until the external bfile is opened
Bfile read #># 0 # Wait until the external bfile file is read
Buffer busy due to global cache #># 0 #
Buffer busy waits #># 0 # The block is being read into the buffer or the buffer is being used by another session. In this case, it may be adjusted in several ways: Increase data buffer, increase freelist, and reduce pctused, increase the number of rollback segments and initrans, and use 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 # When the buffer is read under ops, the content changes and reads the buffer again.
Cache simulator heap #> #0 #
Checkpoint completed #> #0 # Wait for the completion of the checkpoint. The cause of this problem is that the IO problem is serious. You can adjust the checkpoint-related parameters log_checkpoint_interval, log_checkpoint_timeout, db_block_max_dirty_target, it can indirectly increase the size of log files and increase the number of log files.
Contacting SCN server or SCN lock master #># 0 #
Control File parallel write #># 0 # Wait until all the control files are written. The control files can be dispersed on different disks.
Control File sequential read #># 0 # Read control file, which is generated when the control file and ops are backed up
Control file single write #># 0 # ops allows only one session to write shared information to the disk at the same time.
Conversion File Read #> #0 #
DB file parallel read #># 0 # restore Data in parallel from the data file
DB file parallel write #># 0 # When multiple Io can occur simultaneously (multiple disks), dbwr can be written in parallel, and dbwr waits for the completion of the last Io
DB file scattered read #># 0 # Blocks Obtained at a time are dispersed in the buffer's discontinuous space. This usually indicates that the entire table has been scanned too much and can be checked to check whether the application has properly used the index, is the database properly indexed?
DB file sequential read #># 0 # usually implies that the amount of data obtained through the index is relatively large (for example, the percentage of table data obtained by range scanning through the index is too large ), during Multi-table join, the connection sequence is incorrect. During hash join, hash_area_size cannot accommodate hash table.
DB file single write #># 0 # Wait until the data file header is updated
Debugger command #> #0 #
DFS dB file lock #> #0 # each instance under ops has a shared global lock on the data file. When the offline data file is to be synchronized from other instances
DFS Lock handle #> #0 # session waits for a global lock request
Direct path read #> #0 # usually occurs in temporary tablespace sorting and parallel query.
Direct path read (LOB) #> #0 #
Direct path write #> #0 # Sort data (sqlldr, CTAs), PDML, and temporary tablespace in direct mode
Direct path write (LOB) #> #0 #
Dispatcher listen timer #># 0 #
Dispatcher shutdown #> #0 #
Dispatcher timer #># 0 #
DLM generic wait event #># 0 #
Dupl. Cluster key #> #0 #
Enqueue #> #0 # requirements for obtaining shared resources a queue (FIFO) mechanism to protect shared resources, st enqueue indicates that the LMT tablespace is used to avoid problems caused by space allocation or release. TX enqueue is mainly caused by unique index duplication, frequent updates of Bitmap indexes, initrans being too small, or pctfree being too small.
File identify #> #0 #
File Open #> #0 #
Free buffer waits #> #0 # Wait for available buffer in the buffer. The data buffer may be too small, the checkpoint interval may be too long, or the DML may be frequent, and Io may become the bottleneck.
Free global transaction table entry #># 0 # session waits for a global transaction slot in the distributed database
Free process state object #> #0 #
Global cache BG acks #> #0 #
Global cache Cr request #> #0 #
Global cache freelist wait #># 0 #
Global cache lock busy #># 0 # session waits to convert a buffer from the current shared status to the current exclusive status
Global cache lock cleanup #> #0 #
Global cache lock null to s #> #0 #
Global cache lock null to X #> #0 #
Global cache lock open s #> #0 #
Global cache lock open X #> #0 #
Global cache Lock S to X #> #0 #
Global cache multiple locks #># 0 #
Global cache pending ast #> #0 #
Global cache pending ASTs #># 0 #
Global cache retry prepare #># 0 #
Global cache retry request #> #0 #
Imm OP #> #0 #
Inactive session #> #0 #
Inactive transaction branch #># 0 #
Index block split #># 0 # When you search for a key in the index, if the index block is split, it will wait until the split is completed.
Io done #> #0 # The session waits for the completion of Io
Ksim gds request cancel #># 0 #
Latch activity #> #0 #
Latch free #> #0 # latch is a kind of lock for maintaining the memory. It does not use the queuing mechanism and is quickly obtained and then released, the cause is that the program does not use the binding variable, the shared_pool_size setting is too large (such as 1G), LRU competition, and some blocks are overheated (too frequent access)
Lgwr wait for redo copy #># 0 # indicates waiting for redo allocation and redo copy latches. You can add _ log_simulteneous_copies (2 * CPUs by default ), but it is also easy to introduce redo allocation latch contention, so you need to be careful
Library cache load lock #> #0 #
Library cache lock #> #0 #
Library cache pin #> #0 #
Listen endpoint status #># 0 #
Lmon wait for LMD to inherit communication channels #># 0 #
Local write wait #> #0 #
Lock manager wait for dlmd to shutdown #># 0 #
Lock manager wait for remote message #># 0 #
Log buffer space #># 0 # generate the log and wait for lgwr to write the file quickly to free up the log buffer. You can increase the log_buffer In the init parameter file and place the log file on the high-speed disk.
Log File parallel write #># 0 # When lgwr writes a log file, waiting usually leads to a log file sync event, which is placed on a high-speed disk.
Log File sequential read #># 0 #
Log File single write #> #0 #
Log File switch (archiving needed) #> #0 # When switching logs, log groups are cyclically used, but log archiving is not completed yet, which is usually due to Io serious problems, you can increase log files and add log groups, and adjust log_archive_max_processes.
Log File switch (checkpoint incomplete) #> #0 # When the log is switched, the log group uses a loop, but the checkpoint in the used log group is not completed yet, i/O is usually a serious problem. You can increase the log file and add a log group.
Log File switch (clearing Log File) #># 0 #
Log File switch completion #> #0 #
Log File sync #> #0 # When the user commit, the lgwr is notified to write logs, but lwgr is busy, the possible cause is that the commit is too frequent or the lgwr is too long to write logs at a time (probably because the size of one log Io is too large). You can adjust the _ log_io_size combined with log_buffer to make (_ log_io_size * db_block_size) * n = log_buffer, which can avoid conflicts with increasing log_buffer; place log files on high-speed disks.

Write complete waits #># 0 # The user waits for the buffer to be written into the file, implying that the write data file is waiting