Dbfilesequentialread wait event

Today is, and we will continue to complete the waiting event series. What is: dbfilesequentialread :? Simply put, oracle needs to read a single piece of data, and other sessions are waiting. There are three parameters p1, which are the file to be read, p2 is the block #, and the data block number to be read starts, p3 is blocks. Generally, p3 is a single block, but if it is multiple blocks

Today is, and we will continue to complete the waiting event series. What is db file sequential read :? Simply put, oracle needs to read a single piece of data, and other sessions are waiting. There are three parameters p1, which are the file to be read, p2 is the block #, and the data block number to be read starts, p3 is blocks. Generally, p3 is a single block, but if it is multiple blocks

Today is, and we will continue to complete the waiting event series.

What is db file sequential read :?

Simply put, oracle needs to read a single piece of data, and other sessions are waiting. There are three parameters p1, which are the file to be read, p2 is the block #, and the data block number to be read starts, p3 is blocks. Generally, p3 is a single block, but if it is multiple blocks, it is generally read from temporary segment.

This type of wait event occurs mainly because of the execution of the index, undo segment, and table (when accessed with rowid ), A single read SQL statement (user and Recursion) that controls the file and data file header. This event is generated either because the table connection sequence is incorrect or the index is improperly used.

How can this problem be solved:

1. Optimize the SQL statement mainly by looking at the execution plan. If you can leave the entire table, you will not leave the whole table (but sometimes the whole table may be faster than the index ), you can skip table access byindex rowid if you can go to index scan.

2. Increase the I/0 speed of the file. Generally, for hardware storage devices, which raid is used for the disk where the data file is located is significant

3. Increase the buffer cache size so that the data can be obtained in the memory. However, when asmm and amm are enabled at 10G and 11g, the buffer cache will change, you can also store the related tables in the keep pool. If it is 11 GB, you can consider the result cache.

4. data can be stored in non-standard blocks (large blocks store multiple rows of data) to avoid I/0

5. Use indexes to organize tables and reduce I/o

6. Use parallel query execution (taking up a certain amount of memory and cpu, which must be determined based on the host load)

7. Try partition tables.

Case Analysis:

1. Obtain the SQL information as follows:

SELECTcount (1)

FROMWF_DEAL_COMMON_MAIN_T M,

WFC_ROOT_SUB_RELATION_T R,

RT_WORKITEMINST W

WHERE M.F _ PROCINST_ID = R. ROOT_ID

And r. SUB_ID = W. PROC_INSTANCE_ID

And w. current_statein (1, 2)

And w. USER_ID =: 1

And w. OVERDUED =: 2

View the execution plan as follows:

> SELECT count (1)

16:02:35 2 FROM WF_DEAL_COMMON_MAIN_T M,

16:02:35 3 WFC_ROOT_SUB_RELATION_T R,

16:02:35 4 RT_WORKITEMINST W

16:02:35 5 WHERE M.F _ PROCINST_ID = R. ROOT_ID

16:02:35 6 and r. SUB_ID = W. PROC_INSTANCE_ID

16:02:35 7 and w. current_state in (1, 2)

16:02:36 8 and w. USER_ID = 999

16:02:36 9 and w. OVERDUED = 1

16:02:36;

Predicate Information (identified by operation id ):

---------------------------------------------------

4-filter ("W". "CURRENT_STATE" = 1 OR "W". "CURRENT_STATE" = 2) AND "W". "OVERDUED" = 1 AND

TO_NUMBER ("W". "USER_ID") = 999)

6-access ("R". "SUB_ID" = "W". "PROC_INSTANCE_ID ")

Filter ("R". "SUB_ID" = "W". "PROC_INSTANCE_ID ")

8-access ("M". "F_PROCINST_ID" = "R". "ROOT_ID ")

Note

-----

-Dynamic samplingused for this statement

Index full scan, table access full. Note that there is implicit conversion at this time.

If you replace the variable with a string. The execution plan is as follows:

> R

1 SELECT count (1)

2 FROM WF_DEAL_COMMON_MAIN_T M,

3 WFC_ROOT_SUB_RELATION_T R,

4 RT_WORKITEMINST W

5 WHERE M.F _ PROCINST_ID = R. ROOT_ID

6 and r. SUB_ID = W. PROC_INSTANCE_ID

7 and w. current_state in (1, 2)

8 and w. USER_ID = '123'

9 * and w. OVERDUED = 2

Predicate Information (identified by operation id ):

---------------------------------------------------

4-filter ("W". "CURRENT_STATE" = 1 OR "W". "CURRENT_STATE" = 2) AND "W". "OVERDUED" = 2)

5-access ("W". "USER_ID" = '000000 ')

7-access ("R". "SUB_ID" = "W". "PROC_INSTANCE_ID ")

8-access ("M". "F_PROCINST_ID" = "R". "ROOT_ID ")

Note

-----

-Dynamicsampling used for this statement

Summary:

From the above content, we can analyze it as follows. user_id is of the varchar type, when w. if the value of user_id is a number, index full scan and table access full are generated. This is mainly caused by implicit conversion. One solution is to create a function index, the other is to convert the value when the variable is transferred. The latter is recommended.