Optimizing Oracle Optimizer (2009년)
Filter Operation의 Cache 효과 0 0 52,838

by 구루비스터디 Transformation Filter Operation [2018.07.14]


  1. Filter Operation의 Cache효과
    1. 추가 테스트 1
    2. Deterministic Function과 Cache효과
    3. 추가 테스트 2


Filter Operation의 Cache효과

  • 아래의 결과를 보면 총 10,000개의 Row를 가지는 Table t1을 Full Scna하는데 38 Block의 Logical Reads가 필요하다.

select /*+ gather_plan_statistics */
  max(c1)
from t1
where c2 = 'dummy'
;

@stat

--------------------------------------------------------------------------------------------------
| 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 |    11   (0)|  10000 |00:00:00.04 |      38 |
--------------------------------------------------------------------------------------------------

select /*+ gather_plan_statistics */
  max(c1)
from t1
where c3 = 1
;

@stat

--------------------------------------------------------------------------------------------------
| 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 |
--------------------------------------------------------------------------------------------------


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

select /*+ gather_plan_statistics */
  *
from t1
where c1 = (select /*+ no_unnest */ max(c1)
            from t1 x
            where x.c3 = t1.c3
            )
;

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|*  1 |  FILTER             |      |
|   2 |   TABLE ACCESS FULL | T1   |
|   3 |   SORT AGGREGATE    |      |
|*  4 |    TABLE ACCESS FULL| T1   |
------------------------------------

-- 10,000개의 Row에 매번 Filter 작업을 수행하므로 최악의 경우 Subquery를 수행하는데 필요한 일량(Logical Reads)은 38*10,000 = 380,000 Block이 된다.

@stat
---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |      |      1 |        |            |     10 |00:00:00.02 |     419 |
|   2 |   TABLE ACCESS FULL | T1   |      1 |  10000 |    11   (0)|  10000 |00:00:00.04 |      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.04 |     380 |
---------------------------------------------------------------------------------------------------




  • 하지만 실제 일량은 380 Block에 불과하다.380이 의미하는 것은 실제로는 Subquery가 10번만 수행되었다는 의미이다.
  • 조건을 x.c3 = t1.c3 에서 x.c2 = t1.c2로 바꾸면 일량은 더욱 줄어든다.



select /*+ gather_plan_statistics */
  *
from t1
where c1 = (select /*+ no_unnest */ max(c1)
            from t1 x
            where x.c2 = t1.c2
            )
;

@stat

---------------------------------------------------------------------------------------------------
| 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.05 |      38 |
|   3 |   SORT AGGREGATE    |      |      1 |      1 |            |      1 |00:00:00.01 |      38 |
|*  4 |    TABLE ACCESS FULL| T1   |      1 |  10000 |    11   (0)|  10000 |00:00:00.03 |      38 |
---------------------------------------------------------------------------------------------------



  • 일량이 38 Block에 불과하다. Subquery가 불과 한 번만 수행된 결과와 일치한다.
  • 이런 개선이 이루어지는 이유는 Filter Operation에 의해 수행되는 조건의 Input 값(여기서는 Subquery)을 Cache하며,
  • 동일한 Input이 사용되는 경우에는 추가적인 읽기 작업 없이 Cache된 값을 사용한다.(Buffer Pinning 과는 다른 mechanism)
  • 이것을 흔히 Filter Optimization이라고 부른다.


추가 테스트 1


_query_execution_cache_max_size hidden parameter로 cache size 조절가능(defualt 65536)
--------------------------------------------------------------------------------
NOTE: This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.

Oracle 11.1.0:
Parameter Name: _query_execution_cache_max_size
Description: max size of query execution cache
Type: NUMBER  Obsoleted: FALSE
Can ALTER SESSION: TRUE  Can ALTER SYSTEM: DEFERRED

Oracle 10.2.0:
Parameter Name: _query_execution_cache_max_size
Description: max size of query execution cache
Type: NUMBER  Obsoleted: FALSE
Can ALTER SESSION: TRUE  Can ALTER SYSTEM: DEFERRED

