OB PL块中的SQL参数化测试

概述

生产环境中发现有些SQL不进行参数化,没办法通过OUTLINE绑定执行计划, OB原厂提到"PL中的SQL不能进行参数化",不能通过OUTLINE绑定执行计划,这里进行测试验证,发现这个说法并不准确(至少在我测试的3.2.4 BP5上有所差异).

1、pl中执行使用pl变量的SQL，不缓存到plan cache中

2、pl中执行动态拼接的SQL文本，可以进行参数化，会缓存到plan cache

测试版本：3.2.4 BP5

测试过程如下

pl中执行使用pl变量的SQL

测试数据
create table tab1(id number,c varchar2(100));
insert into tab1 select rownum,'test'||rownum from dual connect by rownum<=100;
commit;



declare
cnt number;
begin
for i in 1..100 
loop
select /*testplparse*/ count(*) into cnt from tab1 where id=i;
dbms_output.put_line(cnt);
end loop;
end;
/

select svr_ip,plan_id,sql_id,query_sql,statement from gv$plan_cache_plan_stat  where query_sql like '%testplparse%' and query_sql not like '%plan_cache%';

obclient [SYS]> declare
    -> cnt number;
    -> begin
    -> for i in 1..100 
    -> loop
    -> select /*testplparse*/ count(*) into cnt from tab1 where id=i;
    -> dbms_output.put_line(cnt);
    -> end loop;
    -> end;
    -> /
Query OK, 1 row affected (0.033 sec)

obclient [SYS]> 
obclient [SYS]> select svr_ip,plan_id,sql_id,query_sql,statement from gv$plan_cache_plan_stat  where query_sql like '%testplparse%' and query_sql not like '%plan_cache%';

Empty set (0.091 sec)

pl中执行的SQL在gv$plan_cache_plan_stat中查不到,如果是这样,那PLSQL中执行的SQL是在plan cache中查不到的

pl中执行动态拼接的SQL文本

declare
cnt number;
begin
for i in 1..100 
loop
execute immediate 'select /*testplparse*/ count(*)  from tab1 where id='||i into cnt;
dbms_output.put_line(cnt);
end loop;
end;
/

select svr_ip,plan_id,sql_id,query_sql,statement,EXECUTIONS from gv$plan_cache_plan_stat  where query_sql like '%testplparse%' and query_sql not like '%plan_cache%';



obclient [SYS]> declare
    -> cnt number;
    -> begin
    -> for i in 1..100 
    -> loop
    -> execute immediate 'select /*testplparse*/ count(*)  from tab1 where id='||i into cnt;
    -> dbms_output.put_line(cnt);
    -> end loop;
    -> end;
    -> /
Query OK, 1 row affected (0.063 sec)

obclient [SYS]> select svr_ip,plan_id,sql_id,query_sql,statement,EXECUTIONS from gv$plan_cache_plan_stat  where query_sql like '%testplparse%' and query_sql not like '%plan_cache%';
+---------------+---------+----------------------------------+-------------------------------------------------------+----------------------------------------+------------+
| SVR_IP        | PLAN_ID | SQL_ID                           | QUERY_SQL                                             | STATEMENT                              | EXECUTIONS |
+---------------+---------+----------------------------------+-------------------------------------------------------+----------------------------------------+------------+
| 192.168.56.87 | 2760880 | 47EC474E6D0E2B7C5F21B32EC841883E | select /*testplparse*/ count(*)  from tab1 where id=1 | select  count(*)  from tab1 where id=? |        100 |
+---------------+---------+----------------------------------+-------------------------------------------------------+----------------------------------------+------------+
1 row in set (0.094 sec)

pl中使用execute immediate 执行的动态SQL文本,在plan cache中可以查到,而且进行了参数化