Full Table Scan 의 비밀

Full table scans (FTS) 에 관한 안개가 서서히 걷히고 있다.
FTS 는 DW환경에서는 필수적이며 심지어 OLTP 시스템의 온라인 환경에서도 FTS 는 필요하다.
하지만 FTS 가 일어나면 DBMS에 여러가지 악영향을 끼친다.

첫째로는 I/O 가 2단계(디스크에서 Buffer Cache 에 올리고 다시 Buffer Cache 에서 Read 함)로 인한 성능저하다.
이말은 디스크에서 바로 읽어올수 있으면 성능이 향상된다는 의미이다.
두번째로는 2단계 I/O 로 인해여 Buffer Cache 에 있는 데이터 들이 Aging Out 이 될가능성이 있다는 것이다.

그렇다면 2가지의 악영향을 제거하고 FTS 를 어떻게 효율적으로 수행할수 있을까?
이질문에 답하기 위해서는 2가지의 경우를 생각해야만 한다.

1.Buffer Cache 를 우회하여 성능을 향상시켜야 하는경우(Direct Path Read) --> 빠른성능을 보장하기위함
  이경우의 문제점은 조회화면인 경우 에 Direct Path Read 를 실행해야 한다는 것이다.
  하지만 통상 Direct Path Read 는 Insert 시에 Append 힌트를 사용하거나 Parallel Process 를 사용해야만 했다.
   Parallel Process 를 이용하지 않고 Select 만 실행시키는 조회화면을 위해서 본 블로그에서는 이문제에대한 해법을 제시한다.
2.데이터를 Buffer Cache 에 올려서 Hit Ratio 를 올려야 하는경우(해당 데이터를 자주 FTS  해야 하는경우) 

이경우 필자의 해법은 단 2가지이다.

1.Buffer Cache 를 우회하여 성능을 향상시켜야 하는경우의 Solution:

"_serial_direct_read = true" 를 사용하라

기존의 관행적으로 수행되던 방식으로는 해법이 없다.
왜냐하면 Full Scan(테이블이나 인덱스)시에  Insert - Select 가 아니라 Select 만 수행할때는 Buffer cache 를 우회하여 빠른성능의 Direct I/O 를 사용할수 있는 방법이 없기 떄문이다.
물론 Insert - Select 라면 Append 힌트를 사용하면 Buffer cache 를 우회하여 빠른성능의 Direct I/O 를 사용할수가 있다는건 많이 알려진 사실이다.
혹자는 "SELECT 시에 Parallel Process 를 사용하면 된다" 라고 하지만 실제 Online 운영환경에서 Parallel Process 를 사용한다는건 얻는 이득보다 손실이 많기 때문에 견딜수 없다.
이럴때 사용할수 있는것이 _serial_direct_read 파라미터이다.
"_serial_direct_read = true" 로 바꾸면 single threaded multi block read  환경에서 direct path reads가 일어난다.  
위파라미터를 적용하고 FTS 를 실행하면 오라클은 체크포인트를 수행하여 해당 dirty buffer 들을 모두 디스크로 내리고 direct path reads를 발생시킨다.
아래의 실행결과를 보자

TEST 실행:

SQL> alter session set "_serial_direct_read" = true;
Session altered.

SQL> select avg(id) from test;

SQL> select event, total_waits from v$session_event
 2 where sid = (select sid from v$mystat where rownum = 1)
 3 and event like '%read%';

EVENT TOTAL_WAITS
------------------------------ -----------
direct path read 124                                      --> single mode select 에서 direct path read 만 발생됨.
Labels: buffer cache _serial_direct_read direct path read full table scan parallel



2.데이터를 Buffer Cache 에 올려서 Hit Ratio 를 올려야 하는경우:

"SELECT 문에 CACHE 힌트를 사용하라"

위방법을 사용하면 해당 데이터가 Aging Out 될 확률이 현저히 줄어든다.
하지만 대용량 데이터의 Select 시에 이방법을 사용하면 무리가 따르기 마련이다. 
그런경우 아래의 방법을 사용해야 한다.

"Buffer Cache 의 Keep Pool 을 사용하라"

이방법의 보다 자세한 내역은 엑셈의 조동욱님 블로그에 자세히 나와 있다.
정말 주인장 동욱님의 포스가 느껴지는 블로그이다.


결론

1.single mode(Parallel 사용안함) 조회시 FTS 를 사용할경우 시에 Buffer Cache 를 우회하는 유일한 방법은 "_serial_direct_read = true" 이다.
  반드시 해당 세션에서만 적용시켜야 함을 잊지말자.
  upgrade 시에 해당 파라미터는 없어질수 있으므로 유의 해야한다.
  또한 _로 시작하는 파라미터는 default 값을 바꾸면 매우 위험하다는걸 알아야 한다.
2.FTS 시에 데이터를 Aging Out 되지않게 하는방법은 Select 문에 Cache 힌트를 사용하거나 해당테이블에 Keep Pool 을 적용하는것이다.

물론 11g 라면 Result Cache를 사용하는 것이 바람직 하다.
Result Cache는 shared pool에 Result Cache Memory로 불리는 영역에 SQL 및 PL/SQL funtion의 결과를 저장하는 것이다.
특정 query가 반복적으로 수행될 때 이 결과를 캐시하여 그 다음 부터는 해당 query를 다시 execute하는 것이 아니라 캐시 메모리에 저장된 결과값을 그대로 가지고 오게 된다.