Oracle 10g的内存调整报告

最新推荐文章于 2023-04-21 20:59:01 发布

cuiyinyao6929

最新推荐文章于 2023-04-21 20:59:01 发布

阅读量196

点赞数

文章标签：数据库数据结构与算法人工智能

本文详细介绍了Oracle10g数据库中SGA与PGA内存的自动管理与手动调整策略，包括如何根据Oracle顾问建议调整sharedpool与buffercache大小，以及如何设定PGA目标值。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle 10g的内存调整报告

SGA--(初始化参数 sga_target)

|__shared pool(初始化参数 shared_pool_size) chunk链表结构构成Bucket

| |

| |__library cache

| |

| |__dictionary cache

| |

| |__reserved size (初始化参数 shared_pool_reserved_size)

|__ DEFAULT buffer cache(初始化参数 db_cache_size) LRU链表结构

|__KEEP buffer cache (初始化参数 db_keep_cache_size)

|__RECYCLE buffer cache (初始化参数 db_recycle_cache_size)

|__ large pool (初始化参数 large_pool_size)

|__java pool (初始化参数 java_pool_size)

|__ streams pool (初始化参数 streams_pool_size)

|__log buffer (初始化参数 log_buffer)

PGA--(初始化参数 pga_aggregate_target)

|__ sort_area_size

|__ bitmap_merge_area_size

|__ hash_area_size

|__ open_cursors

|__ oracle 堆栈和TNS堆栈

PRE_PAGE_SGA

oracle实例启动时，会只载入各个内存区最小的大小。而其他SGA内存只作为虚拟内存分配，只有当进程touch到相应的页时，才会置换到物理内存中。但我们也许希望实例一启动后，所有SGA都分配到物理内存。这时就可以通过设置PRE_PAGE_SGA参数来达到目的了。

这个参数的默认值为FALSE，即不将全部SGA置入物理内存中。当设置为TRUE时，实例启动会将全部SGA置入物理内存中。它可以使实例启动达到它的最大性能状态，但是，启动时间也会更长（因为为了使所有SGA都置入物理内存中，oracle进程需要touch所有的SGA页）。

但是，要记住一点：PRE_PAGA_SGA只是在启动时将物理内存分配给SGA，但并不能保证系统在以后的运行过程不会将SGA中的某些页置换到虚拟内存中，也就是说，尽管设置了这个参数，还是可能出现Page In/Out。如果需要保障SGA不被换出，就需要由另外一个参数LOCK_SGA来控制了。

LOCK_SGA

上面提到，为了保证SGA都被锁定在物理内存中，而不必页入/页出，可以通过参数LOCK_SGA来控制。这个参数默认值为FALSE，当指定为TRUE时，可以将全部SGA都锁定在物理内存中。当然，有些系统不支持内存锁定，这个参数也就无效了。

use_indirect_data_buffers

这个参数使32位平台使用扩展缓冲缓存基址，以支持支持4GB多物理内存。设置此参数，可以使SGA突破在32位系统中的2G最大限制。64位平台中，这个参数被忽略。

SGA中，设置sga_target的值，并把statistics_level设置为typical或者all以启动ASMM，启动自动SGA管理后，Oracle可以自动为我们调整以下内存池的大小:

shared pool

buffer cache

large pool

java pool

streams pool

需要手动调整的参数

log buffer

db_nk_cache_size

db_keep_cache_size

db_recycle_cache_size

下面是初始化参数中定义的各个内存的值：

SQL> SELECT NAME,VALUE

2 FROM v$parameter

3 WHERE NAME IN('sga_max_size',

4 'db_cache_size',

5 'shared_pool_size',

6 'shared_pool_reserved_size',

7 'large_pool_size',

8 'java_pool_size',

9 'db_block_size',

10 'db_block_buffers',

11 'log_buffer',

12 'sort_area_size',

13 'sort_area_retained_size',

14 'hash_area_size',

15 'sessions',

16 'open_cursors',

17 'streams_pool_size',

18 'sga_target',

19 'pga_aggregate_target')

20 ORDER BY NAME;

NAME VALUE

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

db_block_buffers 0

db_block_size 32768

db_cache_size 0

hash_area_size 131072

java_pool_size 0

large_pool_size 0

log_buffer 14238720

open_cursors 500

pga_aggregate_target 1995440128

sessions 445

sga_max_size 4294967296

sga_target 4294967296

shared_pool_reserved_size 38587596

shared_pool_size 0

sort_area_retained_size 0

sort_area_size 65536

streams_pool_size 0

