Simple insert statement with bind variable is hard parsing!
We have this following insert (column /table names changed) :
INSERT INTO TABLE1 (A, B,C, D, E, F.........Z) (SELECT A, B, C, D, E,F,........Z FROM TABLE2 WHERE ROWID = :1 )
where :1 is the row id bind variable.
This insert statement is executed 100s of times in a minute (concurrent connections with various bind values). When I check the performance, using spotlight, it shows that these statements are hard parsed every time. Due to this our overall time for the batch is doubled. I don't understand why it's hard parsing even with a bind variable. The only thing is that this statement is sent from java and obviously whatever value (of row id ) that is sent in the bind variable is being converted to CHARTOROWID by oracle (when I view the exec. plan). We even introduced row id