오라클 튜닝 & 유지보수 관련정리

유닉스

  cpu : sar, vmstat, mpstat, iostat

  mem: sar, vmstat

  disk : sar, iostat

  net   : netstat

 

방법:

 1. 유저로 부터  솔직한 피트백을 얻는다 “ 예전 퍼포먼스가 좋게 나온 이후로 어떤 것을 변경했는가?”

     튜닝의 범위, 순서, 목표을 설정한다.

   문제 정의 :

    튜닝 목표 설정

    문제 범위 설정(전체 인스턴스 문제인지 아니면 특별한 app인지

                       싱글유저인지  특별한 작업인지)

    문제 시간 설정

           피크 타임인지  아니면 다른 시간대인지

 

  

 

 

2. os, db, application 의 모든 통계정보를 얻는다.

 

3 . os를 체크한다. 하드웨어나 os 상의 에러가 없는지 체크한다.

     os 상의 다른것이 리소스를 점유하지 않는지 확인

     os 체크 방법

         시스템 전체에서 유저들이 차지하고 있는 cpu 사용률 과 커널이 점유하는 cpu 사용률을 체크

         각 cpu 당  유저들이 차지하고 있는 cpu 사용률 과 커널이 점유하는 cpu 사용률을 체크

          시스템에서 메모리 페이징이나 수와핑이 절대로 일어나지 않게 메모리 증설

          장비들 간의 네크워크 속도를 체크

         큐가 차 있거나 reponse가 늦은 디스크를 찾아낸다.

         하드웨어 에러가 하나도 없는 것을 확인한다

 

        CPU 사용률, DISK, MEMORY 사용률, NETWORK 성능

4. oracle top 10 체크

     1 매번 커넥션 -미들웨어가 지속적인  커넥션을 못가져감

       2 잘못된 커져오 shared pool 사용

          커져가 없으면 계속 파싱됨. 그리고 바인드 변수가 없어도 마찬가지.

      3. db의 i/o 구성도를 확인

      4. 너무 작지않은 크기의 리두로그 5개 정도.

      5. 버퍼캐시에서 시리얼한 데이타 블럭은 free lists, free list groups, initrans 부족 초래.

      6  full table scan 을 피하게 모델링 한다.

      7  disk sort 를 피한다.

     8 많은 양의 recursive sql( sys가 만든)것은 공간 할당하는 게 많고 이런것들은 성능 저하초래

        sql이나 plsql 이 발생시킨 것들은 실행자 가 다른 유저 id로 나타나는데 이런것 들은 상관 없음

    9 완전하지 않게 개발된 db나 잘못된 통계가 성능저하를 초래 <-- 통계 주기적 갱신

    10 ...

     

 

 

 