17 rows selected

通过以上语句可以看到SGA的大小是4294967296/1024/1024=4096MB 这4096MB是如何分配的呢，通过以下语句可以看到:

SQL> select t.COMPONENT,t.CURRENT_SIZE/1024/1024 "CURRENT_SIZE (MB)"from v$sga_dynamic_components t;

COMPONENT CURRENT_SIZE (MB)

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

shared pool 800

large pool 16

java pool 16

streams pool 0

DEFAULT buffer cache 3248

KEEP buffer cache 0

RECYCLE buffer cache 0

DEFAULT 2K buffer cache 0

DEFAULT 4K buffer cache 0

DEFAULT 8K buffer cache 0

DEFAULT 16K buffer cache 0

DEFAULT 32K buffer cache 0

ASM Buffer Cache 0

13 rows selected

SGA实际大小 = DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + STREAMS_POOL_SIZE（10g中的新内存池） + LOG_BUFFERS+11K(Redo Log Buffer的保护页) + 1MB + 16M(SGA内部内存消耗，适合于9i及之前版本)

4096MB=3248+0+0+0+800+16+16+0+((14238720/1024)+11)/1024+1=4094.6MB

一般来说，设置1GB以上的shared pool不会给性能带来明显的提高，相反，这将给Oracle管理shared pool以及监控shared pool的过程中带来较多的麻烦。我们可以在系统上线时，设置shared pool为SGA的10%，但是不要超过1GB，或者利用9i以后引入的顾问来帮助我们判断shared pool 的设置是否合理。只要将初始化参数statistics_level设置为typical或者all就启动对shared pool的建议，basic为关闭该功能。

SQL> select t.SHARED_POOL_SIZE_FOR_ESTIMATE "SP",

2 t.ESTD_LC_SIZE "EL",

3 t.ESTD_LC_MEMORY_OBJECTS "ELM",

4 t.ESTD_LC_TIME_SAVED "ELT",

5 t.ESTD_LC_TIME_SAVED_FACTOR "ELTS %",

6 t.ESTD_LC_MEMORY_OBJECT_HITS "ELMO"

7 from v$shared_pool_advice t

8 ;

SP EL ELM ELT ELTS % ELMO

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

320 88 5074 33933340 0.9864 1103895941

400 164 9091 34082404 0.9908 1103917266

480 243 12488 34212579 0.9946 1103936243

560 321 16246 34298181 0.997 1103950027

640 400 20958 34349935 0.9985 1103959377

720 479 25599 34381446 0.9995 1103965662

800 558 29274 34399956 1 1103969741

880 637 31830 34410211 1.0003 1103972287

960 716 36387 34415861 1.0005 1103973889

1040 771 38954 34419129 1.0006 1103974939

1120 810 41453 34421119 1.0006 1103975631

1200 889 45092 34422524 1.0007 1103976097

1280 969 48656 34423731 1.0007 1103976439

1360 1018 51779 34424784 1.0007 1103976714

1440 1057 54744 34425580 1.0007 1103976944

1520 1096 57024 34426021 1.0008 1103977142

1600 1175 63265 34426180 1.0008 1103977322

通过这两个语句可以看出，Oracle建议我们shared pool的值为800MB，而且实际分配的值也是800MB。

ESTD_LC_SIZE:估计库高速缓存的使用数量（以兆字节为单位）

ESTD_LC_MEMORY_OBJECTS: 估算共享池中库缓存的内存对象数

ESTD_LC_TIME_SAVED: 估算将可以节省的解析时间

ESTD_LC_TIME_SAVED_FACTOR: 估算的节省的解析时间与当前节省解析时间的比

ESTD_LC_MEMORY_OBJECT_HITS: 估算可以直接从共享池中命中库缓存的内存对象的命中次数

我们主要关注estd_lc_time_saved_factor列的值，当该列的值为1时表示再增加shared pool的大小对性能的提高没有意义，对于上面例子来说，当shared pool为800MB时达到最佳。

在如何设置buffer cache的大小上，从Oracle 9i 开始通过初始化参数db_cache_advice,从而启动buffer cache顾问，Oracle会监控default类型，keep类型和recycle类型的buffer cache的使用，以及其他5种不同数据库尺寸（2，4，8，16或32KB）的buffer cache的使用，Oracle会根据当前所监控到的物理读的速率，从而估算出在不同大小尺寸的buffer cache下，所产生的可能的物理读的数量，Oracle将这些信息放入v$db_cache_advice中，每种类型的buffer cache 都会有相应的若干条记录来表示所建议的buffer cache的大小，下面是对于默认类型的，默认数据块尺寸的buffer cache的建议大小应该是多少。

