Re: sequential read on full-table scan?

From: The Human Fly <sjaffarhussain_at_gmail.com>
Date: Sun, 22 May 2005 11:55:31 +0300
Message-ID: <97b7fd2f0505220155528647ee@mail.gmail.com>

In Chapter 5: Interpreting Common I/O Related Wait Events of 'Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning' book, authors have explained, 'Why Does db file sequential read event Show Up in a Full Table Scan Operation'

If you trace or monitor a full table scan operation closely, you may find db file sequential read events sandwiched between db file scattered read events. This may or may not be a problem depending on the circumstance of the single-block read. Following are the four primary reasons why you see db file sequential read events in a full scan operation.
=A7=09Extent boundary When the last set of blocks in an extent is only 1 block, Oracle fetches that block with a single-block read call. This is normally not a problem unless your extent size is too small. Following is an event 10046 trace file that shows db file sequential read events embedded in a full table scan operation. The table block size is 8K, the MBRC is 8 blocks, and the extent size is 72K (9 blocks). A full table scan against the table will result in many db file sequential read events if the table is large. If this is the case, the full table scan operation will complete faster if the table is rebuilt with a larger extent size.

WAIT #1: nam=3D'db file scattered read' ela=3D 470 p1=3D7 p2=3D18 p3=3D8
WAIT #1: nam=3D'db file sequential read' ela=3D 79 p1=3D7 p2=3D26 p3=3D1
WAIT #1: nam=3D'db file scattered read' ela=3D 459 p1=3D7 p2=3D27 p3=3D8
WAIT #1: nam=3D'db file sequential read' ela=3D 82 p1=3D7 p2=3D35 p3=3D1
WAIT #1: nam=3D'db file scattered read' ela=3D 466 p1=3D7 p2=3D36 p3=3D8
WAIT #1: nam=3D'db file sequential read' ela=3D 79 p1=3D7 p2=3D44 p3=3D1
WAIT #1: nam=3D'db file scattered read' ela=3D 460 p1=3D7 p2=3D45 p3=3D8
WAIT #1: nam=3D'db file sequential read' ela=3D 60 p1=3D7 p2=3D53 p3=3D1
WAIT #1: nam=3D'db file scattered read' ela=3D 779 p1=3D7 p2=3D54 p3=3D8
WAIT #1: nam=3D'db file sequential read' ela=3D 78 p1=3D7 p2=3D62 p3=3D1

. . .
=A7=09Cached blocks See explanation in the "Why Does a Full Scan Operation Request Fewer Blocks than the MBRC" section. This is not a problem. =A7=09Chained or migrated rows It is a problem if you see many db file sequential read waits against a table when the execution plan of the SQL statement calls for a full table scan. This indicates the table has many chained or migrated rows. Oracle goes after each chained or migrated row with the single-block I/O call. Check the table's CHAIN_CNT in the DBA_TABLES view. Of course, the CHAIN_CNT is as of the LAST_ANALYZED date. Migrated rows can be corrected by reorganizing the table (export and import, or ALTER TABLE MOVE). =A7=09Index entry creation It is not a problem if you see many db file sequential read waits against an index when the execution plan of the SQL statement calls for a full table scan. In the following example, TABLE_A has an index and the db file sequential read waits were the result of reading index blocks into the SGA to be filled with data from TABLE_B. Notice the magnitude of the db file sequential read waits versus the db file scattered read in the statistics. 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. =A7=09-- SQL statement
insert into table_A
select * from table_B;

=A7=09
-- Explain plan
LVL OPERATION OBJECT

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

  1 INSERT STATEMENT=20
  2 TABLE ACCESS FULL TABLE_B =20 =A7=09
-- Wait event statistics

SID EVENT                          TIME_WAITED
--- ------------------------------ -----------
  7 SQL*Net message from client              5
  7 latch free                              11
  7 log file switch completion             155
  7 log buffer space                       205
  7 log file sync                          467
  7 db file scattered read               1,701=20
  7 db file sequential read            185,682

On 5/21/05, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
> OOps, in case anyone wonders how I come to a cost calculation of 1152/16
> and 1152/3 for a 1024 block table. The explanation is that I had started
> with 1024 but by the time I got to 1024/3=3D341.33333 I thought it would =
be
> simpler to avoid the decimals and switched the calculations to a 1152 blo=
ck
> table, except I forgot to make that change in the first line.

>=20

> At 11:45 PM 5/20/2005, Wolfgang Breitling wrote:

>=20


> >e.g. for a table of 1024 blocks

> >

> >with a dfmrc of 16: cost of FTS =3D 1152/16 =3D 72 ( + 1 for

> >_table_scan_cost_plus_one =3D true) =3D 73

> >with a real mbrc of 3: cost of FTS =3D 1152/3 =3D 384 ( + 1 for
> >_table_scan_cost_plus_one =3D true) =3D 385

>=20

> Regards

>=20

> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com

>=20

> --
> http://www.freelists.org/webpage/oracle-l
>=20

--=20
Best Regards,
Jaffar, OCP DBA
Banque Saudi Fransi
Saudi Arabia

---------------------------------------------------------------------------=

---

"It is your atittude, not your aptitude that determins your altitude."

--
http://www.freelists.org/webpage/oracle-l

Received on Sun May 22 2005 - 05:00:13 CDT