Bind variables - The key to application performance
To understand bind variables, consider an application that generates thousands of SELECT statements against a table; for example: SELECT fname, lname, pcode FROM cust WHERE id = 674; SELECT fname, lname, pcode FROM cust WHERE id = 234; SELECT fname, lname, pcode FROM cust WHERE id = 332; Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is know as a « hard parse » and for OLTP applications can actually take longer to carry out that the DML instruction itself. When looking for a matching statement in the shared pool, only statements that exactly match the text