SQL> SELECT size_for_estimate "target M", buffers_for_estimate, estd_physical_read_factor "physical_read %", estd_physical_reads

2 FROM V$DB_CACHE_ADVICE

3 WHERE name = 'DEFAULT'

4 AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')

5 AND advice_status = 'ON';

target M BUFFERS_FOR_ESTIMATE physical_read % ESTD_PHYSICAL_READS

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

320 10040 22.7562 146498585

640 20080 13.3366 85857536

960 30120 5.9642 38395958

1280 40160 2.8749 18507961

1600 50200 2.0151 12972443

1920 60240 1.6118 10376066

2240 70280 1.3775 8867754

2560 80320 1.2042 7752114

2880 90360 1.0749 6920255

3200 100400 1.0058 6474792

3248 101906 1 6437754

3520 110440 0.9674 6227738

3840 120480 0.9445 6080193

4160 130520 0.931 5993347

4480 140560 0.9196 5920463

4800 150600 0.9059 5832272

5120 160640 0.8762 5640700

5440 170680 0.8106 5218403

5760 180720 0.683 4397109

6080 190760 0.4802 3091367

target M BUFFERS_FOR_ESTIMATE physical_read % ESTD_PHYSICAL_READS

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

6400 200800 0.2046 1317289

21 rows selected

size_for_estimate : 预测buffer cache的尺寸

buffers_for_estimate: 预测buffer Cache大小（缓冲块数）

estd_physical_read_factor: 这一缓冲大小时，物理读因子，它是如果缓冲大小为SIZE_FOR_ESTIMATE时，建议器预测物理读数与当前实际物理读数的比率值。如果当前物理读数为0，这个值为空。

estd_physical_reads: 如果缓冲大小为SIZE_FOR_ESTIMATE时，建议器预测物理读数。

这里的字段estd_physical_read_factor在足够内存的前提下，这个比值应该是越低越好，从上面的输出可以看出，当前的buffer cache为3248MB，如果为320M，则估计产生的物理读会是当前buffer cache尺寸下的22.7562倍，也就是增加了2275.62%左右的物理读（22.7562-1），而如果增加buffer cache，将其设置为6400MB，会使得物理读减少79.54%（1-0.2046）。

综上所述，如果在内存许可的情况下建议将sga_target的值调整为7250MB-8000MB之间。

PGA中，设置workarea_size_policy参数的值为auto时，启用PGA自动管理，pga设置多大合适呢，如果数据库为OLTP应用的，则其应用一般都是小的短的进程，所需要的pga也相应较少，所以该值通常为总共分配给Oracle实例的20%，另外的80%给SGA，如果是OLAP的应用，建议各占50%。而如果数据库是混合类型的，一般会先分配给pga 40%的初始值，然后随着应用不断监控和调整PGA。

比如8GB的物理内存，按照Oracle的推荐，分配给Oracle实例的内存为物理内存的80%，对于OLAP应用来说，pga_aggregate_target的值大约就是1310MB（8192*80%*20%），而对于OLAP应用来说，则该值大约就是3276MB，而对于混合型数据库，则该值大约就是2621MB。

当然这只是对于一个新的数据库来说设置的初始值，这些值不一定准确，可以通过v$pga_target_advice这个视图来确定pga的大小。

SQL> select round(t.PGA_TARGET_FOR_ESTIMATE/1024/1024) "target M",

t.ESTD_PGA_CACHE_HIT_PERCENTAGE "Est Cache Hit %",

round(t.ESTD_EXTRA_BYTES_RW/1024/1024) "Est RW M",

t.ESTD_OVERALLOC_COUNT "alloc" from v$pga_target_advice t;

target M Est Cache Hit % Est RW M alloc

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

238 89 20169 11429

476 100 67 0

952 100 67 0

1427 100 67 0

1903 100 67 0

2284 100 0 0

2664 100 0 0

3045 100 0 0

3425 100 0 0

3806 100 0 0

5709 100 0 0

7612 100 0 0

11418 100 0 0

15224 100 0 0

14 rows selected

该输出告诉我们，按照系统目前的准转情况，随着PGA增加，estd_pga_cache_hit_percentage不断增加，同时estd_extra_bytes_rw(sql语句与磁盘上临时表空间交互读写的字节数)不断减小,由此可以知道，将PGA_aggregate_target设置为476M是最合理的。当然，如果可能的话设置为2284MB则更好。