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

Db file scattered read

One: db file scattered read description

Second: db file scattered read solution

Third: db file scattered read reproduction process

Four: db file scattered read official documents

One: db file scattered read description

When Oracle performs full table scan (Full Table Scan,FTS) and index fast full scan (Index Fast Full Scan), in order to ensure performance, try to read more than one block at a time, which is called Multi Block I scan O.

Each time you execute Multi Block iCandle O, it waits for the end of the physical iUnip O and the db file scattered read event at this time.

Https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94479

This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations. A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan.

The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other. Such reads are called scattered read calls, because the blocks are scattered throughout memory. This is why the corresponding wait event is called'db file scattered read'. Multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for'db file scattered read'.

Https://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents003.htm#BGGIBDJI

Similar to db file sequential read, except that the session is reading multiple data blocks.

Second: db file scattered read solution

1 SQL optimization

If it is caused by some SQL, such as inaccurate statistical information, no index or the use of inefficient indexes, the db file scattered read can be reduced by optimizing SQL

2 Partition Table

You can consider optimizing full table scans to partition scans

3 increase BUFFER CACHE

If db file scattered read occurs very frequently and Buffer HIT is low, you can consider increasing buffer cache.

4 use faster Stora

Select name, parameter1, parameter2, parameter3

From v$event_name

Where name ='db file scattered read'

-View the session with db file scattered read wait events

SELECT sid, total_waits, time_waited

FROM v$session_event

WHERE event ='db file scattered read'

And total_waits > 0

ORDER BY 2 desc

Select sid, event, p1, p2, p3, wait_class

From v$session_wait

Where event ='db file scattered read'

Check the following V$SESSION_WAIT parameter columns:

P1: The absolute file number

P2: The block being read

P3: The number of blocks (should be greater than 1)

Select owner, segment_name, segment_type

From dba_extents

Where file_id = 6

And 37475 between block_id and block_id + blocks-1

SELECT row_wait_obj# FROM V$SESSION WHERE EVENT ='db file scattered read'

SELECT owner, object_name, subobject_name, object_type

FROM DBA_OBJECTS

WHERE data_object_id = '88605'

Select v.last_call_et

V.username

V.sid

Sql.sql_text

Sql.sql_id

Sql.disk_reads

V.event

From v$session v, v$sql sql

Where v.sql_address = sql.address

And v.last_call_et > 0

And v.status = 'ACTIVE'

And v.username is not null

Select * from table (dbms_xplan.display_cursor ('d24df9xbujb75'))

-SELECT SQL_ADDRESS, SQL_HASH_VALUE

FROM V$SESSION

WHERE EVENT LIKE'db file%read'

Third: db file scattered read reproduction process

Index Fast full scan (Index Fast Full Scan)

SQL > create tablespace chenjch_tbs datafile'/ u01 size size oracle autoextend on maxsize 1G

SQL > create user chenjch identified by a default tablespace chenjch_tbs

SQL > grant connect,resource,dba to chenjch

SQL > create table T1 as select * from dba_objects

SQL > insert into T1 select * from T1

SQL > commit

SQL > insert into T1 select * from T1

SQL > commit

SQL > insert into T1 select * from T1

SQL > commit

.

SQL > create index i_t1_001 on T1 (object_id,object_name,object_type)

SQL >

Begin

Dbms_workload_repository.create_snapshot ()

End

SQL > alter system flush buffer_cache

SQL > alter session set tracefile_identifier='10046'

SQL > alter session set events' 10046 trace name context forever, level 12'

SQL > select / * + index_ffs (T1 i_t1_001) * / object_id, object_name, object_type from T1 where object_id = '20'

SQL > alter session set events' 10046 trace name context off'

SQL >

Begin

Dbms_workload_repository.create_snapshot ()

End

SQL >

Select distinct (m.sid), p.pid, p.tracefile

From v$mystat m, v$session s, v$process p

Where m.sid = s.sid

And s.paddr = p.addr

-- / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_57032_10046.trc

[oracle@dip ~] $tkprof / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_57032_10046.trc / home/oracle/10046_3.trc

Full table scan (Full Table Scan,FTS)

Http://f.dataguru.cn/thread-44033-1-1.html

Oracle 11g, there are new changes in the full table scan algorithm of large tables, according to the size of the table, the size of the cache and other information, decide whether to bypass SGA to read data directly from the disk.

On the other hand, 10g is all read through cache.

Oracle 11g thinks that using direct path reading for large tables with full tables may be faster and more efficient than data file hash reading (db file scattered reads) in 10g, so most of what we see are direct path read wait events.

Oracle 11g provides us with the option, which can be controlled by an implicit parameter, namely "_ serial_direct_read", which defaults to auto.

SQL > create tablespace chenjch_tbs datafile'/ u01 size size oracle autoextend on maxsize 1G

SQL > create user chenjch identified by a default tablespace chenjch_tbs

SQL > grant connect,resource,dba to chenjch

SQL > create table T1 as select * from dba_objects

SQL > insert into T1 select * from T1

SQL > commit

SQL > insert into T1 select * from T1

