提高短连接性能方法测试

发表于 2012 年 5 月 12 日 由 惜分飞

创建测试脚本
通过在三个会话中同时执行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的新功能稳定性不知道如何?使用该功能前,请一定要做好相关测试工作.如有可能还是建议从应用层面尽可能的使用长连接,提高数据库会话效率.

发表在 Oracle 监听 | 评论关闭

shared pool latch 等待事件

发表于 2012 年 5 月 11 日 由 惜分飞

shared pool latch相关描述

The shared pool latch is used to protect critical operations when allocating 
and freeing memory in the shared pool. 
If an application makes use of literal (unshared) SQL then this can severely 
limit scalability and throughput. The cost of parsing a new SQL statement is 
expensive both in terms of CPU requirements and the number of times the library 
cache and shared pool latches may need to be acquired and released. Before Oracle9, 
there was just one such latch for the entire database to protect the allocation of 
memory in the library cache. In Oracle9, multiple children were introduced to relieve
contention on this resource.

减少shared pool latch方法

Avoid hard parses when possible, parse once, execute many. 
Eliminate  literal SQL so that same sql is shared by many sessions.
Size the shared_pool adequately to avoid reloads
Use of MTS (shared server option) also greatly influences the shared pool latch.

查询未绑定sql

--9i
SELECT substr(sql_text,1,40) "SQL", 
         count(*) , 
         sum(executions) "TotExecs"
    FROM v$sqlarea
   WHERE executions < 5
   GROUP BY substr(sql_text,1,40)
  HAVING count(*) > 30
   ORDER BY 2
  ;

--10g及其以后版本
SET pages 10000
SET linesize 250
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
     (SELECT  FORCE_MATCHING_SIGNATURE,
              COUNT(*) cnt
     FROM     v$sqlarea
     WHERE    FORCE_MATCHING_SIGNATURE!=0
     GROUP BY FORCE_MATCHING_SIGNATURE
     HAVING   COUNT(*) > 20
     )
     ,
     sq AS
     (SELECT  sql_text                ,
              FORCE_MATCHING_SIGNATURE,
              row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
     FROM     v$sqlarea s
     WHERE    FORCE_MATCHING_SIGNATURE IN
              (SELECT FORCE_MATCHING_SIGNATURE
              FROM    c
              )
     )
SELECT   sq.sql_text                ,
         sq.FORCE_MATCHING_SIGNATURE,
         c.cnt "unshared count"
FROM     c,
         sq
WHERE    sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE
AND      sq.p                       =1
ORDER BY c.cnt DESC

查询数据库整体解析情况

select to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits, 
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses, 
to_char(100 * hard / calls, '999990.00') || '%' hard_parses 
from ( select value calls from v$sysstat where name = 'parse count (total)' ), 
( select value hard from v$sysstat where name = 'parse count (hard)' ), 
( select value sess from v$sysstat where name = 'session cursor cache hits' );

参考:Note 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch

发表在 Oracle性能优化 | 评论关闭

Oracle 8.0.5 安装过程截图

发表于 2012 年 5 月 11 日 由 惜分飞
1

此多媒体陈列厅包含 7 张图像。

作为新一代dba(包括我),很少有机会能够接触到ORACLE 8.0.5数据库. … 继续阅读 →

更多多媒体陈列厅 | 一条评论