Oracle 10.1.0:
Parameter Name: _query_execution_cache_max_size
Description: max size of query execution cache
Type: NUMBER  Obsoleted: FALSE
Can ALTER SESSION: TRUE  Can ALTER SYSTEM: DEFERRED

Oracle 9.2.0:
No such parmeter in Oracle 9.2.0.
Oracle 8.1.7:
No such parmeter in Oracle 8.1.7.
Oracle 8.0.6:
No such parmeter in Oracle 8.0.6.
Oracle 7.3.4:
No such parmeter in Oracle 7.3.4.
----------------------------------------------------------------------------------



drop table t4 purge;

create table t4(c1 int, c2 char(10), c3 int);

create index t4_n1 on t1(c1);

insert into t4
select level, 'dummy', mod(level, 15) + 1 from dual
connect by level <= 10000
;

commit;

@gather t4

select /*+ gather_plan_statistics */
  *
from t4
where c1 = (select /*+ no_unnest */ max(c1)
            from t4 x
            where x.c3 = t4.c3
            )
;

@stat

---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |      |      1 |        |            |     15 |00:00:00.02 |     609 |
|   2 |   TABLE ACCESS FULL | T4   |      1 |  10000 |    11   (0)|  10000 |00:00:00.05 |      39 |
|   3 |   SORT AGGREGATE    |      |     15 |      1 |            |     15 |00:00:00.02 |     570 |
|*  4 |    TABLE ACCESS FULL| T4   |     15 |    667 |    11   (0)|  10000 |00:00:00.05 |     570 |
---------------------------------------------------------------------------------------------------
==> 15 * 38 = 570

-- 16
---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |      |      1 |        |            |     16 |00:00:00.69 |   24359 |
|   2 |   TABLE ACCESS FULL | T4   |      1 |  10000 |    11   (0)|  10000 |00:00:00.04 |      39 |
|   3 |   SORT AGGREGATE    |      |    640 |      1 |            |    640 |00:00:00.67 |   24320 |
|*  4 |    TABLE ACCESS FULL| T4   |    640 |    625 |    11   (0)|    400K|00:00:02.82 |   24320 |
---------------------------------------------------------------------------------------------------

==> 현재 시스템에서의 캐쉬 크기에서는 15개를 가질 수 있음.

-- 캐쉬값 기본값 2배로 조절
alter session set "_query_execution_cache_max_size"=131072

-- 30
---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |      |      1 |        |            |     30 |00:00:00.04 |    1180 |
|   2 |   TABLE ACCESS FULL | T4   |      1 |  10000 |    11   (0)|  10000 |00:00:00.04 |      40 |
|   3 |   SORT AGGREGATE    |      |     30 |      1 |            |     30 |00:00:00.03 |    1140 |
|*  4 |    TABLE ACCESS FULL| T4   |     30 |    333 |    11   (0)|  10000 |00:00:00.07 |    1140 |
---------------------------------------------------------------------------------------------------
==> 30 * 38 = 1140

-- 70
---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |      |      1 |        |            |     70 |00:00:00.08 |    2702 |
|   2 |   TABLE ACCESS FULL | T4   |      1 |  10000 |    11   (0)|  10000 |00:00:00.04 |      42 |
|   3 |   SORT AGGREGATE    |      |     70 |      1 |            |     70 |00:00:00.07 |    2660 |
|*  4 |    TABLE ACCESS FULL| T4   |     70 |    143 |    11   (0)|  10000 |00:00:00.12 |    2660 |
---------------------------------------------------------------------------------------------------
==> 70 * 38 = 2660

-- 100
---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |      |      1 |        |            |    100 |00:00:00.67 |   26415 |
|   2 |   TABLE ACCESS FULL | T4   |      1 |  10000 |    11   (0)|  10000 |00:00:00.04 |      43 |
|   3 |   SORT AGGREGATE    |      |    694 |      1 |            |    694 |00:00:00.66 |   26372 |
|*  4 |    TABLE ACCESS FULL| T4   |    694 |    100 |    11   (0)|  69400 |00:00:00.78 |   26372 |
---------------------------------------------------------------------------------------------------

-- 캐쉬값 기본값의 10배로 조절
alter session set "_query_execution_cache_max_size"=655350

