에디터맞추기가 질문보다 힘드네요 ..헉헉 ㅠ,.ㅠ

아래 종훈님 글에 답변(제가 리플을 많이달면 진짜답을 보기힘들까봐^^) --> 저두 답이 기대되요..세팅은 하지만 고수님들처럼 이론을 바탕으로한 완벽한..모 그렇게 못하고..그냥 감(?)으로 하거든요. 근데 마침위에 문제가 생겨서 셋팅과 튜닝 두토끼를 잡으려고요 ^^..ERP와는 멀구요 거리는 가깝네요 ㅋㅋ

높이 평가 해주셔서 감사합니다 만.

서버쪽 세팅은 잘모르겠내요^^

해보질 않아서요..

대형프로젝트만 하다보니 DBA가 따로있구 접근권한도 없었다는 ㅠㅠ

답변이 기대되내요 저두, 근데 ERP 하시나요? 전주로 DW만 하다보니 혹시 같은곳 아니실까요? 저는 광화문 입니다. (보안상 사이트이름은 XX보험)

일단 서버1의 경우 그냥 디폴트 설정을 쓴 것 같네요.

오라클 엔지니어가 설치한 DB가 아닌 듯 싶습니다.

(보통 최초 설치시 Physical Memory나 사용형태 (OLTP 인지 DW)에 따라서 파라미터나 메모리를 계산해서 배정하기 마련인데...)

Server1의 db_cache_size 25M 는 너무 작죠. 당연히 Physical I/O를 유발하고 SQL도 빨라졌다 느려졌다 할겁니다.

큰 Block I/O를 유발하는 대형 SQL이 한번 훑고 지나가면 기존에 캐쉬된 블록들이 전부 쫓겨날 테니... 그 이후에 들어오는 SQL은 또 Physical I/O를 해야하거든요. 그러니 속도가 들쭉 날쭉하게 보일 수 있습니다.

9i의 경우 Workarea_sizy_policy=auto 이고 Dedicated Mode를 사용하실 경우 sort_area_size, hash_area_size등은 사용하지 않습니다. Pga_aggregate_target 에서 지정된 메모리에서 자동으로 할당을 하게 됩니다.

그리고 옵티마이져에 영향을 주는 파라미터 중에 하나가

db_file_multiblock_read_count 인데 128이면... OLTP에서는 볼 수 없을 정도로 큰 값입니다. 8이나 16이면 좋습니다.

128이면 옵티마이져가 index보다 Table Full Scan을 선호하게 됩니다. 16일 때 index를 타다가 128로 변경하는 순간 Full Scan 선호 플랜으로 변할 수 있습니다.

128 정도의 수치는 야간에 대량의 배치를 돌릴 경우나 선호되는 수치입니다. 이 경우 대용량 배치를 돌릴 때만

alter session set db_file_multiblock_read_count=128 로 하시고 평상시에는 8 정도의 값으로 유지시켜주는게 낫습니다.

정렬시 Temp (disk)를 사용한 이유도 pga_aggregate_target을 너무 작게 설정했기 때문입니다.

shared_pool_size 는 1G까지 필요하지는 않을 듯 싶네요. BIND변수 사용 여부를 체크해보시고... v$sgastat 뷰의 Free Memory를 참고하여 줄여주세요.

마지막으로 질문 내용 중에 db_cache_size와 정렬의 상관 관계를 언급하셨는데요. 전혀 상관관계 없습니다.

Db_cache_size는 Buffer Cache를 의미하는 것으로 SGA에 구성되는 영역이고...

sort_area_size같은 것들은 전용 프로세스들이 각각 개별적으로 할당하는 PGA영역입니다. 정렬은 PGA영역에서 일어납니다. 그 메모리가 부족할 경우 Temp TS영역을 사용하는 것이구요.

9i에는 Shared pool, buffer cache, pga_aggregate_target에 대한 Advice 기능이 있으므로 실재로 늘려보지 않고도 얼마나 효과를 볼지 예측할 수가 있습니다. 메뉴얼을 보시고 참고 바랍니다.

주현님 말씀대로라면 질문2와 같은 경우는 왜 발생하는거지요? pga_aggregate_target 값을 증가시켰더니 disk sort와 physical reads는 줄었는데 db block gets 값이 증가하면서 응답속도가 느려지는..

