성능 분석관련 SQL (펌)

rem **********************************************************

rem *         system계정으로 connect 되어야 수행 가능하다.   *

rem *          (자동으로 sysstat.out로 spooling 된다)        *

rem **********************************************************

set feedback off

clear columns;

spool sysstat.out

prompt *******************************************************

prompt *               LIBRARY CACHE TUNING                  *

prompt *******************************************************

select to_char(trunc(sum(reloads)/sum(pins)*100, 5),99.99999)||'% 

     (less than 1%)' "Library Cache MISS RATIO"

from v$librarycache;

prompt #  sum(reloads)/sum(pins) * 100 > 1%

prompt #  libary cache miss ratio is good if it is less than 1 -2 %

prompt #  shared_pool_size(default 3.5M)의 값을 증가 시켜 library cache를 늘린다.

pause ... please press enter key ...

prompt *******************************************************

prompt *            DATA  DICTIONARY CACHE TUNING            *

prompt *******************************************************

select trunc(sum(getmisses)/sum(gets)*100, 5)||'%  (less than 9.8%)'

"Data dictionary miss ratio "

from v$rowcache;

prompt

prompt  #  sum(getmisses)/sum(gets) * 100 < 10 ~ 15%

prompt  #  data dictionary miss ratio is good if it is less than 9.8%

prompt  #  ratio를 늘리려면 shared_pool_size의 값을 증가 시켜야 한다.

prompt

pause ... please press enter key ....

rem 계산의 편의를 위한 temporary table 생성

create table buffer_cache

( aa   number,

  bb   number,

  cc   number

);

insert into buffer_cache

select a.value, b.value, c.value

from (select value from v$sysstat where name = 'db block gets') a,

     (select value from v$sysstat where name = 'consistent gets') b,

     (select value from v$sysstat where name = 'physical reads') c;

prompt *******************************************************

prompt *              BUFFER CACHE TUNING                    *

prompt *******************************************************

select trunc((1 - (cc/(aa+bb)))*100, 5)||'%  (more than 60-70%)'

      "Buffer Cache hit ratio"

from buffer_cache;

prompt # buffer_cache hit ratio is good if it is more than 60-70%

prompt # Hit Ratio = 1 - physical reads/(db block gets + consistent gets)

prompt # hit ratio가 60 - 70% 이하일 경우에는 db_block_buffers의 값을

prompt # 증가 시켜야 한다.

pause ... please press enter key ....

 

prompt *******************************************************

prompt *  DATA FILE'S PHYSICAL READ, PHYSICAL WRITE NUMBER   *

prompt *******************************************************

column name format a35 heading '  Datafile Name';

column tablespace_name format a15 heading 'Tablespace Name';

select tablespace_name, name, phyrds, phywrts

  from v$datafile df, v$filestat fs, dba_data_files

where df.file# = fs.file# and

      df.file# = dba_data_files.file_id;

prompt # 특정 Disk에 집중적인 I/O 발생시 Tablespace를 적절히 옮겨

prompt # I/O를 분산한다.

prompt # < Distributing I/O >

prompt #    - Data File과 Redo Log File을 다른 Disk에 분리해 저장

prompt #    - Table과 Index를 다른 Disk에 분리해 저장

prompt #    - ORACLE Server에 관련되지 않은 Disk I/O의 최소화.

pause ... please press enter key ....

prompt *******************************************************

prompt *        INTERNAL SORT AND EXTERNAL SORT              *

prompt *******************************************************

select name, value from v$sysstat

where name in ('sorts (memory)', 'sorts (disk)');

prompt # MEMORY SORT에 비해 DISK SORT가 상대적으로 많으면

prompt # SORT_AREA_SIZE의 크기를 늘려 주거나 또는 sort_area_size와

prompt # sort_area_retained_size를 적절히 조정하여 DISK I/O를 감소시킨다.

prompt # 기본적으로 on-line 환경에서 sort_area_size는 retained size의

prompt # 2배 정도를 제안하지만 업무에 따라 크게 조정될 수 있다.

pause ... please press enter key ....

prompt *******************************************************

prompt *        ROLLBACK SEGMENT'S WAIT RATIO                *

prompt *******************************************************

column miss_ratio format 999.999999 heading 'Miss Ratio';

select name "Rollback Segment Name",

       trunc(waits/gets*100, 5)||'%' miss_ratio

from v$rollstat, v$rollname

where v$rollstat.usn = v$rollname.usn

order by name;

prompt #    MISS RATIO가 1~2% 이하 이어야 한다.

prompt #    1~2%보다 크면 ROLLBACK SEGMENT의 갯수를 늘려주어야 한다.

prompt #    < Other Guideline >    *Transaction 수 = Process * 1.1

prompt #    *****************************************************

prompt #    * Concurrent Trans. 수(n)   *   Rollback Segs. 수   *

prompt #    *****************************************************

prompt #    *         n > 16            *           4           *

prompt #    *      16 <= n < 32         *           8           *

prompt #    *        32 <= n            *    n/4, (50개 이하)   *

prompt #    *****************************************************

pause ... please press enter key ....

prompt *******************************************************

prompt *            REDO LOG FILE'S WAIT RATIO               *

prompt *******************************************************

select value "Redo log request"

from v$sysstat

where name = 'redo log space requests';

prompt # redo log space request의 값이 "0" 근처의 값이 유지되도록

prompt # log_buffer를 증가시킨다.

pause ... please press enter key ....

prompt *******************************************************

prompt *             FREE LIST'S WAIT RATIO                  *

prompt *******************************************************

select trunc(a.count/(b.aa + b.cc)*100, 5)||

   '%' "Free list wait ratio(1%이하)"

from v$waitstat a, buffer_cache b

where a.class = 'free list';

prompt # Freelists는 insert나 update시 검색되는 해당 object의

prompt # free block list로서 동시에 access될 때 Performance에 영향을

prompt # 미치는 중요한 요소이며 특히 OPS환경에서 더욱 유의해야 한다.

prompt #  (Db Block Gets + Consistents Gets) < 1%

pause ... please press enter key ....

prompt *******************************************************

prompt *                                                     *

prompt *  tablespace의 사용현황을 보기 위한 select statement *

prompt *   db_block_size 를 8K 로 계산.                      *

prompt *                                                     *

prompt *******************************************************

prompt

  select distinct d.file_id               file#,

  d.tablespace_name                 ts_name,

d.bytes /1024 / 1024               MB,

d.bytes / 8192                     total_blocks,

  sum(e.blocks)                      used_blocks,

to_char( nvl( round( sum(e.blocks)/(d.bytes/8192), 4),0) *100,'09.99') || ' %' pct_used

from   dba_extents  e, dba_data_files  d

  where  d.file_id = e.file_id(+)

  group by  d.file_id , d.tablespace_name , d.bytes

  order by 1,2 ;

drop table buffer_cache;

clear columns

set pagesize 15

spool off

 

어디까지나 참고사항으로 쓰고 내가 직접 스크립트를 작성하여야만 실력이 늘어나니깐..