Oracle基础知识-内存管理（未整理）

 
oracle9i:

 

教训：windows平台下万万不可将sga_max_size设得过大，曾经有一次将其从1200M加大到1500M，结果数据库启动时报ORA-27100:shared memory realm already exits。原因是：对于32位的os,每个进程可以访问的最大内存空间是2的32次方,即4G.每个进程的内存由user space和kernal space两个组件组成.user space可以使用的内存空间最大为2g。对于oracle，可使用的内存空间大概是1.7G左右。要注意的是：专用会话(以专用数据库连接建立的会话)的内存在sga之外的内存空间分配,但仍然在oracle.exe进程之内。如果sga_max_size过大，sga_max_size+专用会话内存超过1.7G，这可能是ORA-27100出现的原因。

workarea_size_policy=true 内存自动管理，
可以不管sort_area_size，hash_area_size等等烦人的设置，也不用再去计算繁琐的pga大小
oracle会根据pga_aggregate_target， v$pgastat.global memory bound限制每个session使用pga的最大空间，每个session可以使用到最大至5%pga的总空间
如果pga_aggregate_target=1G，则每个session可以使用到最大为50M

对于一个单纯的数据库服务器，通常我们需要保留20%的物理内存给操作系统使用，剩余80%可以分配给Oracle使用。Oracle使用的内存分为两部分SGA和PGA，那么PGA可以占用Oracle消耗总内存的20%（OLTP系统）至50%（DSS系统）。
这只是一个建议设置，更进一步的我们应该根据数据库的具体性能指标来调整和优化PGA的使用。

对于所有的进程,操作系统对整个虚拟内存也有一个系统全局性的限制,这个限制跟系统的SWAP SPACE相关.一旦超过了这两个限制,Oracle的进程在执行中会遇到ORA-4030错误.
　

ORA-4030这个错误的产生一般不是因为每个进程的资源限制而是因为SWAP SPACE空间不足造成.为了诊断这个问题可以使用操作系统的一些选项来查看SWAP SPACE的使用情况.另外,在一些操作系统中,Oracle包含了一个工具叫maxmem,它可以用来查看每个进程可以被分配的堆数据段的最大SIZE以及哪一个限制是第一次超过的.
　

如果这个问题的出现是因为SWAP SPACE空间不足,而且换页的动作非常频繁而且较多,则需要减少系统一级的虚拟内存的使用,这个可以通过减少进程数也可以通过减少每个进程的内存限制.如果换页动作不频繁而且比较少,则需要调大SWAP SPACE SIZE.

------------------------------------------------------------------------------------------------------------------------------------------------------

如果问题是由于swap空间不足造成的，并且由中度或者严重的page in/page out（可以用vmstat查看），你就需要尝试降低系统整体的虚拟内存的使用（如调整SGA大小），或者降低单个进程内存的使用（如调整sort_area_size），或者减少进程数量（如限制processes参数，使用MTS）。而如果page in/page out很少或者根本没有，就可以考虑增大swap空间。某些系统中，可以考虑使用伪交换区（如hp-ux中，可以考虑设置swapmen_on）。

如果问题和PLSQL操作有关，可以，1、检查PLSQL中的TABLE，看看其中的数据是否全都必要，是否可以减少数据放入TABLE中；2、优化相关语句（比如通过调整优化器策略，使查询计划走sort比较少的访问路径），减少sort操作，或者减少sort_area_size（代价就是一部分sort操作会放在磁盘上进行，降低性能）。

9i以后可以考虑设置PGA内存自动管理。即设置PGA_AGGREGATE_TARGET在一定数值范围内，WORKAREA_SIZE_POLICY设置为AUTO。但是注意，9i在OpenVMS系统上、或者在MTS模式下不支持PGA内存自动关联。

如果是因为进程数过多导致的内存大量消耗，首先可以考虑调整客户端，减少不必要的会话连接，或者采用连接池等方式，以保持系统有稳定的连接数。如果会话非常多，且无法降低的话，可以考虑采用MTS，以减少Oracle进程数。

检查SGA中的内存区是否分配过多（如shared pool、large pool、java pool）等，尝试减少SGA的内存大小。

在windows下，可以尝试使用ORASTACK来减少线程的堆栈大小，以释放更多的内存。

考虑增加物理内存。

--------------------------------------------------------------------------------------------------------------------------------------------------------
PGA(program global areas) 　　保存服务进程的数据和控制信息的内存结构，这个内存结构是非共享的，只有服务进程本身才能够访问它自已的PGA区。每个服务进程都有它自已的PGA区，各个服务进程PGA的总和即为实例的PGA区的大小
资料引用:http://www.knowsky.com/388134.html

show parameter pga;
show parameter sga;
show sga;---显示内存分配量（Total SGA与sga_max_size有关,可能比v$sgastat的和要多一些）
show parameter size;
select * from v$pgastat;
select sum(bytes) from v$sgastat;---显示实际内存使用量
--查查看都是哪些process占用内存多(从下面这个查询可以看到v$session s, v$sesstat st, v$statname sn, v$process p这四个动态视图之间的关系)
SELECT   s.SQL_ADDRESS,s.MACHINE,s.PROGRAM,s.LOGON_TIME,s.PROCESS,server, s.username, s.osuser, sn.NAME, st.VALUE / 1024 / 1024, s.SID, s.serial#, p.spid
    FROM v$session s, v$sesstat st, v$statname sn, v$process p
   WHERE st.SID = s.SID
     AND st.statistic# = sn.statistic#
     AND sn.NAME LIKE 'session pga memory'
     AND p.addr = s.paddr
     AND VALUE > 10 * 1024 * 1024                                              --only show pga > 10M
ORDER BY VALUE DESC

---然后通过查询v$sqlarea可得到该process正在执行的sql语句

select t.SQL_TEXT,t.ADDRESS from v$sqlarea t where t.ADDRESS='上面的SQL_ADDRESS'

----------------------------------------------------------
Usage:  orakill sid thread

  where sid    = the Oracle instance to target
        thread = the thread id of the thread to kill

  The thread id should be retrieved from the spid column of a query such as:

        select spid, osuser, s.program from
        v$process p, v$session s where p.addr=s.paddr

或者：
select sid,serial#,username,program,machine,status from v$session;
 然后
alter system kill session 'SID,SERIAL#';

注意，上例中SID为1到7(USERNAME列为空)的会话，是Oracle的后台进程，不要对这些会话进行任何操作。

----------------------------------------------------------

v$process
这个视图提供的信息，都是oracle服务进程的信息，没有客户端程序相关的信息
服务进程分两类，一是后台的，一是dedicate/shared server
pid, serial# 这是oracle分配的PID
spid 这才是操作系统的pid(实际上是oracle进程中的线程)
program 这是服务进程对应的操作系统进程名