Filter Operation의 Cache 효과

이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=1343561&
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

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년 3월 21일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
이 문서의 내용은 조동욱님의 'Optimizing Oracle Optimizer'을 참고하였습니다.

문서정보

이 문서는 구루비에서 작성하였습니다.
이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=1343561&
구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

Filter Operation의 Cache 효과