-- 100
---------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|*  1 |  FILTER             |      |      1 |        |            |    100 |00:00:00.10 |    3845 |
|   2 |   TABLE ACCESS FULL | T4   |      1 |  10000 |    11   (0)|  10000 |00:00:00.04 |      45 |
|   3 |   SORT AGGREGATE    |      |    100 |      1 |            |    100 |00:00:00.10 |    3800 |
|*  4 |    TABLE ACCESS FULL| T4   |    100 |    100 |    11   (0)|  10000 |00:00:00.12 |    3800 |
---------------------------------------------------------------------------------------------------
==> 100 * 38 = 3800


Deterministic Function과 Cache효과

  • Deterministic Function을 일반 Query에서 사용하면 Cache의 효과를 얻을 수 있다.
  • Filter Optimization이 발생하는 것과 비슷한 효과이다.
  • Oracle 10g R2에서부터 구현되었다.



drop table t1 purge;

create table t1(c1 int, c2 int);

-- Column c1 : 1 ~ 10 값

insert into t1
select mod(level, 10) + 1, level
from dual
connect by level <= 1000
;

commit;

-- Package를 이용해 Function의 실제 호출 회수를 기록할 변수 선언
create or replace package p1 as
  g_idx int := 0;
end;
/

-- Deterministic Function f1을 선언. Function이 호출될 때마다 Package 변수의 값을 증가시킴으로써 실제 호출 회수 파악
create or replace function f1(v1 int)
return number
deterministic
is
begin
  p1.g_idx := p1.g_idx + 1;
  return 1;
end;
/


  • 총 100건의 Row를 Fetch. 각 Fetch 마다 Column c1 값을 인자로 Function f1을 호출.
  • Column c1은 1~10까지 10개의 Distinct 값을 가진다. 따라서 Deteministic Function에 대한 Cache 기능이 동작ㅎ사는 경우에는 실제 Function 호출은 10회에 불과해야 한다.



set serveroutput on
exec p1.g_idx := 0;

select f1(c1) from t1
where rownum <= 100
;

exec dbms_output.put_line('count = 100, call = ' || p1.g_idx);
--------------------------------------------------------------
count = 100, call = 70

SQL> show arraysize
arraysize 15
Fetch Call 회수 : 100/15 = 7
Fetch Call 마다 Cache가 이루어지므로 각 10번의 Function 호출 : 7 * 10 = 70


  • Deterministic Function의 결과에 대한 Cache가 Query 단위가 아닌 Fetch Call 단위로 이루어진다.


  • PL/SQL Block 의 Fetch Call은 "1회"에 불과하므로 완벽한 Cache 효과가 발생한다.

declare
  v_count number;
begin
  for idx in 1 .. 500 loop
    p1.g_idx := 0;

    for r in (select (select f1(c1) from dual) from t1
      where rownum <= idx) loop
        null;
    end loop;

    dbms_output.put_line(idx || ', count = 100, call = ' || p1.g_idx);
  end loop;
end;
/
count = 100, call = 1
count = 100, call = 2
count = 100, call = 3
count = 100, call = 4
count = 100, call = 5
count = 100, call = 6
count = 100, call = 7
count = 100, call = 8
count = 100, call = 9
count = 100, call = 10
count = 100, call = 10
...
count = 100, call = 10
count = 100, call = 10


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

set arraysize 1000
exec p1.g_idx := 0;

select f1(c1) from t1
where rownum <= 100
;


exec dbms_output.put_line('count = 100, call = ' || p1.g_idx);
--------------------------------------------------------------
count = 100, call = 11


  • Fetch Array Size를 1,000으로 키워준 결과 Function Call 회수가 11회로 완벽한 Cache가 이루어진 것을 알 수 있다.
  • (추가적인 1회는 SQL*Plus의 내부 Mechanism으로 인한 것이다.)



set arraysize 1
-- Array를 사용하지 않겠다.
exec p1.g_idx := 0;

select f1(c1) from t1
where rownum <= 100
;


exec dbms_output.put_line('count = 100, call = ' || p1.g_idx);
--------------------------------------------------------------
count = 100, call = 100

