topic Re: db file sequential read in Operating System - HP-UX

db file sequential read

ericfjchen — Wed, 01 Sep 2004 03:03:37 GMT

A SQL statment needs a long time to execution. This session often waited for 'db file sequential read'. Can anyone explain this? It means "Full table scan"?

Thanks

Eric

Re: db file sequential read

Simon Hargrave — Wed, 01 Sep 2004 03:22:35 GMT

Yes, basically there isn't an index on a search field within your query. Therefore the only way it can perform your query is to scan the whole table.

If this is a required query, you either need to add extra index(es) to you schema, or rewrite the query.

Re: db file sequential read

ericfjchen — Wed, 01 Sep 2004 03:28:46 GMT

If Oracle performs a index scan, the session wait event should be "db file scattered read", right?

Re: db file sequential read

Marcel Boogert_1 — Wed, 01 Sep 2004 04:09:26 GMT

The wait event 'db file sequential read' can have all kind of explanations. The most important thing is to read it's context. I have the same wait events, but in the context of the complete performance it means nothing.

Re: db file sequential read

Yogeeraj_1 — Wed, 01 Sep 2004 08:43:52 GMT

hi,

db file sequential read is due to INDEXED reads and it is single block IO. It is not the result of a full scan.

Also, a sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block.

hope this helps!
regards
Yogeeraj

Re: db file sequential read

ericfjchen — Wed, 01 Sep 2004 19:52:58 GMT

Hi ALL,

Thanks for your reply in advance.
However, I am very confused whether it is full scan or not. Does any Oracle white paper mention this? Could you pls explain detail about 'sequential'? When that session execution, v$session_wait also showed P1, P2, and P3.

P1 = file#
P2 = block#
P3 = blocks

After checking this, it is a table.

Thanks

Eric

Re: db file sequential read

Hein van den Heuvel — Wed, 01 Sep 2004 23:58:07 GMT

It is NOT a full scan (contrary to the name).
There are LOTs of writings in this subject.
CHeck Oracle sites, and hotsos, and ixora,...

For example.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3230221064491

http://www.hotsos.com/dnloads/1/kevents/db_file_sequential_read.html

Cheers,
Hein.

Re: db file sequential read

Emilio Brusa — Fri, 03 Sep 2004 11:04:30 GMT

Hi Eric,
Check this documment.

E.

Re: db file sequential read

Brian Crabtree — Fri, 03 Sep 2004 15:22:12 GMT

Eric,

DB Sequential Read doesn't nessecarily mean that a FTS scan is being done, only that it is pulling multiple blocks from the database in a query. DB Scattered Read means that it is most likely using an index. I would recommend checking the SQL plan for the query that you see it on, and see what it is doing.

Thanks,

Brian