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" <jonathan@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxx

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




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


Other related posts: