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
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
