Science of Database

튜닝 관련 오라클 문서(White Paper)를 소개한다.
아래에 링크된 모든문서는 Oracle 10g 관련 문서이다.

이중에서 가장 추천할만한 문서는 Query Optimization in Oracle Database10g Release 2 이다.
특히 이문서에는 Query Transformation 에 대하여 비교적 자세히 나와있다.
Query Transformation 은 크기 2가지로 나눌수 있다

1) Cost Base transformations(비용기반) :
-Materialized view rewrite
-OR-expansion --> inlist 로 인해서 활용범위가 줄어들긴 하였다.
-Star transformation --> DW나 DSS 에서만 사용하는것이 아니다.
테스트 결과 대용량 배치에서 CODE 테이블 부터 엑세스 하여 GROUP BY , COUNT, SUM 하는경우 Star transformation을 사용하면 HASH 조인에 비해서 2배이상의 성능 차이가 난다.
Star 조인에 배해서도 1.5 배나 빨랐다.
-Predicate pushdown for outer-joined views.

2) Heuristic query transformations(전통적인 방식) :
-View Merge
-SubQuery Unnesting
-Transitive predicate generation
-Common subexpression elimination
-Predicate pushdown and pullup
-Group pruning for “CUBE” queries
-Outer-join to inner join conversion
위의 두가지 주제에 대하여 관심이 있으면 해당 백서를 꼭읽어보기 바란다.

하위버젼에서 Oracle 10g 로 업그레이드 하는경우 성능 이슈가 존재할수 있는데
이를위한 문서가 2가지 이다.
1.Migrating to the Cost-Based Optimizer
2.Upgrading from Oracle Database 9i to 10g-What to Expect from the Optimizer

DW 혹은 대용량 배치 SQL 관련 튜닝은 아래의 문서를 참조하면된다.
1.DSS Performance in Oracle Database 10g Release 1
2.DSS Performance in Oracle Database 10g Release 2

Sort 나 Group by 관련 문서는 아래문서를 참조하기 바란다.
1.Sort Performance Improvements in Oracle Database 10 Release 2
2.SQL Aggregation for Business Intelligence in Oracle Database 10g

파티션 관련 문서도 2가지이다.
1.Oracle Partitioning 10gR2
2.Partitioning in Oracle Database 10g

위문서 이외에도 SQLAccess Advisor나 CDC, MVIEW, HP Machine 관련한 흥미 진진한 문서들이 아래에 링크되어 있다.

백서의 매력은 네가지로 요약할수 있다.
1.주제가 한가지로 한정되어 있으므로 한가지 주제에 대하여 깊게 파고들수 있다.
2.매뉴얼에 없는 비급(?) 이 가끔 소개된다.
3.장수가 적다(보통 20~40 페이지로 끝난다)
4.백서는 오류가 거의 없다는점(가끔 오라클 매뉴얼도 오류가 있음)
시간이 나는대로 백서를 참조하도록 하자.

DSS Performance in Oracle Database 10g Release 1 :
http://www.oracle.com/wocportal/pls/wocprod/docs/1/379013.PDF

DSS Performance in Oracle Database 10g Release 2 :
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1306255.PDF

Key Data WarehousingFeatures in Oracle10g: A Comparative Performance Analysis :
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1106757.PDF

Migrating to the Cost-Based Optimizer :
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1101645.PDF

Oracle Partitioning 10gR2:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1185109.PDF

Partitioning in Oracle Database 10g :
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1065154.PDF

Performance Tuning Using SQLAccess Advisor:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/450780.PDF

Query Optimization in Oracle Database10g Release 2:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1194820.PDF

Sort Performance Improvements in Oracle Database 10 Release 2 :
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1214088.PDF

SQL Aggregation for Business Intelligence in Oracle Database 10g :
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1230066.PDF

Table Compression in Oracle Database 10g Release 2:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1310336.PDF

Upgrading from Oracle Database 9i to 10g-What to Expect from the Optimizer :
http://www.oracle.com/wocportal/pls/wocprod/docs/1/14421415.PDF

Change Data Capture Cookbook :
 http://www.oracle.com/wocportal/pls/wocprod/docs/1/4983723.PDF

