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

From: <bfogarty_at_gaports.com>
Date: Wed, 24 Oct 2007 13:15:53 -0700
Message-ID: <1193256953.486026.128970@q3g2000prf.googlegroups.com>

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 Received on Wed Oct 24 2007 - 15:15:53 CDT