分类目录归档：数据库

常驻连接池(Database Resident Connection Pool)

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

一.介绍常驻连接池(Database Resident Connection Pool，Oracle DRCP)
数据库驻留连接池是Oracle Database 11g的一个新特性，专门为了解决在需要支持大量连接的环境对可扩性的迫切需求而设计的。数据库驻留连接池把数据库服务器进程和对话汇合起来(这样的组合称之为池服务器)，通过从单主机或不同主机发出的多个应用软件进程的连接进行共享。由一个连接代理(Connection Broker)进程控制着数据库后台进程中的池服务器。连接代理会持续的连接客户并对客户进行验证。当需要进行某种数据库活动时，客户将请求连接代理提供池服务器，使用完毕后再将它们释放以供其他客户重新使用。当池服务器处在使用当中时，相当于一台专用服务器。对于来自常驻通道中的客户端连接请求，连接代理会为其选择一个合适的池服务器，并把客户端请求交给该池服务器处理，不再干涉。此后客户通过和该池服务器的直接对话来完成所有的数据库活动。当客户完成请求任务释放池服务器后，连接代理将重新接管该池服务器。

二.什么时间使用DRCP
1 使用较小内存的、大量的客户端连接
2 客户端应用是相似的，可以共享或重用会话
3 客户端占用数据库连接的周期相当短
4 会话不需要跨客户请求
5 客户端有众多的主机与进程

三.Dedicated Servers,Shared Servers与DRCP的内存需求和区别对比
一般情况下，由于每个会话需要消耗400k的内存，每个进程需要消耗4m的内存，现在我们以DRCP的pool size是100，shared server的shared server进程是100为例，假如有5000个客户端连接到这些环境，则这些主机的内存分配如下：

A Dedicated Server
Memory used = 5000 X (400 KB + 4 MB) = 22 GB

B Shared Server
Memory used = 5000 X 400 KB + 100 X 4 MB = 2.5 GB
Out of the 2.5 GB, 2 GB is allocated from the SGA
.
C Database Resident Connection Pooling
Memory used = 100 X (400 KB + 4 MB) + (5000 X 35KB)= 615 MB

四.使用DRCP时，当实例有活动pooled server，有以下限制：
1 不能shutdown database;
2 不能停掉DRCP;
3 不能用database link连接到不同实例的DRCP;
4 不能使用Advanced Security Option (ASO)，比如encryption等

五.客户端如何连接到DRCP
如果是专用服务器连接，则SERVER=DEDICATED，如果是DRCP连接，则SEVER=POOLED。如果要指定客户端请求到DRCP，则客户端的tnsnames.ora中的连接字符串必须指定连接类型是POOLED，配置方式如下所示：

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

说明：
1)如果在tnsnames.ora中指定了SERVER=POOLED，但并没有在实例中启动DRCP，则当客户请求连接时，DB会报ORA-12520错误。
2)11g的客户端才可以使用DRCP，如果10g的客户端在tnsnames.ora中指定了SERVER=POOLED，则连接时报ORA-56606。

六.DRCP配置/查询
1.DRCP启动/关闭

--查看当前DRCP状态
SQL> select CONNECTION_POOL,status from dba_cpool_info;

CONNECTION_POOL                STATUS
------------------------------ --------------------------------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE

--启动DRCP
SQL> execute dbms_connection_pool.start_pool;

PL/SQL procedure successfully completed.

SQL> select CONNECTION_POOL,status from dba_cpool_info;

CONNECTION_POOL                STATUS
------------------------------ --------------------------------
SYS_DEFAULT_CONNECTION_POOL    ACTIVE

--关闭DRCP
SQL> exec dbms_connection_pool.stop_pool

PL/SQL procedure successfully completed.

SQL> select CONNECTION_POOL,status from dba_cpool_info;

CONNECTION_POOL                STATUS
------------------------------ --------------------------------
SYS_DEFAULT_CONNECTION_POOL    INACTIVE

