Re: Overhead to consistent gets?

Notes in-line

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 10th Jan 2006

• Original Message ----- From: "Jonathan Gennick" To: Sent: Thursday, January 12, 2006 3:16 AM Subject: Overhead to consistent gets?

> 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
> http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l