设置32位的windows2003使用大于1.7的内存

设置32位的windows2003使用大于1.7的内存(2009-09-30 12:24:14)
标签：杂谈   分类：数据库

32位的 CPU的在windows系统中2G给系统用2G给应用程序使用，oracle的SGA内存使用不能超过1.7G，将sga扩大到1.7G以上需要下面的一些设置

一.OS上修改
1.修改boot.ini文件，加/3GB /PAE:
在这行，multi(0)disk(0)rdisk(0)partition(1)WINDOWS="Microsoft Windows" /3GB /PAE
2.修改windows注册表:
regedit到HKEY_LOCAL_MACHINESOFTWAREORACLEHOME0这个目录中找到 AWE_WINDOW_MEMORY这个参数，将其修改为oracle需要内存的大小:例如：3G时为：3*1024*1024*1024
这个参数如不存大时，可以新建一个字符串名为AWE_WINDOW_MEMORY，值为上面讲过的大小,这个值需要足够大，不够时将报：
ORA-27102 out of memory
OSD-00034 Message 34 not found; Product=RDBMS;facility =SOSD
O/S Error: (OS 8) Not enough storage is available to process this command
3.修改windows控制面板中的管理工具--&gt 域安全策略--&gt本地安全策略--&gt用户权限分配--&gt锁定内存页（内存中锁定页面）中加入启oracle数据库的OS用户名.
4.重启电脑
二、oracle上的修改

1.备份spfile
create pfile='c:/initSID.ora' from spfile;

2.在pfile上修改参数
use_indirect_data_buffers=true
pre_page_sga=true
db_block_buffers=262144
sga_max_size=2147483648
sga_target=2147483648

3.用pfile启动数据库，将参数写回spfile，重启数据库
startup pfile=''
create spfile from pfile
shutdown immeidate
startup

 

 

===============

方法2：

 

通过PAE选项让Oracle在4G内存的32位windows服务器上使用超过1.7G的SGA
大 | 中 | 小
[不指定 2009/02/23 14:35 | by zeus ]
1.测试环境：HP DL380 G5服务器，4GB内存，32位Windows 2003企业版

2.修改c:\boot.ini 文件，添加/PAE选项后如下：

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE
[code]
3.重新启动Windows后，修改Oracle SGA参数，重启Oracle即可生效：
[code]
alter system reset sga_max_size scope=spfile sid='*';
alter system reset sga_target scope=spfile sid='*';
alter system set shared_pool_size=256M scope=spfile;
alter system set db_block_buffers=320000 scope=spfile;
alter system set use_indirect_data_buffers=true scope=spfile;
shutdown immediate;
startup;

使用PAE模式后，无法再使用Oracle自动内存管理功能，因此需要将sga_max_size和sga_target参数去掉，改用手动管理各内存组件，如上面的shared_pool_size参数和db_block_buffers参数分别指定的是共享池和数据缓冲区的大小，use_indirect_data_buffers指定oracle可以使用超过32位平台限制4GB以上的内存段

4.通过查看Oracle sga统计信息可以看到设置已生效：

SQL> select pool,sum(bytes)/1024/1024 from v$sgastat group by pool;
 
POOL         SUM(BYTES)/1024/1024
------------ --------------------
                      2507.99859 ---db buffer cache
java pool                      24
shared pool             256.00449 ---shared pool

SGA的设置浅谈
2009-04-10 07:57:10
　标签：Oracle　　　[推送到技术圈]

关于SGA设置的一点总结
1.测试环境：HP 服务器，4GB内存，32位Windows 2003企业版
2.修改c:\boot.ini文件，添加/PAE选项后如下：

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect /PAE
[code]
3. 重新启动Windows后，修改Oracle SGA参数，重启Oracle即可生效：
[code]
alter system reset sga_max_size scope=spfile sid='*';
alter system reset sga_target scope=spfile sid='*';
alter system set shared_pool_size=256M scope=spfile;
alter system set db_block_buffers=320000 scope=spfile;
alter system set use_indirect_data_buffers=true scope=spfile;
shutdown immediate;
startup;

使用PAE模式后，无法再使用Oracle自动内存管理功能，因此需要将sga_max_size和sga_target参数去掉，改用手动管理各内存组件，如上面的shared_pool_size参数和db_block_buffers参数分别指定的是共享池和数据缓冲区的大小，use_indirect_data_buffers指定oracle可以使用超过32位平台限制4GB以上的内存段
4.通过查看Oracle sga统计信息可以看到设置已生效：

SQL> select pool,sum(bytes)/1024/1024 from v$sgastat group by pool;
 
POOL         SUM(BYTES)/1024/1024
------------ --------------------
                      2507.99859 ---db buffer cache
java pool                      24
shared pool             256.00449 ---shared pool
 
