BULK COLLECT
LIMIT을 (를) 알면서 % NOTFOUND을 걷어차는 모범 사례
많은 양의 데이터를 가져와야 할 때마다 BULK COLLECT를 사용하기 시작했습니다. 이로 인해 내 DBA에 문제가 발생했습니다. 그는 나의 프로그램이 훨씬 더 빨리 달릴지라도 너무 많은 메모리를 소비하고 있다고 불평하고있다. 그는 생산 롤아웃을 승인하지 않습니다. 프로그래머는 무엇을 할 수 있습니까?
BULK COLLECT와 같은 기능을 배우고 사용할 때 기억해야 할 가장 중요한 점은 무료 점심이 없다는 것입니다. 거의 언제나 어쩔 수없는 트레이드 오프가 있습니다. BULK COLLECT와 다른 많은 성능 향상 기능과 마찬가지로 성능은 향상되지만 더 많은 메모리를 소비합니다.
특히 콜렉션의 메모리는 SGA (System Global Area)가 아닌 PGA (Program Global Area)에 저장됩니다. SGA 메모리는 Oracle Database에 연결된 모든 세션에서 공유되지만 PGA 메모리는 각 세션에 할당됩니다 . 따라서 프로그램에 콜렉션을 채우기 위해 5MB의 메모리가 필요하고 100 개의 동시 연결이있는 경우 해당 프로그램은 SGA에 할당 된 메모리 외에도 500MB의 PGA 메모리를 소비합니다.
다행스럽게도 PL / SQL을 사용하면 개발자가 LIMIT 절을 사용하여 BULK COLLECT 연산에서 사용되는 메모리 양을 쉽게 제어 할 수 있습니다.
직원 테이블에서 모든 행을 검색 한 다음 각 행에 대한 보상 분석을 수행해야한다고 가정합니다. BULK COLLECT는 다음과 같이 사용할 수 있습니다.
PROCEDURE process_all_rows
IS
TYPE employees_aat
IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;
l_employees employees_aat;
BEGIN
SELECT *
BULK COLLECT INTO l_employees
FROM employees;
FOR indx IN 1 .. l_employees.COUNT
LOOP
analyze_compensation
(l_employees(indx));
END LOOP;
END process_all_rows;
매우 간결하고 우아하며 효율적인 코드. 그러나 직원 테이블에 수십만 개의 행이 포함되어 있고 각 테이블에 수백 개의 열이 포함되어 있으면이 프로그램이 PGA 메모리를 과도하게 소비 할 수 있습니다.
결과적으로 BULK COLLECT의 "무제한"사용을 피해야합니다. 대신 SELECT 문을 명시 적 커서 선언으로 이동시킨 다음 간단한 루프를 사용하여 루프 본문을 실행할 때마다 테이블의 모든 행이 아닌 전체 행을 가져옵니다 (목록 1 참조).
Code Listing 1: Using BULK COLLECT with LIMIT clause
PROCEDURE process_all_rows (limit_in IN PLS_INTEGER DEFAULT 100)
IS
CURSOR employees_cur
IS
SELECT * FROM employees;
TYPE employees_aat IS TABLE OF employees_cur%ROWTYPE
INDEX BY PLS_INTEGER;
l_employees employees_aat;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_employees LIMIT limit_in;
FOR indx IN 1 .. l_employees.COUNT
LOOP
analyze_compensation (l_employees(indx));
END LOOP;
EXIT WHEN l_employees.COUNT < limit_in;
END LOOP;
CLOSE employees_cur;
END process_all_rows;Listing 1의 process_all_rows 프로시 저는 한 번에 limit_in 행의 값까지 페치하도록 요청한다. PL / SQL은 데이터를 가져올 때마다 컬렉션의 동일한 limit_in 요소를 다시 사용하므로 동일한 메모리를 다시 사용합니다. 테이블 크기가 커지더라도 PGA 소비량은 안정적입니다.
LIMIT 절에서 사용할 숫자를 어떻게 결정합니까? 이론적으로 PGA에서 소비 할 수있는 메모리 양을 파악한 다음 가능한 한 그 양에 최대한 근접하도록 조정해야합니다.
그러나 I (및 다른 사람들)가 수행 한 테스트에서 적어도 25 이상이면 어떤 값을 선택하든 거의 동일한 성능을 보입니다. test_diff_limits.sql 스크립트는 이 열의 샘플 코드 는 Oracle Database 11g 인스턴스 에서 ALL_SOURCE 데이터 사전보기를 사용하여 이러한 동작을 보여줍니다 . 다음은 모든 행 (총 47 만)을 가져올 때 보았던 결과 (백분의 1 초)입니다.
Elapsed CPU time for limit of 1 = 1839 Elapsed CPU time for limit of 5 = 716 Elapsed CPU time for limit of 25 = 539 Elapsed CPU time for limit of 50 = 545 Elapsed CPU time for limit of 75 = 489 Elapsed CPU time for limit of 100 = 490 Elapsed CPU time for limit of 1000 = 501 Elapsed CPU time for limit of 10000 = 478 Elapsed CPU time for limit of 100000 = 527
%NOTFOUND 잊지 마세요
오라클 데이터베이스 10g 가 BULK COLLECT에 필적하는 속도로 수행 할 수 있도록 커서 FOR 루프를 자동으로 최적화 한다는 사실을 알고 매우 기뻤습니다 . 불행하게도, 우리 회사는 여전히 Oracle9의에서 실행되고 난 내가 대량 수집에 루프에 대한 나의 커서를 변환하기 시작했습니다 있도록 데이터베이스. 나는 문제가 생겼다 : 나는 100의 한계를 사용하고있다. 그리고 나의 질문은 총 227의 줄을 가져온다. 그러나 나의 프로그램은 단지 그들 중의 200 개를 처리한다. [쿼리는 Listing 2에 나와있다.] 내가 뭘 잘못하고 있니?
Code Listing 2: BULK COLLECT, %NOTFOUND, and missing rows
PROCEDURE process_all_rows
IS
CURSOR table_with_227_rows_cur
IS
SELECT * FROM table_with_227_rows;
TYPE table_with_227_rows_aat IS
TABLE OF table_with_227_rows_cur%ROWTYPE
INDEX BY PLS_INTEGER;
l_table_with_227_rows table_with_227_rows_aat;
BEGIN
OPEN table_with_227_rows_cur;
LOOP
FETCH table_with_227_rows_cur
BULK COLLECT INTO l_table_with_227_rows LIMIT 100;
EXIT WHEN table_with_227_rows_cur%NOTFOUND; /* cause of missing rows */
FOR indx IN 1 .. l_table_with_227_rows.COUNT
LOOP
analyze_compensation (l_table_with_227_rows(indx));
END LOOP;
END LOOP;
CLOSE table_with_227_rows_cur;
END process_all_rows;당신이 와서 너무 BULK 수집하는 루프에 대한 커서에서 완전히 올바른 전환 가까이! 유일한 실수는 EXIT WHEN 절에서 % NOTFOUND 커서 속성을 사용하는 습관을 포기하지 않았다는 것입니다.
EXIT WHEN table_with_227_rows_cur%NOTFOUND;
한 번에 한 행씩 데이터를 가져올 때 완벽한 의미를가집니다. 그러나 BULK COLLECT를 사용하면 해당 코드 행은 설명 된대로 정확하게 불완전한 데이터 처리를 초래할 수 있습니다.
프로그램을 실행할 때 무슨 일이 일어나고 있는지, 왜 마지막 27 행이 빠져 있는지 살펴 보겠습니다. 커서를 열고 루프에 들어가면 다음과 같이됩니다.
1. fetch 문은 1에서 100까지의 행을 검색합니다.
2. table_with_227_rows_cur % NOTFOUND는 FALSE로 평가되고 행이 처리됩니다.
3. fetch.은 101에서 200까지의 행을 검색합니다
4. table_with_227_rows_cur % NOTFOUND는 FALSE로 평가되고 행이 처리됩니다.
5. fetch 문은 201에서 227까지의 행을 검색합니다.
6. table_with_227_rows_cur % NOTFOUND는 TRUE로 평가되고 루프는 처리가 끝난 227 행으로 종료됩니다!
따라서 쿼리가 227 개의 행을 모두 처리하도록하려면 다음 문을 바꿉니다.
BULK COLLECT 및 컬렉션을 사용하여 커서에서 데이터를 가져 오는 경우 커서 특성을 사용하여 루프 및 데이터 처리를 종료할지 여부를 결정 해서는 안됩니다 .
EXIT WHEN table_with_227_rows_cur%NOTFOUND; with EXIT WHEN l_table_with_227_rows.COUNT = 0;
일반적으로 BULK COLLECT로 작업 할 때는 다음 사항을 염두에 두어야합니다.
- 컬렉션은 인덱스 값 1부터 항상 순차적으로 채워집니다.
- BULK COLLECT로 채워지면 컬렉션 1에서 컬렉션. COUNT 까지 반복 할 수 있도록 항상 안전합니다 (즉, NO_DATA_FOUND 예외를 발생시키지 않습니다) .
- 가져온 행이 없으면 fetch 비어 있습니다.
- 처리 할 행이 더 있는지 보려면 COUNT 메소드를 사용하여 콜렉션의 내용을 항상 확인하십시오.
- 커서 속성에 의해 리턴 된 값, 특히 % NOTFOUND는 무시하십시오.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
대량의 데이터를 load 하거나 update(delete) 할 때 일반적으로 사용하는 LOOP 문보다 성능이 수십배 이상 좋은 방법이 BULK SQL 을 사용하는 방법이다.
일반적인 LOOP 문 안에 있는 SQL 은 매 LOOP 마다 1회씩 수행된다. LOOP 가 100만개이면 100만번의 SQL 수행이 발생되는 것이다. 반면에 BULK SQL 을 사용하면 LOOP 없이 1번의 SQL 수행으로 처리할 수 있다.
BULK SQL 처리를 위해 오라클 PL/SQL 에서는 FORALL 과 BULK COLLECT INTO 라는 이라는 예약어를 사용한다.
FORALL 은 PL/SQL 이 DML 문장을 SQL 엔진에게 보내는데 사용되고,
BULK COLLECT INTO 는 SQL 수행결과 데이터셋을 PL/SQL 엔진에게 보낸다.
BULK SQL 은 MULTI ROW 집합을 한번에 처리하기 때문에 ARRAY 처리가 쉬운 TABLE DATATYPE 같은 collection type 이 주로 사용된다.
BULK SQL 로 수백만건을 한번에 처리할 수도 있지만, 이렇게 하면 PGA 소모가 크기 때문에 1000 ~ 100000 건 단위로 나누어서 작업하는 것이 좋다. 이를 위해 LIMIT 이라는 예약어를 사용한다.
<참고> BULK SQL 에 대해서 자세히 공부하려면 아래의 링크를 보세요.
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#BABFHGHI
---------------------
BULK SQL 사용예
---------------------
CREATE OR REPLACE PROCEDURE P_BULK_INSERT_TEST IS
CURSOR sales_cur IS
SELECT PROD_ID,
CUST_ID,
TIME_ID,
CHANNEL_ID,
PROMO_ID,
QUANTITY_SOLD,
AMOUNT_SOLD
FROM SH.SALES;
TYPE SALES_TBL_TYPE IS TABLE OF sales_cur%ROWTYPE INDEX BY BINARY_INTEGER;
SALES_TBL SALES_TBL_TYPE;
BEGIN
OPEN sales_cur;
LOOP
FETCH sales_cur BULK COLLECT INTO sales_tbl LIMIT 1000; -- PGA 소모를 작게 유지하기 위해 1000건씩만 처리
--각종 계산은 여기에서
FOR i IN sales_tbl.FIRST..sales_tbl.LAST LOOP
sales_tbl(i).AMOUNT_SOLD := sales_tbl(i).AMOUNT_SOLD * 1.5;
END LOOP;
--<방법1> FETCH 된 1000건을 SQL 1회 수행으로 처리
FORALL i IN sales_tbl.FIRST..sales_tbl.LAST
INSERT INTO SALES2 VALUES (sales_tbl(i).PROD_ID,
sales_tbl(i).CUST_ID,
sales_tbl(i).TIME_ID,
sales_tbl(i).CHANNEL_ID,
sales_tbl(i).PROMO_ID,
sales_tbl(i).QUANTITY_SOLD,
sales_tbl(i).AMOUNT_SOLD
);
--
-- <방법2> LOOP를 사용하는 방법; INSERT SQL 1000 번 수행 (속도는 방법1 보다 30배 이상 느림)
FOR i IN sales_tbl.FIRST..sales_tbl.LAST LOOP
INSERT INTO SALES2 VALUES (sales_tbl(i).PROD_ID,
sales_tbl(i).CUST_ID,
sales_tbl(i).TIME_ID,
sales_tbl(i).CHANNEL_ID,
sales_tbl(i).PROMO_ID,
sales_tbl(i).QUANTITY_SOLD,
sales_tbl(i).AMOUNT_SOLD
);
--
END LOOP;
-- UPDATE / DELETE 도 가능
FORALL i IN sales_tbl.FIRST..sales_tbl.LAST
UPDATE SALES SET AMOUNT_SOLD = sales_tbl(i).AMOUNT_SOLD
WHERE PROD_ID = sales_tbl(i).PROD_ID
AND CUST_ID = sales_tbl(i).CUST_ID
AND TIME_ID = sales_tbl(i).TIME_ID
AND CHANNEL_ID = sales_tbl(i).CHANNEL_ID
AND PROMO_ID = sales_tbl(i).PROMO_ID;
--
EXIT WHEN sales_cur%NOTFOUND;
END LOOP;
CLOSE sales_cur;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001,'RAISE_APPLICATION_ERROR : ' ||SQLCODE||':'|| SUBSTR(SQLERRM,1,200));
END;
'ORACLE > SQL' 카테고리의 다른 글
| PLSQL - 다차원 콜렉션 (0) | 2018.01.24 |
|---|---|
| 피벗 (행->열) (0) | 2017.12.22 |
| 오라클 정규식 사용 팁 (0) | 2017.07.07 |
| 잡동사니 퀴리 (0) | 2017.07.05 |
| DML 수행 시 내부 절차 (0) | 2017.06.20 |