[20171102]测试大量子光标对性能影响2.txt
--//跟开发讲关于绑定变量的问题,总有人讲不是有一个参数cursor_sharing能快捷简单地解决问题,设置cursor_sharing=force,
--//实际上合理的使用绑定变量才是王道.
--//许多开发人员设置这个参数带来的各种bug,我第一次在8i下使用差点到处服务器cpu资源耗尽,好在我知道我当时的改动,修改回来一些正常.
--//我当时还记得设置这个参数报ora-00600错误.
--//我想起以前10g下遇到设置cursor_sharing=force的一个bug,正好拿来测试产生大量子光标的情况.
1.环境:
SCOTT@test> @&r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SCOTT@test> CREATE PUBLIC DATABASE LINK loopback USING '192.168.100.33:1521/test';
Database link created.
--//建立一个loopback的dblink,实际上还是使用原来的服务器,这样可以简单模拟dblink的情况.
--//关于遇到bug的一些细节,参考链接 http://blog.itpub.net/267265/viewspace-1985215/=>[20160201]db_link与子光标问题.txt
2.建立测试环境:
CREATE TABLE t (n NUMBER);
INSERT INTO t VALUES (1);
COMMIT;
create unique index p_t on t(n);
execute dbms_stats.gather_table_stats(user,'t');
--//建立执行脚本tt1.sql,tt2.sql
$ cat tt1.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..1e5
LOOP
EXECUTE IMMEDIATE 'SELECT count(*) FROM t@loopback where n = '||i into l_count ;
END LOOP;
END;
/
$ cat tt2.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..1e5
LOOP
EXECUTE IMMEDIATE 'SELECT count(*) FROM t@loopback,dual where n = '|| i into l_count ;
END LOOP;
END;
/
--//注:如果加入一张本地表dual,就不会出现大量子光标的情况.
$ cat tt3.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..1e5
LOOP
EXECUTE IMMEDIATE 'SELECT count(*) FROM t@loopback where n = :x ' into l_count using i;
END LOOP;
END;
/
3.测试一:
SCOTT@test> alter system set cursor_sharing=force scope=memory;
System altered.
--//注:测试一定要在system级别打开这个参数,因为dblink是本地服务器,这样如果cursor_sharing=force仅仅在会话级别设置,
--//远程的sql语句会按照原样执行,而不是如下,结果更慢....
--//SELECT COUNT(*) FROM "T" "A1" WHERE "A1"."N"=:"SYS_B_0"
alter system flush shared_pool;
set timing on
@ tt1.sql
commit
SCOTT@test> @ tt1.sql
PL/SQL procedure successfully completed.
Elapsed: 00:03:48.46
--//需要3:48.46.
SCOTT@test> select count(*),sql_text from v$sql where sql_id='f5z89fqpdz81t' group by sql_text;
COUNT(*) SQL_TEXT
---------- ------------------------------------------------------------
5861 SELECT count(*) FROM t@loopback where n = :"SYS_B_0"
--//可以发现产生大量子光标.
SCOTT@test> select count(*),sql_text,executions from v$sql where sql_id='2s6ybj0r4ukpb' group by sql_text,executions;
COUNT(*) SQL_TEXT EXECUTIONS
---------- ------------------------------------------------------------ ----------
1 SELECT COUNT(*) FROM "T" "A1" WHERE "A1"."N"=:"SYS_B_0" 100000
4.测试二:
alter system flush shared_pool;
set timing on
@ tt2.sql
commit
SCOTT@test> @ tt2.sql
PL/SQL procedure successfully completed.
Elapsed: 00:01:03.10
--//需要3:48.46.
SYS@test> select count(*),sql_text,executions from v$sql where sql_id='5xjj10nkpcpgx' group by sql_text,executions;
COUNT(*) SQL_TEXT EXECUTIONS
---------- ------------------------------------------------------------ ----------
1 SELECT count(*) FROM t@loopback,dual where n = :"SYS_B_0" 100000
SYS@test> select count(*),sql_text,executions from v$sql where sql_id='7gvf1y78qrx48' group by sql_text,executions;
COUNT(*) SQL_TEXT EXECUTIONS
---------- ------------------------------------------------------------ ----------
1 SELECT "N" FROM "T" "T" WHERE "N"=:1 100000
--//远程执行语句与上面不同.
5.测试三:
alter system flush shared_pool;
set timing on
@ tt3.sql
commit
SCOTT@test> @ tt3.sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:44.07
SYS@test> select count(*),sql_text,executions from v$sql where sql_id='1s79mgdrcs29m' group by sql_text,executions;
COUNT(*) SQL_TEXT EXECUTIONS
---------- ------------------------------------------------------------ ----------
1 SELECT count(*) FROM t@loopback where n = :x 100000
SYS@test> select count(*),sql_text,executions from v$sql where sql_id='g8ug1c1cr255j' group by sql_text,executions;
COUNT(*) SQL_TEXT EXECUTIONS
---------- ------------------------------------------------------------ ----------
1 SELECT COUNT(*) FROM "T" "A1" WHERE "A1"."N"=:X 100000
--//改用绑定变量44秒.
6.测试四:
--//补充测试,不使用dblink看看.
$ cat tt4.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..1e5
LOOP
EXECUTE IMMEDIATE 'SELECT count(*) FROM t where n = :x ' into l_count using i;
END LOOP;
END;
/
alter system flush shared_pool;
set timing on
@ tt3.sql
commit
SCOTT@test> @ tt4.sql
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.03
SYS@test> select count(*),sql_text,executions from v$sql where sql_id='5duc59nwpqpfj' group by sql_text,executions;
COUNT(*) SQL_TEXT EXECUTIONS
---------- ------------------------------------------------------------ ----------
1 SELECT count(*) FROM t where n = :x 100000
--//不知道为什么通过dblink连接会这么慢,除了产生少量事务外,语句多执行1倍外.也许更多的消耗在网络上.