Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Overhead to consistent gets?

Re: Overhead to consistent gets?

From: Jonathan Lewis <>
Date: 2006-01-12 08:46:55
Message-id: 010201c6174c$5c413b10$0200a8c0@Primary

Notes in-line


Jonathan Lewis The Co-operative Oracle Users' FAQ Cost Based Oracle: Fundamentals Public Appearances - schedule updated 10th Jan 2006

> Is there likely to be a performance difference between the following
> two situations:
> a) Your query reads 1000 rows packed into 10 blocks. All blocks
> are already in the buffer cache (no physical reads). No other
> transaction has updated the blocks.
> b) Your query reads the same 1000 rows, but this time they are
> scattered over, say, 50 blocks. Again, all blocks are in cache. No
> other transaction has updated the blocks.
> Case b results in more consistent gets. But the same number of rows
> are returned in either case. This question seems to boil down to
> "what's the overhead to a consistent get?"

    Yes - (b) can result in more consistent gets, but it depends on     (at least) the access path, fetch arraysize, and the ordering of     the rows in the block (and the version of Oracle, and whether     table-prefetching is enabled).

    Set arraysize to one and you will probably do about 500 consistent     gets (because OCI8 cheats, and sets arraysize to 2 under the covers).

    But if you are doing a tablescan, Oracle can read all the rows in a     single block on a single consistent get.

    If Oracle is using an indexed access path it can PIN buffers after     the GET (in other words not let it go immediately after the read)     and may get multiple rows from the same block - if those rows     are in order in the index.         

> And, my own question that I'll is: do things change if another
> transaction has updated the blocks since your transaction began?

    If you've done "set transaction read only", then it is possible     that the blocks you first read were CR blocks that your session     created for the query (which means you would have done some     "consistent gets - examination" to read some undo blocks to create     them. If this case, your second query could still find them in memory     at no extra cost.

    If you were not running with "read only", then you would have to do     some undo reads now to rollback the changes made by the other     transaction.

> Best regards,
> Jonathan Gennick --- Brighten the corner where you are
> * 906.387.1698 *
> --

Received on Thu Jan 12 2006 - 08:46:55 CST

Original text of this message