Optimizing Oracle Optimizer (2011년)
목록
Filter Operation의 Cache 효과 0 0 2,669

by 구루비스터디 Transformation [2018.07.14]

Filter Operation의 Cache 효과

Table Full Scan 일량 확인


SQL> set pagesize 0
SQL> set linesize 130
SQL> SELECT /*+ gather_plan_statistics */
  2         MAX(c1)
  3    FROM t1
  4   WHERE c2 = 'dummy'
  5  ;
     10000

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  0jgfqwmumc0y4, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        MAX(c1)   FROM t1  WHERE c2 = 'dummy'

Plan hash value: 3724264953

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      38 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |  10000 |    12   (9)|  10000 |00:00:00.01 |      38 |
--------------------------------------------------------------------------------------------------

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

   2 - filter("C2"='dummy')


18 개의 행이 선택되었습니다.

SQL> SELECT /*+ gather_plan_statistics */
  2         MAX(c1)
  3    FROM t1
  4   WHERE c3 = 1
  5  ;
     10000

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  45aqvrk98b63j, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        MAX(c1)   FROM t1  WHERE c3 = 1

Plan hash value: 3724264953

--------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      38 |
|*  2 |   TABLE ACCESS FULL| T1   |      1 |   1000 |    11   (0)|   1000 |00:00:00.01 |      38 |
--------------------------------------------------------------------------------------------------

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

   2 - filter("C3"=1)


18 개의 행이 선택되었습니다.


  • 총 10000 Rowd의 Table T1 을 Full Scan 하는데 38 Block 의 Logical Read 필요


Filter Operation 이 사용되었다면 일량은 얼마가 되어야 할까?


SQL> SELECT /*+ gather_plan_statistics */
  2         *
  3    FROM t1 y
  4   WHERE c1 IN (SELECT /*+ NO_UNNEST */ MAX(c1) FROM t1 x WHERE x.c3 = y.c3 )
  5  ;
      9991 dummy                         2
      9992 dummy                         3
      9993 dummy                         4
      9994 dummy                         5
      9995 dummy                         6
      9996 dummy                         7
      9997 dummy                         8
      9998 dummy                         9
      9999 dummy                        10
     10000 dummy                         1

10 개의 행이 선택되었습니다.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  9thkv08ta6naz, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        *   FROM t1 y  WHERE c1 IN (SELECT /*+
NO_UNNEST */ MAX(c1) FROM t1 x WHERE x.c3 = y.c3 )

Plan hash value: 2626881942

---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |      |      1 |        |            |     10 |00:00:00.01 |     419 |
|   2 |   TABLE ACCESS FULL | T1   |      1 |  10000 |    11   (0)|  10000 |00:00:00.01 |      39 |
|   3 |   SORT AGGREGATE    |      |     10 |      1 |            |     10 |00:00:00.01 |     380 |
|*  4 |    TABLE ACCESS FULL| T1   |     10 |   1000 |    11   (0)|  10000 |00:00:00.01 |     380 |
---------------------------------------------------------------------------------------------------

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

   1 - filter("C1"=)
   4 - filter("X"."C3"=:B1)


22 개의 행이 선택되었습니다.


Filter Operation 이 사용되었다면 일량은 얼마가 되어야 할까?
  • 10000개의 Row 에 매번 Filter 작업을 한다면 예측되는 일량(Logical Read)은 38*10000 Block이다.
  • 하지만 테스트 결과는 380 Block(38 * 10)이다.
  • 조건으로 주어지는 c3의 Distinct 갯수(10개)만큼만 Subquery가 수행되었음을 알 수 있다.
  • 조건을 c2로 바꾸면 서브쿼리는 한번만 수행이 된다.




SQL> SELECT /*+ gather_plan_statistics */
  2         *
  3    FROM t1 y
  4   WHERE c1 IN (SELECT /*+ NO_UNNEST */ MAX(c1) FROM t1 x WHERE x.c2 = y.c2 )
  5  ;
     10000 dummy                         1

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID  23x8b104hz9xd, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        *   FROM t1 y  WHERE c1 IN (SELECT /*+
NO_UNNEST */ MAX(c1) FROM t1 x WHERE x.c2 = y.c2 )

