Initrans

I was looking at Anand Blog and thought to mention one thing.

I am using 11.2.0.2 Version and here default initrans is 1. Also, the thing i want to mention is when you increase this parameters older blocks are not affected only newly created blocks after this change would be affected.

Taral@Taral1> drop table test1 purge;

Table dropped.

Taral@Taral1> create table test1(col1 number);

Table created.

Taral@Taral1> select owner,table_name,INI_TRANS,MAX_TRANS from dba_tables where owner='TARAL';

OWNER                          TABLE_NAME                      INI_TRANS  MAX_TRANS
------------------------------ ------------------------------ ---------- ----------
TARAL                          TEST1                                   1        255

Taral@Taral1> insert into test1 select rownum r from dual connect by level<=10000; 10000 rows created. Taral@Taral1> commit;

Commit complete.

Taral@Taral1> select segment_name,tablespace_name,header_file, header_block from dba_segments where segment_name='TEST1';

SEGMENT_NAME                                                                      TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK
--------------------------------------------------------------------------------- ------------------------------ ----------- ------------
TEST1                                                                             USERS                                    4          522

Taral@Taral1> select SEGMENT_NAME,BLOCK_ID,FILE_ID,TABLESPACE_NAME from dba_extents where SEGMENT_NAME='TEST1';

SEGMENT_NAME                                                                        BLOCK_ID    FILE_ID TABLESPACE_NAME
--------------------------------------------------------------------------------- ---------- ---------- ------------------------------
TEST1                                                                                    520          4 USERS
TEST1                                                                                    528          4 USERS
TEST1                                                                                    536          4 USERS

Taral@Taral1>
Taral@Taral1> alter system dump datafile 4 block 528;

System altered.

Block header dump:  0x01000210
 Object id on Block? Y
 seg/obj: 0x127c1  csc: 0x00.d0319  itc: 2  flg: E  typ: 1 - DATA
     brn: 1  bdba: 0x1000208 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.002.000002ee  0x00c02ec2.008d.02  --U-  660  fsc 0x0000.000d037d
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01000210
data_block_dump,data header at 0xf298264

Taral@Taral1> alter table test1 initrans 5;

Table altered.

Taral@Taral1> insert into test1 select rownum r from dual connect by level<=10000; 10000 rows created. Taral@Taral1> commit;

Commit complete.

Taral@Taral1> select SEGMENT_NAME,BLOCK_ID,FILE_ID,TABLESPACE_NAME from dba_extents where SEGMENT_NAME='TEST1';

SEGMENT_NAME                                                                        BLOCK_ID    FILE_ID TABLESPACE_NAME
--------------------------------------------------------------------------------- ---------- ---------- ------------------------------
TEST1                                                                                    520          4 USERS
TEST1                                                                                    528          4 USERS
TEST1                                                                                    536          4 USERS
TEST1                                                                                    544          4 USERS
TEST1                                                                                    552          4 USERS

Taral@Taral1> alter system dump datafile 4 block 544;

System altered.

Block header dump:  0x01000220
 Object id on Block? Y
 seg/obj: 0x127c2  csc: 0x00.db29d  itc: 5  flg: E  typ: 1 - DATA
     brn: 1  bdba: 0x1000218 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0007.00b.00000306  0x00c00861.009a.05  --U-  654  fsc 0x0000.000db2a5
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x04   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x05   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01000220
data_block_dump,data header at 0xf2982ac

Taral@Taral1> alter system dump datafile 4 block 528;

System altered.

Block header dump:  0x01000210
 Object id on Block? Y
 seg/obj: 0x127c2  csc: 0x00.d03e4  itc: 2  flg: E  typ: 1 - DATA
     brn: 1  bdba: 0x1000208 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.015.000002fd  0x00c00112.00a4.0c  --U-  660  fsc 0x0000.000d03ec
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01000210
data_block_dump,data header at 0xf298264

Blocks already allocated are not affected but newly created blocks will take new settings.

How To Provide Hint

Today Learned new way to provide hint in oracle. May be other guys know this but i didn’t know this and one of our developer showed me. Thought to give a shot on this. Anyways, generally we provide hint with /*+..*/ Format. But see this

Taral@Taral1> drop table test1 purge;

Table dropped.

Taral@Taral1> create table test1 as select * from dba_objects;

Table created.

Taral@Taral1> insert --+ append
  2  into test1 select * from dba_objects;

73253 rows created.

Taral@Taral1> select count(*) from test1;
select count(*) from test1
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Taral@Taral1> commit;

Commit complete.

Taral@Taral1> select count(*) from test1;

  COUNT(*)
----------
    146506

============
Plan Table
============
------------------------------------------------------+-----------------------------------+
| Id  | Operation                        | Name       | Rows  | Bytes | Cost  | Time      |
------------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT                 |            |       |       |   261 |           |
| 1   |  LOAD AS SELECT                  |            |       |       |       |           |
| 2   |   VIEW                           | DBA_OBJECTS|   68K |   14M |   261 |  00:00:04 |
| 3   |    UNION-ALL                     |            |       |       |       |           |
| 4   |     TABLE ACCESS BY INDEX ROWID  | SUM$       |     1 |     9 |     1 |  00:00:01 |
| 5   |      INDEX UNIQUE SCAN           | I_SUM$_1   |     1 |       |     0 |           |
| 6   |     TABLE ACCESS BY INDEX ROWID  | OBJ$       |     1 |    30 |     3 |  00:00:01 |
| 7   |      INDEX RANGE SCAN            | I_OBJ1     |     1 |       |     2 |  00:00:01 |
| 8   |     FILTER                       |            |       |       |       |           |
| 9   |      HASH JOIN                   |            |   72K | 8828K |   258 |  00:00:04 |
| 10  |       TABLE ACCESS FULL          | USER$      |    93 |  1581 |     3 |  00:00:01 |
| 11  |       HASH JOIN                  |            |   72K | 7598K |   255 |  00:00:04 |
| 12  |        INDEX FULL SCAN           | I_USER2    |    93 |  2046 |     1 |  00:00:01 |
| 13  |        TABLE ACCESS FULL         | OBJ$       |   72K | 6006K |   253 |  00:00:04 |
| 14  |      TABLE ACCESS BY INDEX ROWID | IND$       |     1 |     8 |     2 |  00:00:01 |
| 15  |       INDEX UNIQUE SCAN          | I_IND1     |     1 |       |     1 |  00:00:01 |
| 16  |      NESTED LOOPS                |            |     1 |    29 |     2 |  00:00:01 |
| 17  |       INDEX SKIP SCAN            | I_USER2    |     1 |    20 |     1 |  00:00:01 |
| 18  |       INDEX RANGE SCAN           | I_OBJ4     |     1 |     9 |     1 |  00:00:01 |
| 19  |     NESTED LOOPS                 |            |     1 |   105 |     3 |  00:00:01 |
| 20  |      TABLE ACCESS FULL           | LINK$      |     1 |    88 |     2 |  00:00:01 |
| 21  |      TABLE ACCESS CLUSTER        | USER$      |     1 |    17 |     1 |  00:00:01 |
| 22  |       INDEX UNIQUE SCAN          | I_USER#    |     1 |       |     0 |           |
------------------------------------------------------+-----------------------------------+

Dumping Hints
=============
  atom_hint=(@=29F46C58 err=0 resol=0 used=1 token=814 org=1 lvl=1 txt=APPEND ())

To Further confirm this hint is been used. See the PLAN and last part. It says it’s used. And this will work with mostly all kind of hints. Everyday new learning.