Ò»¡¢ÄÚ´æ½á¹¹

SGA(System Global Area)£ºÓÉËùÓзþÎñ½ø³ÌºÍºǫ́½ø³Ì¹²Ïí£»
PGA(Program Global Area)£ºÓÉÿ¸ö·þÎñ½ø³Ì¡¢ºǫ́½ø³ÌרÓУ»Ã¿¸ö½ø³Ì¶¼ÓÐÒ»¸öPGA¡£
¶þ¡¢SGA
°üº¬ÊµÀýµÄÊý¾ÝºÍ¿ØÖÆÐÅÏ¢£¬°üº¬ÈçÏÂÄÚ´æ½á¹¹£º
1£©Database buffer cache£º»º´æÁË´Ó´ÅÅÌÉϼìË÷µÄÊý¾Ý¿é¡£
2£©Redo log buffer£º»º´æÁËдµ½´ÅÅÌ֮ǰµÄÖØ×öÐÅÏ¢¡£
3£©Shared pool£º»º´æÁ˸÷Óû§¼ä¿É¹²ÏíµÄ¸÷Öֽṹ¡£
4£©Large pool£ºÒ»¸ö¿ÉÑ¡µÄÇøÓò£¬ÓÃÀ´»º´æ´óµÄI/OÇëÇó£¬ÒÔÖ§³Ö²¢Ðвéѯ¡¢¹²Ïí·þÎñÆ÷ģʽÒÔ¼°Ä³Ð©±¸·Ý²Ù×÷¡£
5£©Java pool£º±£´æjavaÐéÄâ»úÖÐÌØ¶¨»á»°µÄÊý¾ÝÓëjava´úÂë¡£
6£©Streams pool£ºÓÉOracle streamsʹÓá£
7£©Keep buffer cache£º±£´æbuffer cacheÖд洢µÄÊý¾Ý£¬Ê¹Æä¾¡Ê±¼ä¿ÉÄܳ¤¡£
8£©Recycle buffer cache£º±£´æbuffer cacheÖм´½«¹ýÆÚµÄÊý¾Ý¡£
9£©nK block size buffer£ºÎªÓëÊý¾Ý¿âĬÈÏÊý¾Ý¿é´óС²»Í¬µÄÊý¾Ý¿éÌṩ»º´æ¡£ÓÃÀ´Ö§³Ö±í¿Õ¼ä´«Êä¡£
database buffer cache, shared pool, large pool, streams
poolÓëJava pool¸ù¾Ýµ±Ç°Êý¾Ý¿â״̬£¬×Ô¶¯µ÷Õû£»
keep buffer cache,recycle buffer cache,nK block size
buffer¿ÉÒÔÔÚ²»¹Ø±ÕʵÀýÇé¿öÏ£¬¶¯Ì¬Ð޸ġ£
Èý¡¢PGA
ÿ¸ö·þÎñ½ø³Ì˽ÓеÄÄÚ´æÇøÓò£¬°üº¬ÈçϽṹ£º
1£©Private SQL area£º°üº¬°ó¶¨ÐÅÏ¢¡¢ÔËÐÐʱµÄÄÚ´æ½á¹¹¡£Ã¿¸ö·¢³ösqlÓï¾äµÄ»á»°£¬¶¼ÓÐÒ»¸öprivate
SQL area£¨Ë½ÓÐSQLÇø£©
2£©Session memory£ºÎª±£´æ»á»°ÖеıäÁ¿ÒÔ¼°ÆäËûÓë»á»°Ïà¹ØµÄÐÅÏ¢£¬¶ø·ÖÅäµÄÄÚ´æÇø¡£
ËÄ¡¢SGA COMPONENT
(Ò»)¡¢Buffer Cache
1¡¢DB_CACHE_SIZE
ͨ¹ý²ÎÊýDB_CACHE_SIZE¿ÉÖ¸¶¨DB buffer cacheµÄ´óС
ALTER SYSTEM SET DB_CACHE_SIZE=20M
scope=both;
·þÎñ½ø³Ì´ÓÊý¾ÝÎļþ¶ÁÊý¾Ýµ½buffer cache£»DBWn´Óbuffer
cacheдÊý¾Ýµ½Êý¾ÝÎļþ¡£
buffer cacheµÄËÄÖÖ״̬£º
1£©pinned£ºµ±Ç°¿éÕýÔÚ¶Áµ½cache»òÕýдµ½´ÅÅÌ£¬ÆäËû»á»°µÈ´ý·ÃÎʸÿ顣
2£©clean£º
3£©free/unused£ºbufferÄÚΪ¿Õ£¬ÎªÊµÀý¸ÕÆô¶¯Ê±µÄ״̬¡£
4£©dirty£ºÔàÊý¾Ý£¬Êý¾Ý¿é±»Ð޸ģ¬ÐèÒª±»DBWnˢе½´ÅÅÌ£¬²ÅÄÜÖ´ÐйýÆÚ´¦Àí¡£
ͬһ¸öÊý¾Ý¿âÖУ¬Ö§³Ö¶àÖÖ´óСµÄÊý¾Ý¿é»º´æ¡£Í¨¹ýDB_nK_CACHE_SIZE²ÎÊýÖ¸¶¨£¬Èç
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE
±ê×¼¿é»º´æÇø´óСÓÉDB_CACHE_SIZEÖ¸¶¨¡£Èç±ê×¼¿éΪnK£¬Ôò²»ÄÜͨ¹ýDB_nK_CACHE_SIZEÀ´Ö¸¶¨±ê×¼¿é»º´æÇøµÄ´óС£¬Ó¦ÓÉDB_CACHE_SIZEÖ¸¶¨¡£
Àý£¬±ê×¼¿éΪ8K£¬ÔòÊý¾Ý¿â¿ÉÒÔÉèÖõĿ黺´æ´óСµÄ²ÎÊýÈçÏ£º
DB_CACHE_SIZE £¨Ö¸¶¨±ê×¼¿é£¨ÕâÀïΪ8K£©µÄ»º´æÇø£©
DB_2K_CACHE_SIZE £¨Ö¸¶¨¿é´óСΪ2KµÄ»º´æÇø£©
DB_4K_CACHE_SIZE £¨Ö¸¶¨¿é´óСΪ4KµÄ»º´æÇø£©
DB_16K_CACHE_SIZE £¨Ö¸¶¨¿é´óСΪ16KµÄ»º´æÇø£©
DB_32K_CACHE_SIZE £¨Ö¸¶¨¿é´óСΪ32KµÄ»º´æÇø£©
2¡¢¶àÖÖ»º³å³Ø£¨buffer pool£©
1£©Keep£ºÍ¨¹ýdb_keep_cache_size²ÎÊýÖ¸¶¨¡£
¸ÃbufferÄÚµÄÊý¾Ý¿ÉÄܱ»ÖØÓã¬ÒÔ½µµÍI/O²Ù×÷¡£¸Ã³ØµÄ´óСҪ´óÓÚÖ¸¶¨µ½¸Ã³ØµÄ¶ÎµÄ×ܺ͡£
¶ÁÈëµ½keep bufferµÄ¿é²»ÐèÒª¹ýÆÚ²Ù×÷¡£
2£©Recycle£ºÍ¨¹ýdb_recycle_cache_size²ÎÊýÖ¸¶¨¡£
¸Ã³ØÖеÄÊý¾Ý±»ÖØÓûú»á½ÏС£¬¸Ã³Ø´óСҪСÓÚ·ÖÅäµ½¸Ã³ØµÄ¶ÎµÄ×ܺ͡£¶ÁÈë¸Ã³ØµÄ¿éÐèÒª¾³£Ö´ÐйýÆÚ´¦Àí¡£
3£©Default£ºÏ൱ÓÚÒ»¸öûÓÐKeepÓëRecycle³ØµÄʵÀýµÄbuffer
cache£¬Í¨¹ýdb_cache_size²ÎÊýÖ¸¶¨¡£
3¡¢Îª¶ÔÏóÃ÷È·Ö¸¶¨buffer pool
buffer_pool×Ӿ䣬ÓÃÀ´Îª¶ÔÏóÖ¸¶¨Ä¬ÈϵÄbuffer pool£¬ÊÇstorage×Ó¾äµÄÒ»²¿·Ö¡£
¶ÔcreateÓëalter table¡¢cluster¡¢indexÓï¾äÓÐЧ¡£
Èç¹ûÏÖÓжÔÏóûÓÐÃ÷È·Ö¸¶¨buffer pool£¬ÔòĬÈ϶¼Ö¸¶¨Îªdefault
buffer pool£¬´óСΪDB_CACHE_SIZE²ÎÊýÉèÖõÄÖµ¡£
Óï·¨£º
a.CREATE INDEX cust_idx ON tt(id) STORAGE
(BUFFER_POOL KEEP);
b.ALTER TABLE oe.customers STORAGE
(BUFFER_POOL RECYCLE);
c.ALTER INDEX oe.cust_lname_ix STORAGE
(BUFFER_POOL KEEP);
(¶þ)¡¢Share Pool
1¡¢SHARE_POOL_SIZE
1£©Share Pool¿Éͨ¹ýSHARE_POOL_SIZE²ÎÊýÖ¸¶¨£º
SQL> alter system set shared_pool_size=20M
scope=both;
2£©Share Pool±£´æµÄÐÅÏ¢±»¶à¸ö»á»°¹²Ïí£¬ÀàÐͰüÀ¨£º
a.Library Cache
Library CacheÓÖ°üº¬¹²ÏíSQLÇøÓëPL/SQLÇø£º
a).¹²ÏíSQLÇø±£´æÁË·ÖÎöÓë±àÒë¹ýµÄSQLÓï¾ä¡£
b).PL/SQLÇø±£´æÁË·ÖÎöÓë±àÒë¹ýµÄPL/SQL¿é£¨¹ý³ÌºÍº¯Êý¡¢°ü¡¢´¥·¢Æ÷ÓëÄäÃûPL/SQL¿é£©¡£
b.Data Dictionary Cache
±£´æÁËÊý¾Ý×Öµä¶ÔÏóµÄ¶¨Òå¡£
c.UGA(User Global Area)
UGAÄÚ°üº¬Á˹²Ïí·þÎñÆ÷ģʽϵĻỰÐÅÏ¢¡£
¹²Ïí·þÎñÆ÷ģʽʱ£¬Èç¹ûlarge poolûÓÐÅäÖã¬ÔòUGA±£´æÔÚShare PoolÖС£

