ORA-04031: 无法分配 12519000 字节的共享内存 ("large pool"，"unknown object"，"hash-join subh"

konglx

浏览: 636253 次
性别:
来自: 上海

最近访客更多访客>>

adiligentpiggy

mengstart

xkz

czl026

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

数据库

Oracle SQL Server SQL 配置管理 .net

ORA-04031: 无法分配 12519000 字节的共享内存 ("large pool"，"unknown object"，"hash-join subh"，"kllcqc:kllcqslt")

解决方法：

SQL> show parameter dispa

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (PROTOCOL=TCP)
max_dispatchers                      integer     5
mts_dispatchers                      string      (PROTOCOL=TCP)
mts_max_dispatchers                  integer     5

sql> alter system set dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)';

SQL> show parameter dispa

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dispatchers                          string      (PROTOCOL=TCP)(SERVICE=orclXDB
                                                 )
max_dispatchers                      integer     5
mts_dispatchers                      string      (PROTOCOL=TCP)(SERVICE=orclXDB
                                                 )
mts_max_dispatchers                  integer     5

问题解决，再用select × from v$session 查看，都是dedicated连接了

后者指定了服务名，而前者没有。没有指定服务器，大概就造成了oracle实例向listener注册时，
以任何服务名来建立的连接都是使用shared server方式。这是个人理解。

参考文档：http://blog.chinaunix.net/u/25477/showart_251764.html

相关知识总结：

Oracle Shared Server比较适合一些小而快的事务连接，这些事务的特点就是事务比较短，
操作和返回的数据量比较少。而对于一些数据仓库而言，显然是不适合建成 Shared Server
模式。下面说一下Shared Server和Dedicated Server的一些区别：

在Dedicated Server环境中，每一个连接都将启动一个专用服务进程，这个专用服务进程始终
服务于这个连接直到连接断开。每一个专用服务进程都有一个属于自己的内存区域，叫做
PGA（Program GlobalArea），里面存放了会话的信息，包括绑定变量、游标、排序等等。

而在Shared Server环境中，这些信息被存放到SGA中的UGA（User Global Area）区域中，
这个区域一般都位于大型池中（Large pool）

在Shared Server环境中，一个调度器服务多个连接的请求，并将请求放到请求队列（request queue）
中，所有调度器共用一个请求队列，接着由共享服务进程（Shared Server processes）来处理这些请
求，并将处理后的结果返回到响应队列（response queue），与请求队列不同，每个调度器都有自己
的一个响应队列，然后由调度器把响应队列中的结果返回给客户端。其中请求队列和响应队列都是SGA中的一部分。
在一个共享服务环境中，一个客户端请求的步骤是这样的：

1、客户端发送一个请求到调度器
2、调度器将请求放入到请求队列中
3、共享服务进程从请求队列中取出请求进行处理
4、共享服务进程将处理后的结果放到调度器的响应队列中
5、调度器从响应队列中取出结果返回给客户端

同时，在Shared server环境中，也可以使用连接池的方法来存放多个连接请求，数据库定
时断开空闲的连接来服务其它新的连接请求。

在Shared server环境中也存在不足，当某一个请求需要处理并返回大量数据的时候，将会
导致其它新的请求得不到及时的响应。所以对于这样的请求，最好使用专用服务进程。同时
，部分数据库管理操作需要使用专用服务进程，比如数据库启动关闭，数据库备份恢复等等，
对于表分析、大量数据加载、索引重建等操作也建议使用专用服务进程。

监听器在Shared server环境中也扮演着重要的角色。PMON进程定期检测调度器的负载情况，
并将这些信息反馈给监听器。监听器记录着每个调度器的地址信息及负载情况（当前服务多
少个连接等等），当有新的连接请求的时候，监听器将负载较低的调度器地址信息返回给客
户端，客户端根据地址信息连接到相应的调度器。

如果用户进程不能够连接到调度进程，或者用户请求的是一个专有服务器连接，则监听器将
创建一个专有服务器进程（dedicated server process），并把这个用户进程连接到这个服务
器进程

下面谈谈如何配置Oracle shared server环境。

可以通过多种方法来配置shared server，包括创建数据库的时候指定参数、EM、修改初始化
参数文件，还有可以通过Alter system进行修改，因为这些参数都是动态参数。

设置连接池：
dispatchers="(protocol=tcp)(serv=orcl)(list=orcl)(pool=on)(TICK=1)(disp=5)(con=33)(sess=224)

上面表示启动连接池，一个调度器的连接数最大为33个，sessions数目最大为224个，并10分钟后自动断开未活动的连接。（TICK=1表示10分钟）

