Skip to main content

Search

Welcome to Tibero GTS!

Bind Variable SQL vs Literal SQL Performance Comparison Example

Document Type | Technical Information

Category | Tuning

Document Number | TTUTI017

 

Overview

This guides you through the differences between Literal SQL and Bind Variable SQL and compares their performance through examples.
 
Using many literal SQL statements increases the frequency of Hard Parsing, and SQL statements are frequently replaced in the Library Cache, which negatively impacts performance. To improve performance, you should identify literal SQL statements in the Shared SQL Area and convert them to use Bind Variables.
 
 

Method

Literal SQL Example

This is an example of literal SQL where the values in the WHERE clause differ. They are recognized as different SQL statements, causing hard parsing. Flushing the SHARED_POOL clears the Shared SQL Area and PSM area (SQL statements, stored procedures, functions, packages, and triggers). It also deletes the dictionary cache loaded in the SHARED POOL, causing SQL execution to require hard parsing.
SQL> CREATE TABLE EMP1(EMPNO NUMBER, ENAME VARCHAR(20));
SQL> INSERT INTO EMP1 SELECT LEVEL,'TIBERO'||LEVEL FROM DUAL CONNECT BY LEVEL <=5; SQL> INSERT INTO EMP1 SELECT LEVEL,'TIBERO'||LEVEL FROM DUAL CONNECT BY LEVEL <=3; SQL> COMMIT;
SQL> SELECT * FROM EMP1;
EMPNO ENAME
---------- --------------------
1 TIBERO1
2 TIBERO2
3 TIBERO3
4 TIBERO4
5 TIBERO5
1 TIBERO1
2 TIBERO2
3 TIBERO3
SQL> ALTER SYSTEM FLUSH SHARED_POOL; SQL> SET SERVEROUTPUT ON
SQL> 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;
/
SELECT /* LITERAL001 */ COUNT(*) FROM EMP1
WHERE EMPNO = 1
2SELECT /* LITERAL001 */ COUNT(*)
FROM EMP1
WHERE EMPNO = 2
2
SELECT /* LITERAL001 */ COUNT(*)
FROM EMP1
WHERE EMPNO = 3
2
SELECT /* LITERAL001 */ COUNT(*)
FROM EMP1
WHERE EMPNO = 4
1
SELECT /* LITERAL001 */ COUNT(*)
FROM EMP1
WHERE EMPNO = 5
1

 

You can query information about executed queries from V$SQLAREA. All SQL statements executed earlier are recognized as different SQL statements and have undergone hard parsing.

SQL> col sql for a40
SQL> SELECT SUBSTR(sql_text,1,200) "SQL", COUNT(*), SUM(EXECUTIONS) FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'SELECT /* LITERAL001%' GROUP BY SUBSTR(sql_text,1,200)
HAVING COUNT(*) > 0 ORDER BY COUNT(*);
SQL
COUNT(*) SUM(EXECUTIONS)
---------------------------------------- ---------- ---------------
SELECT /* LITERAL001 */ COUNT(*)                 1               1
FROM EMP1
WHERE EMPNO = 4
SELECT /* LITERAL001 */ COUNT(*)                 1               1
FROM EMP1
WHERE EMPNO = 3
SELECT /* LITERAL001 */ COUNT(*)                 1               1
FROM EMP1
WHERE EMPNO = 1SELECT /* LITERAL001 */ COUNT(*) FROM EMP1
WHERE EMPNO = 2
11
SELECT /* LITERAL001 */ COUNT(*)                 1               1
FROM EMP1
WHERE EMPNO = 5

 

Bind Variable SQL Example

This time, the query is executed using bind variables. This is the same example as the literal SQL example but processed with bind variables in the WHERE clause.
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;
/
SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i <---- 1
2
SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i <---- 2
2
SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i <---- 3
2
SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i <---- 4
1
SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i <---- 5
1


The SQL statements executed above are processed as bind variables, recognized as the same SQL statement, so soft parsing occurs. Parsing was performed only once, while execution was performed five times.

SQL> COL SQL FOR A55
SQL> SET LINESIZE 90
SQL> SELECT SUBSTR(sql_text,1,200) "SQL", COUNT(*), SUM(EXECUTIONS) 
FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'SELECT /* BIND001%' 
GROUP BY SUBSTR(sql_text,1,200) 
HAVING COUNT(*) > 0
ORDER BY 2;

SQL                                                     COUNT(*)   SUM(EXECUTIONS)
------------------------------------------------------- ---------- ---------------
SELECT /* BIND001 */COUNT(*) FROM EMP1 WHERE EMPNO = :i          1               5

1 row selected.

 

 

 

Related to