How to test SQL execution manually using bind variables

Thank you very much for your interest. 

Problem statement:Suppose some developer said one of the application query performance is not consistent. Out of 10 execution lets say 6 executions are slow.You started analyzing and found that there are multiple plan available in cursor cache.You have gatherd plan stats and decided to create a SQL plan baseline out of that.Now, as a DBA how will you test execution manually from sqlplus client.Application using bind variable and if you use literal then your SQL text will change which results change in SQL_ID.

As a result SQL plan baseline will not be attached because plan baseline is specific to a single SQL_ID.So, how will you solve this problem? If you are interested I will walk you through the steps involved in this case.

Test environment:-Oracle database 19c in OEL 7.9

I have created following SQL plan baseline for the below SQL coming from application.Lets capture all these information from v$sql

SELECT FT_ID, FT_TYPE_FLG, CUR_AMT, TOT_AMT, ARS_DT, SIBLING_ID, VERSION
FROM CI_FT
WHERE SA_ID = :1 AND FREEZE_SW = :2 AND ARS_DT IS NOT NULL AND ARS_DT < :3 AND REDUNDANT_SW = :4 AND NOT_IN_ARS_SW = :5 ORDER BY ARS_DT , SIBLING_ID , FT_TYPE_FLG 

SQL_Plan_BASELINE :SQL_PLAN_dpfk7jn4r397y612e1e29

Please see that all variables are bind here using :1,:2  respectively

You can find SQL text from SQL_TEXT column of v$sql if the query still presents in cursor cache.

If it is aged out you can search in dba_hist_sqltext dictionary.

Now you may be aware that bind variables can be captured during extract of execution plan like below

select * from table(dbmsx_xplan.display(cursor('SQL_ID','CURSOR_CHILD_NO','OUTLINE PEEKED_BINDS'));

but I would recommend you to use below SQL to construct variables which can be used later.

if query is still in cursor cache

select replace(name,':','v'||substr(name,2,0))||' '||datatype_string||' :='''||value_string||''';' from v$sql_bind_capture a where sql_id='7dxj4xw55cra4'

else

select replace(name,':','v'||substr(name,2,0))||' '||datatype_string||' :='''||value_string||''';' from dba_hist_sqlbind a where sql_id='7dxj4xw55cra4' and  snap_id = (select max(snap_id) from dba_hist_sqlbind b where b.sql_id='7dxj4xw55cra4')

REPLACE(NAME,':','V'||SUBSTR(NAME,2,0))||''||DATATYPE_STRING||':='''||VALUE_STRI

--------------------------------------------------------------------------------

v1 CHAR(32) :='7983757218'                                                      

v2 CHAR(32) :='Y'                                                               

v3 DATE :='01/17/2025 00:00:00'                                                 

v4 CHAR(32) :='N'                                                               

v5 CHAR(32) :='N'     

Now construct below anonymous PL/SQL code like below

Use SQL_FULL TEXT of v$sql to get exact SQL text and put it in the variable vSQL.

DO NOT MAKE ANY CHANGES otherwise it will create a new SQL_ID

set serverout on

alter session set current_schema=<Valid Schema for tables>; --This is required 

alter session set nls_date_format='/mm/dd/yyyy HH24:MI:SS';

declare

v1 CHAR(32) :='7983757218' ;                                                     

v2 CHAR(32) :='Y';                                                               

v3 DATE :=to_date('01/17/2025 00:00:00','/mm/dd/yyyy HH24:MI:SS')  ;                                               

v4 CHAR(32) :='N'    ;                                                           

v5 CHAR(32) :='N'   ;

vSQL varchar2(4000);

begin

                                                        

vSQL :='SELECT                    FT_ID,                    FT_TYPE_FLG,                    CUR_AMT,                    TOT_AMT,                    ARS_DT,                    SIBLING_ID,                    VERSION                 FROM                    CI_FT                 WHERE                    SA_ID = :1                      AND FREEZE_SW = :2                      AND ARS_DT IS NOT NULL                     AND ARS_DT < :3                      AND REDUNDANT_SW = :4                      AND NOT_IN_ARS_SW = :5                      ORDER BY ARS_DT                    , SIBLING_ID                    , FT_TYPE_FLG ';

dbms_output.put_line(vSQL);

execute immediate vSQL using v1,v2,v3,v4,v5;

end;

/

Use below query to find out last execution like below

set line 200

col SQL_PLAN_BASELINE format a30

select last_load_time,plan_hash_value,sql_plan_baseline,elapsed_time from v$sql where sql_id='7dxj4xw55cra4'

LAST_LOAD_TIME      PLAN_HASH_VALUE SQL_PLAN_BASELINE              ELAPSED_TIME

------------------- --------------- ------------------------------ ------------

2025-06-14/14:45:41      1243860814 SQL_PLAN_dpfk7jn4r397y612e1e29        11147                                                                                         

1 row selected.

This proves that you have successfully tested the SQL with same SQL_ID which is using your created plan baseline with expected elapsed time