Hi Davey

Once again thanks for your valuable advice

I have noted down a few things but when it comes to statspack more experienced eyes the better

Instance Level

1)Hard Parses are high
2)Rollback per transaction is high
3)Execute to Parse is low

Wait Events
enq: HW - contention
enq: TX - row lock contention
enq: TX - index contention
db file sequential read

are all appearing.
I guess the contention on enques is due to the fact that session are holding locks which other sessions wants and are not commiting.

TX - index contention occurs when many sessions try to insert but they need to wait due to non availibilty of ITL can be alleviated to some extent by increasing PCTFREE on the table

log file parallel write need to check if all the logfiles are on the same disks and the sma econtroller

Logbuffer size is only 14Mb which is small


Code:
SELECT  /*YANTRA*/   LT_RECIPE_ALIAS.* FROM LT_RECIPE_ALIAS LT_
RECIPE_ALIAS     WHERE LT_RECIPE_ALIAS.RECIPE_KEY=  :1
 
SELECT  /*YANTRA*/   LT_RECIPE.* FROM LT_RECIPE LT_RECIPE     W
HERE LT_RECIPE.IMAGE_KEY=  :1

SELECT  /*YANTRA*/   LT_IMAGE_ALIAS.* FROM LT_IMAGE_ALIAS LT_IM
AGE_ALIAS     WHERE LT_IMAGE_ALIAS.IMAGE_KEY=  :1

INSERT   /*YANTRA*/   INTO YFS_AUDIT(AUDIT_KEY,AUDIT_TRAN_KEY,AU
DIT_CONTEXT_ID,REFERENCE_1,REFERENCE_2,REFERENCE_3,REFERENCE_4,R
EFERENCE_5,REFERENCE_6,TABLE_NAME,TABLE_KEY,AUDIT_XML,OPERATION,
LOCKID,CREATETS,MODIFYTS,CREATEUSERID,MODIFYUSERID,CREATEPROGID,
MODIFYPROGID) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13
Is there anything else ?
are my observations right ?