{"id":457,"date":"2014-01-28T13:13:43","date_gmt":"2014-01-28T22:13:43","guid":{"rendered":"\/blog\/?p=457"},"modified":"2023-09-21T09:38:46","modified_gmt":"2023-09-21T00:38:46","slug":"oracle-sga-%ec%84%b1%eb%8a%a5%ed%8f%89%ea%b0%80-%eb%b0%8f-%ec%b5%9c%ec%a0%81%ed%99%94","status":"publish","type":"post","link":"https:\/\/hasu0707.duckdns.org\/blog\/?p=457","title":{"rendered":"Oracle SGA \uc131\ub2a5\ud3c9\uac00 \ubc0f \ucd5c\uc801\ud654"},"content":{"rendered":"\n

\u203b sqlplus \/ as sysdba \uc5d0\uc11c \uc2e4\ud589\ud55c\ub2e4.<\/span>

\u25a0 DATA DICTIONARY \uc131\ub2a5 \ud3c9\uac00<\/span><\/b>

SELECT  sum(gets) \"gets\",<\/span>
 sum(getmisses) \"getmisses\" ,<\/span>
 round( (1-(sum(getmisses)\/sum(gets)) )*100,2) \"Hit Ratio\"<\/span>
FROM  gv$rowcache<\/span>
WHERE gets > 0 ;<\/span>

\u261e \uc774 \uac12\uc740 Hit Ratio\uac00 95% \uc774\uc0c1\uc774\uba74 \uc815\uc0c1\uc774\ub2e4. \ub9cc\uc57d \uc218\uce58\uac00 \ub192\uc73c\uba74 shared_pool_size\uc758 \uac12\uc744 \uc99d\uac00 \uc2dc\ucf1c\uc57c \ud55c\ub2e4.<\/span>


\u25a0 LIBRARY CACHE \uc131\ub2a5 \ud3c9\uac00<\/span><\/b>

select to_char(trunc(sum(reloads)\/sum(pins)*100, 5),99.99999)||'% (less than 1%)' \"Library Cache MISS RATIO\" from v$librarycache;<\/span>

\u261e \uc774 \uac12\uc740 1~2% \uc774\ud558\uc774\uba74 \uc815\uc0c1\uc774\ub2e4. \ub9cc\uc57d \uc218\uce58\uac00 \ub192\uc73c\uba74 shared_pool_size\uc758 \uac12\uc744 \uc99d\uac00 \uc2dc\ucf1c\uc57c \ud55c\ub2e4.<\/span>


\u25a0 \ubc84\ud37c \uce90\uc2dc \uc131\ub2a5 \ud3c9\uac00<\/span><\/b>

SELECT  bg.value \"DB Block Gets\",<\/span>
 cg.value \"Consistent Gets\",<\/span>
 pr.value \"Physical Reads\",<\/span>
 round((1-(pr.value\/(bg.value+cg.value)))*100, 2) \"Hit Ratio\"<\/span>
FROM  v$sysstat pr,<\/span>
 v$sysstat bg,<\/span>
 v$sysstat cg<\/span>
WHERE  pr.name = 'physical reads'<\/span>
 AND  bg.name = 'db block gets'<\/span>
 AND  cg.name = 'consistent gets';<\/span>

\u261e \uc774 \uac12\uc740 Hit Ratio\uac00 90% \uc774\uc0c1 \ub098\uc640\uc57c \uc815\uc0c1\uc774\uba70, 70% \uc774\ud558\ub85c \ub5a8\uc5b4\uc838 \uc788\uc744 \uacbd\uc6b0<\/span>
   db_block_buffers \ub610\ub294 db_cache_size\ub97c \uc99d\uac00\uc2dc\ucf1c\uc57c \ud55c\ub2e4.<\/span>


\u25a0 \uc18c\ud2b8 \uc131\ub2a5 \ud3c9\uac00<\/span><\/b>

select name, value from v$sysstat where name in ('sorts (memory)', 'sorts (disk)');<\/span>

\u261e \uba54\ubaa8\ub9ac\uc640 \ub514\uc2a4\ud06c \uc591\ucabd\uc5d0\uc11c \uc18c\ud2b8\uc5d0 \uc5bc\ub9c8\ub098 \ub9ce\uc740 \uc790\uc6d0\uc744 \ud560\ub2f9\ud558\ub294\uc9c0 \uc54c\uc544\ubcf8\ub2e4.<\/span>
   \ub9cc\uc57d \uba54\ubaa8\ub9ac \uc18c\ud2b8\ubcf4\ub2e4 \ub514\uc2a4\ud06c \uc18c\ud2b8\uac00 \ub9ce\uc73c\uba74 sort_area_retained_size\uc758<\/span>
   \ud06c\uae30\ub97c \ub298\ub824\uc8fc\uc5b4\uc57c \ud55c\ub2e4.<\/span>


\u25a0 \ub9ac\ub450 \ub85c\uadf8 \ubc84\ud37c \uc131\ub2a5 \ud3c9\uac00<\/span><\/b>

SELECT value \"Redo log request\" FROM  v$sysstat WHERE name = 'redo log space requests';<\/span>

\u261e \uacb0\uacfc\uac12\uc774 0\uc5d0 \uac00\uae4c\uc6cc\uc9c8 \ub54c\uae4c\uc9c0 \ub9ac\ub450\ub85c\uadf8 \ubc84\ud37c \uc218\ub97c \ub298\ub824\uc57c \ud55c\ub2e4.

<\/span>
\u25a0 ROLLBACK SEGMENT \uc131\ub2a5 \ud3c9\uac00<\/span><\/b>

column 'rollback segment name' format a25<\/span>
column miss_ratio format a10<\/span>
column rssize format 99,999,999<\/span>
column writes format 99,999,999<\/span>
column xacts format 999<\/span>
column status format a8<\/span>

select name \"rollback segment name\",<\/span>
       rssize, writes, xacts, status,<\/span>
       trunc(waits\/gets*100, 5)||' %' miss_ratio<\/span>
from v$rollstat, v$rollname<\/span>
where v$rollstat.usn = v$rollname.usn<\/span>
order by waits\/gets desc;<\/span>

\u261e MISS RATIO\uac00 1~2% \uc774\ud558 \uc774\uc5b4\uc57c \ud55c\ub2e4.<\/span>
   1~2%\ubcf4\ub2e4 \ud06c\uba74 ROLLBACK SEGMENT\uc758 \uac2f\uc218\ub97c \ub298\ub824\uc8fc\uc5b4\uc57c \ud55c\ub2e4.<\/span><\/p>

<\/p>


SHOW PARAMETERS<\/span>
SHOW PARAMETER process<\/span>
SHOW PARAMETER spfile<\/span>

Default: 300<\/span>
ALTER SYSTEM SET OPEN_CURSORS=300 SCOPE=SPFILE;<\/span>

Default: 150<\/span>
ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;<\/span>

Default: 0<\/span>
ALTER SYSTEM SET SHARED_POOL_SIZE=200M SCOPE=SPFILE;<\/span>

Default: 0<\/span>
ALTER SYSTEM SET LARGE_POOL_SIZE=200M SCOPE=SPFILE;<\/span>

Default: 0<\/span>
ALTER SYSTEM SET DB_BLOCK_SIZE=8192 SCOPE=SPFILE;<\/span>

Default: 0<\/span>
ALTER SYSTEM SET DB_CACHE_SIZE=200M SCOPE=SPFILE;<\/span>

Default: 0<\/span>
ALTER SYSTEM SET SORT_AREA_RETAINED_SIZE=0 SCOPE=SPFILE;<\/span>

Default: 1G<\/span>
ALTER SYSTEM SET MEMORY_TARGET=1G SCOPE=SPFILE;<\/span>

ALTER SYSTEM SET JAVA_POOL_SIZE=0 SCOPE=SPFILE;<\/span>
ALTER SYSTEM SET STREAMS_POOL_SIZE=0 SCOPE=SPFILE;<\/span>

SELECT (<\/span>
   (SELECT SUM(value) FROM V$SGA) -<\/span>
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)<\/span>
   ) \"SGA_TARGET\"<\/span>
FROM DUAL;<\/span>
ALTER SYSTEM SET SGA_TARGET=512M SCOPE=SPFILE;<\/span>

ALTER SYSTEM SET SGA_MAX_SIZE=1024M SCOPE=SPFILE;<\/span>
ALTER SYSTEM SET LARGE_POOL_SIZE=256M SCOPE=SPFILE;<\/span>

<\/p>\n","protected":false},"excerpt":{"rendered":"

\u203b sqlplus \/ as sysdba \uc5d0\uc11c \uc2e4\ud589\ud55c\ub2e4. \u25a0 DATA DICTIONARY \uc131\ub2a5 \ud3c9\uac00 SELECT  sum(gets) “gets”, sum(getmisses) “getmisses” , round( (1-(sum(getmisses)\/sum(gets)) )*100,2) “Hit Ratio”FROM  gv$rowcacheWHERE gets > 0 ; \u261e \uc774 \uac12\uc740 Hit Ratio\uac00 95% \uc774\uc0c1\uc774\uba74 \uc815\uc0c1\uc774\ub2e4. \ub9cc\uc57d \uc218\uce58\uac00 \ub192\uc73c\uba74 shared_pool_size\uc758 \uac12\uc744 \uc99d\uac00 \uc2dc\ucf1c\uc57c \ud55c\ub2e4. \u25a0 LIBRARY CACHE \uc131\ub2a5 \ud3c9\uac00 select to_char(trunc(sum(reloads)\/sum(pins)*100, 5),99.99999)||’% (less than 1%)’ “Library Cache […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[10],"tags":[],"class_list":["post-457","post","type-post","status-publish","format-standard","hentry","category-computing_database"],"_links":{"self":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/457","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=457"}],"version-history":[{"count":0,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/457\/revisions"}],"wp:attachment":[{"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=457"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=457"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hasu0707.duckdns.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=457"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}