oracle技術之提高短連接配接性能方法測試

建立測試腳本

通過在三個會話中同時執行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

sqlplus chf/xifenfei@ORA11G_P</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 =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1522))

SID_LIST_LISTENER1 =

ADR_BASE_LISTENER1 = /u01/oracle

LISTENER2 =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1523))

SID_LIST_LISTENER2 =

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*********

4:13

4:17

4:18

情況2:使用常駐連接配接池DRCP(11g新特性)

--啟動預設DRCP

SQL> exec dbms_connection_pool.start_pool();

PL/SQL procedure successfully completed.

ORA11G_P =

   (ADDRESS_LIST =

    (SERVER=POOLED)  --注意

--執行結果

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*********

3:16

3:18

3:19

總結

如果在資料庫短連接配接過程中發現監聽是瓶頸的時候,可以考慮使用多個監聽+tns 負載均衡,從一定程度上緩解監聽瓶頸.如果是11g資料庫可以考慮使用其心功能DRCP,進而很大程度上提高短連接配接過程中資料庫的效率.因為DRCP還屬于11g的新功能穩定性不知道如何?使用該功能前,請一定要做好相關測試工作.如有可能還是建議從應用層面盡可能的使用長連接配接,提高資料庫會話效率.