Shulou(Shulou.com)06/01 Report--

This wait event, which is often seen in the actual production library, is a wait event caused by a user action, which occurs when the SESSION waits for the multi-block Ihand O to complete.

This usually occurs in two cases: a full table scan (FTS: Full Table Scan) and a fast index scan (IFFS: index fast full scan).

Oracle reads the DB_FILE_MULTIBLOCK_READ_COUNT of one contiguous block at a time and distributes them into the buffer cache. How you do this depends on the platform and the release of the Oracle you are running. It may also vary depending on the type of device being read and the number of blocks requested.

This wait event has three parameters:

Select event#,name,parameter1,parameter2,parameter3 from v$event_name where name ='db file scattered read' EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3--154db file scattered read File# block# blocks

The file number of the data file in which the data block is to be read by File#:.

The starting block number to be read by Block#:.

Blocks: the number of blocks to read.

First, wait-- location

Conversation information

It is useful to see which sessions are performing scans and track them to see if scans are expected.

SELECT sid, total_waits, time_waited FROM v$session_eventWHERE event='db file scattered read' AND total_waits > 0ORDER BY 3J 2; also query sessions with high "table scans blocks gotten" in V$SESSTAT

When this wait event is significant, it can be diagnosed in conjunction with the v$session_longops dynamic performance view, which records things that have been running for a long time (running for more than 6 seconds).

Tablespaces & data files

You can view the "Tablespace IO" and "File IO" sections of the AWR (or STATSPACK) report, as well as ADDM and ASH output. These files should show which tablespaces / files is serving most Imax O requests, as well as the speed of the Imax O subsystem. The tablespaces/files involved in "db file scattered read" will have "Av Blks/Rd" > 1.

The file that reads the file can also be found by looking at BLKS_READ / READS > 1 in V$FILESTAT (a ratio greater than 1 indicates that multiple block reads have occurred).

Segments & objects

See which segments/objects objects perform read operations.

Select event,p1,p2,p3,row_wait_obj# from gv$session where event ='db file scattered read';select object_name,object_type from dba_objects where object_id=row_wait_obj#

SQL

You can check the "Top SQL by Disk Reads" section of the AWR report for any clues to the SQL that caused the high I Dot O. If statistics collection is enabled, V$SQL_PLAN can also use a full scan to provide clues about SQL statements.

High disk _ READS statement in V$SQL-- displayed in the "Top SQL by Disk Reads" section of awr.

If a database has too many large table scans, the db file scattered read wait event may also be significant. If the per Trans value of table scans (long tables) is greater than 0, you may need to add the appropriate index to optimize your SQL statement

Second, wait-- lower

Some specific points of "db file scattered read" include:

Tuning SQL usually yields the most benefits.

Consider partitioning to reduce the amount of data to be scanned.

Are the affected objects sparsely populated? If so, consider reducing them.

Consider advanced compression to reduce the number of blocks that need to be accessed.

It may be helpful to be careful with multiple buffer pools and cache options.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.