그리고 보시다시피 4만건 정도의 데이타를 일반 sorting 할 경우 전혀 생기지 않는 문제가 connect by 절을 쓰면서 발생하는 특별한 이유라도 있나요?  공부시켜주세요....please~^^

pga_aggregate_target을 늘려줘서 느려진게 아니라... 다른 원인에 의해서 실행계획이 변화했다거나...

index의 구성이 달라졌다거나...

물리적 특성이 달라졌을 수 있습니다. 테이블에 Row Migration 현상이 심할 수도 있고 클러스터링 팩터도 상이할 수가 있습니다. PCTFREE , PCTUSED 설정도 다를 수 있구요.

Logical I/O가 증가한 것은 다른 원인으로 보여집니다.

그리고 connect by 구문의 문제가 아니라... 뭔가 대량의 정렬을 하면 order by, group by, distinct, union, minus 등의 쿼리는 모두 느려질 수 있습니다.

즐겁게 보았습니다...^^

시롱이

http://freeboard.wawa.to

sironge@empal.com

주현님이 잘 설명해 주신거 같네요... 더이상...뭐 달리 설명드릴 말씀이....  ^^;;

한가지 덧 붙인다면... 질문 2의 경우 multi block size 의 변화에 따라서 옵티미아이져 실행 계획은 변할수 있습니다.

쿼리의 성능이 느려졌다는 건 pga 영역의 확장과는 관련이 없고 실행계획이 전 multi block 사이즈 일때와는 달라 졌을 겁니다.

즉 간결한 트랜재션의 oltp 성 db 는 가능한 인덱스를 태워서 빨리 트랜잭션이 처리 되는 것이 유리 한 만큼 블럭 사이즈를 적게 가져가시는 것이 유리 하며, 배치성의 큰 규모의 테이블이 핸들링 되는 경우라면 블럭사이즈를 크게 가져 가는 것이 유리 합니다. 그만큼 multi block 을 스캔하는 비율이 더 적을 테니까요...

즉 multi block 사이즈의 차이 즉, 적을때와 클때의 차이는 옵티마이져가 가능한 인덱스를 태울 것이냐 full scan 을 할것이냐의 차이겠죠...

하지만 alter system set db_file_multi_read_count  의 사용시 기존 실행계획에 따라 판단을 잘 하셔서 사용하는 것이 바람직합니다.

무조건 배치성 작업이라고 해서 위 옵션이 성능을 향상 시키지는 않습니다.

여기에 덧붙여서 아래의 파라키터 값은 기본값이지만.. 이 부분에 대한 핸들링도 어느 정도 시스템을 구성하는 데는 필요하다고 개인적으로는 생각합니다. 테스트 결과 옵티마이져내에서 실행계획이 다소 틀려 진점들을 발견했거든요..

 optimizer_index_cacheing

 optimizer_index_cost_adj

즉 이 값에 따라서 옵티마이져가 인덱스를 충분히 사용할 것인가 아니면 full scan하는 비율을 많이 사용할 것인가에 대한 어드바이징을 합니다. kyte 에 의하면 이 두 파라미터는 옵티마이져가 핸들링되는데 민감하게 영향을 끼친다고 하네요.. 대부분의 DBA 는 이 두 파라미터의 설정값을 기본값으로 사용하고 있는 걸로 알고 있고 , 오라클의 권장 사항도 그런 것으로 알고 있지만.. 테스트는 필요할거 같습니다.

데이터 베이스의 사용 용도에 따라서... ^^

 optimizer_index_cacheing

 optimizer_index_cost_adj 

두 파라미터는 조정이 필요합니다.

하지만 9i는 system 통계를 수집하는 기능이 생겼으므로...

오라클이 CPU 의 성능, 디스크 I/O의 성능, 현재의 부하 상태를 팩터에 넣고 계산을 하게 되므로...

(옵티마이져가 아무리 똑똑해도 CPU 성능같은 환경적 요인까지 고려에 넣지는 못했는데 9i에서는 이 기능으로 인해서 가능해졌습니다.

CBO를 쓰신다면 이 개선된 옵티마이져의 기능을 활용하실 수 있습니다.)

 9i 이상에서는 optimizer_index_cacheing, optimizer_index_cost_adj  파라미터보다는 시스템 통계를 수집하시는게 더나을겁니다.

