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 is also a 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.
