本文共 2306 字,大约阅读时间需要 7 分钟。
db file sequential read and db file scattered read
[@more@]db file sequential read and db file scattered read
The db file sequential read and db file scattered read are I/O related wait events.
1. Determine the object name and partition name (if applicable) using the P1 and P2 values supplied by the CURRENT_EVENT cursor:
Querying the DBA_EXTENTS view in this manner can be slow, especially when many sessions are waiting on db file sequential read and db file scattered read events. This is because DBA_EXTENTS is a complex view that is comprised of many views and base tables. The performance gets worse when the number of extents in the database is high. The following are two alternatives that improve performance.
Precreate a working table (regular heap or global temporary table) with the same structure as the DBA_EXTENTS view. Then take a snapshot of the DBA_EXTENTS view once at the beginning of each sampling interval (or once a day depending on the number of extents and their volatility) and insert the data into the working table. In this case, the preceding query can be rewritten to go against the working table instead of the DBA_EXTENTS view.
Obtain the object number (OBJ) from the X$BH view using the P1 and P2 from the CURRENT_EVENT cursor. With the object number, you can resolve the object name (NAME) and subobject name (SUBNAME) by querying the DBA_OBJECTS view. An example of the query follows. The caveat to this method is that you must wait for the block to be read into the SGA; otherwise, the X$BH view has no information on the block that is referenced by the P1 and P2. Also, you may not be quick enough to catch the blocks that are being read in by a full table scan operation. This is because full table scans against NOCACHE objects do not flood the buffer cache. A small number of blocks are quickly reused. By the time you get the P1 and P2 values from the V$SESSION_WAIT view, the block in X$BH could have been reused by another block from subsequent reads.
2. Obtain the SQL statement that is associated with the db file sequential read or db file scattered read event using the hash value supplied by the CURRENT_EVENT cursor:
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/178357/viewspace-896947/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/178357/viewspace-896947/
转载地址:https://blog.csdn.net/cizhuima2286/article/details/100411591 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!