Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Is Vendor or Customer Responsible for Tuning INITRANS / PCTFREE / PCTUSED ?

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

From: <>
Date: Wed, 24 Oct 2007 13:15:53 -0700
Message-ID: <>

On Oct 24, 11:13 am, 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

Original text of this message