ITPub博客

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

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

原创 Oracle 作者:lfree 时间:2016-02-05 11:17:06 0 删除 编辑

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

--测试大量子光标引起的等待事件(11G):

1.环境:
SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE TABLE t (id NUMBER);
INSERT INTO t VALUES (1);
create unique index pk_t on t(id);
alter table t add constraint pk_t  primary key (id);
COMMIT;
execute dbms_stats.gather_table_stats(user,'t');

SYS@book> @ &r/hide _cursor_obsolete_threshold
NAME                        DESCRIPTION                                      DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE
--------------------------- ------------------------------------------------ -------------- -------------- ------------
_cursor_obsolete_threshold  Number of cursors per parent before obsoletion.  TRUE           1024           1024

2.测试:
$ cat aa.sql
DECLARE
    l_count PLS_INTEGER;
BEGIN
    FOR i IN 1..100
    LOOP
    EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_caching = '||i;
    FOR j IN 1..10000
    LOOP
        EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||j;
        EXECUTE IMMEDIATE 'SELECT count(*) FROM t' into l_count;
    END LOOP;
    END LOOP;
END;
/

sqlplus scott/book @aa.sql &
sqlplus scott/book @aa.sql &
sqlplus scott/book @aa.sql &

--sql_id='5tjqf7sx5dzmj'

SELECT event, COUNT (*)
    FROM V$ACTIVE_SESSION_HISTORY
   WHERE sql_id = '5tjqf7sx5dzmj'
   and sample_time >= '2016/02/05 11:00'
GROUP BY event
ORDER BY COUNT (*) DESC ;

EVENT                                      COUNT(*)
---------------------------------------- ----------
                                                423
cursor: pin S wait on X                         342
kksfbc child completion                         146
library cache lock                                7
cursor: mutex S                                   6
cursor: mutex X                                   3
library cache: mutex X                            2
7 rows selected.

# perf top  -k  /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
   PerfTop:    2122 irqs/sec  kernel:11.0%  exact:  0.0% [1000Hz cycles],  (all, 24 CPUs)
------------------------------------------------------------------------------------------------------------
             samples  pcnt function                     DSO
             _______ _____ ____________________________ ____________________________________________________

             2401.00 36.4% kgxShared                    /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
             1504.00 22.8% kgxRelease                   /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              168.00  2.5% kkscsSearchChildList         /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              146.00  2.2% kkscsPruneChild              /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              140.00  2.1% kspnum                       /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle
              122.00  1.8% kkshGetNextChild             /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle

--可以看到基本与10g一样。主要都是cursor: pin S wait on X,kksfbc child completion。
--另外11g通过隐含参数_cursor_obsolete_threshold限制了子光标产生的数量。11.2.0.4该参数设置是1024.
--而11.2.03设置是100.

--通过perf观察可以发现主要调用函数是kgxShared,kgxRelease.

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

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

注册时间:2008-01-03

  • 博文量
    2471
  • 访问量
    6278687