Re: (RE): Calculating LIOs
- From: Tanel Põder <tanel.poder.003@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 13 Aug 2004 22:04:11 +0300
NB! If you're already aware of the differences between index full and fast
full scan then the interesting stuff is in the second half of the post;)
> How do you tell what the HWM is of indexes? I did not see a blocks column
in DBA_INDEXES?
Use dbms_space.space_usage or dbms_space.unused_space procedures. Or dump
index segment header :)
> Also, I would assume that an 'index full scan' also reads up to the HWM.
Index full scan traverses index through it's logical TREE structure block by
block:
From root to first branch up to first leaf block and continues reading
logically next leaf block until there is no more leaf blocks in the chain
(means that the next leaf pointer in given leaf block is null). Also note
that you could do a descending full scan, thus the scanning starts from
other end of the tree.
This kind of access path returns index entries in index order.
Index Fast full scan just scans through the whole segment, usually with
multiblock reads, ignoring the index tree structure (and branch blocks since
they're needed only for traversing the index using tree structure). Fast
full scan doesn't guarantee to return index keys in order (although in some
circumstances it can return).
(--- the interesting part starts here ---)
> However, some people on here said that a full scan can read at your
multiblock read
If a regular index index full or range scan causes "multiblock" reads, it is
due index prefetching which is up to optimizer to decide.
Index prefetching means that when doing a range or full scan, then Oracle
will read the index by traversing the index tree, but it reads the tree
several blocks at a time (for example, it gets a number of logically
consecutive leaf block addresses from branch block and reads a batch of
leafs in).
So this is a *noncontiguous* multiblock read, meaning that Oracle just
issues multiple single block read requests in one batch. This should result
in a "db file parallel read" wait. So, asynch IO should definitely be used
when relying on index prefetch, otherwise your performance might degrade,
especially when some of the prefetched blocks don't get used at all.
CKPT process maintains statistics history in X$KCBKPFS (kernel cache buffer
k? pre-fetch statistics) about wasted prefetched blocks.
(Luckily this x$ table's columns are written in english not just
abbreviations). So if sessions start hitting a lot of never pinned prefetch
blocks, the x$kcbkpfs statistics start to indicate inefficient prefetching,
thus causing CBO not to favour it and prefer regular single block reads.
Index full scans are the main candidates which may benefit from prefetch,
since a full scan needs to read all leaf blocks anyway, thus not risking
with prefetch wastage. However, in order to prefetch batches of leaf blocks
you have to read in the branch blocks "above" it, causing some extra IO (for
regular single block index full scan you only have to read in branch blocks
from root to first or last leaf block in tree).
Note that I'm only concluding my last claim, I've not done testing to see
whether extra branch blocks actually happen - but I don't see any other way
how to find out DBAs of next leaf block batch.
You can control prefetching with parameters:
_index_prefetch_factor - defaults to 100, but when changed smaller, the
optimizer will favor index prefetching.
_db_file_noncontig_mblock_read_count - maximum number of blocks to be
prefetched in one batch. If set to 1, prefetching should be disabled.
There is an event 10299 which traces prefetch operations and if you set it
to CKPT process you'll see some aggregate statistics as well, also there are
a lot of %prefetch% statistics in v$sesstat & v$sysstat
Also, if you Wolfgang your queries (using 10053 trace), you should see
something like "prefetch is used for ..." in CBO trace file if prefetch is
used.
Tanel.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
