Overview

This explains the concepts and differences between Literal and Bind Variable in SQL.

Method

1. What is Literal SQL?

WHERE clause comparison values are written by directly hardcoding character/number constant values in the SQL statement.

SELECT * FROM EMP WHERE EMPNO = 100;

When different values are entered each time like this, the server recognizes it as a new SQL every time and performs Hard Parsing.

2. What is Bind Variable SQL?

WHERE clause specific values are specified in the form of bind variables (:B) and only the values are passed at execution time.

SELECT * FROM EMP WHERE EMPNO =:B1;

Using bind variables allows the SQL statement to be recognized as the same, enabling Soft Parsing,

and increases Library Cache reuse rate, which helps improve performance.

3. Types of Parsing

🔹 Hard Parsing

• Performed when the SQL statement is not in the Shared Pool / Library Cache.
• Checks SQL syntax, existence of tables and columns, permissions, etc. using Dictionary Cache.
• Consumes a lot of CPU and increases Spin Lock contention.

🔹 Soft Parsing

• Performed when the identical SQL statement already exists in the Shared Pool.
• Reuses the existing parsing tree and execution plan.
• Performance improves the more the same SQL is re-executed.

4. Cases When Hard Parsing Occurs

In the following cases, even with the same meaning, different SQL statements are recognized, causing hard parsing.

Practice Examples

Practice 1: Execute Literal SQL

CREATE TABLE EMP1(EMPNO NUMBER, ENAME VARCHAR(20));
INSERT INTO EMP1 SELECT LEVEL, 'TIBERO' || LEVEL FROM DUAL CONNECT BY LEVEL <= 5;
INSERT INTO EMP1 SELECT LEVEL, 'TIBERO' || LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
COMMIT;

ALTER SYSTEM FLUSH SHARED_POOL;

DECLARE
  v_sql VARCHAR2(500);
  v_cnt NUMBER;
BEGIN
  FOR i IN 1..5 LOOP
    v_sql := 'SELECT /* LITERAL001 */ COUNT(*) FROM EMP1 WHERE EMPNO = ' || i;
    DBMS_OUTPUT.PUT_LINE(v_sql);
    EXECUTE IMMEDIATE v_sql INTO v_cnt;
    DBMS_OUTPUT.PUT_LINE(v_cnt);
  END LOOP;
END;
/

Result:

Each query is recognized differently according to the EMPNO value, resulting in 5 different SQL statements being hard parsed.

Practice 2: Check Literal SQL in V$SQLAREA

SELECT SUBSTR(sql_text,1,200) AS SQL, COUNT(*), SUM(EXECUTIONS)
FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'SELECT /* LITERAL001%'
GROUP BY SUBSTR(sql_text,1,200)
ORDER BY COUNT(*);

Result:

WHERE EMPNO = 1, 2, 3, ... each has COUNT(*) = 1

→ Confirms they are not the same SQL. (Hard parsing occurs)

Practice 3: Execute Bind Variable SQL

DECLARE
  v_sql VARCHAR2(100);
  v_cnt NUMBER;
BEGIN
  FOR i IN 1..5 LOOP
    v_sql := 'SELECT /* BIND001 */ COUNT(*) FROM EMP1 WHERE EMPNO = :i';
    DBMS_OUTPUT.PUT_LINE(v_sql || ' <---- ' || i);
    EXECUTE IMMEDIATE v_sql INTO v_cnt USING i;
    DBMS_OUTPUT.PUT_LINE(v_cnt);
  END LOOP;
END;
/

Result:

All SQL statements are recognized as the same, so parsing occurs once, execution occurs 5 times.

Practice 4: Check Bind Variable SQL in V$SQLAREA

SELECT SUBSTR(sql_text,1,200) AS SQL, COUNT(*), SUM(EXECUTIONS)
FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'SELECT /* BIND001%'
GROUP BY SUBSTR(sql_text,1,200)
ORDER BY 2;

Result:

COUNT(*) = 1, SUM(EXECUTIONS) = 5

→ Recognized as the same SQL, so soft parsing occurs.

Summary

Reference Terms

※ Shared Pool / Library Cache: Memory area that stores and reuses SQL statements, execution plans, PL/SQL, etc.

※ Dictionary Cache: Cache that stores metadata such as objects, users, and privileges

※ Spin Lock: Lightweight lock controlling exclusive access to resources inside the SGA

※ SGA (System Global Area): Memory structure shared by the entire DB instance

※ Hard Parsing: The process of parsing a new SQL statement for the first time and storing it in the cache

※ Soft Parsing: The process of reusing SQL that already exists in the cache