5. 퍼포먼스에 문제를 일으키는 어떤 실마리를 찾았다고 생각하면 개략적인 모델을 작성한다. (이 항목은 엔지니어 경험을 계속적으로  append 해 나감)

  ---싱글 유저일때 response time/ batch run time 이 제대로 나오지 않으면 app(sql 스크립트)가 잘못 디자인 된 것이다.

  이럴땐 app의 internal 통계를 얻는다 (sql trace, sql plan)

   계발자와 함께 데이터, 인덱스, sql, 모델링, 등을 검사한다.

 ---모든 cpu가 잘 활용되는지 조사

    커널 사용율이 40% 이상이면 db인전의 os를 조사한다. ( 네트워크 transfer, paging, process thrashing)

       그렇지 않으면 유저당 cpu 사용율을 조사한다.

       그 외의 다른 프로세스가 cpu을 점유하고 있는지 조사

    오라클 9i 에서는 cpu 통계를 제공 (v$sql

---- 모든 cpu가 잘 활용되지 않고 있다면  시리얼한 작업을 하고 있는지 확인

     wait_events 확인 하면 알수 있음  <--큰 것 한나 잡아내서 sql 스크립트 정정 및 db 파라미터 수정

    시리얼 한게 없다면 문제는 db밖의 것이다.

 

6. 모델을 검토해서 시스템에대해서 추천할수 있는  변경사항들과 해서는 안될 사항들을 제안하고

   한번에 하나의 항목을 변경해서 상태를 모니터링 해야함

7 기대한 성능이 나오면 그것으로 모든것을 완료하고 그렇지 않으면 다른 bottleneck을 찿아본다.

8 . 5 6 7을 반복한다.

 

=================================================================

 

비상사태시 대처방안

1. 유저로 부터  솔직한 피트백을 얻는다 “ 예전 퍼포먼스가 좋게 나온 이후로 어떤 것을 변경했는가?”

2. CPU 사용률, DISK, MEMORY 사용률, NETWORK 성능

3. 많은 CPU 사용율을 가지는 세션을 조사

4  Many buffer gets 를 하는 세션이나 sql 문을 조사 V$SESSTAT, V$SQL 

5. 실행 계획 변경은 다른 서브 실행 계획들에 영향을 준다.

6 잘못된 파라미터 변경

7. 코드 변경에 따른 알고리즘적 이슈 확인

 

튜닝 대상

 application

 oracle

system (os & hardware)

 

 

튜닝 타입

1.    사전 모니터링

   ( 주기적으로 한다. – 퍼포먼스 플래닝의 일부분으로 해석)

     퍼포먼스 통계와 시스템 리소스 사용통계등을 수집해서 변화가 있었는지 확인)

    모니터링 시에는 configure 변경같은 것을 될수 있으면 하지 않는다. (심각한 문제가 발생된다면 고려해 볼일이고 그렇지 않으면 체계적으로 튜닝 순서에 맞게 계획을 세운후 한다.)

정상적으로 동작하는 시스템에 아주 조그마한 튜닝 작업도 때때로 심각한 성능저하를 초래할 수 있다는 것을 명심해라.)

 

2.    bottleneck 제거

   보통 튜닝은 문제점을 고치는 과정이지만 최종단계가 아님. 이것은 시스템이 설계되고(design) , coding, production, maintenance 과정의  일부분이어야 한다.

 

 

 

 

----------------------------------------------------------

 

cpu부터 네트워크 까지는 “host 검사” 이다

cpu

cpu usage

idle 상태가 많으면 -> i/o 일 것이다. wail i/o--> idle cpu