由于工作需要，单位的服务器进行升级，使用至强CPU，内存为8GB，系统为windows2003,数据库为oracle8i,在32位系统中使oracle使用超过4G内存的问题，
　　1、由于32位系统内存寻址只能到4G，所以在32位系统上使用超过4G的内存，首先要使用支持大内存的软硬件，比如使用至强的CPU（虽然是32位CPU，但是上增加了扩展寻址的能力），windows2003企业版或数据中心版。
　　2、在操作系统中启用 PAE（Physical Address Extensions ）功能，这样oracle便可以通过windows的AWE（Address Windowing Extensions）API使用多余4G的内存。
　　方法：打开系统根目录下的隐藏文件 Boot.ini ，添加 PAE 开关：
　　（1）multi(0)disk(0)rdisk(0)partition(2)\%systemroot%="Windows Server 2003, Datacenter Edition" /PAE
　　（2）multi(0)disk(0)rdisk(0)partition(2)\%systemroot%="Windows Server 2003, Datacenter Edition" /3GB /PAE
　　我们知道32位windows对于每个进程都分配4GB内存（虚拟内存），其中起始的2GB归windows核心使用，考试@大提示剩余的归应用程序本身使用。因此这两种方法的区别就在于：
　　方法一只使用了 /PAE开关表示启用/PAE功能但是系统对每个进程仍然采用2G核心、2G应用程序的内存分配方式。
　　方法二除了/PAE开关还使用了 /3GB开关表示不仅启用/PAE功能并且系统对每个进程采用1G核心、3G应用程序的内存分配方式。不过这种方式不支持大于16GB的内存，也就是说如果你的实际内存超过16GB则只能使用方法一，这是因为大于16GB后1G的核心内存已经不够windows实现PAE功能。
　　3、给运行 Oracle数据库的操作系统帐户，授予"Lock Pages in Memory"的系统权限。
　　执行 gpedit.msc打开“组策略”控制台
　　“计算机配置”->“Windows 设置”->“安全设置”->“本地策略”->“用户权利指派”
　　双击右边“锁定内存中的页”（或名为"内存中锁定页"），在 “本地安全策略设置”对话框中，单击“添加”按钮，在“选择用户或组”对话框中，添加有权运行 oracle的帐户。
　　4、配置oracle数据库的参数文件（init*.ora），添加USE_INDIRECT_DATA_BUFFERS=TRUE参数，表示使用扩展的内存。
　　5、修改注册表中的AWE_WINDOW_MEMORY键值为合适值。该值表示在3GB内存中（如果使用了/3GB开关，如果没有使用该开关则为2GB）有多少用于数据库块缓存。
　　注意：
　　（1）该值位置在HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE \HOME0为二进制类型，单位为bytes。
　　（2）如果不存在表示使用默认值1GB。
　　（3）该值太大或太小都有可能导致数据库无法启动。
　　这里解释一下：尽管我们现在拥有超过4GB的内存，但是这些多余的内存并不是oracle可以随便使用的，这些内存只能用于数据库块缓存（即db_buffer），而像share Pool，log buffer等只能保存在应用程序可访问的3GB内存中（如果使用了/3GB开关，如果没有使用该开关则为2GB）。在这里我将4GB以外内存中的数据库块缓存叫做AWE数据块缓存（自己起的名字:-)）。这里又有问题了，oracle缓冲的数据块并不能全部保存到4GB以外的内存中，还必须在应用程序可直接访问的3GB内存（如果使用了/3GB开关，如果没有使用该开关则为2GB）中使用一部分空间来保存，这一部分内存我称为直接数据块缓存（自己起的名字:-)）。也就是说“数据块缓存=AWE数据块缓存+直接数据块缓存”，为什么会这样呢，因为oracle缓冲到内存中的每个数据块的头部必须保存到 “直接数据块缓存”中，是不能够保存到“AWE数据块缓存”中的，并且每个数据块的大小（db_block_size）和数据块的多少（db_block_buffers）都会影响到头部占用空间的多少。并且访问“直接数据块缓存”要比“AWE数据块缓存” 快， 因此AWE_WINDOW_MEMORY一般来讲需要设置的尽量大（但不能无限大，比如必须小于3GB），oracle建议以默认值为基础，以20%递增扩大，进行测试。比如先测试1GB大小，在测试1.2GB大小等等。
　　一般来讲AWE_WINDOW_MEMORY有一个最小公式
　　 min(AWE_WINDOW_MEMORY)=(4096 * db_block_size * db_block_lru_latches)/8
　　其中：
　　max buffer pools是一个常量=8
　　sets_per_tool=2*cpu_count （use_indirect_data_buffers=true）
　　sets_per_tool=cpu_count/2 （use_indirect_data_buffers<>true）
!!!!win2003 企业+sp2 默认就支持4G了
本总结不针对特例，仅对服务器只存在OS + ORACLE 为例，如果存在其他应用请酌情考虑
写这个也是因为近来这种重复性的问题发生的太多所导致的
首先不要迷信STS,SG,OCP,EXPERT 等给出的任何建议、内存百分比的说法
基本掌握的原则是， data  buffer 通常可以尽可能的大，shared_pool_size 要适度，log_buffer 通常大到几百K到1M就差不多了
设置之前，首先要明确2个问题
1： 除去OS和一些其他开销，能给ORACLE使用的内存有多大
2：oracle是 64bit  or  32 bit,32bit 通常 SGA有 1.7G 的限制(某些OS的处理或者WINDOWS上有特定设定可以支持到2G以上甚至达到3.7G，本人无这方面经验)
下面是我的windows2000下的oracle :
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE    8.1.7.0.0       Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
SQL>
windows上存在32bit的限制，如AIX、HP UNIX 等有明确的64BIT OS and ORACLE的版本，32bit oracle可以装在64bit os 上，64  bit oracle不能装在32 bit  OS上
不管oracle是32 bit ORACLE还是 64  bit 的,假定应用存在没有很好的使用bind  var 的情况，也不能设置 shared_pool_size 过大，通常应该控制在200M--300M,如果是 ORACLE ERP 一类的使用了很多存储过程函数、包 ，或者很大的系统，可以考虑增大shared_pool_size ,但是如果超过500M可能是危险的，达到1G可能会造成CPU的严重负担，系统甚至瘫痪。所以shared_pool_size 如果超过300M还命中率不高，那么应该从应用上找原因而不是一味的增加内存，shared_pool_size 过大主要增加了管理负担和latch 的开销。
log_buffer :  128K ----  1M 之间通常问题不大，不应该太大
large_pool_size :如果不设置MTS，通常在 RMAN 、OPQ 会使用到，但是在10M ---  50M 应该差不多了。假如设置 MTS,则由于 UGA 放到large_pool_size 的缘故，这个时候依据 session最大数量和 sort_ares_size 等参数设置，必须增大large_pool_size 的设置，可以考虑为  session *  (sort_area_size + 2M)。这里要提醒一点，不是必须使用MTS，我们都不主张使用MTS，尤其同时在线用户数小于500的情况下。
java_pool_size : 若不使用java，给30M通常就够了
data  buffer ,在做了前面的设置后，凡可以提供给oracle的内存，都应该给data buffer = (db_block_size * db_block_buffers)
在9i 中可以是 db_cache_size
还有2个重要参数我们需要注意
sort_area_size and  hash_area_size
这两个参数在非MTS下都是属于PGA ，不属于SGA,是为每个session单独分配的，在我们的服务器上除了OS + SGA,一定要考虑这两部分
(****)  : OS 使用内存+  SGA + session*(sort_area_size + hash_area_size + 2M) < 总物理RAM  为好

