[20160205]大量子游標引起的等待事件.txt

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