À¯¿ëÇÑ SQL scripts 3
ÀÛ¼ºÀÚ °ü¸®ÀÚ ÀÛ¼º½Ã°£ 2006-03-07 16:40:27
 

[b][color=BLUE]SGAÀÇ shared pool ÀÇ hitratio¸¦ ¿¬»êÇØÁÖ´Â SQL[/color][/b]

/*
**  Shared_poolÀÇ hit ratioº¸´Â ½ºÅ©¸³Æ®..                      <¹ÚÁ¦¿ë>
**
**  ÀÌ ¿µ¿ªÀº SQL Äõ¸®¹®ÀÌ ÀúÀåµÇ°í, À¯Àúº° »ç¿ë ¿µ¿ª°ú, µ¥ÀÌÅÍ µñ¼Å³Ê¸®µîÀÌ ÀúÀåµÈ´Ù.
**  ¸¸ÀÏ Àû°Ô ÇÒ´çµÇ¸é À¯ÀúÀÇ Á¢¼ÓÀÌ ¸¹¾ÆÁú¼ö·Ï throughput¿¡ Å« ¿µÇâÀ» ÁØ´Ù.
**  hit ratio´Â 95% ÀÌ»óÀ» À¯Áö½ÃÄÑ¾ß ÇÑ´Ù.
** 
*/
select sum(gets) "Gets", sum(getmisses) "Misses",
      (1-(sum(getmisses) / (sum(gets)+sum(getmisses))))*100
      "HitRate"
from v$rowcache;

[b][color=BLUE]SGA¿¡ Å« ¿µÇâÀ» ÁÖ´Â ÆĶó¹ÌÅ͵éÀÇ ¼³Á¤À» Ç¥½ÃÇØÁÖ´Â SQL[/color][/b]

