Life with Oracle

  INITTrans  Whenever a DML transaction wants to modify a block , it should inform the block by sending 'interested to modify' message to the block .Technically speaking, a dml transaction will always send message to the block , that it is interested to modify the block . This message is stored in ITL slots ,which is preallocated in the block header. Transactions whose entry is there in this ITL slots can only modify the block.This ITL slots are controlled by INITRANS and MAXTRANS values, the inittrans controls the minimum number of slots that will be allocated in the block header. Honestly speaking, the number of ITL slots is equal to number of DML transactions that can happen parallely at one time. Each ITL slot occupies 24 bytes of space in the block. Inittrans values is 1 for table and 2 for index by default. MAXTrans Maxtrans is the maximum number of ITL slots that can be allocated in the block . When the block runs out of this ITL slots and there is enough free space in ...

HASH JOIN : Hash Joins is one of the method of joining two tables based on the join condition mentioned in the query. It is important to mention the smaller table  in leading position in the sql query when joining two tables. The optimizer always picks the leading table/first table as per the order mentioned in the query.If the execution plan does not pick the smaller table as the leading table then there will be more load/activity at the joining step then the plan which might impact the overall elapsed time of the query. We can explicitly ask the optimizer to pick a particular table as leading table using a oracle hint Ex: /*+ leading(table name)*/  How Hash Join works?  1) First Optimizer scan the smaller table and creates a hash table  by applying hash function on the column to be joined.   2) Now the optimizer scans the second table applies a hash function(applies on the column to be joined) to get the hash value for each record in the second table. ...