Tuning Database (-) 摘自官档 Oracle版权所有

  • yewushang

    2014-02-19 21:22:11
  • Oracle
  • 原创

点击(此处)折叠或打开

  1. Dynamic Performance Views Containing Wait Event Statistics
  3. These dynamic performance views can be queried for wait event statistics:
  5. V$ACTIVE_SESSION_HISTORY
  7. The V$ACTIVE_SESSION_HISTORY view displays active database session activity, sampled once every second. See \"Active Session History (ASH)\".
  9. V$SESS_TIME_MODEL and V$SYS_TIME_MODEL
  11. The V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views contain time model statistics, including DB time which is the total time spent in database calls
  13. V$SESSION_WAIT
  15. The V$SESSION_WAIT view displays the resources or events for which active sessions are waiting.
  17. V$SESSION
  19. The V$SESSION view contains the same wait statistics that are contained in the V$SESSION_WAIT view. If applicable, this view also contains detailed information on the object that the session is currently waiting for (object number, block number, file number, and row number), plus the blocking session responsible for the current wait.
  21. V$SESSION_EVENT
  23. The V$SESSION_EVENT view provides summary of all the events the session has waited for since it started.
  25. V$SESSION_WAIT_CLASS
  27. The V$SESSION_WAIT_CLASS view provides the number of waits and the time spent in each class of wait events for each session.
  29. V$SESSION_WAIT_HISTORY
  31. The V$SESSION_WAIT_HISTORY view displays the last ten wait events for each active session.
  33. V$SYSTEM_EVENT
  35. The V$SYSTEM_EVENT view provides a summary of all the event waits on the instance since it started.
  37. V$EVENT_HISTOGRAM
  39. The V$EVENT_HISTOGRAM view displays a histogram of the number of waits, the maximum wait, and total wait time on an event basis.
  41. V$FILE_HISTOGRAM
  43. The V$FILE_HISTOGRAM view displays a histogram of times waited during single block reads for each file.
  45. V$SYSTEM_WAIT_CLASS
  47. The V$SYSTEM_WAIT_CLASS view provides the instance wide time totals for the number of waits and the time spent in each class of wait events.
  49. V$TEMP_HISTOGRAM
  51. The V$TEMP_HISTOGRAM view displays a histogram of times waited during single block reads for each temporary file.

点击(此处)折叠或打开

  1. System Statistics
  3. System statistics are typically used in conjunction with wait event data to find further evidence of the cause of a performance problem.
  5. For example, if V$SYSTEM_EVENT indicates that the largest wait event (in terms of wait time) is the event buffer busy waits, then look at the specific buffer wait statistics available in the view V$WAITSTAT to see which block type has the highest wait count and the highest wait time.
  7. After the block type has been identified, also look at V$SESSION real-time while the problem is occurring or V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY views after the problem has been experienced to identify the contended-for objects using the object number indicated. The combination of this data indicates the appropriate corrective action.
  9. Statistics are available in many V$ views. Some common views include the following:
  11. 10.1.3.4.1 V$ACTIVE_SESSION_HISTORY
  12. This view displays active database session activity, sampled once every second. See \"Active Session History (ASH)\".
  14. 10.1.3.4.2 V$SYSSTAT
  15. This contains overall statistics for many different parts of Oracle, including rollback, logical and physical I/O, and parse data. Data from V$SYSSTAT is used to compute ratios, such as the buffer cache hit ratio.
  17. 10.1.3.4.3 V$FILESTAT
  18. This contains detailed file I/O statistics for each file, including the number of I/Os for each file and the average read time.
  20. 10.1.3.4.4 V$ROLLSTAT
  21. This contains detailed rollback and undo segment statistics for each segment.
  23. 10.1.3.4.5 V$ENQUEUE_STAT
  24. This contains detailed enqueue statistics for each enqueue, including the number of times an enqueue was requested and the number of times an enqueue was waited for, and the wait time.
  26. 10.1.3.4.6 V$LATCH
  27. This contains detailed latch usage statistics for each latch, including the number of times each latch was requested and the number of times the latch was waited for.


