[오라클] I/O SQL튜닝 방법 정리(1)

db file sequential read

---

db file sequential read 대기 이벤트는 싱글 블록 I/O 수행 시에 발생하는 대기 이벤트입니다. 한번의 싱글 블록 I/O가 발생할 때마다 한 번의 db file sequential read이벤트 대기가 발생합니다. 싱글 블록 I/O는 파일로부터 하나의 블록을 읽는 모든 작업들에서 발생 가능하며 일반적으로 인데스 스캔 및 ROWID에 의한 테이블 액세스 시에 발생합니다.

* Parameter

  P1(파일#), P2(블록#), P3(블록 수(항상 1))

* Wait Time

  I/O를 수행하기 위해 대기한 시간을 의미

* 일반적인 문제 상황 및 개선방법

  - 원인: 비효율적인 인덱스 스캔으로 인한 과다한 싱글 블록 I/O

  - 진단 방법: SQL 실행 계획 확인(DBMS_XPLAN 패키지를 이용)

                  인덱스 Clustering Factor 확인

  - 개선 방법: SQL 튜닝, 인덱스 Clustering Factor

  - 원인: Row changing 및 Row migration에 의한 추가적인 싱글 블록 I/O

  - 진단 방법: ANALYZE TABLE 수행

                  V$SYSSTAT 및 V$SESSTAT의 table fetch continued row 값의 증가 여부 확인

  - 개선 방법: PCTFREE를 작게 해서 테이블 재생성, 더 큰 블록 크기를 이용해서 테이블 재생성

  - 원인: 선택도(Selectivity)가 좋지 않은 인덱스 사용

  - 진단 방법: 실행계획 확인

  - 개선 방법: SQL 튜닝, 사용인덱스 변경 또는 신규인덱스 생성

인덱스 Clustering Factor란?

-- 인덱스 Clustering Factor 확인 방법

SELECT t.owner,
	i.index_name,
	t.blocks AS t_blocks,
	i.num_rows,
	i.clustering_factor
FROM   dba_indexes i,
	dba_tables t
WHERE  i.table_name = t.table_name
AND	i.table_owner = t.owner
AND	i.index_name  = :INDEX_NAME;

인덱스 Clustering Factor(이하 CF)는 인덱스 키 칼럼 기준으로 테이블의 데이터들이 얼마나 잘 정렬(군집)되어 있는지를 나타내는 수치입니다. CF는 메모리에 단 하나의 블록만을 담을 수 있는 공간이 있다고 가정하고, 인덱스 스캔 시에 테이블 블록을 몇 번 액세스해야 하는지를 계산한 값입니다.

즉 CF가 높을수록 db file sequential read 대기가 증가할 수 있는 가능성이 존재합니다. CF 값은 ANALYZE 명령문이나, DBMS_STATS 패키지를 이용해서 확인할 수 있습니다. 인덱스에 대해 통계정보를 생성하면 DBA_INDEXES.CLUSTERING_FACTOR에 CF의 값이 입력됩니다. CF 수치는 테이블의 블록 수에 가까울 수록 좋고, 로우 수에 가까울수록 좋지 않습니다. 성능 문제의 원인이 CF인 경우, 테이블을 인덱스의 정렬순서와 동일한 순서로 재생성함으로써 해결할 수 있습니다. 하지만 테이블 재생성은 해당 테이블을 참조하는 다른 인덱스 성능에 영향을 미치므로 신중하게 고려해야 합니다.

로우 체이닝(ROW CHAINING), 로우 마이그레이션(ROW MIGRATION) 이란?

SELECT value
FROM   v$sysstat
WHERE  name = `table fetch continued row`;

SELECT a.sid, b.value
FROM   v$session a, v$sesstat b, v$statname c
WHERE  a.sid=b.sid
and     b.statistic#=c.statistic#
and     c.name=`table fetch continued row`;

인덱스의 ROWID를 이용해서 테이블을 액세스하는 경우, ROW CHAINING이나 ROW MIGRATION이 발생한 로우에 대해서는 추가적인 디스크 I/O가 발생하게 되면 db file sequential read 대기가 증가하게 됩니다. ANALYZE 명령을 이용해 통계정보를 생성하면 DBA_TABLES 뷰의 CHAIN_CNT 칼럼에 CHAINING이나 MIGRATION이 발생한 로우 수가 기록됩니다. 또한 위와 같은 SQL 문을 이용해서 현재 시스템 상에서 발생하고 있는 ROW CHAINING이나 ROW MIGRATION 발생 내역을 확인할 수 있습니다.

db file scattered read

---

db file scattered read 대기 이벤트는 멀티 블록 I/O시 발생하는 대기 이벤트입니다. Full Table Scan 및 Index Fast Full Scan을 수행하는 경우, 성능 향상을 위해서 여러 개의 블록을 한번에 읽는 멀티블록 I/O를 수행합니다. 멀티 블록 I/O를 한번 수행할 때마다 Physicial I/O가 끝나기를 기다리게 되며, db file scattered read 이벤트를 대기하게 됩니다. 멀티 블록 I/O는 OS마다 최대값에 한계가 있으며, DB_FILE_MULTIBLOCK_READ_COUNT 파라미터로 지정된 값만큼 수행합니다.

* Parameter

  P1(파일#), P2(시작 블록#), P3(블록 수)

* Wait Time

  I/O를 수행하기 위해 대기한 시간

* 일반 문제상황 및 개선방법

  - 원인: 비효율적인 Full Table Scan으로 인한 과도한 멀티 블록 I/O

  - 진단 방법: SQL 실행 계획 확인(DBMS_XPLAN 패키지를 이용)

  - 개선 방법: 파티션 적용, 인덱스 생성, KEEP BUFFER 적용, 

                   DB_FILE_MULTIBLOCK_READ_COUNT(MBRC) 설정 값 증가, 큰 사이즈의 블록 사용

  - 원인: 버퍼 캐시의 크기가 지나치게 작아 반복적으로 Physical I/O가 발생

  - 진단 방법: SQL 실행 계획 확인(DBMS_XPLAN 패키지를 이용)

  - 개선 방법: 다중 버퍼 풀 사용, 버퍼 캐시의 최적화

  - 원인: 불필요한 Index Fast Full Scan 사용으로 인한 과도한 멀티 블록 I/O

  - 진단 방법: SQL 실행계획 확인(DBMS_XPLAN 패키지를 이용)

  - 개선 방법: SQL문 튜닝

Physical I/O 분류

Physical I/O는 Conventional Path I/O와 Direct Path I/O로 나누어집니다.

Conventional Path I/O는 일반적으로 알고있는 버퍼 캐시를 경유하여 블록을 읽는 작업을 의미합니다. Direct Path I/O는 데이터파일에 있는 블록이 버퍼 캐시를 거치지 않고 PGA로 올리는 것입니다. Direct Path I/O가 발생하면, I/O 작업이 발생되기 전에 체크 포인트가 발생됩니다. 이렇게 되면 더티 버퍼를 데이터 파일에 쓰게 되어 데이터 파일과 버퍼 캐시의 내용에 대해서 동기화 한 후 Direct Path I/O가 발생하게 됩니다.

Index Full Scan vs. Index Fast Full Scan

인덱스 스캔에서 발생될 수 있는 성능문제는 넓은 범위 인덱스 스캔에 의한 I/O 발생량을 많이 일으키는 경우가 거의 대부분입니다. 필연적으로 많은 양의 인덱스 스캔이 발생되어야만 하고, 정렬이 필요 없는 경우라면 싱글 블록 I/O가 발생되는 Index Full Scan이 아닌 Index Fast Full Scan을 사용하여 멀티 블록 I/O로 유도하는 것이 성능적인 측면에서 바람직합니다.

-- Index Fast Full Scan 제어 파라미터
_FAST_FULL_SCAN_ENABLED=TRUE(Default=TRUE)

-- Index Fast Full Scan 유도 힌트
/*+ index_ffs(table_alias index_name) */

DB_FILE_MULTIBLOCK_READ_COUNT(MBRC) 설정

alter session set db_file_multiblock_read_count = 1000;

select /*+ full(a) */ count(*) from big_table a;

 

-- 아래의 예제는 10046 트레이스 파일로부터 발췌한 것입니다.

-- 시스템에서 허용할 수 있는 최대 MBRC는 128 블록임을 알 수 있습니다.

 

WAIT #1: nam=`db file scattered read` ela=17946 p1=6 p2=56617 p3=128

WAIT #1: nam=`db file scattered read` ela=21055 p1=6 p2=56745 p3=128

WAIT #1: nam=`db file scattered read` ela=17628 p1=6 p2=56873 p3=128

WAIT #1: nam=`db file scattered read` ela=29881 p1=6 p2=57001 p3=128

WAIT #1: nam=`db file scattered read` ela=33220 p1=6 p2=57129 p3=128

...

 

높은 MBRC 수치는 옵티마이저가 Full Table Scan을 선호하도록 영향을 줄 수 있습니다. 적절한 수치는 애플리케이션(DSS 또는 OLTP)에 따라 다릅니다. 높은 MBRC 수치는 Full Table Scan 수행을 좀 더 빠르게 수행할 수 있도록 하므로, 배치 처리시 유리할 수 있습니다. MBRC 수치는 sstiomax, DB_BLOCK_SIZE 및 DB_BLOCK_BUFFERS 등 몇 가지 요소에 따라 좌우됩니다. 사용 환경에 맞춰 쉽게 설정하기 위해서는, 위의 예에서 보는 바와 같이 MBRC에 아주 큰 값을 주고 오라클이 시스템에서 처리 가능한 최대값으로 수행하도록 합니다. 그 후, Full Table Scan을 수행하는 SQL을 실행시킨 뒤 V$SESSION_WAIT 뷰를 조회하면 됩니다. 그러면 db file scattered read 대기 이벤트의 P3 파라미터의 수치가 현재 시스템의 최댓값이 됩니다. 

다른 방법은 10046 트레이스 이벤트를 설정하는 것입니다. 이 최댓값은 데이터베이스 레벨에 설정하기 보다는 Full Table Scan의 수행속도를 향상시킬 필요성이 있는 세션에 대해 설정해야 합니다.

direct path read

---

direct path read 이벤트 대기는 Parallel Query 수행 시 슬레이브 세션(Slave Session)이 수행하는 direct path I/O에 의해 발생합니다. direct path I/O는 SGA 내의 버퍼 캐시를 거치지 않고 세션의 PGA로 직접 블록을 읽어 들이는 것으로 direct read는 I/O 방식(synchronous I/O, asynchronous I/O)에 상관없이 수행될 수 있으나 하드웨어 플랫폼과 DISK_ASYNCH_IO 파라미터에 영향을 받습니다.

* Parameter

  P1(절대(absolute)파일#), P2(시작 블록#), P3(블록 수)

* Wait Time

  I/O를 수행하기 위해 대기한 시간을 의미

* 일반 문제상황 및 개선방법

  - 원인: Parallel Query 수행 시 슬레이브 세션이 수행하는 direct path I/O

  - 진단 방법: SQL 실행계획 확인(DBMS_XPLAN 패키지를 이용)

  - 개선 방법: SQL 튜닝 > Parallel Query 자체의 성능 개선

Parallel Query의 성능 향상

Parallel Query를 수행하는 과정에서의 direct path read 대기는 필연적인 것으로 이 대기 자체를 튜닝하는 것은 불가능합니다. 오히려 SQL 튜닝을 통해 Parallel Query 자체의 성능을 개선하는 것이 올바른 접근 방법입니다. 시스템의 용량에 비해 불필요하게 Parallel Query를 수행하는 것은 오히려 성능을 저하시키는 요인이 됩니다. 한가지 기억할 것은 데이터 파일에 대해 직접 읽기 작업을 수행하기 전에 읽기의 대상이 되는 객체의 더티 블록이 데이터 파일에 기록이 되어야 한다는 것입니다. 즉 체크 포인트가 발생하게 됩니다. 이 작업을 수행하는 동안 코디네이터 세션은 enq: TC - connection 대기 이벤트를 발생시킵니다.

만약 병렬 쿼리(parallel query) 슬레이브에서 direct reads가 발생한다면, 병렬 스캔(parallel scan)이 parent SQL문에 적합한지와 슬레이브의 개수가 적당한지 확인해야 합니다. 또한 쿼리 슬레이브들이 시스템의 CPU와 디스크 자원을 모두 점유하지 않는지도 확인해야 합니다.

Direct Path I/O

 - 정렬작업을 위해 정렬 세그먼트(Sort segment)를 읽고 쓰는 경우, direct path read temp, direct path write temp 이벤트를 대기합니다.

 - Parallel Query를 위해 데이터파일을 읽는 경우, direct path read 이벤트를 대기합니다.

 - Parallel DML(PDML), CTAS를 위해 데이터파일을 쓰는 경우, direct path write 이벤트를 대기합니다.

 - NOCACHE 속성으로 생성된 LOB 세그먼트를 읽고 쓰는 경우 direct path read(lob), direct path write(lob) 이벤트를 대기합니다.

 - I/O 시스템이 데이터를 읽어서 오라클에 반환하는 속도보다 훨씬 빠른 속도로 버퍼를 요구할 때, 오라클 성능 개선을 위해 readahead I/O(이후에 읽을 것으로 판단되는 데이터를 미리 한꺼번에 읽는 I/O 작업)을 이용합니다. 이경우 direct path read 이벤트를 대기합니다.

오라클의 I/O는 기본적으로 SGA(버퍼 캐시)를 경우합니다. 하지만 특수한 상황에서는 SGA를 우회해서 PGA에 데이터를 올립니다. 데이터를 공유할 필요가 없을 때는 버퍼 캐시에 데이터를 적재하는 과정에서 발생하는 오버헤드를 피함으로써 성능을 개선하는 것이 가능합니다. 버퍼 캐시내의 변경된 블록을 데이터파일에 기록하는 것은 DBWR 프로세스 고유의 작업입니다. 반면 버퍼 캐시를 우회하는 쓰기 작업은 개별 프로세스가 직접 수행하게 됩니다. 이처럼 버퍼 캐시를 우회하는 I/O 작업을 direct path I/O라고 부릅니다.

_DB_FILE_DIRECT_IO_COUNT의 조정
_DB_FILE_DIRECT_IO_COUNT 히든 파라미터의 값이 direct path I/O에서의 최대 I/O 버퍼 크기를 결정합니다. 오라클 9i부터 이 값은 기본적으로 1M의 값을 가집니다. 하지만 실제로는 O/S나 하드웨어 설정에 따라 최댓값이 결정됩니다. 이 값을 높이면 Parallel Query의 성능이 높아질 수도 있으나, 대부분 실제 사용 가능한 값은 1M보다 작은 값이므로 실제로는 변경할 필요가 없습니다.

 

 

direct path read와 undo

ERROR at line 1 :

 

ORA-12801: error signaled in parallel query server P002

 

ORA-01555: snapshot too old: rollback segment number 68 with name

"_SYSSMU68$" too small

 

-- PQ 슬레이브 세션이 데이터 파일에 대해 direct read를 수행하면서 변경된 블록을 발견하면 언두 데이터를 참조

 

Direct path read가 비록 데이터파일에서 직접 데이터를 읽지만, 언두를 참조하는 메커니즘은 동일합니다. 즉, direct path read는 SGA(Shared Global Area)를 경유하지 않을 뿐, 읽기 일관성(Read consistency)을 보장하는 방법은 동일합니다. 이것을 증명하는 방법은 크기가 작은 언두 테이블스페이스(Undo tablespace)를 생성한 후, Parallel Query를 수행하면서 다른 세션에서 DML을 과다하게 수행할 때 ORA-01555(Snapshot too old)에러가 나는 것을 관찰하는 것입니다. 

DB_FILE_DIRECT_IO_COUNT

DB_FILE_DIRECT_TO_COUNT 파라미터는 direct path read 성능에 영향을 미칠 수 있습니다. 해당 파라미터는 direct reads, direct writes에 대한 최대 I/O 버퍼크기로 설정해야 합니다. 오라클 8i까지는 대부분의 플랫폼에서 기본 설정 값은 64 블록이었습니다. 따라서 DB_BLOCK_SIZE가 8K인 경우 direct reads, direct writes에 대한 최대 I/O 버퍼크기는 512K(8K*64)입니다. 최대 I/O 버퍼 크기는 하드웨어의 한계값에 의해서도 제한됩니다.

오라클 9i에서는 DB_FILE_DIRECT_IO_COUNT 파라미터는 hidden 파라미터로 변경되었고, 블록수가 아니라 바이트(BYTE) 단위로 변경되었습니다. 오라클 9i의 기본 설정 값은 1MB입니다. 실질적인 direct I/O 크기는 하드웨어 환경설정(configuration) 및 한계값에 의해서도 영향을 받습니다.

 

Direct Read I/O 크기 알기

WAIT #1: nam='direct path read' ela=4 p1=4 p2=86919 p3=8

WAIT #1: nam='direct path read' ela=5 p1=4 p2=86927 p3=8

WAIT #1: nam='direct path read' ela=10 p1=4 p2=86935 p3=8

WIAT #1: nam='direct path read' ela=39 p1=4 p2=86943 p3=8

...

 

direct read를 수행하는 세션에 대해 10046 트레이스 이벤트를 레벨 8로 설정합니다. P3 파라미터는 읽은 블록 수를 나타냅니다. 위의 예제의 경우, 블록 크기가 8k이므로 direct path read I/O 크기는 64K(8K*8블록)입니다. 또한, V$SESSION_WAIT 뷰를 조회하여 direct path read 대기 이벤트의 P3 값을 확인할 수 있습니다.

 

데이터 파일에 대한 direct path read의 증명

Session A: Degree가 4인 pq_test 테이블에 대해 PQ를 여러번 수행하면서 direct path read 유발

 

declare 

v_count number;

begin

for idx in 1 .. 100 loop

select count(*) into v_count from pq_test;

end loop;

end;

/

 

Session B: Session A에서 발생한 PQ의 슬레이브 세션에 대해서 direct path read 이벤트를 캡쳐

(Session A의 SID=162)

 

set serveroutput on size 100000

declare

begin

for px in (select * from v$px_session where acsid=162) loop

for wait in (select * from v$session_wait where

sid = px.sid and event like '%direct path read%') loop

dbms_output.put_line('SID='||wait.sid||', P=1'||wait.P1);

end loop;

end loop;

end;

/

 

-- Session B의 수행결과

SID=138, P1=1

SID=152, P1=1

SID=144, P1=1

...

SID=142, P1=1

SID=144, P1=1

SID=138, P1=1

 

-- direct path read 대기 이벤트의 P1 = file#이므로 해당되는 파일이 실제 데이터파일인지 확인할 수 있다.

 

 

SQL>exec print_table('select * from v$datafile where file# = 1');

 

FILE#                    : 1

 

BLOCK_SIZE               : 8192

NAME

C:\ORCALE\PRODUCT\10.1.0\ORADATA\UKJADB\SYSTEM01.DBF

PLUGGED_IN               : 0

BLOCK1_OFFSET            : 8192

AUX_NAME                 : NONE

-- 위와 같이 system01.dbf라는 데이터 파일에 대한 direct path read임을 알 수 있다.

 

하나의 세션에서 PQ를 수행한 후, PQ가 수행되는 동안 다른 세션에서 V$SESSION_WAIT 뷰를 조회해서 P1 값을 얻으면 어떤 파일에 대한 direct path read인지 알 수 있습니다.

출처: https://12bme.tistory.com/311?category=749950 [길은 가면, 뒤에 있다.]