Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> OS-dependent parameters (was: Secret maximum for INITRANS?)

OS-dependent parameters (was: Secret maximum for INITRANS?)

From: Boivin, Patrice J <BoivinP_at_mar.dfo-mpo.gc.ca>
Date: Tue, 05 Mar 2002 07:03:28 -0800
Message-ID: <F001.0041F8C9.20020305070328@fatcity.com>


I sometimes think that every os-dependent parameter in Oracle is secret.

Oracle doesn't publish that information, and the OS vendors usually have no idea or are not likely to publish that info either.

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Systems Admin & Operations | Admin. et Exploit. des systmes
Technology Services        | Services technologiques
Informatics Branch         | Direction de l'informatique 
Maritimes Region, DFO      | Rgion des Maritimes, MPO

E-Mail: boivinp_at_mar.dfo-mpo.gc.ca

 -----Original Message-----

Sent:	Tuesday, March 05, 2002 9:13 AM
To:	Multiple recipients of list ORACLE-L
Subject:	Re: Secret maximum for INITRANS?


I've found the note. It wasn't what I remembered, it was worse. I've just re-run a test on 9.0.1.2 that shows an interesting issue:

create table t1 (n1 number, v1 varchar2(10)); create index i1 on t1(n1) pctfree 0 initrans 10;

insert into t1
select rownum,'x'
from all_objects
where rownum <= 3000;

validate index i1;
select leaf_blk_len from index_stats;

This shows leaf_blk_len = 3904 on my 4K blocks - follow this with a block dump, and you find that the ITL has a total length of 2 on leaf blocks, despite the demand for initrans 10.

alter index rebuild;
validate index i1;
select leaf_blk_len from index_stats;

This shows leaf_blk_len = 3712 on my 4K blocks - follow this with a block dump, and you find that the ITL has a total length of 10 as required.

Comment: don't expect INITRANS to be obeyed if you do an array insert into an empty table until you have proved that it is actually obeyed. There are no doubt more tests you could do to pursue this one and find out exactly when things go wrong. My starting assumption is that it only goes wrong on a new, or truncated, table.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

-----Original Message-----
To: ORACLE-L_at_fatcity.com <ORACLE-L_at_fatcity.com> Date: 05 March 2002 08:26

|
|Nice to know the actual strategy.
|
|I came across an oddity some time ago
|when trying to work this one out by setting
|silly values for INITRANS. This would be
|(correctly) ignored on a 'create index', and
|then obeyed on a 'rebuild index' with the
|result that the index got bigger. I think
|it was 8.1.5 - it doesn't reproduce in 8.1.7.3
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Boivin, Patrice J
  INET: BoivinP_at_mar.dfo-mpo.gc.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Tue Mar 05 2002 - 09:03:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US