It's interesting you should mention the
'select for update' in this context.
I'm still working on a puzzle where
I do:
create table t1 (n1 number);
insert into t1 values (0);
insert into t1 values(1);
commit;
select rowid from t1 where n1 = 0;
for i in 1..1000 loop
update t1 set n1 = n1 + 1
where n1 = i;
end loop; -- updates the '1' row 1,000 times.
/
Now, without committing - start another session
that does:
select n1 from t1
where rowid = '{value seen above for n1 = 0}
for update;
Repeat the experiment, but the second time do:
update t1
set n1 = 99
where rowid = '{value seen above for n1 = 0};
Why does one of these statements to 1000
CR gets, whilst the other does none ? How
different are they - they both put an ITL entry
on the block, and change the row content -
they both need to be able to lock the row.
I think this may have some bearing on your
'large number of CR reads' - I too have seen
sites where the numbers got very large (in
part because the CR limit doesn't seem to
be considered if there are free blocks (state = 0)
around to be used). But if the code does
'select for update, update' - then it takes a
long time to make a CR copy in a busy
enviornment, so if concurrency is high on
that block, then I guess the evolving (or
is that devolving) CR block is pinned for
a long time - allowing lots more CR blocks
to be created.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )
____UK_______March 19th
____USA_(FL)_May 2nd
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 20 February 2003 20:58
>Stephan just passed this on to me...
>
>"Cary,
>
>I really must subscribe to this mail list, but until I do, maybe you
can
>pass this on.
>
>You are correct, the _db_block_max_cr_dba parameter is just a guide.
I
>believe that when needing to create a new CR copy and this limit has
>been reached Oracle tries to place any older CR buffers (not sure if
it
>does all of them or oldest found) to the cold end of the LRU ready to
>leave the cache at the next possible opportunity. If the buffer has
any
>users or waiters (can be seen in x$bh), then the CR buffer will
remain
>in cache until next time. When a new CR buffer is created, and an
older
>CR buffer no longer has users or waiters, it should be aged out of
the
>cache as soon as possible.
>
>I hope this helps,
>Stephan"
>
>
>
>Cary Millsap
>Hotsos Enterprises, Ltd.
>http://www.hotsos.com
>
>Upcoming events:
>- RMOUG Training Days 2003, Mar 5-6 Denver
>- Hotsos Clinic 101, Mar 25-27 London
>
>
>-----Original Message-----
>Millsap
>Sent: Thursday, February 20, 2003 1:41 PM
>To: Multiple recipients of list ORACLE-L
>
>Anjo personally "saved my bacon" when I was at a site in Dallas with
>this problem. This particular problem was a vendor application ported
>from Sybase and thus used "select from blah_id for update; update
blah;
>commit;" instead of Oracle sequences. These guys had 1,200+ CR copies
of
>each little 1-row-1-column id table in their system. In the end, the
>vendor repaired its app to use sequence numbers (within the week,
>actually!), and the problem which had caused daily shutdown/restarts
>ended instantly. The "42 patch," as it was called at the time, would
>have helped reduce the severity of the problem, but it wouldn't have
>solved it.
>
>I was pretty proud of myself when the engagement was done, but a
monkey
>could have probably executed my part in the project if the monkey had
>known how to call Anjo.
>
>
>Cary Millsap
>Hotsos Enterprises, Ltd.
>http://www.hotsos.com
>
>Upcoming events:
>- RMOUG Training Days 2003, Mar 5-6 Denver
>- Hotsos Clinic 101, Mar 25-27 London
>
>
>-----Original Message-----
>Sent: Thursday, February 20, 2003 1:04 PM
>To: Multiple recipients of list ORACLE-L
>
>
>The _db_block_max_cr_dba parameter was put in to fix this problem
with
>massive
>number of CR copies (segment header blocks mostly). I remember seeing
a
>test
>case that had 1500+ CR copies of the segment header block. So the fix
>was to
>limit the number of CR copies. The parameter _db_block_max_cr_dba had
>initially a default value of 42 (really). And worked perfectly, but
it
>was
>brok in Parallel Server, the reason for it not working was very
funny.
>
>While scanning the hash chain for the right (tsn, rdba) the CR code
may
>already stop if it finds the best fit and never scan all the buffers
and
>
>there it can't enforce the limit of 6. Another reason could be that
the
>buffers are pinned (in use), but they should be flushed out later if
the
>same
>buffer hash chain is scanned again for the (tsn, rdba).
>
>In version8 I have seen a particular test case with over 60+ CR
copies
>of a
>index root block (running many processes doing NL and inserts into
that
>index
>didn't help ofcourse). It is alway hard to tell why the limit is not
>enforced
>(may be we need a stat on this? ;-)). It could be a bug or buffer
pinned
>
>(have seen both in production situations).
>
>Anjo.
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