这样归结过来，假定oracle是 32 bit ,服务器RAM大于2G ，注意你的PGA的情况，,则建议
shared_pool_size + data buffer +large_pool_size + java_pool_size < 1.6G

再具体化，注意满足上面(****) 的原则的基础上可以参考如下设置
如果512M RAM
建议 shared_pool_size = 50M, data buffer = 200M
如果1G RAM
shared_pool_size = 100M , data  buffer = 500M
如果2G
shared_pool_size = 150M ,data  buffer = 1.2G
物理内存再大已经跟参数没有关系了

假定64  bit  ORACLE
内存4G
shared_pool_size =  200M , data  buffer = 2.5G
内存8G
shared_pool_size = 300M , data  buffer = 5G
内存 12G
shared_pool_size = 300M-----800M , data  buffer = 8G
 
以上仅为参考值，不同系统可能差异比较大，需要根据具体情况调整。建议在设置参数的同时，init中使用 lock_sga ，在不同的平台上可能有不同的方式，使得SGA锁定在物理内存中而不被放入 SWAP 中，这样对效率有好处

关于内存的设置，要再进行细致的调整，起的作用不大，但可根据statspack信息和 v$system_event,v$sysstat,v$sesstat,v$latch  等view信息来考虑微调

 

设置32位的windows 2003 中oracle SGA内存使用大于1.7的方法--PAE

上一篇 / 下一篇  2008-12-11 17:14:10 / 个人分类：Linux / Unix
查看( 180 ) / 评论( 0 ) / 评分( 0 / 0 )

设置32位的windows2003 使用大于1.7的内存

前两天出差遇到:32位的windows 2003 使用大于1.7的内存的问题，PCserver上内存为16G, 但由于32位的CPU的在windows系统中2G给系统用2G给应用程序使用，如系统及oracle参数不作修改时，oracle的SGA内存使用不能超过1.7G，所以要对一些进行windosITPUB个人空间S`X9N {$O4]&v;O%y|
ows和oracle参数据进行修改,大致有以下几步：

一 windows 上的参数据修改：
a9t{,L7p/P F40011   1. 修改boot.ini文件，加/3GB /PAE:ITPUB个人空间:^%ug'g m-b4P%|a |Y_
      在这行，multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows" /3GB /PAE

   2.修改windows 注册表:
NiM+o `,Nk+w40011     regedit到HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 这个目录中找到AWE_WINDOW_MEMORY这个参数，将其修改为oracle需要内存的大小:例如：6G时为：6*1024*1024*1024ITPUB个人空间-|?I7A'mC0~Nx
     这个参数如不存大时，可以新建一个字符串名为AWE_WINDOW_MEMORY，值为上面讲过的大小,这个值需要足够大，不够时将报：ITPUB个人空间"af-YxCj
         ORA-27102 out of memory
)c{v n;_Y)n40011         OSD-00034 Message 34 not found;  Product=RDBMS;facility =SOSDITPUB个人空间-J+ [9u7ZX;T+^1O
         O/S Error: (OS 8) Not enough storage is available to process this command

ITPUB 个人空间}-Y9m3tJP"F4t
   3.修改windows控制面板中的管理工具--&gt  域安全策略--&gt 本地安全策略 --&gt锁定内存页 中加入启oracle数据库的OS用户名.

ITPUB个人空间#C)u3d1pIN
二 ORACLE数据库中要改的参数：ITPUB个人空间!QOx?Q'pX%d
   1.在改参数之前最好能先备份一个spfile到pfile 文件以防数据库修改失败时可以从这个参数文件在启动数据库: create pfile='d:\inittest.ora' from spfile;

   2.主要修改的参数为：
&urSF*_ S `Vm\40011     _db_block_lru_latches --这个参数据大小为=CPU数*2*8ITPUB个人空间 i0J+qZ&}jG(Yij
     取消参数据：db_cache_size,sga_max_size  ITPUB个人空间 o9RmDA;W#jF,^
     db_block_buffersITPUB个人空间!tmD3@6U%|
     USE_INDIRECT_DATA_BUFFERS=TRUE
#` ]']n8[%vu+}40011

