기록지

정의

Call이 어디서 발생하느냐에 따라 User Call과 Recursive Call로 나눌 수 있다.

1) User Call : OCI(Oracle Call Interface)를 통해 외부로부터 들어오는 Call

2) Recursive Call : 오라클 내부에서 발생하는 Call, 
- SQL파싱과 최적화 과정에서 발생하는 Data Dictionary 조회, 
- PL/SQL로 작성된 사용자 정의 함수 , Procedure , Trigger로 인한 SQL수행

 특징
1) User Call
User Call 발생 빈도를 결정하는 요소들
 개발자의 기술이 관여
 프레임워크 내에 Array processing을 지원 유무가 중요하다.
 설계 표준 가이드
 사용자 정의 함수/프로시저에 대한 무조건적인 제약
 모듈이 지나치게 단위로 구성되어 SQL이 건건이 호출되는 개발환경

 Loop 쿼리를 해소하고, 집합적 사고를 통해 One-SQL로 구현

 Array Processing : Array 단위 Fetch, Bulk Insert / Update / Delete
 부분범위처리 활용
 효과적인 화면 페이지 처리
 사용자 정의함수 / 프로시저 / 트리거의 적절한 활용

2) Recursive Call
     Hard Parsing에 대해 Recursive Call 발생 

     바인드 변수의 적극적 사용, 하드파싱 횟수 절감을 통해 Recursive Call 감소
     Recursive Depth를 크게 만들지 않게끔 프로시저 구성 ( 프로시저 안에 프로시저가 들어있는 횟수 : Recursive Depth )
     Recursive Depth를 크기 만들지 않게끔 지나친 Procedure의 모듈화를 지양
     대용량 데이터 조회시 함수호출이 건건이 발생하지 않게끔, 함수를 부분범위 처리가 가능한 상황에서,
     제한적으로 사용해야함
    조인 또는 스칼라 서브쿼리 형태로 변환.

Array Processing 활용

# Array Processing 기능을 사용하면 한번의 SQL수행으로 다량의 로우를 insert/update/delete 할수 있다.
  이를 통해 Network를 통한 데이터베이스 Call을 감소시켜 SQL수행시간과 CPU 사용량을 줄일 수 가 있다.

# 예시상황 : "03 데이터베이스 Call이 성능에 미치는 영향"에 있는 예시상황 
1) 3만건인 월요금납부실적 테이블에서 각로우를 fetch하여 읽은 다음 조건에 맞게끔
   납입방법별_월요금집계 테이블에 중복삽입
2) stmt1.setFetchSize(1000); 구문을 통해 월요금납부실적 테이블을 fetch 할때 1000건씩 fetch
3) select시 fetch call    : 30
   insert시 execute call : 30
4) fetch Array processing size    : 1000 (=Fetch Size)
   execute Array processing size : 5000

---------------------------------------------------------------------------------------------------------------------------------
public static void execute (Connection con , String i nput month) 
throws Exception { 
   long rows 0;
   String SQLStmtl "SELECT 고객번호, 납입월 "
               + ", 지로, 자동이체, 신용카드, 핸드폰, 인터넷"
               + "from 월요금납부실적"
               + "where 납입월 ? ";
   String SQLStmt2 "INSERT INTO 납입방법별_월요금집계 " 
               +"(고객변호, 납입월,납입방법묘드, 납입금액) "
               + "VALUES (?, ?, ?, ?)";
con. setAutoCommit(false);
PreparedStatement stmtl = con.prepareStatement(SQLStmt1);
PreparedStatement stmt2 = con.prepareStatement(SQLStmt2);
stmt1.setFetchSize(1000);
stmtl.setString(1, input month);
ResultSet rs = stmtl.executeQuery();

while(rs.next()) {
   String 고객변호 = rs.getString(1);
   String 납입월 = rs.getString(2);
   long 지로 = rs.getLong(3);
   long 지동이체 = rs.getLong(4);
   long 신용카드 = rs.getLong(5);
   long 핸드폰 = rs.getLong(6);
   long 인터넷 rs.getLong(7);
   if( 지로 > 0 )
      insertData (con, stmt2, 고객변호, 납입월, "A", 지로);
   if ( 지동이체 > 0 )
      insertData (con, stmt2, 고객번호, 납입월, "B", 지동이체);
   if ( 신용카드 > 0 ) 
      insertData (con, stmt2, 고객변호, 납입월, "C", 신용카드);
   if ( 핸드폰 > 0 ) 
      insertData (con, stmt2, 고객변호, 납입월, "D" , 핸드폰);
   if ( 인터넷 > 0 )
      insertData (con, stmt2, 고객변호, 납입월, "E" , 인터넷) ;
   if(++rows%1000 == 0) stmt2.executeBatch();
}
---------------------------------------------------------------------------------------------------------------------------------

# Array processing의 효과를 제대로 발휘하려면, 연속된 일련의 처리과정이 모두 Array 단위로 지정되어야 한다.
   ex : insert select문 - select (fetch), insert(execute)의 각각의 단계에서 
Array processing size가 일치해야한다. 만약 select에서 5000건식 Fetch를 한다 하더라도,
         insert 단계에서, 건건이 execute를 수행하면, 병렬로부터 직렬로 처리되는 부분이 병목현상을 야기하기 때문에
         쿼리 처리속도가 빠르지 않다.

위의 표는 앞 절에서 수행한 3가지 테스트(PL/SQL,JAVA(array가 10),One-SQL)와 방금 확인한 java(array가 1000)결과를 표로 정리한 것.
네트워크를 경유해 발생하는 데이터베이스 Call이 얼마만큼 심각한 성능부하를 일으키는지 알 수 있다. 그 뿐 아니라 One-SQL로 통합하지 않더라도 Array Processing만으로 그에 버금가는 성능개선 효과를 얻을 수 있음을 확인 할 수 있다.

FILTER 동작방식

Main SQL에서 추출된 데이터 건수만큼 서브쿼리가 반복적으로수행되면 처리되는 방식
Main SQL의 추출 결과에 대해서, 매 로우마다 서브쿼리에 조인 연결 값을 제공한 후 수행한 후, TRUE일 경우 데이터를 추출
Main SQL의 결과가 100만건이면 최대 100만번 수행
이런 이유로 적절한 인덱스가 없을 경우 Full Table Scan을 100만번 수행되어 SQL 성능 저하 발생
Main SQL의 추출 결과가 많더라도 서브쿼리의 Input 값이 동일하면 Filter가 1번만 수행되는 Filter Optimization이라 불리는 최적화 작업을 수행하므로 성능 문제가 발생하지 않는다.

Filter Optimization

서브쿼리를 수행한 Input 값을 Cache하며, 동일한 Input값이 사용되는 경우 추가적입 읽기 작업 없이 Cache된 값을 사용

서브 쿼리의 Input 값이 동일한 확률이 매우 희박하므로 추출 건수가 많은 경우 서브커리를 Filter 동작 방식으로 처리할 경우 성능상 비횰율적인 경우가 더 많음
Filter 동작 방식으로 수행될 경우 Input 값의 종류가 적어 성능에 유리한지를 반드시 확인해야 함.

Script. 서브쿼리 테스트용

DROP TABLE SUBQUERY_T1 PURGE;
DROP TABLE SUBQUERY_T2 PURGE;
DROP TABLE SUBQUERY_T3 PURGE;

<SUBQUERY_T1>
* 생성요건
- 테이블건수 1,500,000
- 컬럼 C4의 값의 종류: 250,000
- 컬럼 C5의 값의 종류 : 26
- 컬럼 C6SMS 100,000 부터 시작하여, 순차적으로 증가하며 값의 종류 : 250,000


* 테이블 생성
CREATE TABLE SUBQUERY_T1
AS
SELECT LEVEL AS C4, CHR(65+MOD(LEVEL, 26)) AS C5, LEVEL+99999 AS C6
FROM DUAL
CONNECT BY LEVEL <=250000;

- 250,000개의 데이터를 생성 한 후 동일한 테이블 값을 6번 반복하여 복사
 FOR I IN 1..6 LOOP
   INSERT INTO SUBQUERY_T1 SELECT * FROM SUBQUERY_T1;
   COMMIT;
 END LOOP;
END;
/

- 각 컬럼에 인덱스 생성 및 통계정보를 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT', TABNAME=>'SUBQUERY_T1', CASCADE=>TRUE, ESTIMATE_PERCENT=>100);

CREATE INDEX SUBQUERY_T1_IDX1_01 ON SUBQUERY_T1(C4, C5);
CREATE INDEX SUBQUERY_T1_IDX1_02 ON SUBQUERY_T1(C5);

<SUBQUERY_T2>
* 생성요건
- 테이블건수 500,000
- 컬럼 C1 값 종류 : 500,000
- 컬럼 C2 값 종류 : 26
- 컬럼 C3 값 종류 : 500,000(100,000부터 증가)
- 컬럼 C4 값 종류 : 26

* 테이블 생성
CREATE TABLE SUBQUERY_T2
AS
SELECT LEVEL AS C1,
       CHR(65+MOD(LEVEL, 26)) AS C2,
       LEVEL+99999 AS C3,
       CHR(65+MOD(LEVEL,26)) AS C4
  FROM DUAL
CONNECT BY LEVEL <= 500000;

* 각 컬럼 인덱스 생성 및 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'SCOTT', TABNAME=>'SUBQUERY_T2', CASCADE=>TRUE, ESTIMATE_PERCENT=>100);

CREATE INDEX SUBQUERY_T2_IDX_01 ON SUBQUERY_T2(C2, C1);
ALTER TABLE SUBQUERY_T2 ADD CONSTRAINT PK_SUBQUERY_2 PRIMARY KEY(C1);

