[20171028]测试大量子光标对性能影响.txt

[20171028]测试大量子光标对性能影响.txt

--//做一个测试例子说明存在大量子光标对性能影响.

1.环境:
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

CREATE TABLE t (n NUMBER);
INSERT INTO t VALUES (1);
COMMIT;
execute dbms_stats.gather_table_stats(user,'t')

2.建立测试脚本:
> cat tt1.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..1000
    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;
/

> cat tt2.sql
DECLARE
    l_count PLS_INTEGER;
BEGIN
    FOR i IN 1..100
    LOOP
    EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_caching = '||0;
    FOR j IN 1..1000
    LOOP
        EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj = '||100;
        EXECUTE IMMEDIATE 'SELECT count(*) FROM t' into l_count;
    END LOOP;
    END LOOP;
END;
/
--//注:第2个例子使用常量.

3.测试一:
SCOTT@test01p> set timing on
SCOTT@test01p> @ spid

       SID    SERIAL# SPID                     PID  P_SERIAL# C50
---------- ---------- -------------------- ------- ---------- --------------------------------------------------
       251         37 4912                      22          5 alter system kill session '251,37' immediate;

SCOTT@test01p> @ d:\temp\tt1.sql
--//等待...

SCOTT@test01p> @ wait
P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------------- --------------- ---------------
000000000AC62B80 000000000000018E 00                180759424        398          0        251         37         69 latch: shared pool   WAITED SHORT TIME                21               0
--//等待时间是latch: shared pool

SCOTT@test01p> select count(*) from v$sql where sql_id='5tjqf7sx5dzmj';
  COUNT(*)
----------
      1225

SCOTT@test01p> select count(*) from v$sql where sql_id='5tjqf7sx5dzmj';
  COUNT(*)
----------
      1247

SCOTT@test01p> select count(*) from v$sql where sql_id='5tjqf7sx5dzmj';
  COUNT(*)
----------
      1671

SCOTT@test01p> select count(*) from v$sql where sql_id='5tjqf7sx5dzmj';
  COUNT(*)
----------
      1550
--//可以发现子光标数量在发生变化.

SCOTT@test01p> @ d:\temp\tt1.sql
PL/SQL procedure successfully completed.
Elapsed: 00:03:04.36
--//需要3分钟以上.

4.测试二:
SCOTT@test01p> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.08

SCOTT@test01p> @ d:\temp\tt2.sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:09.96
--//很快9秒就执行完成了.

SCOTT@test01p> @ wait
no rows selected

SCOTT@test01p> select count(*) from v$sql where sql_id='5tjqf7sx5dzmj';
  COUNT(*)
----------
         1

--//另外11g以后增加一个参数_cursor_obsolete_threshold,限制产生子光标的数量.缺省1024.从前面的测试看瞬间还是存在超过1024的可能性.
--//注:前面的测试曾经达到16XX.

SYS@test01p> @ hide %cursor%threshold
NAME                                     DESCRIPTION                                                        DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ----------------------
_cursor_obsolete_threshold               Number of cursors per parent before obsoletion.                    TRUE                   1024                   1024
_cursor_reload_failure_threshold         Number of failed reloads before marking cursor unusable            TRUE                   0                      0

5.继续测试三:
--//修改_cursor_obsolete_threshold =64看看.

SYS@test> alter system set "_cursor_obsolete_threshold"=64 scope=spfile;
System altered.

--//重新启动数据库看看.

SYS@test> @ hide %cursor%threshold
NAME                                     DESCRIPTION                                                        DEFAULT_VALUE          SESSION_VALUE          SYSTEM_VALUE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- -------------------
_cursor_obsolete_threshold               Number of cursors per parent before obsoletion.                    FALSE                  64                     64
_cursor_reload_failure_threshold         Number of failed reloads before marking cursor unusable            TRUE                   0                      0

SCOTT@test01p> set timing on
SCOTT@test01p> @spid

       SID    SERIAL# SPID                     PID  P_SERIAL# C50
---------- ---------- -------------------- ------- ---------- --------------------------------------------------
       369         65 976                       55          6 alter system kill session '369,65' immediate;

SYS@test01p> @ wait
P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------------- --------------- ---------------
000000000B0C2B80 000000000000018E 00                185346944        398          0        369         65        397 latch: shared pool   WAITED SHORT TIME                 5               0

SYS@test01p> select count(*) from v$sql where sql_id='5tjqf7sx5dzmj';
  COUNT(*)
----------
       268

SYS@test01p> select count(*) from v$sql where sql_id='5tjqf7sx5dzmj';
  COUNT(*)
----------
       532

SYS@test01p> select count(*) from v$sql where sql_id='5tjqf7sx5dzmj';
  COUNT(*)
----------
       564
--//密集的调用执行,还是超过限制许多...

SCOTT@test01p> @ d:\temp\tt1.sql
PL/SQL procedure successfully completed.
Elapsed: 00:02:01.87

--//需要2分钟以上.但是还是比前面3分钟快许多.