Asynchronous Autolog CDC Cookbook :
http://www.oracle.com/wocportal/pls/wocprod/docs/1/4995974.PDF

On-Time Data Warehousing with Oracle Database 10g:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/469428.PDF

Oracle Database 10g Materialized Views :
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1101619.PDF

Oracle Materialized Views & Query Rewrite:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1174608.PDF

HP Integrity Superdome Cluster with Oracle Database 10:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1015875.PDF

HP ProLiant Cluster with Oracle Database 10g and Oracle:
http://www.oracle.com/wocportal/pls/wocprod/docs/1/1015835.PDF

현재 많은 종류의 Oracle 튜닝 책에 Update, Delete 시의 parallel operation 관련하여
Partition 이 되어 있지 않으면 single mode 로 처리된다고 되어 있다.
하지만 이것이 맞는말인가?
하나씩 테스트를 해보자
테스트 환경은 Oracle 10g R2(10.2.0.3) 버젼이다.

테스트 시나리오
--고객테이블(100 만건) 의 고객영문명에 serial update 와 parallel update 를 한번씩 한다.
--고객테이블은 파티션이 되지않은 테이블이다.

1.update test

/**************serial update 시작******************/
alter session disable parallel dml; -- parallel 을 disable 한다.

update tb_cus set cus_enm = '1'; -- 100만건 update(17초)

commit;

아래는 trace 결과 이다.
trace 결과 가 깨지는 점을 이해하기 바란다.

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 16.410 16.999 845 27205 1032475 1000000
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 16.410 17.001 845 27205 1032475 1000000

Elapsed Time for Client(sec.): 17.000
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user: SI31041 (ID=387)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
0 UPDATE TB_CUS (cr=27205 pr=845 pw=0 time=16998894 us)
1000000 TABLE ACCESS FULL TB_CUS (cr=27133 pr=845 pw=0 time=1000149 us)

/**************parallel update 시작******************/

alter session enable parallel dml; -- parallel 을 enable 한다.

update /*+ parallel(tb_cus 8) */ tb_cus set cus_enm = '1'; -- 100만건 update(8.7초)

commit;


Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.170 8.700 0 6 1 1000000
Fetch 0 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 2 0.170 8.701 0 6 1 1000000