<SUBQUERY_T3>
*생성 요건
- 테이블 데이터 건수 : 500,000
- 컬럼 C1 값 : 500,000
- 컬럼 C2 값 : 26
- 컬럼 C3 값 : 500,000 (100,000부터 순차적으로 증가)

*테이블 생성
CREATE TABLE SUBQUERY_T3
AS
SELECT LEVEL AS C1,
       CHR(65+MOD(LEVEL, 26)) AS C2,
       LEVEL+99999 AS C3
  FROM DUAL
CONNECT BY LEVEL <= 500000;

* 각 컬럼 인덱스 생성 및 통계정보 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'SCOTT', TABNAME=>'SUBQUERY_T3', CASCADE=>TRUE, ESTIMATE_PERCENT=>100);

CREATE INDEX SUBQUERY_T3_IDX_01 ON SUBQUERY_T3(C1, C2);
ALTER TABLE SUBQUERY_T3 ADD CONSTRAINT PK_SUBQUERY_3 PRIMARY KEY(C1);

테스트1.Main SQL의 추출 건수가 많고, INPUT값이 UNIQUE한 경우

var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 400000

SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1
   AND c1 <= :b2
   AND EXISTS (SELECT /*+ NO_UNNEST */ 'X'
                 FROM SUBQUERY_T1 t1
                WHERE t1.c4 = t2.c1);

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch     2302    1.326        1.378          0     789240          0     230001
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     2304    1.326        1.378          0     789240          0     230001


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
 230001  FILTER  (cr=789240 pr=0 pw=0 time=1019504 us)
 380001   FILTER  (cr=3611 pr=0 pw=0 time=305638 us)
 380001    TABLE ACCESS FULL SUBQUERY_T2 (cr=3611 pr=0 pw=0 time=205235 us cost=373 size=4560036 card=380003)
 230001   INDEX RANGE SCAN SUBQUERY_T1_IDX1_01 (cr=785629 pr=0 pw=0 time=0 us cost=3 size=10 card=2)

FILTER 방식으로 동작
SUBQYER_T2를 Full Table Scan으로 읽으며 "C1 >= :B1 AND C1 <= :B2" 조건에 만족하는 데이터 추출 ==> 380,001
C1의 값을 SUBQUERY_T1_IDX_01 인덱스를 사용해 총 380,001번 반복조회하여 최종 건수 230,001건을 추출
Main Query의 추출건수(38만건)만큼 서브 쿼리가 38만번 반복적으로 수행
SQL전체 I/O 발생량의 대부분은 서브쿼리에서 반복적으로 사용하는 SUBQUERY_T1_IDX_01인덱스에서 발생 (cr=785629)

테스트2. Main SQL의 추출 건수가 적고, INPUT값이 UNIQUE한 경우

var b1 number
var b2 number

exec :b1 := 20000
exec :b2 := 20004

SELECT C1,
       C2,
       C3
  FROM SUBQUERY_T2 T2
 WHERE C1 >= :B1
   AND C1<=:B2
   AND EXISTS(SELECT /*+ NO_UNNEST */'X'
          FROM SUBQUERY_T1 T1
         WHERE T1.C4 = T2.C1)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.000        0.000          0         21          0          5
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.000          0         21          0          5

Rows     Row Source Operation
-------  -----------------------------------------------------------------------
      5  FILTER  (cr=21 pr=0 pw=0 time=0 us)
      5   TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=21 pr=0 pw=0 time=0 us cost=13 size=756 card=63)
      5    INDEX RANGE SCAN PK_SUBQUERY_2 (cr=19 pr=0 pw=0 time=224 us cost=7 size=0 card=2250)
      5     INDEX RANGE SCAN SUBQUERY_T1_IDX1_01 (cr=15 pr=0 pw=0 time=0 us cost=3 size=10 card=2)

FILTER 방식으로 수행되지만, Main SQL의 조건이 효율적이어서 추출되는 데이터 건수가 총 5건 밖에 되지 않음
서브쿼리도 5번만 수행되어 비교적 양호

테스트3. Main SQL의 추출 건수는 많지만, INPUT값의 종류가 26개인 경우

var b1 number
var b2 number
exec :b1 := 20000
exec :b2 := 400000

SELECT C1,
       C2,
       C3
  FROM SUBQUERY_T2 T2
 WHERE C1 >= :B1
   AND C1<=:B2
   AND EXISTS(SELECT /*+ NO_UNNEST */'X'
          FROM SUBQUERY_T1 T1
         WHERE T1.C5 = T2.C2)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      1    0.000        0.005          0          0          0          0
Fetch     3802    0.484        0.974         60      19809          0     380001
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total     3804    0.484        0.979         60      19809          0     380001

Misses in library cashe during parse   : 1
Misses in library cashe during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
 380001  FILTER  (cr=19809 pr=60 pw=0 time=457459 us)
 380001   FILTER  (cr=5107 pr=0 pw=0 time=292967 us)
 380001    TABLE ACCESS FULL SUBQUERY_T2 (cr=5107 pr=0 pw=0 time=185774 us cost=377 size=4560036 card=380003)
  14640   INDEX RANGE SCAN SUBQUERY_T1_IDX1_02 (cr=14702 pr=60 pw=0 time=0 us cost=3 size=4 card=2)

Main Query의 추출건수 : 380,001건/ INPUT 종류 : 26
위의 서브쿼리는 Main SQL의 추출 결과만큼 배번 수행하지 않았음.
서브쿼리의 INPUT값을 CACHE하여 INPUT값이 같을 경우 서브쿼리를 수행하지 않았음.

Filter 방식 수행
FILTER 방식은 Main SQL의 추출 결과가 많고 서브쿼리에 제공해 주는 값(INPUT)의 종류가 많다면 성능이 좋지 않음
Main SQL의 추출 거누가 적거나, Main SQL의 추출결과가 많을 경우 INPUT값의 종류가 적으면 성능 양호
FILTER 방식으로 수행되면 먼저 서브쿼리의 조인 연결 컬럼에 인덱스가 존재하는지 확인해야 함.
FULL TABLE SCAN으로처리된다면 심각한 성능 문제가 발생할 수 있음


조인 동작 방식
조안방식과 FILTER 동작방식과 비교시 큰 차이점은 가변성이다.
FILTER는 수행순서나 수행방법이 고정 (Main SQL -> SubQuery) 다양한 상황에 유연한 대처가 어려움
조인 동작 방식은 Nest Loops Join, Hash Join, Sort Merge Join, Semi Join, Anti Join등의 다양한 조인 방법 중 유리한 것을 선택 가능
SEMI/ANTI JOIN을 제외하고 수행 순서까지 선택 가능

기본적으로 SEMI/ANTI JOIN은 수행순서가 변경되지 않아 Main Query Block의 테이블이 먼저 수행.
Oracle 10부터 Hash Join의 경우에 한해 Driving Table의 순서 변경이 가능

Nested Loops Join Semi를 제외한 나머지 조인 방법은 Filter 동작 방식이 가지고 있는 FILTER 오퍼레이션 의 효과는 없음.( Cashing 기능) INPUT값의 종류가 적은 경우라면, FILTER 방식이 성능상 유리 할 수 있음.

테스트1.Filter 방식으로 수행되어 성능 문제가 발생하는 SQL

var b1 number
var b2 number
exec :b1 := 249990
exec :b2 := 250210

SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1
   AND c1 <= :b2
   AND EXISTS (SELECT /*+ NO_UNNEST */ 'X'
                 FROM SUBQUERY_T1 t1
                WHERE T1.C6 = T2.C3
                  AND T1.C6 >= :b1)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2  311.628      932.413    7885118    9223016          0         11
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4  311.628      932.413    7885118    9223016          0         11



Rows     Row Source Operation
-------  -----------------------------------------------------------------------
     11  FILTER  (cr=9223016 pr=7885118 pw=0 time=0 us)
    221   FILTER  (cr=1317 pr=0 pw=0 time=1980 us)
    221    TABLE ACCESS FULL SUBQUERY_T2 (cr=1317 pr=0 pw=0 time=1100 us cost=372 size=2702664 card=225222)
     11   FILTER  (cr=9221699 pr=7885118 pw=0 time=0 us)
     11    TABLE ACCESS FULL SUBQUERY_T1 (cr=9221699 pr=7885118 pw=0 time=0 us cost=164 size=10 card=2)

테이블 T1컬럼 C6에 인덱스가 없어 Main SQL의 추출 건수만큼 SUBQUERY_T1 테이블을 반복적으로 Full Table Scan 수행
SUBQUERY_T1의 컬럼 C6에 인덱스를 생성하면 성능이 개선.

인덱스 생성시 SUBQUERY_T1 테이블을 액세스하는 다른 SQL의 실행계획에 영향을 미치는지 확인해야 함
트랜잭션 프로그램의 부하도 고려해야 함
인덱스 추가에 따른 디스크의 여유 및 앞으로의 증가량 체크
DB 서버의 성능 측면에서 인덱스를 생성하는 것이 이득이 크지 않다면 다른 개선 방법을 찾아봐야 함.

-- c6에 인덱스를 생성 한 후 결과

SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1
   AND c1 <= :b2
   AND EXISTS (SELECT /*+ NO_UNNEST */ 'X'
                 FROM SUBQUERY_T1 t1
                WHERE T1.C6 = T2.C3
                  AND T1.C6 >= :b1)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.003          0          0          0          0
Fetch        2    0.000        0.065          8        249          0         11
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.068          8        249          0         11