감사합니다..책에서 볼때 가물가물하던 것들이 이렇게 직접 상황에 부딪혀보고, 얘기를 들으니 훨씬 더 와닿는 느낌입니다. 저혼자 공부하는 것보다 더 많은 사람이 공부할 수 있을것 같아 이렇게 글로 올린것 입니다. 생생한 설명 감사드리고요.. 좋은 공부소재가 나오며 또 글 올리겠습니다.

Merry Christmas & Happy New Year..

^^ 향후 5년간 DBA가 먹고살기좋은 직업이라고 얼마전에 나왔더군요..

말씀하신데로 위에 말씀드린 두 파라미터는 dbms_stat ... 라는 system 통계작업이 대체 할수 있습니다.

특히나 9i 릴리즈 2에서는 기능이 강화되서 .. 상세한 시스템 상태까지도 파악이 가능하니 물론 말씀하신데로

더 권장은 될겁니다.

하지만 시스템이 일관적이지 않은 상태인 경우 즉 배치 작업및 간결한 트랜잭션이 계속적으로 발생하는 경우 가장 대표적인 수집된 통계정보가 있어야 하는데.... 그렇지 못할 경우는 말그대로 엉뚱한 실행계획을 타게 됩니다. 지속적인 자료의 수집이 필요한 부분이죠...

물론 위의 두 파라미터를 설정한다는 의미는 보다 cbo 를 최적화 하기 위함이고 ... optimizer_dynamic_sampling  등의 대한 튜닝을 통해 최적화를 통해서 옵티마이져를 개선할수 있는 여지가 충분히 있다고 생각합니다.

저 또한 시스템 상태로 통계정보를 자동화하여 job 으로 설정해서 운영중이 있지만...  optimizer 에 관련된 튜닝을 통해 cbo 를 최적화 할수 있다면.. 한번쯤 테스트해 보는 것도 나쁘지는 않다고 생각합니다.

항상 밴더사의 기능이나 메뉴얼등이 옳은것은 아니니까요 ^^;;

김주현님의 말씀중에 조금 잘못된 듯한 부분이 있어서 한번 짚어 봤으면 합니다.

>> db_file_multiblock_read_count 인데 128이면... OLTP에서는 볼 수 없을 정도로 큰 값입니다. 8이나 16이면 좋습니다.

제 생각으론 db_file_multiblock_read_count의 값은  OLTP환경인지  DW 환경인지에 따라 조절하는 값이 아닌것 같습니다. MBRC의 값은  O/S + HARDWARE에서 SERIAL I/O에 대해 최적으로 응답해 주는 수치를 적어주기 위함이지, OPTIMIZER에게 FULL SCAN을 선호하도록 힌트를 주기위해 사용하는 값이 아닙니다. 

MBRC를 128 (1M)로 크게 설정하는 이유는 DISK SEEK TIME에 대한 OVERHEAD를 줄여서 I/O THROUGH PUT을 높이기 위함입니다. (물론 아시겠지만요)

OLTP라고 해서 FULL SCAN시 적은 데이터를 여러번 읽게 만들어서 (MBRC값을 작게 설정함으로서) 득 될게 있을지 의문입니다.

물론 FULL SCAN을 높게 설정하면 OPTIMIZER가 FULL SCAN을 선호하게 되고 이것이 오히려 OLTP류의 시스템에서는 부하로 작용할수가 있습니다.

그래서 아래의 두 파라미터가 존재하는 것이죠.

optimizer_index_cacheing

optimizer_index_cost_adj 

INDEX 비용에 대한 힌트를 OPTIMIZER에게 줌으로서 DBA가 OPTIMIZER의 성향을 시스템에 맞게 조절할수 있게 하는 것이죠.

결론은

* MBRC는 I/O SYB SYSTEM에 맞는 최적의 값을 적어주고,

* 이로인해 발생하는 OPTIMIZER의 FULL SCAN의 선호는 optimizer_index* 파라미터 들을 통해 조절한다.

이게 제 생각입니다.

(제 경험이 그리 많지 않아 정확하다고 주장은 못하겠네요. ^^)

지속적으로 관심을 가져주시고 좋은 글들을 올려주셔서 너무 감사합니다. 공부할게 얼마나 많은지 새삼 느끼게 해주시는군요.^^

앞으로도 좋은 글 부탁드리겠습니다.

윗분의 말씀 잘 보았습니다.

하지만 제 생각은 약간 차이가 있는데요....

dbms_stats 로 system 통계를 내 보면 말씀하신 멀티 블럭에 대한 i/o 를 aux_stats$ 에서 확인이 가능합니다.