Elapsed Time for Client(sec.): 8.701
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user: SI31041 (ID=387)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
8 PX COORDINATOR (cr=6 pr=0 pw=0 time=8791448 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
0 UPDATE TB_CUS (cr=0 pr=0 pw=0 time=0 us)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL TB_CUS (cr=0 pr=0 pw=0 time=0 us)


2.delete test

update 테스트 결과 와 같이 parallel 옵션 사용시 전혀문제 없었음.
delete 테스트 결과는 생략함.


-- 테스트시 재미있는점은 PARALLEL 적용시에 TRACE 결과의 ROWS 에 DOP 수가 나온다는 점이다.
--일종의 버그인것 같다.

3.결론
파티션 되지않은 테이블을 update, delete 할때 parallel 옵션의 적용은 문제가
전혀 없는것으로 드러남.
V$PX_PROCESS 나 GV$PX_SESSION 등의 뷰에서도 정상적으로 Parallel Process 가 관찰되었다.
Parallel 관련 wait event 도 발생됨 .
따라서 최소한 10g 의 parallel 관련서적들은 모두 위의 테스트 결과대로
파티션되지 않은 테이블에 parallel update, delete는 적용되는걸로 수정하여야 한다.
하지만 테스트를 안해보고 서적을 집필한 저자나 출판사의 잘못만은 아니다.
왜냐하면 오라클 10g R2 Data Warehousing Guide 의 25-58에는 분명히 아래와 같이 적용불가능 하다고 나와 있다.

Parallel updates and deletes work only on partitioned tables.
If you are performing parallel UPDATE, MERGE, or DELETE operations, the DOP isequal to or less than the number of partitions in the table.

오라클 매뉴얼도 참조서적에 불과하다.
언제나 의심해보고 테스트를 해보아야 하는것을 잊지말자.

편집후기 :
Parallel DML은 내부적으로 쿼리변환(각각의 slave 쿼리가 Granule 단위로 쪼개짐)에 관계된다. 그런데 조나단루이스저서(cost base~) 의 9장을 참조해보면 쿼리변환과 관계해서 기능의 생명주기를 beta --> 처음으로 공식화 하는상태 -->최종상태 로 나타내고있다.
그런데 파티션 되지 않은 테이블의 parallel update, delete는 아직도 beta 상태인것 같다.
다시말하면 기능은 구현되어 있지만 여러가지문제들로 인하여 공식화 하지 않은상태라는 것이 필자의 생각이다.
참고로 11g 의 매뉴얼에도 10g 와 마찬가지로 공식적으로는 적용불가능이라고 되어 있다.
엑셈의 조동욱씨에 따르면 한가지 주의 할점은 Intra-partition parallelism이 항상 동작하는 것은 아니라는 것이다. 일부 제약이 있고, 또 제약이 없더라도 간혹 동작하지 않는 경우도 있는 것 같다고 한다.
이글을 쓰는데 도움을 주신 조동욱 수석과 비투엔의 김정삼 책임 오픈메이드 컨설팅의 김중국책임에게 감사드린다.

참조 URL :
1.http://youngcow.net/doc/oracle10g/server.102/b14223/usingpe.htm#CACEJACE
2.메타링크 문서제목 :What is Intra-partition parallelism, 문서 id : 241376.1

10g 부터 group by 시에 느린성능의 Sort Group by 가 사라지고 빠른성능의 Hash Group By 가 등장 했다.
하지만 현재시점(10.2.0.3)에서 항상 Hash Group By 가 동작되는 것은 아니다.
성능면에서 배치 SQL 혹은 Migration 작업시에 몇억건의 데이터를 sort 하게되면 견딜수 없다.
주로 Sort Group by 는 insert - select - group by 상황에서 발생한다.
opt_param 힌트로도 해결되지 않았으며 Only select 문또는
CTAS (Create table as Select)문에서는 발생하지 않는걸로 확인됬다.
아래는 간단한 테스트를 진행 하고 현상황에서 Sort Group by 를 피할수 있는 해법을 제공한다.


1.테스트 환경
버젼 : 10gR2(10.2.0.3) ,
Optimizer mode :all_rows
관련 파라미터 : _gby_hash_aggregation_enabled = true
계정 : scott/tiger


2.Select Test

analyze table dept compute statistics;
analyze table emp compute statistics;

select /*+ use_hash(b a) */ empno, ename, job, mgr, hiredate, sal, comm, a.deptno
from
(select max(EMPNO) as empno, max(ENAME) as ename, max(JOB) as job, max(MGR) as mgr,
max(HIREDATE) as hiredate, max(SAL) as sal, max(COMM) as comm, deptno
from emp
group by DEPTNO) a,
dept b
where a.deptno = b.deptno;

Execution Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=4 Bytes=356)
1 0 HASH JOIN (Cost=6 Card=4 Bytes=356)
2 1 VIEW (Cost=4 Card=4 Bytes=348)
3 2 HASH (GROUP BY) (Cost=4 Card=4 Bytes=128) --> 정상적인 hash group by
4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=448)
5 1 INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=8)

3.CTAS Test

-- CTAS 시에 inline view 가 merge 되므로 상황을 재현하기위해 no_merge 힌트 사용

create table new_emp as
select /*+ use_hash(b a) */ empno, ename, job, mgr, hiredate, sal, comm, a.deptno
from
(select /*+ no_merge */
max(EMPNO) as empno, max(ENAME) as ename, max(JOB) as job, max(MGR) as mgr,
max(HIREDATE) as hiredate, max(SAL) as sal, max(COMM) as comm, deptno
from emp
group by DEPTNO) a,
dept b
where a.deptno = b.deptno;

Execution Plan
--------------------------------------------------------------------------------
0 CREATE TABLE STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=4 Bytes=356)
1 0 LOAD AS SELECT OF 'NEW_EMP'
2 1 HASH JOIN (Cost=7 Card=4 Bytes=356)
3 2 VIEW (Cost=5 Card=4 Bytes=348)
4 3 HASH (GROUP BY) (Cost=4 Card=4 Bytes=128) --> 정상적인 hash group by
5 4 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=448)
6 2 INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=8)