사용률이 높으면 -> cpu가 효율적으로 사용되는지 확인 (

1개의 cpu 사용률이 높으면 ->높은 cpu 사용률을 가진 몇개의 프로그램 때 문은 아닌가?

cpu 사용률이 균일하게 분산되었는게

  

몇개의 오라클 프로셍서가 많은 cpu를 차지한다면 sql_trace, tkprof를 돌린다.

 (단 select  문이 캐쉬 데이터를 많이 읽을경우 cpu가 높게 올라가고 이런문장은 튜닝 할 필요가 없다)

v$sysstat -> 모든 세션에 대한 cpu usage

  여기서 “cpu used by  this session” 은 모든 세션에서 사용된 cpu 총량을 나타냄

v$sesstat -> 여기서 “cpu used by  this session” 은 각 세션별로  사용된 cpu 총량을 나타냄

 

 

i/o

disk queue lengths가 2보다 클때 혹은 disk service time 이 20 ~ 30 ms 이상일때

os 툴 --> sar  -d ,  iostat

어떤 프로세스가 어떤 disk (file)를 잡고 있는지 –아마 오라클 파일이 아닐수도 있다

오라클의  wait event 인 v$system_event 를 살펴서 i/o와 관련된 이벤트 확인

 관련 이벤트 :

 DB FILE sequential read ,  db file scattered read, db file single write, db file parallel write

 

statspack의  i/o 섹션의 ( cross reference) host i/o system data 부분은 hot 데이터 파일과 테이블 스페이스를 말해준다.

그리고 (tuning 22-9 페이지)

i/o 시스템을 재 구성하기 전에 load를 줄일수 있으면 줄여라.

physical read를 일으키는 쿼리문을 v$sqlarea에서 찾을 수 있다.

그리고 statspack의 “sql ordered by physical read section”은  그걸 가리킴

 

 

네트웨크

 round trip ping 와 collisons, --> 속도를 봐라.

 

---- 이제부터는 오라클 을 검사--------

stistics_level = basic, typical, all  ( 세부 내용tune- 22-10)

db_cache_advice, timed_statistics 이런 파라미터는 statisitcs_level 값을 덥어쓴다.

v$statistics_level에 각 항목별로 enable, disable되어있는지 확인가능

alter system 은 모든 항목 가능

alter session은 다음 항목가능

  timed statistics

  timed operating system statistics

 

 

오라클 검사는 wait 이벤트와 system staitsitc 를 본다.

wait 이벤트  - latch contention, buffer contention.. 등등 <--이것들은 문제의증상이지 문제의원인은 아님

  wait event 에서 많은 횟수를 갖는 것을 찾아내는것 보다 total wait 시간을 많이 잡아먹는 것을 찾아 내는 게 중요하다.

어디서 시간이 소비되는지 알기위새서 wait event를 사용할 때는

 1. v$system_event 검사 -> wait time 로 소트함

      퍼센트로 다시 계산 (이때 tolal에서 제외하는 항목은  null event, sql*net message from client, sql*net message to client, sql*net more data) 한 top 5개의 항목을 골라낸다.

     만일 몇개 항목이 같은 퍼세티지를 나타내면 똑같은 리소스를 참조하는 이벤트일수도 있음 을 기억 (예 i/o)

   2, 그러면 waits (기다린 횟수) 평균 wait time을 봐라. 보통 평균 wait time를 보면 알수 있다.

 3. 자주 쓰이는 일반적인 wait event –p22-19 테이블 22-1 참조

 4. 관련된 데이터를 조사하고 결과를 결정한다.

 --추가적인 wait event 와 관련이 없는 것들이지만 중요한것

  redo log space requests <--로그 파일 공간을 요구한 횟수인데  redo 버퍼가 아니다.

   이것은 체크포인트, dbwr, arch, 와 관련된 것이지 lgwr이랑 관련이 없다.

 read consistency

1.              많은 작은 트랜잭션이 있고 same table에 백그라운드로 롱런 쿼리가 실생되다가 roll back될때

    1-1 consistent changes를 봐라 <-db 블럭이 롤백된 횟수

              read block consistent 를 유지하기위해  db 블럭이 롤백된 횟수-> 많은 리소스를 잡아먹는다.

      1-2 consistent gets  consistent mode에서 로직컬 read 의 횟수를 나타냄

 

 

2.              적은 수의 아주 큰 rollback segment일 경우

3.              롤백 세그먼트가 충분하지 않을 경우 (header contention이 일어남)

          v$rollstat 의 gets와 waits를 확인.  wait/get 가 작아야 함

           v$waitstat의 undo header class의  wait 버퍼를 확인.

 

table fetch by continued row

      1% 미만의 chained row는 괜찮음

           update는 느려지고, 쿼리도 느려짐

   chained row를 알기위해서는 analyze  문에 list change rows 명령어를 쓴다.

                       코스트 베이스 옵티마이저와 관련이 없는 항목(chained row, 및 freelists)는 analyzer를 쓴다.

 

 

parse-related statisics

1.       parse time cpu / cpu used by this session  0에 가까우면 좋다.

2.       parse time cpu / parse time elapsed (경과된시간) 1에 가까우면 좋다.

 parse time elapsed - parse time cpu는 리소스를 기다린 시간.

 

system staitsitc

기본 인스턴스 통계- sysstat  -> table scan, redo wastage,...

statspack-> 퍼포먼스 관련 통계생성 , cpu, i/o, wait event , 자원을 많이 사용하는 sql plan등등

 

dbms_stats  <- 옵티마이즈를 위한 통계생성

dbms_stats 는 스키마(테이블 , 인덱스 등등) 통계생성에 사용되지만

하지만 시스템 통계( cpu, i/o) 도 생성 가능함 -> dbms_stats.gather_system_stats

 

 

 system statisics

v$sysstat  v$filestat v$rollstat  v$enqueue_stat  v$latch

 세그먼트레벨 통계 (9.2. 이후)

   v$segstat_name  v$segstat  v$segment_statistics

 

%% 일단 –통계를 해석하는 방법 (sysstat)

load 관련 통계 데이터 항목은 : redo size, session logical reads, db block changes, physical reads,. physical writes, parse count (total), parse count (hard) , user calls.

 이 데이터들을 시간당 , transaction 당 으로 변경하면 좋다.

 statspack의 “load profile section” 위 항목들을 시간당, transaction 당 으로 노멀라이즈 된것을 나타냄,

   지침 --- 초당 100 이상의 hard parse rate는 아주 높은 시스템 중량을 준다.

               높은 hard parse rate은 shared pool이나library cache에 latch contention을 야기 -> top 5 wait 이벤트에 latch free 의  wait이벤트가 있는지 확인

    만일 그렇다면 statspack의  latch section을 검사함.

   높은 soft parse rate-> 초당 300 안에 존재.

    sql 문장은 각각의 session에서 soft parse가 되게하고 많이 실행되게 한다.

 

wait event를 사용할 때는 퍼프 튜닝 660 페이지 page 부터 몇가지 이벤트에 대한 솔루션

 

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

13장

 auto segment에서는 pctfree만 필요, pctused 는 필요없다.

 그리고 initrans를 높게 잡아준다. 많이 엑세스되고 update 되는 테이블

shared server--> sort area size 를 설정하고

 {(total mem – sga-os)/ cpu개수} –(5~10M)

토탈 (512 -100(sga)-50(os) )/4 (cpu) -10 (오버헤드) = 약 80메가 정도

dedicate --> pga aggregate target 설정

 

-------------------------------------------------------------

-------------------------------------------------------------

14장

(shared_pool, large_pool, java_pool,  buffer cache, log buffer,) -sga,  pga

 

 

V$sga_dynamic_components

v$sga_dynamic_free_memory

v$sga_max_size

 

 

memory

1.       reducing pageing

2.       fit the sga in main memory

3.       lock_sga parameter <-플렛폼에 따라 다르다

 

 buffer cache

-------------------------------------------------------------

-------------------------------------------------------------

    v$db_cache_advice

     buffer hit ratio

    

      db_cache_advice 파라미터를 on 하면 cpu 가 조금 올라가고 각각의 buffer에 대하여 100 바이트의 메모리가 필요 (디폴트 항목만 해당되는지  검사 필요)

 

select  size_for_estimate, .... estd_physical_read_factor... from v$db_cache_advice

 where name=’default’

      and block_size=

      and advice_status=’on’

 

 v$sysstat 항목의 

physical reads  : 토탈 디스크에 있는 블럭을 읽어서 캐쉬(data buffer cache)에 저장한것과 바로 사용중인 메모리(shared poo)로 읽어들인 경우를 모두 합한것이다. 

 

 

 

    A+B

 physical reads direct : lob를 제외한 바로 shared pool로 읽어들인 블럭수

physical reads direct ( lob)

session logical reads : 토탈 메모리 (캐쉬)와 디스크에서 블럭을 읽은 회수

총 읽은 횟수  = DB blcok gets + consistent gets

 로직컬 리드라는 것은  캐쉬나 디스크에서 모두 sql shared pool 영역으로 읽어들인 것을 말함

 

 hit ratio = 1- ((physical reads – physical read direct – physical read direct (lob)) / (session

              logical read - physical read direct – physical read direct (lob))

 히트율 해석

  메모리 사이즈만 늘려줘서는 안된다.

  같은 테이블이나 같은 인덱스를 반복적으로 scan 할 때 hit 율이 낮아진다.

    따라서  많은 buffer gets를 발생시키는 sql문을 검사하고 한번의 scan으로 모든 결과가 나오도록 sql문을 짠다.

 아니면 클라이언트나 미들티어에 자주 sacn 하는 데이터는 caching 한다.

 풀테이블 스캔시 블럭들은 lru 리스트의 머리부분에 놓여지지 않기 때문에 indexed lookup 이나 small 테이블 스캔보다 쉽게 age out 됨

 그리고 oltp 환경에서 많은 용량의 테이블의 각 row들은 0, 1번 엑세스 되기 때문에

이런것들이 메모리에 keep되어 있을 수도 있다.

 그리고 풀 테이블 스캔을 하거나 버퍼 캐쉬를 이용하지 않은 app라면 cache 증설은 효과가 없다. 

 

db_nK_cache_size  도 있다.

 

버버풀

large 세그먼트의 random access -> buffer cache 사이즈에 비해서 많이 큰

 바운드 되지안은 large index  스캔으로 랜덤 엑세스 될때  다른 세그먼트의 캐쉬된 것들을 발리 age out 시킨다.(아주 자주 엑세스되는 것은 영향받지 않지만) 이것은 안좋다.

문제는 age out 될정도로 자주 엑세스되지 않는것 해결법

1.       그 object가 인덱스 이면 그 인덱스가 selective 인지 확인하고 selective가 아니면 다른 selective 인것을 사용하도록 sql문을 수정

2.       문장 수정이 되었다면 원인 제공자인 large segment를 recycle 캐쉬로 바꾼다.

3.       그리고 쉽게 age out 되는 작은 세그먼트를 keep 풀로 옮긴다.

 

버퍼 풀 hit 율

   select  size_form setimate,.... from v$db_cache_advice

where name =’keep’

 and block_size=8192

 and advice_status=’on’

 

 위 문장으로 조정하고

hit율은  v$buffer_pool_statistics;를 쿼리해서  1- (physical_reads /( db_block_gets + consistent_gets))

 그럼 풀안에 많은 block을 위치시키고 있는 세그먼트는 어떤것이 있는가?

   v$bh – sga에 있는 object id를 보여줌

 

   select  o.object_name, count (1) number_of_block from dba_objects o, v$bh bh

where o.object_id=bh.obj

 and o.owner !=’sys’

group by o.object_name

order by count(1);

 

 이렇게 하거나 버퍼 풀에서

 object id를 알아내고

  select * from  dba_objects

 where object_name =upper(‘aaa’)

 

buffer 에 점유량을 알아내고

 select  count (*) from v$bh

where objd= 123344

 

buffer_pool 량을 나타내서 계산하면 된다.

buffer_pool 량

 select name, block_size, sum(buffers) from v$buffer_pool

group by name, block_size

having sum( buffers) > 0;

 따라서 점유량/ 토탈 량 하면 %가 나온다.

 이 쿼리는 파티션된 것은 안된다. 파티션 된것은 다시 한다.

 

keep : db_keep_cache_size 파라미터가 있다,.   create table .... buffer_pool keep

 keep: 테이블이 작고 자주 엑세스 되는것, 크기가 잘 늘어 나지 않는것

db_recycle_cache_size 

 거의 엑세스 되지 않는것

 

테이블 만들때 nocache option도 있다.

 

서로 다른 시간의 snap shot (아까 위의 쿼리를 이용)를 찍어 hit ratio를 할 수 있다.  

buffer hit ratio 가 100% 이면 optimal 하지 않다.

 keep을 줄여라.

recycle 풀  거의 사용되지 않는 테이블 같은거

 app가 select 하기 위해서 읽고 다시 update 하기 위해 읽으니까

함부로 사용하지 않기.

 recycle_pool_size

 

-----------------------------------------------------------------

----------------------------------------------------------------

shared pool

1.       같은 sql 문은  shared pool에 저장되어 다시 parse 되지 않는다.

    이것은 cpu 사용률을 감소시킨다.

2.       래치를 감소키키면 아주 scalability 됨

3.       shared pool 요청 감소

4.       i/o를 감소시키낟.

 

sql 재사용은 shared pool 과 library cache latch를 감소시킨다.

 

shared server, parallel query, rman,  -> large pool

아주 가끔 java, plsql, sql cursor를 위한 --> reserved pool

 

라이브러리 캐쉬에서  soft parse 혹은 library hit  --> 같은 sql 문장을 재사용

---공간 비워둠 페이지 page sql 재사용 관련 수칙

     .....똑같은 sql을 쉐어 하지 않는 예

......

....

 

-------------

OLTP 시 수칙

shared cursors 

 1. 바인드 변수 사용

   select * from emp where dept_id= 10;

   select * from emp where dept_id= 20;

è       select  * from emp where dept_id= :dept_id  <--cursor_sharing 파라미터 세팅

 

2. 다이나믹 쿼리를 피한다.

3. 개발자 수칙

    a) naming 수칙, space 수칙 사용 -- sql 문장 및 pl/sql블럭을 위해서

    b) stored procedure 사용

