오라클 메모리 튜닝 권고

######  What are the major tuning areas in database performance tuning  #######

  - Memory - shared pool, large pool, buffer cache, redo log buffer, and sort area size.
  - I/O - distributing I/O, striping, multiple DBWn processes, and DBWn I/O slaves.
  - CPU - CPU utilization.
  - Space management - extent allocation and Oracle block efficiency.
  - Redo log and checkpoint - redo log file configuration, redo entries, and checkpoint.
  - Rollback segment - sizing rollback segments.
  - Network


###### 조정해야할 메모리 영역
전체 SGA 영역 : 2.4G
shared_pool_size = ???
large_pool_size = ???
java_pool_size = ???
db_cache_size = ???
SHARED_POOL_RESERVED_SIZE=???

 

##################################################
### PGA 성능(할당량) 조정
##################################################
PGA target advice => v$pga_target_advice를 이용하여 적당 할당량을 조사한다.

-- PGA 어드바이스
select round(pga_target_for_estimate/1024/1024) as target_size_MB,
bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
estd_pga_cache_hit_percentage as est_hit_pct,
estd_overalloc_count as est_overalloc
from v$pga_target_advice;

=>위의 쿼리를 실행해서 최적의 PGA할당량을 찾아낸다

PGA Memory Advisory  DB/Inst: KMSMESV1/KMSMESV1  End Snap: 886
-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value
   where Estd PGA Overalloc Count is 0

                                       Estd Extra    Estd PGA   Estd PGA
PGA Target    Size           W/A MB   W/A MB Read/      Cache  Overalloc
  Est (MB)   Factr        Processed Written to Disk     Hit %      Count
---------- ------- ---------------- ---------------- -------- ----------
        94     0.1        326,688.8         11,869.3     96.0      2,140
       188     0.3        326,688.8         11,869.3     96.0      2,139
       375     0.5        326,688.8          2,393.2     99.0        646
       563     0.8        326,688.8            110.8    100.0          0
       750     1.0        326,688.8            110.8    100.0          0
       900     1.2        326,688.8            110.8    100.0          0
     1,050     1.4        326,688.8            110.8    100.0          0
     1,200     1.6        326,688.8            110.8    100.0          0
     1,350     1.8        326,688.8            110.8    100.0          0
     1,500     2.0        326,688.8            110.8    100.0          0
     2,250     3.0        326,688.8            110.8    100.0          0
     3,000     4.0        326,688.8            110.8    100.0          0
     4,500     6.0        326,688.8            110.8    100.0          0
     6,000     8.0        326,688.8            110.8    100.0          0
          -------------------------------------------------------------

:::::::::::::::::::::::         결과          ::::::::::::::::::::::::::::::
=========> PGA 사이즈 조정 : 현재 사이즈 적정 


##################################################
### SGA 크기 조정
##################################################

SGA Target Advisory  DB/Inst: KMSMESV1/KMSMESV1  End Snap: 886

SGA Target SGA Size   Est DB      Est DB   Est Physical
  Size (M)   Factor Time (s) Time Factor          Reads
---------- -------- -------- ----------- --------------
       752       .5   78,897         1.0      5,109,138
     1,128       .8   78,044         1.0      4,873,605
     1,504      1.0   77,494         1.0      4,723,249
     1,880      1.3   77,293         1.0      4,667,665
     2,256      1.5   77,238         1.0      4,653,967
     2,632      1.8   77,238         1.0      4,653,967
     3,008      2.0   77,239         1.0      4,653,967
-------------------------------------------------------------


:::::::::::::::::::::::         결과          ::::::::::::::::::::::::::::::
=========> SGA 사이즈 조정 : 1.5GB -> 2.5GB로 사이즈 조정 
★★★★ statspack분석 결과에 의해  SGA_TARGET 파라메타 2.5GB로 재조정.
alter system set SGA_MAX_SIZE=(2.5GB)

##################################################
### shared pool 성능(할당량) 조정
##################################################

1.먼저 현재 사용중인 데이타베이스에 대해 라이브러리 캐시영역에 대한 크기가 적정한지 조사한다.

select namespace,gets,gethits,gethitratio
from v$librarycache
where namespace = 'SQL AREA';

=> 참조 : 만약 gethitratio 컬럼의 값이 90%이상이라면 라이브러리 캐쉬영역이 개발자들의 SQL 파싱정보를 저장하기에 충분한 메모리 공간을 확보하고 있음을 의미하며 만약 90% 이하라면 성능이 저하 될 수도 있다는 것을 의마한다.

