Testing application sql and maintaining the sql_id.

I often analyze sql from Siebel that performs poorly. I always try to reproduce the execution plan outside the application to be able to work with the performance issue.

In Siebel and I’ll guess many other applications the predicate list often has bind variables without names, like :1 or :2. If you try to reproduce that in a script you will get an error. see this simple example:

The variable must have a valid name

> variable 1 varchar2(40);
SP2-0553: Illegal variable name "1".

Without a valid variable, the sql wont execute.

> select count(1) from siebel.s_org_ext where X_PARMA_CODE LIKE :1;
SP2-0552: Bind variable "1" not declared.

The option is to change the variable name

> variable v1 varchar2(40);

Assign a value

APPDBA_SHARED@ncrm1p02 > execute :v1 := '909509%';
PL/SQL procedure successfully completed.

And run the query

> select /*MJ123 */ count(1) from siebel.s_org_ext 
where X_PARMA_CODE LIKE :v1;

COUNT(1)
----------
 1

But this means we will have a different sql_id.

> select sql_id, sql_text from v$sql 
where sql_text like '%MJ123%' and sql_text not like '%v$sql%';
SQL_ID        PLAN_HASH_VALUE EXECUTIONS 
------------- --------------- ---------- 
3d1c91havx6h3      4084375074          1 
select /* MJ123 */ count(1) from siebel.s_org_ext 
where X_PARMA_CODE LIKE :v1
6y3putkxy1grw      4084375074          1 
select /* MJ123 */ count(1) from siebel.s_org_ext 
where X_PARMA_CODE LIKE :1

Since we change the name of the variable we have different sql_id:s, perhaps it is not an issue. If it is and you want to maintain the sqlid and still run it with your settings, you can do it this way.

declare 
sqltext clob;
begin
 select sql_text into sqltext from dba_hist_sqltext 
 where sql_id ='6y3putkxy1grw ' and rownum < 2;
   execute immediate sqltext USING IN '909509%';
end;
/

I use table dba_hist_sqltext but you can also use v$sql. We put the text into the clob variable sqltext and then execute it using execute immediate supplying the bind variables needed. In my case I have :1 but we can have more, and then you just list them after each other with a , between. This is an example with two binds.

execute immediate sqltext USING IN "'909509%','Volvo AB'";

I just used this method when I had a poor plan generated by the application, when I ran my sql script (with modified variable names) i got a better plan. I could use the technique described in an earlier post to fix this, but I I used the one I just described.

After executing the sql using this method I had the same sql_id but two different plans, the poor one created by the application and the new better one generated by me.

I then used dbms_spm.load_plans_from_cursor_cache and loaded my better plan.
This is just a workaround useful if you need to fix the case quickly. You can use it if you discover that a parameter change fixes your plan but not if you must rewrite your sql.
If you need to rewrite your sql use this method to fix the plans.

When users are happy you can take your time to analyze why you saw different plans
and find a permanent solution.


		
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s