4.Insert-Select-group by Test

--2번/3번 테스트 에서 나타나지 않았던 Sort Group By 가 Insert 시에만 나타난다.

insert into emp
select /*+ use_hash(b a) */ empno, ename, job, mgr, hiredate, sal, comm, a.deptno
from
(select max(EMPNO) as empno, max(ENAME) as ename, max(JOB) as job, max(MGR) as mgr,
max(HIREDATE) as hiredate, max(SAL) as sal, max(COMM) as comm, deptno
from emp
group by DEPTNO) a,
dept b
where a.deptno = b.deptno;

Execution Plan
--------------------------------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=4 Bytes=356)
1 0 HASH JOIN (Cost=6 Card=4 Bytes=356)
2 1 VIEW (Cost=4 Card=4 Bytes=348)
3 2 SORT (GROUP BY) (Cost=4 Card=4 Bytes=128) --> 비정상적인 Sort group by
4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=448)
5 1 INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=8)

5.해법

1) merge 힌트사용: 두개의 테이블이 merge 되는것이 유리한 경우

insert into emp
select /*+ merge(a) use_hash(b a) */ empno, ename, job, mgr, hiredate, sal, comm, a.deptno
from
(select max(EMPNO) as empno, max(ENAME) as ename, max(JOB) as job, max(MGR) as mgr, max(HIREDATE) as hiredate, max(SAL) as sal, max(COMM) as comm, deptno
from emp
group by DEPTNO) a,
dept b
where a.deptno = b.deptno;

Execution Plan
--------------------------------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=4 Bytes=184)
1 0 HASH (GROUP BY) (Cost=6 Card=4 Bytes=184) --> 정상적인 hash group by
2 1 HASH JOIN (Cost=5 Card=14 Bytes=644)
3 2 INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=56)
4 2 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=448)

2)Migration 인 경우는 insert-select 대신에 CTAS 사용

3)With 구문과 Merterialize 힌트사용

insert into emp
with max_emp as
(select /*+ materialize */
max(EMPNO) as empno, max(ENAME) as ename, max(JOB) as job,
max(MGR) as mgr, max(HIREDATE) as hiredate, max(SAL) as sal,
max(COMM) as comm, DEPTNO
from emp
group by DEPTNO),
max_dept_emp as
( select /*+ use_hash(b a) */
empno, ename, job, mgr, hiredate, sal, comm, a.deptno
from max_emp a,
dept b
where a.deptno = b.deptno )
select * from max_dept_emp;

Execution Plan
--------------------------------------------------------------------------------
0 INSERT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=4 Bytes=356)
1 0 TEMP TABLE TRANSFORMATION
2 1 LOAD AS SELECT OF 'EMP'
3 2 HASH (GROUP BY) (Cost=4 Card=4 Bytes=128) --> 정상적인 hash group by
4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=448)
5 1 HASH JOIN (Cost=4 Card=4 Bytes=356)
6 5 VIEW (Cost=2 Card=4 Bytes=348)
7 6 TABLE ACCESS (FULL) OF 'SYS.SYS_TEMP_0FD9D6616_4EBAE980' (TABLE (TEMP)) (Cost=2 Card=4 Bytes=128)
8 5 INDEX (FULL SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=1 Card=4 Bytes=8)

6.결론
적절한 방법을 사용하여 대용량 Group By 시에 Sort 를 피할수 있는방법을 제시하였으나
더뛰어난 solution 이 있을수 있으므로 여러가지 테스트 후에 적용하기 바란다.

개념 :
SQL 이 좋은 언이이긴 하지만 집합처리 언어이기 때문에 불편한점이 많은 것이 사실이다.
특히 아래와 같은 구조에서 세로를 가로로 바꿀려고 하면 힘들다.

SELECT DEPTNO, ENAME
FROM EMP;

DEPTNO ENAME
------ ----------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 ADAMS
20 FORD
20 SCOTT
20 JONES
30 ALLEN
30 BLAKE
30 MARTIN
30 JAMES
30 TURNER
30 WARD

위의 데이터를 아래처럼 부서번호별로 가로로 바꾸어야 하는것이 미션이다.

