今天发现一个奇怪的现象,一个数据库的shared_pool_size设置为50m,但是启动后Variable Size非常大
Total System Global Area 253199856 bytes
Fixed Size 742896 bytes
Variable Size 100663296 bytes
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–
Latest Comments
RSS