HighWaitson & #39; DbFileSequentialRead & #39; Due

DbFileSequentialRead in the top 5 of AWR recently occupies a very large percentage of time. Generally, this waiting event is normal. However, there are some problems with the current system performance. The concurrency is large and slow. Therefore, you need to determine whether such waiting events can be reduced. MOS has several introductions about such wait events. This is one of them.

In some recent AWR top 5 systems, "Db File Sequential Read" occupies a very large percentage of time. Generally, this waiting event is normal. However, there are some problems with the current system performance. The concurrency is large and slow. Therefore, you need to determine whether such waiting events can be reduced. MOS has several introductions about such wait events. This is one of them.

In some recent AWR top 5 systems, "Db File Sequential Read" occupies a very large percentage of time. Generally, this waiting event is normal. However, there are some problems with the current system performance. The concurrency is large and slow. Therefore, you need to determine whether such waiting events can be reduced. MOS has several introductions about such wait events. This is one of them.

High Waits on 'db File Sequential Read 'due to Table Lookup Following Index Access (Document ID 875472.1)

Even if the execution plan is already the best, a query can still wait for the "db file sequential read" event for a long time. It is usually because the index scan result set is very large. For example:

SELECT DFROM BIG_TABLEWHERE A = 1253AND B in ('CA', 'CO')AND C > 210 ;

Rows    Row Source Operation------- --------------------------------------------------- 215431 TABLE ACCESS BY INDEX ROWID BIG_TABLE (cr=880191 pr=430780 pw=0 time=2293667056 us) <<<3582275  INDEX RANGE SCAN BIG_TABLE_IDX (cr=664748 pr=218595 pw=0 time=352506821 us)Elapsed times include waiting on following events:  Event waited on                             Times   Max. Wait  Total Waited  ----------------------------------------   Waited  ----------  ------------  SQL*Net message to client                   14363        0.00          0.02  db file sequential read                    461688        1.15       2254.55 <<<  SQL*Net message from client                 14363        0.01          9.77  SQL*Net break/reset to client                   1        0.00          0.00...

In most such cases, the execution of the query statement on "table access by index rowid" requires more than the index scan. This is because random access to table rows costs more than index scanning.

To do this, you can debug the following methods:

1. check whether there is a better index or execution plan. You may need to redesign the index.

2. Try full table scan. Full table scanning is usually faster than index scanning, although the cost of CBO is higher than that of index scanning.

SELECT/* + FULL (BIG_TABLE) */D
FROM BIG_TABLE
Where a = 1, 1253
And B in ('CA', 'co ')
And c> 210;

3. If only a few columns are listed in the SELECT and WHERE clauses, you can consider creating a composite index for the query to avoid going back to the table.

For example:

CREATE INDEX ON BIG_TABLE (A, B, C, D);

Note: It is only valid for SELECT statements. This method may be useless if it is an UPDATE statement.

4. Move the table to a table with a larger block size. Larger block blocks have more rows, which helps reduce block I/O. It is also helpful to reorganize the table, because this can make the index have a smaller clustering factor.

5. You can increase the buffer cache size so that more blocks can be cached. If tables are frequently accessed, using the keep buffer pool is also a good choice.

6. Consider using IOT (index organization tables ). IOT may reduce IO because it stores data in a B-tree index structure. For example, if column A is the primary key of the BIG_TABLE table, you can create IOT as follows:

Create table BIG_TABLE (A number primary key, B char (2), C number, D varchar2 (10) organization index;

7. If the server has enough idle resources (CPU and memory), use parallel execution. This method does not reduce IO, but helps reduce the execution time.

8. Poor disk IO may also be a cause. Improves the IO of the disk device where the table is located. This may require assistance from the system administrator.