DEPTNO ENAME
------- ------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

오라클의 경우 조금 복잡한 Recursive SQL 을 작성해야 한다.

select deptno,
ltrim(sys_connect_by_path(ename,','),',') ename
from (
select deptno,
ename,
row_number() over (partition by deptno order by empno) rn,
count(*) over (partition by deptno) cnt
from emp
)
where level = cnt
start with rn = 1
connect by prior deptno = deptno and prior rn = rn-1 ;

그럼 mySQL은 어떨까?
놀랍게도 mySQL 은 group_concat 한방으로 해결하고 있다.

select deptno,
group_concat(ename order by empno separator, ',') as ename
from emp
group by deptno;

사실 필자는 오라클 9i 이전버젼부터 mySQL 의 정규식을 부러워 했었다.
mySQL 은 정말 그렇고 그런? DBMS 일까? 다시생각해야 될필요가 있다.

그럼 DB2 나 SQL SERVER 는 Recursive SQL 이 사용가능할까?

현재 많은 사람들이 Recursive 쿼리가 안된다고 판단하고 Self Join 을 사용하고 있다.
그러나 DB2나 SQL 서버의 최신버젼은 아래와 같이 Recursive With 를 사용하면 된다.
with x (deptno, cnt, list, empno, len)
as (
select deptno, count(*) over (partition by deptno),
cast(ename as varchar(100)), empno, 1
from emp
union all
select x.deptno, x.cnt, x.list ',' e.ename, e.empno, x.len+1
from emp e, x --> recursive call
where e.deptno = x.deptno
and e.empno > x. empno
)
select deptno,list
from x
where len = cnt ;

참고로 오라클은 Recursive With 절 이나 group_concat 은 11g 버젼에서도 지원하지 않고 있다.
오라클에서는 Recursive function 은 예전부터 지원되어 왔다.

참조서적 : SQL Cookbook By Anthony Molinaro(O'Reilly)

개념:
Oracle 10g R2 부터 재미있는 힌트가 생겼다.
OPT_PARAM 이라는 힌트인데 세션단위가 아니라 SQL 단위로 파라미터를 바꿀수 있는 힌트이다.

주의사항:
1)Oracle 10g R2 에서는 비공식 힌트이며 11g에서는 공식지원되는 힌트이다.
2)11g 에서 이 힌트에 공식지원되는 파리미터는 아래와 같다.
OPTIMIZER_DYNAMIC_SAMPLING
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_SECURE_VIEW_MERGING
STAR_TRANSFORMATION_ENABLED
3)10g 에서는 어떤파라미터가 적용되는지 알려지지 않았으며
그렇기 때문에 이힌트를 사용하려면 철저한 테스트를 수행후에 적용해야 한다.

사용법:
SELECT /*+ OPT_PARAM('파라미터' 'VALUE') */ * FROM ... ;
--주의 : VALUE 가 숫자인경우는 ' 를 붙이지 않는다.

예제:
아래예제는 object_type 로 SORT 하기위하여 HASH GROUP BY 를
SORT GROUP BY PLAN 으로 바꾸고 있다.

select object_type,count(*)
from test group by object_type;

--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 34 204 34915 (3) 00:06:59
1 HASH GROUP BY 34 204 34915 (3) 00:06:59
2 TABLE ACCESS FULL TEST 9999K 57M 34147 (1) 00:06:50

select /*+ opt_param(’_gby_hash_aggregation_enabled’,'false’) */ object_type,count(*)
from test group by object_type;

--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 34 204 34915 (3) 00:06:59
1 SORT GROUP BY 34 204 34915 (3) 00:06:59
2 TABLE ACCESS FULL TEST 9999K 57M 34147 (1) 00:06:50

참조문서:
Metalink Note 377333.1

개념 :
-Oracle 10g 의 새기능인 DBMS_ADVANCED_REWRITE는 특정 SQL 을 다른 SQL 로 대체하는
기능이다.
-SQL 을 바꾸지 않고 튜닝해야하는 경우에 적합하다.
-Oracle 9i 에 Stored OutLine 이라는 기능이 있었지만 위와 같은경우에는 쓰기가 매우 불편하였다.