large oltp시 CONNECTION manager (MIDDLE tier 사용)

 

테이블 네임지정시 hr.emp 이렇게 사용( public synonm을 사용하는 것 보다).  아니면 단일 유저 아이디 사용.

 각각의 id를 가지고 synonym를 사용할 경우 stored pl/sql 패키지를 사용하면 더 효ㅇ율적으로 확장할 수 있다. 많은 수의 유저를 극복함.

 왜냐하면  패키지는 소유자를 기준으로 실행됨, 호출자를 기준으로 실행되는게 아니고,

high peak time 시 avoid ddl

 

자주 업데이트되는 시퀀스는 cache 를 할당한다.( dictionary lookup를 피함)

 시퀀스 생성할때 cache 키워드 필요

 

커서 엑세스와 관리

    jdcb 3.0  statement cache

     문자의 파싱과 생성을 막고, 반복적인 sursor 생성을 방지함

 

shared pool 사이징

v$librarycache  의  reloads 카럼: sql 문이 cache에서 age out되어 다시 리로딩(reparse) 된 회수 : 잘 튜닝된 시스템에서는 거의 0에 가깝다.

 invalidation은 캐쉬된 sql 문이 다른 이유로 (ddl 문장 수행) 쓸모없게 됨

  --> 0에 가까워야 함.

 