SQL > commit

SQL > insert into T1 select * from T1

SQL > commit

.

SQL >

Begin

Dbms_workload_repository.create_snapshot ()

End

SQL > alter system flush buffer_cache

SQL > alter session set tracefile_identifier='10046'

SQL > alter session set events' 10046 trace name context forever, level 12'

SQL > select object_id, object_name, object_type from T1 where object_id = '20'

SQL > alter session set events' 10046 trace name context off'

SQL >

Begin

Dbms_workload_repository.create_snapshot ()

End

SQL >

Select distinct (m.sid), p.pid, p.tracefile

From v$mystat m, v$session s, v$process p

Where m.sid = s.sid

And s.paddr = p.addr

-- / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_55600_10046.trc

[oracle@dip] $cp / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_55600_10046.trc.

[oracle@dip ~] $tkprof orcl_ora_55600_10046.trc 10046_1.trc

Turn off direct path read special effects and wait for the event to become db file scattered read when the full table is scanned

SQL >

Select a.ksppinm name, b.ksppstvl value, a.ksppdesc description

From x$ksppi a, x$ksppcv b

Where a.indx = b.indx

And a.ksppinm like'_ serial_direct_read';-auto

SQL > alter session set "_ serial_direct_read" = never

SQL >

Begin

Dbms_workload_repository.create_snapshot ()

End

SQL > alter system flush buffer_cache

SQL > alter session set tracefile_identifier='10046'

SQL > alter session set events' 10046 trace name context forever, level 12'

SQL > select object_id, object_name, object_type from T1 where object_id = '20'

SQL > alter session set events' 10046 trace name context off'

SQL >

Begin

Dbms_workload_repository.create_snapshot ()

End

SQL >

Select distinct (m.sid), p.pid, p.tracefile

From v$mystat m, v$session s, v$process p

Where m.sid = s.sid

And s.paddr = p.addr

-- / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_55600_10046.trc

[oracle@dip ~] $cp / u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_55600_10046.trc 10046_2.trc

[oracle@dip ~] $tkprof 10046_2.trc 10046_2a.trc

Four: db file scattered read official documents

Https://docs.oracle.com/cd/E11882_01/server.112/e40402/waitevents003.htm#BGGIBDJI

Db file scattered read

Similar to db file sequential read, except that the session is reading multiple data blocks.

Wait Time: The wait time is the actual time it takes to do all of the I/Os

Https://docs.oracle.com/cd/E11882_01/server.112/e41573/instance_tune.htm#PFGRF94479

10.3.2 db file scattered read

This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations. A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan.

The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other. Such reads are called scattered read calls, because the blocks are scattered throughout memory. This is why the corresponding wait event is called'db file scattered read'. Multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for'db file scattered read'.

Check the following V$SESSION_WAIT parameter columns:

P1: The absolute file number

P2: The block being read

P3: The number of blocks (should be greater than 1)

10.3.2.2 Managing Excessive I/O

There are several ways to handle excessive I/O waits. In the order of effectiveness, these are as follows:

Reduce the I/O activity by SQL tuning.

Reduce the need to do I/O by managing the workload.

Gather system statistics with DBMS_STATS package, allowing the query optimizer to accurately cost possible access paths that use full scans.

Use Automatic Storage Management.

Add more disks to reduce the number of I/Os for each disk.

Alleviate I/O hot spots by redistributing I/O across existing disks.

The first course of action should be to find opportunities to reduce I/O. Examine the SQL statements being run by sessions waiting for these events and statements causing high physical I/Os from V$SQLAREA. Factors that can adversely affect the execution plans causing excessive I/O include the following:

Improperly optimized SQL

Missing indexes

High degree of parallelism for the table (skewing the optimizer toward scans)

Lack of accurate statistics for the optimizer

Setting the value for DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter too high which favors full scans

10.3.2.3 Inadequate I/O Distribution

Besides reducing I/O, also examine the I/O distribution of files across the disks. Is I/O distributed uniformly across the disks, or are there hot spots on some disks? Are the number of disks sufficient to meet the I/O needs of the database?

See the total O operations (reads and writes) by the database, and compare those with the number of disks used. Remember to include the I/O activity of LGWR and ARCH processes.

10.3.2.4 Finding the SQL Statement executed by Sessions Waiting for I/O

Use the following query to determine, at a point in time, which sessions are waiting for I/O:

SELECT SQL_ADDRESS, SQL_HASH_VALUE

FROM V$SESSION

WHERE EVENT LIKE'db file%read'

10.3.2.5 Finding the Object Requiring I/O

To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for db file scatteredread. For example:

SELECT row_wait_obj#

FROM V$SESSION

WHERE EVENT ='db file scattered read'

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type

FROM DBA_OBJECTS

WHERE data_object_id = & row_wait_obj

Https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=162624307195503&id=34558.1&_afrWindowMode=0&_adf.ctrl-state=1a6zu7mmvj_153

WAITEVENT: "db file scattered read" Reference Note (document ID 34558.1)

Modification time:

2018-1-17

Type:

REFERENCE