Misses in library cashe during parse   : 0
Misses in library cashe during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
     11  FILTER  (cr=249 pr=8 pw=0 time=0 us)
    221   FILTER  (cr=7 pr=0 pw=0 time=4400 us)
    221    TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=7 pr=0 pw=0 time=220 us cost=4 size=2664 card=222)
    221     INDEX RANGE SCAN PK_SUBQUERY_2 (cr=4 pr=0 pw=0 time=2970 us cost=3 size=0 card=222)
     11   FILTER  (cr=242 pr=8 pw=0 time=0 us)
     11    INDEX RANGE SCAN SUBQUERY_T1_IDX_03 (cr=242 pr=8 pw=0 time=0 us cost=3 size=5 card=1)
     
     
SELECT c1, c2, c3
  FROM SUBQUERY_T2 t2
 WHERE c1 >= :b1
   AND c1 <= :b2
   AND EXISTS (SELECT /*+ UNNEST HASH_SJ */ 'X'
                 FROM SUBQUERY_T1 t1
                WHERE T1.C6 = T2.C3
                  AND T1.C6 >= :b1)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    2.761        9.234      37509      43888          0         11
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    2.761        9.235      37509      43888          0         11


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
     11  FILTER  (cr=43888 pr=37509 pw=0 time=0 us)
     11   HASH JOIN SEMI (cr=43888 pr=37509 pw=0 time=0 us cost=10404 size=748 card=44)
    221    TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=5 pr=0 pw=0 time=880 us cost=4 size=1860 card=155)
    221     INDEX RANGE SCAN PK_SUBQUERY_2 (cr=3 pr=0 pw=0 time=440 us cost=3 size=0 card=222)
6400640    TABLE ACCESS FULL SUBQUERY_T1 (cr=43883 pr=37509 pw=0 time=5948287 us cost=10368 size=32003330 card=6400666)

반복적인 Full Table Scan을 줄이는게 성능 개선 포인트
/*+ UNNEST HASH_SJ*/ 힌트를 부여
HASH JOIN SEMI로 수행. 단 한번 Full Table Scan만으로 수행되도록 변경
인덱스가 존재하지 않는 것이 비효율의 근본적인 원인이지만, 인덱스를 생성하기 힘든 경우에 위와 같은 튜닝으로 개선 효과 발생


Filter 방식 Test

var b1 number
var b2 number
var b3 number
var b4 number

exec :b1 := 1
exec :b2 := 450210
exec :b3 := 100000
exec :b4 := 100004

SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS (SELECT /*+ NO_UNNEST */'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      1    0.000        0.009          0         31          0          0
Fetch        5   63.929       63.982      37509   33288865          0        320
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        7   63.929       63.991      37509   33288896          0        320

Misses in library cashe during parse   : 1
Misses in library cashe during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
    320  FILTER  (cr=33288865 pr=37509 pw=0 time=159 us)
16000000   FILTER  (cr=43886 pr=37509 pw=0 time=16265983 us)
16000000    TABLE ACCESS FULL SUBQUERY_T1 (cr=43886 pr=37509 pw=0 time=12611300 us cost=10424 size=192000000 card=16000000)
      5   FILTER  (cr=33244979 pr=0 pw=0 time=0 us)
      5    TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=33244979 pr=0 pw=0 time=0 us cost=3 size=10 card=1)
15935488     INDEX UNIQUE SCAN PK_SUBQUERY_2 (cr=17309491 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

Main SQL의 추출 데이터 : 1,600만건.
SubQuery는 PK 인덱스를 UNIQUE 스캔 수행하여 I/O가 33만 블록
최종 데이터는 320건
320건을 추출하기 위해 처리한 I/O블록수와 수행 시간이 많이 소요 되었음.


Join 방식 Test1 : UNNEST HASH_SJ

SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS (SELECT /*+ UNNEST HASH_SJ */'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        5    4.103        5.269      37509      45202          0        320
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        7    4.103        5.269      37509      45202          0        320

Misses in library cashe during parse   : 0
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
    320  FILTER  (cr=45202 pr=37509 pw=0 time=319 us)
    320   HASH JOIN RIGHT SEMI (cr=45202 pr=37509 pw=0 time=319 us cost=10861 size=8448 card=384)
      5    TABLE ACCESS FULL SUBQUERY_T2 (cr=1316 pr=0 pw=0 time=8 us cost=374 size=50 card=5)
16000000    TABLE ACCESS FULL SUBQUERY_T1 (cr=43886 pr=37509 pw=0 time=8017471 us cost=10408 size=192000000 card=16000000)

기존 SQL에서 서브쿼리가 1,600만번 반복 수행되던 부분을 제거하여 SQL 성능 개선
최종 결과가 320건추출 ==> 5초 수행


성능 개선 수행
성능 개선 여지는 SUBQUERY_T2의 추출 건수 => 5건으로 SUBQUERY_T1과 조인 후 최종 결과 320건 추출
SUBQUERY_T2를 먼저 처리하고, Main SQL의 SUBQUERY_T1 테이블과 Nested Loops Join(조인 연결 컬럼 인덱스 존재) 수행


개선 방법
SQL변경(O) : 서브쿼리를 조인으로 변경하여 효율적인 실행계획 유도
SQL변경(X) : 서브쿼리와 Main SQL에 조인 순서(LEADING), 조인 방법(NL_SJ),QUERY BLOCK(QB_NAME) 힌트 추가하여 효율적인 실행 계획 유도

서브쿼리를 조인으로 변경하기 위해서 추출 결과의 건수가 틀려지는 것을 방지하기 위해서 반드시 중복값을 제거하기 위해 SORT UNIQUE 오퍼레이션이 발생
서브쿼리의 추출 데이터가 5건에 불과해 추가적인 정렬 작업의 부하가 거의 없음.


서브쿼리 동작 방식을 제어하는 힌트들

사용 방법 참고(merge, no_merge, unnest, no_unnest, push_subq, no_push_subq, push_pred, no_push_pred)

https://horullll.tistory.com/19

사용 예시

SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_NL(T1@MAIN) */
       C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS (SELECT /*+ UNNEST QB_NAME(SUB) */'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4)

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        5    0.062        0.052          0       1654          0        320
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        7    0.062        0.053          0       1654          0        320

Misses in library cashe during parse   : 0
Optimizer Goal : ALL_ROWS
Parsing user : SCOTT (ID=84)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
    320  FILTER  (cr=1654 pr=0 pw=0 time=3509 us)
    320   NESTED LOOPS  (cr=1654 pr=0 pw=0 time=3349 us)
    320    NESTED LOOPS  (cr=1334 pr=0 pw=0 time=2871 us cost=477 size=7040 card=320)
      5     SORT UNIQUE (cr=1316 pr=0 pw=0 time=8 us cost=374 size=50 card=5)
      5      TABLE ACCESS FULL SUBQUERY_T2 (cr=1316 pr=0 pw=0 time=4 us cost=374 size=50 card=5)
    320     INDEX RANGE SCAN SUBQUERY_T1_IDX1_01 (cr=18 pr=0 pw=0 time=567 us cost=2 size=0 card=64)
    320    TABLE ACCESS BY INDEX ROWID SUBQUERY_T1 (cr=320 pr=0 pw=0 time=0 us cost=66 size=768 card=64)

1. 서브쿼리를 FILTER 동작 방식으로 수행하도록 제어

NO_UNNEST 힌트를 부여하면, Filter 동작 방식으로 수행하도록 제어할 수 있다.

SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ NO_UNNEST*/ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);
-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |    12 | 97718   (1)| 00:19:33 |
|*  1 |  FILTER                       |               |       |       |            |          |
|*  2 |   FILTER                      |               |       |       |            |          |
|*  3 |    TABLE ACCESS FULL          | SUBQUERY_T1   | 40000 |   468K| 10453   (3)| 00:02:06 |
|*  4 |   FILTER                      |               |       |       |            |          |
|*  5 |    TABLE ACCESS BY INDEX ROWID| SUBQUERY_T2   |     1 |    10 |     3   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | PK_SUBQUERY_2 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

2. NL SEMI JOIN으로 수행되도록 제어

SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST NL_SJ */ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |  1352 | 29744 | 90486   (1)| 00:18:06 |
|*  1 |  FILTER                       |               |       |       |            |          |
|   2 |   NESTED LOOPS SEMI           |               |  1352 | 29744 | 90486   (1)| 00:18:06 |
|*  3 |    TABLE ACCESS FULL          | SUBQUERY_T1   | 40000 |   468K| 10453   (3)| 00:02:06 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| SUBQUERY_T2   |    42 |   420 |     2   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN         | PK_SUBQUERY_2 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

3. HASH JOIN SEMI JOIN으로 수행되며, 서브쿼리를 Main SQL 테이블 보다 먼저 수행하도록 제어

UNNEST와 HASH_SJ, SWAP_JOIN_INPUTS힌트를 사용하면 서브쿼리부터 수행하도록 실행계획 제어 가능

SELECT
       C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST HASH_SJ SWAP_JOIN_INPUTS(T2)*/ 'X'
                FROM SUBQUERY_T2 T2
               WHERE T2.C1 = T1.C4
                 AND T2.C3 >= :B3
                 AND T2.C3 <= :B4);
----------------------------------------------------------------------------------
d  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
 0 | SELECT STATEMENT      |             |  1352 | 29744 | 10828   (3)| 00:02:10 |
 1 |  FILTER               |             |       |       |            |          |
 2 |   HASH JOIN RIGHT SEMI|             |  1352 | 29744 | 10828   (3)| 00:02:10 |
 3 |    TABLE ACCESS FULL  | SUBQUERY_T2 |  1250 | 12500 |   374   (3)| 00:00:05 |
 4 |    TABLE ACCESS FULL  | SUBQUERY_T1 | 40000 |   468K| 10453   (3)| 00:02:06 |
----------------------------------------------------------------------------------

4. SQL 서브쿼리를 HASH SEMI JOIN으로 수행하되, Main SQL 테이블을 먼저 수행하도록 제어

UNNEST와 HASH_SJ 힌트를 사용하면, HASH SEMI JOIN으로 수행하도록 제어
SEMI JOIN은 MAIN SQL쪽 테이블을 먼저 수행하는 것이 기본이나 HASH RIGHT SEMI JOIN으로 수행되면 조인 순서가 변경되므로 NO_SWAP_JOIN_INPUTS 힌트를 명시적으로 사용

SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST HASH_SJ NO_SWAP_JOIN_INPUTS(T2) */ 'X'
               FROM SUBQUERY_T2 T2
              WHERE T2.C1 = T1.C4
                AND T2.C3 >= :B3
                AND T2.C3 <= :B4);

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |  1352 | 29744 | 10828   (3)| 00:02:10 |
|*  1 |  FILTER             |             |       |       |            |          |
|*  2 |   HASH JOIN SEMI    |             |  1352 | 29744 | 10828   (3)| 00:02:10 |
|*  3 |    TABLE ACCESS FULL| SUBQUERY_T1 | 40000 |   468K| 10453   (3)| 00:02:06 |
|*  4 |    TABLE ACCESS FULL| SUBQUERY_T2 |  1250 | 12500 |   374   (3)| 00:00:05 |
-----------------------------------------------------------------------------------

5. SQL을 NL JOIN으로 수행하되, 서브쿼리를 수행하도록 제어

QB_NAME 힌트를 사용해 QUERY BLOCK명을 지정한 후, QUERY BLOCK 명을 지정한 후, 조인 순서와 조인 방법을 제어

SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_NL(T1@MAIN) */
       C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST QB_NAME(SUB) */ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  1351 | 29722 | 10829   (3)| 00:02:10 |
|*  1 |  FILTER              |             |       |       |            |          |
|*  2 |   HASH JOIN          |             |  1351 | 29722 | 10829   (3)| 00:02:10 |
|   3 |    SORT UNIQUE       |             |  1250 | 12500 |   374   (3)| 00:00:05 |
|*  4 |     TABLE ACCESS FULL| SUBQUERY_T2 |  1250 | 12500 |   374   (3)| 00:00:05 |
|*  5 |    TABLE ACCESS FULL | SUBQUERY_T1 | 40000 |   468K| 10453   (3)| 00:02:06 |
------------------------------------------------------------------------------------

6. HASH JOIN으로 처리하되, 서브쿼리부터 수행하도록 제어

SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_HASH(T1@MAIN) */
       C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST QB_NAME(SUB) */ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  1351 | 29722 | 10829   (3)| 00:02:10 |
|*  1 |  FILTER              |             |       |       |            |          |
|*  2 |   HASH JOIN          |             |  1351 | 29722 | 10829   (3)| 00:02:10 |
|   3 |    SORT UNIQUE       |             |  1250 | 12500 |   374   (3)| 00:00:05 |
|*  4 |     TABLE ACCESS FULL| SUBQUERY_T2 |  1250 | 12500 |   374   (3)| 00:00:05 |
|*  5 |    TABLE ACCESS FULL | SUBQUERY_T1 | 40000 |   468K| 10453   (3)| 00:02:06 |
------------------------------------------------------------------------------------

7. NOT EXISTS로 작성된 SQL을 NL JOIN ANTI로 수행하도록 제어

NOT EXISTS의 경우 NL JOIN ANTI로 수행 제어 하기 위해서 UNNEST, NL_AJ힌트를 부여

SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST NL_AJ */ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |  1352 | 29744 | 10828   (3)| 00:02:10 |
|*  1 |  FILTER               |             |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|             |  1352 | 29744 | 10828   (3)| 00:02:10 |
|*  3 |    TABLE ACCESS FULL  | SUBQUERY_T2 |  1250 | 12500 |   374   (3)| 00:00:05 |
|*  4 |    TABLE ACCESS FULL  | SUBQUERY_T1 | 40000 |   468K| 10453   (3)| 00:02:06 |
-------------------------------------------------------------------------------------

8. NOT EXISTS로 작성된 SQL을 HASH JOIN ANTI 조인으로 수행하도록 제어

SELECT C4,
       C5,
       C6
  FROM SUBQUERY_T1 T1
 WHERE C6 >= :B1
   AND C6 <= :B2
   AND EXISTS(SELECT /*+ UNNEST HASH_AJ */ 'X'
          FROM SUBQUERY_T2 T2
         WHERE T2.C1 = T1.C4
           AND T2.C3 >= :B3
           AND T2.C3 <= :B4);

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |  1352 | 29744 | 10828   (3)| 00:02:10 |
|*  1 |  FILTER               |             |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|             |  1352 | 29744 | 10828   (3)| 00:02:10 |
|*  3 |    TABLE ACCESS FULL  | SUBQUERY_T2 |  1250 | 12500 |   374   (3)| 00:00:05 |
|*  4 |    TABLE ACCESS FULL  | SUBQUERY_T1 | 40000 |   468K| 10453   (3)| 00:02:06 |
-------------------------------------------------------------------------------------

9. 서브쿼리를 먼저 읽은 후, NL 으로 수행

SELECT
       *
  FROM EMP E
 WHERE EMPNO IN (SELECT
                        MAX(EMPNO)
                   FROM EMP X
                  GROUP BY DEPTNO);

-- 서브쿼리를 먼저 읽은 후, NL로 수행

SELECT /*+ LEADING(X@SUB) QB_NAME(MAIN) USE_NL(E@MAIN) */
       *
  FROM EMP E
 WHERE EMPNO IN (SELECT /*+ UNNEST QB_NAME(SUB) */
                        MAX(EMPNO)
                   FROM EMP X
                  GROUP BY DEPTNO);

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    14 |  1400 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |          |    14 |  1400 |     8  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | EMP      |    14 |  1218 |     3   (0)| 00:00:01 |
|   3 |   VIEW               | VW_NSO_1 |    14 |   182 |     4  (25)| 00:00:01 |
|   4 |    HASH GROUP BY     |          |    14 |   364 |     4  (25)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EMP      |    14 |   364 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

의도한대로 제어되지 않는 이유는 OPTIMIZER가 서브쿼리를 인라인 뷰로 변경하는 SQL 최적화 작업을 수행했기 때문이다.
VW_NSO_1이란 점에서 추축 가능
SQL이 변경되고, 이로 인해 QUERY BLOCK명도 변경되어 QB_NAME 힌트는 물론, 다른 힌트들도 무시

이런 경우는 FROM절에 나열된 순서대로 조인 순서를 결정하는 ORDERED 힌트를 사용하면 유도 할 수 있음
LOGICAL OPTIMIZER가 서브쿼리를 인라인 뷰로 변경할 때 FROM절의 맨 앞에 위치 시키기 때문에 ORDERED로 유도 가능

SELECT /*+ ORDERED USE_NL(E) */
       *
  FROM EMP E
 WHERE EMPNO IN (SELECT /*+ UNNEST */
                        MAX(EMPNO)
                   FROM EMP X
                  GROUP BY DEPTNO);

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |    14 |  1400 |    18   (6)| 00:00:01 |
|   1 |  NESTED LOOPS                |          |       |       |            |          |
|   2 |   NESTED LOOPS               |          |    14 |  1400 |    18   (6)| 00:00:01 |
|   3 |    VIEW                      | VW_NSO_1 |    14 |   182 |     4  (25)| 00:00:01 |
|   4 |     HASH GROUP BY            |          |    14 |   364 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL       | EMP      |    14 |   364 |     3   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | PK_EMP   |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| EMP      |     1 |    87 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

SUBQUERY가 여러개 일 경우 ORDERED 힌트로 제어 불가.
여러개의 SUBQUERY가 존재할 경우는 SQL을 재작성하여 SUBQUERY를 인라인 뷰로 면경 후, 힌트로 실행계획을 제어 해야 함

Sort Aggregate 

 - Sort aggregate는 아래처럼 전체 로우를 대상으로 집계를 수행할 때 (집계함수 사용)나타나는, 

   'sort'라는 표현을 사용하지만 실제 소트가 발생하지는 않는다. 

select sum(sal), max(sal), min(sal) from emp;

--------------------------------------------
| Id  | Operation          | Name | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT   |      |        |
|   1 |  SORT AGGREGATE    |      |      1 |
|   2 |   TABLE ACCESS FULL| EMP  |   7013 |
--------------------------------------------

Sort Order by 

 - 데이터 정렬을 위해 order by 오퍼레이션을 수행할 때 나타난다. 

select * from emp order by sal desc;

-----------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |       |          |
|   1 |  SORT ORDER BY     |      |   7013 | 14336 | 14336 |12288  (0)|
|   2 |   TABLE ACCESS FULL| EMP  |   7013 |       |       |          |
-----------------------------------------------------------------------

Sort Group by 

 - sort group by는 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다. 

   ( group by 와 order by 같이 사용 시) 

select deptno, job, sum(sal), max(sal), min(sal)
from emp
group by deptno, job
order by deptno, job;

-----------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |       |          |
|   1 |  SORT GROUP BY     |      |      4 |  3072 |  3072 | 2048  (0)|
|   2 |   TABLE ACCESS FULL| EMP  |   7013 |       |       |          |
-----------------------------------------------------------------------

Hash Group By와 비교
10gR2에서 hash group by 방식이 도입되면서, order by절을 함께 명시하지 않으면 대부분 hash group by 방식으로 처리된다.

select deptno, job, sum(sal), max(sal), min(sal)
from emp
group by deptno, job