select *from v$sgastat

where name=’free memory’

 and pool=’shared pool’ ;

메모리가 남아있는지 본다. 남아 있으면  늘려도 소용없다.

V$shared_pool_advice 

   estd_lc_time_saved  ( seconds)

 

v$livrary_cache_memory 확인

 

--------------------------------------------------------------

-------------------------------------------------------------

large pool

shared 서버에서 세션정보 (sort area 및 private sql)는 large pool이 있다면 거기 저장되고 없다면 shared pool에 저장됨 ( uga는 shared pool 안에 존재함. 하지만 large pool은 sga 안에 존재하지만 shared pool 밖임.

따라서 uga는 large pool 안에 존재할 수도 있고 shared pool 안에 존재 할 수도 있다.

dedicate 서버에서는 pga에 저장됨. pga는 sga와 별개이다.

 즉 uga는 pga 안에 존재함

 

shared server에서

large_pool_size  session 300K  그러므로 100 명의 동시 접속자는 30M

 

shared server에서 sort_area_size와 sort_retained_size를 같게한다. -> sort 된 것을 large pool에 저장 시킨다.

dedicate 서버에서는 소용없다.

 

select sum(value) || ‘bytes’  “total memory for all session” from v$sesstat, v$statname

where name=’session uga memory’

 and v$sesstat.statistics#=v$statname.statistic#

 

혹은 name=’session uga memory max’

 

dedicated 모드일경우 위의 session memroy가 유저 프로세서 메모리에 저장되나, shared 서버 모드일 경우 shared pool이나 large pool에 저장됨

 

----------------------------------------------------

-----------------------------------------------------

cursor

 

cursor_space_for_time= false (default) app가 cursor이 open된것과 상관없이 deallocate

할수 있다. <--

cursor_space_for_time= true <- app가 cursor를 닫을 때만 deallocate된다. 메모리 많이 소모 하니까 parse 할 메모리가 없으면 error이 남

 

session_cached_cursors =

 

 

parse count (total) – session cursor cache hit = real number of parse

parse count (hard) –hard parse

 

soft parse 가 session cursor cache hit과 어떻게 다른지 확인

 

shared_pool_reserved_size= 5~ 10% shared_pool

 v$sgastat에서  request_failures가 0 이상이면 reserved pool를 증가

 free memory가 reserve pool size (min) 의 50%보다 크면 pool를 줄려라.

 

dbms_shared_pool.keep

 

cursor_sharing = similar를 가급적 하지 않고 exact를 하고

                바인드 변수 사용시 similar –기존 plan 변경않음

                force –기존 플랜 변경가능함 – 차선의 plan의 위험성이 없을때 사용

                 dds 환경에서는 force 사용않음

 

----------------------------------------------------------------

----------------------------------------------------------------

java_pool   자바 사용여부에 따라서

-----------------

redo log buffer

 

select name, value from V$sysstat

where name=’ redo buffer allocation retries’ ;

 값이 0에 가까워야 함 – 이 값은 누적이므로 인터벌을 두고 한다.

이 값이 계속적으로 증가하면 wait이 있다는것이다.

이 값은 buffer에 로그 쓰기가 지연되거나 늦어질때 발생하거나 아니면 log switch 이벤트가 나올때 발생된다.

 

log 버퍼 사이즈는 log_buffer 파라미터를 사용함

그리고 wait 이벤트를 조산한다.

system_event의 log buffer space 항목을 보면 아주조금 알수 있다.

만일 log buffer 사이즈가 큰데도 이 이벤트가 일어나면 리두로그가 들어있는 디스크에  ii/o가 많다는것을 알수 있다.

-------------------------------------------------

----------------------------------------------------------------

pga  -shared server mode일 경우

       세션정보와 cursor 정보가 shared pool에 저장됨 ( large pool 항목 참조) stack 만 빼고

 

pga_aggregate_target은 dedicated server일때만 의미있음

sort_area_size, hash_area_size, bitmap_area_size, create_bitmap_area_size

관련해서 통합된 것이다.

초기로 적정한 값은 : oltp -> total * 0.8* 20%  sqa의 20%정도

                    dss -> total *0.8 * 50%   sqa의 50~ 70 정도

 

select * from v$pgastat

여기는 자료를 참조해라. 14-53 퍼프 튜닝

global memory bound는 반드시 1MB보다 커야 한다.

 

tunnig pga---v$pga_target_advice  , v$pga_target_vice_histogram

 

-----------------------------------------------------------------------------------------------------------------------------------------------

sort_area_size  --

shared server 나 auto pga 를 사용하지 않는 구성에서만 사용

1)      select name, value from v$sysstat

    where name in (‘sort (memory)’ ,’ sorts (disk)’);

 