즉 멀티 블럭을 얼마나 i/o 했는가 및 cpu 의 성능 및 단일 블럭의 i/o 등등에 대한 통계자료를 확인할수 있습니다.

cbo 기반이라면 당연히 옵티마이져는 cost 를 기준으로 실행계획을 잡아 가게 됩니다. 즉 시스템 통계정보는 옵티마이져에 많은 영향을 주는 것은 틀립없으며, optimizer_index_caching 및 optimizer 에 관련된 파라미터 역시도 옵티마이져에 지대한 영향을 끼치는 요소임에는 틀립없습니다. 다면 시스템 단위에 비교적 정확한 통계정보를 기반으로 옵티마이져에게 어드 바이징 하는 역할을 담당하는 요소로 위의 파라미터보다는 system 통계 정보가 좀더 정확한 영향을끼치겠죠..

다면 이 통계정보가 정확히 peak time 때 정확한 정보를 제공해야 하지만..말이죠..

여하튼 system  통계정보가 멀티 블럭에 대한 i/o 비용이 인덱스를 사용했을때 보다 cost 비용이 적다면 옵티마이져는 full scan 을 통하는 방법을 많이 사용할 것으로 판단 할 가능성이 높습니다.

즉 데이터 베이스의 성격에 따라 단일 블럭에 대한 i/o 및 멀티 블럭에 대한 i/o 에 대한 cost 를 산정해서 cost 가 좋은 쪽의 실행계획을 갈 확률이 높기 때문에 블럭 사이즈 및 multi block 에 대한 파라미터는 옵티마이져에게 영햐을 끼칠 소지가 아주 높다고 생각합니다.

저도 물론 system 통계를 기반으로 옵티마이져에게 어드바이징 하는 하는 것을 사용하고 있으나 요즘은 시스템이 들쭉 날쭉해서 위에 언급한 파라미터를 통해서 인스턴스 레벨의 어느 정도 고정된 어드바이징을 위해 파라미터를 조정하려고 요즘 테스트 중입니다.

수고하세요.. ^^;;좋은글 잘 읽었씁니다.

저같은 경우 현재 개발중인 오라클에 대해서

기본적으로 system statistics 수집은 하고

예상과는 달리 옵티마이저가 지나치게 full scan을 선호하게 되는경우, optimizer_index* 파라미터를  조금씩 수치를 조정하여 적절히 튜닝을 합니다.

system statistics가 수집된 상황에서도 optimizer_index* 파라미터의 설정에 따라서 plan이 바뀐답니다.

참고.

It is still meaningful, by the way, to use optimizer_index_cost_adj as a clue to table caching effects (specifically, what percentage of single block table reads are likely to turn into real read requests) even when using system statistics.

(http://www.oracle.com/technology/pub/articles/lewis_cbo.html)

저도 좋은글 잘 읽고 있습니다.

^^; 수고하세요.

원문의 취지엔 벗어나는 질문입니다만..

오늘 technical bulletins에서 본 내용중에

"
db_file_multiblock_read_count 는 Full Table Scan 시에만 영향을 미치는  파라미터이다. 오라클의 최대 I/O 크기는 64KB 이므로 
   db_blocksize * db_file_multiblock_read_count <= 64KB
 이어야 한다.
"

란 내용이 있었습니다만..  block 사이즈가 얼마인지는 몰라도 일반적인 4k 혹은 8k 를 사용한다면

최대값은 db_file_multiblock_read_count=8 혹은 16이 되어야하지 않을까 란 생각이 들어서요.

즉, SERVER2의 128이란 값은 목적이 어떠하든 엄청나게 큰 값이라는 거죠.

고수분님들의 확인 부탁드려봅니다.

(생각난김에 우리 DB도 확인해봤더니 blocksize 8k 에

db_file_multiblock_read_count=16   이더라는 -_-;; )

맞습니다. 128 이란 값은 아무 의미없는값입니다.

이 변수는 os 의존적인 패러미터입니다.

가령 솔라리스의 경우 그 값이 max_io_size(커널 패러미터)/db_block_size 값을 넘어설수 없습니다.

os 별로 maximum 값이 다릅니다.

테스트 디비가 있는 경우 db_file_multiblock_read_count 값을 늘려가면서 테이블풀스캔시의 trace 화일을 뜨면, 현재 운영중인 os 에 세팅할수 있는 db_file_multiblock_read_count의 최대값이 뭔지 알수 있습니다.