SQL> shutdown immediate;ITPUB个人空间zip#yc9nZ
数据库已经关闭。ITPUB个人空间 zT2KsW"yl?0O
已经卸载数据库。
3H)V6`e5@ L,Hp40011ORACLE 例程已经关闭。ITPUB个人空间 j,QJ7rp id;A
SQL> startup
9ToR7m+x40011ORACLE 例程已经启动。

Total System Global Area 1008280152 bytesITPUB个人空间_'W1Y(N1@A]i
Fixed Size                   455256 bytesITPUB个人空间:|K"w kt5wH'o
Variable Size             478150656 bytesITPUB个人空间Da`-VH HSe
Database Buffers          528482304 bytesITPUB个人空间i8rbO2z d-b
Redo Buffers                1191936 bytesITPUB个人空间T6a0a+T7}
数据库装载完毕。
bZ-~$~nO[ D40011数据库已经打开。ITPUB个人空间2W q.p+}Me&zsf
SQL> alter system set "_db_block_lru_latches"=128 scope=spfile;

系统已更改。

SQL> alter system reset db_cache_size scope=spfile sid='*';

系统已更改。

SQL> alter system set lock_sga=false scope=spfile;

系统已更改。

SQL> alter system set db_block_buffers=1179648 scope=spfile;

系统已更改。

SQL> alter system set use_indirect_data_buffers=true scope=spfile;

系统已更改。

SQL> create pfile='d:\init2.ora' from spfile;

文件已创建。

SQL> shutdown immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

可以通过OEM来修改PGA,shared pool这些内存大小

"b{'U6gW;r&W5b`40011SQL> startup
Riy5fXr40011ORACLE 例程已经启动。

Total System Global Area 7516192768 bytes
h9AUNJ EX b40011Fixed Size                   455256 bytes
!U7t w_6b'r~40011Variable Size             478150656 bytes
\a)X!w(B)q-Vb40011Database Buffers          3528482304 bytes
+RW gx+T'hp40011Redo Buffers                1191936 bytes
"x*]:}+^.T&p.Rq"AvV40011数据库装载完毕。ITPUB个人空间#uh+t?$?T
数据库已经打开。

T;jeP0JRXU40011

下为metalink为文章：

Subject: Implementing Address Windowing Extensions (AWE) or VLM on Windows Platforms
  Doc ID: Note:225349.1 Type: BULLETIN
  Last Revision Date: 11-JUL-2007 Status: PUBLISHED

PURPOSE
-------

To address the growing need for use of more memory on 32-Bit Windows platforms,
and explain how AWE is implemented by Oracle on Windows.

 
SCOPE & APPLICATION
-------------------
    Oracle DBA's running on the Microsoft Windows platform.
    Oracle Support Analysts, Field Engineers troubleshooting problems
    related to AWE and/or memory issues on Windows.

AWE Memory implementation on Windows 2000
------------------------------------------
 
    A common question on the Windows NT/Windows 2000 platform. revolves around
    how to take advantage of systems with more than 4 GB of RAM.  As discussed
    in MetalinkNote 46001.1andNote 46053.1, the 32-Bit process address
    space for any process on Windows equates to a total of 4GB of addressable
    RAM. Of this, by default, 2GB is reserved for the process itself, and 2GB
    for the kernel.  On systems running either Windows 2000 Advanced Server,
    or Windows NT 4.0 Enterprise Edition, this ratio can be changed by adding
    the /3GB switch to the boot.ini, allowing a process to address 3GB and
    reserving 1GB for the kernel.  However, the total addressable memory for
    a single process is still only 4GB.
    See alsoNote 1036312.6: Utilizing Up to 3GB Virtual Memory on Windows NT Server 4.0

__________________________________________________________________

What can be done to address memory beyond 4GB?:
===============================================

    The answer is to take advantage of Physical Address Extensions (PAE), or
    Address Windowing Extensions (AWE)(These two terms are used interchangeably,
    so the rest of this document will refer to this simply as AWE).
    AWE support is available if you are running on a machine with more than 4GB  
    of physical RAM which is running any of the below Windows operating systems:

    * Windows 2000 Datacenter Server
    * Windows 2000 Advanced Server
    * Windows 2003 Data Center Edition (32-Bit)
    * Windows 2003 Enterprise Edition (32-Bit)

    On the above operating systems, AWE support is built into the OS.  No
    special drivers are needed to take advantage of the additional memory.

  AWE CANNOT be used on the following Operating Systems:

    * Windows 2000 Server (Standard)
    * Windows 2000 Professional
    * Windows XP Home Edition
    * Windows XP Professional
    * Windows 2003 Standard Edition
    * Windows 2003WebEdition

   NOTE Also that on 64-Bit Windows operating systems, there is no need for AWE
   implementation support, because the directly addressable memory for a single
   process on 64-Bit Windows is 8 Terabytes.

__________________________________________________________________

