데이터베이스 최적화

옛날 성능 저하 발생시 

-> SQL 튜닝보단 해당 시스템의 cpu 또는 디스크 등의 자원을 증설하는 부분에 초점.

# 다르게 생각하라.

주어진 SQL을 그대로 보지 말고 다르게 보라.
가령 데이터 삭제를 Delete 하지 않고, 데이터 갱신을 UPDATE 하지 않는다?

! DML 은 왜 성능을 저하시키는가

1) Insert의 성증 저하 원인

1. 로그 기록

2. HWM BUMP UP

3. 인덱스의 개수

4. 롤백을 위한 로그 기록

5. 디스크 I/O

   !로그 기록 

-> 데이터베이스는 작업의 수행도 중요하지만 작업이 실패하거나 데이터베이스 장애(다른 장애에 의해 시스템이 재기동)에 대해 데이터를 보호해야 하는 책임을 갖음. 그래서 데이터베이스는 INSERT 작업을 수행하기 전 어떤 작업을 수행하는지에 대한 로그를 기록(선 로그 기법(LOG AHEAD)이라 함). 그래서 INSERT를 하다 문제가 발생해도 복구가 되는것이다. 실제 INSERT 작업과 관계가 없는 로그를 기록해야 하기 때문에 성능이 저하됨.

  !! HWM BUMP UP

-> HWM BUMP UP은 오라클 데이터베이스의 내부적인 요소임. INSERT를 수행하게 되면 해당 테이블에 할당되어 있는 공간에 데이터를 저장하게 되며 해당 공간을 익스텐트라 부름. 이 익스텐트에는 HWM가 설정되어 있어서 데이터는 HWM 앞의 블록에만 저장됨. 따라서 HWM 앞까지만 데이터를저장하고 HWM가 뒤로 후진해야만 데이터를 INSERT 할수 있는거임. 이것을 HWM BUMP UP 이라 함. 많은 양의 데이터를 INSERT 하면 HWM BUMP UP이 많이 발생해서 성능이 저하됨.

!!! 인덱스의 개수

-> 데이터를 테이블에 저장하는 것은 여유 공간을 가지고 있는 데이터 블록에 해당 데이터를 저장하면 됨. 그러나 인덱스에는 정해진 위치가 존재하게 되므로 정해진 위치를 찾는 프로세스가 수행됨. ex) 해당 테이블에 인덱스가 10개라면 이와 같이 저장되는 데이터에 대해 인덱스에서의 위치를 찾기 위해 정해진 위치를 찾는 프로세스가 10번 수행되어야 할것임. 따라서 인덱스의 개수가 많다면 성능이 저하됨. 

!!!! 롤백을 위한 로그 기록

-> 해당 작업을 수행한 후 작업을 취소하는 경우 이전 데이터로 복구하기 위해 이전 데이터의 값을 저장하는것. 실제 데이터를 저장하는 작업과는 별개로 수행되므로 성능이 저하됨.

!!!!! 디스크 I/O

-> HWM BUMP UP을 제외한 로그 기록, 인덱스 및 롤백을 위한 로그 기록은 모드 디스크의 I/O를 발생시킴. 실제 데이터를 저장하는 작업에서도 디스크 I/O가 발생. 따라서 성능이 저하됨.

  2) UPDATE 의 성능 저하 원인

1) 로그 기록

2) UPDATE 컬럼이 사용된 인덱스의 개수

3) 롤백을 위한 로그 기록

4) 디스크 I/O

-> UPDATE는 HWM BUMP UP 은 발생하지 않음. 인덱스 개수도 해당 테이블에 존재하는 모든 인덱스는 아니고 UPDATE가 수행되는 컬럼이 사용된 인덱스의 개수를 의미. UPDATE가 수행되면 해당 컬럼을 인덱스의 컬럼으로 구성하고 있는 인덱스만 갱신하기 때문에 성능 저하됨.

-> 동일한 양에 대해 INSERT 와 UPDATE를 수행한다면 UPDATE가 성능 저하를 더 많이 발생시킴. INSERT 작업은 로그에 이전 데이터라는 것은 존재하지 않음. INSERT 작업이 수행된 데이터의 위치 정보만 가지게 된다면 언제든지 롤백이 수행 가능. 장애시 복구도 어렵지 않음. 하지만 UPDATE는 이전 데이터의 값이 존재하기 때문에 이전 데이터를 로그에 기록함. 따라서 로그 기록 및 롤백을 위한 로그 기록에서 UPDATE 가 INSERT 보다 더 많은 데이터를 기록해야 하므로 디스크 I/O도 증가하므로 성능도 더 저하됨.

!! UPDATE 컬럼이 사용된 인덱스의 개수

->UPDATE가 수행되면 해당 컬럼을 인덱스의 컬럼으로 구성하고 있는 인덱스만을 갱신하게 됨. 

3) DELETE 의 성능 저하 원인

1) 로그 기록

2) 인덱스의 개수

3) 롤백을 위한 로그 기록

4) 디스크 I/O

-> DELETE도 이전 데이터와 이후 데이터를 모두 로그에 기록해야 하므로  더 많은 디스크 I/O가 발생하고 INSERT 보다 성능이 더 저하됨. 

출처 : DBGuide.net(데이터 전문가 지식포털) - D스토리 - 권순용의 DBA이야기 - 생각의 전환이 데이터베이스를 최적화 시킨다.