cancel
Showing results for 
Search instead for 
Did you mean: 

db file sequential read

ericfjchen
Regular Advisor

db file sequential read

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
8 REPLIES
Simon Hargrave
Honored Contributor

Re: db file sequential read

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
Regular Advisor

Re: db file sequential read

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

Re: db file sequential read

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
Honored Contributor

Re: db file sequential read

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
ericfjchen
Regular Advisor

Re: db file sequential read

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
Honored Contributor

Re: db file sequential read

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
Frequent Advisor

Re: db file sequential read

Hi Eric,
Check this documment.

E.
Brian Crabtree
Honored Contributor

Re: db file sequential read

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