=> Quality DB의 경우 다음과 같은 결과가 나옴.
NAMESPACE             GETS    GETHITS GETHITRATIO
--------------- ---------- ---------- -----------
SQL AREA            885978     878552  .991618302
=========> 99%

## 부가적으로 pins에 대한 reloads의 비율을 확인한다.

select sum(pins),sum(reloads),sum(reloads) / sum(pins)
from v$librarycache
where namespace = 'SQL AREA';

=> 완성차 DB의 경우 다음과 같이 나옴.
 SUM(PINS) SUM(RELOADS) SUM(RELOADS)/SUM(PINS)
---------- ------------ ----------------------
  85931701          506             5.8884E-06

========> 참조 : PINS에 대한 reloads에 대한 비율이 1%미만일 경우 라이브러리 캐쉬 영역의 크기가 SQL 파싱정보를
                 저장하기에 충분하다는 의미이다.

 

현재 할당된 shared_pool 사이즈를 조사한다.
select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

=> 완성차 DB의 경우 다음과 같이 나옴.
POOL             MBYTES
------------ ----------
             863.996956
shared pool  511.985039
streams pool 48.0495529
large pool           32
java pool            64


2. Shared pool advice => v$shared_pool_advice 를 이용해서 oracle의 사이즈 advice를 조사한다.


SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",
       shared_pool_size_factor "Size Factor",estd_lc_time_saved "Time Saved in sec"
  FROM v$shared_pool_advice;

Shared Pool Advisory  DB/Inst: KMSMESV1/KMSMESV1  End Snap: 886
-> SP: Shared Pool     Est LC: Estimated Library Cache   Factr: Factor
-> Note there is often a 1:Many correlation between a single logical object
   in the Library Cache, and the physical number of memory objects associated
   with it.  Therefore comparing the number of Lib Cache objects (e.g. in
   v$librarycache), with the number of Lib Cache Memory Objects is invalid

                                        Est LC Est LC  Est LC Est LC
    Shared    SP   Est LC                 Time   Time    Load   Load      Est LC
      Pool  Size     Size       Est LC   Saved  Saved    Time   Time         Mem
  Size (M) Factr      (M)      Mem Obj     (s)  Factr     (s)  Factr    Obj Hits
---------- ----- -------- ------------ ------- ------ ------- ------ -----------
       256    .5       72        4,370 #######    1.0  33,605    1.4  32,095,562
       320    .6      132        7,162 #######    1.0  28,485    1.2  32,102,312
       384    .8      195       12,092 #######    1.0  24,534    1.0  32,102,864
       448    .9      204       13,130 #######    1.0  24,534    1.0  32,102,864
       512   1.0      204       13,130 #######    1.0  24,534    1.0  32,102,864
       576   1.1      204       13,130 #######    1.0  24,534    1.0  32,102,864
       640   1.3      204       13,130 #######    1.0  24,534    1.0  32,102,864
       704   1.4      204       13,130 #######    1.0  24,534    1.0  32,102,864
       768   1.5      204       13,130 #######    1.0  24,534    1.0  32,102,864
       832   1.6      204       13,130 #######    1.0  24,534    1.0  32,102,864
       896   1.8      204       13,130 #######    1.0  24,534    1.0  32,102,864
       960   1.9      204       13,130 #######    1.0  24,534    1.0  32,102,864
     1,024   2.0      204       13,130 #######    1.0  24,534    1.0  32,102,864
          -------------------------------------------------------------+
The above output shows the current setting of the shared pool is   512M (for which Size factor is 1).

+ It also shows decreasing the size of the shared pool to the 50% of its
  current value will also be equally efficient as the current value.
+ Also doubling the size of the shared pool will save extra 2300 sec in parsing.
+ Using this view a DBA has the correct picture to design Shared pool.


========> 결론 : 현재 PEMS DB의 shared_pool 사이즈를 init*.ora 파일에서 늘려준다.
shared_pool_size = 800M(????)

 

### 현재 SGA에 대한 할당 조사
select current_size from v$buffer_pool;
select pool, sum(bytes)/1024/1024 Mbytes from v$sgastat group by pool;

3. SHARED_POOL_RESERVED_SIZE에 대한 성능 조절

=> SHARED_POOL_RESERVED_SIZE는 PL/SQL 블록으로 실행된 sql문의 구문분석 정보를 저장할때 사용되는 공유 풀 영역의크기를  지정하는 파라메타이다
=> 기본값은 SHARED_POOL_SIZE의 10%이며 최대값은 SHARED_POOL_SIZE 값의 1/2 이다.
=> v$shared_pool_reserved 자료사전의 REQUEST_FAILURES의 컬럼값이 0이 아니거나 계속 증가값을 보일때
    이 파라메타 값을 크게 해준다.

