Document Type | Technical Information
Category | Tuning
Document Number | TTUTI016
Overview
This document summarizes the differences between Literal SQL and Bind Variable SQL, and explains the concepts of Hard Parsing and Soft Parsing together.
Using many Literal SQL statements increases the frequency of Hard Parsing, causing SQL statements in the Library Cache to be frequently replaced, which is detrimental to performance. To improve performance, it is helpful to find Literal SQL statements among the SQL statements in the Shared SQL Area and convert them to use Bind Variables.
Method
Comparison of SQL Concepts
- Literal SQL
Literal SQL is SQL where numeric or string values are hardcoded directly in the WHERE clause comparison.
- Bind Variable SQL
Bind Variable SQL specifies the values in the WHERE clause not by hardcoding but by using bind variables (:B).
Comparison of Parsing Concepts
- Hard Parsing
When an SQL statement is executed for the first time and is not found in the Shared Pool/Library Cache, it means a complete new parse is performed.
Tibero allocates the new SQL statement in the Shared Pool, performs SQL syntax checks, object existence verification, permission checks, etc., causing a significant increase in CPU and Spin Lock usage.
- Soft Parsing
If the SQL statement to be executed already exists in the Shared Pool/Library Cache, the existing parse result (parse tree, execution plan) is reused. Therefore, the execution time of most SQL statements is faster the second time than the first.
SQL Statements Subject to Hard Parsing
For soft parsing to occur, the SQL statement must be exactly identical. The following cases are subject to hard parsing.
- Even if the SQL statements are exactly the same, if the schema of the table is different, they are considered different SQL statements.
ex) User A logs in and queries EMP table in schema A: 'select * from emp'
User B logs in and queries EMP table in schema B: 'select * from emp'
- If the whitespace in the SQL statement differs, it is considered a different SQL statement.
ex) 'select * from emp' and 'select * from emp' are different statements.
- Even if the SQL statements are the same, if the lines differ
ex) 'select * from emp' and 'select * from emp' are different statements.
- If the case is different
ex) 'select * from emp' and 'select * from EMP' are different statements.