Oracle versions that can use AWE:
=================================

    Oracle can take advantage of AWE in the following 32-Bit RDBMS releases:

    * Oracle 8.1.6.x
    * Oracle 8.1.7.x
    * Oracle 9.2.x
    * Oracle 10.1.x
    * Oracle 10.2.x

   Oracle does NOT implement AWE support in release 9.0.1.x

   AWE support is available on both the Enterprise Edition of Oracle and
   the Standard Edition of Oracle.  However, on Standard Edition of 9.2.0.1,
   you may receive the following error if trying to start the database with
   USE_INDIRECT_DATA_BUFFERS=TRUE:

   ORA-439 - FEATURE NOT ENABLED: VERY LARGE MEMORY

   In Standard Edition 9.2.0.2 and 9.2.0.3, you will not receive the above errors,
   but VLM functionality is still not enabled.  Refer to BUG#2945011 for more detail.
   This BUG is fixed in 9.2.0.3 Patch 2, and will be fixed in 9.2.0.4 as well.

__________________________________________________________________

Enabling support at the OS level:
==================================

    AWE can be enabled at the OS by adding the /PAE switch to the boot.ini
    as such:

multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Advanced Server" /PAE

    It IS possible to have BOTH the /PAE and /3GB switch in place on the same
    machine, as such:

multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Advanced Server" /3GB /PAE

    However, be aware that if BOTH switches are in place, the server will only
    be able to recognize up to 16GB of RAM.  If you are working with a server
    with more than 16GB of RAM, you will need to choose between the two.

    It is important to note that once either or both of these switches are in
    place in the boot.ini, ALL processes running can take advantage of these
    switches.  Thus, in a case where multiple Oracle instances are running on
    the same server, ALL instances can take advantage of the additional memory               
    afforded by these switches, up to the amount of physical memory on the box.

Operating System Privileges Needed at the OS Level:
====================================================

   In order to take advantage of the additional memory afforded through PAE,
   the operating system user account which is used to start the OracleService
   must be granted the 'Lock Pages in Memory' system privilege at the operating system
   level.   By default, the OracleService starts as the LocalSystem account.
   The LocalSystem account has the privilege to Lock Pages in Memory granted to
   it by default.

   However, if you change the OracleService to logon as a user OTHER than
   LocalSystem, you may see the following errors when attempting to start the
   database with USE_INDIRECT_DATA_BUFFERS set to TRUE :

   SQL> startup pfile=c:\temp\initscott.ora
   ORA-27102: out of memory
   OSD-00010: Message 10 not found;  product=RDBMS; facility=SOSD
  
   O/S-Error: (OS 1300) Not all privileges referenced are assigned to the caller.

   To rectify this, you must grant the 'Lock pages in memory' privilege to the user
   that the OracleService starts as.  To do this, click on:
   Start ->  Programs -> Administrative Tools -> Local Security Policy
   (on a Domain Controller, click on 'Domain Security Policy' instead of 'Local Security Policy')
   Double-click on the 'Lock Pages in memory' policy.
   Add the appropriate user and click 'Ok'.
   Restart the OracleService

__________________________________________________________________

Understanding the Oracle implementation of AWE support:
=======================================================

    What the PAE switch allows you to do from the Oracle perspective is to
    increase the amount of memory that can be used for the Oracle Database
    Block Buffer Cache.  It is important to note that this additional memory
    can ONLY be used by Oracle in the form. of an increased value for
    DB_BLOCK_BUFFERS. 

    There is still confusion on the old style. of VLM versus AWE on Windows 2000.
    With VLM on Windows NT 4.0, there was the concept of pointers pointing to
    the extended memory area, but that is no longer the case on Windows 2000.
    Instead, the windowing technology as described in these articles is being
    used.  For more information on AWE/PAE implementation on the Windows
    platform, refer to Microsoft's website.

    As mentioned previously, with AWE enabled, this allows the process(es)
    (in this case ORACLE.EXE) to use memory above and beyond the 4GB
    mark defined by a 32-Bit Process Address space.  The physical location of
    these blocks does not matter.  However, the database blocks must still be
    accessed from within a ‘window’, which exists (logically) in that regular
    3GB process address space.
    The size of this window is defined by a registry setting in the HOME key for
    Oracle (HKLM\Software\Oracle\Homex) called AWE_WINDOW_MEMORY.  By default,
    this value is 1GB, so if this value is not set in the registry, 
    AWE_WINDOW_MEMORY will be 1GB. 

    If you add the registry key yourself, the datatype should be a string value,
    or a REG_SZ.   The value for AWE_WINDOW_MEMORY must be specified in BYTES.

    It is important to realize that any database blocks accessed by Oracle
    (or any user/background thread within Oracle.exe) must first be mapped into
    the 'window' defined by AWE_WINDOW_MEMORY.  In this scenario, it does not
    matter where the blocks are physically located - there is no need to be
    concerned with where the blocks are physically residing.  The window will be
    drawn around the block (i.e. the block will be mapped) wherever it is located 
    in memory.  If the block is in memory but has not been mapped into the
    ‘window’, then it may be necessary to unmapped another block that IS in the
    window, in order to accommodate the new block.  While this mapping and
    unmapping of blocks does add some cost, it is still faster than incurring
    an I/O operation to read the block from disk.  This will be discussed
    further down in the section on troubleshooting.
   
    Note:  

    Keep in mind that if there are multiple instances on a machine with
    the /PAE switch enabled, ALL instances can take advantage of the additional
    memory.  However, AWE_WINDOW_MEMORY cannot be set on a per-instance basis,
    so all databases that are running out of the HOMEx key where
    AWE_WINDOW_MEMORY is set will inherit the same value.

__________________________________________________________________