点击(此处)折叠或打开

  1. Segment-Level Statistics
  3. You can gather segment-level statistics to help you spot performance problems associated with individual segments. Collecting and viewing segment-level statistics is a good way to effectively identify hot tables or indexes in an instance.
  5. After viewing wait events and system statistics to identify the performance problem, you can use segment-level statistics to find specific tables or indexes that are causing the problem. Consider, for example, that V$SYSTEM_EVENT indicates that buffer busy waits cause a fair amount of wait time. You can select from V$SEGMENT_STATISTICS the top segments that cause the buffer busy waits. Then you can focus your effort on eliminating the problem in those segments.
  7. You can query segment-level statistics through the following dynamic performance views:
  9. V$SEGSTAT_NAME This view lists the segment statistics being collected, as well as the properties of each statistic (for instance, if it is a sampled statistic).
  11. V$SEGSTAT This is a highly efficient, real-time monitoring view that shows the statistic value, statistic name, and other basic information.
  13. V$SEGMENT_STATISTICS This is a user-friendly view of statistic values. In addition to all the columns of V$SEGSTAT, it has information about such things as the segment owner and table space name. It makes the statistics easy to understand, but it is more costly.


点击(此处)折叠或打开

  1. Segment-Level Statistics
  3. You can gather segment-level statistics to help you spot performance problems associated with individual segments. Collecting and viewing segment-level statistics is a good way to effectively identify hot tables or indexes in an instance.
  5. After viewing wait events and system statistics to identify the performance problem, you can use segment-level statistics to find specific tables or indexes that are causing the problem. Consider, for example, that V$SYSTEM_EVENT indicates that buffer busy waits cause a fair amount of wait time. You can select from V$SEGMENT_STATISTICS the top segments that cause the buffer busy waits. Then you can focus your effort on eliminating the problem in those segments.
  7. You can query segment-level statistics through the following dynamic performance views:
  9. V$SEGSTAT_NAME This view lists the segment statistics being collected, as well as the properties of each statistic (for instance, if it is a sampled statistic).
  11. V$SEGSTAT This is a highly efficient, real-time monitoring view that shows the statistic value, statistic name, and other basic information.
  13. V$SEGMENT_STATISTICS This is a user-friendly view of statistic values. In addition to all the columns of V$SEGSTAT, it has information about such things as the segment owner and table space name. It makes the statistics easy to understand, but it is more costly.


点击(此处)折叠或打开

  1. 10.2.4 Additional Statistics
  3. There are a number of statistics that can indicate performance problems that do not have corresponding wait events.
  5. 10.2.4.1 Redo Log Space Requests Statistic
  7. 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. A significant value for this statistic and the wait events should be used as an indication that checkpoints, DBWR, or archiver activity should be tuned, not LGWR. Increasing the size of log buffer does not help.
  9. 10.2.4.2 Read Consistency
  11. Your system might spend excessive time rolling back changes to blocks in order to maintain a consistent view. Consider the following scenarios:
  13. 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:
  15. 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.
  17. consistent gets statistic counts the number of logical reads in consistent mode.
  19. 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 SCN a transaction was committed. When Oracle 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.
  21. The ratio of the following V$SYSSTAT statistics should be close to 1:
  23. ratio = transaction tables consistent reads - undo records applied /
  24. transaction tables consistent read rollbacks
  25. The recommended solution is to use automatic undo management.
  27. If there are insufficient rollback segments, then there is rollback segment (header or block) contention. Evidence of this problem is available by the following:
  29. Comparing the number of WAITS to the number of GETS in V$ROLLSTAT; the proportion of WAITS to GETS should be small.
  31. Examining V$WAITSTAT to see whether there are many WAITS for buffers of CLASS \'undo header\'.
  33. The recommended solution is to use automatic undo management.
  35. 10.2.4.3 Table Fetch by Continued Row
  37. 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.
  39. Chaining on rows larger than the block size is inevitable. You might want to consider using tablespace with larger block size for such data.
  41. 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.
  43. 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 tries to find another block with enough free space to hold the entire row. If such a block is available, then Oracle moves the entire row to the new block. This is called migrating a row. If the row is too large to fit into any available block, then Oracle splits the row into multiple pieces and stores each piece in a separate block. This is called chaining a row. Rows can also be chained when they are inserted.
  45. Migration and chaining are especially detrimental to performance with the following:
  47. UPDATE statements that cause migration and chaining to perform poorly
  49. Queries that select migrated or chained rows because these must perform additional input and output
  51. 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, datatypes, and sizes as the CHAINED_ROWS table.
  53. 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.


点击(此处)折叠或打开

  1. There are a number of statistics available to identify the extent of time spent parsing by Oracle. Query the parse related statistics from V$SYSSTAT. For example:
  3. SELECT NAME, VALUE
  4. FROM V$SYSSTAT
  5. WHERE NAME IN ( \'parse time cpu\', \'parse time elapsed\',
  6. \'parse count (hard)\', \'CPU used by this session\' );
  7. There are various ratios that can be computed to assist in determining whether parsing may be a problem:
  9. parse time CPU / parse time elapsed
  11. 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.
  13. parse time CPU / CPU used by this session
  15. 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 Event
请使用浏览器的分享功能分享到微信等