[20160205]大量子光标引起的等待事件.txt

--前几天遇到1一个bug，在10g下，如果设置参数cursor_sharing=force的情况下，如果访问远程表带文字变量，转成带参后，出现大量子
--光标的问题。链接如下：
http://blog.itpub.net/267265/viewspace-1985215/

--今天看看相关的等待事件：

1.环境：
SCOTT@test> @&r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

CREATE PUBLIC DATABASE LINK loopback USING '192.168.100.33:1521/test';
--注：我的IP就是服务器本身，这样相当于建立了一个loop link。

2.建立测试环境：
create table t as select rownum id ,rpad(rownum,100,'test') pad,'Y' flag from dual connect by level<=20000;
create unique index pk_t on t(id);
alter table t add constraint pk_t  primary key (id);
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1');
--分析表T，没有建立直方图。

select * from dba_tab_cols where table_name='T' and owner=user;

2.测试使用db_link与不使用db_link的情况情况：
select /*+ full(t) */ count(*) from t;

$ cat aa.sql
set timing on
--alter system flush shared_pool;
alter session set cursor_sharing=force;
declare
v_pad varchar2(200);
v_count number;
begin
for i in 1 .. 20000 loop
--execute immediate 'select count(pad) from t where id = ' || i into v_count;
execute immediate 'select count(pad) from t@loopback where id = ' || i into v_count;
end loop;
end;
/

--不使用db_link情况:
SCOTT@test> @aa
Session altered.
Elapsed: 00:00:00.00
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.09

--使用db_link情况:
SCOTT@test> @aa
Session altered.
Elapsed: 00:00:00.00
PL/SQL procedure successfully completed.
Elapsed: 00:01:04.70

--可以发现如果产生大量的子光标需要1:04.70才完成，比原来慢差不多60倍。产生了2818个子光标(注实际上因为共享内存的大小，一些
--已经清除了).

SCOTT@test> select count(*) from v$sql where sql_id='3nmu275jxwxx1';
  COUNT(*)
----------
      2818

3.打开3个会话同时执行看看：
sqlplus scott/btbtms @aa.sql &
sqlplus scott/btbtms @aa.sql &
sqlplus scott/btbtms @aa.sql &

SELECT count(*), event
  FROM v$active_session_history
WHERE sql_id      =  '3nmu275jxwxx1'
   AND sample_time >= '2016-02-05 10:38:05'
group by event;

  COUNT(*) EVENT
---------- ----------------------------------------
        43 cursor: pin S wait on X
        36
        17 kksfbc child completion

--可以发现主要的等待事件是cursor: pin S wait on X,kksfbc child completion.