Enabling AWE Support at the Database/Instance Level:
====================================================

    To enable the AWE implementation on Oracle, you must set the following
    parameter in the init file (or spfile) used to start the instance:

      USE_INDIRECT_DATA_BUFFERS=TRUE

    Note again that the buffer cache MUST be defined using the parameter
    DB_BLOCK_BUFFERS, no matter what version of the RDBMS you are running. 
    The 9.2 feature allowing for Multiple block sizes in a database will be
    disabled if you set USE_INDIRECT_DATA_BUFFERS=TRUE, and you cannot specify
    the DB_CACHE_SIZE parameter to define the size of the buffer cache.
   

    On 9.2, if you attempt to startup a database with this combination of
    parameters:

      USE_INDIRECT_DATA_BUFFERS=TRUE
      DB_CACHE_SIZE=xxxxx (Any number)

    The startup will fail with the following error:

      SQL> startup
      ORA-00385: cannot enable Very Large Memory with new buffer cache
      parameters

    You must change DB_CACHE_SIZE to use DB_BLOCK_BUFFERS instead, as was the
    syntax under Oracle8i and earlier.

 

__________________________________________________________________

AWE_WINDOW_MEMORY Within the 3GB Process Address Space:
=======================================================

    If you are using /PAE and the /3GB switch together, the address space for
    ORACLE.EXE will be 3GB.  The value for AWE_WINDOW_MEMORY must come from the
    normal address space used by the ORACLE.EXE process.  Memory that comes
    from that 3GB address space addressable by the oracle.exe process includes
    the following:

     ·The Value for AWE_WINDOW_MEMORY
     ·The rest of the SGA (shared_pool, large_pool, java_pool, log_buffers, etc)
     ·Overhead for Oracle.exe and DLL’s (65-100M depends on version & options)
     ·Stack space for all threads (Defaults to 1MB/thread, unless orastack
         is used)
     ·PGA and UGA memory for all user sessions

    Therefore, the value for AWE_WINDOW_MEMORY should be tuned such that mapping
    and unmapping operations are avoided as much as possible, while still
    allowing enough memory within the 3GB address space for the rest of the
    process memory that MUST fit within the 3GB (i.e. overhead, remaining SGA
    components and all user connection memory (stack + uga + pga) noted above).

    The total size of the buffer cache can then be set to the amount of
    physical memory remaining above the 4GB barrier, plus AWE_WINDOW_MEMORY.
    On a machine with 12GB of RAM, using the default value of 1GB for
    AWE_WINDOW_MEMORY, your total buffer cache could theoretically be as high
    as 9GB:

     (Total RAM - 4GB + AWE_WINDOW_MEMORY) = 12GB - 4GB + 1GB = 9GB

    In reality, your maximum buffer cache size will be somewhat less than
    this, allowing for some overhead and additional processes running on the
    system.

    Attempting to startup the database with a buffer cache larger than the
    maximum value as calculated above may result in the following errors:

      ORA-27102 out of memory
      OSD-00034 Message 34 not found;  Product=RDBMS;facility =SOSD
      O/S Error: (OS 8) Not enough storage is available to process this command

    (Note - If you are on Release 9.2, another possible cause for these errors
    is  noted further down, in the troubleshooting section)

    As mentioned above, the buffer cache must be specified using
    DB_BLOCK_BUFFERS rather than DB_CACHE_SIZE, so assuming an 8K block
    size (8192), to get a 9GB buffer cache, you would set the following init
    parameters:

      DB_BLOCK_BUFFERS = 1179648
      DB_BLOCK_SIZE = 8192

__________________________________________________________________

Troubleshooting AWE_WINDOW_MEMORY implementation:

=========================
=========================