/*
**  DBÀÇ ÁÖ¿ä ¸Þ¸ð¸® »ç¿ë Á¶È¸                    <¹ÚÁ¦¿ë>
**
**  DBÀÇ ÁÖ¿ä ¸Þ¸ð¸® »ç¿ëÀ» º¸¿©ÁØ´Ù. DB°¡ »ç¿ëÇÏ´Â ¸Þ¸ð¸®´Â
**  v7.3ÀÇ °æ¿ì OS¸Þ¸ð¸®ÀÇ 2/5 ¸¦, v8.x ¹öÁ¯ÀÇ °æ¿ì 1/2 Á¤µµ¸¦
**  ÇÒ´çÇØ ÁÖ´Â °ÍÀÌ ÁÁ´Ù.
** 
*/
select name, value
from  v$parameter
where name in('db_block_buffers','db_block_size','shared_pool_size','sort_area_si
ze');

[b][color=BLUE]SGAÁß¿¡ Block bufferÀÇ ÇöÀç »ç¿ë·®°ú ºó°ø°£À» º¸°í½ÍÀ» ¶§[/color][/b]

/*
**  DB_BLOCK_BUFFERSÀÇ ÇöÀç »ç¿ë ÇöȲÀ» º¸¿©ÁÜ.            <¹ÚÁ¦¿ë>
**
**  block_buffer¸¦ Æ©´×Çϱâ Àü¿¡ ÇöÀçÀÇ »ç¿ëÇöȲÀ» º¸¿©ÁØ´Ù.
**  ÀÌ µ¥ÀÌÅ͸¦ ÁÖ±âÀûÀ¸·Î º¸°üÇÏ¿© ºÐ¼®ÇÑ´Ù.
*/
select decode(state, 0, 'FREE',
                    1, decode(lrba_seq,0,'AVAILABLE','BEING USED'),
                    3, 'BEING USED', state)
        "BLOCK STATUS", count(*)
from x$bh
group by decode(state,0, 'FREE',
                      1, decode(lrba_seq,0,'AVAILABLE','BEING USED'),
                      3, 'BEING USED', state);

[b][color=BLUE]SGAÀÇ Block bufferÀÇ hitratio¸¦ ¿¬»êÇØÁÖ´Â SQL[/color][/b]

/*
**  DB_BLOCK_BUFFERSÀÇ hit ratioº¸´Â ½ºÅ©¸³Æ®..                      <¹ÚÁ¦¿ë>
**
**  ÀÌ ¿µ¿ªÀº À¯ÀúÀÇ Äõ¸® ³»¿ëÀÌ ¹öÆÛ¸µ µÇ´Â °ø°£À¸·Î Å©±â°¡ ÀûÀ¸¸é
**  À¯Àúº°·Î °úµµÇÑ disk read¸¦ ¹ß»ý½ÃŲ´Ù.
**  hit ratio´Â 90~95% ÀÌ»óÀ» À¯Áö½ÃÄÑ¾ß ÇÑ´Ù.
** 
*/
select 1-(sum(decode(name, 'physical reads', value,0))/
(sum(decode(name, 'db block gets', value,0)) +
(sum(decode(name, 'consistent gets', value,0))))) * 100
"Read Hit Ratio"
from v$sysstat;

[b][color=BLUE]ÇÑ À¯Àú ½ºÅ°¸¶ÀÇ ¸ðµç °´Ã¼¸¦ Analyze ÇØÁÖ´Â SQL[/color][/b]

/*
**  Å×À̺í analyze ½ºÅ©¸³Æ® 2..                      <¹ÚÁ¦¿ë>
**  ÇÑ À¯Àú¿¡ ¼ÓÇÑ ¸ðµç °´Ã¼¸¦ analyzeÇÑ´Ù.
**
**  »ç¿ë¹æ¹ý SQL>@analyze0 [À¯ÀúID] 
**                          À¯ÀúID´Â ¹Ýµå½Ã ´ë¹®ÀÚ·Î.
*/
exec dbms_utility.analyze_schema('&1','DELETE');
exec dbms_utility.analyze_schema('&1','COMPUTE');

[b][color=BLUE]ÁöÁ¤ÇÑ Å×À̺íÀ» ´Ù½Ã Analyze ÇØÁÖ´Â SQL[/color][/b]

/*
**  Å×À̺í analyze ½ºÅ©¸³Æ®..                      <¹ÚÁ¦¿ë>
**  Query¸¦ ÆĽÌÇÏ´Â Optimizer·Î ÇÏ¿©±Ý ´õ¿í Á¤È®ÇÏ°í, ºü¸¥ ÆĽÌÀ» À¯µµÇϱâÀ§ÇØ Analyze¸¦ ÇÑ´Ù.
**
**  »ç¿ë¹æ¹ý SQL>@analyze1 [Å×À̺í¸í] 
*/
analyze table &1 delete statistics;
analyze table &1 compute statistics;

[b][color=BLUE]TKPROF »ç¿ë¹æ¹ý[/color][/b]

** trace °á°ú¸¦ ÆľÇÀ» À§ÇÑ Tkprof À¯Æ¿¸®Æ¼ »ç¿ë

** TKPROF trace_file output_file [¿É¼Ç¼³Á¤] [explain=username/password]

**        trace_file         SQL_TRACE·Î »ý¼ºÇÑ Æ®·¹À̽º *.trc Æ®·¹À̽º ÆÄÀÏ.
**        output_file         °á°ú°¡ ÀúÀåµÉ ÆÄÀϸí
**        SORT=parameters ¼ÒÆà Æĸ®¹ÌÅÍ execpu, ....
**        EXPLAIN=username/password
**        SYS=no/yes          ½Ã½ºÅÛÀÌ »ç¿ëÇÑ Äõ¸®¸¦ º¼¶§´Â yes·Î ¼³Á¤ÇÑ´Ù.

¿¹Á¦))))

tkprof ora_12626.trc result.txt explain=scott/tiger sys=no

c:\orawin95\bin\tkprof73.exe 2.TRC ORA%1.TXT explain=TUNING/TUNING sys=no sort=execpu

EDIT ORA%1.PRF


¸ñ·Ï | ÀÔ·Â | ¼öÁ¤ | ´äº¯ | »èÁ¦