Hard parses in a OLTP system(many small transactions) are expensive in both terms of CPU used and number of shared pool latches. Binding reuses cached sql statements, while hard parsing SQL statement always loads a new version of SQL to the shared pool. Bacause each new SQL consumes memory, some already cached statements may need to be “aged out” from shared pool. One of the steps during hard parse is generation of execution plan which is also cached along with the SQL statement itself.

The following select will produce a list of sql that were loaded to the shared pool in last minute. It should be run couple of times during a typical workload.

SELECT * FROM V$SQLAREA
WHERE TO_DATE(FIRST_LOAD_TIME,'YYYY-MM-DD/HH24:MI:SS')
> SYSDATE - INTERVAL '1' MINUTE
/

You can then easily identify sql queries which use literals instead of bind variables. For e.g. “select * from table_name where col1 = 345346456″, “select * from table_name2 where col2 = ‘SOMESTRING’”, etc. These are all probably a result of the following construction: execute immediate ‘SELECT .. WHERE col1=’||N_COL INTO N_XXX; while it is possible to use bind variables using keyword USING. e.g. execute immediate ‘SELECT .. WHERE col1=:1′ USING N_COL INTO N_XXX; In pl/sql this would look like:

-- you get bind when using pl/sql:
DECLARE
N_XXX NUMBER;
N_COL NUMBER := 34;
BEGIN
SELECT some_col INTO N_XXX WHERE col1 = N_COL;
END;
/

Note: When dealing with searches or reports you may want to rethink the use of bind variables(sql and execution plan reuse) as these sql statements possibly scan and fetch huge amount of rows, well not all the time, this depends on the input parameters in your application. You would probably not want to always use the same execution plan in these types of queries.