Oracle 23c New Features for DBA's
Hello All,
What wait event you observe when Oracle Performs a full table scan?
- DB File Scattered Read
- Direct Path Reads (from 11g onwards under certain conditions)
What wait event you observe when Oracle Performs a table via index scan?
- DB File Sequential Read
While we are working a issue, we apparently found an issue that a full tablescan of 17gb table performing a db file sequential read.
This post is not how to resolve the issue (that's need a separate post), but to know what is the reason that Oracle performs the db file sequential read when doing a full tablescan. The following are best possible cases.
| Case | Reason | Description |
|
Case 1 |
Chained Rows |
chained rows
|
|
Case 2 |
Table Size just large above mbrc |
For example, if mbrc is 8 and table has 10 blocks, then oracle do one db file scattered read and 2 sequential reads. |
|
Case 3 |
Columns more than 255 |
Oracle fits the rows into different blocks of those columns beyond 255. So there is a chaining of rows , so when scan happens it has to fetch from different blocks, so indeed a sequential scan with in a multiblock scan, to know this, check v$sesstat for statistics "table fetch continued row" |
|
Case 4 |
IOT Table |
When having IOT table it can appear db file sequential read |
|
Case 5 |
MView Table |
When having Mview the mview with index may show db file sequential read |
|
Case 6 |
Extent & Block Mapping |
This occurs when Oracle is unable to read DBFMBRC worth of blocks from a given extent. This usually happens towards the edge of the high-water-mark of the table, but in theory can happen in any extent of the table. |
|
Case 7 |
Cached Blocks |
When certain blocks are already in cache from a table oracle will not read them from disk again, in those cases it do partial scans of mbrc value like block 9813, 9814 and 9815 - scattered read (MBR) with MBRC=3 |
Seems the marked red was the ones we have encountered as the table has 299 columns and the extents allocation shows more varying sizes (not rounded to block sizes of mbrc)
(The last two cases found in internet from Gaza Vaidyanth and Other's)
Hope this helps
-Thanks
Geek
Hi,
What is mean by mbrc. Would like to know how can be we resolve the waits with dbfsqread abd dbfsqread.
Thanks.
MBRC is multi block read count,
To resolve first you need to identify what might causing it, chaining is one of the major cause and you do the reorg of the table.
BTW, resolution is needed only when you think it is causing issue. For my case it has the issue and we reorg the table that solved my problem.
Good information