2)      select hash_valus, sql_text, sorts ( sorting 회수), executions (실행 회수)

 

large_pool 참조

 

sort_area_retained_size 는 sorting이끝나고 그 할당된 영역을 해제하는 제한선을 절정한다.

dedicated 서버에서는 프리메모리는 다시 세션으로 돌려진다.

그러나 shared 서버에서는 (보통 large sort를 하지 않는다고 가정)

따라서 dedicate connecttion과 shared 커넥션을 보통 혼합한다.

 

shared 서버에서 소팅결과가 sort_area_size보다 크면 저장하는데 추가적인 i/o를 발생시키는 면도 있다.

실제 sorting된 메모리와 sort_area_retained_size중 작은것 의 양만큼 메모리를 할당해서 저장함. 따라서 메모리를 절약하는 효과가 있다.

 

3 만일 테이블이 1,2,3...순서대로 (ascending) 하게 저장되었다면

인덱스를 만들 때 (인덱스는 기본적으로 sorting 함) nosort 옵션을 꼭 주라.

그리고 nosort 옵션은 파라렐을 무효화 시킴.

 

input 데이터가 이미 sort 되어 있다면 group by 시에 nosort옵션을 꼭 해주라.

 

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

i/o

  스토리지 관련 팩터

i/o rate (= read + write)

