Alireza Kamrani
Additional Statistics
There are several statistics that can indicate performance problems that do not have corresponding wait events.
Redo Log Space Requests Statistic
The V$SYSSTAT statistic redo log space requests indicates how many times a server process had to wait for space in the online redo log, not for space in the redo log buffer.
Use this statistic and the wait events as an indication that you must tune checkpoints, DBWR, or archiver activity, not LGWR. Increasing the size of the log buffer does not help.
Read Consistency
Your system might spend excessive time rolling back changes to blocks in order to maintain a consistent view. Consider the following scenarios:
• If there are many small transactions and an active long-running query is running in the background on the same table where the changes are happening, then the query might need to roll back those changes often, in order to obtain a read-consistent image of the table. Compare the following V$SYSSTAT statistics to determine whether this is happening:
• consistent: changes statistic indicates the number of times a database block has rollback entries applied to perform a consistent read on the block. Workloads that produce a great deal of consistent changes can consume a great deal of resources.
• consistent gets: statistic counts the number of logical reads in consistent mode.
• If there are few very, large rollback segments, then your system could be spending a lot of time rolling back the transaction table during delayed block cleanout in order to find out exactly which system change number (SCN) a transaction was committed. When Oracle Database commits a transaction, all modified blocks are not necessarily updated with the commit SCN immediately. In this case, it is done later on demand when the block is read or updated. This is called delayed block cleanout.
The ratio of the following V$SYSSTAT statistics should be close to one:
✴️ ratio = transaction tables consistent reads - undo records applied / transaction tables consistent read rollbacks
▶️The recommended solution is to use automatic undo management.
• If there are insufficient rollback segments, then there is rollback segment (header or block) contention. Evidence of this problem is available by the following:
• Comparing the number of WAITS to the number of GETS in V$ROLLSTAT; the proportion of WAITS to GETS should be small.
• Examining V$WAITSTAT to see whether there are many WAITS for buffers of CLASS 'undo header'.
▶️The recommended solution is to use automatic undo management.
Table Fetch by Continued Row
You can detect migrated or chained rows by checking the number of table fetch continued row
statistic in V$SYSSTAT.
▶️A small number of chained rows (less than 1%) is unlikely to impact system performance. However, a large percentage of chained rows can affect performance.
▶️Chaining on rows larger than the block size is inevitable. Consider using a tablespace with a larger block size for such data.
However, for smaller rows, you can avoid chaining by using sensible space parameters and good application design.
🔶️For example, do not insert a row with key values filled in and nulls in most other columns, then update that row with the real data, causing the row to grow in size. Rather, insert rows filled with data from the start.
If an UPDATE statement increases the amount of data in a row so that the row no longer fits in its data block, then Oracle Database tries to find another block with enough free space to hold the entire row. If such a block is available, then Oracle Database moves the entire row to the new block. This operation is called row migration.
If the row is too large to fit into any available block, then the database splits the row into multiple pieces and stores each piece in a separate block.
This operation is called row chaining. The database can also chain rows when they are inserted.
Migration and chaining are especially detrimental to performance with the following:
• UPDATE statements that cause migration and chaining to perform poorly
• Queries that select migrated or chained rows because these must perform additional input and output
The definition of a sample output table named CHAINED_ROWS appears in a SQL script available on your distribution medium. The common name of this script is UTLCHN1.
SQL, although its exact name and location varies depending on your platform.
Your output table must have the same column names, data types, and sizes as the CHAINED_ROWS table.
▶️Increasing PCTFREE can help to avoid migrated rows. If you leave more free space available in the block, then the row has room to grow. You can also reorganize or re-create tables and indexes that have high deletion rates.
If tables frequently have rows deleted, then data blocks can have partially free space in them. If rows are inserted and later expanded, then the inserted rows might land in blocks with deleted rows but still not have enough room to expand. Reorganizing the table ensures that the main free space is totally empty blocks.
🔶️Note:
PCTUSED is not the opposite of PCTFREE.
Parse-Related Statistics
The more your application parses, the more potential for contention exists, and the more time your system spends waiting. If parse time CPU represents a large percentage of the CPU time, then time is being spent parsing instead of executing statements.
If this is the case, then it is likely that the application is using literal SQL and so SQL cannot be shared, or the shared pool is poorly configured.
There are several statistics available to identify the extent of time spent parsing by Oracle. Query the parse related statistics from V$SYSSTAT.
For example:
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ( 'parse time cpu', 'parse time elapsed', 'parse count (hard)', 'CPU used by this session' );
There are various ratios that can be computed to assist in determining whether parsing may be a problem:
• parse time CPU / parse time elapsed
This ratio indicates how much of the time spent parsing was due to the parse operation itself, rather than waiting for resources, such as latches. A ratio of one is good, indicating that the elapsed time was not spent waiting for highly contended resources.
• parse time CPU / CPU used by this session
This ratio indicates how much of the total CPU used by Oracle server processes was spent on parse-related operations. A ratio closer to zero is good, indicating that the majority of CPU is not spent on parsing.
Wait Events Statistics
The V$SESSION, V$SESSION_WAIT, V$SESSION_HISTORY, V$SESSION_EVENT, and V$SYSTEM_EVENT
views provide information on what resources were waited for, and, if the configuration parameter TIMED_STATISTICS is set to true, how long each resource was waited for.
Investigate wait events and related timing data when performing reactive performance tuning. The events with the most time listed against them are often strong indications of the performance bottleneck.
The following views contain related, but different, views of the same data:
• V$SESSION lists session information for each current session. It lists either the event currently being waited for, or the event last waited for on each session. This view also contains information about blocking sessions, the wait state, and the wait time.
• V$SESSION_WAIT is a current state view. It lists either the event currently being waited for, or the event last waited for on each session, the wait state, and the wait time.
• V$SESSION_WAIT_HISTORY lists the last 10 wait events for each current session and the associated wait time.
• V$SESSION_EVENT lists the cumulative history of events waited for on each session. After a session exits, the wait event statistics for that session are removed from this view.
• V$SYSTEM_EVENT lists the events and times waited for by the whole instance (that is, all session wait events data rolled up) since instance startup.
Because V$SESSION_WAIT is a current state view, it also contains a finer-granularity of information than V$SESSION_EVENT or V$SYSTEM_EVENT. It includes additional identifying data for the current event in three parameter columns: P1, P2, and P3.
For example, V$SESSION_EVENT can show that session 124 (SID=124) had many waits on the db file scattered read, but it does not show which file and block number.
However, V$SESSION_WAIT shows the file number in P1, the block number read in P2, and the number of blocks read in P3 (P1 and P2 let you determine for which segments the wait event is occurring).
This section concentrates on examples using V$SESSION_WAIT.
▶️However, Oracle recommends capturing performance data over an interval and keeping this data for performance and capacity analysis.
This form of rollup data is queried from the V$SYSTEM_EVENT view by AWR.
Most commonly encountered events are described in this chapter, listed in case-sensitive alphabetical order. Other event-related data to examine is also included. The case used for each event name is that which appears in the V$SYSTEM_EVENT view.
Changes to Wait Event Statistics from Past Releases
Starting with Oracle Database 11g, Oracle Database accumulates wait counts and time outs for wait events (such as in the V$SYSTEM_EVENT view) differently than in past releases.
Continuous waits for certain types of resources (such as enqueues) are internally divided into a set of shorter wait calls. In releases prior to Oracle Database 11g, each individual internal wait call was counted as a separate wait. Starting with Oracle Database 11g, a single resource wait is recorded as a single wait, irrespective of the number of internal time outs experienced by the session during the wait.
This change allows Oracle Database to display a more representative wait count, and an accurate total time spent waiting for the resource.
▶️Time outs now refer to the resource wait, instead of the individual internal wait calls. This change also affects the average wait time and the maximum wait time.
For example, if a user session must wait for an enqueue in order for a transaction row lock to update a single row in a table, and it takes 10 seconds to acquire the enqueue, Oracle Database breaks down the enqueue wait into 3-second wait calls.
In this example, there will be three 3-second wait calls, followed by a 1-second wait call.
From the session's perspective, however, there is only one wait on an enqueue.
In releases prior to Oracle Database 11g, the V$SYSTEM_EVENT view would represent this wait scenario as follows:
• TOTAL_WAITS: 4 waits (three 3-second waits, one 1-second wait)
• TOTAL_TIMEOUTS: 3 time outs (the first three waits time out and the enqueue is acquired during the final wait)
• TIME_WAITED: 10 seconds (sum of the times from the 4 waits)
• AVERAGE_WAIT: 2.5 seconds
• MAX_WAIT: 3 seconds
Starting with Oracle Database 11g, this wait scenario is represented as:
• TOTAL_WAITS: 1 wait (one 10-second wait)
• TOTAL_TIMEOUTS: 0 time outs (the enqueue is acquired during the resource wait)
• TIME_WAITED: 10 seconds (time for the resource wait)
• AVERAGE_WAIT: 10 seconds
• MAX_WAIT: 10 seconds
The following common wait events are affected by this change:
• Enqueue waits (such as enq: name - reason waits)
• Library cache lock waits
• Library cache pin waits
• Row cache lock waits
The following statistics are affected by this change:
• Wait counts
• Wait time outs
• Average wait time
• Maximum wait time
The following views are affected by this change:
• V$EVENT_HISTOGRAM
• V$EVENTMETRIC
• V$SERVICE_EVENT
• V$SERVICE_WAIT_CLASS
• V$SESSION_EVENT
• V$SESSION_WAIT
• V$SESSION_WAIT_CLASS
• V$SESSION_WAIT_HISTORY
• V$SYSTEM_EVENT
• V$SYSTEM_WAIT_CLASS
• V$WAITCLASSMETRIC
• V$WAITCLASSMETRIC_HISTORY
Oracle Enqueue Names
This appendix lists Oracle enqueues. Enqueues are shared memory structures (locks) that serialize access to database resources.
They can be associated with a session or transaction.
Enqueue names are displayed in the LOCK_TYPE
column of the DBA_LOCK and DBA_LOCK_INTERNAL data dictionary views.
A resource uniquely identifies an object that can be locked by different sessions within an instance (local resource) or between instances (global resource). Each session that tries to lock the resource will have an enqueue on the resource.
Note:
The names of enqueues and their definitions may change from release to release.
The oracle user enqueues are:
The action to take depends on the lock type which is causing the most problems. The most common lock waits are generally for:
• TX: Transaction Lock -- Generally due to application or table setup issues, for example row level locking conflicts and ITL allocation
• TM: DML enqueue -- Generally due to application issues, particularly if foreign key constraints have not been indexed.
• ST: Space management enqueue -- Usually caused by too much space management occurring (for example, small extent sizes, lots of sorting, and so on)
• HW: High Water Mark -- Concurrent users trying to extend a segment's high-water mark for space allocated.
To determine which enqueues are causing the most waits systemwide, examine the V$ENQUEUE_STAT view thus:
SELECT eq_type "Lock", total_req# "Gets", total_wait# "Waits", cum_wait_time FROM V$enqueue_stat WHERE Total_wait# > 0 ;
This gives the systemwide number of waits for each lock type.
You can also examine:
• Sessions with high numbers of "enqueue waits" in the V$SESSTAT view
• Sampling of the V$LOCK view to find waiting / blocking sessions
The Oracle system enqueues are:
• BL, Buffer Cache Management
• BR, Backup/Restore
• CF, Controlfile Transaction
• CI, Cross-instance Call Invocation
• CU, Bind Enqueue
• DF, Datafile
• DL, Direct Loader Index Creation
• DM, Database Mount
• DR, Distributed Recovery Process
• DW, SecureFiles
• DX, Distributed Transaction
• FP, File Object
• FS, File Set
• IN, Instance Number
• IR, Instance Recovery
• IS, Instance State
• IV, Library Cache Invalidation
• JI, Enqueue used during AJV snapshot refresh
• JQ, Job Queue
• KK, Redo Log "Kick"
• KP, contention in Oracle Data Pump startup and shutdown processes
• KO, Multiple Object Checkpoint
• L[A-P], Library Cache Lock
• LS, Log Start or Switch
• MM, Mount Definition
• MR, Media Recovery
• N[A-Z], Library Cache Pin
• PE, ALTER SYSTEM SET PARAMETER = VALUE
• PF, Password File
• PI, Parallel Slaves
• PR, Process Startup
• PS, Parallel Slave Synchronization
• Q[A-Z], Row Cache
• RO, Object Reuse
• RT, Redo Thread
• RW, Row Wait
• SC, System Change Number
• SM, SMON
• SN, Sequence Number
• SQ, Sequence Number Enqueue
• SR, Synchronized Replication
• SS, Sort Segment
• SV, Sequence Number Value
• TA, Transaction Recovery
• TC, Thread Checkpoint
• TE, Extend Table
• TO, Temporary Table Object Enqueue
• TS, Temporary Segment (also TableSpace)
• TT, Tablespace
• UL, User-defined Locks
• UN, User Name
• US, Undo Segment, Serialization
• WL, Being Written Redo Log
• XA, Instance Attribute Lock
• XI, Instance Registration Lock
• ZA, Exclusive Lock When Moving Audit Table
Statistics Descriptions
This section describes some of the statistics stored in the V$SESSTAT and V$SYSSTAT views.
The statistics are listed in alphabetical order.
The CLASS column contains a number representing one or more statistics classes. The following class numbers are additive:
• 1, User
• 2, Redo
• 4, Enqueue
• 8, Cache
• 16, OS
• 32, Real Application Clusters
• 64, SQL
• 128, Debug
For example, a class value of 72 represents a statistic that relates to SQL statements and caching.
Some statistics are populated only if the TIMED_STATISTICS initialization parameter is set to true.
Examples of Database Statistics Descriptions:
To see another's Oracle statistics you can visit :
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/statistics-descriptions-2.html
No comments:
Post a Comment