[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倍外.也许更多的消耗在网络上.