Cursors allocation when using literals and bind variables

Cursors allocation when using literals and bind variables:

=====================================================

Using literals in query:

SQL> conn ramesh

Enter password:

Connected.

create table test_emp as select * from scott.emp;

SQL> SELECT SUM(sal) FROM ramesh.test_emp WHERE empno < 7401;

  SUM(SAL)

----------

       800

SQL> SELECT SUM(sal) FROM ramesh.test_emp WHERE empno < 7520;

  SUM(SAL)

----------

      2400

SQL> SELECT SUM(sal) FROM ramesh.test_emp WHERE empno < 7965;

  SUM(SAL)

----------

     29025

SQL> COL SQL_TEXT FORMAT a30

SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE

FROM   V$SQLAREA

WHERE  SQL_TEXT LIKE '%test_emp%'

AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE

------------------------------ ------------- ------------- ----------

SELECT SUM(sal) FROM ramesh.ci 9qxus586mw2mv          1  222169723

ber_emp WHERE empno < 7965

SELECT SUM(sal) FROM ramesh.ci abzq01nbt0tqv          1  395339483

ber_emp WHERE empno < 7401

SELECT SUM(sal) FROM ramesh.ci gyjqpv8h7v4wh          1  545100688

ber_emp WHERE empno < 7520

The above three queries generated three different hash values, it means created three different cursors, and since three queries are different syntactically, these statements will be parsed individually so three times parsing.

Instead using literals use bind variable

=========================================

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

SQL> VARIABLE emp_id NUMBER

SQL> EXEC :emp_id := 7401;

PL/SQL procedure successfully completed.

SQL> SELECT SUM(sal) FROM ramesh.test_emp WHERE empno < :emp_id;

  SUM(SAL)

----------

       800

SQL> EXEC :emp_id := 7520;

PL/SQL procedure successfully completed.

SQL> SELECT SUM(sal) FROM ramesh.test_emp WHERE empno < :emp_id;

  SUM(SAL)

----------

      2400

SQL> EXEC :emp_id := 7965;

PL/SQL procedure successfully completed.

SQL> SELECT SUM(sal) FROM ramesh.test_emp WHERE empno < :emp_id;

  SUM(SAL)

----------

     29025

SQL> COL SQL_TEXT FORMAT a30

SELECT SQL_TEXT, SQL_ID, VERSION_COUNT, HASH_VALUE

FROM   V$SQLAREA

WHERE  SQL_TEXT LIKE '%test_emp%'

AND    SQL_TEXT NOT LIKE '%SQL_TEXT%';

SQL_TEXT               SQL_ID        VERSION_COUNT HASH_VALUE

------------------------------ ------------- ------------- ----------

SELECT SUM(sal) FROM ramesh.ci c97hqp4s49v03         1  809823235

ber_emp WHERE empno < :emp_id

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('c97hqp4s49v03',0));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID     c97hqp4s49v03, child number 0

-------------------------------------

SELECT SUM(sal) FROM ramesh.test_emp WHERE empno < :emp_id

Plan hash value: 2933903646

--------------------------------------------------------------------------------

| Id  | Operation       | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |           |       |       |     3 (100)|        |

|   1 |  SORT AGGREGATE    |           |     1 |     8 |       |        |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|*  2 |   TABLE ACCESS FULL| TEST_EMP |     1 |     8 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("EMPNO"<:EMP_ID)