Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: (RE): Calculating LIOs

Re: (RE): Calculating LIOs

From: Tanel Põder <tanel.poder.003_at_mail.ee>
Date: Fri, 13 Aug 2004 22:04:11 +0300
Message-ID: <068901c48168$519f6900$71849fd9@porgand>


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_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Fri Aug 13 2004 - 13:59:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US