-----------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |       |          |
|   1 |  HASH GROUP BY     |      |      4 |   780K|   780K|  481K (0)|
|   2 |   TABLE ACCESS FULL| EMP  |   7013 |       |       |          |
-----------------------------------------------------------------------

hash group by는 정렬을 수행하지 않고 해싱 알고리즘을 사용해 데이터를 그룹핑한다.
읽는 로우마다 group by 컬럼의 해시 값으로 해시 버킷을 찾아 그룹별로 집계항목을 갱신하는 방식이다.
sort group by 라고 해서 모든 데이터를 정렬하고 나서 집계하는 것은 아니며, hash group by와
마찬가지로 읽는 로우마다 그룹별로 집계항목을 갱신한다.
다만, 그룹을 찾아가는 방식이 해싱 알고리즘이냐 소팅 알고리즘이냐 차이만 있을 뿐이다.
집계할 대상 로우가 아무리 많아도 그룹개수가 소수일 때는 두 방식 모두 디스크 소트가 전혀 발생하지 않는 것을 통해 이를 알 수 있다.

Hash Group by와 비교 

 - 10gR2에서 Hash group by 방식이 도입되면서, order by절을 함께 명시하지 않으면 

   대부분 hash group 방식으로 처리된다. 

Group by 결과의 정렬 순서 

 - 오라클은 9i부터 이미 group by 결과가 보장되지 않는다고 여러 문서를 통해 

   공식적으로 밝히고 있다. 

 - 실행계획에서 'sort group by'의 의미는 '소팅 알고리즘을 사용해 값을 집계한다'는 뜻일 뿐 

   결과의 정렬을 의미하지는 않는다. 물론 쿼리에 order by절을 명시하면 정렬 순서가 

   보장 되지만, 이때도 실행계획은 똑같이 'sort group by'로 표시된다. 

 - 10gR2에서 sort group by에서 sort group by가 나타나는 경우는 distinct , count 함수를 

   만났을 땐 항상 sort group by 방식으로 수행한다. 여기서도 결과는 정렬되지 않는다. 

Sort ( Unique ) 

 - Unnesting된 서브쿼리가 M쪽 집합이거나 Unique 인덱스가 없다면, 그리고 세미 조인으로 

   수행되지도 않느다면 메인 쿼리와 조인되기 전에 sort unique 오퍼레이션이 먼저 수행된다. 

   ( 여기서 M쪽 집합이 Unnesitng 된다고 했는데 이 튜닝을 하기 전에 고려해야 할 것은 

     M쪽 집합 서브쿼리가 Unnesting 되면 조인 형식이 되어서 경로가 바뀔 수 있다. )

 - 만약 PK/Unique 제약 또는 Unique 인덱스를 통해, Unnesting 된 서브쿼리의 Uniqueness가 

   보장된다면 sort unique 오퍼레이션은 생략된다. 

select  /*+ leading( b@subq ) use_nl(a) */ * 
from hr.DEPARTMENTS a
where a.DEPARTMENT_ID = 10
  and  exists ( select /*+ unnest qb_name(subq) */ 1 from hr.employees b where b.department_id  = a.department_id ) 
;

*************************[Explain Plan Time: 2025/08/07 16:47:12]*************************
Execution Plan
-----------------------------------------------------------
   0    7      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=24)
   1    6    0   NESTED LOOPS (Cost=3 Card=1 Bytes=24)
   2    4    1     NESTED LOOPS (Cost=3 Card=1 Bytes=24)
   3    2    2       SORT (UNIQUE) (Cost=1 Card=1 Bytes=3)
   4    1    3         INDEX (RANGE SCAN) OF 'HR.EMP_DEPARTMENT_IX' (INDEX) (Cost=1 Card=1 Bytes=3)
   5    3    2       INDEX (UNIQUE SCAN) OF 'HR.DEPT_ID_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
   6    5    1     TABLE ACCESS (BY INDEX ROWID) OF 'HR.DEPARTMENTS' (TABLE) (Cost=1 Card=1 Bytes=21)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   4 - access("B"."DEPARTMENT_ID"=10)
   5 - access("B"."DEPARTMENT_ID"="A"."DEPARTMENT_ID")
   5 - filter("A"."DEPARTMENT_ID"=10)
-----------------------------------------------------------



select  /*+ leading( b@subq ) use_nl(a) */ * 
from hr.DEPARTMENTS a
where a.DEPARTMENT_ID = 10
  and a.DEPARTMENT_ID in ( select /*+ unnest qb_name(subq) */ department_id from hr.employees b where b.department_id  = a.department_id ) 
;

*************************[Explain Plan Time: 2025/08/07 16:45:59]*************************
Execution Plan
-----------------------------------------------------------
   0    7      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=24)
   1    6    0   NESTED LOOPS (Cost=3 Card=1 Bytes=24)
   2    4    1     NESTED LOOPS (Cost=3 Card=1 Bytes=24)
   3    2    2       SORT (UNIQUE) (Cost=1 Card=1 Bytes=3)
   4    1    3         INDEX (RANGE SCAN) OF 'HR.EMP_DEPARTMENT_IX' (INDEX) (Cost=1 Card=1 Bytes=3)
   5    3    2       INDEX (UNIQUE SCAN) OF 'HR.DEPT_ID_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
   6    5    1     TABLE ACCESS (BY INDEX ROWID) OF 'HR.DEPARTMENTS' (TABLE) (Cost=1 Card=1 Bytes=21)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   4 - access("DEPARTMENT_ID"=10)
   5 - access("A"."DEPARTMENT_ID"="DEPARTMENT_ID")
   5 - filter("A"."DEPARTMENT_ID"=10)
-----------------------------------------------------------

 - union, minus, intersect 같은 집합연산자를 사용할 때도 아래와 같이 sort unique 오퍼레이션이 

   나타난다. 

select job, mgr from emp where deptno = 10
union
select job, mgr from emp where deptno = 20;

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |        |       |       |          |
|   1 |  SORT UNIQUE                  |                |   7014 |  3072 |  3072 | 2048  (0)|
|   2 |   UNION-ALL                   |                |        |       |       |          |
|*  3 |    TABLE ACCESS FULL          | EMP            |   7013 |       |       |          |
|   4 |    TABLE ACCESS BY INDEX ROWID| EMP            |      1 |       |       |          |
|*  5 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      1 |       |       |          |
--------------------------------------------------------------------------------------------

select job, mgr from emp where deptno = 10
union
select job, mgr from emp where deptno = 20;

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |        |       |       |          |
|   1 |  MINUS                        |                |        |       |       |          |
|   2 |   SORT UNIQUE                 |                |   7013 |  3072 |  3072 | 2048  (0)|
|*  3 |    TABLE ACCESS FULL          | EMP            |   7013 |       |       |          |
|   4 |   SORT UNIQUE                 |                |      1 | 73728 | 73728 |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMP            |      1 |       |       |          |
|*  6 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      1 |       |       |          |
--------------------------------------------------------------------------------------------

 - distinct 연산을 위해서도 sort unique 오퍼레이션이 사용된다. 

 - 오라클 10gR2부터는 group by처럼 distinct 연산에서도 order by를 생략하면 

   hash unique 방식으로 수행된다. 

select distinct deptno from emp order by deptno;

-----------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |       |          |
|   1 |  SORT UNIQUE       |      |      1 |  3072 |  3072 | 2048  (0)|
|   2 |   TABLE ACCESS FULL| EMP  |   7013 |       |       |          |
-----------------------------------------------------------------------

select distinct deptno from emp;

-----------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |       |          |
|   1 |  HASH UNIQUE       |      |      1 |  1518K|  1518K|  286K (0)|
|   2 |   TABLE ACCESS FULL| EMP  |   7013 |       |       |          |
-----------------------------------------------------------------------

 - 참고로, 10gR2에서 아래처럼 _convert_set_to_join 파라미터를 true로 설정하면 minus, 

   intersect 같은 집합 연산에 hash unique 오퍼레이션을 사용한다. 즉, 조인을 통해 두 집합을 

   연결하고 나서 중복을 제거하는 방식이다. 

  * 이 부분 연관하여 4장 11절 내용이 언급되는데 4장 11절은 null 허용 컬럼이기 때문에 

     그런 쿼리가 되는 건지와 distinct가 결과 출력 모양상 필요한 것인지 테스트할 필요가 있다. 

alter session set "_convert_set_to_join" = true;

select job, mgr from emp where deptno = 10
minus
select job, mgr from emp where deptno = 20;

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |        |       |       |          |
|   1 |  HASH UNIQUE                  |                |     22 |  1115K|  1115K|  550K (0)|
|*  2 |   HASH JOIN RIGHT ANTI        |                |   5610 |  1594K|  1594K|83968  (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP            |      1 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      1 |       |       |          |
|*  5 |    TABLE ACCESS FULL          | EMP            |   7013 |       |       |          |
--------------------------------------------------------------------------------------------

Sort Join 

 - sort join 오퍼레이션은 소트 머지 조인을 수행할 때 나타난다. 

 - outer 테이블에 pk 제약이나 unique 제약이 있을 때는 sort가 한 번만 발생한다. 

select /*+ordered use_merge(e) */*
from dept d, emp e
where d.deptno = e.deptno;

------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |       |          |
|   1 |  MERGE JOIN                  |         |   7013 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      4 |       |       |          |
|   3 |    INDEX FULL SCAN           | DEPT_PK |      4 |       |       |          |
|*  4 |   SORT JOIN                  |         |   7013 | 14336 | 14336 |12288  (0)|
|   5 |    TABLE ACCESS FULL         | EMP     |   7013 |       |       |          |
------------------------------------------------------------------------------------

DEPT 테이블의 인덱스를 사용하였기 때문에 소트가 한번만 발생하였다.

Window Sort 

 - window sort는 분석함수를 수행할 때 나타난다. 

select empno, ename, job, mgr, sal
     , avg(sal) over (partition by deptno)
  from emp;

-----------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |       |          |
|   1 |  WINDOW SORT       |      |    114 | 11264 | 11264 |10240  (0)|
|   2 |   TABLE ACCESS FULL| EMP  |    114 |       |       |          |
-----------------------------------------------------------------------

Index Full Scan은 인덱스 리프 블록을 처음부터 끝까지 모두 스캔하는 방식이며, 인덱스 선두 컬럼이 조건절에 없을 때 사용된다.

인덱스 선두 컬럼이 조건절에 없으면 Index Range Scan이 불가능하므로 테이블을 Full Scan해애 하는데, 컬럼이 많은 큰 테이블을 스캔하려면 블록 I/O가 많이 발생하므로 성능이 느리다. 그럴 때 컬럼이 적은 인덱스를 스캔하면 I/O 발생량을 줄일 수 있다. 단, 인덱스 필터 조건을 만족하는 데이터가 적어야 한다. ( 그래야지 테이블 ACCESS가 적으므로) 필터 조건을 만족하는 데이터가 많으면 테이블 랜덤 엑세스도 그만큼 많이 발생하므로 테이블 전체 스캔보다 성능이 훨씬 더 느려진다.(랜덤 엑세스는 sigle block I/O, table full scan은 multi block I/O). 

하지만, 필터 조건을 만족하는 데이터가 많더라도 결과집합 중 앞쪽 일부만 스캔하고 멈춘다면, 즉 부분 범위 처리가 가능하다면 Index Full Scan이 효과적일 수 있다. 인덱스 앞쪽에서 조건을 만족하는 데이터를 빨리 찾을 수 있기 때문이다.

// ※ Parameter는 Hint보다 우선권이 낮음. 

# Index 제어

   - OPTIMIZER_INDEX_CACHING (Default : 0)

      Index가 메모리에 캐싱되어 있을 확률을 의미. 80 ~ 95가 적당.

   - OPTIMIZER_INDEX_COST_ADJ (Default : 100)

      Index Cost를 조정하는 역할을 함. 즉, 값을 50으로 바꾸게 되면 Index Cost를 1/2로 감소시킴.

      그만큼 Index Scan이 선택될 확률을 높임. 5 ~ 10이 적당.

# CBQT(Cost Based Query Transformation) 제어

   - _OPTIMIZER_COST_BASED_TRANSFORMATION (Default : Linear)

      CBQT의 동작여부를 지정. 

   - _OPTIMIZER_PUSH_PRED_COST_BASED (Default : True)

      Cost Based Join Predicate Pushing의 동작여부를 지정.

   - _OPTIMIZER_CONNECT_BY_COST_BASED (Default : True)

      Cost Based Connect By Transformation의 동작여부를 지정.

# Prefetch

   - _INDEX_PREFETCH_FACTOR (Default : 100)

      이 값을 더 작게 설정할수록 옵티마이저는 인덱스 Prefetch를 더 선호하게 됨.

   - _DB_FILE_NONCONTIG_MBLOCK_READ_COUNT

     한 번에 최대 몇 개 블록을 Prefetch할지를 지정한다. 1로 지정하면 Prefetch 기능이 정지됨.

   - 테이블 Prefetch 

      + _TABLE_LOOKUP_PREFETCH_SIZE (Default : 40)

      + _TABLE_LOOKUP_PREFETCH_THRESH (Default : 2)

      + _MULTI_JOIN_KEY_TABLE_LOOKUP (Default : TRUE)

# Push Predicate 제어

   - _PUSH_JOIN_PREDICATE (Default : TRUE)

     뷰 Merging에 실패한 뷰 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화함.

   - _PUSH_JOIN_UNION_VIEW (Default : TRUE)

     union all을 포함하는 Non-mergeable View 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화함.

   - _PUSH_JOIN_UNION_VIEW2 (Default : TRUE)

     union을 포함하는 Non-mergeable View 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화함.

# Result Cache (11g 이상)

   - RESULT_CACHE_MODE (Default : MANUAL) [MANUAL | FORCE] 

     Result 캐시 등록 방식을 결정

     // manual : result_cache 힌트를 명시한 SQL만 등록 force : no_result_cache 힌트를 명시하지 않은 모든 SQL 등록

   - RESULT_CACHE_MAX_SIZE

     SGA 내에서 result_cache가 사용할 메모리 총량을 바이트로 지정. 0으로 설정하면 이 기능이 작동하지 않음.

   - RESULT_CACHE_MAX_RESULT (Default : 5)

     하나의 SQL 결과집합이 전체 캐시 영역에서 차지할 수 있는 최대 크기를 %로지정

   - RESULT_CACHE_REMOTE_EXPIRATION (Default : 0)

     remote 객체의 결과를 얼마동안 보관할지를 분 단위로 지정.

     remote 객체는 result 캐시에 저장하지 않도록 하려면 0으로 설정.

# 그 밖의 Parameter

   - OPTIMIZER_PERCENT_PARALLEL (Default : 0)

      이 값의 수치가 높을수록 parallel을 이용하여 풀 테이블 스캔으로 테이블을 액세스하려고 함.

   - OPTIMIZER_MODE

      옵티마이저 모드 설정

   - HASH_AREA_SIZE

      해쉬 조인할 때  PGA에 드라이빙할 수 있는 공간의 크기 설정

   - SORT_AREA_SIZE

      소트 머지 조인할 때 PGA에 드라이빙할 수 있는 공간의 크기 설정

   - DB_FILE_MULTIBLOCK_READ_COUNT (Default : 128) // db_block_size가 8k일 때, 최대값 128

      이 값의 수치가 높을수록 Optimizer가 Full Scan하려는 비중이 높아짐. (소트 머지 또는 해쉬 조인하려는 경향이 커짐.)

   - WORKAREA_SIZE_POLICY (Default : AUTO) [AUTO | MANUAL] 

      Optimizer가 *_AREA_SIZE를 자동으로 또는 수동으로 PGA 관리함을 설정

   - OPTIMIZER_DYNAMIC_SAMPLING (Default : 1 > 9i, 2 > 10g이상) [ 0 ~ 10 ]

      이 값의 크기가 클 수록  더 정확한 선택도와 카디널리티를 구하기위해 추가적인 Recursive SQL을 실행하며 OLTP 환경에서는 Default 값 이상으로 높이지 않음. 10g의 경우 통계정보가 없다면 '다이내믹 샘플링' 이 적용됨.

# Hidden Parameter

   - _b_tree_bitmap_plans (Default : TRUE) [TRUE | FALSE]

      bitmap conversion이 일어나게 할지 설정

   - _or_expand_nvl_predicate (Default : TRUE) [TRUE | FALSE]

      조건절에 nvl 사용 시 or expand가 일어나게 할지 설정

   - _direct_path_insert_features (Default : 0)

      direct path i/o가 발생하게 할지 설정

   - _nlj_batching_enabled (Default : 1)

      NL조인할 때 physical read할 때와 logical read할 때의 정렬 순서가 달라질 수 있는데 해당 파라미터를 0으로 바꿔 해결할 수 있음. (NL조인이 10g의 NL조인과 같은 방식으로 동작한다고 함.) 

      // NO_NLJ_BATCHING 힌트로도 해결할 수 있음.

   - _nlj_batching_misses_enabled (Default : 1)

      해당 파라미터를 0으로 설정 시, Batching NL조인은 그대로 유지하면서 bufferCache에 존재할 때와 그렇지 않을 때의 결과가 같을 수 있도록 함.

   - _optimizer_batch_table_access_by_rowid (Default : TRUE) (12c 이상)

      특정 테이블에 대해 배치 작업을 하고자 할 때 rowid로 접근할지 설정(?)

   - _no_or_expansion (Default : FALSE)

      or expand 사용할지 설정

   - _optimizer_unnest_scalar_sq (Default : TRUE) (12c 이상)

      TRUE로 설정 시, 스칼라 서브쿼리를 unnseting 할지 여부를 옵티마이저가 결정함.

      FALSE로 설정 시, 옵티마이저가 이 기능을 사용하지 않지만, 사용자가 unnest 힌트로 유도할 수 있음.

    - _optimizer_native_full_outer_join (Default : FORCE)

      TRUE로 설정 시, 옵티마이저가 Native Hash Full Outer 조인을 사용할 수 있게 함.

      FALSE로 설정 시, 옵티마이저가 Native Hash Full Outer 조인을 사용할 수 없게 함.

    - _complex_view_merging (Default : TRUE)

      TRUE로 설정 시, view가 merging 할 수 있게 함.

      FALSE로 설정 시, view가 merging 할 수 없게 함.

    - _optimizer_compute_index_stats (Default : TRUE)

      TRUE로 설정 시, 인덱스 신규생성이나 리빌드 시에 통계정보를 갱신하게함.

      FALSE로 설정 시, 인덱스 신규생성이나 리빌드 시에 통계정보를 갱신하지 않게함.

    - _optimizer_gather_stats_on_load (Default : TRUE) (12c 이상)

      batch load 시 테이블 통계정보 업데이트할지 여부 설정하는 파라미터

      보통 OFF 시키고 수동으로 통계정보 업데이트한다고 함. 

    - _optimizer_gather_stats_on_load_index (Default : TRUE) (12c 이상)

      batch load 시 index 통계정보 업데이트할지 여부 설정하는 파라미터

      보통 OFF 시키고 수동으로 통계정보 업데이트한다고 함. 

    - _query_execution_cache_max_size (Default : 4194304?) (10g 이상?)

      스칼라(scalar) 서브쿼리 8i, 9i 기준으로는 256개 엔트리를 캐싱, 10g이후로는 입력과 출력 값 크기,

      _query_execution_cache_max_size 파라미터에 의해 사이즈를 결정함.

    - _optimizer_group_by_place (Default : TRUE) 

출처 : https://chess-drive.tistory.com/14

개념

기본적으로 OR로 묶인 where 조건의 경우 OR의 사용으로 INDEX를 사용할 수 없어 FULL TABLE SCAN을 해야한다. 그러나 OR-Expansion을 사용하게 되면 INDEX를 사용할 수 있게 되면서 해당 결과 집합을 UNION ALL로 합쳐서 사용한다.

즉, 결론적으로 INDEX를 사용하며 UNION ALL로 합쳐주는 작업으로 이해하면 쉽다. 하지만 테이블을 2번 ACCESS한다는 단점이 있어서 겹치는 부분이 적을때 효과적이다. 

HINT

USE_CONCAT   : OR-Expansion을 유도하고자 할 때 사용

NO_EXPAND    :  OR-Expansion을 방지하고자 할 때 사용 

• alter session set "_no_or_expansion"=true; 로도 설정가능

-힌트사용(use_concat) 옵티마이저에 의한 쿼리변환 
select /*+ use_concat */* from emp
where job='CLERK'  or  deptno=20;

Execution Plan
----------------------------------------------------------
Plan hash value: 668283641

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     8 |   304 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |                |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     4 |   152 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IDX    |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP            |     4 |   152 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("JOB"='CLERK')
   4 - filter(LNNVL("JOB"='CLERK'))
   5 - access("DEPTNO"=20)

분기된 쿼리가 각각 다른 인덱스를 사용하긴 하지만 emp 테이블의 경우 엑세스가 두번 일어난다. 

중복 엑세스되는 영역의 비중이 작을수록 효과적이고, 그 반대의 경우라면 오히려 쿼리 수행 비용이 증가한다. 

중복엑세스 되더라도 결과집합에는 중복이 없게 하려고 내부적으로 LNNVL 함수를 사용한것을 볼 수 있다. 

* LNNVL 함수 : 두 쿼리의 교집합이 두번 출력되는 것을 방지하기 위해서 OR-Expansion 사용시 자동으로 사용되는 함수

job<>'CLERK' 이거나 job is null 인 집합만 읽으려는 것이며 
이 함수는 조건식이 false 이거나 알수없는 값일때 true를 리턴한다.
  LNNVL(1=1)    : FALSE
  LNNVL(1=2)    : TRUE
  LNNVL(Null=1) : TRUE

같은 컬럼에 대한 OR-Expansion

-OR절 
select  *
  from emp
 where (deptno = 10 or deptno = 30)
   and ename = 'CLARK';

Execution Plan
----------------------------------------------------------
Plan hash value: 1707373705

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     9 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ENAME"='CLARK')
   3 - access("DEPTNO"=10 OR "DEPTNO"=30)

-OR OR-Expansion 유도
-use_concat 힌트에 아래와 같이 인자를 제공하여  유도할수 있다 

select /*+  qb_name(MAIN) use_concat(@MAIN 1) */ *
  from emp e
 where (deptno = 10 or deptno = 30)
   and ename = 'CLARK';

Execution Plan
----------------------------------------------------------
Plan hash value: 809118877

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     2 |    76 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |                |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     3 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     6 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ENAME"='CLARK')
   3 - access("DEPTNO"=10)
   4 - filter("ENAME"='CLARK')
   5 - access("DEPTNO"=30)



-IN 절 
select  *
  from emp
 where deptno in (10, 30)
   and ename = 'CLARK';



Execution Plan
----------------------------------------------------------
Plan hash value: 1707373705

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     9 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ENAME"='CLARK')
   3 - access("DEPTNO"=10 OR "DEPTNO"=30)

-IN 절  OR-Expansion 유도
select /*+  qb_name(MAIN) use_concat(@MAIN 1) */ *
  from emp
 where deptno in (10, 30)
   and ename = 'CLARK';


Execution Plan
----------------------------------------------------------
Plan hash value: 809118877

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     2 |    76 |     4   (0)| 00:00:01 |
|   1 |  CONCATENATION               |                |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     3 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    38 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     6 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ENAME"='CLARK')
   3 - access("DEPTNO"=10)
   4 - filter("ENAME"='CLARK')
   5 - access("DEPTNO"=30)


-하지만 or-expansion을 유도해도 나아지는 점이 없으므로 굳이 그렇게 할 이유가 없다.

9i 까지는 같은 컬럼에 대한 or 조건이나 in-list도 OR-Expansion이 작동할 수 있었지만, 10g 부터는 기존적으로 아래와 같이 In-List Iterator 방식으로만 처리된다. 만약 억지로 OR-Expansion으로 유도하려면 Use_Concat 힌트에 qb_name 인자를 제공하면 되지만, In-List Iterator에 비해 나은 점이 없으므로 굳이 그렇게 할 이유는 없다. 

* 기본적으로 index가 있어야 OR-Expansion 이 가능하다. 없으면 실행계획에 concatenation 실행계획이 발생하지 않음!

9i까지는 위와 같은 형태 즉, 같은 컬럼에 대한 OR 조건이나 IN-List도 OR-Expansion이 작동할 수 있었지만 10g 부터는 기본적으로 IN-List Iterator 방식으로만 처리된다.

주의) 9i까지는 OR조건이나 IN-List를 힌트를 이용해 OR-Expansion으로 유도하면 뒤쪽에 놓인 값이 항상 먼저 출력됐었다.
하지만 10g CPU 비용 모델에서는 위와 같이 OR-Expansion으로 유도했을 때 통계적으로 카디널리티가 작은 값이 먼저 출력된다. 9i 이전처럼 뒤쪽에 놓인 값이 먼저 출력되도록 하려면 ordered_predicates 힌트를 사용하거나 IO 비용 모델로 바꿔줘야 한다.

