Db file scattered read p1 p2 p3

Like all wait events the columns P1, P2, P3 give us the information needed to diagnose the waiting. SID, c. FILE , c. Wait Time: The wait blocks until all blocks in the IO request have been read. Some advise If you see this wait event then general you are NOT in a good position. It is very usual some databases to have wait events and doing IO for a full table or index scan, but can be avoided START considering to create an index instead of full table scan, if this is not posible, try to imporove the full table or index scan.

How to improve performance for db file scattered read The steps are, starting from that might come with better results: 1. Move the table to reset table High Water Mark 2. Use Parallel processing 3. Use tablespace with bigger block size 4. If the table has small size change it to IOT 5. Wow responding to a 4 years old post. All times are GMT The time now is PM. Prakash Blog. Skip to content. Home About About Me:. A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block See P3.

Share this: Twitter Facebook. Like this: Like Loading Bookmark the permalink. Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in:. Email required Address never made public. Name required. Search for:. This means you cannot always assume which bottlenecks you will see from looking at an execution plan.

Most DBAs would expect to see a lot of db file scattered read events. Another point worth noting is that the db file sequential read wait event does apply to insert statements.

The common misconception is that it only applies to update and delete statements. This irregularity is due to any of the following reasons:. The last set of blocks in an extent is less than the MBRC. Since the third fetch is for a single database block, the wait event is db file sequential read. However, for the first two read calls, the wait event is db file scattered read because the number of blocks is greater than 1. Therefore, cached blocks can cause full table scans operations to perform more reads than required.

As mentioned, a higher MBRC number can influence the optimizer to lean toward full scans. Batch processes can benefit from a higher MBRC as it allows full scan operations to complete faster. If the database serves both batch and OLTP processes, you must find a balance. The default value of 8 is rather conservative. If full scans are the best way to go, you want SQL statements to scan the objects with the maximum value supported by your system. Why waste time with a smaller value? You should find out what the maximum value is and dynamically apply this value to processes that perform large full scans.

There is a limit on MBRCs. The value is internally set in the Oracle code and varies with the Oracle version. The limit is K in earlier versions of Oracle and 1MB beginning in version 8. Furthermore, MBRC is subject to hardware limits such as the Solaris maxphys and file system maxcontig values.

Does this sound like too much to you? It is! The good news is there is a shortcut to finding the limit for your platform. You can set the MBRC to a ridiculously high number for your session, as shown next , and let Oracle figure out what the system can handle. The maximum P3 value of the db file scattered read events that belong to the first session is the MBRC limit for your platform.

Alternatively, you can monitor the full table scans with trace event This maximum value is not meant to be set at the database level; rather, it may be applied at the session level to speed up full scans when that is the best way to go. Yes, the storage layer is the slowest component, but that is only half of the story. The other half is about the stuff that goes on inside Oracle when blocks are being read into the SGA. There are numerous operations that have to take place.

If a free buffer is not found when the maximum scan limit is reached, the foreground process posts the DBWR process to make free buffers. Then the foreground process has to retry for the free buffer. Once it finds a free buffer, it unlinks it from the free lists chain and relinks the buffer in at the top of the LRU Least Recently Used or the midpoint insertion of the LRU depending on the version.

Then the pointers for the buffer header must be adjusted accordingly.