DB file scattered read wait event:we often meet with the db file scattered read wait event, which may be more common in a production environment. This event indicates that the user process is reading the data into the buffer cache, waiting until the I/O call returns. The db file scattered read emits discrete reads that will store contiguous chunks of data in discrete reads into multiple disjoint memory locations. Scattered read is typically multi-block, and is used in access modes such as full Table scan or fast full scan. Scattered read represents full scan, when a full scan is performed to read data to the buffer cache, the usual continuous data is not contiguous in memory, so this wait is named scattered read (discrete read). The number of data blocks per block read Read is limited by the initialization parameter db_file_multiblock_read_count. The data read method of scattered read is briefly described. As you can see from the V$event_name view, the wait has 3 parameters, representing the file number, the starting block number, and the number of data blocks:[email protected]> Select Event#,name,parameter1, Parameter2,parameter3 2 from v$event_name 3 where name = ' db file scattered Read '; event# NAME &N Bsp PARAMETER1 PARAMETER2 PARAMETER3-------------------------------------------------------------- -------------- db file scattered read file# &NBSP ; block# Blocks data file number, starting data number, and number of data blocks, this information allows you to know the object files that the Oracle session is waiting for. This wait may be related to continuous reads of full table scan or fast full index Scan (index fast fully scan), and, according to experience, a large number of db file scattered read waits can mean application problems or missing indexes. During the diagnosis of the real environment, we can find the waiting of the session through the V$session_wait view, and then combine the other views to find the root cause of the problem SQL, and solve the problem fundamentally. When this wait event is significant, the user can also diagnose it in conjunction with the V$session_longops dynamic performance view, which records transactions that are running for a long time (longer than 6 seconds), many of which are full table scans (anyway, this part of the information is worth our attention). From OraclBeginning with the E 9i, Oracle added a new view V$sql_plan to record the execution plan of the SQL statements in the current system library cache, which can be used to find the SQL statements that are problematic. Can process V$sql_plan and V$sqltext union, get these query SQL statement, look for full table scan SQL statement can refer to the following statement: Select Sql_text from v$sqltext T,v$sql_plan p where T.hash_value = P.hash_value and p.operation = ' TABLE ACCESS ' and p.options = ' Full ' order by p.hash_value,t.piece; Find the SQL statement for fast Full index scan can refer to the following statement: Select Sql_text from v$sqltext T, v$ Sql_plan p where t.hash_value = P.hash_value and p.operation = ' INDEX ' and p.options = ' Full SCAN ' &N Bsp;order by P.hash_value, t.piece; This information is very instructive for discovering database problems and optimizing database performance. In Oracle 10g, Oracle classifies wait events, and the db file scattered read event is categorized as user I/O: select Name,parameter1 p1,parameter2 p2,parameter3 P3,wait_class_id,wait_class#,wait_class from V$event_name where name = ' db file Scattere D read '; NAME  P1 P2 P3 wait_class_id wait_class# wait_class-------------------------------------------- -----------------------------------------------db file scattered read file# block# &N Bsp Blocks 1740759767 8 User I/O completes categorization of wait events, Oracle 10g a DDM can easily locate the problem by fault analysis, help users to quickly discover the bottleneck of the database and the source of the bottleneck, which is the design idea of Oracle's ADDM expert system. The Oracle Expert Diagnostic system is visually and clearly visible in conjunction with this wait model and ADDM.
Ext.: http://www.360doc.com/content/16/0906/20/36335693_588898007.shtml
DB file scattered Read wait event
