2015-01-07

checking suspicious bind variables in v$sql_monitor

In my current company we try to stabilize the performance of our Peoplesoft application. So on a more or less regular base I get a call to investigate as "it" is "slow" now.
During my research I found one error-pattern I'd like to show here: SQLs which where parsed for very selective BINDs (like customer-id) are executed with a single space (" ") as bind.
Unfortunately in Peoplesoft this character is used similar to NULL - so when it doesn't know what value to store, a space is used.
In general such BINDs lead to improper NESTED LOOPs.
Every time I found such executions, they where accepted as defect by development.

I'm in the lucky position to have tuning pack licensed, so I can use real time sql monitoring. That's very handy as I can save the a SQL in question together with a lot of infos (client, machine, module, start-time, BINDs, ...) in a html file and save it for later analysis/forward.

As v$sql_monitor shows specific executions, and it has all the BINDs stored as well, I can use it now to find other suspicious statements. So this little statement gives me some infos where to look closer:

SELECT sql_id, 
       name, 
       '--' || value || '--' 
FROM   (SELECT KEY, 
               sql_id, 
               xmltype.Createxml(binds_xml) confval 
        FROM   v$sql_monitor) v, 
       XMLTABLE('/binds/bind' passing v.confval 
                COLUMNS name  VARCHAR2(25)   path '@name', 
                        value VARCHAR2(4000) path '.'      ) 
WHERE  value = ' ' 
        OR value IS NULL; 
with an output like this:
SQL_ID        NAME                      VALUE
------------- ------------------------- -----
63pw6bqt84793 :2                        -- -- 

Please do NOT start with this statement when your application is "slow" - not even when it's Peoplesoft.

Keine Kommentare: