Top 10 Reasons for poor Oracle performance

출처 :  http://cafe.naver.com/prodba



무엇이 오라클 성능 저하의 주된 원인일까? 대부분의 전문가들이 다른 견해를 주지만 오라클 튜닝 대상 업체 100여개를 토대로 리스트를 만들어 보았다.

1. 잘못된 모델링
 : 성능 저하의 주범 중 하나는 단순한 fetch 에도 15개 이상의 조인과 과다하게(사용하지 않는 인덱스) 생성한 것과 같은 테이블을 여러개로 쪼개는 지나친 정규화이다.

2. 불량한 서버 최적화
 : Server 커널 파라미터와 I/O configuration (direct I/O와 같은) 은 오라클 성능에 많은 영향을 끼친다. 

3. 불량한 DISK I/O
 : RAID5의 부적절한 사용은 DISK 채널 보틀낵와 DISK 스트라이핑 불량의 원인이 된다.

4. 옵티마이저 통계정보의 부재
 : 10g 이전(자동통계정보) 에는 SQL 퍼포먼스의 일반적인 원인이 CBO 통계정보와 히스토그램 정보의 부재나 오래된 것이 원인이 였다.              

5. 오브젝트 경합(Object Contention)
 : ASSM 을 설정하지 않았다면 DML 이 진행중인 테이블과 인덱스의 Freelist, freelist groups이 원인이 되어 DML 퍼포먼스가 느리게 된다.

6. 메모리의 부족
 : shared_pool_size, pga_aggregate_target,db_cache_size 를 수용하기에 충분한 크기가 아니면 disk I/O를 발생시키는 원인이 된다.

7. 하드파싱이 일어나는 SQL
 : 모든 SQL은 호스트 변수를 사용하거나 cursor_sharing=force 로서 library cache 에서 재사용되어야 한다.

8. 잘못된 초기화 파라미터 설정
 : 많은 오라클 파라미터가 DBA에 의해서 설정된다. (db_file_multiblock_read_count, optimizer_index_caching). 그리고 이러한 파라미터 설정을 제대로 하지 못한다면 비효율적인 실행계획이 수립될 가능성을 높일 것이다.

9. Nested loop 조인의 과다
 : 64 Bit Oracle 시스템에서는 메모리 소팅과 해시 조인을 위한 기가바이트의 메모리를 이용할 수 있다. Pga_aggregate_target 을 제대로 설정하지 못하면 CBO 가 매우 느린 hash join 을 선택할 수 있다.

10. 인재에 의한 실수
 : DBA가 데이터베이스 모니터링(AWR/STATSPACK)이나 OEM 리포트의 설치와 인스턴스에 맞게 부하를 조절하지 않는 것은 성능저하의 주요 원인이 될 수 있다.

 




다음은 Oracle Document list 에서 제시하는 퍼포먼스 불량의 10가지 원인에 대해 나온 리스트이다. 위의 BC 리스트와 유사하지만 고객과 같이 접했던 10가지 리스트에 근거하였다.

1.Bad Connection 관리어플리케이션은 데이터베이스로 접속을 맺거나 끊는다. 이 문제는  어플리케이션상에서의 미들웨어에서 손실이 대부분이다. 이 문제가 2개 이상 발생한다면 시스템은 불안정해질것이다.

2.Cursor 와 Shared pool 의 잘못된 사용 반복되는 파싱 결과에는 Cursor 를 사용하지 않는다. 만일 바인드 변수가 사용되지 않는다면 모든 SQL문장은 하드파싱을 수행한다. 커서를 오픈하고 많은 횟수를 실행하는 경우 바인드 변수를 사용한 커서를 사용하라. 또한 Dynamic SQL을 사용하는 어플리케이션을 일단 의심해보라.

3.데이터베이스 I/O에 대한 오판많은 사이트에서 데이터베이스를 퍼포먼스가 나오지 않는 Disk 상에 위치시키고 있다. 그 밖의 사이트는 Disk개수를 잘못 산정하고 있다.  왜냐하면 그들은 I/O 대역폭이 아닌 단지 디스크 스페이스만으로 판단하기 때문이다.

4.리두로그 설정 문제 많은 사이트에서 사이즈가 작고 적은 갯수의 redo 로그를 사용하고 있다. 작은 Redo 로그는 잦은 시스템 체크포인트로 버퍼 캐시와 시스템 I/O 상에서의 지속적인 부하를 일으키는 원인이 된다. 만일 너무 작은 갯수의 redo 로그를 가진다면 데이터베이스는 아카이브 프로세스가 대기하는 원인이 될 것이다.  

5.버퍼 캐시상에서의 연속된 데이터블록은 Free list, Free list Group, INITRANS, 롤백세그먼트의 부족의 원인이 된다. 8K 나 16K 블록사이즈를 가지거나 많은 Active 유저나 적은 롤백 세그먼트를 가지는 Insert 지향적인 어플리케이션상에서의 현저히 문제가 된다. 

6.Long Full table Scan 인덱스 부재나 불량한 SQL 최적화로 인해 long Full Table scan 을 유발한다. Long table scans은 I/O 집중과 불안정의 원인이 된다.

7.Disk Sorting  잘못된 트랜잭션 설계나 인덱스 부재, 불량한SQL 최적화로 인해서 온라인 작업에서 Disk sort 를 유발한다. Disk sort 는 I/O 집중과 불안정의 원인이 된다.

8.과다한 Recursive (SYS) SQL SYS유저에 의해 실행되는 Recursive SQL이 많은 경우 extent 확장과 같은 space management 작업을 유발시킨다. 이는 시스템의 불안정과 유저의 응답시간에 영향을 미친다. 반면 SYS 유저가 아닌 일반유저가 실행하는 부가적인 Recursive SQL 은 문제가 되지 않는다. 

9.스키마 에러와 옵티마이저 문제 많은 경우 오래된 시스템이나 개발 시스템으로부터 마이그레이션 된 스키마가 소유한 테이블이 성공적으로 이행하지 못해서 어플리케이션이 많은 리소스를 사용하는 경우가 많다. 인덱스가 누락되었거나 잘못된 통계정보가 그 대표적인 경우이다. 어플리케이션 마이그레이션시에는 잘알려진 플랜 안정화를 할 수 있는 DBMS_STATS 패키지를 사용하고 초기화 파라미터는 최적의 실행계획을 도출할 수 있도록 설정해야 한다. 이러한 이유로 스키마와 스키마 통계정보 그리고 옵티마이저 세팅은 최적의 퍼포먼스를 유지할 수 있도록 함계 관리되어져야 한다.

10.표준화되지 않은 초기화 파라미터 사용부적절한 조언이나 잘못된 산정으로 인해 설정된 파라미터 예를 들어 SPIN_COUNT 와 같은 래치에 관련된 문서화되지 않는 파라미터를 커다란 성능적인 문제를 유발할 수도 있다. 반드시 문서화되지 않는 파라미터는 전문가의 조언을 받아야 할 것이다.