사용방법 :
1)user 에게 권한을 준다
CONN sys/password AS SYSDBA
GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO test;
GRANT CREATE MATERIALIZED VIEW TO test;

2) rewrite 될 SQL
SELECT * FROM test_tab;
ID DESCRIPTION
---------- --------------------------------------------------
1 GLASGOW
2 BIRMINGHAM
3 LONDON

3 rows selected.
SQL>

3) 뷰를 하나만들고 쿼리가 들어오면 뷰로 대신한다.
CREATE OR REPLACE VIEW rewrite_v AS
SELECT id, INITCAP(description) AS description
FROM test_tab
ORDER BY description;

BEGIN

END;/

4) 이제 설정이 끝났으므로 기능이 되는지 TEST 한다.
SELECT * FROM test_tab;
ID DESCRIPTION
---------- --------------------------------------------------
1 GLASGOW
2 BIRMINGHAM
3 LONDON

3 rows selected

기능이 실행되지 않았는데 이유는 아래 파라미터 때문이다.

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
Session altered.

SELECT * FROM test_tab;

ID DESCRIPTION
---------- --------------------------------------------------
2 Birmingham
1 Glasgow
3 London

3 rows selected.

성공적으로 수행되었다.

5)DBMS_ADVANCED_REWRITE 기능의 모든 설정을 보려면 아래뷰들을 참조하면 된다.
-DBA_REWRITE_EQUIVALENCES
-ALL_REWRITE_EQUIVALENCES
-USER_REWRITE_EQUIVALENCES

-SELECT * FROM user_rewrite_equivalences;

6) 이기능을 비활성화 하려면 아래 프로시져를 사용한다.
EXEC SYS.DBMS_ADVANCED_REWRITE.drop_rewrite_equivalence (name => 'test1_rewrite');

편집후기:
DBMS 과학자 조동욱님에 따르면 바인드 변수가 있으면 기능이 수행되지 않는다고 한다.

Outer Join 의 Lateral Views 로의 변환

목적 : 많은 사람들이 ANSI OUTER JOIN 에 대해 이해하지 못하고 심지어 튜너들이라고 하는사람들까지도 개념정립을 못하고 있다.

개념 :
- Oracle 10g 의 새기능인 Automatic Workload Repository(AWR) 의 튜닝 솔루션인
Server-Based Advisors의 콤포넌트 중의 하나이다.
- Server-Based Advisors 는 다음과 같이구성된다.
Automatic Database Diagnostic Monitor (ADDM)
SQL Tuning Advisor
SQL Access Advisor
Shared Pool Advisor
MTTR Advisor
- SQL Tuning Advisor 는 SQL 구문들을 분석하여 튜닝방법을 제안한다.
- 튜닝방법을 제안하는 것에서 그치지 않고 쉽게적용할수 있도록 도와주기도 한다.


사용방법:
-엔터프라이져 매니져를 통해 관리할수도 있지만 여기서는 DBMS_SQLTUNE 패키지를
사용하기로 한다.

1) 튜닝 Task 생성

Declare
ret_val varchar2(2000);
Begin
ret_val:=dbms_sqltune.create_tuning_task(sql_text=>
'SELECT *FROM EMP WHERE EMPNO=7934');

dbms_output.put_line('Output: 'ret_val);

end;/

OUTPUT: TASK_00004

2) 튜닝가이드 생성

Exec Dbms_sqltune.execute_tuning_task('TASK_00004');


3) 튜닝 리포트 생성
set serveroutput on size 999999
set long 999999
select dbms_sqltune.report_tuning_task ('TASK_00004') from dual;

4)결과분석

GENERAL INFORMATION SECTION
----------------------------------------------------
Tuning Task Name : TASK_00004
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 11/06/2003 01:47:38

 -------------------------------------------------------------------------------------------

위 결과를 보면 recommendation 항목에서 튜닝제안을 했으며 구체적인 스크립트까지
제시하고 있다.
Rationale 항목에서는 튜닝방법대로 했을경우 옵티마이져가 어떻게 반응하는지를 보여준다.
위의 예제에서는 통계정보를 생성하면 실행계획이 바뀐다는점을 알려주고 있다.
신기하지 않은가?