未使用绑定变量对share_pool的影响

oracle SGA中包含数据高速缓冲，重做日志缓冲，以及共享池（share_pool）。共享池中包含库高速缓冲（所有的SQL，执行计划等）和数据字典缓冲（对象的定义，权限等）。

           所以，如果SQL中没有绑定变量，那么会产生大量的SQL以及对应的执行计划，对共享池 造成影响

测试：

1.清空共享池

ALTER SYSTEM FLUSH SHARED_POOL;

2.执行不带绑定变量的SQL

SQL> declare
  2  begin
  3  for x in 1..1000 loop
  4  execute immediate 'select * from monkey.testtable where id='||x;
  5  dbms_lock.sleep(1);
  6  end loop;
  7  end;
  8  /

执行动态SQL并且直接拼接而不使用绑定变量

3.查看sqlarea中sql的状况

select sql_text,PARSE_CALLS,executions,FIRST_LOAD_TIME,LAST_LOAD_TIME from v$sqlarea where sql_text like '%monkey.testtable%' order by FIRST_LOAD_TIME desc;

从这里看到看到，不绑定变量的情况下，每一条SQL及其执行计划都会放进share_pool中，如果share_pool很小的情况下，就会出问题。同时，解析次数和执行次数都是1，即这个sql没有被复用，每一次都需要硬解析，影响性能。

4.执行带绑定变量的SQL

--清空share_pool
ALTER SYSTEM FLUSH SHARED_POOL;
--执行
SQL> declare
  2  begin
  3  for x in 1..1000 loop 
  4  execute immediate 'select * from monkey.testtable where id=:x' using x;
  5  end loop;
  6  end;
  7  /

5.查看sqlarea中sql的情况

select sql_text,PARSE_CALLS,executions,FIRST_LOAD_TIME,LAST_LOAD_TIME from v$sqlarea where sql_text like '%monkey.testtable%' order by FIRST_LOAD_TIME desc;

可以看到，绑定变量的SQL解析了1次，执行了1000次，即被复用了。节省了sqlarea的空间，减少了硬解析。

6.使用DDL将相关对象的SQL提出share_pool

查看sqlarea中与monkey.testtable相关的sql及其状态

select sql_text,OBJECT_STATUS from v$sqlarea where sql_text like '%monkey.testtable%';

可以看到，有并且是可用的

--执行DDL语句
grant select on monkey.testtable to monkey02;
--查看sqlarea中是否还有与monkey.testtable的sql
select sql_text,OBJECT_STATUS from v$sqlarea where sql_text like '%monkey.testtable%';

 

可以看到，这个sql并没有被踢出，但是状态已经变为不可用。当share_pool空间不足时，会将其踢出。