shared_servers:指定oracle启动时，启动的共享服务器进程的最小数量，对于很繁忙的系统，这个值设置的大一些
对于很空闲的系统，这个值设置的小一些，对于一般的系统这个值设置是连接数的十分之一，
默认值库1。设置为0表示不使用SHARED_SERVER。

dispatchers 用于设置调度进程，其中，protocol用于设置调度进程支持的协议，poo用于设置是否启用共享进程；poo=on
表示启用共享进程，disp用于设置调度进程的数量；con用户设置每个调度进程3的最大网络连接数量；sess用户设置每个调度
进程的最大会话数（session）。调度器数量 = 数据库最大session数 /每个调度器服务的session数；

dispatchers还有如下属性：

service：dispatcher注册的net service name，没有给出采用services_name中的；
listener：监听动态注册，非默认端口1521或没有在local_listener中给出时需使用；
sessions：每个dispacther的会话数
connections：每个dispacther连接数

MAX_DISPATCHERS:设置最大的调度器数，可以动态调整，如：ALTER SYSTEM SET MAX_DISPATCHERS=10;

SHARED_SERVER_SESSIONS：设置ORACLE SHARED SERVER的最大session数。
当连接到SHARED SERVER的session数超过此值的话，将报错：
ERROR:
ORA-00018 maximum number of sessions exceeded

不过当数据库session超过此值的时候，仍然可以通过专用服务器连接进行连接。

MAX_SHARED_SERVERS：设置最大的SHARED_SERVER数。如果未给此参数附值，那么SHARED_SERVER数库无限制。

原来oracle服务在注册到监听器是默认到1521端口的，如果没有注册上则监听的状态始终会为unknow状态，
不过客户端通过tns的配置专用服务请求还是能找到相应的数据服务的，但是共享服务就没有办法了，因为
端口不是默认的1521，因此共享服务器的调度器dispatcher找不到监听所在的端口41521，因此找不到调度
服务，至使与监听不能互相通信。

上面连接就讲了如何让oracle服务注册到特殊的监听端口。
其实很简单：
在oracle数据库服务器上配置上本地的oracle的tnsname连接。
sdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sdb)(PORT =41521))
)
(CONNECT_DATA =
(SID = sdb)
(SERVER = DEDICATED)
)
)

然后修改参数：
alter system set local_listener = "sdb";
alter system register;
之前local_listener是为空的，此时本地oracle服务就知道可以通过sdb这个tnsname到本地的监听服务器进行
注册，状态变成了ready，这时调度器也一并被进行了注册。

参考文档：http://rainbowbridg.itpub.net/post/23663/262006

数据库的session数可以通过下面的视图获得：
1、 v$session:获得当前数据库的session数
SQL> select count(*) from v$session where username is not null;
2、 V$LICENSE：获得当前数据库的session数及数据库启动以来最大的session数
SQL> select sum(sessions_current) cur_sessions,sum(sessions_highwater) high_sessions from V$LICENSE;
比如数据库当前有500个TCP/IP session，每个调度器管理50个session，那么就需要10（500/50）个调度器。参数设置如下：
DISPATCHERS=”(PRO=TCP)(DIS=10)”
当然也可以根据数据库当前的负载，使用ALTER SYSTEM命令动态的增加或减少调度器的数据，如下：
ALTER SYSTEM SET DISPATCHERS=”(PRO=TCP)(DIS=5)”;

2）从动态性能图获得信息：
V$CIRCUIT :select * from v$circuit;
V$QUEUE
V$SESSION
V$DISPATCHER：显示当前调度器的一些信息，包括调度器的状态（等待还是繁忙）、当前的连接数、历史的连接数等等。
V$DISPATCHER_CONFIG：显示调度器的一些配置参数。
V$SHARED_SERVER：显示当前数据库SHARED SERVER的一些状态信息
V$SHARED_SERVER_MONITOR：显示当前数据库SHARED SERVER的一些统计信息，包括SHARED SERVER的最大连接数，会话数及启动的SHARED SERVER数等等。
同时，在ORACLE SHARED SERVER环境中，也可以配置专用服务器连接，但只有当你是使用Localnaming方法的时候才可以，如果你是使用Hostnaming方法的话则不可以。有几个方法进行配置，如下：
1、手工修改Tnsname文件
ora10g =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.172)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora10g)
)
)
2、通过Oracle net manager进行配置

3）对于配置了ORACLE SHARED SERVER的数据库，有一些参数需要值得我们的关注：
1、LARGE POOL SIZE
在ORACLE SHARED SERVER环境中，ORACLE 将UGA存放在LARGE POOL中，而在没有LARGE POOL或者LARGE POOL太小的数据库中，
UGA将存放于SHARED POOL中，这样将影响到数据库其它的性能。所以LARGE POOL的大小也值得我们考究。LARGE POOL最小为300K，
最大为2G（不同操作系统可能有所不同）。一般来讲，在ORACLE SHARED SERVER环境中，一个连接将占用1-3M的内存，不过要看
会话做了哪些操作。我们可以通过下面的语句获得数据库启动以来，最大的UGA值：