Minimum Value Required for AWE_WINDOW_MEMORY in 9.2 and Above:
==============================================================

    Here are key points to understand when using AWE_WINDOW_MEMORY:

     1.  Under Oracle 8.1.7 we do NOT enforce a minimum value for
         AWE_WINDOW_MEMORY to be able to start the database.
     2.  This was changed under Oracle9i Release 2, such that we DO
         enforce a minimum value for AWE_WINDOW_MEMORY. This change was
         done to help improve performance by enforcing a larger window size.
     3.  You can alter the minimum required value for AWE_WINDOW_MEMORY
         under 9.2 by changing/setting the value of the parameter
         _DB_BLOCK_LRU_LATCHES.  Under 8.1.7, this parameter was named
         DB_BLOCK_LRU_LATCHES.  However, under 9.x, this parameter was
         changed to be a hidden parameter.

    The minimum value for AWE_WINDOW_MEMORY starting with 9.2 is calculated as such:

    MIN(AWE_WINDOW_MEMORY)=(4096 * DB_BLOCK_SIZE * _DB_BLOCK_LRU_LATCHES)/8

    Starting with 9.2, to calculate the value for _DB_BLOCK_LRU_LATCHES, we need
    this formula:

    _DB_BLOCK_LRU_LATCHES = (Max buffer pools * SETS_PER_POOL)

    Max Buffer Pools is a constant = 8
    SETS_PER_POOL is variable, and depends on whether or not VLM is enabled.

    SETS_PER_POOL = 2* CPU_COUNT   (if VLM is enabled)
    SETS_PER_POOL= CPU Count /2  (If VLM is NOT enabled)

    /* Recall that VLM is enabled by setting USE_INDIRECT_DATA_BUFFERS=TRUE

    So, as you can see, the value for _DB_BLOCK_LRU_LATCHES in 9.2 and above is
    dependent on the number of CPU's in the box, and therefore
    MIN(AWE_WINDOW_MEMORY) is dependent on the # of CPU's as well as the
    DB_BLOCK_SIZE.  The larger the Block Size, and the more CPU's in a system,
    the higher the value for MIN(AWE_WINDOW_MEMORY). Here are a couple of
    example configurations and caclulations showing MIN(AWE_WINDOW_MEMORY).

    Example #1:
    ----------------
      # of CPU's = 8
      DB_BLOCK_SIZE = 8192
      Total RAM = 8GB

      SETS_PER_POOL = 2 * CPU_COUNT = 16
      _DB_BLOCK_LRU_LATCHES = (max buffer Pools * sets_per_pool) = 8*16 = 128
      MIN(AWE_WINDOW_MEMORY) =(4096*DB_BLOCK_SIZE*_DB_BLOCK_LRU_LATCHES)/8 =
      ( 4096 * 8192 * 128) / 8 = 536870912 bytes = 512 MB

    Example #2:
    ---------------
      # of CPU's = 16
      DB_BLOCK_SIZE = 8192
      Total RAM = 16 GB

      SETS_PER_POOL = 2 * CPU_COUNT = 32
      _DB_BLOCK_LRU_LATCHES = (max buffer Pools * sets_per_pool) = 8*32 = 256
      MIN(AWE_WINDOW_MEMORY) =(4096*DB_BLOCK_SIZE*_DB_BLOCK_LRU_LATCHES)/8 =
      ( 4096 * 8192 * 256) / 8 = 1073741824 bytes = 1024 MB

   
    These values above are the minimum values required for AWE_WINDOW_MEMORY
    to be set to, UNLESS you explicitly set _DB_BLOCK_LRU_LATCHES to a lower
    value.  If AWE_WINDOW_MEMORY is not set to the minimum value, you will
    receive the following errors:

      ORA-27102 out of memory
      OSD-00034 Message 34 not found;  Product=RDBMS;facility =SOSD
      O/S Error: (OS 8) Not enough storage is available to process this command

    If you receive these errors when trying to start the database under 9.2 or 10g,
    this may be because the AWE_WINDOW_MEMORY value in the registry is set
    too low for the calculated minimum value.  If you cannot increase the
    value for AWE_WINDOW_MEMORY, then you can explicitly set
    _DB_BLOCK_LRU_LATCHES to a value lower than the calculated value, and
    retry the startup.

    _DB_BLOCK_LRU_LATCHES must be at least 8 (Equal to the maximum number of
    buffer pools)

    Note #1 - Recall from the earlier section that these errors may also occur if
    you are trying to start up with a buffer cache that is too large for the
    physical memory available.

    Note #2 - The same errors above have also been observed with a buffer
    cache that is too small.  When USE_INDIRECT_DATA_BUFFERS is set to TRUE
    the value for DB_BLOCK_BUFFERS should equate to a buffer cache that is
    AT LEAST equal to AWE_WINDOW_MEMORY.  In most cases, the total buffer
    cache size will be greater than AWE_WINDOW_MEMORY.  If you attempt to
    start up with a buffer cache that is too small (i.e. < AWE_WINDOW_MEMORY)
    that may also result in the ORA-27102 error.

    Note#3 - It has been observed on some systems that you may need to add a few
    additional meg to AWE_WINDOW_MEMORY to calculate for overhead.  Therefore, if
    you go through the above calculations, and the instance still does not start,
    try adding an additional 10 Meg or so to the calculated value.
 
    Note#4 - Also, keep in mind that when calculating the # of CPU's in the system,
    you have to take hyperthreading into account.  On a hyperthreaded system, the OS
    will think that you have double the # of CPU's in the system over what you actually
    have, and this is the number that must be used in the calculations.

How to calculate the maximum used memory
=========================================
    With respect to awe_window_memory the following maximum amount of memory can be used
    from physical memory:

    The SGA size is composed from:
    ((db_block_buffers * block size) +  (shared_pool_size + large_pool_size +
    java_pool_size + log_buffers) + 1MB
    The size of SGA + Oracle's overhead  must not exceed the available virtual memory.

    The size of buffer cache depends on the available virual memory and can be calculated with
    buffer cache = db_block_buffer * db_block_size

CPU Spins Possible When Using AWE Implementation:
=================================================

    Use caution when setting _DB_BLOCK_LRU_LATCHES or AWE_WINDOW_MEMORY too low.
    If we are unable to map a requested buffer into the window because all of
    the space  defined by AWE_WINDOW_MEMORY is in use with buffers already
    actively being accessed, then we spin and wait, checking every so often
    until an existing buffer in the window can be unmapped, and a new buffer can
    be mapped in.
 
    This spin will consume CPU cycles until enough buffers can be
    Mapped/Unmapped to satisfy the request.  In some cases, there may be so
    many buffers needing to be mapped into the window, that DBWR will consume
    100% of cycles on all CPUs, effectively locking up the machine.  This is
    normal behavior. under some circumstances, and is simply an indication that
    AWE_WINDOW_MEMORY is too small. 

Monitoring Mapping Operations in 9.2 and later releases:
========================================================

    Starting with 9.2, we have added additional statistics which can be
    measured in v$sesstat (sesssion-level stats) and v$sysstat (system-wide
    stats):

    STATISTIC# NAME
    ---------- ------------------------------
    154 number of map operations
    155 number of map misses

    This query below will give you system-wide information on map
    operations and map misses:
   
      SQL> select * from v$sysstat where statistic# in (154, 155);

    If the # of Map misses is relatively high, or particularly of the # of map
    misses increases consistently over time, this may be an indication that the
    value for AWE_WINDOW_MEMORY is set too low.

   
    Note that the statistic#'s change from version to version, so the below query
    will allow you to determine the statistic# for your particular DB version.
    this example is from a 10gR2 database:

SQL> select statistic#, name from v$sysstat where name like '%map %';

STATISTIC# NAME
---------- ----------------------------------------------------------------
       168 number of map operations
       169 number of map misses

   So simply substitute in the correct statistic#, depending on your DB version.

Dynamic Memory Management/Automatic Memory Management with AWE Enabled
=============================================================

Oracle10g introduces the concept of Automatic Memory Management,
whereby the Oracle RDBMS will dynamically adjust SGA parameters
such as SHARED_POOL_SIZE, JAVA_POOL_SIZE, DB_CACHE_SIZE, etc.

This is enabled by setting the parameter SGA_TARGET to a non-zero value.
However, in order for this to work properly, you must use DB_CACHE_SIZE
for the buffer cache.   When setting USE_INDIRECT_DATA_BUFFERS, you cannot
set DB_CACHE_SIZE, as noted above.  Therefore, SGA_TARGET should not be set
when using AWE - these two features are mutally exclusive.
When setting USE_INDIRECT_DATA_BUFFERS=TRUE on Oracle10g, you should also
set SGA_TARGET to 0.

Diagnosing Spins Associated With AWE in 8.1.x:
==============================================

    The above stats are not available in 8.1.7, so if you are encountering
    problems with CPU spins, with AWE_WINDOW_MEMORY enabled, it is more
    difficult to diagnose.

    You can start by identifying and monitoring the thread associated with
    DBWR via the following query:

      SQL> select b.name, p.spid  from v$process p, v$bgprocess b
      where p.addr=b.paddr;

      NAME  SPID
      ----- ---------
      PMON  1900
      DBW0  1956
      LGWR  572
      CKPT  1908
      SMON  1808
      RECO  920
      SNP0  1784
      SNP1  1892
      SNP2  1896
      SNP3  1844

      10 rows selected.

    As you can see, DBWR has an SPID of 1956, which will equate to the
    Thread ID of that thread within the Oracle executable.  This thread can
    then be monitored using Performance Monitor and/or the PSLIST utility,
    which is available as a free download from http://www.sysinternals.com

    If your monitoring shows that DBWR is consuming excessive CPU, you can
    attempt to get an errorstack from that thread using oradebug:

      SQL> oradebug setospid 1956
      Oracle pid: 3, Windows thread id: 1956, image: ORACLE.EXE
      SQL> oradebug unlimit
      Statement processed.
      SQL> oradebug dump errorstack 3
      Statement processed.

    This should dump the errorstack to the DBWR trace file, found in BDUMP. 
    If the errorstack contains the function SKGMMAP, this is an indication
    that DBWR is working to map/unmap database block buffers.

Note:   In 8.1.7 of the RDBMS, you cannot use DBWR_IO_SLAVES in combination with
USE_INDIRECT_DATA_BUFFERS, due to BUG#3042660/BUG#2215894.  You must leave
DBWR_IO_SLAVES at its default value - otherwise, buffers are not unmapped
and eventually a spin of the process will result.
This problem is resolved in 9.2.0.1 - the fix is NOT backported to 8.1.7

KNOWN ISSUES
--------------------
BUG#2461474 - SHOW SGA DOES NOT SHOW CORRECT # OF DB BUFFERS412485 - LONG SHUTDOWN TIME WITH AWE_WINDOW_MEMORY: FIXED IN 8.1.7.1
BUG#1406194 - AWE_WINDOW_MEMORY NOT RELEASED WHEN DB SHUTDOWN:  FIXED IN 8.1.7.1
BUG#2520796 - ORA-439 TRYING TO ENABLE VLM IN STANDARD EDITION OF ORACLE - FIXED IN 9.2.0.4
BUG#2945011 - VLM DOES NOT WORK ON STANDARD EDITION ORACLE 9.2.0.2 ON WINDOWS - FIXED IN 9.2.0.4
BUG#3120033 - ORA-600[KCBVMAP] may occur with AWE, or DBWR may crash with ORA-471 on 9.2.0.4 - FIXED 9.2.0.4 PATCH 2
BUG#3042660 / BUG#2215894 - IO SLAVES DON'T UNMAP BUFFERS ON LINUX IN VLM MODE (APPLIES TO WINDOWS AS WELL)

RELATED DOCUMENTS
-----------------Bug 1803377- WINDOWS AWE MEMORY AND CPU - CAN'T MAP THE BUFFERNote 46001.1- Oracle Database and the Windows NT memory architectureNote 46053.1- Windows NT Memory Architecture Overview

Oracle 8.1.7 Release Notes for Windows:
http://download-west.oracle.com/docs/pdf/A85305_01.pdf

Oracle9i Database Getting Started
Release 2 (9.2) for Windows
Part Number A95490-01
Chapter 4: Oracle9i Architecture on Windows
http://download-west.oracle.com/docs/cd/B10501_01/win.920/a95490/architec.htm#1005809

Oracle? Database Platform. Guide
10g Release 1 (10.1) for Windows
Part Number B10113-01
Chapter 1:  Oracle Database Architecture on Windows
http://otn.oracle.com/pls/db10g/portal.portal_
<

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/40011/viewspace-674039/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/40011/viewspace-674039/