(Èý)¡¢Large Pool
1£©Large Pool´óСͨ¹ýLARGE_POOL_SIZE²ÎÊýÖ¸¶¨£º
SQL> alter system set large_pool_size=20m scope=both;
2£©×÷ÓÃ:
a.ΪI/O·þÎñ½ø³Ì·ÖÅäÄÚ´æ
b.Ϊ±¸·ÝÓë»Ö¸´²Ù×÷·ÖÅäÄÚ´æ
c.ΪOracle¹²Ïí·þÎñÆ÷ģʽÓë¶à¸öÊý¾Ý¿â¼äµÄÁª»úÊÂÎñ·ÖÅäÄÚ´æ¡£
ͨ¹ý´Ólarge poolÖÐΪ¹²Ïí·þÎñÆ÷ģʽ·ÖÅä»á»°Äڴ棬¿ÉÒÔ¼õÉÙshare poolÒòƵ·±Îª´ó¶ÔÏó·ÖÅäºÍ»ØÊÕÄÚ´æ¶ø²úÉúµÄË鯬¡£½«´óµÄ¶ÔÏó´Óshare
poolÖзÖÀë³öÀ´£¬¿ÉÒÔÌá¸ßshared poolµÄʹÓÃЧÂÊ£¬Ê¹Æä¿ÉÒÔΪеÄÇëÇóÌṩ·þÎñ»òÕ߸ù¾ÝÐèÒª±£ÁôÏÖÓеÄÊý¾Ý¡£