-- Function Call 회수가 Fetch 회수와 동일하다.


  • Deterministic Function을 사용할 수 없는 환경이나 Oracle 10gR2 이전 버전에서 Function Call의 Cache효과를 얻는 방법은 Subquery를 사용하는 것이다.

set arraysize 1
exec p1.g_idx := 0;

select (select f1(c1) from dual) from t1
where rownum <= 100
;


exec dbms_output.put_line('count = 100, call = ' || p1.g_idx);
--------------------------------------------------------------
count = 100, call = 10
-- Subquery 를 사용하는 경우 Fetch Array Size와 Deteministic Function 사용여부에 무관하게 Function Call 횟수를 줄일 수 있다.


추가 테스트 2


-- 50가지의 값을 가진 테이블로 테스트

drop table t4 purge;

create table t4(c1 int, c2 int);

insert into t4
select mod(level, 50) + 1, level
from dual
connect by level <= 1000
;

commit;


-- 함수 실행
declare
  v_count number;
begin
  for idx in 1 .. 500 loop
    p1.g_idx := 0;

    for r in (select f1(c1) from t4
      where rownum <= idx) loop
        null;
    end loop;

    dbms_output.put_line(idx || ', count = 100, call = ' || p1.g_idx);
  end loop;
end;
/

...
49, count = 100, call = 49
50, count = 100, call = 50
51, count = 100, call = 50
...
64, count = 100, call = 50
65, count = 100, call = 51
...
100, count = 100, call = 51
101, count = 100, call = 52
102, count = 100, call = 53
103, count = 100, call = 54
...
149, count = 100, call = 100
150, count = 100, call = 101
...
164, count = 100, call = 101
165, count = 100, call = 102
...
200, count = 100, call = 102
201, count = 100, call = 103
202, count = 100, call = 104
...
249, count = 100, call = 151
250, count = 100, call = 152
...
498, count = 100, call = 255
499, count = 100, call = 255
500, count = 100, call = 255

==> 규칙은 알수 없지만 50개를 모두 캐쉬 하지 못하고 엑세스

-- 스칼라 서브쿼리 실행
declare
  v_count number;
begin
  for idx in 1 .. 500 loop
    p1.g_idx := 0;

    for r in (select (select f1(c1) from dual) from t4
      where rownum <= idx) loop
        null;
    end loop;

    dbms_output.put_line(idx || ', count = 100, call = ' || p1.g_idx);
  end loop;
end;
/

...
49, count = 100, call = 49
50, count = 100, call = 50
...
64, count = 100, call = 50
65, count = 100, call = 51
...
114, count = 100, call = 51
115, count = 100, call = 52
...
214, count = 100, call = 52
215, count = 100, call = 53
...
314, count = 100, call = 53
315, count = 100, call = 54
...
414, count = 100, call = 54
415, count = 100, call = 55
...
500, count = 100, call = 55

==>캐쉬 메카니즘은 알수 없지만 스칼라 서브 쿼리의 캐쉬 이용 효율이 더 좋다.

-- 캐쉬값 기본값 2배로 조절 후 테스트
alter session set "_query_execution_cache_max_size"=131072

-- 함수의 경우
-- PL/SQL 실행
declare
  v_count number;
begin
  for idx in 1 .. 500 loop
    p1.g_idx := 0;

    for r in (select f1(c1) from t4
      where rownum <= idx) loop
        null;
    end loop;

    dbms_output.put_line(idx || ', count = 100, call = ' || p1.g_idx);
  end loop;
end;
/

...
500, count = 100, call = 250

-- 스칼라 서브쿼리 실행
declare
  v_count number;
begin
  for idx in 1 .. 500 loop
    p1.g_idx := 0;

    for r in (select (select f1(c1) from dual) from t4
      where rownum <= idx) loop
        null;
    end loop;

    dbms_output.put_line(idx || ', count = 100, call = ' || p1.g_idx);
  end loop;
end;
/

...
500, count = 100, call = 50

==> deterministic 함수나 스칼라 서브 쿼리의 캐쉬도 Filter Operation과 동일 캐쉬 영역 사용.

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

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

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

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

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