Re: Overhead to consistent gets?

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 12 Jan 2006 07:46:55 -0000

Notes in-line


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Cost Based Oracle: Fundamentals
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

Best regards,

Jonathan Gennick --- Brighten the corner where you are * 906.387.1698 * mailto:jonathan@xxxxxxxxxxx



Other related posts: