728x90
부분범위처리의 원리
결과 집합을 전송할 때, 전체 데이터를 쉼 없이 연속적으로 처리하지 않고 사용자로부터 Fetch Call이 있을때마다 일정량씩 나누어서 전송하는것을 말한다.
DBMS는 데이터를 클라이언트에게 전송할 때 일정량씩 나누어 전송하며, 오라클의 경우 ArraySize(또는 Fetch Size) 설정을 통해 운반단위를 조절한다.
그리고 전체 결과집합 중 아직 전송하지 않은 분량이 많이 남아 있어도 클라이언트로부터 추가 Fetch Call을 받기 전까지는 그대로 멈춰 서서 기다린다.
또한 클라이언트에게 전송할 량이 끝났어도 (오라클은 끝났는지 모름) 운반단위가 차지 않으면 운반단위가 다 찰때까지 대기한 후 끝까지 다 읽은 후에야 더이상의 데이터가 없음을 인지하고 데이터의 나머지를 내보낸다.
이러한 부분범위 처리는 전체 일량을 줄이는 것과는 상관이 없지만, OLTP환경등 일부 데이터만 먼저 보여지고자 할때 유용하게 사용할 수 있다.
OLTP(online transaction processing)
- 온라인 업무의 처리 형태의 하나이다.
- 터미널에서 받은 메시지를 따라 호스트가 처리를 하고, 그 결과를 다시 터미널에 되돌려주는 방법을 말한다.
- 흔히 Array Fetch를 얘기할 때, Array 버퍼가 서버 측(Server Side)에 할당된다고 생각한다.
- 서버 측 Array 버퍼에 데이터가 차면 전송한다는 설명이다. 하지만 필자가 아는 한 Array 버퍼는 클라이언트(Client Side)에 위치하며, 서버 측에서는 SDU에 버퍼링이 이루어진다.
- 오라클에서 데이터를 전송하는 단위는 ArraySize에 의해 결정된다. 하지만 내부적으로 데이터는 네트워크 패킷 단위로 단편화되어 여러 번에 걸쳐 나누어 전송된다. 부분범위처리 내에 또 다른 부분범위처리가 작동하는 것이다.
- 이렇게 패킷으로 단편화해야 유실이나 에러가 발생했을 때 부분 재전송을 통해 복구할 수 있다.
- OSI 7 레이어(Application, Presentation, Network, Data Link, Physical)가 있는데, 오라클서버와 클라이언트는 Application레이어에 위치하며, 그 아래에 있는 에리어를 통해 서로 데이터를 주고받는다.
- SDU(Session Data Unit) : 네트워크를 통해 전송하기 전에 Oracle Net이 데이터를 담아 두려고 사용하는 버퍼이다.
- 서버 측에서 클라이언트로부터 Fetch Call을기다리는데, Oracle Net이 서버 프로세스로 부터 전송명령을 받을 때까지 대기하는 곳이다. Oracle Net은 서버 프로세스로부터 전송요청을 받기 전이라도 SDU가 다 차면 버퍼에 쌓인 데이터를 전송하는데, 이때 클라이언트로부터 Fetch Call을 기다리지 않고 곧이어 데이터를 받아 SDU를 계속 채워나간다.
- TDU(Transport Data Unit) : Transport 레이어 데이터 버퍼에 대한 규격이다. 물리적인 하부 레이어로 내려보내기 전에 데이터를 잘게 쪼개어 클라이언트에게 전송되는 도중에 유실이나 에러가 없도록 제어하는 역할을 한다.
- SDU와 TDU 사이즈는 TNSNAMES.ORA, LISTENER.ORA 파일에서 아래와 같이 설정 가능하며, 이들의 기본 설정 값은 2KB이다.
- (SDU=2048)(TDU=2048)
-- 테이블 생성
create table t (
x NUMBER not null
, y NUMBER not null ) ;
-- 데이터 insert(랜덤으로)
insert into t
select *
from (
select rownum x, rownum y
from dual
connect by level <= 5000000
)
order by dbms_random.value
;
alter table t add
constraint t_pk primary key (x);
alter system flush buffer_cache;
-- 6건 검색
select /*+ index(t t_pk) */ x, y
from t
where x > 0
and y <= 6 ;
-- 1건 검색
select /*+ index(t t_pk) */ x, y
from t
where x > 0
and y <= 1 ;
- 그림 5-6(페이지376) 을 참조하여 보기 바란다.
- 앞에서 설명했던 내용의 연장이다. 책에 있는 내용을 빌어 설명하자면
- 첫번째 Fetch Call에서는 인덱스를 따라 x컬럼 값이 1~5인 5개의 레코드는 테이블 필터 조건인 y<=6조건도 만족한다.
- 오라클 서버는 이 5개 레코드를 아주 빠르게 찾았으므로 지체 없이 전송 명령을 통해 클라이언트에게 전송하고, 클라이언트는 Array 버퍼에 담긴 5개의 레코드를 곧바로 화면에 출력한다.
- 문제는 두번째 Fetch Call에서 발생한다. 두번째 Fetch Call 명령을 받자마자 x=y=6인 레코드를 찾아 Oracle Net으로 내려보낸다. 이제 조건에 더이상 만족하는 레코드가 없다는 사실을 오라클은 모르기 때문에 계속 인덱스를 스캔하면서 테이블을 엑세스해 본다. 끝까지 가 본 후에야 더는 전송할 데이터가 없음을 인식하고 그대로 한 건만 전송하도록 Oracle Net에 명령을 보낸다.
ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
대량 데이터를 내려받을 때 ArraySize를 크게 설정하면
- Fetch Call 횟수가 줄어 네트워크 부하가 감소하고, 쿼리 성능이 향상
- 서버 프로세스가 읽어야 할 블록 개수까지 줄어든다.
- ArraySize를 조정하는데 왜 블록 I/O가 줄어드는 것일까? 직접 테스트 해보자
SQL> create table test as select * from all_objects;
테이블이 생성되었습니다.
SQL> set autotrace traceonly statistics;
SQL> set arraysize 2;
SQL> select * from test;
47094 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23871 consistent gets
530 physical reads
0 redo size
6247169 bytes sent via SQL*Net to client
259498 bytes received via SQL*Net from client
23548 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
47094 rows processed
- ArraySize를 2로 설정하고 47094로우를 가져오게 함.
- 읽은 블록개수 : 23871
- Featch 횟수 : 23548
- ===> Fetch할 때마다 2개의 로우(47094/23548 = 1.9999.... (ArraySize와 거의 동일)) 씩 읽는것을 알 수 있다.
- SQL 트레이스를 이용해도 같다. 각 항목을 매치시켜 보자.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 23548 0.27 1.54 0 23871 0 47094
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 23550 0.27 1.58 0 23871 0 47094
| db block gets | current |
| consistent gets | query |
| physical reads | disk |
| SQL*Net roundtrips to/from client | Fetch count |
| rows processed | Fetch rows |
- ArraySize를 계속 늘려서 위와같은 테스트를 반복해 보자.
- ArraySize를 키울수록 Fetch Count는 횟수가 줄고 더불어 Block I/O까지 주는것을 볼수 있다. 즉, 반비례다.
- 보기쉽게 그래프로 정리해 놓았다.(책내용임)
- 그러나 ArraySize를 키운다고 같은 비율로 Fetch Count와 Block I/O가 줄지 않는다는 것을 확인할 수 있다.
- 따라서, 무작정 크게 설정한다고 좋은것만은 아니며, 오히려 리소스 낭비가 된다.
- 이 내용은 각 사이트에 맞게 테스트하여 설정해야 하겠다.
- 그럼 ArraySize가 늘면서 블록I/O가 감소하는 원리는?
아래 그림은 책의 예시이며, 10개의 행으로 구성된 3개의 블럭이 있다고 가정한다.
- 총 30개의 레코드이므로 ArraySize를 3으로 설정하면 Fetch 횟수는 10이고, Block I/O는 12번 발생한다.
- 1번블록 : 2~4번째 Fetch에서 반복 엑세스(흠... 1번째 Fetch는 한번만 읽는가?? 맞는듯...)
- 2번블록 : 4~7번째 Fetch에서 반복 엑세스
- 3번블록 : 7~10번째 Fetch에서 반복 엑세스
- 만약 ArraySize를 30으로 설정하면 Fetch횟수는 1로 줄어든다.
프로그램 언어에서 Array 단위 Fetch 기능 활용
- 지금까지는 SQL*Plus 중심으로만 설명했는데, PL/SQL을 포함한 프로그램 언어에서 어떻게 ArraySize를 제어하는지 확인하자.
Cursor FOR Loop문을 사용할 경우
- 9i까지(4절 Array Processing의 Bulk Collect 구문을 사용하지 않는 한) 한 로우씩 처리
- 10g부터 자동으로 100개씩 Array Processing
Cursor FOR Loop문의 커서
- Open, Fetch, Close가 내부적으로 이루어지는 것이 특징
- Implicit(절대적인) Cursor FOR Loop, Explicit(명백한) Cursor FOR Loop 두가지 형태가 있다. (두개 다 Array Fetch 효과 얻음)
Implicit Cursor FOR Loop
declare
l_object_name big_table.object_name%type;
begin
for item in ( select object_name from big_table where rownum <= 1000 )
loop
l_object_name := item.object_name;
dbms_output.put_line(l_object_name);
end loop;
end;
/
Explicit Cursor FOR Loop
declare
l_object_name big_table.object_name%type;
cursor c is select object_name from big_table where rownum <= 1000;
begin
for item in c
loop
l_object_name := item.object_name;
dbms_output.put_line(l_object_name);
end loop;
end;
/
-- sql트레이스는 동일하다.
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 11 0.00 0.00 0 24 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13 0.00 0.00 0 24 0 1000
Cursor FOR Loop가 아닌 일반 커서사용
declare
cursor c is
select object_name
from test where rownum <= 1000;
l_object_name test.object_name%type;
begin
open c;
loop
fetch c into l_object_name;
exit when c%notfound;
dbms_output.put_line(l_object_name);
end loop;
close c;
end;
-- sql 트레이스 결과
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 1001 0.00 0.00 0 1003 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1003 0.00 0.00 0 1003 0 1000
위의 결과 Cursor FOR Loop를 사용하지 않으면 Array단위 Fetch가 작동하지 않음을 알 수 있다.
JAVA 프로그램에서 ArraySize를 조정하는 방법.
String sql = "select id,pw from customer";
PreparedStatment stmt = conn.prepareStatment(sql);
Stmt.setFetchSize(100); //여기!!!
ResultSet rs = stmt.executeQuery();
// rs.setFetchSize(100); -- ResultSet에서 조정할 수도 있다.
while(rs.next()){
......
}
rs.close();
stmt.close();
- JAVA에서 FetchSize 기본 값은 10이다.
- 대량 데이터를 Fetch 할 때 이 값을 100~500 정도로 늘려 주면 기본 값을 사용할 때보다 데이터베이스 Call 부하를 1/10 ~ 1/50로 줄일 수 있다.
- FetchSize를 100으로 설정했을 때 데이터를 Fetch 해오는 메커니즘은 아래와 같다.
- 최초 rs.next() 호출 시 한꺼번에 100건을 가져와서 클라이언트 Array 버퍼에 캐싱한다.
- 이후 rs.next() 호출할 때는 데이터베이스 Call을 발생시키지 않고 Array 버퍼에서 읽는다.
- 버퍼에 캐싱 돼 있던 데이터를 모두 소진한 후 101번째 rs.next() 호출 시 다시 100건을 가져온다.
- 모든 결과집합을 다 읽을 때까지 2~3번 과정을 반복한다.
User Call vs. Recursive Call
- SQL 트레이스 파일을 TKProf 유틸리티로 포맷팅하면 OVERALL Total 통계가 나온다.
- NON-RECURSIVE 통계가 User Call에 해당한다.
- 아래쪽 RECURSIVE 통계가 Recursive Call에 해당된다.
================================================================================
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.02 0.03 0 121 0 0
Execute 4 0.03 0.03 4 95 2915 0
Fetch 30 0.04 0.02 0 122 0 2859
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 38 0.09 0.09 4 338 2915 2859
Misses in library cache during parse: 2
================================================================================================================================================================
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 53 0.00 0.00 0 0 0 0
Fetch 53 0.00 0.00 80 277 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 110 0.00 0.00 80 277 0 3
================================================================================
[2] user Call
- OCI(Oracle Call Interface)를 통해 오라클 외부로부터 들어오는 Call이다.
- Peak 시간대에 시스템 장애를 발생시키는 가장 큰 주범은 User Call이다.
- User Call이 많이 발생되도록 개발된 애플리케이션은 결코 좋은 성능을 낼 수 없다.
- DBMS 성능과 확장성(Scalability)를 높이려면 User Call을 최소화 하려는 노력이 무엇보다 중요하다.
(1) User Call 최소화를 위한 노력 --> 위에 설명한 모든 내용이 User Call 최소화를 위한 것이다.
User Call 최소화를 위해 아래와 같은 기능과 기술을 적극적으로 활용한다.
- Loop 쿼리를 해소하고 집합적 사고를 통해 One-SQL로 구현하기
- Array 단위로 Fetch하는 Array Processing을 활용한다.
- 또는 Bulk Insert/Update/Delete 가 있다.
- 부분범위처리 원리를 활용한다.
- 효과적인 화면 페이지 처리를 구현한다.
[3] Recursive Call
- 오라클 내부에서 발생하는 Call이다.
- Recursive Call의 종류
- SQL 파싱과 최적화 과정에서 발생하는 Data Dictionary 조회
- PL/SQL로 작성된 사용자 정의 함수/프로시저/트리거 내에서의 SQL 수행 등
(1) Recursive Call 최소화를 위한 노력
- 바인드변수를 적극적으로 사용해 하드파싱 횟수를 줄인다.
- PL/SQL로 작성한 프로그램을 이해하고 시기 적절하게 사용한다.
[PL/SQL 예시]
================================================================================
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 493 0.01 0.00 0 0 0 0
Fetch 493 0.03 0.02 0 3451 0 493
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 986 0.04 0.02 0 3451 0 493
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 41 (recursive depth: 1)
================================================================================
- recursive depth는 PL/SQL실행시에 나오는 결과에서 프로시저의 호출 횟수를 나타낸다.
- recursive depth가 2이상이면 특정 프로시저에서 또 다른 프로시져를 호출한 경우이며
이 때 트레이스 결과는 마지막 프로시저에서 사용된 SQL에 대한 수행 결과를 보인다. - PL/SQL은 가상머신(Virtual Machine)상에서 수행되는 인터프리터(Interpreter)언어이므로
빈번한 호출 시 컨텍스트 스위칭(Context Switching)때문에 성능이 매우 나빠진다. - 성능을 위해서라면 PL/SQL에 대한 지나친 모듈화는 지양한다.
- 대용량 데이터 조회시에 함수를 잘못 사용하면 건건이 함수 호출이 발생되어 성능이 극도로
제한될 수 있는 등의 문제가 생긴다.- 조인 또는 스칼라 서브쿼리 형태로 변환하려는 노력이 필요하다.
* 컨텍스트 스위칭이란, 'CPU/코어에서 실행 중이던 프로세스/스레드가 다른 프로세스/스레드로 교체되는 것'입니다.그렇다면, 여기서 말하는 컨텍스트란 무엇일까요? 컨텍스트란 프로세스/스레드의 상태를 의미합니다. 또 이 상태라는 것은 CPU, 메모리에서의 상태를 의미합니다.
참조 :
http://www.gurubee.net/lecture/3112
Fetch Call 최소화
Fetch Call 최소화부분범위처리의 원리OLTP 환경에서 부분범위처리에 의한 성능개선 원리ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효..
www.gurubee.net
728x90
반응형
'Oracle > Tunning' 카테고리의 다른 글
| [Oracle] Tunning 관련 파라미터 정리 (0) | 2024.08.12 |
|---|---|
| [Oracle]OR-Expansion (0) | 2024.08.12 |
| [Oracle] NL Join 확장 메커니즘 (0) | 2024.03.22 |
| [Oracle] sort group by, hash group by 차이 (2) | 2024.02.28 |
| [Oracle] SubQuery Hint (0) | 2024.02.22 |