Oracle Expert

Sometimes when we are asked to review performance of an application, we come across a bunch of codes which uses bind variables, and sniffing the SGA is not all that useful since it will show you the statements with the bind variables (:1, :2), not actually showing what values are being passed on.

Thankfully in 10g, the detective work is made easier.

SELECT * FROM A,B,C WHERE A.COL1=B.COL1 and A.COL1=C.COL1 and A.COL3=:1 and B.COL2=:2 and C.COL3=:3

Let’s grab the DBID and SQL_ID

select DBID, SQL_ID, SQL_TEXT from dba_hist_sqltext where sql_text like ‘%A,B,C%’;

    DBID   |   SQL_ID    |         SQL_TEXT          |   

 ———-+————-+—————————+-

 1234567890|12345xxxxxxxx|<your statement here>      |                  

Then let’s dig in into the AWR to try to find what is being passed on

                                                                  select name, position, datatype_string

from dba_hist_sql_bind_metadata

where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’

  NAME | POSITION |  DATATYPE_STRING   

 ——+———-+——————–

 :1    |1         |DATE           

 ——+———-+——————–

 :2    |2         |VARCHAR           

 ——+———-+——————–

 :3    |3         |NUMBER            

This will highlight whether there are some implicit conversion done, lets say

col2=’1’ instead of col2=1 as it should

Another view that is useful would be

select *

from dba_hist_sqlbind

where dbid = 1234567890 and sql_id = ‘12345xxxxxxxx’