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