Expert Oracle Posts

Bind variables are often known as one of the key feature for better SQL query performance. Bind variables as per Oracle documentation is a placeholder in a SQL statement that must be replaced with a valid value or value address for the statement to execute successfully. By using bind variables, you can write a SQL statement that accepts inputs or parameters at run time.

You can think of SQL query as a kind of “function” in any programming language and bind variables as “values” that you pass to the function.

Example:

Select * from EMP where EMP_ID=1;
Select * from EMP where EMP_ID=:a;

First statement uses a literal value (1) to run the query while the second SQL statement uses bind variable (:a) to run the SQL statement. The value of (:a) will be provide to Oracle at run time.

A “cursor” is a memory area in the library cache that is allocated to the SQL statement which users execute. This memory area stores key information about the SQL statement like SQL text, SQL execution plan, statistics etc.

From performance tuning perspectives, it is very important to understand the concept of parsing. Parsing is primary phase in the SQL execution. It is followed by other stages: Execute and Fetch.

PARSING BASICS:

Whenever a SQL statement is executed, Oracle Engine performs below actions on it:

1. Validate the Syntax
2. Validate the objects being referenced in the statement
3. Privileges assigned to user executing the Job
4. Verifying if statement is already available in the shared pool. Oracle engine will calculate the hash value for the SQL statement and look in
5. If statement is not already present then allocate shared memory and create a cursor in shared pool
6. Generate the Execution Plan

Performance tuning is one area where most Junior DBAs face those tough-to-break problems. The kind of problems which require deep understanding of the concepts before…