* Checked for relevance on 05 color Augmuri 2015 audio colors *

"db file scattered read" Reference Note

This is a reference note for the wait event "db file scattered read" which includes the following subsections:

Brief definition

Individual wait details (eg: For waits seen in V$SESSION_WAIT)

Systemwide wait details (eg: For waits seen in V$SYSTEM_EVENT)

Reducing waits / wait times

Troubleshooting

Known Bugs

See Note:61998.1 for an introduction to Wait Events.

Definition:

Versions: 7.0-12.1 Documentation: 12.1 11.2 11.1 10.2 10.1

This wait happens when a session is waiting for a multiblock IO to complete. This typically occurs during FULL TABLE SCANs or INDEX FAST FULL SCANs. Oracle reads up to DB_FILE_MULTIBLOCK_READ_COUNT consecutive blocks at a time and scatters them into buffers in the buffer cache. How this is done depends on the platform and the release of Oracle you are running. It may also vary depending on the device type being read from and the number of blocks requested.

Individual Waits:

Parameters:

P1 = file#

P2 = block#

P3 = blocks

File# This is the file# of the file that Oracle is trying to read

From. In Oracle8 onwards it is the ABSOLUTE file number (AFN).

Block# This is the starting block number in the file from where

Oracle starts reading the blocks.

See Note:181306.1 to determine the tablespace, filename

And object for this file#,block# pair.

Blocks This parameter specifies the number of blocks that Oracle is

Trying to read from the file# starting at block#.

The upper limit is DB_FILE_MULTIBLOCK_READ_COUNT, which is self

Tuned from Oracle 10.2 onwards.

Wait Time:

The wait blocks until all blocks in the IO request have been read.

Note than an Oracle read request to the OS may be satisfied from an

OS file system cache and so may not incur a disk IO.

Systemwide Waits:

IO is a normal activity so you are really interested in unnecessary or slow IO activity.

If the TIME spent waiting for multiblock reads is significant then determine which segments/objects Oracle is performing the reads against. See the "Tablespace IO" and "File IO" sections of the AWR (or STATSPACK) reports, along with ADDM and ASH output. These should show which tablespaces / files are servicing the most IO requests, and give an indication of the speed of the IO subsystem. Tablespaces / files involved in "db file scattered read" waits will have "Av Blks/Rd" > 1.

The files where the reads are occuring can also be found by looking at V$FILESTAT where BLKS_READ / READS > 1. (A ratio greater than 1 indicates there are some multiblock reads occuring)

See the "Top SQL by Disk Reads" sections of AWR reports for clues about any SQL causing high Imax O. If statistics gathering is enabled then V$SQL_PLAN can also give clues about SQL statements using FULL scans.

It can sometimes be useful to see which sessions are performing scans and trace them to see if the scans are expected or not. This statement can be used to see which sessions are incurring waits:

SELECT sid, total_waits, time_waited

FROM v$session_event

WHERE event='db file scattered read'

And total_waits > 0

ORDER BY 3,2

One can also look at:

Statements with high DISK_READS in V$SQL-shown in the "Top SQL by Disk Reads" section of AWR.

Sessions with high "table scans blocks gotten" in V$SESSTAT

Reducing Waits / Wait times:

Ideally you do not want to repeatedly perform full scans in online portions of an application when there is a faster more selective way to get at the data-in this case query tuning should be used to optimize the online SQL. In non online portions of an application table scanning is much more likely to be required. The main steps for tuning IO waits are described in Note:223117.1. Some specific points for "db file scattered read" waits include:

Tuning of SQL usually gives the largest gains

Consider partitioning to reduce the amount of data you need to scan

Are affected objects sparsely populated? If so consider shrinking them

Consider Advanced Compression to reduce the number of blocks that need to be visited

Careful use of multiple buffer pools and the CACHE option might help.

Troubleshooting

See the following documents for help troubleshooting issues relating to "db file scattered read" waits:

Document:1475785.1 Resolving Issues Where Application Queries are Waiting Too Often for'db file scattered read' Operations Document:1476092.1 Resolving Issues Where'db file scattered read' Waits are Seen Due to IO Performance Problems Document:223117.1 Troubleshooting I of the Database is Slow O Related Waits Document:1275596.1 How to Tell if the I big O of the Database is Slow

Known Issues / Bugs:

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:

NB

Prob

Bug

Fixed

Description

II

25150925

12.1.0.2.180116, 18.1

Extreme Performance Degradation on Cursor-Duration Temporary Table (WITH Clause)

P

II

13400729

11.2.0.4, 12.1.0.1

Increased elapsed time on "db file scattered read" in IBM AIX

I

6452766

10.2.0.4.3, 10.2.0.5, 11.2.0.1

10046 trace does not always show the correct "obj#" value in the trace

II

5376783

10.2.0.4, 11.1.0.6

DBMS_SPACE.OBJECT_GROWTH_TREND can perform excessive IO

'*' indicates that an alert exists for that issue.

Indicates a particularly notable issue / bug.

See Note:1944526.1 for details of other symbols used

Related:

Tracing User sessions Note:62160.1

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

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.