[관리][튜닝] howcodi oracle tunning

# 전체 메모리 영역

SVRMGR> show sga

Total System Global Area                         59388368 bytes

Fixed Size                                          86480 bytes

Variable Size                                    54722560 bytes

Database Buffers                                  4399104 bytes

Redo Buffers                                       180224 bytes

# 데이타 버퍼 영역

SVRMGR> select name,value

     2> from v$sysstat

     3> where name in (‘consistent gets’,’db block gets’,’physical reads’);

NAME                                                             VALUE    

—————————————————————- ———-

db block gets                                                       4483914

consistent gets                                                    71922103

physical reads                                                     40768726

hit ratio = 47%     database buffers 를 줄인다.

#공유 풀 메모리

SVRMGR> select sum(pins) pins, sum(reloads) reloads

     2> from v$librarycache;

PINS       RELOADS  

———- ———-

   3337726      29024

ratio(pins) = 0.87

ratio(gets) = 9.94     shared_pool_size 값을 늘려준다.

#로그 버퍼

SVRMGR> select name,value

     2> from v$sysstat

     3> where name = ‘redo log space requests’;

NAME                                                             VALUE    

—————————————————————- ———-

redo log space requests                                                 969

log_buffer 값을 늘린다.

#롤백 세크먼트

SVRMGR>  select r.name, s.gets, s.waits

     2>  from v$rollstat s, v$rollname r

     3>  where s.usn = r.usn;

NAME                           GETS       WAITS    

—————————— ———- ———-

SYSTEM                               2073          0

R01                                 53871          0

R02                                 43727          0

R03                                 55367          0

R04                                 48733          0

# 디스크 경합

SVRMGR> select d.name, f.phyrds, f.phywrts

     2> from v$datafile d, v$filestat f

     3> where d.file# = f.file#;

NAME                                                                             PHYRDS     PHYWRTS  

——————————————————————————– ———- ———-

/data1/oradata/ORCL/system01.dbf                                                    4922542     128066

/data1/oradata/ORCL/oemrep01.dbf                                                        298        296

/data1/oradata/ORCL/rbs01.dbf                                                        102124      22494

/data1/oradata/ORCL/temp01.dbf                                                          302        332

/data1/oradata/ORCL/users01.dbf                                                         298        296

/data1/oradata/ORCL/indx01.dbf                                                          298        296

/data1/oradata/ORCL/drsys01.dbf                                                         298        296

/data2/datafile01.dbf                                                                   298        296

/data2/datafile02.dbf                                                                   298        296

/data2/datafile3.dbf                                                                 818916      10531

/data2/datafile4.dbf                                                                 904375      10461

/data2/datafile5.dbf                                                                 876762      13164