SQL & PL/SQL

Team,

I have been doing stress test for my application and after looking at the top activity tab in the ORacle OEM , I have found out few recommendations. Could you please assist me in implementing the recommendations.

All our Indexes have ini_trans has 2 and max_Trans as 255.

Assuming we have base index IDX_CO_USRPASS on scott.emp table for column hiredate please provide your suggestion in this case.

Recomendation 1:

Consider increasing INITRANS for the INDEX "IDX_CO_USRPASS" with object ID 125068 without rebuilding it.

The SQL statement with SQL_ID "f2gfdy1mdjdz9" was found waiting for the Interested Transaction List (ITL) enqueue on the INDEX "IDX_CO_USRPASS" with object ID 125068.

Recommendations :2

Consider rebuilding the INDEX "IDX_CO_USRPASS" with object id 125068 using a higher value for INITRANS.

The SQL statement with SQL_ID "f2gfdy1mdjdz9" was found waiting for the Interested Transaction List (ITL) enqueue on the INDEX "IDX_CO_USRPASS" with object ID 125068.

Recommendation :3

Consider hash partitioning the INDEX "IDX_CO_USRPASS" with object ID 125068 in a manner that will evenly distribute concurrent DML across multiple partitions.

The SQL statement with SQL_ID "f2gfdy1mdjdz9" was found waiting for the Interested Transaction List (ITL) enqueue on the INDEX "IDX_CO_USRPASS" with object ID 125068.

THe above recommendations were suggested during an insert happening into the table.

Hope this index info from user_indexes data dictionary would be useful in assisting me further:

Thanks