(ËÄ)¡¢Java Pool
1¡¢JAVA_POOL_SIZE
ͨ¹ýJAVA_POOL_SIZE²ÎÊýÖ¸¶¨java pool´óС¡£
±£´æÁËjvmÖÐÌØ¶¨»á»°µÄjava codeºÍÊý¾Ý¡£
2¡¢ÔÚ±àÒëÊý¾Ý¿âÖеÄjava´úÂëºÍʹÓÃÊý¾Ý¿âÖеÄjava×ÊÔ´¶ÔÏóʱ£¬¶¼»áÓõ½share pool¡£
javaµÄÀà¼ÓÔØ³ÌÐò¶Ôÿ¸ö¼ÓÔØµÄÀà»áʹÓôóÔ¼8KµÄ¿Õ¼ä¡£
ϵͳ¸ú×ÙÔËÐйý³ÌÖУ¬¶¯Ì¬¼ÓÔØµÄjavaÀ࣬Ҳ»áʹÓõ½share pool¡£
(Îå)¡¢Redo Log Buffer
1¡¢·þÎñ½ø³Ì´ÓÓû§¿Õ¼ä¿½±´Ã¿ÌõDML/DDLÓï¾äµÄredoÌõÄ¿µ½redo log bufferÖС£
2¡¢redo log bufferÊÇÒ»¸ö¿ÉÒÔÑ»·Ê¹ÓõÄbuffer£¬·þÎñ½ø³Ì¿½±´ÐµÄredo¸²¸Çµôredo
log bufferÖÐÒÑͨ¹ýLGWRдÈë´ÅÅÌ£¨online redo log£©µÄÌõÄ¿¡£
3¡¢µ¼ÖÂLGWRÖ´ÐÐдredo log bufferµ½online redo logµÄÌõ¼þ
a.Óû§Ö´ÐÐÊÂÎñÌá½»commit
b.ÿ3ÃëÖÓ»òredo log bufferÄÚÒÑ´ïµ½1/3Âú»ò°üº¬1MBÊý¾Ý
c.DBWn½ø³Ì½«Ð޸ĵĻº³åÇøÐ´Èë´ÅÅÌʱ£¨Èç¹ûÏàÓ¦µÄÖØ×öÈÕÖ¾Êý¾ÝÉÐδдÈë´ÅÅÌ£©
£¨Áù£©¡¢ASMM(Automatic Shared Memory Management)
1¡¢SGA_TARGET
1£©SGA_TARGETĬÈÏֵΪ0£¬¼´ASMM±»½ûÓá£ÐèÒªÊÖ¶¯ÉèÖÃSGA¸÷Öи÷×é¼þµÄ´óС¡£
2£©µ±SGA_TARGETΪ·Ç0ʱ£¬ÔòÆôÓÃASMM£¬×Ô¶¯µ÷ÕûÒÔϸ÷×é¼þ´óС£º
DB buffer cache£¨default pool£©
shared pool
large pool
streams pool
java pool
µ«ASSMÖУ¬ ÒÔϲÎÊýÈÔÐèÒªÊÖ¶¯Ö¸¶¨£º
log buffer
keep¡¢recycle¡¢ÒÔ¼°·Ç±ê×¼¿é»º³åÇø
¹Ì¶¨SGAÒÔ¼°ÆäËûÄÚ²¿·ÖÅä¡£
2¡¢ÆôÓÃASMMÐèÒª½«STATISTICS_LEVELÉèÖóÉTYPICAL»òALL
3¡¢ÆôÓÃASMM£¬×Ô¶¯µ÷ÕûSGAÄÚ²¿×é¼þ´óСºó¡£ÈôÊÖ¶¯Ö¸¶¨Ä³Ò»×é¼þÖµ£¬Ôò¸ÃֵΪ¸Ã×é¼þµÄ×îСֵ¡£Èç
ÊÖ¶¯ÉèÖÃSGA_TARGET=8G,SHARE_POOL_SIZE=1G£¬ÔòASMMÔÚ×Ô¶¯µ÷ÕûSGAÄÚ²¿×é¼þ´óСʱ£¬±£Ö¤share
pool²»»áµÍÓÚ1G¡£
SQL> SELECT component, current_size/1024/1024 size_mb
FROM v$sga_dynamic_components;
4¡¢SGA_MAX_SIZE
SGA_MAX_SIZEÖ¸¶¨ÄÚ´æÖпÉÒÔ·ÖÅ䏸SGAµÄ×î´óÖµ¡£
SGA_TARGETÊÇÒ»¸ö¶¯Ì¬²ÎÊý£¬Æä×î´óֵΪSGA_MAX_SIZEÖ¸¶¨µÄÖµ¡£
Îå¡¢PGA
£¨Ò»£©Private SQL Area
1¡¢±£´æÁ˵±Ç°»á»°µÄ°ó¶¨ÐÅÏ¢ÒÔ¼°ÔËÐÐʱÄÚ´æ½á¹¹¡£ÕâЩÐÅÏ¢
2¡¢Ã¿¸öÖ´ÐÐsqlÓï¾äµÄ»á»°£¬¶¼ÓÐÒ»¸öprivate sql area¡£
3¡¢µ±¶à¸öÓû§Ö´ÐÐÏàͬµÄsqlÓï¾ä£¬´ËsqlÓï¾ä±£´æÔÚÒ»¸ö³ÆÎªshared sql area¡£´Ëshare
sql area±»Ö¸¶¨¸øÕâЩÓû§µÄprivate sql area
4¡¢¹²Ïí·þÎñÆ÷ģʽ£ºprivate sql areaλÓÚSGAµÄshare pool»òlarge poolÖÐ
רÓ÷þÎñÆ÷ģʽ£ºprivate sql areaλÓÚPGAÖÐ
£¨¶þ£©Cursor¡¢SQL Areas
£¨Èý£©Work Area
PGAµÄÒ»´ó²¿·Ö±»·ÖÅ䏸Work Area£¬ÓÃÀ´Ö´ÐÐÈçϲÙ×÷£º
a.»ùÓÚ²Ù×÷·ûµÄÅÅÐò£¬group by¡¢order by¡¢rollupºÍ´°¿Úº¯Êý¡£
²ÎÊýΪsort_area_size
b.hashÉ¢ÁÐÁ¬½Ó£¬
²ÎÊýΪhash_area_size
c.λͼºÏ²¢£¬
²ÎÊýΪbitmap_merge_area_size
d.λͼ´´½¨£¬
²ÎÊýΪcreate_bitmap_area_size
e.ÅúÁ¿×°ÔزÙ×÷ʹÓõÄд»º´æ
£¨ËÄ£©Session memory
±£´æÁ˻ỰµÄ±äÁ¿£¬ÈçµÇ¼ÐÅÏ¢¼°ÆäËûÓë»á»°Ïà¹ØµÄÐÅÏ¢£¬¹²Ïí·þÎñÆ÷ģʽÏ£¬Session memoryÊǹ²ÏíµÄ¡£
£¨Î壩×Ô¶¯PGA¹ÜÀí
ÉèÖÃPGA_AGGREGATE_TARGETΪ·Ç0£¬ÔòÆôÓÃPGA×Ô¶¯¹ÜÀí£¬²¢ºöÂÔËùÓÐ*_area_sizeµÄÉèÖá£Èçsort_area_size,hash_area_sizeµÈ¡£
ĬÈÏΪÆôÓÃPGAµÄ×Ô¶¯¹ÜÀí£¬Oracle¸ù¾ÝSGAµÄ20%À´¶¯Ì¬µ÷ÕûPGAÖÐרÓÃÓëWork Area²¿·ÖµÄÄÚ´æ´óС£¬×îСΪ10MB¡£
ÓÃÓÚʵÀýÖи÷»î¶¯¹¤×÷Çø£¨work area£©µÄPGA×ÜÁ¿£¬ÎªPGA_AGGREGATE_TARGET¼õÈ¥ÆäËû×é¼þ·ÖÅäµÄPGAÄÚ´æ¡£µÃµ½µÄ½á¹û£¬°´ÕÕÌØ¶¨ÐèÇó¶¯Ì¬·ÖÅ䏸¶ÔÓ¦µÄ¹¤×÷Çø¡£
1£©ÉèÖÃPGA_AGGREGATE_TARGET´óСµÄ²½Öè
a.ÉèÖÃPGA_AGGREGATE_TARGETΪSGAµÄ20%£¬¶ÔÓÚDSSϵͳ£¬´ËÖµ¿ÉÄܹýµÍ¡£
b.ÔËÐеäÐ͵ĸºÔØ£¬Í¨¹ýoracleÊÕ¼¯µÄpgaͳ¼ÆÐÅÏ¢À´µ÷ÕûPGA_AGGREGATE_TARGETµÄÖµ¡£
c.¸ù¾ÝoracleµÄpga½¨Òéµ÷ÕûPGA_AGGREGATE_TARGET´óС¡£
2£©½ûÓÃ×Ô¶¯pga¹ÜÀí
ΪÏòºó¼æÈÝ£¬ÉèÖÃPGA_AGGREGATE_TARGETΪ0£¬¼´½ûÓÃpgaµÄ×Ô¶¯¹ÜÀí¡£¿ÉʹÓùØÁªµÄ*_area_size²ÎÊýµ÷Õû¶ÔÓ¦¹¤×÷ÇøµÄ×î´ó´óС¡£
bitmap_merge_area_size
create_bitmap_area_size
hash_area_size
sort_area_size |