i/o concurrency <-동시에 여러 프로세스(cpu가 아닌것 같음)가 i/o 리퀘스트를 가질수 있는가?

i/o size

i/o

  다음은 conurrency관련 팩터

               

 

 

구성시 고려사항

디스크 개수

레디트 레벨

stripe depth (size), width (디스크 개수)

raw filesystem& filesystem

async i/o 사용여부

 

현재 시스템 사양을 파악 항목 {

i/o sizw

raw system 대 file system

read wirte performance

시퀀셜 access 대 랜덤 access

싱크 i/o 대 어싱크

   

i/o 통계 확인

filesystemio_options 싱크 어싱크 관리 (9.2. 부터)

.....

....페이지 531 page

나머지...

 

high data rate :  (보통 시퀀셜을 의미)low concurrency large block  <- full scan 같은것들의 조합

high i/o rate : (보통 random을 위미) high concurrency a few block <- oltp 환경

   스트라이프 사이즈를  n* db_block_size 하라.

    = db_fiel_multilblock_read_count

 

db_fie_multiblock_read_count =default 8  <-max values는 os 에 따라

oltp 에서는  위값 4~ 16

dds엔서는 os에서 가능한 최고 값을 설정.

 

log concurrency oltp나 dds에서는  spripe 사이즈는 =n* db_block_size  <

 db_file_multiblock_read_count 보다 작게 함.

 

redo log 디스크에는 raid 5는 피해라.

아카이브 로그는 다른 다스크로 해라.

 

db_block_size: row 사이즈가 작고 random 엑세스일때  블럭사이즈 작게하고

              나머지 모든 경우 크게하거나 적절하게 한다.

 

 

 

DISK_ASYNC_IO <--OPS 나 RAC 시의 RAW디바이스에 적용

                    OS가 지원할 때 이항목을 TRUE로 한다

                  HP-UX는 ASYNC 드라이버를 만들고, 권한 주고, 이항목 TRUE

DBWR_IO_SLAVES--> 위 항목이 지원안될때 이값을 0 이상으로 한다.

 

 

만일 버퍼 CACHE 크기가 작거가 너무 크더라도 IO 성능이 떨어질 수 있다.

  너무 버퍼가 크면은 또 그것을 Free 할 때에도 많은 시간이 걸릴 수 있다.

따라서 그럴때는 dbwr 프로세서 수를 늘리거나, io slaves를 늘린다.

db_writer_processes  <--멀티플cpu에서 메모리가 충분하다면 .추천..

              적어도 8개의 cpu당 한개의 dbwr 프로세서 식으로 구성

dbwr_io_slaves

멀티플 dbwr를 구성하면  io slave를 사용하지 않는다.

io slave는 dbw0만 있을 때 사용함.

 

구성순서는

async io를 먼저하고 그래도 바틀렉이 걸리면 dbwr 프로세서 수를 늘린다.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

os 와 file 시스템 버퍼와 성능

temporary tablespace의 버퍼 사용량.

nocache lob의 데이터

parallel query slaves reading data  이런것은 파일 시스템 버퍼를 사용하는게 더 성능이 좋다.

 

async i/o 

  filesystemio_options= asynch

 

오라클 background 프로세서의 priority를 바꾸지 말라.

오라클 프로세서를 cpu와 짝짓지 말라.

unix에서 60~ 75 %의 app (user)  cpu사용율

         25 ~ 40 %의  os cpu 사용율을 확인해라

         만일 50% 사용율일 경우 원인 조사

           paging, swipping, 아니면 너무 많은 프로세스, 너무 많은 시스템 콜 이 원인일 수있다,.

 

CPU 사용률  UNIX--- sar –u

memory             sar,  vmstat

 

-----------------------------

-----------------------------------------------

 

recover

fast_start_mttr_target 은 enterprise 급에서만 지원.

fast_start_mttr_target  0  이것을 결정하면 다른것들은 제거 해라.

fast_start_io_target   0

log_checkpoint_timeout  3600

log_checkpoing_interval  0

recovery_parallelism

log_parallelism

 

 

 

-----------------------------------------------

-----------------------------------------------

undo & temp tablespace

 

temp tablespace 에서

   pctincrease =0 , initail 과 next를 같게함. <- sort_area_size 를 참고로 적용

전용 temp tablespace를 만든다. > tablespace 를 temporary하게 만드는 것 보다.

create temporary tablespace ... 명령어 사용.

[출처] 오라클 튜닝 & 유지보수 관련정리|작성자 훈이에요