Re: DB_BLOCK_SIZE set to the highest value

On Sat, 26 May 2001 09:00:11 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>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).
>

It's actually quite funny. It wasn't until I monitored v$access for quite a while that it downed on me what was happenning. This particular vendor used to have a strategy of using temporary tablespace. Obviously this caused problems with other databases.

They now have a strategy of using static tables suffixed as "_TMP" for each given business function. Now all the temp stuff for that function goes there, concurrent use. There are about 200 such tables in a typical install, all pre-indexed with avg 4 indexes per table, all empty.

Great idea, they must have thought: spread the load, etc,etc. Problem: this is a financial application. What do financial users do? Begin of month, all budgetting. Mid of month, all invoicing. End of month, all billing.

There goes the spreading of the load down the drain. All they have achieved is to move the load around, not spread it! ;-)

Get 6 concurrent jobs doing billing at end of month, all inserting and deleting from the same table at avg 300Krows per job and boom!: disaster strikes with rlbsegs (and a few other things).

Better way would be to create the table in the job, suffix its name with the job number, build the indexes after the load, do the work and drop the lot at the end. Unfortunately, that will be some future version.

Thanks a lot for the heads-up. I'll be posting soon another example of utlstat for this database, this time for a typical high usage period of the day. It's much more tame now, but still has a few hotspots. Can't figure out what the high "calls to kcmgrs" means. Steve's book and the "kc" point to either buffer management or redo handling. But that's as far as I got.

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Sat May 26 2001 - 06:45:35 CDT