인덱스를 사용하는 것으로 수천만건의 데이터에서 원하는 데이터를 매우 빠르게 찾아낼 수 있다.
하지만 그를 넘는 대량의 데이터에서, 인덱스가 오히려 느린 경우가 있다.
테이블 스캔이 블록 엑세스하는 과정
익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다. → 같은 익스텐트 == 같은 데이터 블록 => block I/O 용이하다!
인덱스로 테이블 블록 엑세스하는 과정
리프 블록에서 읽은 ROWID를 분해해서 DBA(디스크 주소 정보) 를 얻고,
I/O 성능 개선을 위해 버퍼 캐시를 활용하기 위해 버퍼 캐시를 확인하고, 읽으려는 DBA를 해시 함수에 입력해서 hash chain을 찾고 여기서 버퍼 헤더를 찾는다.
**캐시 적재시 매번 같은 해시 함수 쓰기에 버퍼 헤더는 항상 같은 해시 체인에 연결되지만, 실제 데이터가 담긴 버퍼 블록은 매번 다른 위치에 캐싱 된다.
그리고 동시 액세스가 심한 경우에는 캐시 버퍼 채인 래치와 버퍼 lock 경합 등도 발생하는 등 생각보다 고비용이다.
+ index 사용하는데, 만약 테이블이 너무 커서 index 를 메모리에 1번에 담을 수 없으면, 성능 이슈로 이어진다.
인덱스 클러스터링 팩터
CF ( clustering factor ) == 인덱스 레코드 정렬 순서와 테이블 레코드 정렬의 일치하는 정도
CF가 좋으면 ' 테이블 액세스량에 비해 블록 I/O가 적게 발생' 한다.
** 어차피 레코드마다 건건이 블록 I/O하면 의미 없는데 왜 그럴까?
buffer pinning
인덱스의 ROWID로 테이블 액세스 과정에서, 오라클은 latch 획득 & 해시 체인 획득 과정을 거쳐 어렵게 찾아낸 '테이블 블록에 대한 메모리 주소값을 바로 버리지 않고 일단 유지한다.'
이로인해, 만약 인덱스에서 다음 레코드를 읽었는데, '직전과 같은 테이블 블록' 이 필요하면, latch 획득 & 해시 체인 스캔 과정을 생략하고 바로 테이블 블록을 읽을 수 있다.
따라서, CK가 높으면 buffer pinning으로 인해 같은 테이블 블록을 여러번 가져오는 비효율이 감소한다.
> 정확히는, '다음에 읽을 블록이 직전 블록과 일치' 하는 상황이 많이 생기면 좋은 것.
인덱스 손익분기점
table full scan은 1건을 가져오던, 전체를 가져오던 비용이 차이 없다.
|
// 아래 쿼리들을 table full scan 수행, 건수 상관없이 성능 동일.
select /*+ full(t)*/ count(*) from t where no <= 1;
select /*+ full(t)*/ count(*) from t where no <= 10000;
select /*+ full(t)*/ count(*) from t where no <= 10000000;
|
하지만 인덱스 사용 시, 읽는 양에 비례하여 성능이 느려진다.
table full scan:
sequencial access, , multi block I/O
index table scan:
random access, single block I/O
보통 5~20% 수준은 약 10만건, 많아봐야 100만건 이내이다.
1000만건 가량의 테이블에서는 인덱스가 무의미해질 확률이 높아져간다. → 이정도 규모면 파티셔닝 고려가 필수다.
대량 데이터에서 인덱스 성능 급감현상
너무 데이터가 많아지면, 조회건수가 늘어난 양에 비해, 폭팔적으로 조회 성능이 느려지는 현상을 볼 수 있다
1) 데이터를 버퍼 캐시에서 찾게 될 가능성이 낮아지기 때문이다.
2) 인덱스를 메모리에 한번에 못 올리기 때문에, page 교체 오버헤드도 급격히 증가한다( 1~100 필요한데, 1~10 까지 밖에 못올리면, 1~10 버리고 11~20 올림. 근데 이후에 또 1~10 필요하면...? 이런식의 비효율 생김)
3) 인덱스 컬럼 기준으로 값이 같은 테이블 레코드가 '모여있을 가능성' 도 급격히 낮아진다. 따라서, 직전과 동일한 테이블 블록 읽을 확률, 즉 buffer pinning의 덕을 볼 확률이 급감한다.
온라인 트랜잭션 처리 프로그램
DW/OLAP/BATCH 프로그램
배치 프로그램 튜닝 VS 온라인 프로그램 튜닝
온라인 프로그램 튜닝
'특정' 고객의 확인번호를 받아서 조회
보통의 온라인 서비스는 소량의 데이터를 쓰고 읽는다. 따라서 큰 데이터 에서 정확히 필요한 소량의 데이터를 조회하는데 인덱스 사용이 성능에 이점을 준다.
또한, NL 조인도 효과적이다 ( 보통 inner는 가능한 index 사용하고, outer 도 index같이 사용해서 join한다)
배치 프로그램 튜닝
batch job의 경우에는 대량의 데이터 쓰기, 읽기 작업이 생긴다.
따라서, 항상 전체 범위 처리 기준으로 튜닝해야한다 (부분이 아닌 전체를 빠르게 처리) 이때, 보통 인덱스 와 NL 조인 보다 table full scan & hash join이 유리하다.
고객 코드가 'A001' 인 고객이 수백만명이라는 가정하에
아래와 같이, 기존 온라인 프로그램에서 사용한 쿼리에서 조건절만 바꿔서 사용하면 비효율이 생긴다.
위와 같이 하는 것보다, 아래와 같은 full scan + hash join 활용하는 방식이 차라리 더 나은 성능을 보인다.
하지만 이 경우, 조건절에 해당하지 않는 고객 데이터, 1년 초과 이력 데이터까지 읽는 비효율이 생긴다.
** from a,b >> a,b 를 crosejoin 한 값 가져오는 것.
** INDEX_FFS: [Hint]ACCESS 경로를 변경하는 힌트
이 힌트의 의미는 인덱스를 FAST FULL SCAN하라는 것인데 보통 인덱스에 대한 스캔은 단일 블록 스캔인데 반해 인덱스 패스트 풀 스캔은 Multi Block Scan 이다.
window 함수 활용
위에서, 고객 변경 이력 테이블을 2번 읽는 비효율을 없애기 위해 아래와 같이 윈도우 함수 사용이 가능하다.
초대용량 테이블에서는, 보통 파티션 + 병렬 처리 로 튜닝한다.
위 쿼리의 경우, 고객 변경 이력 테이블을 변경일시 기준으로 파티셔닝하면, 변경일시 조건에 해당하는 파티션만 골라서 full scan 하므로 부담을 크게 줄일 수 있다.
ex) 1년 단위로 테이블 나눈다.
그리고 최근 3년 에대한 데이터 조회 시, 최근 1년, 2년 ,3년 테이블 각각에 병렬적으로 Full Scan 쿼리 날려서 최적화 가능하다. 하지만 그를 넘는 대량의 데이터에서, 인덱스가 오히려 느린 경우가 있다.
테이블 스캔이 블록 엑세스하는 과정
익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다. → 같은 익스텐트 == 같은 데이터 블록 => block I/O 용이하다!
인덱스로 테이블 블록 엑세스하는 과정
리프 블록에서 읽은 ROWID를 분해해서 DBA(디스크 주소 정보) 를 얻고,
I/O 성능 개선을 위해 버퍼 캐시를 활용하기 위해 버퍼 캐시를 확인하고, 읽으려는 DBA를 해시 함수에 입력해서 hash chain을 찾고 여기서 버퍼 헤더를 찾는다.
**캐시 적재시 매번 같은 해시 함수 쓰기에 버퍼 헤더는 항상 같은 해시 체인에 연결되지만, 실제 데이터가 담긴 버퍼 블록은 매번 다른 위치에 캐싱 된다.
그리고 동시 액세스가 심한 경우에는 캐시 버퍼 채인 래치와 버퍼 lock 경합 등도 발생하는 등 생각보다 고비용이다.
+ index 사용하는데, 만약 테이블이 너무 커서 index 를 메모리에 1번에 담을 수 없으면, 성능 이슈로 이어진다.
인덱스 클러스터링 팩터
CF ( clustering factor ) == 인덱스 레코드 정렬 순서와 테이블 레코드 정렬의 일치하는 정도
CF가 좋으면 ' 테이블 액세스량에 비해 블록 I/O가 적게 발생' 한다.
** 어차피 레코드마다 건건이 블록 I/O하면 의미 없는데 왜 그럴까?
buffer pinning
인덱스의 ROWID로 테이블 액세스 과정에서, 오라클은 latch 획득 & 해시 체인 획득 과정을 거쳐 어렵게 찾아낸 '테이블 블록에 대한 메모리 주소값을 바로 버리지 않고 일단 유지한다.'
이로인해, 만약 인덱스에서 다음 레코드를 읽었는데, '직전과 같은 테이블 블록' 이 필요하면, latch 획득 & 해시 체인 스캔 과정을 생략하고 바로 테이블 블록을 읽을 수 있다.
따라서, CK가 높으면 buffer pinning으로 인해 같은 테이블 블록을 여러번 가져오는 비효율이 감소한다.
> 정확히는, '다음에 읽을 블록이 직전 블록과 일치' 하는 상황이 많이 생기면 좋은 것.
인덱스 손익분기점
table full scan은 1건을 가져오던, 전체를 가져오던 비용이 차이 없다.
|
// 아래 쿼리들을 table full scan 수행, 건수 상관없이 성능 동일.
select /*+ full(t)*/ count(*) from t where no <= 1;
select /*+ full(t)*/ count(*) from t where no <= 10000;
select /*+ full(t)*/ count(*) from t where no <= 10000000;
|
하지만 인덱스 사용 시, 읽는 양에 비례하여 성능이 느려진다.
table full scan:
sequencial access, , multi block I/O
index table scan:
random access, single block I/O
보통 5~20% 수준은 약 10만건, 많아봐야 100만건 이내이다.
1000만건 가량의 테이블에서는 인덱스가 무의미해질 확률이 높아져간다. → 이정도 규모면 파티셔닝 고려가 필수다.
대량 데이터에서 인덱스 성능 급감현상
너무 데이터가 많아지면, 조회건수가 늘어난 양에 비해, 폭팔적으로 조회 성능이 느려지는 현상을 볼 수 있다
1) 데이터를 버퍼 캐시에서 찾게 될 가능성이 낮아지기 때문이다.
2) 인덱스를 메모리에 한번에 못 올리기 때문에, page 교체 오버헤드도 급격히 증가한다( 1~100 필요한데, 1~10 까지 밖에 못올리면, 1~10 버리고 11~20 올림. 근데 이후에 또 1~10 필요하면...? 이런식의 비효율 생김)
3) 인덱스 컬럼 기준으로 값이 같은 테이블 레코드가 '모여있을 가능성' 도 급격히 낮아진다. 따라서, 직전과 동일한 테이블 블록 읽을 확률, 즉 buffer pinning의 덕을 볼 확률이 급감한다.
온라인 트랜잭션 처리 프로그램
DW/OLAP/BATCH 프로그램
배치 프로그램 튜닝 VS 온라인 프로그램 튜닝
온라인 프로그램 튜닝
'특정' 고객의 확인번호를 받아서 조회
보통의 온라인 서비스는 소량의 데이터를 쓰고 읽는다. 따라서 큰 데이터 에서 정확히 필요한 소량의 데이터를 조회하는데 인덱스 사용이 성능에 이점을 준다.
또한, NL 조인도 효과적이다 ( 보통 inner는 가능한 index 사용하고, outer 도 index같이 사용해서 join한다)
배치 프로그램 튜닝
batch job의 경우에는 대량의 데이터 쓰기, 읽기 작업이 생긴다.
따라서, 항상 전체 범위 처리 기준으로 튜닝해야한다 (부분이 아닌 전체를 빠르게 처리) 이때, 보통 인덱스 와 NL 조인 보다 table full scan & hash join이 유리하다.
고객 코드가 'A001' 인 고객이 수백만명이라는 가정하에
아래와 같이, 기존 온라인 프로그램에서 사용한 쿼리에서 조건절만 바꿔서 사용하면 비효율이 생긴다.
위와 같이 하는 것보다, 아래와 같은 full scan + hash join 활용하는 방식이 차라리 더 나은 성능을 보인다.
하지만 이 경우, 조건절에 해당하지 않는 고객 데이터, 1년 초과 이력 데이터까지 읽는 비효율이 생긴다.
** from a,b >> a,b 를 crosejoin 한 값 가져오는 것.
** INDEX_FFS: [Hint]ACCESS 경로를 변경하는 힌트
이 힌트의 의미는 인덱스를 FAST FULL SCAN하라는 것인데 보통 인덱스에 대한 스캔은 단일 블록 스캔인데 반해 인덱스 패스트 풀 스캔은 Multi Block Scan 이다.
window 함수 활용
위에서, 고객 변경 이력 테이블을 2번 읽는 비효율을 없애기 위해 아래와 같이 윈도우 함수 사용이 가능하다.
초대용량 테이블에서는, 보통 파티션 + 병렬 처리 로 튜닝한다.
위 쿼리의 경우, 고객 변경 이력 테이블을 변경일시 기준으로 파티셔닝하면, 변경일시 조건에 해당하는 파티션만 골라서 full scan 하므로 부담을 크게 줄일 수 있다.
ex) 1년 단위로 테이블 나눈다.
그리고 최근 3년 에대한 데이터 조회 시, 최근 1년, 2년 ,3년 테이블 각각에 병렬적으로 Full Scan 쿼리 날려서 최적화 가능하다.
테이블 스캔이 블록 엑세스하는 과정
익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다. → 같은 익스텐트 == 같은 데이터 블록 => block I/O 용이하다!
인덱스로 테이블 블록 엑세스하는 과정
리프 블록에서 읽은 ROWID를 분해해서 DBA(디스크 주소 정보) 를 얻고,
I/O 성능 개선을 위해 버퍼 캐시를 활용하기 위해 버퍼 캐시를 확인하고, 읽으려는 DBA를 해시 함수에 입력해서 hash chain을 찾고 여기서 버퍼 헤더를 찾는다.
**캐시 적재시 매번 같은 해시 함수 쓰기에 버퍼 헤더는 항상 같은 해시 체인에 연결되지만, 실제 데이터가 담긴 버퍼 블록은 매번 다른 위치에 캐싱 된다.
그리고 동시 액세스가 심한 경우에는 캐시 버퍼 채인 래치와 버퍼 lock 경합 등도 발생하는 등 생각보다 고비용이다.
+ index 사용하는데, 만약 테이블이 너무 커서 index 를 메모리에 1번에 담을 수 없으면, 성능 이슈로 이어진다.
인덱스 클러스터링 팩터
CF ( clustering factor ) == 인덱스 레코드 정렬 순서와 테이블 레코드 정렬의 일치하는 정도
CF가 좋으면 ' 테이블 액세스량에 비해 블록 I/O가 적게 발생' 한다.
** 어차피 레코드마다 건건이 블록 I/O하면 의미 없는데 왜 그럴까?
buffer pinning
인덱스의 ROWID로 테이블 액세스 과정에서, 오라클은 latch 획득 & 해시 체인 획득 과정을 거쳐 어렵게 찾아낸 '테이블 블록에 대한 메모리 주소값을 바로 버리지 않고 일단 유지한다.'
이로인해, 만약 인덱스에서 다음 레코드를 읽었는데, '직전과 같은 테이블 블록' 이 필요하면, latch 획득 & 해시 체인 스캔 과정을 생략하고 바로 테이블 블록을 읽을 수 있다.
따라서, CK가 높으면 buffer pinning으로 인해 같은 테이블 블록을 여러번 가져오는 비효율이 감소한다.
> 정확히는, '다음에 읽을 블록이 직전 블록과 일치' 하는 상황이 많이 생기면 좋은 것.
인덱스 손익분기점
table full scan은 1건을 가져오던, 전체를 가져오던 비용이 차이 없다.
|
// 아래 쿼리들을 table full scan 수행, 건수 상관없이 성능 동일.
select /*+ full(t)*/ count(*) from t where no <= 1;
select /*+ full(t)*/ count(*) from t where no <= 10000;
select /*+ full(t)*/ count(*) from t where no <= 10000000;
|
하지만 인덱스 사용 시, 읽는 양에 비례하여 성능이 느려진다.
table full scan:
sequencial access, , multi block I/O
index table scan:
random access, single block I/O
보통 5~20% 수준은 약 10만건, 많아봐야 100만건 이내이다.
1000만건 가량의 테이블에서는 인덱스가 무의미해질 확률이 높아져간다. → 이정도 규모면 파티셔닝 고려가 필수다.
대량 데이터에서 인덱스 성능 급감현상
너무 데이터가 많아지면, 조회건수가 늘어난 양에 비해, 폭팔적으로 조회 성능이 느려지는 현상을 볼 수 있다
1) 데이터를 버퍼 캐시에서 찾게 될 가능성이 낮아지기 때문이다.
2) 인덱스를 메모리에 한번에 못 올리기 때문에, page 교체 오버헤드도 급격히 증가한다( 1~100 필요한데, 1~10 까지 밖에 못올리면, 1~10 버리고 11~20 올림. 근데 이후에 또 1~10 필요하면...? 이런식의 비효율 생김)
3) 인덱스 컬럼 기준으로 값이 같은 테이블 레코드가 '모여있을 가능성' 도 급격히 낮아진다. 따라서, 직전과 동일한 테이블 블록 읽을 확률, 즉 buffer pinning의 덕을 볼 확률이 급감한다.
온라인 트랜잭션 처리 프로그램
DW/OLAP/BATCH 프로그램
배치 프로그램 튜닝 VS 온라인 프로그램 튜닝
온라인 프로그램 튜닝
'특정' 고객의 확인번호를 받아서 조회
보통의 온라인 서비스는 소량의 데이터를 쓰고 읽는다. 따라서 큰 데이터 에서 정확히 필요한 소량의 데이터를 조회하는데 인덱스 사용이 성능에 이점을 준다.
또한, NL 조인도 효과적이다 ( 보통 inner는 가능한 index 사용하고, outer 도 index같이 사용해서 join한다)
배치 프로그램 튜닝
batch job의 경우에는 대량의 데이터 쓰기, 읽기 작업이 생긴다.
따라서, 항상 전체 범위 처리 기준으로 튜닝해야한다 (부분이 아닌 전체를 빠르게 처리) 이때, 보통 인덱스 와 NL 조인 보다 table full scan & hash join이 유리하다.
고객 코드가 'A001' 인 고객이 수백만명이라는 가정하에
아래와 같이, 기존 온라인 프로그램에서 사용한 쿼리에서 조건절만 바꿔서 사용하면 비효율이 생긴다.
위와 같이 하는 것보다, 아래와 같은 full scan + hash join 활용하는 방식이 차라리 더 나은 성능을 보인다.
하지만 이 경우, 조건절에 해당하지 않는 고객 데이터, 1년 초과 이력 데이터까지 읽는 비효율이 생긴다.
** from a,b >> a,b 를 crosejoin 한 값 가져오는 것.
** INDEX_FFS: [Hint]ACCESS 경로를 변경하는 힌트
이 힌트의 의미는 인덱스를 FAST FULL SCAN하라는 것인데 보통 인덱스에 대한 스캔은 단일 블록 스캔인데 반해 인덱스 패스트 풀 스캔은 Multi Block Scan 이다.
window 함수 활용
위에서, 고객 변경 이력 테이블을 2번 읽는 비효율을 없애기 위해 아래와 같이 윈도우 함수 사용이 가능하다.
초대용량 테이블에서는, 보통 파티션 + 병렬 처리 로 튜닝한다.
위 쿼리의 경우, 고객 변경 이력 테이블을 변경일시 기준으로 파티셔닝하면, 변경일시 조건에 해당하는 파티션만 골라서 full scan 하므로 부담을 크게 줄일 수 있다.
ex) 1년 단위로 테이블 나눈다.
그리고 최근 3년 에대한 데이터 조회 시, 최근 1년, 2년 ,3년 테이블 각각에 병렬적으로 Full Scan 쿼리 날려서 최적화 가능하다.
테이블 스캔이 블록 엑세스하는 과정
익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다. → 같은 익스텐트 == 같은 데이터 블록 => block I/O 용이하다!
인덱스로 테이블 블록 엑세스하는 과정
리프 블록에서 읽은 ROWID를 분해해서 DBA(디스크 주소 정보) 를 얻고,
I/O 성능 개선을 위해 버퍼 캐시를 활용하기 위해 버퍼 캐시를 확인하고, 읽으려는 DBA를 해시 함수에 입력해서 hash chain을 찾고 여기서 버퍼 헤더를 찾는다.
**캐시 적재시 매번 같은 해시 함수 쓰기에 버퍼 헤더는 항상 같은 해시 체인에 연결되지만, 실제 데이터가 담긴 버퍼 블록은 매번 다른 위치에 캐싱 된다.
그리고 동시 액세스가 심한 경우에는 캐시 버퍼 채인 래치와 버퍼 lock 경합 등도 발생하는 등 생각보다 고비용이다.
+ index 사용하는데, 만약 테이블이 너무 커서 index 를 메모리에 1번에 담을 수 없으면, 성능 이슈로 이어진다.
인덱스 클러스터링 팩터
CF ( clustering factor ) == 인덱스 레코드 정렬 순서와 테이블 레코드 정렬의 일치하는 정도
CF가 좋으면 ' 테이블 액세스량에 비해 블록 I/O가 적게 발생' 한다.
** 어차피 레코드마다 건건이 블록 I/O하면 의미 없는데 왜 그럴까?
buffer pinning
인덱스의 ROWID로 테이블 액세스 과정에서, 오라클은 latch 획득 & 해시 체인 획득 과정을 거쳐 어렵게 찾아낸 '테이블 블록에 대한 메모리 주소값을 바로 버리지 않고 일단 유지한다.'
이로인해, 만약 인덱스에서 다음 레코드를 읽었는데, '직전과 같은 테이블 블록' 이 필요하면, latch 획득 & 해시 체인 스캔 과정을 생략하고 바로 테이블 블록을 읽을 수 있다.
따라서, CK가 높으면 buffer pinning으로 인해 같은 테이블 블록을 여러번 가져오는 비효율이 감소한다.
> 정확히는, '다음에 읽을 블록이 직전 블록과 일치' 하는 상황이 많이 생기면 좋은 것.
인덱스 손익분기점
table full scan은 1건을 가져오던, 전체를 가져오던 비용이 차이 없다.
|
// 아래 쿼리들을 table full scan 수행, 건수 상관없이 성능 동일.
select /*+ full(t)*/ count(*) from t where no <= 1;
select /*+ full(t)*/ count(*) from t where no <= 10000;
select /*+ full(t)*/ count(*) from t where no <= 10000000;
|
하지만 인덱스 사용 시, 읽는 양에 비례하여 성능이 느려진다.
table full scan:
sequencial access, , multi block I/O
index table scan:
random access, single block I/O
보통 5~20% 수준은 약 10만건, 많아봐야 100만건 이내이다.
1000만건 가량의 테이블에서는 인덱스가 무의미해질 확률이 높아져간다. → 이정도 규모면 파티셔닝 고려가 필수다.
대량 데이터에서 인덱스 성능 급감현상
너무 데이터가 많아지면, 조회건수가 늘어난 양에 비해, 폭팔적으로 조회 성능이 느려지는 현상을 볼 수 있다
1) 데이터를 버퍼 캐시에서 찾게 될 가능성이 낮아지기 때문이다.
2) 인덱스를 메모리에 한번에 못 올리기 때문에, page 교체 오버헤드도 급격히 증가한다( 1~100 필요한데, 1~10 까지 밖에 못올리면, 1~10 버리고 11~20 올림. 근데 이후에 또 1~10 필요하면...? 이런식의 비효율 생김)
3) 인덱스 컬럼 기준으로 값이 같은 테이블 레코드가 '모여있을 가능성' 도 급격히 낮아진다. 따라서, 직전과 동일한 테이블 블록 읽을 확률, 즉 buffer pinning의 덕을 볼 확률이 급감한다.
온라인 트랜잭션 처리 프로그램
DW/OLAP/BATCH 프로그램
배치 프로그램 튜닝 VS 온라인 프로그램 튜닝
온라인 프로그램 튜닝
'특정' 고객의 확인번호를 받아서 조회
보통의 온라인 서비스는 소량의 데이터를 쓰고 읽는다. 따라서 큰 데이터 에서 정확히 필요한 소량의 데이터를 조회하는데 인덱스 사용이 성능에 이점을 준다.
또한, NL 조인도 효과적이다 ( 보통 inner는 가능한 index 사용하고, outer 도 index같이 사용해서 join한다)
배치 프로그램 튜닝
batch job의 경우에는 대량의 데이터 쓰기, 읽기 작업이 생긴다.
따라서, 항상 전체 범위 처리 기준으로 튜닝해야한다 (부분이 아닌 전체를 빠르게 처리) 이때, 보통 인덱스 와 NL 조인 보다 table full scan & hash join이 유리하다.
고객 코드가 'A001' 인 고객이 수백만명이라는 가정하에
아래와 같이, 기존 온라인 프로그램에서 사용한 쿼리에서 조건절만 바꿔서 사용하면 비효율이 생긴다.
위와 같이 하는 것보다, 아래와 같은 full scan + hash join 활용하는 방식이 차라리 더 나은 성능을 보인다.
하지만 이 경우, 조건절에 해당하지 않는 고객 데이터, 1년 초과 이력 데이터까지 읽는 비효율이 생긴다.
** from a,b >> a,b 를 crosejoin 한 값 가져오는 것.
** INDEX_FFS: [Hint]ACCESS 경로를 변경하는 힌트
이 힌트의 의미는 인덱스를 FAST FULL SCAN하라는 것인데 보통 인덱스에 대한 스캔은 단일 블록 스캔인데 반해 인덱스 패스트 풀 스캔은 Multi Block Scan 이다.
window 함수 활용
위에서, 고객 변경 이력 테이블을 2번 읽는 비효율을 없애기 위해 아래와 같이 윈도우 함수 사용이 가능하다.
초대용량 테이블에서는, 보통 파티션 + 병렬 처리 로 튜닝한다.
위 쿼리의 경우, 고객 변경 이력 테이블을 변경일시 기준으로 파티셔닝하면, 변경일시 조건에 해당하는 파티션만 골라서 full scan 하므로 부담을 크게 줄일 수 있다.
ex) 1년 단위로 테이블 나눈다.
그리고 최근 3년 에대한 데이터 조회 시, 최근 1년, 2년 ,3년 테이블 각각에 병렬적으로 Full Scan 쿼리 날려서 최적화 가능하다.
테이블 스캔이 블록 엑세스하는 과정
익스텐트 맵을 통해 읽을 블록들의 DBA 정보를 얻는다. → 같은 익스텐트 == 같은 데이터 블록 => block I/O 용이하다!
인덱스로 테이블 블록 엑세스하는 과정
리프 블록에서 읽은 ROWID를 분해해서 DBA(디스크 주소 정보) 를 얻고,
I/O 성능 개선을 위해 버퍼 캐시를 활용하기 위해 버퍼 캐시를 확인하고, 읽으려는 DBA를 해시 함수에 입력해서 hash chain을 찾고 여기서 버퍼 헤더를 찾는다.
**캐시 적재시 매번 같은 해시 함수 쓰기에 버퍼 헤더는 항상 같은 해시 체인에 연결되지만, 실제 데이터가 담긴 버퍼 블록은 매번 다른 위치에 캐싱 된다.
그리고 동시 액세스가 심한 경우에는 캐시 버퍼 채인 래치와 버퍼 lock 경합 등도 발생하는 등 생각보다 고비용이다.
+ index 사용하는데, 만약 테이블이 너무 커서 index 를 메모리에 1번에 담을 수 없으면, 성능 이슈로 이어진다.
인덱스 클러스터링 팩터
CF ( clustering factor ) == 인덱스 레코드 정렬 순서와 테이블 레코드 정렬의 일치하는 정도
CF가 좋으면 ' 테이블 액세스량에 비해 블록 I/O가 적게 발생' 한다.
** 어차피 레코드마다 건건이 블록 I/O하면 의미 없는데 왜 그럴까?
buffer pinning
인덱스의 ROWID로 테이블 액세스 과정에서, 오라클은 latch 획득 & 해시 체인 획득 과정을 거쳐 어렵게 찾아낸 '테이블 블록에 대한 메모리 주소값을 바로 버리지 않고 일단 유지한다.'
이로인해, 만약 인덱스에서 다음 레코드를 읽었는데, '직전과 같은 테이블 블록' 이 필요하면, latch 획득 & 해시 체인 스캔 과정을 생략하고 바로 테이블 블록을 읽을 수 있다.
따라서, CK가 높으면 buffer pinning으로 인해 같은 테이블 블록을 여러번 가져오는 비효율이 감소한다.
> 정확히는, '다음에 읽을 블록이 직전 블록과 일치' 하는 상황이 많이 생기면 좋은 것.
인덱스 손익분기점
table full scan은 1건을 가져오던, 전체를 가져오던 비용이 차이 없다.
|
// 아래 쿼리들을 table full scan 수행, 건수 상관없이 성능 동일.
select /*+ full(t)*/ count(*) from t where no <= 1;
select /*+ full(t)*/ count(*) from t where no <= 10000;
select /*+ full(t)*/ count(*) from t where no <= 10000000;
|
하지만 인덱스 사용 시, 읽는 양에 비례하여 성능이 느려진다.
table full scan:
sequencial access, , multi block I/O
index table scan:
random access, single block I/O
보통 5~20% 수준은 약 10만건, 많아봐야 100만건 이내이다.
1000만건 가량의 테이블에서는 인덱스가 무의미해질 확률이 높아져간다. → 이정도 규모면 파티셔닝 고려가 필수다.
대량 데이터에서 인덱스 성능 급감현상
너무 데이터가 많아지면, 조회건수가 늘어난 양에 비해, 폭팔적으로 조회 성능이 느려지는 현상을 볼 수 있다
1) 데이터를 버퍼 캐시에서 찾게 될 가능성이 낮아지기 때문이다.
2) 인덱스를 메모리에 한번에 못 올리기 때문에, page 교체 오버헤드도 급격히 증가한다( 1~100 필요한데, 1~10 까지 밖에 못올리면, 1~10 버리고 11~20 올림. 근데 이후에 또 1~10 필요하면...? 이런식의 비효율 생김)
3) 인덱스 컬럼 기준으로 값이 같은 테이블 레코드가 '모여있을 가능성' 도 급격히 낮아진다. 따라서, 직전과 동일한 테이블 블록 읽을 확률, 즉 buffer pinning의 덕을 볼 확률이 급감한다.
온라인 트랜잭션 처리 프로그램
DW/OLAP/BATCH 프로그램
배치 프로그램 튜닝 VS 온라인 프로그램 튜닝
온라인 프로그램 튜닝
'특정' 고객의 확인번호를 받아서 조회
보통의 온라인 서비스는 소량의 데이터를 쓰고 읽는다. 따라서 큰 데이터 에서 정확히 필요한 소량의 데이터를 조회하는데 인덱스 사용이 성능에 이점을 준다.
또한, NL 조인도 효과적이다 ( 보통 inner는 가능한 index 사용하고, outer 도 index같이 사용해서 join한다)
배치 프로그램 튜닝
batch job의 경우에는 대량의 데이터 쓰기, 읽기 작업이 생긴다.
따라서, 항상 전체 범위 처리 기준으로 튜닝해야한다 (부분이 아닌 전체를 빠르게 처리) 이때, 보통 인덱스 와 NL 조인 보다 table full scan & hash join이 유리하다.
고객 코드가 'A001' 인 고객이 수백만명이라는 가정하에
아래와 같이, 기존 온라인 프로그램에서 사용한 쿼리에서 조건절만 바꿔서 사용하면 비효율이 생긴다.
위와 같이 하는 것보다, 아래와 같은 full scan + hash join 활용하는 방식이 차라리 더 나은 성능을 보인다.
하지만 이 경우, 조건절에 해당하지 않는 고객 데이터, 1년 초과 이력 데이터까지 읽는 비효율이 생긴다.
** from a,b >> a,b 를 crosejoin 한 값 가져오는 것.
** INDEX_FFS: [Hint]ACCESS 경로를 변경하는 힌트
이 힌트의 의미는 인덱스를 FAST FULL SCAN하라는 것인데 보통 인덱스에 대한 스캔은 단일 블록 스캔인데 반해 인덱스 패스트 풀 스캔은 Multi Block Scan 이다.
window 함수 활용
위에서, 고객 변경 이력 테이블을 2번 읽는 비효율을 없애기 위해 아래와 같이 윈도우 함수 사용이 가능하다.
초대용량 테이블에서는, 보통 파티션 + 병렬 처리 로 튜닝한다.
위 쿼리의 경우, 고객 변경 이력 테이블을 변경일시 기준으로 파티셔닝하면, 변경일시 조건에 해당하는 파티션만 골라서 full scan 하므로 부담을 크게 줄일 수 있다.
ex) 1년 단위로 테이블 나눈다.
그리고 최근 3년 에대한 데이터 조회 시, 최근 1년, 2년 ,3년 테이블 각각에 병렬적으로 Full Scan 쿼리 날려서 최적화 가능하다.
'sql튜닝' 카테고리의 다른 글
| sql 최적화 - 2장 인덱스 - index scan 종류 (0) | 2025.02.19 |
|---|---|
| 1장 sql 처리 과정 (0) | 2025.02.19 |
