Apr 22, 2015 10:51:56 PM by Richard To
I have heard a lot of people ask "Why my SQL performance is vary depending on where the SQL is executed ?", sometimes you may notice that the same SQL is performing good in one place, but not good in another place. If your SQL has bind variables, there may be a problem that you are not aware; it is the bind sensitive optimization feature of your SQL statement may be disabled in a static SQL cursor like the following:
Static SQL cursor
-------------------------------
declare
V1 NUMBER(6);
C_NAME VARCHAR2(60);
CURSOR C1 is select emp_name from employee where emp_id < V1;
begin
V1:=120000;
OPEN C1;
FETCH C1 INTO C_NAME;
CLOSE C1;
end;
As the cursor is hardcoded in the declare section, the bind sensitive feature for the SQL will be disabled, the cursor's query plan will be fixed when the SQL is first executed in a day.
If you code the SQL as a dynamic SQL/cursor like the following, the bind sensitive optimization feature will be used if your SQL is bind sensitive.
Dynamic SQL cursor
------------------------------------
declare
V1 NUMBER(6);
C_NAME VARCHAR2(60);
LONGSQL CLOB;
C1 SYS_REFCURSOR;
begin
V1:=120000;
LONGSQL := 'select emp_name from employee where emp_id < :var';
OPEN C1 FOR LONGSQL USING V1 ;
FETCH C1 INTO C_NAME;
CLOSE C1;
end;
For the dynamic SQL cursor, the query plan of the SQL statement may be changed upon the bind-in value of V1, the performance of the SQL statement can be changed significantly(most likely become better if the SQL syntax is simple). So, you may find that a bind sensitive SQL may also be place sensitive SQL!
In Toad Xpert SQL Optimizer, we have developed a good tool to help you to identify the potential performance problems that related on how and where you execute your SQL. this tool is available in Oracle and SQL Server platforms, I believe it is the most precise SQL tuning tool in the market.
Tags: SQL Optimizer for Oracle SQL Optimizer for SQL Server
Written by Richard To
Richard specializes in using AI technique to solve database performance problems.
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.