Point below already covered by Tanel rely lower on list which I had missed before appending.
The initial post said this was an ASSM tablespace so the inittrans parameter should not be an issue since this would be an ASSM handled task.
HTH -- Mark D Powell --
Edited by: MarkDPowell on Feb 24, 2009 5:10 PM
If you have uniform extent size then the only way for making the extents larger is to create another tablespace with larger extent size as you said and moving the segments there.
A temporary workaround for your problem could be that you preallocate extents to the segments involved in the concurrent inserts (with alter table ... allocate extent).
The inittrans parameter should not be directly related to ASSM as they manage different things. (ASSM manages space inside blocks, initrans just initial number of ITL entries...)
Tanel, Automatic Segment Space Management, ASSM, pretty much takes over managment of all table paramters other than pctfree including how many ITL's are allocated to a block. The initran parameter is ignored in ASSM tablespaces. The OP had asked about the setting but since the first post said it was an ASSM tablespace the parameter does not apply. I posted before I saw you later post which was at the time on the very bottom of the thread and I had stopped one short and it seems without going back to check that you told the OP to not worry about initrans in that reply.
HTH -- Mark D Powell --
ASSM only takes care of PCTUSED and removes the FREELIST & FREELIST GROUP parameters. The MAXTRANS parameter setting is ignored and always defaults to 255 since 9i I think, but that's not ASSM specific, it's the same with freelists.
The INITRANS is not ignored neither with freelists nor ASSM. Otherwise how would Oracle know how much space to leave for ITL entries in case of many concurrent transactions per block.
The min value of INITRANS is nowadays 2 for conventional path created ("newed") datablocks and 3 for direct path load initialized datablocks.
If you manually set INITRANS higher, Oracle creates more ITL entries, so INITRANS is not ignored. You can easily test it by creating couple of tables with different INITRANS, inserting a row to intialize the datablock and then dump the block (and look for itc variable or just count the list of ITL entries seen.
Thanks for your input.. It was extremly useful.
PS. : Your wait profile script is awesome.. It's so useful that I now use SQL trace less frequently ..[ I know , it still has its place ]. I can easily nominate that for the "greatest script/tool for the YEAR' if there is any such nomination... Thanks for sharing that with Oracle community.
There's a reason why I write these scripts. I want to make my life easier ;-)
And I sure am glad if others benefit from this as well !!! So there's double win for me :)
I also don't use sql_trace that much, at least for first round troubleshooting. SQL_trace still does have a place, in some cases its irreplacable, but it has two shortcomings:
1) Need to enable SQL_TRACE in order to get any output. This is slower than just running a plain SQL script AND in some shops you need to go through formal change control procedures before you can enable the trace
2) You need to log in to the server, get the tracefile, post-process it. This is slow. In some shops DBAs do not have access to Unix/Windows boxes at all (as this is the sysadmins domain).
So I prefer simpler approaches first - if I can diagnose a problem with a SQL, I prefer that. That's why there's Snapper, WaitProf, LatchProfX, BufProf, sample.sql etc :)
Yes. I preallocated the extents and that took care of the contention..
On a silghtly diffrent topic but related to smaller uniform exent size.. suppose if I have 128K extent size on a tablespace that host a huge table (100GB)
What would be impact with performing FTS with MBRC=16 and BLKSIZE=16K? Here my MBRC can't be higher than 128K. i.e. more smaller (8 block ) reads.. but with today's smart storage ( caching and readahead ) , should it matter much? I mean , it's worthwhile to move all these mostly readonly objects to tablespace with larger extent size ( at least 256K ... )
If this hasn't changed in recent releases, Oracle doesn't issue multiblock IO which spans extend boundaries. So with small extents you have more physical read operations + read syscalls which translates to higher CPU usage (and perhaps higher cache buffers lru chains latch contention if that's an issue) even if some readahead is performed at OS/storage level.