RE: sequential read on full-table scan?

Hi Jaffar=20

>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'

OWI is a great book. But I have to disagree with one of the = explanations...

>Chained 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.

IMO this is not true. A simple testcase:

rem ***** begin **************************************

CREATE TABLE t (id NUMBER, v1 VARCHAR2(4000), v2 VARCHAR2(4000), v3 = VARCHAR2(4000))=20
TABLESPACE USERS_NOASSM PCTFREE 0; INSERT INTO t select rownum, lpad('1',4000,'1'), lpad('1',4000,'1'), = lpad('1',4000,'1')=20
FROM all_objects=20
WHERE rownum <=3D 10;

COMMIT; ANALYZE TABLE t COMPUTE STATISTICS;
SELECT num_rows, chain_cnt FROM user_tables WHERE table_name =3D 'T';

rem just to parse the statement and avoid additional WAIT lines in the = trace...
SELECT * FROM t;
ALTER SYSTEM SET EVENTS 'IMMEDIATE TRACE NAME FLUSH_CACHE'; ALTER SESSION SET EVENTS '10046 trace name context forever, level 8'; SELECT * FROM t;
ALTER SESSION SET EVENTS '10046 trace name context off';

rem ***** end **************************************

If I run these statements in my 9.2 database the tracefile contains the = following I/O-related waits:

linux:oracle:A920> grep WAIT a920_ora_1105.trc | grep db WAIT #1: nam=3D'db file sequential read' ela=3D 36 p1=3D15 p2=3D9 p3=3D1 WAIT #1: nam=3D'db file scattered read' ela=3D 3402 p1=3D15 p2=3D10 = p3=3D7
WAIT #1: nam=3D'db file scattered read' ela=3D 105 p1=3D15 p2=3D17 = p3=3D8
WAIT #1: nam=3D'db file scattered read' ela=3D 70 p1=3D15 p2=3D25 p3=3D5

I.e. except for the header, only multiblock reads.

HTH
Chris

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