Document Type | Technical Information
Category | Tuning
Applicable Product Version | Tibero4 or higher
Document Number | TTUTI033
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.
| Category | Description |
|---|---|
| Different Schema | If the schema is different, it is recognized as a different SQL. |
| Different Whitespaces | Whitespace differences also count as different SQL. |
| Different Line Breaks | Different line positions are recognized as different SQL. |
| Case Sensitivity | Case is distinguished. |
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
| Category | Literal SQL | Bind Variable SQL |
|---|---|---|
| Value Handling Method | Direct hardcoding of values | Passed as bind variables |
| SQL Recognition | Recognized as different SQL each time | Recognized as the same SQL |
| Parsing Method | Hard Parsing | Soft Parsing |
| Cache Utilization | Low | High |
| Performance | Slow (CPU load, Spin Lock increase) | Fast (Reuse efficiency โ) |
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