一，SGA作用
oracle数据库用户都可以共享SGA中的数据，通常为了优化性能，我们总是期望在物理内存允许的情况下，设置更高的SGA区，以减少物理I/O（SGA中的数据缓冲区的增大可以有效减少物理读）。oracle对数据的处理过程中，代价最昂贵的就是物理I/O操作了。
二，BufferCache
1，在oracle 9i之前，Buffer cache的设置主要由两个参数决定：db_block_buffers和db_block_size。
2，db_block_buffers设置了分配给Buffer Cache的缓冲区数量，这个数量乘以db_block_size得出的才是Buffer Cache的大小。
3，从oracle 9i之后，oracle引入了一个新的参数db_cache_size。该参数用来定义主Block_size（db_block_size定义的大小）的Default缓冲池的大小。
4，SQL> select name ,value from v$parameter where name in ('db_block_buffers','db_block_size');
NAME
---------------------------------VALUE---------------------------------
db_block_buffers 0
db_block_size 8192
SQL>
5，db_cache_size最小值为一个粒度oracle 9i引入的一个新概念，连续虚拟内存分配的单位，其大小决定于估计的SGA的总大小（SGA总大小有SGA_MAX_SIZE参数得到）。如果估计的SGA的大小<128MB，则值为4MB；否则值为16MB；
这个Granule大小受到一个内部隐含参数_ksmg_granule_size 的控制。
6，内存空间有限的，oracle管理Buffer Cache使用的LRU算法，但是这又带来了另外一个问题，很多批处理操作（如全表扫描等）可能会导致Buffer Cache的刷新，将经常使用的数据"挤出"Buffer Cache，在不同的版本中，oracle不停地改进LRU算法，以避免这类操作的过度影响。oracle提供了Buffer Cache的多缓冲池技术从另外一个方面来解决这个问题。所谓的多缓冲技术是指，根据不同的数据的不同的访问方式，将Buffer Cache分为Default、Keep和Recycle池三个部分，对于常用的使用的数据，可以在建表时就指定存放在Keep池中;对于一次性读取使用 的数据，可以将其存放在Recycle池中；Keep池中的数据倾向于一直保存，Recycle池中的数据倾向于即时老化，而Default池则存放未指定存储池的数据，按照LRU算法管理。
7，默认情况下，所有表都使用DEFAULT池，它的大小就是数据缓冲区BufferCache的大小，由初始化参数db_cache_size决定。如果在 创建数据表或修改数据表时，指定STORAGE（buffer_pool_size）或者STORAGE（buffer_pool_recycle）语 句，就设置了这张表使用keep或者recycle缓冲区。这两个缓冲区的大小分别有初始化参数db_keep_cache_size和 db_recycle_cache_size来决定。
三，粒度
1，SQL> @ GetParDescrb.sql
Enter value for par: _ksmg_granule_size
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%_ksmg_granule_size%'
NAME VALUE DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_ksmg_granule_size 4194304 granule size in bytes
2， GetParDescrb.sql里面的内容：
SET linesize 120
COL name for a30
COL value for a20
COL describ for a60
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';
四，查看参数
1，SQL> show parameter cache_size;
NAME TYPE VALUE
------------------------------------ ----------- -----------------
db_keep_cache_size big integer 4M
db_recycle_cache_size big integer 4M
2， select id,name,block_size,current_size,target_size from v$buffer_pool;
ID NAME BLOCK_SIZE CURRENT_SIZE TARGET_SIZE
---------- -------------------- ---------- ------------ -----------
1 KEEP 8192 4 4
2 RECYCLE 8192 4 4
3 DEFAULT 8192 196 196
SQL>
3， SQL> select * from v$sga;,
NAME VALUE
-------------------- ----------
Fixed Size 1219184
Variable Size 96470416
Database Buffers 213909504
Redo Buffers 2973696
4，SQL> show sga;
5，SQL> show parameter sga_max_size;
6，Fixed Size 部分是SGA中的固定部分，包含数据库和实例的状态等通用信息，后台进程需要访问这部分信息，不存储用户的数据，通常只需要很小部分内存。
7，SQL> select * from v$sgastat;
8，SQL> select * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1219184 No
Redo Buffers 2973696 No
Buffer Cache Size 213909504 Yes
Shared Pool Size 58720256 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 314572800 No
Startup overhead in Shared Pool 37748736 No
Free SGA Memory Available 29360128
11 rows selected.
SQL>
五，Shmmax
1，shmmax内核参数定义的是系统允许的单个共享内存段的最大值，如果该参数小于oracle SGA设置，那么SGA仍然可以创建成功，但是会被分配多个共享内存段。通常推荐通过调整shmmax设置，将SGA限制在一个共享内存段中。
2，[root@rhel ~]# more /proc/sys/kernel/shmmax
2147483648
3，[root@rhel ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
4，[root@rhel ~]# uname -r
2.6.18-164.el5,
5，可以通过ipcs命令查看此设置下共享内存的分配,
[root@rhel ~]# ipcs -sa
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x4032a294 32768 oracle 640 318767104 20
0x00000000 98305 root 600 393216 2 dest
0x00000000 131074 root 600 393216 2 dest
0x00000000 163843 root 600 393216 2 dest
0x00000000 196612 root 600 393216 2 dest
0x00000000 229381 root 600 393216 2 dest
0x00000000 262150 root 600 393216 2 dest
0x00000000 294919 root 600 393216 2 dest
0x00000000 327688 root 600 393216 2 dest
0x00000000 360457 root 600 393216 2 dest
0x00000000 393226 root 600 393216 2 dest
------ Semaphore Arrays --------
key semid owner perms nsems
0xddaf4f30 98304 oracle 640 154
------ Message Queues --------
key msqid owner perms used-bytes messages
6，[root@rhel ~]# ps -ef | grep dbw
oracle 2883 1 0 17:12 ? 00:00:00 ora_dbw0_orcl
root 5618 3276 0 19:36 pts/3 00:00:00 grep dbw
7，针对一个进程，使用pmap工具可以看到这个共享内存段的地址空间
8，[root@rhel ~]# pmap 2883
2883: ora_dbw0_orcl
00110000 268K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libocrb10.so
00153000 4K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libocrb10.so
00154000 344K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libocrutl10.so
001aa000 16K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libocrutl10.so
001ae000 4K rwx-- [ anon ]
001af000 88K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libdbcfg10.so
001c5000 8K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libdbcfg10.so
001c7000 36K rwx-- [ anon ]
001d0000 4K rwxs- /u01/app/oracle/10.2.0/db_1/dbs/hc_orcl.dat
001d1000 8K r-x-- /lib/libdl-2.5.so
001d3000 4K r-x-- /lib/libdl-2.5.so
001d4000 4K rwx-- /lib/libdl-2.5.so
001d5000 148K r-x-- /lib/libm-2.5.so
001fa000 4K r-x-- /lib/libm-2.5.so
001fb000 4K rwx-- /lib/libm-2.5.so
001fc000 76K r-x-- /lib/libpthread-2.5.so
0020f000 4K r-x-- /lib/libpthread-2.5.so
00210000 4K rwx-- /lib/libpthread-2.5.so
00211000 288K rwx-- [ anon ]
00263000 4K rwx-- [ anon ]
00264000 1276K r-x-- /lib/libc-2.5.so
003a3000 4K --x-- /lib/libc-2.5.so
003a4000 8K r-x-- /lib/libc-2.5.so
003a6000 4K rwx-- /lib/libc-2.5.so
003a7000 12K rwx-- [ anon ]
003aa000 36K r-x-- /lib/libnss_files-2.5.so
003b3000 4K r-x-- /lib/libnss_files-2.5.so
003b4000 4K rwx-- /lib/libnss_files-2.5.so
003dd000 32K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libclsra10.so
003e5000 4K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libclsra10.so
00475000 4K rwx-- [ anon ]
00520000 128K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libskgxp10.so
00540000 8K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libskgxp10.so
00578000 104K r-x-- /lib/ld-2.5.so
00592000 4K r-x-- /lib/ld-2.5.so
00593000 4K rwx-- /lib/ld-2.5.so
00594000 1904K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libnnz10.so
00770000 152K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libnnz10.so
00796000 1040K rwx-- [ anon ]
008b7000 4K rwx-- [ anon ]
008e0000 4K rwx-- [ anon ]
00900000 4K r-x-- /usr/lib/libaio.so.1.0.1
00901000 4K rwx-- /usr/lib/libaio.so.1.0.1
009b2000 76K r-x-- /lib/libnsl-2.5.so
009c5000 4K r-x-- /lib/libnsl-2.5.so
009c6000 4K rwx-- /lib/libnsl-2.5.so
009c7000 1564K rwx-- [ anon ]
00b81000 436K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libocr10.so
00bee000 4K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libocr10.so
00c65000 4K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libskgxn2.so
00c66000 4K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libskgxn2.so
00c71000 4K rwx-- [ anon ]
00c9e000 4K r-x-- [ anon ]
00d02000 140K rwx-- [ anon ]
00d65000 880K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libhasgen10.so
00e41000 20K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libhasgen10.so
00e46000 12K rwx-- [ anon ]
00e49000 7028K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libjox10.so
01526000 260K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libjox10.so
01567000 4K rwx-- [ anon ]
08048000 77032K r-x-- /u01/app/oracle/10.2.0/db_1/bin/oracle
0cb82000 324K rwx-- /u01/app/oracle/10.2.0/db_1/bin/oracle
0cbd3000 120K rwx-- [ anon ]
0d975000 264K rwx-- [ anon ]
20000000 311296K rwxs- [ shmid=0x8000 ]
b7ef6000 64K rwx-- /dev/zero
b7f06000 64K rwx-- /dev/zero
b7f16000 64K rwx-- /dev/zero
b7f26000 64K rwx-- /dev/zero
b7f36000 64K rwx-- /dev/zero
b7f46000 64K rwx-- /dev/zero
b7f56000 64K rwx-- /dev/zero
b7f66000 64K --x-- /dev/zero
b7f76000 40K rwx-- /dev/zero
b7f80000 64K rwx-- /dev/zero
b7f90000 64K rwx-- /dev/zero
b7fa0000 64K rwx-- /dev/zero
b7fb0000 128K rwx-- /dev/zero
b7fd0000 64K rwx-- /dev/zero
b7fe0000 64K rwx-- /dev/zero
b7ff0000 24K rwx-- /dev/zero
bfacd000 84K rwx-- [ stack ]
total 406660K
[root@rhel ~]#
8，为了避免多个共享内存段，可以修改shmmax内核参数，使SGA存在于一个内存共享段中。通过修改/proc/sys/kernel/shmmax参数可以达到这个目的。
9， [root@rhel ~]# more /proc/sys/kernel/shmmax
2147483648
10，[root@rhel ~]# echo 2147490000 >/proc/sys/kernel/shmmax
[root@rhel ~]#
注：重启数据库之后更改才生效。
11，有时候警告数据库异常的关闭，如果数据库异常关闭，后台进程未正常退出，共享内存段没有来得及释放导致，通过ipcs命令找到共享内存段id（shared memeory id），然后通过ipcrm强制释放内存段。
在oracle9i中，可以设置参数SGA_MAX_SIZE，该参数可以控制各缓冲池使用内存的总和，本质上是在进程中预先分配一段虚拟内存地址备用而不分配物理地址，目的是防止和进程的私有地址段的冲突。
六，动态修改SGA参数时，存在的一些常见的限制
1，修改的内存大小必须是粒度（Granule）大小的整数倍，否则自动向上取整；
2，SGA总的大小不能超过SGA_MAX_SIZE;
3，SGA最低配置为3个粒度（Granule），一个粒度是用于固定SGA（包括重做缓冲区），一个粒度是用户缓冲区高速缓存，一个粒度用户共享池。
七，advice
1，SQL> select tname from tab where tname like '%ADVICE%';
TNAME
------------------------------
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_JAVA_POOL_ADVICE
DBA_HIST_MTTR_TARGET_ADVICE
DBA_HIST_PGA_TARGET_ADVICE
DBA_HIST_SGA_TARGET_ADVICE
DBA_HIST_SHARED_POOL_ADVICE
DBA_HIST_STREAMS_POOL_ADVICE
GV_$DB_CACHE_ADVICE
GV_$JAVA_POOL_ADVICE
GV_$MTTR_TARGET_ADVICE
GV_$PGATARGET_ADVICE_HISTOGR
2，DB_CACHE_ADVICE不同参数值的含义分别如下：OFF：关闭建议且不为建议分配内存。ON：开启建议且CPU和内存开销都会发生。READY：关闭建议但是仍保留为建议非配的内存。
3，在某些版本中，如果在参数为OFF状态时，尝试将其设置为ON，可能会出现ORA-4031错误，无法从共享池中分配内存；如果参数处于READY状态则可以将其设置为ON而不会发生错误，这是因为需要的内存已经分配。
SQL> show parameter db_cache_ad;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
SQL>
SQL> select id,name,block_size,size_for_estimate sfe ,size_factor sf,
2 estd_physical_read_factor eprf,estd_physical_reads epr
3 from v$db_cache_advice;
ID NAME BLOCK_SIZE SFE SF EPRF EPR
---------- -------------------- ---------- ---------- ---------- ---------- ----------
1 KEEP 8192 4 1 0
1 KEEP 8192 8 2 0
2 RECYCLE 8192 4 1 0
2 RECYCLE 8192 8 2 0
3 DEFAULT 8192 16 .0816 2.0812 18556
3 DEFAULT 8192 32 .1633 1.2688 11313
3 DEFAULT 8192 48 .2449 1.0517 9377
3 DEFAULT 8192 64 .3265 1 8916
3 DEFAULT 8192 80 .4082 1 8916
3 DEFAULT 8192 96 .4898 1 8916
3 DEFAULT 8192 112 .5714 1 8916
ID NAME BLOCK_SIZE SFE SF EPRF EPR
---------- -------------------- ---------- ---------- ---------- ---------- ----------
3 DEFAULT 8192 128 .6531 1 8916
3 DEFAULT 8192 144 .7347 1 8916
3 DEFAULT 8192 160 .8163 1 8916
3 DEFAULT 8192 176 .898 1 8916
3 DEFAULT 8192 192 .9796 1 8916
3 DEFAULT 8192 196 1 1 8916
最适合的值
3 DEFAULT 8192 208 1.0612 1 8916
3 DEFAULT 8192 224 1.1429 1 8916
3 DEFAULT 8192 240 1.2245 1 8916
3 DEFAULT 8192 256 1.3061 1 8916
3 DEFAULT 8192 272 1.3878 1 8916
ID NAME BLOCK_SIZE SFE SF EPRF EPR
---------- -------------------- ---------- ---------- ---------- ---------- ----------
3 DEFAULT 8192 288 1.4694 1 8916
3 DEFAULT 8192 304 1.551 1 8916
3 DEFAULT 8192 320 1.6327 1 8916
25 rows selected.
SQL>
4，STATISTICS_LEVEL控制数据库收集的统计信息的级别，该参数有3个选项。BASIC：收集基本的统计信息。TYPICAL：收集大部分的统计信息，这是系统的缺省设置，用户始终应该将该参数设置为典型。ALL：收集全部的统计信息。
5，SQL> select STATISTICS_NAME ,SESSION_STATUS ,SYSTEM_STATUS,ACTIVATION_LEVEL , SESSION_SETTABLE from v$statistics_level;
STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------------------- -------- -------- ------- ---
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
Timed Statistics ENABLED ENABLED TYPICAL YES
Timed OS Statistics DISABLED DISABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Modification Monitoring ENABLED ENABLED TYPICAL NO
Longops Statistics ENABLED ENABLED TYPICAL NO
Bind Data Capture ENABLED ENABLED TYPICAL NO
STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------------------- -------- -------- ------- ---
Ultrafast Latch Statistics ENABLED ENABLED TYPICAL NO
Threshold-based Alerts ENABLED ENABLED TYPICAL NO
Global Cache Statistics ENABLED ENABLED TYPICAL NO
Active Session History ENABLED ENABLED TYPICAL NO
Undo Advisor, Alerts and Fast Ramp up ENABLED ENABLED TYPICAL NO
16 rows selected.
6，可以看到在TYPICAL设置下，除Timed OS Statistics和Plan Execution Statistics信息不收集外，其他信息都被收集。其中，Buffer Cache Advice 受db_cache_size参数独立控制，Timed Statistics受timed_statistics参数独控制。
其他统计信息的收集都受到STATISTICS_lEVEL参数的控制。当修改为STATISTICS_LEVEL为Basic时，可以看到，除Buffer Cache Advice和Timed Statistics外，其他的收集 信息都被禁止。
7，在10g下，如果SGA自动调整，不让修改为basic；
SQL> alter system set statistics_level=basic;
alter system set statistics_level=basic
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled
8，可以通过查询v$SHARED_POOL_ADVICE视图获取关于shared_pool的建议信息：
SQL> select SHARED_POOL_SIZE_FOR_ESTIMATE SPZFE, SHARED_POOL_SIZE_FACTOR SPSF,
2 ESTD_LC_MEMORY_OBJECTS ELMO, ESTD_LC_TIME_SAVED ELTS,
3 ESTD_LC_LOAD_TIME_FACTOR ELTSF,ESTD_LC_MEMORY_OBJECT_HITS ELMOH
4 from v$shared_pool_advice;
SPZFE SPSF ELMO ELTS ELTSF ELMOH
---------- ---------- ---------- ---------- ---------- ----------
48 .8571 1039 121 1.1333 23641
56 1 1996 123 1 24514
最适合的值
64 1.1429 2907 123 1 24739
72 1.2857 4039 123 1 24778
80 1.4286 4490 123 1 24778
88 1.5714 4490 123 1 24778
96 1.7143 4490 123 1 24778
104 1.8571 4490 123 1 24778
112 2 4490 123 1 24778
9 rows selected.
9，找到这个是最好的shared_pool_size；alter system set shared_pool_size=56M; 有可能修改参时，session处于等待状态，等待事件为Background parameter adjustment。（这个也是说的oracle9i之前的版本）
八，查询等待事件
1，SQL> select sid,seq#,event,SECONDS_IN_WAIT,state
2 from v$session_wait where sid= 167;
SID SEQ# EVENT SECONDS_IN_WAIT STATE
---------- ---------- ---------------------------------------------------------------- --------------- -------------------
167 155 rdbms ipc message 48 WAITING
2，SQL> select * from v$lock where sid=167;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
32BB9BF8 32BB9C0C 167 PW 1 0 3 0 7364 0
32BB9D0C 32BB9D20 167 MR 1 0 4 0 7367 0
32BB9D68 32BB9D7C 167 MR 2 0 4 0 7367 0
32BB9DC4 32BB9DD8 167 MR 3 0 4 0 7367 0
32BB9E20 32BB9E34 167 MR 4 0 4 0 7367 0
32BB9E7C 32BB9E90 167 MR 5 0 4 0 7367 0
32BB9F34 32BB9F48 167 MR 201 0 4 0 7367 0
7 rows selected
SQL>
3，SQL> select * from v$lock ;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
32BB99D0 32BB99E4 165 XR 4 0 1 0 7162 0
32BB9A2C 32BB9A40 165 CF 0 0 2 0 7162 0
32BB9AE4 32BB9AF8 165 RS 25 1 2 0 7156 0
32BB9B9C 32BB9BB0 166 RT 1 0 6 0 7156 0
32BB9BF8 32BB9C0C 167 PW 1 0 3 0 7150 0
32BB9D0C 32BB9D20 167 MR 1 0 4 0 7153 0
32BB9D68 32BB9D7C 167 MR 2 0 4 0 7153 0
32BB9DC4 32BB9DD8 167 MR 3 0 4 0 7153 0
32BB9E20 32BB9E34 167 MR 4 0 4 0 7153 0
32BB9E7C 32BB9E90 167 MR 5 0 4 0 7153 0
32BB9ED8 32BB9EEC 164 TS 3 1 3 0 7151 0
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
32BB9F34 32BB9F48 167 MR 201 0 4 0 7153 0
12 rows selected.
九，SGA自动管理
1，数据库白天需要处理大量的OLTP任务，这些任务需要大量的Buffer Cache内存，而在夜间系统可能需要运行大量的并行批处理任务，这些任务又需要大量的Large Pool内存，为了这样的一个系统在有限的资源下高效率的运行，在oracle 10g的自动共享内存管理（Automatic Shared Memory Management，ASMM）下，这些动作都不要人工介入，当运行OLTP任务时，BufferCache会获取大部分的内存以达到良好的性能。如果系统需要运行DSS批处理任务时，内存自动转移给Large Pool，以便并行查询等可以获取更多的内存，更快的执行。
2，并非所有SGA组件都可以自动调整，可以自动分配的内存包括：Buffer Cache；Shared Pool；Java Pool；Large Pool;
3，启用自动共享内存管理，可以估算一个SGA的总大小，然后设置SGA_TARGET参数为非0值，Oracle将启动自动共享内存管理。自动内存管理需要STATISTICS_LEVEL参数设置为TYPICAL或者ALL。
4，Oracle服务器根据系统运行的情况自动调整这些内存的大小，并记录在spfile中，进程重新启动时，不会丢失之前的调整结果。自动的共享内存管理引入了一个新的后台进程MMAN（MemoryManager）。该进程用以动态调整内存组件。动态调整的依据来自系统不断收集的内存建议。
5，SQL> select pid,spid,program from v$process;
PID SPID PROGRAM
---------- ------------ ------------------------------------------------
1 PSEUDO
2 2865 oracle@rhel.oracle.com (PMON)
3 2867 oracle@rhel.oracle.com (PSP0)
4 2869 oracle@rhel.oracle.com (MMAN)
5 2884 oracle@rhel.oracle.com (DBW0)
6 2886 oracle@rhel.oracle.com (LGWR)
7 2888 oracle@rhel.oracle.com (CKPT)
8 2890 oracle@rhel.oracle.com (SMON)
9 2892 oracle@rhel.oracle.com (RECO)
10 2894 oracle@rhel.oracle.com (CJQ0)
11 2896 oracle@rhel.oracle.com (MMON)
PID SPID PROGRAM
---------- ------------ ------------------------------------------------
12 2898 oracle@rhel.oracle.com (MMNL)
13 2900 oracle@rhel.oracle.com (D000)
14 2902 oracle@rhel.oracle.com (S000)
15 16116 oracle@rhel.oracle.com (J000)
16 2907 oracle@rhel.oracle.com (ARC0)
17 2909 oracle@rhel.oracle.com (ARC1)
18 15108 oracle@rhel.oracle.com (TNS V1-V3)
19 2913 oracle@rhel.oracle.com (QMNC)
21 3128 oracle@rhel.oracle.com (TNS V1-V3)
27 3069 oracle@rhel.oracle.com (q004)
28 3071 oracle@rhel.oracle.com (q005)
22 rows selected.
6，[oracle@rhel ~]$ ps -ef | grep ora_
oracle 2865 1 0 09:31 ? 00:00:00 ora_pmon_orcl
oracle 2867 1 0 09:31 ? 00:00:00 ora_psp0_orcl
oracle 2869 1 0 09:31 ? 00:00:00 ora_mman_orcl
oracle 2884 1 0 09:31 ? 00:00:01 ora_dbw0_orcl
oracle 2886 1 0 09:31 ? 00:00:01 ora_lgwr_orcl
oracle 2888 1 0 09:31 ? 00:00:03 ora_ckpt_orcl
oracle 2890 1 0 09:31 ? 00:00:02 ora_smon_orcl
oracle 2892 1 0 09:31 ? 00:00:00 ora_reco_orcl
oracle 2894 1 0 09:31 ? 00:00:00 ora_cjq0_orcl
oracle 2896 1 0 09:31 ? 00:00:02 ora_mmon_orcl
oracle 2898 1 0 09:31 ? 00:00:00 ora_mmnl_orcl
oracle 2900 1 0 09:31 ? 00:00:00 ora_d000_orcl
oracle 2902 1 0 09:31 ? 00:00:00 ora_s000_orcl
oracle 2907 1 0 09:31 ? 00:00:00 ora_arc0_orcl
oracle 2909 1 0 09:31 ? 00:00:00 ora_arc1_orcl
oracle 2913 1 0 09:31 ? 00:00:00 ora_qmnc_orcl
oracle 3069 1 0 09:31 ? 00:00:00 ora_q004_orcl
oracle 3071 1 0 09:31 ? 00:00:00 ora_q005_orcl
oracle 16122 1 0 16:58 ? 00:00:00 ora_j000_orcl
oracle 16173 16150 0 16:59 pts/5 00:00:00 grep ora_
[oracle@rhel ~]$
7，在操作系统上一个进程的PID号，对应地，可以在数据库v$process的SPID得到，也就是说v$process视图实际上是从操作系统到数据库的一个借口，可以通过v$process视图把操作系统和数据库联系起来。
8，如果想设置为手工管理，只需要简单的将SGA_TARGET参数设置为0，
9，SQL> select name,value
2 from v$parameter
3 where name in
4 ('large_pool_size','java_pool_size','shared_pool_size','stream_pool_size','db_cache_size');
SQL> /
NAME VALUE
---------------------------------------- ------------------------------
shared_pool_size 0
large_pool_size 0
java_pool_size 0
db_cache_size 0
10，SQL> select x.ksppinm NAME,y.ksppstvl VALUE,x.ksppdesc describ
FROM SYS.x$ksppi x,SYS.x$ksppcv y
WHERE x.inst_id=USERENV('Instance')
AND y.inst_id=USERENV('Instance')
AND x.indx=y.indx
AND x.ksppinm like '%pool_size%'
/
NAME VALUE DESCRIB
---------------------------------------- -------------------------------------------------- ----------------------------------------
_NUMA_pool_size Not specified aggregate size in bytes of NUMA pool
__shared_pool_size 62914560 Actual size in bytes of shared pool
shared_pool_size 0 size in bytes of shared pool
__large_pool_size 4194304 Actual size in bytes of large pool
large_pool_size 0 size in bytes of large pool
__java_pool_size 4194304 Actual size in bytes of java pool
java_pool_size 0 size in bytes of java pool
__streams_pool_size 0 Actual size in bytes of streams pool
streams_pool_size 0 size in bytes of the streams pool
_io_shared_pool_size 4194304 Size of I/O buffer pool from SGA
_backup_io_pool_size 1048576 memory to reserve from the large pool
NAME VALUE DESCRIB
---------------------------------------- -------------------------------------------------- ----------------------------------------
global_context_pool_size Global Application Context Pool Size in
Bytes
olap_page_pool_size 0 size of the olap page pool in bytes
13 rows selected.
11，这些由两个下划线开头的参数决定了当前SGA的分配，也是动态内存管理调整的参数，这些参数的更改会被记录到spfile文件当中，在下一次数据库启动的时候仍然有效。
[oracle@rhel dbs]$ more initorcl.ora
orcl.__db_cache_size=201326592
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=62914560
12，SQL> select COMPONENT ,CURRENT_SIZE ,MIN_SIZE,LAST_OPER_TYPE, LAST_OPER_MODE,to_char(LAST_OPER_TIME ,'yyyy-mm-dd hh24:mi:ss')LOT
from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE LAST_OPER_TYP LAST_OPER LOT
---------------------------------------------------------------- ------------ ---------- ------------- --------- -------------------
shared pool 62914560 58720256 GROW IMMEDIATE 2014-04-17 19:36:35
large pool 4194304 4194304 STATIC
java pool 4194304 4194304 STATIC
streams pool 0 0 STATIC
DEFAULT buffer cache 201326592 201326592 SHRINK IMMEDIATE 2014-04-17 19:36:35
KEEP buffer cache 4194304 4194304 STATIC
RECYCLE buffer cache 4194304 4194304 STATIC
DEFAULT 2K buffer cache 0 0 STATIC
DEFAULT 4K buffer cache 0 0 STATIC
DEFAULT 8K buffer cache 0 0 STATIC
DEFAULT 16K buffer cache 0 0 STATIC
COMPONENT CURRENT_SIZE MIN_SIZE LAST_OPER_TYP LAST_OPER LOT
---------------------------------------------------------------- ------------ ---------- ------------- --------- -------------------
DEFAULT 32K buffer cache 0 0 STATIC
ASM Buffer Cache 0 0 STATIC
13 rows selected.
SQL>
oracle数据库用户都可以共享SGA中的数据，通常为了优化性能，我们总是期望在物理内存允许的情况下，设置更高的SGA区，以减少物理I/O（SGA中的数据缓冲区的增大可以有效减少物理读）。oracle对数据的处理过程中，代价最昂贵的就是物理I/O操作了。
二，BufferCache
1，在oracle 9i之前，Buffer cache的设置主要由两个参数决定：db_block_buffers和db_block_size。
2，db_block_buffers设置了分配给Buffer Cache的缓冲区数量，这个数量乘以db_block_size得出的才是Buffer Cache的大小。
3，从oracle 9i之后，oracle引入了一个新的参数db_cache_size。该参数用来定义主Block_size（db_block_size定义的大小）的Default缓冲池的大小。
4，SQL> select name ,value from v$parameter where name in ('db_block_buffers','db_block_size');
NAME
---------------------------------VALUE---------------------------------
db_block_buffers 0
db_block_size 8192
SQL>
5，db_cache_size最小值为一个粒度oracle 9i引入的一个新概念，连续虚拟内存分配的单位，其大小决定于估计的SGA的总大小（SGA总大小有SGA_MAX_SIZE参数得到）。如果估计的SGA的大小<128MB，则值为4MB；否则值为16MB；
这个Granule大小受到一个内部隐含参数_ksmg_granule_size 的控制。
6，内存空间有限的，oracle管理Buffer Cache使用的LRU算法，但是这又带来了另外一个问题，很多批处理操作（如全表扫描等）可能会导致Buffer Cache的刷新，将经常使用的数据"挤出"Buffer Cache，在不同的版本中，oracle不停地改进LRU算法，以避免这类操作的过度影响。oracle提供了Buffer Cache的多缓冲池技术从另外一个方面来解决这个问题。所谓的多缓冲技术是指，根据不同的数据的不同的访问方式，将Buffer Cache分为Default、Keep和Recycle池三个部分，对于常用的使用的数据，可以在建表时就指定存放在Keep池中;对于一次性读取使用 的数据，可以将其存放在Recycle池中；Keep池中的数据倾向于一直保存，Recycle池中的数据倾向于即时老化，而Default池则存放未指定存储池的数据，按照LRU算法管理。
7，默认情况下，所有表都使用DEFAULT池，它的大小就是数据缓冲区BufferCache的大小，由初始化参数db_cache_size决定。如果在 创建数据表或修改数据表时，指定STORAGE（buffer_pool_size）或者STORAGE（buffer_pool_recycle）语 句，就设置了这张表使用keep或者recycle缓冲区。这两个缓冲区的大小分别有初始化参数db_keep_cache_size和 db_recycle_cache_size来决定。
三，粒度
1，SQL> @ GetParDescrb.sql
Enter value for par: _ksmg_granule_size
old 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%'
new 3: WHERE x.indx = y.indx AND x.ksppinm LIKE '%_ksmg_granule_size%'
NAME VALUE DESCRIB
------------------------------ -------------------- ------------------------------------------------------------
_ksmg_granule_size 4194304 granule size in bytes
2， GetParDescrb.sql里面的内容：
SET linesize 120
COL name for a30
COL value for a20
COL describ for a60
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.indx = y.indx AND x.ksppinm LIKE '%&par%';
四，查看参数
1，SQL> show parameter cache_size;
NAME TYPE VALUE
------------------------------------ ----------- -----------------
db_keep_cache_size big integer 4M
db_recycle_cache_size big integer 4M
2， select id,name,block_size,current_size,target_size from v$buffer_pool;
ID NAME BLOCK_SIZE CURRENT_SIZE TARGET_SIZE
---------- -------------------- ---------- ------------ -----------
1 KEEP 8192 4 4
2 RECYCLE 8192 4 4
3 DEFAULT 8192 196 196
SQL>
3， SQL> select * from v$sga;,
NAME VALUE
-------------------- ----------
Fixed Size 1219184
Variable Size 96470416
Database Buffers 213909504
Redo Buffers 2973696
4，SQL> show sga;
5，SQL> show parameter sga_max_size;
6，Fixed Size 部分是SGA中的固定部分，包含数据库和实例的状态等通用信息，后台进程需要访问这部分信息，不存储用户的数据，通常只需要很小部分内存。
7，SQL> select * from v$sgastat;
8，SQL> select * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1219184 No
Redo Buffers 2973696 No
Buffer Cache Size 213909504 Yes
Shared Pool Size 58720256 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 314572800 No
Startup overhead in Shared Pool 37748736 No
Free SGA Memory Available 29360128
11 rows selected.
SQL>
五，Shmmax
1，shmmax内核参数定义的是系统允许的单个共享内存段的最大值，如果该参数小于oracle SGA设置，那么SGA仍然可以创建成功，但是会被分配多个共享内存段。通常推荐通过调整shmmax设置，将SGA限制在一个共享内存段中。
2，[root@rhel ~]# more /proc/sys/kernel/shmmax
2147483648
3，[root@rhel ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
4，[root@rhel ~]# uname -r
2.6.18-164.el5,
5，可以通过ipcs命令查看此设置下共享内存的分配,
[root@rhel ~]# ipcs -sa
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x4032a294 32768 oracle 640 318767104 20
0x00000000 98305 root 600 393216 2 dest
0x00000000 131074 root 600 393216 2 dest
0x00000000 163843 root 600 393216 2 dest
0x00000000 196612 root 600 393216 2 dest
0x00000000 229381 root 600 393216 2 dest
0x00000000 262150 root 600 393216 2 dest
0x00000000 294919 root 600 393216 2 dest
0x00000000 327688 root 600 393216 2 dest
0x00000000 360457 root 600 393216 2 dest
0x00000000 393226 root 600 393216 2 dest
------ Semaphore Arrays --------
key semid owner perms nsems
0xddaf4f30 98304 oracle 640 154
------ Message Queues --------
key msqid owner perms used-bytes messages
6，[root@rhel ~]# ps -ef | grep dbw
oracle 2883 1 0 17:12 ? 00:00:00 ora_dbw0_orcl
root 5618 3276 0 19:36 pts/3 00:00:00 grep dbw
7，针对一个进程，使用pmap工具可以看到这个共享内存段的地址空间
8，[root@rhel ~]# pmap 2883
2883: ora_dbw0_orcl
00110000 268K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libocrb10.so
00153000 4K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libocrb10.so
00154000 344K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libocrutl10.so
001aa000 16K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libocrutl10.so
001ae000 4K rwx-- [ anon ]
001af000 88K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libdbcfg10.so
001c5000 8K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libdbcfg10.so
001c7000 36K rwx-- [ anon ]
001d0000 4K rwxs- /u01/app/oracle/10.2.0/db_1/dbs/hc_orcl.dat
001d1000 8K r-x-- /lib/libdl-2.5.so
001d3000 4K r-x-- /lib/libdl-2.5.so
001d4000 4K rwx-- /lib/libdl-2.5.so
001d5000 148K r-x-- /lib/libm-2.5.so
001fa000 4K r-x-- /lib/libm-2.5.so
001fb000 4K rwx-- /lib/libm-2.5.so
001fc000 76K r-x-- /lib/libpthread-2.5.so
0020f000 4K r-x-- /lib/libpthread-2.5.so
00210000 4K rwx-- /lib/libpthread-2.5.so
00211000 288K rwx-- [ anon ]
00263000 4K rwx-- [ anon ]
00264000 1276K r-x-- /lib/libc-2.5.so
003a3000 4K --x-- /lib/libc-2.5.so
003a4000 8K r-x-- /lib/libc-2.5.so
003a6000 4K rwx-- /lib/libc-2.5.so
003a7000 12K rwx-- [ anon ]
003aa000 36K r-x-- /lib/libnss_files-2.5.so
003b3000 4K r-x-- /lib/libnss_files-2.5.so
003b4000 4K rwx-- /lib/libnss_files-2.5.so
003dd000 32K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libclsra10.so
003e5000 4K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libclsra10.so
00475000 4K rwx-- [ anon ]
00520000 128K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libskgxp10.so
00540000 8K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libskgxp10.so
00578000 104K r-x-- /lib/ld-2.5.so
00592000 4K r-x-- /lib/ld-2.5.so
00593000 4K rwx-- /lib/ld-2.5.so
00594000 1904K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libnnz10.so
00770000 152K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libnnz10.so
00796000 1040K rwx-- [ anon ]
008b7000 4K rwx-- [ anon ]
008e0000 4K rwx-- [ anon ]
00900000 4K r-x-- /usr/lib/libaio.so.1.0.1
00901000 4K rwx-- /usr/lib/libaio.so.1.0.1
009b2000 76K r-x-- /lib/libnsl-2.5.so
009c5000 4K r-x-- /lib/libnsl-2.5.so
009c6000 4K rwx-- /lib/libnsl-2.5.so
009c7000 1564K rwx-- [ anon ]
00b81000 436K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libocr10.so
00bee000 4K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libocr10.so
00c65000 4K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libskgxn2.so
00c66000 4K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libskgxn2.so
00c71000 4K rwx-- [ anon ]
00c9e000 4K r-x-- [ anon ]
00d02000 140K rwx-- [ anon ]
00d65000 880K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libhasgen10.so
00e41000 20K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libhasgen10.so
00e46000 12K rwx-- [ anon ]
00e49000 7028K r-x-- /u01/app/oracle/10.2.0/db_1/lib/libjox10.so
01526000 260K rwx-- /u01/app/oracle/10.2.0/db_1/lib/libjox10.so
01567000 4K rwx-- [ anon ]
08048000 77032K r-x-- /u01/app/oracle/10.2.0/db_1/bin/oracle
0cb82000 324K rwx-- /u01/app/oracle/10.2.0/db_1/bin/oracle
0cbd3000 120K rwx-- [ anon ]
0d975000 264K rwx-- [ anon ]
20000000 311296K rwxs- [ shmid=0x8000 ]
b7ef6000 64K rwx-- /dev/zero
b7f06000 64K rwx-- /dev/zero
b7f16000 64K rwx-- /dev/zero
b7f26000 64K rwx-- /dev/zero
b7f36000 64K rwx-- /dev/zero
b7f46000 64K rwx-- /dev/zero
b7f56000 64K rwx-- /dev/zero
b7f66000 64K --x-- /dev/zero
b7f76000 40K rwx-- /dev/zero
b7f80000 64K rwx-- /dev/zero
b7f90000 64K rwx-- /dev/zero
b7fa0000 64K rwx-- /dev/zero
b7fb0000 128K rwx-- /dev/zero
b7fd0000 64K rwx-- /dev/zero
b7fe0000 64K rwx-- /dev/zero
b7ff0000 24K rwx-- /dev/zero
bfacd000 84K rwx-- [ stack ]
total 406660K
[root@rhel ~]#
8，为了避免多个共享内存段，可以修改shmmax内核参数，使SGA存在于一个内存共享段中。通过修改/proc/sys/kernel/shmmax参数可以达到这个目的。
9， [root@rhel ~]# more /proc/sys/kernel/shmmax
2147483648
10，[root@rhel ~]# echo 2147490000 >/proc/sys/kernel/shmmax
[root@rhel ~]#
注：重启数据库之后更改才生效。
11，有时候警告数据库异常的关闭，如果数据库异常关闭，后台进程未正常退出，共享内存段没有来得及释放导致，通过ipcs命令找到共享内存段id（shared memeory id），然后通过ipcrm强制释放内存段。
在oracle9i中，可以设置参数SGA_MAX_SIZE，该参数可以控制各缓冲池使用内存的总和，本质上是在进程中预先分配一段虚拟内存地址备用而不分配物理地址，目的是防止和进程的私有地址段的冲突。
六，动态修改SGA参数时，存在的一些常见的限制
1，修改的内存大小必须是粒度（Granule）大小的整数倍，否则自动向上取整；
2，SGA总的大小不能超过SGA_MAX_SIZE;
3，SGA最低配置为3个粒度（Granule），一个粒度是用于固定SGA（包括重做缓冲区），一个粒度是用户缓冲区高速缓存，一个粒度用户共享池。
七，advice
1，SQL> select tname from tab where tname like '%ADVICE%';
TNAME
------------------------------
DBA_HIST_DB_CACHE_ADVICE
DBA_HIST_JAVA_POOL_ADVICE
DBA_HIST_MTTR_TARGET_ADVICE
DBA_HIST_PGA_TARGET_ADVICE
DBA_HIST_SGA_TARGET_ADVICE
DBA_HIST_SHARED_POOL_ADVICE
DBA_HIST_STREAMS_POOL_ADVICE
GV_$DB_CACHE_ADVICE
GV_$JAVA_POOL_ADVICE
GV_$MTTR_TARGET_ADVICE
GV_$PGATARGET_ADVICE_HISTOGR
2，DB_CACHE_ADVICE不同参数值的含义分别如下：OFF：关闭建议且不为建议分配内存。ON：开启建议且CPU和内存开销都会发生。READY：关闭建议但是仍保留为建议非配的内存。
3，在某些版本中，如果在参数为OFF状态时，尝试将其设置为ON，可能会出现ORA-4031错误，无法从共享池中分配内存；如果参数处于READY状态则可以将其设置为ON而不会发生错误，这是因为需要的内存已经分配。
SQL> show parameter db_cache_ad;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
SQL>
SQL> select id,name,block_size,size_for_estimate sfe ,size_factor sf,
2 estd_physical_read_factor eprf,estd_physical_reads epr
3 from v$db_cache_advice;
ID NAME BLOCK_SIZE SFE SF EPRF EPR
---------- -------------------- ---------- ---------- ---------- ---------- ----------
1 KEEP 8192 4 1 0
1 KEEP 8192 8 2 0
2 RECYCLE 8192 4 1 0
2 RECYCLE 8192 8 2 0
3 DEFAULT 8192 16 .0816 2.0812 18556
3 DEFAULT 8192 32 .1633 1.2688 11313
3 DEFAULT 8192 48 .2449 1.0517 9377
3 DEFAULT 8192 64 .3265 1 8916
3 DEFAULT 8192 80 .4082 1 8916
3 DEFAULT 8192 96 .4898 1 8916
3 DEFAULT 8192 112 .5714 1 8916
ID NAME BLOCK_SIZE SFE SF EPRF EPR
---------- -------------------- ---------- ---------- ---------- ---------- ----------
3 DEFAULT 8192 128 .6531 1 8916
3 DEFAULT 8192 144 .7347 1 8916
3 DEFAULT 8192 160 .8163 1 8916
3 DEFAULT 8192 176 .898 1 8916
3 DEFAULT 8192 192 .9796 1 8916
3 DEFAULT 8192 196 1 1 8916
最适合的值
3 DEFAULT 8192 208 1.0612 1 8916
3 DEFAULT 8192 224 1.1429 1 8916
3 DEFAULT 8192 240 1.2245 1 8916
3 DEFAULT 8192 256 1.3061 1 8916
3 DEFAULT 8192 272 1.3878 1 8916
ID NAME BLOCK_SIZE SFE SF EPRF EPR
---------- -------------------- ---------- ---------- ---------- ---------- ----------
3 DEFAULT 8192 288 1.4694 1 8916
3 DEFAULT 8192 304 1.551 1 8916
3 DEFAULT 8192 320 1.6327 1 8916
25 rows selected.
SQL>
4，STATISTICS_LEVEL控制数据库收集的统计信息的级别，该参数有3个选项。BASIC：收集基本的统计信息。TYPICAL：收集大部分的统计信息，这是系统的缺省设置，用户始终应该将该参数设置为典型。ALL：收集全部的统计信息。
5，SQL> select STATISTICS_NAME ,SESSION_STATUS ,SYSTEM_STATUS,ACTIVATION_LEVEL , SESSION_SETTABLE from v$statistics_level;
STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------------------- -------- -------- ------- ---
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
Timed Statistics ENABLED ENABLED TYPICAL YES
Timed OS Statistics DISABLED DISABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Modification Monitoring ENABLED ENABLED TYPICAL NO
Longops Statistics ENABLED ENABLED TYPICAL NO
Bind Data Capture ENABLED ENABLED TYPICAL NO
STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------------------- -------- -------- ------- ---
Ultrafast Latch Statistics ENABLED ENABLED TYPICAL NO
Threshold-based Alerts ENABLED ENABLED TYPICAL NO
Global Cache Statistics ENABLED ENABLED TYPICAL NO
Active Session History ENABLED ENABLED TYPICAL NO
Undo Advisor, Alerts and Fast Ramp up ENABLED ENABLED TYPICAL NO
16 rows selected.
6，可以看到在TYPICAL设置下，除Timed OS Statistics和Plan Execution Statistics信息不收集外，其他信息都被收集。其中，Buffer Cache Advice 受db_cache_size参数独立控制，Timed Statistics受timed_statistics参数独控制。
其他统计信息的收集都受到STATISTICS_lEVEL参数的控制。当修改为STATISTICS_LEVEL为Basic时，可以看到，除Buffer Cache Advice和Timed Statistics外，其他的收集 信息都被禁止。
7，在10g下，如果SGA自动调整，不让修改为basic；
SQL> alter system set statistics_level=basic;
alter system set statistics_level=basic
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled
8，可以通过查询v$SHARED_POOL_ADVICE视图获取关于shared_pool的建议信息：
SQL> select SHARED_POOL_SIZE_FOR_ESTIMATE SPZFE, SHARED_POOL_SIZE_FACTOR SPSF,
2 ESTD_LC_MEMORY_OBJECTS ELMO, ESTD_LC_TIME_SAVED ELTS,
3 ESTD_LC_LOAD_TIME_FACTOR ELTSF,ESTD_LC_MEMORY_OBJECT_HITS ELMOH
4 from v$shared_pool_advice;
SPZFE SPSF ELMO ELTS ELTSF ELMOH
---------- ---------- ---------- ---------- ---------- ----------
48 .8571 1039 121 1.1333 23641
56 1 1996 123 1 24514
最适合的值
64 1.1429 2907 123 1 24739
72 1.2857 4039 123 1 24778
80 1.4286 4490 123 1 24778
88 1.5714 4490 123 1 24778
96 1.7143 4490 123 1 24778
104 1.8571 4490 123 1 24778
112 2 4490 123 1 24778
9 rows selected.
9，找到这个是最好的shared_pool_size；alter system set shared_pool_size=56M; 有可能修改参时，session处于等待状态，等待事件为Background parameter adjustment。（这个也是说的oracle9i之前的版本）
八，查询等待事件
1，SQL> select sid,seq#,event,SECONDS_IN_WAIT,state
2 from v$session_wait where sid= 167;
SID SEQ# EVENT SECONDS_IN_WAIT STATE
---------- ---------- ---------------------------------------------------------------- --------------- -------------------
167 155 rdbms ipc message 48 WAITING
2，SQL> select * from v$lock where sid=167;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
32BB9BF8 32BB9C0C 167 PW 1 0 3 0 7364 0
32BB9D0C 32BB9D20 167 MR 1 0 4 0 7367 0
32BB9D68 32BB9D7C 167 MR 2 0 4 0 7367 0
32BB9DC4 32BB9DD8 167 MR 3 0 4 0 7367 0
32BB9E20 32BB9E34 167 MR 4 0 4 0 7367 0
32BB9E7C 32BB9E90 167 MR 5 0 4 0 7367 0
32BB9F34 32BB9F48 167 MR 201 0 4 0 7367 0
7 rows selected
SQL>
3，SQL> select * from v$lock ;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
32BB99D0 32BB99E4 165 XR 4 0 1 0 7162 0
32BB9A2C 32BB9A40 165 CF 0 0 2 0 7162 0
32BB9AE4 32BB9AF8 165 RS 25 1 2 0 7156 0
32BB9B9C 32BB9BB0 166 RT 1 0 6 0 7156 0
32BB9BF8 32BB9C0C 167 PW 1 0 3 0 7150 0
32BB9D0C 32BB9D20 167 MR 1 0 4 0 7153 0
32BB9D68 32BB9D7C 167 MR 2 0 4 0 7153 0
32BB9DC4 32BB9DD8 167 MR 3 0 4 0 7153 0
32BB9E20 32BB9E34 167 MR 4 0 4 0 7153 0
32BB9E7C 32BB9E90 167 MR 5 0 4 0 7153 0
32BB9ED8 32BB9EEC 164 TS 3 1 3 0 7151 0
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
32BB9F34 32BB9F48 167 MR 201 0 4 0 7153 0
12 rows selected.
九，SGA自动管理
1，数据库白天需要处理大量的OLTP任务，这些任务需要大量的Buffer Cache内存，而在夜间系统可能需要运行大量的并行批处理任务，这些任务又需要大量的Large Pool内存，为了这样的一个系统在有限的资源下高效率的运行，在oracle 10g的自动共享内存管理（Automatic Shared Memory Management，ASMM）下，这些动作都不要人工介入，当运行OLTP任务时，BufferCache会获取大部分的内存以达到良好的性能。如果系统需要运行DSS批处理任务时，内存自动转移给Large Pool，以便并行查询等可以获取更多的内存，更快的执行。
2，并非所有SGA组件都可以自动调整，可以自动分配的内存包括：Buffer Cache；Shared Pool；Java Pool；Large Pool;
3，启用自动共享内存管理，可以估算一个SGA的总大小，然后设置SGA_TARGET参数为非0值，Oracle将启动自动共享内存管理。自动内存管理需要STATISTICS_LEVEL参数设置为TYPICAL或者ALL。
4，Oracle服务器根据系统运行的情况自动调整这些内存的大小，并记录在spfile中，进程重新启动时，不会丢失之前的调整结果。自动的共享内存管理引入了一个新的后台进程MMAN（MemoryManager）。该进程用以动态调整内存组件。动态调整的依据来自系统不断收集的内存建议。
5，SQL> select pid,spid,program from v$process;
PID SPID PROGRAM
---------- ------------ ------------------------------------------------
1 PSEUDO
2 2865 oracle@rhel.oracle.com (PMON)
3 2867 oracle@rhel.oracle.com (PSP0)
4 2869 oracle@rhel.oracle.com (MMAN)
5 2884 oracle@rhel.oracle.com (DBW0)
6 2886 oracle@rhel.oracle.com (LGWR)
7 2888 oracle@rhel.oracle.com (CKPT)
8 2890 oracle@rhel.oracle.com (SMON)
9 2892 oracle@rhel.oracle.com (RECO)
10 2894 oracle@rhel.oracle.com (CJQ0)
11 2896 oracle@rhel.oracle.com (MMON)
PID SPID PROGRAM
---------- ------------ ------------------------------------------------
12 2898 oracle@rhel.oracle.com (MMNL)
13 2900 oracle@rhel.oracle.com (D000)
14 2902 oracle@rhel.oracle.com (S000)
15 16116 oracle@rhel.oracle.com (J000)
16 2907 oracle@rhel.oracle.com (ARC0)
17 2909 oracle@rhel.oracle.com (ARC1)
18 15108 oracle@rhel.oracle.com (TNS V1-V3)
19 2913 oracle@rhel.oracle.com (QMNC)
21 3128 oracle@rhel.oracle.com (TNS V1-V3)
27 3069 oracle@rhel.oracle.com (q004)
28 3071 oracle@rhel.oracle.com (q005)
22 rows selected.
6，[oracle@rhel ~]$ ps -ef | grep ora_
oracle 2865 1 0 09:31 ? 00:00:00 ora_pmon_orcl
oracle 2867 1 0 09:31 ? 00:00:00 ora_psp0_orcl
oracle 2869 1 0 09:31 ? 00:00:00 ora_mman_orcl
oracle 2884 1 0 09:31 ? 00:00:01 ora_dbw0_orcl
oracle 2886 1 0 09:31 ? 00:00:01 ora_lgwr_orcl
oracle 2888 1 0 09:31 ? 00:00:03 ora_ckpt_orcl
oracle 2890 1 0 09:31 ? 00:00:02 ora_smon_orcl
oracle 2892 1 0 09:31 ? 00:00:00 ora_reco_orcl
oracle 2894 1 0 09:31 ? 00:00:00 ora_cjq0_orcl
oracle 2896 1 0 09:31 ? 00:00:02 ora_mmon_orcl
oracle 2898 1 0 09:31 ? 00:00:00 ora_mmnl_orcl
oracle 2900 1 0 09:31 ? 00:00:00 ora_d000_orcl
oracle 2902 1 0 09:31 ? 00:00:00 ora_s000_orcl
oracle 2907 1 0 09:31 ? 00:00:00 ora_arc0_orcl
oracle 2909 1 0 09:31 ? 00:00:00 ora_arc1_orcl
oracle 2913 1 0 09:31 ? 00:00:00 ora_qmnc_orcl
oracle 3069 1 0 09:31 ? 00:00:00 ora_q004_orcl
oracle 3071 1 0 09:31 ? 00:00:00 ora_q005_orcl
oracle 16122 1 0 16:58 ? 00:00:00 ora_j000_orcl
oracle 16173 16150 0 16:59 pts/5 00:00:00 grep ora_
[oracle@rhel ~]$
7，在操作系统上一个进程的PID号，对应地，可以在数据库v$process的SPID得到，也就是说v$process视图实际上是从操作系统到数据库的一个借口，可以通过v$process视图把操作系统和数据库联系起来。
8，如果想设置为手工管理，只需要简单的将SGA_TARGET参数设置为0，
9，SQL> select name,value
2 from v$parameter
3 where name in
4 ('large_pool_size','java_pool_size','shared_pool_size','stream_pool_size','db_cache_size');
SQL> /
NAME VALUE
---------------------------------------- ------------------------------
shared_pool_size 0
large_pool_size 0
java_pool_size 0
db_cache_size 0
10，SQL> select x.ksppinm NAME,y.ksppstvl VALUE,x.ksppdesc describ
FROM SYS.x$ksppi x,SYS.x$ksppcv y
WHERE x.inst_id=USERENV('Instance')
AND y.inst_id=USERENV('Instance')
AND x.indx=y.indx
AND x.ksppinm like '%pool_size%'
/
NAME VALUE DESCRIB
---------------------------------------- -------------------------------------------------- ----------------------------------------
_NUMA_pool_size Not specified aggregate size in bytes of NUMA pool
__shared_pool_size 62914560 Actual size in bytes of shared pool
shared_pool_size 0 size in bytes of shared pool
__large_pool_size 4194304 Actual size in bytes of large pool
large_pool_size 0 size in bytes of large pool
__java_pool_size 4194304 Actual size in bytes of java pool
java_pool_size 0 size in bytes of java pool
__streams_pool_size 0 Actual size in bytes of streams pool
streams_pool_size 0 size in bytes of the streams pool
_io_shared_pool_size 4194304 Size of I/O buffer pool from SGA
_backup_io_pool_size 1048576 memory to reserve from the large pool
NAME VALUE DESCRIB
---------------------------------------- -------------------------------------------------- ----------------------------------------
global_context_pool_size Global Application Context Pool Size in
Bytes
olap_page_pool_size 0 size of the olap page pool in bytes
13 rows selected.
11，这些由两个下划线开头的参数决定了当前SGA的分配，也是动态内存管理调整的参数，这些参数的更改会被记录到spfile文件当中，在下一次数据库启动的时候仍然有效。
[oracle@rhel dbs]$ more initorcl.ora
orcl.__db_cache_size=201326592
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=62914560
12，SQL> select COMPONENT ,CURRENT_SIZE ,MIN_SIZE,LAST_OPER_TYPE, LAST_OPER_MODE,to_char(LAST_OPER_TIME ,'yyyy-mm-dd hh24:mi:ss')LOT
from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE LAST_OPER_TYP LAST_OPER LOT
---------------------------------------------------------------- ------------ ---------- ------------- --------- -------------------
shared pool 62914560 58720256 GROW IMMEDIATE 2014-04-17 19:36:35
large pool 4194304 4194304 STATIC
java pool 4194304 4194304 STATIC
streams pool 0 0 STATIC
DEFAULT buffer cache 201326592 201326592 SHRINK IMMEDIATE 2014-04-17 19:36:35
KEEP buffer cache 4194304 4194304 STATIC
RECYCLE buffer cache 4194304 4194304 STATIC
DEFAULT 2K buffer cache 0 0 STATIC
DEFAULT 4K buffer cache 0 0 STATIC
DEFAULT 8K buffer cache 0 0 STATIC
DEFAULT 16K buffer cache 0 0 STATIC
COMPONENT CURRENT_SIZE MIN_SIZE LAST_OPER_TYP LAST_OPER LOT
---------------------------------------------------------------- ------------ ---------- ------------- --------- -------------------
DEFAULT 32K buffer cache 0 0 STATIC
ASM Buffer Cache 0 0 STATIC
13 rows selected.
SQL>
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29611940/viewspace-1144400/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29611940/viewspace-1144400/
扫一扫
596
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
