[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分钟快许多.