ITPub博客

首页 > 数据库 > Oracle > [20160205]大量子光标引起的等待事件.txt

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

原创 Oracle 作者:lfree 时间:2016-02-05 10:44:01 0 删除 编辑

[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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1986653/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2488
  • 访问量
    6294549