ITL Waits - Summary of Tablespace

By -
If we saw "ITL Waits" in the report "Summary of Tablespace" section "Statistics", we can use this procedure to diagnostic and resolve that.  We have used this method  in high transactional environment and it worked very well in order to find problems and resolve.


Step 01 - ITL Waits

We run this query  to know which segments have more ITL waits.

  SELECT Owner,
         Object_Name,
         Subobject_Name, Object_Type, Value
    FROM V$Segment_Statistics c
   WHERE     Tablespace_Name = UPPER ('&parTABLESPACE')
         AND Value > 0
         AND Statistic_Name = 'ITL waits'
ORDER BY Value DESC;


Owner        OBJECT_NAME                 SUBOBJECT_NAME      OBJECT_TYPE            VALUE
------------ --------------------------- ------------------- ---------------------  ---------
PTP          THIAT_CONCILIACION_AA_1                          TABLE                 243
PTP          THIAT_CONCILIACION_AA_3                          TABLE                 6

Step 02 - Ini_Trans

and now, we know what segment has experimented ITL waits,  we are going to check INI_TRANS;

SELECT OWNER, TABLE_NAME, INI_TRANS
  FROM dba_tables
 WHERE OWNER = '&OWNER' AND TABLE_NAME = '&TABLE';


Owner        TABLE_NAME                      INI_TRANS
------------ ------------------------------ ----------
PTP          THIAT_CONCILIACION_AA_1                 1


For partitioned tables:

  SELECT TABLE_OWNER,
         TABLE_NAME,
         PARTITION_NAME,
         INI_TRANS
    FROM dba_tab_PARTITIONS
   WHERE TABLE_OWNER = '&OWNER' AND TABLE_NAME = '&TABLE'
ORDER BY PARTITION_NAME;


Step 03 - file#, extent, block

Step 03.1

We need to find a file# and extent so that we can check.

SELECT file_id, block_id "Block Min", block_id + BLOCKS - 1 "Block Max"
  FROM dba_extents
 WHERE owner = '&owner' AND segment_name = '&object' AND ROWNUM < 10;

   FILE_ID  Block Min  Block Max
---------- ---------- ----------
       151     167945     168456
       269     287241     287752
      1101      37385      37896
      2353     311305     311816
      3088     393225     393736
      3088     393737     394248
      3105     338953     339464
      3113     249865     250376
      3244     208905     209416


Step 03.2
Let me put it another way

 
 SELECT DBMS_ROWID.rowid_relative_fno(row_id) File#,
         DBMS_ROWID.rowid_block_number (row_id) Block#,
         COUNT (1)  numrows FROM
         (SELECT rowid row_id
    FROM  PTP.THIAT_CONCILIACION_AA_1
    WHERE ROWNUM <10000)
GROUP BY DBMS_ROWID.rowid_relative_fno (row_id),
         DBMS_ROWID.rowid_block_number (row_id)
ORDER BY 1, 2;

      FILE#     BLOCK#   NUNROWS
---------- ---------- ----------
      3088    393225        135
      3088    393226        136
      3088    393227        134
      3088    393228        135
      3088    393229        135
      3088    393230        135
      3088    393231        135
      3088    393232        136
      3088    393233        135
      3088    393234        136
      3088    393235        135
      3088    393236        135
      3088    393237        135
      3088    393238        135
      3088    393239        135



Step 04 - dump

Add word to trace file, in this way is easier to find   the trace file.

     alter session set tracefile_identifier = CHK_ITL;

Where the trace file is.

     show parameter user_dump_dest

NAME                    TYPE           VALUE
----------------------- -------------- ------------------------------
user_dump_dest          string         /oradump/admin/CCSBD/udump

We do the dump:

     alter system dump datafile 3088 block min 393225 block max 393736;


We can find the trace file, finding the last trace file with our identifier.
For example :
cd /oradump/admin/CCSBD/udump/
ls -ltr *CHK_ITL*.trc
-rw-r--r--   1 oracle   oinstall       0 Mar 16 15:18 pccs_ora_19876_CHK_ITL.trc



Step 04 - ITC Avg

We are going to check how many Slots there are approximately per block. 

Solaris:
egrep -i itc pccs_ora_19876_CHK_ITL.trc | awk '{total+=$6} END {print "Avg:", total/NR}'

 Avg: 4.07738

This is approximate number of concurrent DML transaction per block.


Step 05 - Alter table initrans

We are going to do the change on ini_trans of the table.

ALTER TABLE PTP.THIAT_CONCILIACION_AA_1 INITRANS 4;

This only is the value for new blocks but not for the existing ones.


Partition tables:

ALTER TABLE  owner.table_par MODIFY DEFAULT ATTRIBUTES INITRANS 4;
We will just define the INITRANS value for new partitions.


ALTER TABLE owner.table_par  MODIFY PARTITION  partition_name INITRANS 4;
We will just define the INITRANS value for new blocks, not yet existing.

Step 06 - Move table

For changes all blocks, we have to move the table to another tablespace.

ALTER TABLE PTP.THIAT_CONCILIACION_AA_1  MOVE TABLESPACE DATAPTP_02;

Partition tables:

ALTER TABLE owner.table_par MOVE PARTITION par_name  TABLESPACE tablespace;


Step 07 - Rebuild index

When we moved a table, its indexes became UNUSUABLE then we have to rebuild that, also we have to change ini_trans at least to twice table ini_trans.  Altrough i would rather to do dump of index block.


SELECT OWNER, INDEX_NAME, STATUS FROM DBA_INDEXES WHERE TABLE_OWNER = 'PTP' TABLE_NAME = 'THIAT_CONCILIACION_AA_1' AND STATUS='UNUSABLE';




SELECT 'ALTER INDEX ' || OWNER||'.'|| INDEX_NAME  ||' REBUILD INITRANS 8;' cmd

 FROM DBA_INDEXES  WHERE  OWNER='PSP' AND TABLE_NAME ='THIAT_CONCILIACION_AA_1' AND STATUS='UNUSABLE';

CMD                    
-----------------------
ALTER INDEX PSP.PK_CONCILIACION REBUILD INITRANS 8;



Important:
If we change ini_trans for a table we should do the same study with its indexes.  

Each slot uses 24 bytes therefore when we increase ini_trans reduce free space inside block and increase size of the table and index.


The total size allocated for initial ITLs SHOULD be LESS THAN 50% of the database block size. 
2048 2k = 41
4096 4k = 83
8192  8k = 169

We will appreciate your comments.
Thanks a lot for reading.

Eini Trujillo

Review by @orawill

Comments

Post a Comment