2.修改DRCP参数

--dbms_connection_pool.configure_pool
exec dbms_connection_pool.configure_pool(
	POOL_NAME=>'SYS_DEFAULT_CONNECTION_POOL',
	minsize=>10,
	maxsize=>100,
	INCRSIZE=>10,
	SESSION_CACHED_CURSORS=>50,
	inactivity_timeout=>3000,
	max_think_time=>100,
	MAX_USE_SESSION=>10000,
	MAX_LIFETIME_SESSION=>36000
   );

--dbms_connection_pool.alter_param
exec dbms_connection_pool.alter_param(
	POOL_NAME=>'SYS_DEFAULT_CONNECTION_POOL',
	PARAM_NAME=>'MINSIZE',
	PARAM_VALUE=>'2');

3.DRCP视图

DBA_CPOOL_INFO 
displays configuration information about all Database Resident Connection Pools in the database.

V$CPOOL_STATS 
displays information about the Database Resident Connection Pool statistics for an instance


V$CPOOL_CC_STATS 
displays information about the connection class level statistics for 
the Database Resident Connection Pool per instance.

V$CPOOL_CONN_INFO 
displays connection information about each connection to the connection broker.

V$CPOOL_CC_INFO 
displays information about the pool-to-connection class mapping for 
the Database Resident Connection Pool per instance.

七.DRCP相关进程

oracle   11715     1  0 21:38 ?        00:00:00 ora_n000_ora11g
oracle   11719     1  0 21:38 ?        00:00:00 ora_l000_ora11g
oracle   11723     1  0 21:38 ?        00:00:00 ora_l001_ora11g
oracle   11727     1  0 21:38 ?        00:00:00 ora_l002_ora11g
oracle   11731     1  0 21:38 ?        00:00:02 ora_l003_ora11g
oracle   12490     1  0 21:57 ?        00:00:00 ora_l004_ora11g
oracle   12494     1  0 21:57 ?        00:00:00 ora_l005_ora11g
oracle   12498     1  0 21:57 ?        00:00:00 ora_l006_ora11g
oracle   12502     1  0 21:57 ?        00:00:00 ora_l007_ora11g
oracle   12506     1  0 21:57 ?        00:00:00 ora_l008_ora11g
oracle   12510     1  0 21:57 ?        00:00:00 ora_l009_ora11g
oracle   12514     1  0 21:57 ?        00:00:01 ora_l010_ora11g
oracle   12518     1  0 21:57 ?        00:00:00 ora_l011_ora11g
oracle   12522     1  0 21:57 ?        00:00:00 ora_l012_ora11g
oracle   12526     1  0 21:57 ?        00:00:00 ora_l013_ora11g
oracle   12530     1  0 21:57 ?        00:00:00 ora_l014_ora11g
oracle   12534     1  0 21:57 ?        00:00:00 ora_l015_ora11g
oracle   12538     1  0 21:57 ?        00:00:00 ora_l016_ora11g
oracle   12542     1  0 21:57 ?        00:00:00 ora_l017_ora11g
oracle   12546     1  0 21:57 ?        00:00:00 ora_l018_ora11g
oracle   12550     1  0 21:57 ?        00:00:00 ora_l019_ora11g

ora_n000_ora11g 
Connection Broker Process

ora_l000_ora11g 
Pooled Server Process(Handles client requests in Database Resident Connection Pooling)

发表在 Oracle | 2 条评论

提高短连接性能方法测试

发表于 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性能优化 | 评论关闭

17813235971

QQ 咨询

分类目录归档：数据库

常驻连接池(Database Resident Connection Pool)

提高短连接性能方法测试

shared pool latch 等待事件

文章分类

最近发表

分类目录归档：数据库

常驻连接池(Database Resident Connection Pool)

提高短连接性能方法测试

shared pool latch 等待事件

标签云

文章分类

最近发表