오라클 19c FULL TABLE SCAN (FTS)시 direct path read 이벤트 관련

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c FULL TABLE SCAN (FTS)시 direct path read 이벤트 관련

이전 테스트(group by 테스트) 진행중 테이블 full 스캔을 할때 direct path read 가 발생하는것을 보고 궁금해서 찾아봄
참고 ( 오라클 19c group by 컬럼 나열 및 집계함수 적용 성능 비교 https://positivemh.tistory.com/1034 )

 

 

오라클에서 기본적으로 Direct Path I/O 는 아래 경우에만 동작함
- CTAS 작업
- APPEND 힌트를 사용한 insert 작업
- PARALLEL 힌트를 사용한 병렬쿼리 FULL 스캔
- PARALLEL_INDEX 힌트를 사용한 병렬쿼리 INDEX 스캔
- PARALLEL 힌트를 사용한 병렬 DML
- TEMP를 읽고 쓰는 작업
- NOCACHE 옵션으로 LOB 컬럼 읽는 작업
- Datapump 사용시
- 전통 EXP/IMP 중 Direct=Y 옵션 사용시
- SQL*Loader 중 Direct=Y 옵션 사용시

 

 

이 경우가 아닌 일반 테이블을 FULL 스캔 할때는 
테이블 블록이 데이터 파일에서 버퍼 캐시로 읽혀지기 때문에 db file scattered read 이벤트가 발생해야함

그런데 일반 테이블을 FULL 스캔(병렬X 직렬) 할 때 10046 트레이스를 보니 db file scattered read 가 아닌 direct path read 이벤트가 발생하는걸 발견함

 

 

10046 테스트
샘플 테이블 생성

1 2 3 4 5 6 7 8 9

SQL> conn / as sysdba drop table test_emp; create table big_emp as select * from emp connect by level <= 6; select count(*) from test_emp;     COUNT(*) ----------    8108730

 

 

버퍼캐시 플러쉬

1 2 3

SQL> alter system flush buffer_cache;   System altered.

 

 

테이블 풀스캔 후 10046 트레이스 확인
(오라클 19c 10046 trace 및 tkprof 자동수행 스크립트 https://positivemh.tistory.com/960 활용)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

$ sh 10046.sh $ cat /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_37734_t1_tkprof.txt ********************************************************************************   SQL ID: fqhvntk5d9gbr Plan Hash: 2674116386   select count(*) from  test_emp     call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        1      0.10       0.10      47730      47733          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        3      0.10       0.10      47730      47733          0           1   Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 78 Number of plan statistics captured: 1   Rows (1st) Rows (avg) Rows (max)  Row Source Operation ---------- ---------- ----------  ---------------------------------------------------          1          1          1  SORT AGGREGATE (cr=47733 pr=47730 pw=0 time=104406 us starts=1)    8108730    8108730    8108730   TABLE ACCESS FULL TEST_EMP (cr=47733 pr=47730 pw=0 time=110225 us starts=1 cost=13110 size=0 card=8108730)     Elapsed times include waiting on following events:   Event waited on                             Times   Max. Wait  Total Waited   ----------------------------------------   Waited  ----------  ------------   PGA memory operation                            5        0.00          0.00   SQL*Net message to client                       1        0.00          0.00   Disk file operations I/O                        1        0.00          0.00   db file sequential read                         1        0.00          0.00   direct path read                             6003        0.00          0.03 ***   SQL*Net message from client                     1        0.00          0.00 *****************************************************1***************************

direct path read 이벤트가 많이 발생함

 

 

원인은 11g 부터 도입된 _serial_direct_read 파라미터 였음
_serial_direct_read 파라미터는 FULL 스캔시(병렬쿼리가 아닌 직렬 방식) direct path read를 가능하게 제어 해주는 파라미터임
파라미터 값이 auto(기본값) 일 경우 _small_table_threshold 파라미터 값보다 대상 테이블의 blocks(dba_tables) 값이 크다면 Direct Path I/O가 동작함
(여기서 참조하는 blocks 갯수는 _direct_read_decision_statistics_driven 파라미터와도 연관이 있다고함 기본값은 true임)
파라미터 값이 never 일 경우 절대 direct path read를 수행하지 않음
이외에 true, false, always 옵션이 존재함
참고로 _small_table_threshold 파라미터 기본값은 _db_block_buffers 파라미터의 2%임

 

 

관련 히든 파라미터 조회

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

SQL> set lines 200 pages 1000 col desc for a70 col name for a40 col current_value for a15 col default_value for a15 col default_t_f for a15 select ksppinm "name", ksppstvl "current_value", b.ksppstdfl "default_value", b.ksppstdf "default_t_f", ksppdesc "desc" from sys.x$ksppi a, sys.x$ksppcv b where 1=1 and a.indx=b.indx AND SUBSTR(a.KSPPINM, 1, 1) = '_' and a.ksppinm in ('_serial_direct_read', '_small_table_threshold',  '_very_large_object_threshold', '_direct_read_decision_statistics_driven', '_db_block_buffers') order by 1;   name                                     current_value   default_value   default_t_f     desc ---------------------------------------- --------------- --------------- --------------- ---------------------------------------------------------------------- _db_block_buffers                        17622           0               TRUE            Number of database blocks cached in memory: hidden parameter _direct_read_decision_statistics_driven  TRUE            TRUE            TRUE            enable direct read decision based on optimizer statistics _serial_direct_read                      auto            auto            TRUE            enable direct read in serial _small_table_threshold                   352                             TRUE            lower threshold level of table size for direct reads _very_large_object_threshold             500             500             TRUE            upper threshold level of object size for direct reads

*_db_block_buffers 값이 17622임, _small_table_threshold 값은 352임(_db_block_buffers 17622의 2%임)

 

 

파라미터에 따른 변화 테스트

테스트1. _small_table_threshold 값보다 테이블의 블록이 많은 경우

테스트2. _small_table_threshold 값보다 테이블의 블록이 적은 경우

테스트3. _serial_direct_read 값이 never인 경우

 

 

샘플 테이블 생성 후 통계 수집

1 2 3 4 5

SQL> conn / as sysdba drop table test_emp purge; create table test_emp as select * from emp, dual connect by level <= 6; exec dbms_stats.gather_table_stats('SYS', 'TEST_EMP');

 

 

블록 갯수 확인

1 2 3 4 5 6 7 8 9

SQL> col table_name for a20 select table_name, num_rows, blocks  from dba_tables  where table_name = 'TEST_EMP';   TABLE_NAME             NUM_ROWS     BLOCKS -------------------- ---------- ---------- TEST_EMP                8108730      50029

50029개 블록을 가지고 있음

 

 

테스트1. _small_table_threshold 값보다 테이블의 블록이 많은 경우
현재 _serial_direct_read 파라미터는 auto이고, _small_table_threshold 파라미터 값은 352임
_small_table_threshold(352) 보다 test_emp 테이블의 블록(50029)이 더 많은 상태임

 

 

버퍼캐시 플러쉬

1 2 3

SQL> alter system flush buffer_cache;   System altered.

 

 

test_emp 테이블 조회 후 direct path read 사용량 확인

1 2 3 4 5 6 7 8 9 10 11 12

SQL> select /*+ test1 */ count(*) from test_emp;     COUNT(*) ----------    8108730   select sql_id, substr(sql_text, 1, 30) "sql_text", direct_reads, buffer_gets from v$sql where sql_text like '%test1%';   SQL_ID        sql_text                                                     DIRECT_READS BUFFER_GETS ------------- ------------------------------------------------------------ ------------ ----------- 4f4cymkj342j2 select /*+ test1 */ count(*) f                                      50029       50049

DIRECT_READS 값이 50029임(Direct Path I/O가 작동함)

 

 

버퍼캐시 플러쉬

1 2 3

SQL> alter system flush buffer_cache;   System altered.

 

 

테스트2. _small_table_threshold 값보다 테이블의 블록이 적은 경우
_small_table_threshold 파라미터 50030으로 변경

1 2 3

SQL> alter session set "_small_table_threshold" = 50030;   Session altered.

 

 

현재 _serial_direct_read 파라미터는 auto이고, _small_table_threshold 파라미터 값은 50030임
_small_table_threshold(50030) 보다 test_emp 테이블의 블록(50029)이 더 적은 상태임

 

 

test_emp 테이블 조회 후 direct path read 사용량 확인

1 2 3 4 5 6 7 8 9 10 11 12

SQL> select /*+ test2 */ count(*) from test_emp;     COUNT(*) ----------    8108730   select sql_id, substr(sql_text, 1, 30) "sql_text", direct_reads, buffer_gets from v$sql where sql_text like '%test2%';   SQL_ID        sql_text                                                     DIRECT_READS BUFFER_GETS ------------- ------------------------------------------------------------ ------------ ----------- a0m87v5jnf0u4 select /*+ test2 */ count(*) f                                          0       50110

DIRECT_READS 값이 0임(Direct Path I/O가 미작동함)

 

 

테스트3. _serial_direct_read 값이 never인 경우
_small_table_threshold 파라미터 초기값으로 변경 및 _serial_direct_read 파라미터 never 으로 변경

1 2 3

SQL>  alter session set "_small_table_threshold" = 352; alter session set "_serial_direct_read" = never;

 

 

버퍼캐시 플러쉬

1 2 3

SQL> alter system flush buffer_cache;   System altered.

 

 

test_emp 테이블 조회 후 direct path read 사용량 확인

1 2 3 4 5 6 7 8 9 10 11 12

SQL> select /*+ test3 */ count(*) from test_emp;     COUNT(*) ----------    8108730   select sql_id, substr(sql_text, 1, 30) "sql_text", direct_reads, buffer_gets from v$sql where sql_text like '%test3%';   SQL_ID        sql_text                                                     DIRECT_READS BUFFER_GETS ------------- ------------------------------------------------------------ ------------ ----------- 29xpytcp085n9 select /*+ test3 */ count(*) f                                          0       50042

DIRECT_READS 값이 0임(Direct Path I/O가 미작동함)
_small_table_threshold 값이 352임에도 _serial_direct_read 값이 never로 설정되어 있어서 Direct Path I/O가 미동작함

 

 

주의사항
이처럼_serial_direct_read 파라미터가 auto로 설정되어 있을때 테이블 블록이 _small_table_threshold 값보다 큰 경우 Direct Path I/O가 동작함
이 말은 쿼리에서 특정 테이블이 FULL 하는 실행계획으로 풀릴 때 쿼리 수행시마다 캐싱(버퍼캐시)을 전혀 사용하지 않고 매번 Direct Path I/O를 한다는 뜻임
쿼리가 자주 수행되는 경우라면 성능 저하를 겪을 수 있음
참고 : _small_table_threshold 파라미터값 조정을 통한 시스템 성능 향상 사례 ( https://cafe.naver.com/dbian/1673 )
또한 block cleanout 관련 이슈도 발생할 수 있음
참고 : Direct path read 와 block cleanout 그리고 latch: row cache objects ( https://engineering-skcc.github.io/oracle%20tuning/adaptive-direct-path-load/ )

 

 

결론 :
_serial_direct_read 파라미터가 auto일때
_small_table_threshold 값보다 대상 테이블의 블록이 더 많은 경우 Direct Path I/O 가 동작함
_small_table_threshold 값보다 대상 테이블의 블록이 더 적은 경우 Direct Path I/O 가 미동작함
_serial_direct_read 파라미터가 never 일때 Direct Path I/O 가 미동작함
direct path read 이벤트가 많이 발생하는 경우 반복적으로 direct path read를 사용하는 FULL 스캔 쿼리가 없는지
, _small_table_threshold 파라미터 값은 적절한지 확인이 필요함
block cleanout 이슈가 발생할 경우 원인 분석 후 변경이 많이 발생되는 테이블들을 대상으로
주기적으로 수동 cleanout 시키는 방법을 고려할 수 있음(non-direct path read 방식으로 FULL 스캔 수행)

 

 

참조 : 

https://bae9086.tistory.com/418
https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?mod=document&uid=235913
https://scidb.tistory.com/entry/Full-Table-Scan-의-비밀
https://scidb.tistory.com/entry/Oracle11g-에서-Full-Table-Scan의-성능-향상
https://cafe.naver.com/dbian/1673
https://engineering-skcc.github.io/oracle%20tuning/adaptive-direct-path-load/
오라클 성능 고도화 원리와 해법 1 456p
https://positivemh.tistory.com/150
https://www.dba-oracle.com/t_large_small_table_threshold.htm
https://www.dba-oracle.com/t_very_large_object_threshold.htm
https://avdeo.com/tag/_small_table_threshold/
https://oracle-tech.blogspot.com/2014/04/smalltablethreshold-and-direct-path.html
https://energ.tistory.com/entry/Serial-Direct-Scan-기능-소개