Modify INITRANS for Indexes & Tables, How to change INITRANS for existing Index/Table Blocks Options

[Dileep]

Hai.
We have Oracle 8i(8.1.7) database running on True64 V5.1B. We want to migrate it to Oracle 10g R2.
I installed Oracle 10g R2 S/W and did an export and import into Oracle 10g R2 database. The import was successful without any warnings. Auto Segment Space Management in 10g manages PCT_USED and FREELISTS. So this solved FREELISTS problem. But, INITRANS is still same.

1) So, We want to increase the INITRANS parameter for all the TABLES and INDEXES. As we know the default for TABLES is 1 and for INDEXES is 2. If we use ALTER TABLE/INDEX to change the value of INITRANS, it will affect only the NEW BLOCKS (From Oracle Documentation). So, it will not affect or change INITRANS for the existing DATA BLOCKS/INDEX BLOCKS. I would like to know if there is a way to change INITRANS for the existing DATA BLOCKS/INDEX BLOCKS.

2) Is there a way to check these values for all the DATA/INDEX BLOCKS in a SEGMENT(TABLE/INDEX). I mean from some DATA DICTIONARY VIEWS?

3) Is there a way to change


regards,
Dileep Tallam.

[Ben Prusinski Or...]

Hi,

-- I would like to know if there is a way to change INITRANS for the existing DATA BLOCKS/INDEX BLOCKS.

Yes, you can change INITRANS using the ALTER TABLE command.

-- Is there a way to check these values for all the DATA/INDEX BLOCKS in a SEGMENT(TABLE/INDEX). I mean from some DATA DICTIONARY VIEW

Yes, you can query the dictionary level views to get details on tables and indexes for INITRANS values. Below is a good link:

http://www.dba-oracle.com/concepts/data_dictionary.htm

Be sure to also order the free Oracle dictionary poster that lists all of the performance dictionary views for Oracle below is a link:

http://www.rampant-books.com/poster_oracle_database.htm

Also with respect to INITRANS a good article discuss the configurations below link:

http://www.dba-oracle.com/oracle_tips_INIT...S_FREELISTS.htm

[Asad]

is it possible for you to just create tables/index without data from source db; you can get code for object from imp show=y option. later setting whatever values appropriate , you can import data in same tables.

how about dbms_metadata ; you can see all metadata of tables/indexes including storage parameters...

btw; what is current initrans value for index/tables in your db - is it constant in all objects? why you want to increase it and have you calculated the next higher value ; if so kindly share here.

thanks

Hai.
We have Oracle 8i(8.1.7) database running on True64 V5.1B. We want to migrate it to Oracle 10g R2.
I installed Oracle 10g R2 S/W and did an export and import into Oracle 10g R2 database. The import was successful without any warnings. Auto Segment Space Management in 10g manages PCT_USED and FREELISTS. So this solved FREELISTS problem. But, INITRANS is still same.

1) So, We want to increase the INITRANS parameter for all the TABLES and INDEXES. As we know the default for TABLES is 1 and for INDEXES is 2. If we use ALTER TABLE/INDEX to change the value of INITRANS, it will affect only the NEW BLOCKS (From Oracle Documentation). So, it will not affect or change INITRANS for the existing DATA BLOCKS/INDEX BLOCKS. I would like to know if there is a way to change INITRANS for the existing DATA BLOCKS/INDEX BLOCKS.

2) Is there a way to check these values for all the DATA/INDEX BLOCKS in a SEGMENT(TABLE/INDEX). I mean from some DATA DICTIONARY VIEWS?

3) Is there a way to change
regards,
Dileep Tallam.

[burleson]

Hi,

YOU ONLY increase INITRANS when you have the specific waits assocuated with this type of contention. Read this carefully:

http://www.dba-oracle.com/t_initrans.htm

Eliminating data block contention involves eliminating “hot” data  blocks from the application, and super-high concurrent DML can cause block contention that will be relieved by increasing INITRANS for the effected tables and indexes. 

[Dileep]

>> YOU ONLY increase INITRANS when you have the specific waits assocuated with this type of contention. Read this carefully:

http://www.dba-oracle.com/t_initrans.htm

Eliminating data block contention involves eliminating “hot” data  blocks from the application, and super-high concurrent DML can cause block contention that will be relieved by increasing INITRANS for the effected tables and indexes. 
[/quote]


Hai Mr.Burleson,
As the database version is 8i, I dont have v$segment_statistics or as such to check the ITL waits. Actually, we are facing ORA-00060. Most of the times I found a particular table CUST_BILL_MAST in the trace files from udump directory. Some times other tables too.

Also, the application is getting freezed sometimes and we are forced to kill those sessions. I dont find ORA-00060 during that time. I dont know what to do as I cant capture as its not an event. There is no error in alertlog. This is happening at one of the client's place. The DBA operator is just killing the sessions. I told to capture the SQL and also take a system state dump when that freezing occurs. That has not happened since few days. Meanwhile we thought to incrase the INITRANS for all the TABLES. Aslo, As we are migrating to 10g and as we have AUTO SEGMENT SPACE MANAGEMENT, we dont want to worry about FREELISTS.

So, we want too increase INITRANS and check it.

Also, Is there a way to find out ITL Waits or is there something like V$SEGMENT_STATISTICS in Oracle 8i to check the actual contention? Ofcourse once we move to Oracle10g, there are rich features to find out the contention and which help to resolve the issue easily.


regards,
Dileep Tallam.

[burleson]

Hi Dileep,

>> So, we want too increase INITRANS and check it.

OK, but you can see the waits in STATSPACK:

You can also see ITL waits in a STATSPACK or AWR report, in the segments section we see this section: Segments by ITL Waits: Includes segments that had a large contention for Interested Transaction List (ITL). The contention for ITL can be reduced by increasing INITRANS storage parameter of the table.

*******************************************************

I made this page just for you:

http://www.dba-oracle.com/t_initrans.htm

Arup Nanda has scripts for detecting ITL waits:

Select
s.sid SID,
s.serial# Serial#,
l.type type,
' ' object_name,
lmode held,
request request
from
v$lock l,
v$session s,
v$process p
where
s.sid = l.sid and
s.username <> ' ' and
s.paddr = p.addr and
l.type <> 'TM' and
(l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select
s.sid SID,
s.serial# Serial#,
l.type type,
object_name object_name,
lmode held,
request request
from
v$lock l,
v$session s,
v$process p,
sys.dba_objects o
where
s.sid = l.sid and
o.object_id = l.id1 and
l.type = 'TM' and
s.username <> ' ' and
s.paddr = p.addr
union
select
s.sid SID,
s.serial# Serial#,
l.type type,
'(Rollback='||rtrim(r.name)||')' object_name,
lmode held,
request request
from
v$lock l,
v$session s,
v$process p,
v$rollname r
where
s.sid = l.sid and
l.type = 'TX' and
l.lmode = 6 and
trunc(l.id1/65536) = r.usn and
s.username <> ' ' and
s.paddr = p.addr
order by 5, 6;