Plan hash value: 2626881942

---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |      |      1 |        |            |      1 |00:00:00.01 |      76 |
|   2 |   TABLE ACCESS FULL | T1   |      1 |  10000 |    11   (0)|  10000 |00:00:00.01 |      38 |
|   3 |   SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      38 |
|*  4 |    TABLE ACCESS FULL| T1   |      1 |  10000 |    12   (9)|  10000 |00:00:00.01 |      38 |
---------------------------------------------------------------------------------------------------

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

   1 - filter("C1"=)
   4 - filter("X"."C2"=:B1)


22 개의 행이 선택되었습니다.


  • Filter Operation 에 의해 수행되는 조건의 Input값(여기서는 서브쿼리)을 Cache하며
    • 동일한 Input이 사용되는 경우 추가적인 읽기 작업 없이 캐시된 값을 사용한다.
    • 이것을 흔히 Filter Optimization 이라 부른다.
  • Filter Optimization 이 Filter Operation 의 비효울을 경감시켜주긴 하지만
    • Subquery Unnesting 의 장점에 비할바가 아니다.


Deterministic Function 과 Cache 효과
  • Oracle 9i 의 Performance Tuning Guide 에 다음과 같은 언급이 있다.
    • Deterministic Function 을 일반 SQL에서 사용하면 Cache 효과를 얻을 수 있다는 것인데.
  • 불행하게도 이런 효과는 Oracle 10g R2 부터 구현되었다.
    • Oracle 9i 의 Manual 이 너무 성급한 발언을 한 셈이다.


Test용 Table


SQL> DROP TABLE t1 PURGE;

테이블이 삭제되었습니다.

SQL> -- 예시자료 생성 --
SQL> CREATE TABLE t1(c1 INT, c2 INT);

테이블이 생성되었습니다.

SQL> INSERT INTO t1
  2  SELECT MOD(level, 10) + 1 c1    -- 10개값(1~10)
  3       , level              c2    -- 유일값(1~1000)
  4    FROM dual
  5   CONNECT BY level <= 1000
  6  ;

1000 개의 행이 만들어졌습니다.

SQL> COMMIT;

커밋이 완료되었습니다.

SQL> -- 패키지 변수 생성 --
SQL> CREATE OR REPLACE PACKAGE p1 AS g_idx INT := 0; END;
  2  /

패키지가 생성되었습니다.

SQL> -- Deterministic Function 생성 --
SQL> CREATE OR REPLACE FUNCTION f1(v1 INT)
  2  RETURN NUMBER
  3  DETERMINISTIC
  4  IS
  5  BEGIN
  6      p1.g_idx := p1.g_idx + 1;
  7      RETURN 1;
  8  END;
  9  /

함수가 생성되었습니다.


함수 호출 테스트 : 쿼리



SQL> -- 함수 호출 테스트 : 쿼리 --
SQL> set serveroutput on
SQL> EXEC p1.g_idx := 0;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT f1(c1) FROM t1 WHERE ROWNUM <= 100;
         1

100 개의 행이 선택되었습니다.

SQL> EXEC dbms_output.put_line('count = 100, call = ' || p1.g_idx);
count = 100, call = 70

PL/SQL 처리가 정상적으로 완료되었습니다.


Deterministic Function 에 대한 Cache 기능이 동작했다면?
  • Function Call 은 10 이 나와야 한다. 동작하지 않았다면 100 이 나와야 한다. Why 70 ?
  • SQL*Plus 의 기본 Fetch Size 는 15 이며, 100건을 Fetch 하려면 100/15=7 총 7회의 Fetch Call 이 발생한다.
  • 각 Fetch Call 마다 Cache 가 이루어지므로 7*10=70 총 70회의 Function Call 이 발생했다.


