Re: Automatic Segment Space Management

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 <ORACLE-L_at_fatcity.com> 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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).