Hello DBA

今天发现一个奇怪的现象，一个数据库的shared_pool_size设置为50m，但是启动后Variable Size非常大

Total System Global Area  253199856 bytes
Fixed Size                   742896 bytes
Variable Size             100663296 bytes
Murder by Decree movies

Database Buffers          150994944 bytes
Redo Buffers                 798720 bytes

查询v$sgastat
POOL        NAME                            BYTES
----------- -------------------------- ----------
            fixed_sga                      743856
            buffer_cache                536870912
            log_buffer                     787456
shared pool branch                        1180120
shared pool enqueue                       5673320
shared pool DML lock                      2822816
shared pool KGK heap                        64600
shared pool SEQ S.O.                       475920
shared pool db_files                      1060600
shared pool messages                       624000
shared pool sessions                      8936720
shared pool sql area                        44144
shared pool 1M buffer                     1056768
shared pool processes                     3912000
shared pool db_handles                    3480000
shared pool constraints                    824960
shared pool free memory                 220200968
shared pool transaction                   5957952
shared pool enqueue_hash                   318960
shared pool FileOpenBlock                16607024
shared pool library cache                 2200360
shared pool miscellaneous                11748488
shared pool PLS non-lib hp                   1304
shared pool channel handle                 780672
shared pool joxs heap init                   4240
shared pool Checkpoint queue             10242560
shared pool dictionary cache              3229952
shared pool qmps connections              1401320
shared pool enqueue resources             2154232
shared pool ksm_file2sga region            370496
shared pool ktlbk state objects           1948360
shared pool KSXR receive buffers          1034000
shared pool character set object           274528
shared pool FileIdentificatonBlock        2247328
shared pool message pool freequeue         771984
shared pool KSXR pending messages que      853952
shared pool Temporary Tables State Ob      775488
shared pool UNDO INFO SEGMENTED ARRAY      649856
shared pool event statistics per sess    37386160
shared pool fixed allocation callback         664
shared pool replication session stats     1004720
large pool  free memory                  33554432

Flatliners divx
发现Oracle会根据参数的设置，当你的shared_pool设置的过小时，会自动预留出一定的空间，我原来设置的processes为3000，所以Oracle根据需要预留了200多M的空间，当我把processes参数降为200，发现shared pool的大小也降下来了。根据v$sgastat，以下几个参数可能影响shared pool大小：

db_files
dml_locks
enqueue_resources
open_cursors
open_links
open_links_per_instance
processes
session_cached_cursors
session_max_open_files
sessions
transactions

但是到了10g，你会发现如果shared_pool_size设置的过小，Oracle将报告shared pool过小，不能启动。但是9i则不然，你甚至可以把shared_pool_size设置为1M，数据库也可以正常启动，实际上Oracle已经为shared pool预留了足够的空间。

–EOF–