Optimizing Oracle Optimizer (2009년)
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
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.