10g 이후 버전이더라도 비교 연산자가 ‘=’ 조건이 아닐 때는 일반적인 use_concat 힌트만으로도 같은 컬럼에 대한 OR-Expansion이 잘 작동한다.

NVL/DECODE 함수를 사용한 조건식에 대한 OR-Expansion 

조건절에 NVL이나 DECODE 함수를 사용해도 OR-Expansion 실행계획이 발생할 수 있다.

해당 변수 값의 NULL 여부에 따라 위 또는 아래쪽 브랜치만 수행하는 방식으로 무엇보다 중요한 것은 변수 값의 입력 여부에 따라 다른 인덱스를 사용가능하다는 사실이다.

이 유용한 기능을 제어하는 파라미터는 '_or_expand_nvl_predicate'  가 있다. 

NVL

select *
  from emp
 where deptno = nvl(:deptno, deptno)
   and ename like :ename || '%';

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=3 Bytes=114)
   1    0   CONCATENATION
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=2 Card=2 Bytes=76)
   4    3         INDEX (RANGE SCAN) OF 'SCOTT.EMP_N4' (INDEX) (Cost=1 Card=2)
   5    1     FILTER
   6    5       TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=2 Card=1 Bytes=38)
   7    6         INDEX (RANGE SCAN) OF 'SCOTT.EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - filter(:DEPTNO IS NULL)
   3 - filter("DEPTNO" IS NOT NULL)
   4 - access("ENAME" LIKE :ENAME||'%')
   4 - filter("ENAME" LIKE :ENAME||'%')
   5 - filter(:DEPTNO IS NOT NULL)
   6 - filter("ENAME" LIKE :ENAME||'%')
   7 - access("DEPTNO"=:DEPTNO)
-----------------------------------------------------------

-위쪽 브렌치는 EMP_N4 사용
-아래 브렌치는 EMP_DEPTNO_IDX 사용


-위와 같은 형태로 쿼리를 작성하면 오라클 9i에서는 아래와 같은 OR-Expansion 쿼리 변환이 일어난다 
select * from emp
 where :deptno is null
   and deptno is not null
   and ename like :ename || '%'
 union all
select * from emp
 where :deptno is not null
   and deptno = :deptno
   and ename like :ename || '%';
   
 Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=3 Bytes=114)
   1    0   UNION-ALL
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=2 Card=2 Bytes=76)
   4    3         INDEX (RANGE SCAN) OF 'SCOTT.EMP_N4' (INDEX) (Cost=1 Card=2)
   5    1     FILTER
   6    5       TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=2 Card=1 Bytes=38)
   7    6         INDEX (RANGE SCAN) OF 'SCOTT.EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - filter(:DEPTNO IS NULL)
   3 - filter("DEPTNO" IS NOT NULL)
   4 - access("ENAME" LIKE :ENAME||'%')
   4 - filter("ENAME" LIKE :ENAME||'%')
   5 - filter(:DEPTNO IS NOT NULL)
   6 - filter("ENAME" LIKE :ENAME||'%')
   7 - access("DEPTNO"=TO_NUMBER(:DEPTNO))
-----------------------------------------------------------
-deptno 변수값의 null 여부에 따라 위 또는 아래쪽 브렌치만 수행하는것이다.
-decode 함수를 사용하더라도 같은 처리가 일어난다

DECODE

