Oracle – why do I need INITRANS if I have MAXTRANS

oracleperformanceplsql

Probably is a dummy question, but I still don't understand why do I need the INITRANS parameter if I already have the MAXTRANS parameter. I have understand that, the default INITRANS for tables is 1 and index is 2, but also, we have the MAXTRANS that the default is 255 (I guess it depend of the block size). and, because we have 255 in MAXTRANS, the number of ITL can grow dynamically as needed, so, why do I need to care about INITRANS? can someone explain it?

I need to learn because I am having several event in a database: enq: TX - allocate ITL entry

Also, when I change the INITRANS of an existing table, do I need to move the table as well to affect all existing data block in that table? or just change the INITRANS is enough?

Best Answer

ITL cannot grow in a block that is already full.

INITRANS sets a guaranteed minimum to its size.

After changing INITRANS on a table, you need to move it so its blocks will be reorganized to reserve the required space for ITL.

Related Solutions

Oracle Trace File Interpretation: enq: TX – index contention

OBJ#=-1 means either means that the object number is missing or it's not related to an object. The best idea is to go into V$active_session_history and find the SQL_ID then get the SQL text via the SQL_ID and look at the object sin the SQL and what the SQL was doing.

As far as people suggesting INIT TRANS, that is unlikely as there is a separate wait event for INIT TRANS.

USN, SLOT and SEQUENCE map the UNDO entry for the transaction. They could be used to map back to activity in the UNDO but it's probably easier to get the transaction id the XID and use VERSIONS_XID on the objects involved.

see http://www.slideshare.net/khailey/ukoug-oracle-transaction-locks

Example ASH query

col object for A15
col otype for A10
select
      substr(event,0,20)    lock_name,
      ash.session_id        waiter,
      mod(ash.p1,16)        lmode,
      --ash.p2                p2,
      --ash.p3                p3,
      o.object_name           object,
      o.object_type           otype,
      CURRENT_FILE#           filen,
      CURRENT_BLOCK#          blockn,
      ash.SQL_ID             waiting_sql,
      BLOCKING_SESSION        blocker
      --,ash.xid
from
        v$active_session_history ash,
        all_objects o
where
          event like 'enq: TX - index contention'
  and o.object_id (+)= ash.CURRENT_OBJ#
/

Cannot set MEMORY_TARGET for Oracle 11.2

First look what other parameters are set:

CREATE pfile='pfile.txt' FROM spfile;

Maybe db_cache_size, shared_pool_size, sga_target or other memory related parameters are set to non zero? Remember that when using AMM those parameters specify minimum memory allocated for particular pool. So if sga_target is 6GB you will not be allowed to set memory_target to 4GB.

Also sum of internal variables __sga_target, __db_cache_size, etc. may be more than your specified value of 4GB.

If you see those symptoms you can cleanup pfile bounce Oracle with pfile and recreate spfile. In the same step you can also set PGA_AGGREGATE_TARGET to zero.

STARTUP pfile='pfile.txt'
CREATE spfile from pfile='pfile.txt';

Best Answer

Related Solutions

Oracle Trace File Interpretation: enq: TX – index contention

Cannot set MEMORY_TARGET for Oracle 11.2

Related Question