select request_failures from v$shared_pool_reserved;

REQUEST_FAILURES
----------------
               0


========> 결론 :SHARED_POOL_RESERVED_SIZE 변경 없음
SHARED_POOL_RESERVED_SIZE = 25M(?????)

##################################################
#### DB Cache Size 성능 조정
##################################################

1. DB cache advice => v$db_cache_advice 동적 성능 뷰를 이용하여 db cache 사이즈를 시뮬레이션 해본다.

column size_for_estimate         format 999,999,999,999 heading 'Cache Size (m)'
column buffers_for_estimate      format 999,999,999 heading 'Buffers'
column estd_physical_read_factor format 999.90 heading 'Estd Phys|Read Factor'
column estd_physical_reads       format 999,999,999 heading 'Estd Phys| Reads'

SELECT size_for_estimate, buffers_for_estimate,estd_physical_read_factor, estd_physical_reads
  FROM V$DB_CACHE_ADVICE
 WHERE name = 'DEFAULT'
   AND block_size    = (SELECT value FROM V$PARAMETER
                         WHERE name = 'db_block_size')
   AND advice_status = 'ON';


    Size for  Size      Buffers   Read     Phys Reads     Est Phys % dbtime
P    Est (M) Factr  (thousands)  Factr    (thousands)    Read Time  for Rds
--- -------- ----- ------------ ------ -------------- ------------ --------
D         80    .1           10    2.1          9,703       21,737     28.0
D        160    .2           20    1.1          5,315        5,540      7.1
D        240    .3           30    1.1          5,029        4,485      5.8
D        320    .4           40    1.0          4,948        4,186      5.4
D        400    .5           50    1.0          4,897        4,001      5.2
D        480    .6           59    1.0          4,862        3,869      5.0
D        560    .6           69    1.0          4,829        3,750      4.8
D        640    .7           79    1.0          4,796        3,628      4.7
D        720    .8           89    1.0          4,761        3,497      4.5
D        800    .9           99    1.0          4,740        3,419      4.4
D        864   1.0          107    1.0          4,723        3,359      4.3
D        880   1.0          109    1.0          4,709        3,306      4.3
D        960   1.1          119    1.0          4,668        3,152      4.1
D      1,040   1.2          129    1.0          4,649        3,083      4.0
D      1,120   1.3          139    1.0          4,621        2,979      3.8
D      1,200   1.4          149    1.0          4,607        2,929      3.8
D      1,280   1.5          159    1.0          4,599        2,899      3.7
D      1,360   1.6          169    1.0          4,594        2,882      3.7
D      1,440   1.7          178    1.0          4,594        2,880      3.7
D      1,520   1.8          188    1.0          4,592        2,875      3.7
D      1,600   1.9          198    1.0          4,580        2,829      3.7
          -------------------------------------------------------------


========> 결론 : 현재 PEMS DB의 db_cache 사이즈를 1.6GB로 변경
db_cache_size = 1.6GB(???)

##################################################
#### Redo buffer Size 성능 조정
##################################################

Check the statistic redo buffer allocation retries in the V$SYSSTAT view.
If this value is high relative to redo blocks written, try to increase the LOG_BUFFER size.
 
Query for the same is
 
select * from v$sysstat where name like 'redo buffer allocation retries'
or
select * from v$sysstat where name like 'redo blocks written';

혹은 v$sysstat 자료사전에서 서버 프로세스가 로그 정보를 저장했던 로그버퍼의 블록 수(REDO ENTRIES)와 로그버퍼의 경합으로
인해 발생한 대기상태에서 다시 로그 버퍼공간을 할당 받았던 불록 수(redo buffer allocation entries)를 확인한다.

=>이 SQL문에 의한 실행 결과
select name,value
from v$sysstat
where name in ('redo buffer allocation retries','redo entries');


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo entries                                                       23465374
redo buffer allocation retries                                           91=>0일 수록 좋은것

========> 결론 : 현재 quality DB의 log_buffer 사이즈를 14MB * 2 이상으로 init*.ora 파일에서 늘려준다.
log_buffer = 30M  
       
##################################################
#### java_pool Size 성능 조정
##################################################

** Java Pool advice => v$java_pool_advice