Fetch Call 문제 해결을 위해 PL/SQL Block 내에서 실행해 보자.
  • PL/SQL Block 의 Fetch Call 은 1회에 불과하므로 완벽한 Cache 효과가 발생해야 한다.
  • 아래 실행 결과 완벽한 Cache 효과가 발생했음을 알 수 있다.


함수 호출 테스트 : PL/SQL



SQL> -- 함수 호출 테스트 : PL/SQL --
SQL> DECLARE
  2      v_count NUMBER;
  3  BEGIN
  4      FOR idx IN 1..100 LOOP
  5          p1.g_idx := 0;
  6          FOR r IN (SELECT f1(c1) FROM t1 WHERE ROWNUM <= idx) LOOP
  7              null;
  8          END LOOP;
  9          dbms_output.put_line('count = ' || idx || ', call = ' || p1.g_idx);
 10      END LOOP;
 11  END;
 12  /
count = 1, call = 1
count = 2, call = 2
count = 3, call = 3
count = 4, call = 4
count = 5, call = 5
count = 6, call = 6
count = 7, call = 7
count = 8, call = 8
count = 9, call = 9
count = 10, call = 10
count = 11, call = 10
count = 12, call = 10
...
count = 97, call = 10
count = 98, call = 10
count = 99, call = 10
count = 100, call = 10

PL/SQL 처리가 정상적으로 완료되었습니다.


  • Fetch Array Size 를 크게 해주면 Deterministic Function의 Cache 효과가 더욱 개선된다..


Fetch Size 증가 후 함수 호출 테스트 : 쿼리



SQL> -- Fetch Size 증가 후 함수 호출 테스트 : 쿼리 --
SQL> set arraysize 1000
SQL> EXEC p1.g_idx := 0;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT f1(c1) FROM t1 WHERE ROWNUM <= 100;
         1

100 개의 행이 선택되었습니다.

SQL> EXEC dbms_output.put_line('count = 100, call = ' || p1.g_idx);
count = 100, call = 11

PL/SQL 처리가 정상적으로 완료되었습니다.


  • Fetch Array Size 를 1000 으로 크게 키워준 결과 Function Call 횟수가 11회로 완벽한 Cache 효과가 이루어 진것을 알 수 있다..
    • 단 10 회가 아닌 11회가 발생한 것은 SqQL*Plus 내부 Mechanism 으로 인한 것이다.


Fetch Size 낮춘 후 함수 호출 테스트 : 쿼리



SQL> -- Fetch Size 낮춘 후 함수 호출 테스트 : 쿼리 --
SQL> set arraysize 1
SQL> EXEC p1.g_idx := 0;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT f1(c1) FROM t1 WHERE ROWNUM <= 100;
         1

100 개의 행이 선택되었습니다.

SQL> EXEC dbms_output.put_line('count = 100, call = ' || p1.g_idx);
count = 100, call = 100

PL/SQL 처리가 정상적으로 완료되었습니다.


Fetch Array Size 를 낮추면 Cache 효과는 감소한다.
  • Fetch Array Size 를 1로 설정하면 Cache 효과는 없다.
  • Fetch Call 수 = Function Call 수 = 100


  • Deterministic Function 을 사용하지 못하는 경우나 Oracle 10g R2 이전 버전의 경우 Function Call Cache 효과를 얻으려면?
    • 다음과 같이 Subquery 를 사용하는 것이다.


서브쿼리를 이용한 함수 호출 테스트 : 쿼리



SQL> -- 서브쿼리를 이용한 함수 호출 테스트 : 쿼리 --
SQL> set arraysize 1
SQL> EXEC p1.g_idx := 0;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT (SELECT f1(c1) FROM dual) FROM t1 WHERE ROWNUM <= 100;
                     1

100 개의 행이 선택되었습니다.

SQL> EXEC dbms_output.put_line('count = 100, call = ' || p1.g_idx);
count = 100, call = 10

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>
"데이터베이스 스터디모임" 에서 2009년에 "OPTIMIZING ORACLE OPTIMIZER " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3917

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입