db file sequential read

[ericfjchen]

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

[Simon Hargrave]

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.

[ericfjchen]

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

[Marcel Boogert_1]

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.

[Yogeeraj_1]

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

[ericfjchen]

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

[Hein van den Heuvel]

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.

[Emilio Brusa]

Hi Eric,
Check this documment.

E.

[Brian Crabtree]

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