提高短连接性能方法测试

创建测试脚本
通过在三个会话中同时执行test_login.sh脚本,模拟当数据库多个短连接情况性能

[oracle@xifenfei tmp]$ more test_login.sh 
#!/bin/bash
echo "start login database `date`*********" >>/tmp/test_1.log
e=2000
for((i=1;i<=$e;i=i+1))
do
/tmp/login_oracle.sh 
done
echo "end login database `date`*********" >>/tmp/test_1.log

[oracle@xifenfei tmp]$ more login_oracle.sh 
#!/bin/bash
sqlplus chf/xifenfei@ORA11G_P<<XFF>/dev/null 
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
exit
XFF 

--ORA11G_P根据不同的测试情景指定不同名称

情况1:一个监听情况下

start login database Tue May  1 18:03:32 CST 2012*********
start login database Tue May  1 18:03:35 CST 2012*********
start login database Tue May  1 18:03:37 CST 2012*********

end login database Tue May  1 18:08:20 CST 2012*********
end login database Tue May  1 18:08:25 CST 2012*********
end login database Tue May  1 18:08:26 CST 2012*********

--计算2000个会话登录/查询/推出时间
4:48
4:40
4:49

情况2:三个监听,客户端配置tns负载均衡

--监听配置
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = ora11g)
     (ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1)
     (SID_NAME = ora11g)
    )
  )
ADR_BASE_LISTENER = /u01/oracle

LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1522))
    )
  )
SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = ora11g)
     (ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1)
     (SID_NAME = ora11g)
    )
  )
ADR_BASE_LISTENER1 = /u01/oracle


LISTENER2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1523))
    )
  )
SID_LIST_LISTENER2 =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = ora11g)
     (ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1)
     (SID_NAME = ora11g)
    )
  )
ADR_BASE_LISTENER2 = /u01/oracle

--tns配置
ORA11G_M =
  (DESCRIPTION =
      (LOAD_BALANCE=ON)
      (FAILOVER=ON)
      (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1522))
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1523))
       (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
     (SERVER=DEDICATED)
      (SERVICE_NAME = ora11g)
    )
  )

--测试结果
start login database Tue May  1 17:51:45 CST 2012*********
start login database Tue May  1 17:51:49 CST 2012*********
start login database Tue May  1 17:51:51 CST 2012*********

end login database Tue May  1 17:55:58 CST 2012*********
end login database Tue May  1 17:56:06 CST 2012*********
end login database Tue May  1 17:56:09 CST 2012*********

--计算2000个会话登录/查询/推出时间
4:13
4:17
4:18

情况2:使用常驻连接池DRCP(11g新特性)

--启动默认DRCP
SQL> exec dbms_connection_pool.start_pool();

PL/SQL procedure successfully completed.

--tns配置
ORA11G_P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
    )
    (CONNECT_DATA =
     (SERVER=POOLED)  --注意
      (SERVICE_NAME = ora11g)
    )
  )

--执行结果
start login database Tue May  1 18:19:58 CST 2012*********
start login database Tue May  1 18:20:01 CST 2012*********
start login database Tue May  1 18:20:03 CST 2012*********

end login database Tue May  1 18:23:16 CST 2012*********
end login database Tue May  1 18:23:19 CST 2012*********
end login database Tue May  1 18:23:21 CST 2012*********

--计算2000个会话登录/查询/推出时间
3:16
3:18
3:19

总结
如果在数据库短连接过程中发现监听是瓶颈的时候,可以考虑使用多个监听+tns 负载均衡,从一定程度上缓解监听瓶颈.如果是11g数据库可以考虑使用其心功能DRCP,从而很大程度上提高短连接过程中数据库的效率.因为DRCP还属于11g的新功能稳定性不知道如何?使用该功能前,请一定要做好相关测试工作.如有可能还是建议从应用层面尽可能的使用长连接,提高数据库会话效率.