Oracle SQL Processing, Shared Pool and Bind Variables

Hi everyone,

In this article, we query Oracle’s how it works, what steps are processed. I will give an information about how to write query that having good performance. In this context, the structure of Oracle Shared Pool and Explain this case by giving examples that the performance of queries that use the bind variable.

SQL Processing

http://emrahmete.files.wordpress.com/2011/11/oraclesqlproc.jpgOracle SQL processing operation by following the steps implemented in the figure. This process we explain it briefly.

Create Cursor:In this step, an area of ​​memory allocated to SQL and SQL related information is written in this field.

Parse SQL: Step of parsing SQL the following procedures shall be applied, SQL syntax checking operation. SQL conforms to the Structured Query Language rules. SQL semantic checking operation (all references db objects validation). Checking the Query existing in the shared pool (Shared pool information about the given later.). If query exist in the shared pool, Optimizer doesnt generate Execution Plan for tihs SQL. If query exist in the shared pool, optimizer generates execution plan in this step. Step of parsing SQL is the most cost effective and the most important step in Oracle SQL Processing.

Bind Variables: If your query previously operated with different variables, a new parameters were added to same query at this step. However  we use the bind variable in the query should be indicated by the notation in a different spelling. Otherwise, the query will be processed as they are executed for the first time. Query re-parsing again. This means query performance reduce dramatically. I will give an example about this subject later. It will provide positive effect for your query.

Execute SQL: In this step, If query is DML or DDL statemnts, SQL will process exactly. However If SQL is a select statement, SQL will sent fetch operations to returning result rows.

Fetch Rows: According to SQL content,  to biring access the relevant rows returned in the resultset

Close Cursor: Memory that all of used by relevant SQL is dealocated.

As I mentioned above, Parsing SQL is the most crucial and costly step of SQL procesing. If we used structure of bind variable effectively, Our queries would improve their working time performance. Writing a SQL ,that frequently used queries and repetitive many same SQL statements, using bind variables dramatically improve query our performances.

One of the mistakes made in the ordinary by Software developers, creating SQL statements concat variables in the statement. Creating SQL with this way generally only change  part of parameter and same SQL statements run with the different parameters repeatedly. Oracle stores SQL statements (execution plan) that runing before. The purpose of this, Oracle doesnt want to parsing same SQL statements.  Only our queries not to be on the Shared Pool. It is a common area for all db users. In this structure, all db users queries which parsed and run to be on the Shared Pool. Thus the possibility of parsing SQL reduced. If we take an advantage of this mechanism, We would use bind variable when writing new SQL statements. Each query ,that not to applied parsing step, run effectively. Because Oracle doesnt generate new Execution plan about this SQL statements. This structure are available in a more detailed manner in the following ways.

Now Using Bind Variable look at our example below to show how effective.

Create a table as follows:

CREATE TABLE HR.DENEME
(
COL NUMBER NOT NULL
);

whether this table queries, then insert two different ways Let’s see how long they work. (Created by Concatanate in SQL Method 1, Method 2 was created using the bind variable in SQL). Questions had 2 side will do the same. The numbers from 1 to 100000 will insert into table one at a time.

Method 1

1.Yöntem: 

SET timing on;

DECLARE
v_sql VARCHAR2 (100);
BEGIN
FOR i IN 1 .. 100000
LOOP
v_sql := ‘insert into deneme values(‘ || i || ‘)’;

EXECUTE IMMEDIATE v_sql;
END LOOP;
END;

PL/SQL procedure successfully completed.
Elapsed: 00:00:30.01 

 

Method 2 (Bind Variables):

SET timing on;

DECLARE
v_sql VARCHAR2 (100);
BEGIN
FOR i IN 1 .. 100000
LOOP
v_sql := ‘insert into deneme values(:i)’;

EXECUTE IMMEDIATE v_sql
USING i;
END LOOP;
END;

PL/SQL procedure successfully completed.
Elapsed: 00:00:03.49

Yes, even in a very simple query, we see that approximately 10-fold improvement in runtime. The reason for this, as I mentioned earlier, Although SQL statements always same, variable  concatination cause  to evaluation as a new SQL therefore optimizer parse and genrerate execution plan for each statement . As a result of this, 100000 parsing operation for 100000 different variable (This mean optimizer products 100000 execution plan).  Residues resulting from an sql is running too long and its performance
very bad. As shown in our example of my query in the 2nd To use a bind variable in the query method will accelerate our performances seriously. When we make SQL Tuning, we should think using bind variable the first detail.

Resources:

tahiti.oracle.com

tonguc.wordpress.com

Oracle SQL High Performance Tuning Second Edition THE Prentence HALL(Guy HARRISON)

 

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Root, Uncategorized and tagged , , , , , , , , . Bookmark the permalink.

One Response to Oracle SQL Processing, Shared Pool and Bind Variables

  1. raj says:

    Hi,
    Thanks for Posting this article.

    I amn’t developer.Please explian the below one briefly.

    One of the mistakes made in the ordinary by Software developers, creating SQL statements concat variables in the statement. Creating SQL with this way generally only change part of parameter and same SQL statements run with the different parameters repeatedly.

    Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s