본문 바로가기
Oracle/운영

Full Table Scan 시 Direct Path Read ?

by 취미툰 2022. 10. 17.
반응형

운영 중 쿼리 튜닝 요청이 와서 실행계획을 보는 중 특이한 부분이 보여서 찾아보다가 정리를 하게 되었습니다.

 

제가 확인한 실행계획인데요, 해당 쿼리는 Parallel Query도 아니었고, 일반 SELECT 였는데  Table Full Scan이 발생하던 시기에 direct path read가 발생한것을 확인하였습니다.

SQL Plan Monitoring Details (Plan Hash Value=839247681)
=============================================================================================================================================================================
| Id   |            Operation             |    Name    |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |   Activity Detail    | Progress |
|      |                                  |            | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |     (# samples)      |          |
=============================================================================================================================================================================
|    0 | SELECT STATEMENT                 |            |         |      |           |        |     1 |          |      |       |          |                      |          |
|    1 |   HASH GROUP BY                  |            |       1 | 533K |           |        |     1 |          |      |       |          |                      |          |
|    2 |    FILTER                        |            |         |      |           |        |     1 |          |      |       |          |                      |          |
|    3 |     NESTED LOOPS                 |            |      54 | 533K |           |        |     1 |          |      |       |          |                      |          |
|    4 |      NESTED LOOPS                |            |      54 | 533K |           |        |     1 |          |      |       |          |                      |          |
| -> 5 |       TABLE ACCESS FULL          | TB_OGB0243 |      54 | 533K |        10 |     +1 |     1 |        0 | 8540 |   8GB |   100.00 | Cpu (5)              |      41% |
|      |                                  |            |         |      |           |        |       |          |      |       |          | direct path read (3) |          |
|    6 |       INDEX RANGE SCAN           | PK_OGB0249 |       1 |    3 |           |        |       |          |      |       |          |                      |          |
|    7 |      TABLE ACCESS BY INDEX ROWID | TB_OGB0249 |       1 |    4 |           |        |       |          |      |       |          |                      |          |
=============================================================================================================================================================================

 

제가 아는 direct path read 이벤트 발생조건 중에 모르는게 있나?라는 생각에 다시 direct path read에 대해서 찾아보게 되었고 아래에 정리하였습니다.

-----------------------------------------------------------------------------------------------------------------------------------------

Direct path read는 아래 상황에서 발생한다.
1.정렬작업을 위해 정렬 세그먼트(Sort segment)를 읽고 쓰는 경우. direct path read temp , direct path write temp 이벤트를 대기한다.
2.Parallel Query를 위해 데이터파일을 읽는 경우. direct path read 이벤트를 대기한다.
3.PDML(Parallel DML)이나 CTAS를 위해 데이터파일을 쓰는 경우. direct path write 이벤트를 대기한다.
4.NOCACHE 속성으로 생성된 LOB 세그먼트를 읽고 쓰는 경우. direct path read(lob) , direct path write(lob) 이벤트를 대기한다.
5.I/O 시스템이 데이터를 읽어서 오라클에 반환하는 속도보다 훨씬 빠른 속도로 버퍼를 요구할 때. 이 경우 오라클 성능개선을 위해 readahead I/O (이후에 읽을 것으로 판단되는 데이터를 미리 한꺼번에 읽는 I/O 작업을 말함)를 이용한다. 이 경우 direct path read 이벤트를 대기한다.

 


----------------------------------------------------------------------------------------------------------------------
그렇담.. 5번의 경우 때문에 위의 쿼리는 direct path read event가 발생했다고 판단,

테스트를 진행할 수 있는데 이것이 자동적으로 적용된 버전이 11g부터입니다.

 

출처 : http://wiki.gurubee.net/display/CORE/direct+path+read 

 

direct path read - [종료]코어 오라클 데이터베이스 스터디 - 개발자, DBA가 함께 만들어가는 구루비

Added by 이경화, last edited by 이경화 on 1월 14, 2011 1. direct path read 대기이벤트 1. Parallel Query 수행시 슬레이브 세션(Slave Session)이 수행하는 direct path I/O에 의해 발생한다. 2. 슬레이브 세션이 direct path r

wiki.gurubee.net

 

 

Parallel 이 아닌 일반 Select(Serial select라고 이야기하겠음) 일때도 direct path read가 일어나는 것을 제어해주는 파라미터는 히든 파라미터인 _serial_direct_read 입니다.

11g 이전의 버전에서는 해당 파라미터가 기본적으로 사용하지 않는 것으로 되어 있어서 사용할 때마다 아래의 명령어처럼 파라미터 변경 후에 사용하였어야 했습니다, 11g부터는 deafult value로 AUTO로 설정이 되었고 조건에 맞으면 serial select 인 경우 direct path read가 일어나게 되었던 것입니다.

## AUTO(default)/TRUE/FALSE/NEVER/ALWAYS
## 5개의 값으로 설정할 수 있음
alter session set "_serial_direct_read"=TRUE;

출처 : https://scidb.tistory.com/entry/Oracle11g-%EC%97%90%EC%84%9C-Full-Table-Scan%EC%9D%98-%EC%84%B1%EB%8A%A5-%ED%96%89%EC%83%81

 

Oracle11g 에서 Full Table Scan의 성능 향상

문제는 수동이라는 점 예전에 필자가 Full Table Scan 의 비밀 이라는 글에서 _serial_direct_read 파라미터를 true 로 수정하여 Full Table Scan의 성능을 빠르게 한것을 기억하는가? 이것은 Full Table Scan시에 Dis

scidb.tistory.com

 

그리고 해당 기능을 제어하는 히든파라미터가 두개가 더 있다고 나와있었지만, 테스트 해본 결과로는 작동여부에 영향을 미치지 않은 파라미터도 있었습니다. _small_table_threshold 와 _very_large_object_threshold 파라미터라고 합니다. 설명상으로는 _small_table_threshold 의 값은 블록 수이고, 설정한 블록수 보다 낮은 objects에 대해서 direct path read를 할 수 있게 설정하는 파라미터입니다.  _very_large_object_threshold 의 값은 MB이고, 이 값보다 큰 objects에 대해서 direct path read를 할 수 있게 설정하는 파라미터입니다. 

##각 히든파라미터의 default 값
SYS유저에서 수행
SQL> SELECT  a.ksppinm  "Parameter",
        b.ksppstvl "Value",
            decode(bitand(a.ksppiflg/256,1),1,'true','false') AS SES_MODIF,
              a.ksppdesc AS "Description"
    FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
  AND a.ksppinm like lower('_%very_large_object_threshold%') ; ##이부분을 변경하여 히든파라미터 값 조회



  Parameter                                Value           SES_MODIF  Description
---------------------------------------- --------------- ----------
_very_large_object_threshold             500             true       upper threshold level of object size for direct reads


Parameter                                Value           SES_MODIF  Description
---------------------------------------- --------------- ----------
_small_table_threshold                   9452            true       lower threshold level of table size for direct reads


Parameter                                Value           SES_MODIF  Description
---------------------------------------- --------------- ----------
_serial_direct_read                      auto            true       enable direct read in serial

 

테스트1(_small_table_threshold)

첫번째의 테스트에서는 블록수와 크기가 다른 테이블을 3개 생성 후_small_table_threshold 파라미터를 수정하여 direct path read 발생여부를 확인해보겠습니다.

 

1.테이블 생성 및 초기 확인

##테이블 생성
create table DR_TABLE_SMALL (col varchar2(100)) pctused 1 pctfree 99 tablespace users;
create table DR_TABLE_MIDDLE (col varchar2(100)) pctused 1 pctfree 99 tablespace users;
create table DR_TABLE_LARGE (col varchar2(100)) pctused 1 pctfree 99 tablespace users;

##블록 수 및 크기 확인
SQL> select OWNER,segment_name,bytes,blocks from dba_segments where segment_name like '%DR_TABLE%';

OWNER   SEGMENT_NAME      BYTES     BLOCKS    
------- ----------------- --------- --------- 
SYS     DR_TABLE_MIDDLE       65536         8
SYS     DR_TABLE_LARGE        65536         8
SYS     DR_TABLE_SMALL        65536         8

 

2.데이터 INSERT 하기

##SMALL TABLE
INSERT INTO DR_TABLE_SMALL
SELECT ROWNUM AS RN FROM DUAL CONNECT BY ROWNUM < 6000 ;

COMMIT;

##MIDDLE TABLE
INSERT INTO DR_TABLE_MIDDLE
SELECT ROWNUM AS RN FROM DUAL CONNECT BY ROWNUM < 50000 ;

COMMIT;


##LARGE TABLE
INSERT INTO DR_TABLE_LARGE
SELECT ROWNUM AS RN FROM DUAL CONNECT BY ROWNUM < 1000000 ;

COMMIT;

##확인
OWNER   SEGMENT_NAME      BYTES     BLOCKS    
------- ----------------- --------- --------- 
SYS	DR_TABLE_MIDDLE	        57	7296
SYS	DR_TABLE_LARGE	        1346	172288
SYS	DR_TABLE_SMALL	        7	896

 

3.파라미터 값(_small_table_threshold =1000)으로 변경하여 direct parh read 여부 확인

## buffer cache flush
## 메모리에 적재된 쿼리를 초기화하여 앞의 테스트 결과에 영향을 최소화
SQL> alter system flush buffer_cache;

System altered.


##파라미터 변경
alter session set "_small_table_threshold"=1000;


##확인
SELECT  a.ksppinm  "Parameter",
        b.ksppstvl "Value",
        decode(bitand(a.ksppiflg/256,1),1,'true','false') AS SES_MODIF,
        a.ksppdesc AS "Description"
  FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
  AND a.ksppinm like lower('_%small_table_threshold%') ;
  
  
Parameter                                Value           SES_MODIF  Description
---------------------------------------- --------------- ----------
_small_table_threshold                   1000            true       lower threshold level of table size for direct reads

 

4.실제로 블록수가 1000이하인 테이블에 대해서는 direct path read가 일어나지 않았고, 일반 full table scan이 발생하는 event들만 확인되었습니다. 172288개의 블록, 약1.3G의 large 테이블을 조회할 때만, direct path read event가 발생하였습니다.

즉, 테스트 결과로는 _small_table_threshold의 파라미터 수정으로 제어할 수 없었습니다.

##block가 1000이하인 small table 조회
SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_SMALL;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999		5999

##확인(v$session_event를 조회하여 해당 쿼리로 인한 direct path wait event를 확인)
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%';

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    3
db file scattered read                                                    22


##block가 1000이상인 middle 테이블 조회
SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_MIDDLE;
MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		9999		49999
 
 ##확인
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%'; 

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    4
db file scattered read                                                    94


##block가 많이 큰 large 테이블 조회
SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_LARGE;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999999		999999
 
##확인
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%';

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    7
db file scattered read                                                    95
direct path read                                                         393

 

테스트2(_very_large_object_threshold)

테이블은 기존에 만들어놓은 테이블 3개를 사용합니다. 세션의 초기화를 위해 세션 종료후 다시 붙어서 v$session_wait에서 조회되는값을 초기화 하여 줍니다.

 

1.세션 초기화 및 메모리 초기화 후 파라미터 변경

##세션 재접속
SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 17 16:42:36 2022

Copyright (c) 1982, 2017, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

##buffer_cache 초기화
SQL> alter system flush buffer_cache;

System altered.

##파라미터 변경 및확인
 SQL> alter session set "_very_large_object_threshold"=50;

Session altered.

  Parameter                                Value           SES_MODIF  Description
---------------------------------------- --------------- ----------
_very_large_object_threshold             50             true       upper threshold level of object size for direct reads

 

2, 테스트1과 마찬가지로 large 테이블만 direct path read가 발생하였습니다. 파라미터값이 제대로 적용되었다면, 약 57MB 인 MIDDLE TABLE도 direct path read가 발생하였어야 합니다.

SQL>  select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_SMALL;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999		5999                                                                                                   5999


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

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                   12
db file scattered read                                                    84


SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_MIDDLE;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		9999		49999                                                                                                 49999


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

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                   13
db file scattered read                                                   156


SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from  DR_TABLE_LARGE; 

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999999		999999


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

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                   56
db file scattered read                                                   171
direct path read                                                         607

 

 

테스트3(_serial_direct_read)

기존의 테이블을 사용하며, _serial_direct_read 의 값을 다섯가지 다 변경하여 direct path read의 여부를 확인해보겠습니다.

 

1.AUTO(default 일때)

크기가 큰 large table만 direct path read가 발생하였습니다.

##세션 재접속
SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 17 16:42:36 2022

Copyright (c) 1982, 2017, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

##buffer_cache 초기화
SQL> alter system flush buffer_cache;

System altered.

##파라미터 변경 및확인
SQL> alter session set "_serial_direct_read"=auto;

Parameter                                Value           SES_MODIF  Description
---------------------------------------- --------------- ----------
_serial_direct_read                      auto            true       enable direct read in serial

 

테스트

SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_SMALL;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999		5999                                                                                        5999


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

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    1
db file scattered read                                                    22


SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_MIDDLE;
MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		9999		49999
 
 ##확인
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%'; 

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    2
db file scattered read                                                    94


SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_LARGE;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999999		999999
 
 
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%';

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    5
db file scattered read                                                    95
direct path read                                                         603

 

2.TRUE

##세션 재접속
SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 17 16:42:36 2022

Copyright (c) 1982, 2017, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

##buffer_cache 초기화
SQL> alter system flush buffer_cache;

System altered.

##파라미터 변경 및확인
SQL> alter session set "_serial_direct_read"=true;

Parameter                                Value           SES_MODIF  Description
---------------------------------------- --------------- ----------
_serial_direct_read                      true            true       enable direct read in serial

테스트

AUTO와 마찬가지로 large table의 경우에만 direct path read가 발생하였습니다.

SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_SMALL;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999		     5999


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

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    1
db file scattered read                                                    22


SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_MIDDLE;
MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		9999		49999
 
 ##확인
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%'; 


EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    2
db file scattered read                                                    94




SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_LARGE;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999999		999999
 
 
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%';

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    5
db file scattered read                                                    95
direct path read                                                         608

 

 

 

3.ALWAYS

always의 경우 middle table부터 direct path read가 발생하였습니다.

##세션 재접속
SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 17 16:42:36 2022

Copyright (c) 1982, 2017, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

##buffer_cache 초기화
SQL> alter system flush buffer_cache;

System altered.

##파라미터 변경 및확인
SQL> alter session set "_serial_direct_read"=always;

Parameter                                Value           SES_MODIF  Description
---------------------------------------- --------------- ----------
_serial_direct_read                      always            true       enable direct read in serial

2.테스트

SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_SMALL;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999		     5999


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

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    1
db file scattered read                                                    22


SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_MIDDLE;
MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		9999		49999
 
 ##확인
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%'; 

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                   37
db file scattered read                                                    40
direct path read                                                          34



SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_LARGE;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999999		999999
 
 
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%';

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                   40
db file scattered read                                                    41
direct path read                                                         624

 

4.FALSE

auto,true와 마찬가지로 large table만 direct path read가 발생하였습니다.

##세션 재접속
SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 17 16:42:36 2022

Copyright (c) 1982, 2017, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

##buffer_cache 초기화
SQL> alter system flush buffer_cache;

System altered.

##파라미터 변경 및확인
SQL> alter session set "_serial_direct_read"=false;

Parameter                                Value           SES_MODIF  Description
---------------------------------------- --------------- ----------
_serial_direct_read                      false            true       enable direct read in serial

테스트

SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_SMALL;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999		     5999


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

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    1
db file scattered read                                                    22


SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_MIDDLE;
MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		9999		49999
 
 ##확인
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%'; 


EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    2
db file scattered read                                                    94




SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_LARGE;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999999		999999
 
 
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%';

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    5
db file scattered read                                                    95
direct path read                                                         645

 

 

5.never

유일하게 large table도 serial select로 읽었습니다. 

##세션 재접속
SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Oct 17 16:42:36 2022

Copyright (c) 1982, 2017, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

##buffer_cache 초기화
SQL> alter system flush buffer_cache;

System altered.

##파라미터 변경 및확인
SQL> alter session set "_serial_direct_read"=never;

Parameter                                Value           SES_MODIF  Description
---------------------------------------- --------------- ----------
_serial_direct_read                      never            true       enable direct read in serial

테스트

SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_SMALL;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999		     5999


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

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    1
db file scattered read                                                    22


SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_MIDDLE;
MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		9999		49999
 
 ##확인
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%'; 


EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    2
db file scattered read                                                    94




SQL> select /*DR TEST*//*+ FULL */ min(col),max(col),count(*) from DR_TABLE_LARGE;

MIN(COL)	MAX(COL)	 COUNT(*)
------------------------------------
 1		999999		999999
 
 
 SQL> select event, total_waits from gv$session_event
where sid = (select sid from v$mystat where rownum = 1)
and event like '%read%';

EVENT                                                            TOTAL_WAITS
---------------------------------------------------------------- -----------
db file sequential read                                                    5
db file scattered read                                                  1400

 

결론 : detail한 수치로 테이블이 direct path read 대상인지 아닌지는 복잡한 공식이 있습니다. 

기본적으로는 auto로 설정되어 있으며, never와 always를 사용시 유의미한 변화가 보였습니다. 

하지만 히든파라미터의 경우 oracle의 권장사항이 아니라면 수정하지 않는것이 좋습니다.

 

해당 테스트는 저의 full table scan 시의 direct path read 이해를 돕기위한 테스트로 확인하시면 될 것 같습니다. 

 

출처 : https://scidb.tistory.com/entry/Oracle11g-%EC%97%90%EC%84%9C-Full-Table-Scan%EC%9D%98-%EC%84%B1%EB%8A%A5-%ED%96%89%EC%83%81

 

Oracle11g 에서 Full Table Scan의 성능 향상

문제는 수동이라는 점 예전에 필자가 Full Table Scan 의 비밀 이라는 글에서 _serial_direct_read 파라미터를 true 로 수정하여 Full Table Scan의 성능을 빠르게 한것을 기억하는가? 이것은 Full Table Scan시에 Dis

scidb.tistory.com

 

반응형

댓글