[관리][튜닝] Onnoff 사이트 Oracle tunning

메모리 영역

SVRMGR> show sga

Total System Global Area                        146128272 bytes

Fixed Size                                          64912 bytes

Variable Size                                   129114112 bytes

Database Buffers                                 16777216 bytes

Redo Buffers                                       172032 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                                                        975551

consistent gets                                                    35976174

physical reads                                                       749147

hit ratio = 98%

라이브러리 튜닝

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

     2> from v$librarycache;

PINS       RELOADS  

———- ———-

   1062459      10474

ratio = 0.9

SVRMGR> select sum(gets) gets, sum(getmisses) getmisses

     2> from v$rowcache;

GETS       GETMISSES

———- ———-

    780512        213

ratio = 0.02

로그 버퍼

SVRMGR> select name,value

     2> from v$sysstat

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

NAME                                                             VALUE    

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

redo log space requests                                                  25

롤백세그먼트

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                               2754          0

ONNOFF_ROLL                         61984          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  

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

/home/oracle/product/8.1.5//oradata/onnoffdb/system01.dbf                            231761       5345

/home/oracle/product/8.1.5//oradata/onnoffdb/oemrep01.dbf                                29         27

/home/oracle/product/8.1.5//oradata/onnoffdb/rbs01.dbf                                   29         27

/home/oracle/product/8.1.5//oradata/onnoffdb/temp01.dbf                                  99        623

/home/oracle/product/8.1.5//oradata/onnoffdb/users01.dbf                                 29         27

/home/oracle/product/8.1.5//oradata/onnoffdb/indx01.dbf                                  29         27

/home/oracle/product/8.1.5//oradata/onnoffdb/drsys01.dbf                                 29         27

/home/oracle/onnoff_01.dbf                                                            32825       4519

/home/oracle/product/8.1.5/oradata/datafile1.dbf                                         29         27

SVRMGR> select file_name, bytes from dba_data_files;

FILE_NAME                                                                        BYTES    

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

/home/oracle/product/8.1.5//oradata/onnoffdb/system01.dbf                         277872640

/home/oracle/product/8.1.5//oradata/onnoffdb/oemrep01.dbf                           5242880

/home/oracle/product/8.1.5//oradata/onnoffdb/rbs01.dbf                             31913984

/home/oracle/product/8.1.5//oradata/onnoffdb/temp01.dbf                            26214400

/home/oracle/product/8.1.5//oradata/onnoffdb/users01.dbf                           31457280

/home/oracle/product/8.1.5//oradata/onnoffdb/indx01.dbf                            15728640

/home/oracle/product/8.1.5//oradata/onnoffdb/drsys01.dbf                           83886080

/home/oracle/onnoff_01.dbf                                                        104857600

/home/oracle/product/8.1.5/oradata/datafile1.dbf                                   52428800

SVRMGR> select username,default_tablespace from dba_users;

USERNAME                       DEFAULT_TABLESPACE            

—————————— ——————————

SYS                            SYSTEM                        

SYSTEM                         SYSTEM                        

OUTLN                          SYSTEM                        

ORDPLUGINS                     SYSTEM                        

DBSNMP                         SYSTEM                        

ORDSYS                         SYSTEM                        

MDSYS                          SYSTEM                        

ONNOFF                         ONNOFF_TABLE                  

AURORA$ORB$UNAUTHENTICATED     SYSTEM                        

HANNAH                         SYSTEM                        

KOBIS                          SYSTEM                        

SOFTC                          TBS01                        

SVRMGR> select v$logfile.member,v$logfile.group#,v$log.status,bytes

     2> from v$logfile,v$log

     3> where v$logfile.group#=v$log.group#;

MEMBER                                                                           GROUP#     STATUS           BYTES    

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

/home/oracle/product/8.1.5//oradata/onnoffdb/redo01.log                                   1 CURRENT             1024000

/home/oracle/product/8.1.5//oradata/onnoffdb/redo02.log                                   2 INACTIVE            1024000

———————————————————————————————————

튜닝후

—————–

SVRMGR> show sga

Total System Global Area                        135142800 bytes

Fixed Size                                          64912 bytes

Variable Size                                   128352256 bytes

Database Buffers                                  6553600 bytes

Redo Buffers                                       172032 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                                                           398

consistent gets                                                       29142

physical reads                                                         2451

3 rows selected.

hit ratio = 0.92

라이브러리 캐쉬 영역

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

     2> from v$librarycache;

PINS       RELOADS  

———- ———-

       992          6

로그 버퍼

SVRMGR> select name,value

     2> from v$sysstat

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

NAME                                                             VALUE    

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

redo log space requests                                                   0

롤백 세그먼트

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                                 52          0

R01                                     8          0

R02                                     8          0

R03                                     6          0

R04                                     6          0

ONNOFF_ROLL                            12          0

R05                                     6          0

R06                                     6          0

R07                                     6          0

R08                                     6          0

R09                                     6          0

R10                                     6          0