Re: Is Vendor or Customer Responsible for Tuning INITRANS / PCTFREE / PCTUSED ?

On Oct 24, 1:15 pm, bfoga..._at_gaports.com wrote:
> On Oct 24, 11:13 am, bfoga..._at_gaports.com wrote:
>
>
>
>
>
> > A software package we purchased from a third-party vendor occasionally
> > gets "ITL waits". Sometimes the session that waits will hang for an
> > extended period of time. The database is 10gR2. I used v
> > $segment_statistics to determine how many "ITL wait" events have
> > occurred in the various segments.
>
> > I reported this to the vendor and asked for their recommendation on
> > what changes to make to INITRANS and possibly to PCTFREE and PCTUSED.
> > (When creating tables and indexes they took the default values of 1
> > for tables and 2 for indexes where INITRANS is concerned.) The vendor
> > said that it is up to me to decide on what changes to make to these
> > parameters. They say that ITL waits are not an issue at other sites
> > and since our volume of work is much higher than that of most other
> > customers' it is our responsibility to tune these settings. I respond
> > that since many other sites do not have a DBA on staff they do not
> > know if ITL waits are an issue for other customers. Our software
> > vendor no longer has an Oracle DBA on staff.
>
> > I will probably end up making these changes without assistance from
> > the vendor.
>
> > I would like your opinion on who is responsible for tuning INITRANS.
> > Is it the DBA of the customer or the vendor?
>
> > I do not want to spend a lot of time trying to get a near perfect
> > setting for INITRANS. Is there a quick way to approximate an
> > acceptable setting?
>
> > Thank you,
> > Bill
>
> Thanks for the supportive opinions. It is doubtful that the vendor
> will offer any guidance here but at least I have confidence in my
> position.
>
> How about my question on determining a value for INITRANS?
>
> In general, Oracle recommends taking the default values. For large
> tables with few users they suggest a "low" number. For tables with
> many users you might consider a "high" number. They do not say what
> numbers are considered "low" or "high". I know the maximum is 255. I
> was considering 5 or 10. Somewhere I read that the value for an index
> should be at least one more than that of its associated table. I also
> read that INITRANS should be a prime number. Around these parts we say
> "That's as clear as mud."
>
> So, how do I know what are good starting values for INITRANS for
> tables and indexes?
>
> Thank you,
> Bill

Quoting from Practical Oracle 8i: "It is usually very difficult to determine if you have unsuitable values for INITRANS and MAXTRANS. If INITRANS is too low on some tables, you are likely to see large numbers of TX enqueue waits, but these are actually quite hard to spot."

I think 9i made it easier to spot, as you've seen, and increased the defaults for those, which simply might waste a bit of space in some blocks if it is too high. Obviously, you are seeing an effect, so you need to adjust it up. You could take block dumps and see what is really happening (google an oracle-l thread: "Enqueue TX level 4 wait -- blocks dump", among other examples), but that would be time consuming. Also see http://www.dbazine.com/oracle/or-articles/nanda3 .

As far as the vendor being responsible for telling you what the values should be, I think it is just far too much to expect any vendor to be able to tell exactly what every possible use of their product will be, and the effect of twiddling any particular knob. Tuning just doesn't work that way, it is empirical and iterative, too many variables to solve any exact equations - which also applies to answers we give here. This is different than the example Brian gave of a vendor saying to restart the database. That is just a problem with the interaction between the developers and the support staff at the vendor (ie, "We developers won't work on any problem unless you prove it first"), that's why we as customers have to develop demonstrations of specific problems for the unqualified support people. Sybrands remarks are, sadly, all too true.

jg

--
@home.com is bogus.
http://www.itweb.co.za/sections/business/2007/0710161034.asp?S=IT%20in%20Defence&A=DFN&O=FRGN