Re: DB_BLOCK_SIZE set to the highest value

Ours not to reason why ....

Of course, the number 5 only applies to the versions I happen to have looked at - but you get the number on 8.1.7 for 4K blocks and 8K blocks. I doubt if it is anything to do with the old '4 is good' rule, because I __think__ it is a feature that is much newer than the rule.

It isn't obvious that dropping from 16K to 8K should make a dramatic difference - especially with 16 rollbacks and only 6 batch jobs. I would have though that you would have to have at least 96 actual concurrent transactions most of the time for it to have a significant effect. How big were the extents - and how fast were they filling when the big batch was going on.

(100MB giving 4GB is a bit extreme, but it does cost a lot to delete a table - and if anything was doing insert/rollback cycles, that makes life hard).

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Nuno Souto wrote in message <3b0f0a21.1635705_at_news-server>...



>Why 5?  I mean, it looks like a number out of some internal strategy,

>but which one? Would this have anything to do with the old

>rule-of-thumb of 4 connections per rollback segment?

>





>Batch is long transactions, characterized by loading extracted data

>into temp (shared!) tables, processing it, loading it back into other

>tables and then deleting temp data. Up to 5 million rows get processed

>this way on each run. I don't need to explain the problems I have with

>these tables...  ;-)

>The blowout of rlbseg size I was experiencing before happened with

>these batch executions.  In one abnormal case a batch run blew 4Gb of

>rlbseg to process a 100Mb table.

>

>The DB has 16 rlbsegs. I've run all the stats I could find and I was

>getting consistent waits on them at 16K.  With 8K, I get an occasional

>wait here and there and not much else.  If anything, 16 rlbsegs is

>over the top now whereas before it wasn't enough.  All rlbsegs are

>shrunk twice a day.  Once in the morning before dayly load starts,

>once in the evening before overnight batch load starts.  We used to

>use "optimal", but that caused a lot of snapshot too old messages.

>Much better to do it like this.

>

>Do you reckon this sort of load would have caused the problem at 16K?

>I'd have stayed there if I could, but the darn thing was constantly

>giving me headaches.

>

>Cheers

>Nuno Souto

>nsouto_at_bigpond.net.au.nospam

>http://www.users.bigpond.net.au/the_Den/index.html