select * from emp
 where deptno = decode(:deptno, null, deptno, :deptno)
   and ename like :ename || '%';

Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=3 Bytes=114)
   1    0   CONCATENATION
   2    1     FILTER
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=2 Card=2 Bytes=76)
   4    3         INDEX (RANGE SCAN) OF 'SCOTT.EMP_N4' (INDEX) (Cost=1 Card=2)
   5    1     FILTER
   6    5       TABLE ACCESS (BY INDEX ROWID) OF 'SCOTT.EMP' (TABLE) (Cost=2 Card=1 Bytes=38)
   7    6         INDEX (RANGE SCAN) OF 'SCOTT.EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   2 - filter(:DEPTNO IS NULL)
   3 - filter("DEPTNO" IS NOT NULL)
   4 - access("ENAME" LIKE :ENAME||'%')
   4 - filter("ENAME" LIKE :ENAME||'%')
   5 - filter(:DEPTNO IS NOT NULL)
   6 - filter("ENAME" LIKE :ENAME||'%')
   7 - access("DEPTNO"=:DEPTNO)
-----------------------------------------------------------

-dbptno 변수값의 입력 여부에 따라 다른 인덱스를 사용한다
-deptno 변수에 null 이 들어오면 위쪽 EMP_N4를 사용하고, null값이 아닌 값이 들어오면 EMP_DEPTNO_IDX를 사용하게 된다

주의할 점은 NVL 또는 DECODE를 여러 컬럼에서 사용했을때는 그 중 변별력이 가장 좋은 컬럼을 기준으로 한번만 분기가 일어난다. 이러한 이유로 옵션 조건이 복잡할 때는 이 방식에만 의존하기 어렵고 그럴 때는 여전히 수동으로 UNION ALL 분기를 해주는 것이 필요하다.

부분범위처리의 원리

결과 집합을 전송할 때, 전체 데이터를 쉼 없이 연속적으로 처리하지 않고 사용자로부터 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

• 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 해오는 메커니즘은 아래와 같다.

1. 최초 rs.next() 호출 시 한꺼번에 100건을 가져와서 클라이언트 Array 버퍼에 캐싱한다.
2. 이후 rs.next() 호출할 때는 데이터베이스 Call을 발생시키지 않고 Array 버퍼에서 읽는다.
3. 버퍼에 캐싱 돼 있던 데이터를 모두 소진한 후 101번째 rs.next() 호출 시 다시 100건을 가져온다.
4. 모든 결과집합을 다 읽을 때까지 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

https://velog.io/@yooha9621/5-%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-Call-%EC%B5%9C%EC%86%8C%ED%99%94-%EC%9B%90%EB%A6%AC-2.-User-Call-vs.-Recursive-Call-rcglr212

1. 전통적인 실행계획

 Rows Row Source Operation
 ---- -------------------------------------------------------
    5 NESTED LOOPS
    3   TABLE ACCESS BY INDEX ROWID OF 사원 
    5     INDEX RANGE SCAN OF 사원_X1 
    5   TABLE ACCESS BY INDEX ROWID OF 고객 
    8     INDEX RANGE SCAN OF 고객_X1

버전이 올라가면서 오라클은 NL 조인 성능을 높이기 위해 테이블 Prefetch, 배치 I/O 기능을 도입한다. 

2. Table Prefetch 

인덱스를 이용해 테이블을 엑세스 하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능.

이는 Inner쪽 테이블에 대한 디스크 I/O 과정에 테이블 Prefetch 기능이 작동할 수 있음을 표시하기 위함이다. 

nlj_prefetch, no_nlj_prefetch 힌트를 이용해 이 실행계획이 나오게 할 수도 있고, 안 나오게 할 수도 있다. 

 Rows Row Source Operation
 ---- -------------------------------------------------------
   5  TABLE ACCESS BY INDEX ROWID OF 고객                        --> prefetch!!
  12    NESTED LOOPS 
   3      TABLE ACCESS BY INDEX ROWID OF 사원
   3        INDEX RANGE SCAN OF 사원_X1 
   8      INDEX RANGE SCAN OF 고객_X1

3. 배치 I/O 

디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능 

(Prefetch는 미리 가져오고, 배치I/O는 미뤘다 한꺼번에 가져온다.)

1. 리프 블록의 rowid로 버퍼 캐시 조회 -> 실패 시 rowid 저장 

2. 실패한 rowid 일정량 저장 시 블록 번호로 정렬하여 Multiblock I/O 수행 

기존 single I/O 방식에서는 인덱스 만으로 결과 집합의 정렬이 보장되었으나, Batch I/O 작동시 이러한 방식의 결과 집합을 보장하지 않게 되었다.

밑의 실행계획은 Inner쪽 테이블에 대한 디스크 I/O 과정에 배치 I/O 기능이 작동할 수 있음을 표시한다.

nlj_batching, no_nlj_batching 힌트를 이용해 이 실행계획이 나오게 할 수도 있고, 안 나오게 할 수도 있다.

 Rows Row Source Operation
 ---- -------------------------------------------------------
    5 NESTED LOOPS
    8   NESTED LOOPS
    3     TABLE ACCESS BY INDEX ROWID OF 사원
    3       INDEX RANGE SCAN OF 사원_X1
    8     INDEX RANGE SCAN OF 고객_X1
    5   TABLE ACCESS BY INDEX ROWID OF 고객                            --> batch I/O!!

두 기능 모두, 읽는 블록마다 건건이 I/O Call을 발생시키는 비효율을 줄이기 위해 고안 되었다.

오라클 11g에서는 위 세가지 실행계획이 모두 나타날 수 있는데, Inner쪽 테이블 블록을 모두 버퍼캐시에서 읽는 다면 어떤 방식으로 수행하든 성능에 차이가 없다. 데이터 출력 순서도 100% 같다. 

다만, '일부를 디스크에서 읽게 되면' 성능에 차이가 나타날 수 있고,배치 I/O 실행계획이 나타날 때는  결과집합의 정렬 순서도 다를 수 있어 특별한 주의가 필요 하다. 

내부 Algorithm 

example table

CREATE TABLE t2 (id VARCHAR2(30), amount NUMBER);
INSERT INTO t2 VALUES ('A', 10);
INSERT INTO t2 VALUES ('C',  5);
INSERT INTO t2 VALUES ('B',  1);
INSERT INTO t2 VALUES ('B',  2);
INSERT INTO t2 VALUES ('A',  3);
INSERT INTO t2 VALUES ('C',  1);
INSERT INTO t2 VALUES ('A',  7);

SELECT id, sum(amount)
  FROM t2
 GROUP BY id;

Sort group by

전체 테이블을 group by 절의 컬럼으로 정렬 후 sub(amount) 같은 집계 함수를 실행한다.

ID   Amount SUM
A    10
A     3
A     7     10+3+7=20

B     1
B     2     1+2=3

C     5
C     1     5+1=6

Hash group by 

테이블을 한 번 스캔하고 각 행에 대한 해시 값을 계산한 다음 해당 행을 버킷(메모리 또는 디스크)에 넣는다.

SELECT id, ora_hash(id, 4), amount from t2;

ID Bucket  Amount  Hash table
A     2      10    Bucket#2: A=10
C     4       5    Bucket#4: C=5
B     2       1    Bucket#2: A=10, B=1
B     2       2    Bucket#2: A=10, B=1+2
A     2       3    Bucket#2: A=10+3, B=1+2
C     4       1    Bucket#4: C=5+1
A     2       7    Bucket#2: A=10+3+7, B=1+2

모든 값을 버킷에 넣은 후 해시 테이블을 스캔하여 집계를 계산한다.

Bucket#2: A=10+3+7, B=1+2
Bucket#4: C=5+1

성능

example big table 

CREATE TABLE t AS 
SELECT RPAD(object_type, 3000, 'x') as gby, o.* 
  FROM all_objects o WHERE rownum <= 50000; COMMIT;
INSERT INTO t SELECT * FROM t; COMMIT;
EXEC dbms_stats.gather_table_stats(user, 't');

/*+ use_hash_aggregation */  힌트를 사용해 hash group by 사용을 유도할 수 있다. 

SELECT /*+ USE_HASH_AGGREGATION */ gby, count(*)
  FROM t
 GROUP BY gby;

/*+ no_use_hash_aggregation */  힌트를 사용하면 sort group by 사용을 유도할 수 있다.

SELECT /*+ NO_USE_HASH_AGGREGATION */ gby, count(*)
  FROM t
 GROUP BY gby;

SELECT * FROM v$sql WHERE sql_text LIKE '%USE_HASH_AGGREGATION%'; 

SELECT * FROM v$sql_workarea WHERE sql_id IN ('663t56n1tdr59','fp5z7z1fyz42p');

OPERATION_TYPE  EST_OPT_SIZE LAST_MEM_USED ACTIVE_TIME MAX_TEMP
GROUP BY (HASH)       697344       1519616      325145        -
GROUP BY (SORT)       145408        129024      460975        -

GROUP BY HASH는 1519616바이트의 메모리가 필요하고 0.325145초 만에 실행된 반면, GROUP BY SORT는 캐시를 10분의 1 미만으로 사용했지만 약간 더 오래 실행되었다. 둘 다 메모리에서 완전히 실행되었다.

메모리가 부족해 디스크를 사용하게 된다면(여기서 메모리 제한을 인위적으로 낮추어 강제로 수행할 수 있음) max_tempseg_size 열의 값이 보인다.

ALTER SESSION SET workarea_size_policy = MANUAL;
ALTER SESSION SET sort_area_size = 10000;

OPERATION_TYPE  EST_OPT_SIZE LAST_MEM_USED ACTIVE_TIME  MAX_TEMP
GROUP BY (HASH)       697344        623616    22756184 268435456
GROUP BY (SORT)       103424         43008     1064479   4194304

만약 메모리가 부족해 disk를 사용한다면 HASH  GROUP BY 는 256MB 디스크가 필요하고 22.7초 만에 실행된 반면, GROUP BY SORT는 4MB 디스크만 필요하고 1.1초 만에 실행되었습니다. 즉 HASH GROUP BY는 Disk 가 사용되는 순간 성능이 매우 안좋아지는것을 확인할 수 있다.