select JAVA_POOL_SIZE_FOR_ESTIMATE,JAVA_POOL_SIZE_FACTOR,ESTD_LC_LOAD_TIME
from v$java_pool_advice

JAVA_POOL_SIZE_FOR_ESTIMATE JAVA_POOL_SIZE_FACTOR ESTD_LC_LOAD_TIME
--------------------------- --------------------- -----------------
                          4                     1              9493
                          8                     2              9493

========> 결론 : 현재 PEMS DB의 java_pool_size 사이즈를 8MB 이상으로 init*.ora 파일에서 늘려준다.
java_pool_size=128M(20971520)

##################################################
#### Redo-log file  Size 성능 조정
##################################################
FAST_START_MTTR_TARGET='숫자값'으로 설정한다(V$MTTR_TARGET_ADVICE)
alter system set FAST_START_MTTR_TARGET=300

SQL> select ACTUAL_REDO_BLKS,TARGET_REDO_BLKS,TARGET_MTTR,ESTIMATED_MTTR,
    OPTIMAL_LOGFILE_SIZE,CKPT_BLOCK_WRITES  from v$instance_recovery;

ACTUAL_REDO_BLKS TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR OPTIMAL_LOGFILE_SIZE CKPT_BLOCK_WRITES 
---------------- ---------------- ----------- -------------- --------------------
          942            18432          71             59                     49     
388462

The recommended optimal redolog file size is 49 MB as seen from column -OPTIMAL_LOGFILE_SIZE.
This is as per the setting of "fast_start_mttr_target" = 170

각 리두로그 사이즈 600M로 조정

$@# !! SQL 튜닝 전에

SQL>alter session set timed_statistics=true;
SQL>alter session set sql_trace=true;
요거 켜주기......

### REDO LOG 파일 재배치 해야함.(물리적으로 서로 다른 디스크 경로에 변경 배치한다)

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE GROUP 1 ('/ora_log/KMSMESV1/rdo1/redo01a.log','/ora_dump/KMSMESV1/rdo2/redo01b.log') SIZE 500M


ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/ora_log/KMSMESV1/rdo1/redo02a.log','/ora_dump/KMSMESV1/rdo2/redo02b.log') SIZE 500M


ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/ora_log/KMSMESV1/rdo1/redo03a.log','/ora_dump/KMSMESV1/rdo2/redo03b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE ADD LOGFILE GROUP 4 ('/ora_log/KMSMESV1/rdo1/redo04a.log','/ora_dump/KMSMESV1/rdo2/redo04b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/ora_log/KMSMESV1/rdo1/redo05a.log','/ora_dump/KMSMESV1/rdo2/redo05b.log') SIZE 500M

ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/ora_log/KMSMESV1/rdo1/redo06a.log','/ora_dump/KMSMESV1/rdo2/redo06b.log') SIZE 500M


### ADDITIONAL 1 : Disk I/O 튜닝

select tablespace_name,file_name,phyrds, phywrts
from dba_data_files df,v$filestat fs
where df.file_id = fs.file#;

===============> 결론 : 쿼리 결과 system 테이블 스페이스와 undo table space를 분리해야함. 
1.DB SHUTDOWN

2.undo datafile 이동 ex)F:\->H:\
 =>이동 후 기존 datafile 삭제 
3.db startup(mount까지)

alter database rename file 'E:\ora_data1\KMSMESP1\UNDOTBS01.DBF' to 'H:\ora_data4\KMSMESP1\undo_data\UNDOTBS01.DBF';
alter database rename file 'E:\ora_data1\KMSMESP1\UNDOTBS02.DBF' to 'H:\ora_data4\KMSMESP1\undo_data\UNDOTBS02.DBF';
alter database rename file 'E:\ora_data1\KMSMESP1\UNDOTBS03.DBF' to 'H:\ora_data4\KMSMESP1\undo_data\UNDOTBS03.DBF';

4.DB OPEN  


##### redolog 변경 샘플

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE ADD LOGFILE GROUP 1 ('I:\ora_log\KMSMESQ1\rdo1\REDO01A.LOG', 'H:\ora_dump\KMSMESQ1\rdo2\REDO01B.LOG') SIZE 200M;

ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2 ('I:\ora_log\KMSMESQ1\rdo1\REDO02A.LOG', 'H:\ora_dump\KMSMESQ1\rdo2\REDO02B.LOG') SIZE 200M;

ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3 ('I:\ora_log\KMSMESQ1\rdo1\REDO03A.LOG', 'H:\ora_dump\KMSMESQ1\rdo2\REDO03B.LOG') SIZE 200M;