select sum(value) "Max MTS Memory Allocated"from v$sesstat ss, v$statname st
where name = 'session uga memory max'and ss.statistic# =st.statistic#;
Max MTS Memory Allocated
------------------------------------
244416

可以看到，ORACLE分配的最大UGA库240K，如果数据库同时支持100个并发连接，那么可以将LARGE POOL设置库23M（240K*100）。如果LARGE POOL设置过小，可能遇到下面的错误：
ORA-04031: unable to allocate 490 bytes of shared memory
("large pool","MWEIS","session heap","define var info")
可以通过ALTER SYSTEM命令进行动态修改。
ALTER SYSTEM SET LARGE_POOL_SIZE = 51200000 SCOPE=SPFILE；
2、调度器数目
可以通过查询V$DISPATCHER视图来查看调度器的状态：
SQL> desc v$dispatcher;
Name Type Nullable Default Comments
--------- ------------- -------- ------- --------
NAME VARCHAR2(4) Y
NETWORK VARCHAR2(128) Y
PADDR RAW(4) Y
STATUS VARCHAR2(16) Y
ACCEPT VARCHAR2(3) Y
MESSAGES NUMBER Y
BYTES NUMBER Y
BREAKS NUMBER Y
OWNED NUMBER Y
CREATED NUMBER Y
IDLE NUMBER Y
BUSY NUMBER Y
LISTENER NUMBER Y
CONF_INDX NUMBER Y
SQL> Select name, (busy / (busy + idle))*100
2 "Dispatcher % busy Rate"
3 From V$DISPATCHER
4 /
NAME Dispatcher % busy Rate
---- ----------------------
D000 0.0052861386871346
如果繁忙的百分比超过50%，那么可以考虑增加调度器，可以使用下面的语句动态修改：
ALTER SYSTEM SET DISPATCHERS=“（PRO=TCP）（DIS=2）”；
3、调度器响应时间
可以通过查看V$QUEUE 和 V$DISPATCHER来获得连接等待调度器响应的时间：
SELECT decode(sum(totalq),0,’No Responses’,
Sum(wait)/sum(totalq)) “Average Wait time”
FROM V$QUEUE q, V$DISPATCHER d
WHERE q.type = ‘DISPATCHER’
AND q.paddr = d.paddr;
Average Wait Time
------------------
.0413
4、连接等待shared server处理请求的时间
Select decode(totalq,0,’No Requests’) “Wait Time”,
Wait/totalq || ‘ hundredths of seconds’
“Average Wait time per request”
from V$QUEUE
where type = ‘COMMON’
Wait Time Average Wait time per request
-------- -----------------------------------
.023132 hundredths of a second

1。是否为配置为共享服务器，最主要的参数是

8i mts_servers

9i shared_server

show parameter shared_server mts_servers

如果数值 > 0 ,就是enable了共享服务器.

2。在配置为共享服务器的情况，Client可以选择用共享服务器或者专用服务器来连接到数据库，这个参数的控制是在tnsnames.ora里设定的

aaaa=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = aaaa)(PORT = 1521))

)

(CONNECT_DATA =

(SID = aa)

(SERVER = DEDICATED)

)

红色部分指定了用DEDICATED方式连接DB.

另外，Background process ,以及通过本地连接进来的，只能是DEDICATED

比如说sqlplus user/pass 形式

如果DB没有配置共享服务器，那么Client只能以DEDICATED方式连接DB.
(
1. 如果是dedicated server，则客户端只能创建dedicated server connection
2. 如果是shared server，则客户端能创建dedicated server connection和shared server connection，
只要在service name中指定server＝dedicated or server=shared.
)

3.判断一个已经连接的session的连接方式有两种方法

select username,server from v$session;

如果server = 'DEDICATED'则是DEDICATED方式

server='SHARED'则是shared方式，并且正有shared_server_process为其服务

server='NONE'的话，则是shared方式，并且当前没有shared_server_process为其服务。

B. 仅用于Unix 底下，似乎windown不行

连接v$session, v$process 看process中的program

1 select p.program,s.server from v$session s , v$process p

2* where s.paddr = p.addr

如果 program 为。。(S0NN) 的，则是shared方式，并且正有shared_server_process为其服务

如果 program 为。。(D0NN) 的，则是shared方式，并且当前没有shared_server_process为其服务

如果 program 为其它的，则是'DEDICATED'方式