더이상 이블로그를 운영하지 않습니다.
아래의 주소로 이전 되었습니다.
http://scidb.tistory.com/
2008년 5월 24일 토요일
Lateral View 를 활용한 튜닝
Lateral View와 아우터조인의 개념에 대해서는 이미 언급이 되었고 이번에는 Lateral View 를 이용한 튜닝에 대하여 알아보기로 한다.
먼저 개발자들에게 받는 질문 상위 10개 중에 항상 들어있는 질문이 있다.
"선택적으로 조인하는 기능이 오라클에 있습니까?"
필자는 항상 다음과 같이 답변한다.
"있습니다."
아래 모델을 보자.
이모델을 보면 고객유형(subtype) 에 따라서 개인기본으로 조인할지 사업자기본으로 조인할지 결정이 되는것이다.
물론 연락처기본은 고객기본과 항상 1:1 이다.
이런 모델에서 대부분의 개발자는 아래와 같은 SQL 을 작성한다.
SELECT
A.고객번호, A.고객유형,
B.취미코드, B.종교코드,
C.사업규모코드, C.종업원수,
D.대표핸드폰번호
FROM 고객기본 A, 개인기본 B, 사업자기본 C, 연락처기본 D
WHERE A.고객번호 = B.고객번호 (+)
AND A.고객번호 = C.고객번호(+)
AND A.고객번호 = D.고객번호
AND A.고객번호 = :V_고객번호; --> 고객번호에 고객유형이 개인인 고객번호 대입함.
언뜻 보기에 위의 SQL 은 아무 문제가 없어보인다.
하지만 과연 그런가?
아래 Trace 결과를 보자
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT 1 NESTED LOOPS OUTER (cr=15 pr=0 pw=0 time=225 us)
1 NESTED LOOPS OUTER (cr=11 pr=0 pw=0 time=186 us)
1 NESTED LOOPS OUTER (cr=8 pr=0 pw=0 time=145 us)
1 TABLE ACCESS BY INDEX ROWID 고객기본 (cr=4 pr=0 pw=0 time=81 us)
1 INDEX UNIQUE SCAN PK_고객기본 (cr=3 pr=0 pw=0 time=38 us)
1 TABLE ACCESS BY INDEX ROWID 연락처기본 (cr=4 pr=0 pw=0 time=47 us)
1 INDEX UNIQUE SCAN PK_연락처기본 (cr=3 pr=0 pw=0 time=26 us)
0 TABLE ACCESS BY INDEX ROWID 사업자기본 (cr=3 pr=0 pw=0 time=33 us)
0 INDEX UNIQUE SCAN PK_사업자기본 (cr=3 pr=0 pw=0 time=29 us)
1 TABLE ACCESS BY INDEX ROWID 개인기본 (cr=4 pr=0 pw=0 time=37 us)
1 INDEX UNIQUE SCAN PK_개인기본 (cr=3 pr=0 pw=0 time=25 us)
개인고객임에도 불구하고 사업자기본 테이블 및 인덱스에 3블럭(cr =3)씩 read 한것을 볼수 있다.
위의 SQL 은 항상 고객번호 인덱스로 개인기본과 사업자 기본을 뒤진후에 연락처기본과 조인하는 구조이다.
다시말하면 개인고객인경우도 사업자기본 테이블을 access 하고 사업자고객인 경우도 개인기본 테이블을 access 한다는 뜻이다.
아래처럼 ANSI SQL 을 사용하여 SQL 을 수정하면 오라클은 Lateral View 로 변환하여 비효율적인 access 를 방지한다.
고객유형에 따라서 개인일경우 개인기본 테이블만 access하고 고객유형이 사업자일 경우는 사업자기본 테이블만 access 한다.
SELECT
A.고객번호, A.고객유형,
B.취미코드, B.종교코드,
C.사업규모코드, C.종업원수,
D.대표핸드폰번호
FROM 고객기본 A left outer join 개인기본 B
on (A.고객번호 = B.고객번호 and A.고객유형 = '1') --> 고객유형이 개인 일경우만 조인됨
left outer join 사업자기본 C
on (A.고객번호 = C.고객번호 and A.고객유형 = '2') --> 고객유형이 사업자 일경우만 조인됨
join 연락처기본 D
on (A.고객번호 = D.고객번호) --> 무조건 조인한다.
WHERE A.고객번호 = :V_고객번호; --> 고객번호에 고객유형이 개인인 고객번호 대입함
이것을 Trace 에서 차이를 비교해보면 read 한 블럭수가 차이난다.
즉 개인고객이면 사업자기본 테이블을 읽은 블럭수가 0 이고 사업자고객이면 개인기본 테이블을 읽은 블럭수가 0 이라는 뜻이다.
아래의 Trace 결과를 보자.
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT 1 NESTED LOOPS OUTER (cr=15 pr=0 pw=0 time=225 us)
1 NESTED LOOPS OUTER (cr=11 pr=0 pw=0 time=186 us)
1 NESTED LOOPS OUTER (cr=8 pr=0 pw=0 time=145 us)
1 TABLE ACCESS BY INDEX ROWID 고객기본 (cr=4 pr=0 pw=0 time=81 us)
1 INDEX UNIQUE SCAN PK_고객기본 (cr=3 pr=0 pw=0 time=38 us)
1 TABLE ACCESS BY INDEX ROWID 연락처기본 (cr=4 pr=0 pw=0 time=47 us)
1 INDEX UNIQUE SCAN PK_연락처기본 (cr=3 pr=0 pw=0 time=26 us)
0 TABLE ACCESS BY INDEX ROWID 사업자기본 (cr=0 pr=0 pw=0 time=33 us)
0 INDEX UNIQUE SCAN PK_사업자기본 (cr=0 pr=0 pw=0 time=29 us)
1 TABLE ACCESS BY INDEX ROWID 개인기본 (cr=4 pr=0 pw=0 time=37 us)
1 INDEX UNIQUE SCAN PK_개인기본 (cr=3 pr=0 pw=0 time=25 us)
자주 엑세스 하는 뷰를 만들때도 위와 같은 SQL 로 만들어야 할것이다.
ANSI SQL 을 사용할수 없는 구조라면 아래처럼 DECODE 함수를 활용하면 위와 같은 효과를 얻을수 있다.
SELECT
A.고객번호, A.고객유형,
B.취미코드, B.종교코드,
C.사업규모코드, C.종업원수,
D.대표핸드폰번호
FROM 고객기본 A, 개인기본 B, 사업자기본 C, 연락처기본 D
WHERE DECODE(A.고객유형, '1',A.고객번호) = B.고객번호 (+) --> 고객유형이 개인 일경우만 조인됨
AND DECODE(A.고객유형, '2',A.고객번호) = C.고객번호(+) --> 고객유형이 사업자 일경우만 조인됨
AND A.고객번호 = D.고객번호
AND A.고객번호 = :V_고객번호;
결론:
성능을 위해서는 Subtype 으로 인하여 선택적으로 조인해야 하는경우 ANSI Outer 조인으로 조인을 제한 하거나 decode 함수를 사용하여 선택적으로 조인을 해야한다.
2008년 5월 23일 금요일
가로를 세로로 바꾸기
세로를 가로로 만드는 방법에 대해서는 Recursive SQL 에서 충분히 논하였다.
그렇다면 가로를 세로로 바꾸는 방법중의 최적은 무엇인가?
그문제와 관련하여 필자는 얼마전 어떤 개발자가 유져로 부터 다음과 같은 질문을 받았는것을 보았다.
유져 : "DBMS 와 웹 Application 간에 배열 연동이 안되므로 변수를 배열로 던져주지 못하는 대신에 '7654,7698,7780,7788' 처럼 변수를 만들어서 Oracle 에 던지고 싶습니다.
그런데 이렇게 던지면 Oracle 에서 List 로 만들어서 처리할수 있습니까?"
개발자 : "예" --> 어느정도 내공이 있는 개발자임을 알수 있다.
유져 : "구분자(delimiter)가 항상 ',' 가 아닐수도 있고 1 byte 도 아닐수 있습니다.
예를들면 구분자가 ':;;:' 처럼 될수도 있고 그것은 프로그램 마다 다를수 있습니다.
그래도 가능 하겠습니까?"
개발자: (주저주저 하며 대답을 못함)
이런경우 일단 대답은 "예" 이다.
Oracle 10g 라면 아래처럼 간단한 SQL 로 IN-LIST 를 만들수 있다.
SELECT
ltrim(regexp_substr(:v_deli:v_str:v_deli, :v_deli'[^':v_deli']+', 1, level ), :v_deli) AS empno
FROM dual
connect by level<= ( length(:v_deli:v_str) - length(replace(:v_deli:v_str, :v_deli)) ) / length(:v_deli) ;
결과 :
그렇다면 가로를 세로로 바꾸는 방법중의 최적은 무엇인가?
그문제와 관련하여 필자는 얼마전 어떤 개발자가 유져로 부터 다음과 같은 질문을 받았는것을 보았다.
유져 : "DBMS 와 웹 Application 간에 배열 연동이 안되므로 변수를 배열로 던져주지 못하는 대신에 '7654,7698,7780,7788' 처럼 변수를 만들어서 Oracle 에 던지고 싶습니다.
그런데 이렇게 던지면 Oracle 에서 List 로 만들어서 처리할수 있습니까?"
개발자 : "예" --> 어느정도 내공이 있는 개발자임을 알수 있다.
유져 : "구분자(delimiter)가 항상 ',' 가 아닐수도 있고 1 byte 도 아닐수 있습니다.
예를들면 구분자가 ':;;:' 처럼 될수도 있고 그것은 프로그램 마다 다를수 있습니다.
그래도 가능 하겠습니까?"
개발자: (주저주저 하며 대답을 못함)
이런경우 일단 대답은 "예" 이다.
왜냐하면 프로그램마다 문자열과 구분자를 받을수 있기 때문이다.
SELECT :v_str , :v_deli FROM dual;
--여기서 :v_deli 는 구분자(',') 이며 :v_str 는 문자열 '7654,7698,7780,7788' 이다.
결과 :
Oracle 10g 라면 아래처럼 간단한 SQL 로 IN-LIST 를 만들수 있다.
SELECT
ltrim(regexp_substr(:v_deli:v_str:v_deli, :v_deli'[^':v_deli']+', 1, level ), :v_deli) AS empno
FROM dual
connect by level<= ( length(:v_deli:v_str) - length(replace(:v_deli:v_str, :v_deli)) ) / length(:v_deli) ;
결과 :
처리의 핵심은 첫번째로는 문자열 길이 에서 구분자(comma) 를 제거한 길이를 빼서 구분자의 길이로 나누면 row 갯수가 나온다는 점이다.
두번째로는 10g 부터 사용할수 있는 정규식을 사용하여 시작문자와 끝문자 를 정의해서 그사이에 있는 문자들을 가져올수 있다는 점이다.
예를들면 regexp_substr( 문자열, ',[^,]+') 이렇게 하면 문자열에서 ,(comma) 부터 다음 ,(comma) 까지의 문자들을 가지고 올수 있다.
물론 10g 미만의 버젼이라면 level 과 connect by 를 대신해서 copy_t 류의 테이블을 사용하고 substr 과 instr 함수를 적절히 조합하면 된다.
이해를 돕기위해 아래 SQL 을 실행해 보자.
아래 SQL 은 첫번째,두번째,세번째, 네번째 의 사번(EMPNO)을 위의방법을 이용하여 각각 가져오고 있다.
SELECT
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 1 ) empno1,
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 2 ) empno2,
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 3 ) empno3,
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 4 ) empno4
FROM dual;
주의사항은 오라클 정규식의 예약어인 파이프라인 이나 괄호 )( ][ {} 등을 구분자로 사용하지 말라는것이다.
파이프라인 이나 괄호 대신에 ':' 처럼 적당한 다른문자를 사용하면 됨.
파이프라인 이나 괄호 대신에 ':' 처럼 적당한 다른문자를 사용하면 됨.
2008년 5월 19일 월요일
NO Costing in CBO
CBO(Cost Based Optimizer) 라고 하면 DBMS 종류를 막론하고 비용이 가장 낮은 실행계획을 선택하는것이라 할수있다.
보통 CBO 에서 문제가 되는것은 (스키마통계정보 or 시스템통계)의 부재나 부족으로 인한 잘못된 Cost를 계산해서 잘못된 실행계획을 선택하는것 이라고 볼수있다.
하지만 항상 그런것은 아니다.
CBO 가 높은 Cost 를 선택해서 끔찍한 일을 저지를 수도 있다는 것이다.
어떤경우에 No Costing 이 발생하는지 아래를 참조하자.
1.환경 :
버젼 : 10gR2(10.2.0.3)
Optimizer mode :all_rows
계정 : scott/tiger
2.임시 부서 테이블 생성
CREATE TABLE TEMP_DEPT AS SELECT * FROM DEPT; --> DEPT 임시테이블 생성
3.통계정보 생성
EXEC dbms_stats.gather_table_stats(user,'TEMP_DEPT',cascade=>true);
EXEC dbms_stats.gather_table_stats(user,'EMP',cascade=>true); --> EMP 는 그대로 사용.
4. Hash join Cost
SQL> explain plan for
2 select /*+ USE_HASH(d e) */ e.ename, d.dname
3 from emp e , temp_dept d
4 where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';
Explained.
--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 5 90 5 --> cost 가 5이다.
* 1 HASH JOIN 5 90 5
* 2 TABLE ACCESS FULL TEMP_DEPT 1 11 2
3 TABLE ACCESS FULL EMP 14 98 2
--------------------------------------------------------------------
4. NL join Cost
SQL> explain plan for
2 select /*+ USE_NL(d e) */ e.ename, d.dname
3 from emp e , dept d
4 where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';
Explained.
--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 5 90 4 --> cost 가 4이다.
1 NESTED LOOPS 5 90 4
* 2 TABLE ACCESS FULL TEMP_DEPT 1 11 2
* 3 TABLE ACCESS FULL EMP 5 35 2
--------------------------------------------------------------------
-- NL 조인이 COST 가 더작은걸 알수 있다.
5.CBO 는 과연 어떤 조인을 선택 할것인가?
SQL> explain plan for
2 select /*+ ALL_ROWS */ e.ename, d.dname
3 from emp e , dept d
4 where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';
--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 5 90 5 --> COST 가 높은 HASH 조인선택
* 1 HASH JOIN 5 90 5
* 2 TABLE ACCESS FULL TEMP_DEPT 1 11 2
3 TABLE ACCESS FULL EMP 14 98 2
--------------------------------------------------------------------
개인적인 생각으로 all_rows 에서는 비용이 높더라도 NL 조인시 FULL SCAN 을 반복하는것을 피하는 어떤 rule 이 있는것 같다.
6.결론:
FIRST_ROWS 나 FIRST_ROWS(1) 로 힌트를 사용하면 NL 조인으로 풀리는 것이 관찰 되었다.
그러나 FIRST_ROWS 시리즈는 완전한 Cost Base 라고 볼수 없고 NL 조인을 선호하는 Rule 이
포함될수 밖에 없다.
10g Default 로 all_rows 이며 부분범위 Rule 이 적용이 배제된 완전한 CBO 모드인 ALL_ROWS 에서 어처구니 없이 CBO 가 COST 가 높은 JOIN 을 선택하는것을 볼수 있다.
물론 TEMP_DEPT 테이블에 PK 혹은 UK를 만들면 이런현상은 사라진다.
하지만 현실(실제 프로젝트)에서는 이러한 PK 없는 임시작업 테이블들을 많이 사용하고 있다.
이런경우 CBO 가 항상 낮은 COST 를 선택하는 것은 아니므로 임시 테이블이나 global temp table 등을 사용시에는 주의할 필요가 있다.
보통 CBO 에서 문제가 되는것은 (스키마통계정보 or 시스템통계)의 부재나 부족으로 인한 잘못된 Cost를 계산해서 잘못된 실행계획을 선택하는것 이라고 볼수있다.
하지만 항상 그런것은 아니다.
CBO 가 높은 Cost 를 선택해서 끔찍한 일을 저지를 수도 있다는 것이다.
어떤경우에 No Costing 이 발생하는지 아래를 참조하자.
1.환경 :
버젼 : 10gR2(10.2.0.3)
Optimizer mode :all_rows
계정 : scott/tiger
2.임시 부서 테이블 생성
CREATE TABLE TEMP_DEPT AS SELECT * FROM DEPT; --> DEPT 임시테이블 생성
3.통계정보 생성
EXEC dbms_stats.gather_table_stats(user,'TEMP_DEPT',cascade=>true);
EXEC dbms_stats.gather_table_stats(user,'EMP',cascade=>true); --> EMP 는 그대로 사용.
4. Hash join Cost
SQL> explain plan for
2 select /*+ USE_HASH(d e) */ e.ename, d.dname
3 from emp e , temp_dept d
4 where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';
Explained.
--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 5 90 5 --> cost 가 5이다.
* 1 HASH JOIN 5 90 5
* 2 TABLE ACCESS FULL TEMP_DEPT 1 11 2
3 TABLE ACCESS FULL EMP 14 98 2
--------------------------------------------------------------------
4. NL join Cost
SQL> explain plan for
2 select /*+ USE_NL(d e) */ e.ename, d.dname
3 from emp e , dept d
4 where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';
Explained.
--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 5 90 4 --> cost 가 4이다.
1 NESTED LOOPS 5 90 4
* 2 TABLE ACCESS FULL TEMP_DEPT 1 11 2
* 3 TABLE ACCESS FULL EMP 5 35 2
--------------------------------------------------------------------
-- NL 조인이 COST 가 더작은걸 알수 있다.
5.CBO 는 과연 어떤 조인을 선택 할것인가?
SQL> explain plan for
2 select /*+ ALL_ROWS */ e.ename, d.dname
3 from emp e , dept d
4 where e.deptno = d.deptno
5 and d.dname = 'RESEARCH';
--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT 5 90 5 --> COST 가 높은 HASH 조인선택
* 1 HASH JOIN 5 90 5
* 2 TABLE ACCESS FULL TEMP_DEPT 1 11 2
3 TABLE ACCESS FULL EMP 14 98 2
--------------------------------------------------------------------
개인적인 생각으로 all_rows 에서는 비용이 높더라도 NL 조인시 FULL SCAN 을 반복하는것을 피하는 어떤 rule 이 있는것 같다.
6.결론:
FIRST_ROWS 나 FIRST_ROWS(1) 로 힌트를 사용하면 NL 조인으로 풀리는 것이 관찰 되었다.
그러나 FIRST_ROWS 시리즈는 완전한 Cost Base 라고 볼수 없고 NL 조인을 선호하는 Rule 이
포함될수 밖에 없다.
10g Default 로 all_rows 이며 부분범위 Rule 이 적용이 배제된 완전한 CBO 모드인 ALL_ROWS 에서 어처구니 없이 CBO 가 COST 가 높은 JOIN 을 선택하는것을 볼수 있다.
물론 TEMP_DEPT 테이블에 PK 혹은 UK를 만들면 이런현상은 사라진다.
하지만 현실(실제 프로젝트)에서는 이러한 PK 없는 임시작업 테이블들을 많이 사용하고 있다.
이런경우 CBO 가 항상 낮은 COST 를 선택하는 것은 아니므로 임시 테이블이나 global temp table 등을 사용시에는 주의할 필요가 있다.
2008년 5월 6일 화요일
오라클 White Paper 에 대하여
튜닝 관련 오라클 문서(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 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
2008년 4월 25일 금요일
치명적인 Hash Group By 버그
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;
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 이 있을수 있으므로 여러가지 테스트 후에 적용하기 바란다.
하지만 현재시점(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 이 있을수 있으므로 여러가지 테스트 후에 적용하기 바란다.
2008년 4월 23일 수요일
DML 과 PARALLEL의 관계
현재 많은 종류의 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
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
2008년 3월 30일 일요일
DA 조직의 구성에 대하여
필자의 후배들이 몇몇 업체의 DBA를 담당하는데 차세대 프로젝트에서 DA 조직의 구성방법에 대하여 가끔 필자에게 질문이 들어오곤 한다.
이때 필자는 반드시 아래의 구조를 가지게끔 답변을 한다.
1.CIO 는 반드시 DA팀의 수장이어야 한다.
2.전사적인 관점에서 DA 조직의 팀은 크게 7으로 나뉘어야 한다.
-표준화팀: 단어사전, 용어(속성)사전, ERD 표준, SQL 표준 , 프로그램 언어의 표준등 각종 표준정의및 관리
-모델링팀: 업무파악, 개념모델및 논리/물리모델 정의 및 관리
-DBA팀 : DBMS 종류별로 오브젝트의 생성및/관리, 오브젝트별 권한관리, 오프젝트별 용량관리, 백업/복구관리, DBMS 버그페치
-튜닝팀 : 쿼리및 오브젝트, 인스턴스의 최적화
-SYS ADMIN 팀:운영체제 관리및 최적화, 디스크 최적화(스트라이핑)및 관리, 네트워크 관리, OS 버그페치등
-데이터 보안및 품질팀: 기업의 데이터 품질이나 보안(DBMS내의 각종권한 정책 및 데이터 암호화등)에 대하여 관리
-전환팀 : 기존 NDB/HDB/RDB 의 데이터를 Cleansing및 매핑 하여 TO-BE 시스템으로 이관
3.DA의 각팀에는 반드시 권위있는 갑의 직원이 상주해야 한다.
4.DA의 각팀의 구성원은 반드시 해당하는 분야의 전문가 이어야 한다.
5.DA의 각팀은 반드시 긴밀히 협조해야 한다.
1번을 명시한 이유는 권력이 없는 수장이 관리 하는 DA 조직은 항상 불안하고 불필요한 논쟁이 끊이지 않을것이다.
예를 들면 표준화팀에서 표준을 배포해도 개발팀에서 이런저런 이유로 따르지 않을것이고 시간이 지나서 프로그램의 품질및 성능은 보장받지 못할것이며 그럴경우 관리자들은 누구의 책임인지 추궁하게 될것이다.
2번의 팀구성은 항상 7개의 팀으로 구성될 필요는 없다.
예를 들면 차세대 시스템의 분석단계에서는 표준화팀과 모델링팀은 필수적이고 나머지팀은 선택적일수 있다.
또한 개발단계에서는 튜닝팀과 데이터보안/품질팀이 필수적이며 이 필수적이며 세팅이 끝난 SYSTEM 팀은 선택적일수 있다.
OPEN 전단계에서는 전환팀이 필수적이다.
이처럼 단계별로 DA 조직을 늘였다 줄였다 할수 있으나 반드시 단계별로 필수적인 팀을 구성하여 운영하여야 한다.
또한 SYS ADMIN 팀은 사실상 DA 팀으로 볼수 없으나 운영하지 않을경우 DA 팀이 다치는 경우를 많이 보아왔다.
예전에는 요구가 거의 없다시피 했으나 최근에 특히 필수적으로 구성되는 팀이 데이터의 보안및 품질 팀이다.
3번을 명시한이유는 차세대프로젝트의 전형적인 문제점 때문이다.
다시말하면 갑의 요구와 컨설팅의 결과를 분석/설계/개발 하는것이 을의 할일 이지만 그것을 하기위해서는 반드시 갑의 도움이 있어야 한다.
예를 들면 모델러가 AS-IS 모델을 분석해야 할때 갑의 담당자가 없다면? 아마 팀마다 돌아다니면서 ERD 를 받아야 할것이다. 필자도 ERD 및 각종 분석에 필요한 문서를 수거하는데 3주나 걸린 뼈아픈 경험이 있다. 이것 때문에 WBS 상에 분석이 2주가 밀려 버렸다.
ERD와 각종문서를 를 받아야 할 당위성같은 문서를 작성해야 했으며 각종 문서별로 보안등급이 있어 부서마다 처음보는 업무요청서를 작성해야 했으며 부서중에서 1/3 정도는 보안관계상 문서를 주기 어렵다고 기각당하기 까지 했다.
반드시 권위있는 갑측 직원이 팀원으로 구성되어야 한다. 그리고 갑측직원은 반드시 전문가일 필요는 없다.
4번을 명시한 이유는 말하지 않아도 알것이다.
모델러를 잘못 선택한 순간 데이터의 구조가 엉망이 되고 튜너가 실력이 없으면 성능이 떨어지고 결국 DBMS 가 멎을 것이다. 또한 전환팀이 부실하면 데이터 이관작업이 몇년이 걸릴수도 있고 보안팀이 부실하면 OPEN 후에 해킹을 당할수도 있다. 똑똑한 DBA는 장애에 대한 계획을 세우고 장애시 모든데이터를 살린다.똑똑한 몇명이 전체 기업을 살릴수도 똑똑하지 못한 몇명이 전체 기업을 죽일수도 있다.
5번을 명시한 이유는 DA 팀의 각각의 팀원들은 전문가 들이고 또한 자존심 또한 매우 많은 편이다.
불필요한 논쟁을 하다보면 서로가 다치게 된다.
여러분도 알고있는 전형적인 논쟁을 예를들면 물리모델링 할때 모델러와 DBA 간의 싸움은 끝이없다.
최소한 같은 DA 조직내에 있는 팀원모두는 모를지라도 각팀의 PL 들은 서로를 잘알고 친해져야 할필요가 있다.
생각해보라 SQL 표준을 표준화팀에서 만들었고 그것을 어기면서 SQL 을 튜닝하는 튜닝팀이란...
1번부터 5번까지 반복적인 답변을 너무도 많이 하여 이제 는 외울(?) 지경이다
다행인것은 최근의 대형금융권 혹은 대형통신사의 차세대 프로젝트는 거의 필자의 구성법과 비슷하게 가고 있다는 것이다.
꼭 대형 업체(금융권이나 통신사)들의 차세대 시스템 구축이 아니더라도 여러분들이 IT 관련 일을 하고 있다면 나름대로 DA 팀을 구성 해보기 바란다.
어느자리에 누굴 쓸것인가? 혹은 내가 어느자리로 갈것인가?
DA 조직이란것이 21세기에 들어온이상 꿈같은 이야기만은 아닐것이다.
이때 필자는 반드시 아래의 구조를 가지게끔 답변을 한다.
1.CIO 는 반드시 DA팀의 수장이어야 한다.
2.전사적인 관점에서 DA 조직의 팀은 크게 7으로 나뉘어야 한다.
-표준화팀: 단어사전, 용어(속성)사전, ERD 표준, SQL 표준 , 프로그램 언어의 표준등 각종 표준정의및 관리
-모델링팀: 업무파악, 개념모델및 논리/물리모델 정의 및 관리
-DBA팀 : DBMS 종류별로 오브젝트의 생성및/관리, 오브젝트별 권한관리, 오프젝트별 용량관리, 백업/복구관리, DBMS 버그페치
-튜닝팀 : 쿼리및 오브젝트, 인스턴스의 최적화
-SYS ADMIN 팀:운영체제 관리및 최적화, 디스크 최적화(스트라이핑)및 관리, 네트워크 관리, OS 버그페치등
-데이터 보안및 품질팀: 기업의 데이터 품질이나 보안(DBMS내의 각종권한 정책 및 데이터 암호화등)에 대하여 관리
-전환팀 : 기존 NDB/HDB/RDB 의 데이터를 Cleansing및 매핑 하여 TO-BE 시스템으로 이관
3.DA의 각팀에는 반드시 권위있는 갑의 직원이 상주해야 한다.
4.DA의 각팀의 구성원은 반드시 해당하는 분야의 전문가 이어야 한다.
5.DA의 각팀은 반드시 긴밀히 협조해야 한다.
1번을 명시한 이유는 권력이 없는 수장이 관리 하는 DA 조직은 항상 불안하고 불필요한 논쟁이 끊이지 않을것이다.
예를 들면 표준화팀에서 표준을 배포해도 개발팀에서 이런저런 이유로 따르지 않을것이고 시간이 지나서 프로그램의 품질및 성능은 보장받지 못할것이며 그럴경우 관리자들은 누구의 책임인지 추궁하게 될것이다.
2번의 팀구성은 항상 7개의 팀으로 구성될 필요는 없다.
예를 들면 차세대 시스템의 분석단계에서는 표준화팀과 모델링팀은 필수적이고 나머지팀은 선택적일수 있다.
또한 개발단계에서는 튜닝팀과 데이터보안/품질팀이 필수적이며 이 필수적이며 세팅이 끝난 SYSTEM 팀은 선택적일수 있다.
OPEN 전단계에서는 전환팀이 필수적이다.
이처럼 단계별로 DA 조직을 늘였다 줄였다 할수 있으나 반드시 단계별로 필수적인 팀을 구성하여 운영하여야 한다.
또한 SYS ADMIN 팀은 사실상 DA 팀으로 볼수 없으나 운영하지 않을경우 DA 팀이 다치는 경우를 많이 보아왔다.
예전에는 요구가 거의 없다시피 했으나 최근에 특히 필수적으로 구성되는 팀이 데이터의 보안및 품질 팀이다.
3번을 명시한이유는 차세대프로젝트의 전형적인 문제점 때문이다.
다시말하면 갑의 요구와 컨설팅의 결과를 분석/설계/개발 하는것이 을의 할일 이지만 그것을 하기위해서는 반드시 갑의 도움이 있어야 한다.
예를 들면 모델러가 AS-IS 모델을 분석해야 할때 갑의 담당자가 없다면? 아마 팀마다 돌아다니면서 ERD 를 받아야 할것이다. 필자도 ERD 및 각종 분석에 필요한 문서를 수거하는데 3주나 걸린 뼈아픈 경험이 있다. 이것 때문에 WBS 상에 분석이 2주가 밀려 버렸다.
ERD와 각종문서를 를 받아야 할 당위성같은 문서를 작성해야 했으며 각종 문서별로 보안등급이 있어 부서마다 처음보는 업무요청서를 작성해야 했으며 부서중에서 1/3 정도는 보안관계상 문서를 주기 어렵다고 기각당하기 까지 했다.
반드시 권위있는 갑측 직원이 팀원으로 구성되어야 한다. 그리고 갑측직원은 반드시 전문가일 필요는 없다.
4번을 명시한 이유는 말하지 않아도 알것이다.
모델러를 잘못 선택한 순간 데이터의 구조가 엉망이 되고 튜너가 실력이 없으면 성능이 떨어지고 결국 DBMS 가 멎을 것이다. 또한 전환팀이 부실하면 데이터 이관작업이 몇년이 걸릴수도 있고 보안팀이 부실하면 OPEN 후에 해킹을 당할수도 있다. 똑똑한 DBA는 장애에 대한 계획을 세우고 장애시 모든데이터를 살린다.똑똑한 몇명이 전체 기업을 살릴수도 똑똑하지 못한 몇명이 전체 기업을 죽일수도 있다.
5번을 명시한 이유는 DA 팀의 각각의 팀원들은 전문가 들이고 또한 자존심 또한 매우 많은 편이다.
불필요한 논쟁을 하다보면 서로가 다치게 된다.
여러분도 알고있는 전형적인 논쟁을 예를들면 물리모델링 할때 모델러와 DBA 간의 싸움은 끝이없다.
최소한 같은 DA 조직내에 있는 팀원모두는 모를지라도 각팀의 PL 들은 서로를 잘알고 친해져야 할필요가 있다.
생각해보라 SQL 표준을 표준화팀에서 만들었고 그것을 어기면서 SQL 을 튜닝하는 튜닝팀이란...
1번부터 5번까지 반복적인 답변을 너무도 많이 하여 이제 는 외울(?) 지경이다
다행인것은 최근의 대형금융권 혹은 대형통신사의 차세대 프로젝트는 거의 필자의 구성법과 비슷하게 가고 있다는 것이다.
꼭 대형 업체(금융권이나 통신사)들의 차세대 시스템 구축이 아니더라도 여러분들이 IT 관련 일을 하고 있다면 나름대로 DA 팀을 구성 해보기 바란다.
어느자리에 누굴 쓸것인가? 혹은 내가 어느자리로 갈것인가?
DA 조직이란것이 21세기에 들어온이상 꿈같은 이야기만은 아닐것이다.
Jonathan Lewis 의 퀴즈 접근법
Jonathan Lewis 의 Cost-Based Oracle Fundamental 이라는 책을보면
퀴즈에 대한 Jonathan의 재미있는 접근법과 사고방식을 엿볼수 있다.
물론 SQL을 이용한 퀴즈문제 이다.
퀴즈는 두 수학자의 대화로 부터 시작하며 수학자2의 세딸아이의 나이를 맞추는 것이다.
Jonathan은 본퀴즈에 대한 자신의 접근법이 성능과는 무관하다는 것을 먼저 이야기 하였다.
퀴즈의 제약조건은 다른 언어(3GL 또는 4GL)의 도움없이 순수한 select 쿼리 하나로만 이루어져야 한다.
수학자1이 나이를 맞추어야 하며 수학자1이 힌트를 요구할때마다 수학자2가 적절한
힌트를 주는 방식으로 대화가 진행된다.
수학자1 : Do you have any children ?
수학자2 : Yes, three.
수학자1 : How old are they ?
수학자2 : Multiply their ages together and you get 36 --> 세딸의 나이를 곱하면 36임을 알수있다.
수학자1 : That's not enough information to work out the answer -->위의정보로는 부족하므로 힌트를 더 요구한다.
수학자2 :Add their ages together and the answer is the same as the number of people in this room --> 세딸의 나이를 더하면 이방의 사람수와 같음을 알수 있다.
수학자1 :That's still not enough information to work out the answer --> 그것만으론 부족해서 힌트를 더 요구한다.
수학자2 :The oldest daughter has a pet hamster with a wooden leg. -->가장 나이가 많은 딸이 햄스터를 키운다.
수학자1 :Thank you. I've got the answer. --> 수학자1이 드디어 정답을 맞추었다.
위의 대화를 종합하여 필자는 다음과 같은 결론을 내렸다.
1.세딸의 나이의 곱이 36 이다. 따라서 1*1*36 일수도 있고 1* 2* 18 일수 도 있고 나머지조합도 여럿일수 있다.
2.세딸의 나이의 합은 그방의 사람의 수와 일치하나 세딸의 나이의곱 = 36 and 세딸의 나이의 합이 같은 조합 >= 2
위의 조건으로도 조합이 여러개 생겨서 정답을 구할수 없으므로 수학자 1이 힌트를 더요구하고 있다.
3.가장 나이가 많은 딸이 한명임을 알수있다. 즉 막내와 둘째는 나이가 같을수 있지만 장녀는 둘보다 나이가 많다는 것이다.
select 문 한방으로 퀴즈를 풀수 있어야 한다.
이제부터 Jonathan의 방식대로 한줄 한줄씩 풀어보자.
먼저1~36 까지의 나이 LIST 를 만든다.
with age_list as ( select rownum age from all_objects where rownum <= 36),
1)위에서만든 LIST 로 곱이 36인 막내, 둘째, 첫째아이의 나이조합을 만들고
나이의 합과 곱도 만든다.
product_check as (
select age1.age as youngest,
age2.age as middle,
age3.age as oldest,
age1.age + age2.age + age3.age as summed,
age1.age * age2.age * age3.age as product
from age_list age1,
age_list age2,
age_list age3
where age2.age >= age1.age
and age3.age >= age2.age
and age1.age * age2.age * age3.age = 36 ),
2)세딸의 나이를 더하면 이방의 사람수와 같으나 그조합이 여러개임
summed_check as
(
select youngest, middle, oldest, summed, product
from ( select youngest, middle, oldest, summed, product,
count(*) over (partition by summed) ct
from product_check )
where ct > 1 --> 나이의 합의 조합이 2개 이상 나와야 한다.
)
3)가장 나이가 많은 딸이 한명임을 이용한 조건
select *
from summed_check
where oldest > middle;
With 문의 특징과 분석함수를 이용하여 단계적(절차적)으로 퀴즈를 풀고 있다.
유용하고 수학적인 접근법이다.
여러분이라면 더멋지게 퀴즈를 풀수 있지 않을까?
서브쿼리 팩토링(with 서브쿼리) 의 한가지 주의사항은 인라인뷰와는 다르게 no_merge 힌트등을 쓸필요가 없고 (꼭써야 한다면 서브쿼리에 Merterialize 힌트를 사용한다) 서브쿼리에 inline힌트를 써서 옵티마이져 자신이 서브쿼리를 최적화 한다는데 묘미가 있다고 Jonathan은 밝히고 있다.
아래 압축파일중에 9장 with_subq_01~02 부분을 보면된다. 꼭 테스트 해보기 바란다.
관련 SQL:http://www.apress.com/book/downloadfile/2450
2008년 3월 9일 일요일
히스토그램의 가상(virtual) 컬럼이용
개념 :
FBI (Function Based Index) 를 사용하고 히스토그램을 생성하면 오라클은 유져의 의지와는 상관없이 히스토그램에 가상컬럼을 사용한다.
가상컬럼은 11g 에서는 아예 컬럼값으로 인정하고 파티션및 인덱스도 생성할수 있게 되었다.
예를 들면 upper(컬럼1) 로 해서 컬럼을 생성할수 있는것이다.
하지만 11g 에서도 가상컬럼을 인덱스로 만들면 FBI 로 생성된다.
FBI 사용시 히스토그램에서 가상(virtual) 컬럼이용의 개념과 주의사항을 살펴본다.
아래예제는 오라클 10gR2 에서 테스트 하였다.
--테이블 생성
create table HIDDEN_COL_TEST (A varchar2(20) , B varchar2(100));
-- 데이터 생성
--컬럼 A 에는 분포도가 'a' 가 50%, 'A' 가 50% 로 생성한다.
insert into HIDDEN_COL_TEST
select 'a' , rpad('b',100) from all_objects
union all
select 'A' , rpad('b',100) from all_objects;
commit;
--통계정보생성
EXEC dbms_stats.gather_table_stats(user,'HIDDEN_COL_TEST',cascade=>true);
--인덱스 생성
create index HIDDEN_COL_IDX on HIDDEN_COL_TEST ( upper(A) ) ;
--인덱스 통계생성
EXEC dbms_stats.gather_index_stats(user,'HIDDEN_COL_IDX');
이제 준비가 다되었다.
explain plan for select * from HIDDEN_COL_TEST where upper(A) = :v_bind;
-->변수에 'a' 사용함.
위의쿼리의 경우 전체건이 조회된다.
Plan은 당연히 FTS( Full Table Scan) 으로 풀려야함에도 불구하고 옵티마이져는 우리의 기대를 져버린다.
select * from table(dbms_xplan.display);
---------------------------------------------------------------
Id Operation Name Rows
---------------------------------------------------------------
0 SELECT STATEMENT 792
1 TABLE ACCESS BY INDEX ROWID HIDDEN_COL_TEST 792
* 2 INDEX RANGE SCAN HIDDEN_COL_IDX 320
---------------------------------------------------------------
전체건수의 100% 에 해당하는 데이터를 오라클은 인덱스를 사용하였다.왜그럴까?
아래는 dynamic_sampling 을 최고수준으로 주었지만 별소용이 없었다.
explain plan for
select /*+ dynamic_sampling(HIDDEN_COL_TEST 10) */ *
from HIDDEN_COL_TEST where upper(A) = :v_bind;
select * from table(dbms_xplan.display);
---------------------------------------------------------------
Id Operation Name Rows
---------------------------------------------------------------
0 SELECT STATEMENT 792
1 TABLE ACCESS BY INDEX ROWID HIDDEN_COL_TEST 792
* 2 INDEX RANGE SCAN HIDDEN_COL_IDX 320
---------------------------------------------------------------
오라클이 비정상적으로 인덱스를 사용한 이유는 히스토그램에 가상컬럼을 생성시켜 주지 않았기 때문이다.
히스토그램을 조회해보자.
SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE table_name = 'HIDDEN_COL_TEST';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------- ------------------- ------------------------ ------------------------
HIDDEN_COL_TEST A 0 3.37499295804764E35
HIDDEN_COL_TEST B 0 5.09496674487288E35
HIDDEN_COL_TEST A 1 5.03652795277878E35
HIDDEN_COL_TEST B 1 5.09496674487288E35
아직 가상컬럼이 나타나지 않았다.
--테이블 통계정보를 다시생성한다.
EXEC dbms_stats.gather_table_stats(user,'HIDDEN_COL_TEST',cascade=>true);
히스토그램을 다시 조회해보자.
SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE table_name = 'HIDDEN_COL_TEST';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------- ------------------- ------------------------ ------------------------
HIDDEN_COL_TEST A 0 3.37499295804764E35
HIDDEN_COL_TEST B 0 5.09496674487288E35
HIDDEN_COL_TEST A 1 5.03652795277878E35
HIDDEN_COL_TEST B 1 5.09496674487288E35
HIDDEN_COL_TEST SYS_NC00003$ 5585 3.37499295804764E35
히스토그램에 가상컬럼이 생성됬다.
이제 실행계획을 다시 생성시켜보자.
explain plan for select * from HIDDEN_COL_TEST where upper(A) = :v_bind;
select * from table(dbms_xplan.display);
-----------------------------------------------------
Id Operation Name Rows
-----------------------------------------------------
0 SELECT STATEMENT 79061
* 1 TABLE ACCESS FULL HIDDEN_COL_TEST 79061
-----------------------------------------------------
예상대로 실행계획이 정상으로 돌아왔다.
결론 :
FBI 생성시 반드시 테이블 통계정보를 다시생성해야 한다는걸 알수 있다.
그렇지않으면 가상컬럼이 히스토그램에 생성되지 않을 뿐만아니라 성능도 저하될수 있다는걸 반드시 기억하여야 한다.
참조 URL:
http://www.oracledba.co.uk/tips/collect_stats_subtle.htm
FBI (Function Based Index) 를 사용하고 히스토그램을 생성하면 오라클은 유져의 의지와는 상관없이 히스토그램에 가상컬럼을 사용한다.
가상컬럼은 11g 에서는 아예 컬럼값으로 인정하고 파티션및 인덱스도 생성할수 있게 되었다.
예를 들면 upper(컬럼1) 로 해서 컬럼을 생성할수 있는것이다.
하지만 11g 에서도 가상컬럼을 인덱스로 만들면 FBI 로 생성된다.
FBI 사용시 히스토그램에서 가상(virtual) 컬럼이용의 개념과 주의사항을 살펴본다.
아래예제는 오라클 10gR2 에서 테스트 하였다.
--테이블 생성
create table HIDDEN_COL_TEST (A varchar2(20) , B varchar2(100));
-- 데이터 생성
--컬럼 A 에는 분포도가 'a' 가 50%, 'A' 가 50% 로 생성한다.
insert into HIDDEN_COL_TEST
select 'a' , rpad('b',100) from all_objects
union all
select 'A' , rpad('b',100) from all_objects;
commit;
--통계정보생성
EXEC dbms_stats.gather_table_stats(user,'HIDDEN_COL_TEST',cascade=>true);
--인덱스 생성
create index HIDDEN_COL_IDX on HIDDEN_COL_TEST ( upper(A) ) ;
--인덱스 통계생성
EXEC dbms_stats.gather_index_stats(user,'HIDDEN_COL_IDX');
이제 준비가 다되었다.
explain plan for select * from HIDDEN_COL_TEST where upper(A) = :v_bind;
-->변수에 'a' 사용함.
위의쿼리의 경우 전체건이 조회된다.
Plan은 당연히 FTS( Full Table Scan) 으로 풀려야함에도 불구하고 옵티마이져는 우리의 기대를 져버린다.
select * from table(dbms_xplan.display);
---------------------------------------------------------------
Id Operation Name Rows
---------------------------------------------------------------
0 SELECT STATEMENT 792
1 TABLE ACCESS BY INDEX ROWID HIDDEN_COL_TEST 792
* 2 INDEX RANGE SCAN HIDDEN_COL_IDX 320
---------------------------------------------------------------
전체건수의 100% 에 해당하는 데이터를 오라클은 인덱스를 사용하였다.왜그럴까?
아래는 dynamic_sampling 을 최고수준으로 주었지만 별소용이 없었다.
explain plan for
select /*+ dynamic_sampling(HIDDEN_COL_TEST 10) */ *
from HIDDEN_COL_TEST where upper(A) = :v_bind;
select * from table(dbms_xplan.display);
---------------------------------------------------------------
Id Operation Name Rows
---------------------------------------------------------------
0 SELECT STATEMENT 792
1 TABLE ACCESS BY INDEX ROWID HIDDEN_COL_TEST 792
* 2 INDEX RANGE SCAN HIDDEN_COL_IDX 320
---------------------------------------------------------------
오라클이 비정상적으로 인덱스를 사용한 이유는 히스토그램에 가상컬럼을 생성시켜 주지 않았기 때문이다.
히스토그램을 조회해보자.
SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE table_name = 'HIDDEN_COL_TEST';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------- ------------------- ------------------------ ------------------------
HIDDEN_COL_TEST A 0 3.37499295804764E35
HIDDEN_COL_TEST B 0 5.09496674487288E35
HIDDEN_COL_TEST A 1 5.03652795277878E35
HIDDEN_COL_TEST B 1 5.09496674487288E35
아직 가상컬럼이 나타나지 않았다.
--테이블 통계정보를 다시생성한다.
EXEC dbms_stats.gather_table_stats(user,'HIDDEN_COL_TEST',cascade=>true);
히스토그램을 다시 조회해보자.
SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE table_name = 'HIDDEN_COL_TEST';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------------------- ------------------- ------------------------ ------------------------
HIDDEN_COL_TEST A 0 3.37499295804764E35
HIDDEN_COL_TEST B 0 5.09496674487288E35
HIDDEN_COL_TEST A 1 5.03652795277878E35
HIDDEN_COL_TEST B 1 5.09496674487288E35
HIDDEN_COL_TEST SYS_NC00003$ 5585 3.37499295804764E35
히스토그램에 가상컬럼이 생성됬다.
이제 실행계획을 다시 생성시켜보자.
explain plan for select * from HIDDEN_COL_TEST where upper(A) = :v_bind;
select * from table(dbms_xplan.display);
-----------------------------------------------------
Id Operation Name Rows
-----------------------------------------------------
0 SELECT STATEMENT 79061
* 1 TABLE ACCESS FULL HIDDEN_COL_TEST 79061
-----------------------------------------------------
예상대로 실행계획이 정상으로 돌아왔다.
결론 :
FBI 생성시 반드시 테이블 통계정보를 다시생성해야 한다는걸 알수 있다.
그렇지않으면 가상컬럼이 히스토그램에 생성되지 않을 뿐만아니라 성능도 저하될수 있다는걸 반드시 기억하여야 한다.
참조 URL:
http://www.oracledba.co.uk/tips/collect_stats_subtle.htm
묵시적인 형변환을 피하라
SQL 에서 묵시적인 형변환(Implicit Datatype Conversion) 을 피하라.
위와같은 말을 언젠가 들어보았을것이다.
WHERE 절의 묵시적 형변환에 의한 Full table scan 은 잘알려져 있다 .
이문제는 Oracle 10g 부터 SQL 이 PL/SQL내에서 사용된다면 dbms_warning 패키지나 plsql_warnings 기능을 사용하면 식별될수 있다.
아래 예제를 보자.
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; --> warning 가능 활성화
SQL> CREATE TABLE t ( a VARCHAR2(10) ); --> varchar2 타입으로 컬럼생성
Table created.
SQL> CREATE OR REPLACE PROCEDURE p
2 IS
3 BEGIN
4 INSERT INTO t VALUES ( 10 ); --> number 타입으로 insert
5 END p;
6 /
SP2-0804: Procedure created with compilation warnings
SQL> SHO ERR
Errors for PROCEDURE P:
4/26 PLW-07202: bind type would result in conversion away from column type
-->4번째 라인에서 묵시적인 형변환이 일어나고 있음을 알려준다.
그런데 이런 묵시적인 형변환에의한 성능저하가 SQL에만 해당되는 이야기인가?
묵시적인 형변환에 의한 PL/SQL 자체의 성능도 한번쯤 의문을 가져볼 필요가 있다.
아래 예제는 데이터 타입선정을 잘못하면 프로그램이 얼마나 망가질수 있는지를 잘나타내고 있다.
create or replace procedure imp_type_conv_test is
x date;
y varchar2(12) := '01-MAR-03'
t number := dbms_utility.get_time;
begin
for i in 1 .. 1000000 loop
x := y; --> 묵시적인 형변환
end loop; dbms_output.put_line((dbms_utility.get_time-t)'hsec');
end;
/
결과 : 826hsec
create or replace procedure exp_type_conv_test is
x date;
y x%type := to_date('01-MAR-03'); --> date 형으로 선언하고 미리 conversion 함
t number := dbms_utility.get_time;
begin
for i in 1 .. 1000000 loop
x := y; --> 형변환이 일어나지 않음
end loop;
dbms_output.put_line((dbms_utility.get_time-t)'hsec');
end;
/
결과 : 38hsec --> 무려 2200% 가까이 성능이 향상됬다.
결론 :
Implicit Datatype Conversion 은 SQL 의 성능 뿐만아니라 PL/SQL 자체의 성능도 저하시킴을 알수 있다.
명시적으로 TO_DATE, TO_NUMBER 등의 함수를 사용함으로써 좀더 직관적이고
성능면에서도 유리한 프로그램을 개발할수 있다.
참조서적 : Mastering Oracle PL/SQL(Connor McDonald )
위와같은 말을 언젠가 들어보았을것이다.
WHERE 절의 묵시적 형변환에 의한 Full table scan 은 잘알려져 있다 .
이문제는 Oracle 10g 부터 SQL 이 PL/SQL내에서 사용된다면 dbms_warning 패키지나 plsql_warnings 기능을 사용하면 식별될수 있다.
아래 예제를 보자.
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; --> warning 가능 활성화
SQL> CREATE TABLE t ( a VARCHAR2(10) ); --> varchar2 타입으로 컬럼생성
Table created.
SQL> CREATE OR REPLACE PROCEDURE p
2 IS
3 BEGIN
4 INSERT INTO t VALUES ( 10 ); --> number 타입으로 insert
5 END p;
6 /
SP2-0804: Procedure created with compilation warnings
SQL> SHO ERR
Errors for PROCEDURE P:
4/26 PLW-07202: bind type would result in conversion away from column type
-->4번째 라인에서 묵시적인 형변환이 일어나고 있음을 알려준다.
그런데 이런 묵시적인 형변환에의한 성능저하가 SQL에만 해당되는 이야기인가?
묵시적인 형변환에 의한 PL/SQL 자체의 성능도 한번쯤 의문을 가져볼 필요가 있다.
아래 예제는 데이터 타입선정을 잘못하면 프로그램이 얼마나 망가질수 있는지를 잘나타내고 있다.
create or replace procedure imp_type_conv_test is
x date;
y varchar2(12) := '01-MAR-03'
t number := dbms_utility.get_time;
begin
for i in 1 .. 1000000 loop
x := y; --> 묵시적인 형변환
end loop; dbms_output.put_line((dbms_utility.get_time-t)'hsec');
end;
/
결과 : 826hsec
create or replace procedure exp_type_conv_test is
x date;
y x%type := to_date('01-MAR-03'); --> date 형으로 선언하고 미리 conversion 함
t number := dbms_utility.get_time;
begin
for i in 1 .. 1000000 loop
x := y; --> 형변환이 일어나지 않음
end loop;
dbms_output.put_line((dbms_utility.get_time-t)'hsec');
end;
/
결과 : 38hsec --> 무려 2200% 가까이 성능이 향상됬다.
결론 :
Implicit Datatype Conversion 은 SQL 의 성능 뿐만아니라 PL/SQL 자체의 성능도 저하시킴을 알수 있다.
명시적으로 TO_DATE, TO_NUMBER 등의 함수를 사용함으로써 좀더 직관적이고
성능면에서도 유리한 프로그램을 개발할수 있다.
참조서적 : Mastering Oracle PL/SQL(Connor McDonald )
2008년 3월 6일 목요일
PL/SQL 에서 NUMBER 타입의 성능 테스트
10g 버젼까지는 소수점없는 숫자타입 연산에서 제일빠른 숫자타입은 pls_integer 였다.
이제 11g 에서 기존 pls_integer 의 sub type 인 simple_integer 이 나왔으므로 성능관점에서
어떤것이 가장빠른것인지 test를 해볼 필요가 있다.
PL/SQL 의 성능관점에서 11g 에 새로운 simple_integer 타입에 대하여 간단한 테스트를 진행하고자 한다.
진행하기전에 먼저 simple_integer 의 특징에 대해 간단히 설명한다.
simple_integer 의 특징
1)simple_integer 는 기존 pls_integer 의 sub type 이다.
2)simple_integer 의 사용범위는 2,147,483,648 ~ 2,147,483,647 이다.
3)null 값을 가지지 못한다. --> default 로 0 을 주어야 한다.
4)성능면에서 최적이다. 특히 native 로 컴파일 할때는 pls_integer 보다 4~5 배 빠르다.
아래는 number 와 pls_interger,simple_integer 를 비교실험하는 간단한 프로시져이다.
로직은 단순히 1씩 증가하는 연산이다.
성능테스트
CREATE OR REPLACE PROCEDURE plsql_number_test(v_loop in number) as
l_start NUMBER;
l_number NUMBER := 0;
l_number_incr NUMBER := 1;
l_pls_integer PLS_INTEGER := 0;
l_pls_integer_incr PLS_INTEGER := 1;
l_simple_integer SIMPLE_INTEGER := 0;
l_simple_integer_incr SIMPLE_INTEGER := 1;
BEGIN
-- number 형 test
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. v_loop LOOP
l_number := l_number + l_number_incr;
END LOOP;
DBMS_OUTPUT.put_line('NUMBER : ' (DBMS_UTILITY.get_time - l_start) ' hsecs');
-- pls_integer 형 test
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. v_loop LOOP
l_pls_integer := l_pls_integer + l_pls_integer_incr;
END LOOP;
DBMS_OUTPUT.put_line('PLS_INTEGER: ' (DBMS_UTILITY.get_time - l_start) ' hsecs');
--simple_integer 형 test
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. v_loop LOOP
l_simple_integer := l_simple_integer + l_simple_integer_incr;
END LOOP;
DBMS_OUTPUT.put_line('SIMPLE_INTEGER: ' (DBMS_UTILITY.get_time - l_start) ' hsecs');
END plsql_number_test;
/
실행1
SQL> plsql_number_test(100000000 );
결과1
NUMBER : 1217 hsecs
PLS_INTEGER: 546 hsecs
SIMPLE_INTEGER: 512hsecs --> 가장빠르나 PLS_INTEGER 와는 거의 차이가 나지않는다.
NATIVE 모드로 컴파일 한다.
ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE;
ALTER PROCEDURE plsql_number_test COMPILE;
실행2
SQL> plsql_number_test(100000000 );
결과2
NUMBER : 386 hsecs
PLS_INTEGER: 129 hsecs
SIMPLE_INTEGER: 32 hsecs -->PLS_INTEGER 보다 무려 4배정도가 빠르다.
결론 :
native 로 컴파일 했을때 모든 숫자형식이 다빨라졌지만 특히 SIMPLE_INTEGER의 성능향상이 눈에 뛴다.
2,147,483,648 ~ 2,147,483,647 범위내에서 소수점이 없는 숫자연산을 할경우에는
SIMPLE_INTEGER 를 써야 한다.
물론 10g 이하버젼 이라면 number 형보다 2~3 배 이상 빠른 PLS_INTEGER 를 사용해야 할것이다.
아쉬운점은 PL/SQL 뿐만 아니라 테이블의 데이터 타입으로도 사용할수 있으면 하는것이다.
12버젼에서 컬럼타입으로 사용할수 있게 되기를 기대해보면서 이글을 마친다.
참조 URL :
http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements_11gR1.php#simple_integer
이제 11g 에서 기존 pls_integer 의 sub type 인 simple_integer 이 나왔으므로 성능관점에서
어떤것이 가장빠른것인지 test를 해볼 필요가 있다.
PL/SQL 의 성능관점에서 11g 에 새로운 simple_integer 타입에 대하여 간단한 테스트를 진행하고자 한다.
진행하기전에 먼저 simple_integer 의 특징에 대해 간단히 설명한다.
simple_integer 의 특징
1)simple_integer 는 기존 pls_integer 의 sub type 이다.
2)simple_integer 의 사용범위는 2,147,483,648 ~ 2,147,483,647 이다.
3)null 값을 가지지 못한다. --> default 로 0 을 주어야 한다.
4)성능면에서 최적이다. 특히 native 로 컴파일 할때는 pls_integer 보다 4~5 배 빠르다.
아래는 number 와 pls_interger,simple_integer 를 비교실험하는 간단한 프로시져이다.
로직은 단순히 1씩 증가하는 연산이다.
성능테스트
CREATE OR REPLACE PROCEDURE plsql_number_test(v_loop in number) as
l_start NUMBER;
l_number NUMBER := 0;
l_number_incr NUMBER := 1;
l_pls_integer PLS_INTEGER := 0;
l_pls_integer_incr PLS_INTEGER := 1;
l_simple_integer SIMPLE_INTEGER := 0;
l_simple_integer_incr SIMPLE_INTEGER := 1;
BEGIN
-- number 형 test
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. v_loop LOOP
l_number := l_number + l_number_incr;
END LOOP;
DBMS_OUTPUT.put_line('NUMBER : ' (DBMS_UTILITY.get_time - l_start) ' hsecs');
-- pls_integer 형 test
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. v_loop LOOP
l_pls_integer := l_pls_integer + l_pls_integer_incr;
END LOOP;
DBMS_OUTPUT.put_line('PLS_INTEGER: ' (DBMS_UTILITY.get_time - l_start) ' hsecs');
--simple_integer 형 test
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. v_loop LOOP
l_simple_integer := l_simple_integer + l_simple_integer_incr;
END LOOP;
DBMS_OUTPUT.put_line('SIMPLE_INTEGER: ' (DBMS_UTILITY.get_time - l_start) ' hsecs');
END plsql_number_test;
/
실행1
SQL> plsql_number_test(100000000 );
결과1
NUMBER : 1217 hsecs
PLS_INTEGER: 546 hsecs
SIMPLE_INTEGER: 512hsecs --> 가장빠르나 PLS_INTEGER 와는 거의 차이가 나지않는다.
NATIVE 모드로 컴파일 한다.
ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE;
ALTER PROCEDURE plsql_number_test COMPILE;
실행2
SQL> plsql_number_test(100000000 );
결과2
NUMBER : 386 hsecs
PLS_INTEGER: 129 hsecs
SIMPLE_INTEGER: 32 hsecs -->PLS_INTEGER 보다 무려 4배정도가 빠르다.
결론 :
native 로 컴파일 했을때 모든 숫자형식이 다빨라졌지만 특히 SIMPLE_INTEGER의 성능향상이 눈에 뛴다.
2,147,483,648 ~ 2,147,483,647 범위내에서 소수점이 없는 숫자연산을 할경우에는
SIMPLE_INTEGER 를 써야 한다.
물론 10g 이하버젼 이라면 number 형보다 2~3 배 이상 빠른 PLS_INTEGER 를 사용해야 할것이다.
아쉬운점은 PL/SQL 뿐만 아니라 테이블의 데이터 타입으로도 사용할수 있으면 하는것이다.
12버젼에서 컬럼타입으로 사용할수 있게 되기를 기대해보면서 이글을 마친다.
참조 URL :
http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements_11gR1.php#simple_integer
2008년 2월 27일 수요일
Recursive SQL
개념 :
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)
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)
2008년 2월 26일 화요일
힌트로 오라클 파라미터를 바꿀수는 없을까?
개념:
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 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
2008년 2월 22일 금요일
Outer Join 의 재조명
Outer Join 의 Lateral Views 로의 변환
목적 : 많은 사람들이 ANSI OUTER JOIN 에 대해 이해하지 못하고 심지어 튜너들이라고 하는사람들까지도 개념정립을 못하고 있다.
SELECT empno, ename, dname, d.deptno
FROM EMP E LEFT OUTER JOIN DEPT D
ON E.deptno = D.deptno
WHERE E.empno > 7600;
목적 : 많은 사람들이 ANSI OUTER JOIN 에 대해 이해하지 못하고 심지어 튜너들이라고 하는사람들까지도 개념정립을 못하고 있다.
이 문서를 보고 개념정립을 확실히 하기바란다.
개념:
Oracle 에서는, ANSI left outer join은 내부적으로 left outer joined lateral views로 표현된다.
'lateral view'는 FROM절에서 앞서 나타난 다른 테이블들을 참조하는 관계를 포함한
inline view이다.
다시말하면 'lateral view'는 Scalar inline view 라고 보면된다.
'lateral view'는 스칼라 서브쿼리처럼 결과집합의 건수에 영향을 미치지 못한다.
개념:
Oracle 에서는, ANSI left outer join은 내부적으로 left outer joined lateral views로 표현된다.
'lateral view'는 FROM절에서 앞서 나타난 다른 테이블들을 참조하는 관계를 포함한
inline view이다.
다시말하면 'lateral view'는 Scalar inline view 라고 보면된다.
'lateral view'는 스칼라 서브쿼리처럼 결과집합의 건수에 영향을 미치지 못한다.
Oracle 은 ANSI SQL 2003 의 'lateral view'를 지원하지 못하고 내부적으로만 사용하고 있다.
버그인가 아니면 정확한 결과인가?
아래 예제를 보고 헷갈릴수밖에 없는 이유를 설명한다.
데이터는 다음과 같다.
1) EMP 테이블
SELECT EMPNO, ENAME, DEPTNO
FROM EMP;
FROM EMP;
2) DEPT 테이블
SELECT DEPTNO, DNAME
FROM DEPT;
3) ANSI 문법으로 outer join 쿼리를 날려본다.
SELECT empno, ename, dname, d.deptno
FROM EMP E LEFT OUTER JOIN DEPT D
ON E.deptno = D.deptno
AND E.empno > 7600;
뭔가 이상하다 사번이 7600 보다 큰건들만 나오지 않고 전체가 나와 버린다.
버그인가?
전혀아니다.
전혀아니다.
오라클 옵티마이져는 위 SQL 이 나오면 아래와 같이 변환한다.
변환된 모습을 보면 결과를 예측할수 있다.
변환된 모습을 보면 결과를 예측할수 있다.
'lateral view' 개념을 적용
위의 쿼리변형결과를 보고 아래와 같은 결론을 내릴수있다.
1)위의 'lateral view' 는 결과 건수에 영향을 미치지 못하는 스칼라 인라인뷰이다.
2)E.empno > 7600 조건은 결과건수에 영향을 못미치고 DEPT 와의 조인건수에만
영향을 끼친다.
다시말하면 E.empno > 7600 에 만족하는건만 DEPT 와 조인한다.
3)ANSI OUTER JOIN 이 헷갈릴 경우는 Lateral View 개념을 적용하여 쿼리를 작성하면 된다.
다시말하면 E.empno > 7600 에 만족하는건만 DEPT 와 조인한다.
3)ANSI OUTER JOIN 이 헷갈릴 경우는 Lateral View 개념을 적용하여 쿼리를 작성하면 된다.
그렇다면 아래와 같은 쿼리는 결과가 어떻게 나올것인가
SELECT empno, ename, dname, d.deptno
FROM EMP E LEFT OUTER JOIN DEPT D
ON E.deptno = D.deptno
WHERE E.empno > 7600;
위쿼리를 'lateral view' 개념을 적용하면 아래와 같다
위쿼리를 보면 메인쿼리의 WHERE 절이 추가된것을 알수 있다.
결과도 당연히 사번이 7600 보다 큰건만 나오게 된다.
참고 문헌
ANSI Outer Joins And Lateral Views, http://structureddata.org/2008/02/18/ansi-outer-joins-and-lateral-views/(structureddata.org)
Back to basics: outer joins, http://awads.net/wp/2006/03/22/back-to-basics-outer-joins/
ANSI Outer Joins And Lateral Views, http://structureddata.org/2008/02/18/ansi-outer-joins-and-lateral-views/(structureddata.org)
Back to basics: outer joins, http://awads.net/wp/2006/03/22/back-to-basics-outer-joins/
DBMS_ADVANCED_REWRITE
개념 :
-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
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');
-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
SYS.DBMS_ADVANCED_REWRITE.declare_rewrite_equivalence ( name => 'test1_rewrite',
source_stmt => 'SELECT * FROM test_tab',
destination_stmt => 'SELECT * FROM rewrite_v',
validate => FALSE,
rewrite_mode => 'TEXT_MATCH');
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');
2008년 2월 21일 목요일
SQL Tuning Advisor
개념 :
- 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 항목에서는 튜닝방법대로 했을경우 옵티마이져가 어떻게 반응하는지를 보여준다.
위의 예제에서는 통계정보를 생성하면 실행계획이 바뀐다는점을 알려주고 있다.
신기하지 않은가?
- 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 항목에서는 튜닝방법대로 했을경우 옵티마이져가 어떻게 반응하는지를 보여준다.
위의 예제에서는 통계정보를 생성하면 실행계획이 바뀐다는점을 알려주고 있다.
신기하지 않은가?
2008년 2월 11일 월요일
STATSPACK 사용법
1. statspack 인스톨-------------------------------------------------------
1-1. 디렉토리 이동
cd $ORACLE_HOME/rdbms/admin
1-2.
sqlplus /nolog
1-3.
SQL>connect sys/change_on_install as sysdba
1-4.
SQL>@spcreate.sql
Password:perfstat
default tablespace:statspack
temporary tablespace:temp
2. snapshot 취득-------------------------------------------------------
주의＞스냅 레벨의 디폴트는 5
스냅 레벨의 조정은 아래의 snapshot·레벨의 설정 참조
SQL>conn perfstat/perfstat
SQL>execute statspack.snap
혹은 execute statspack.snap(i_snap_level =>5）
3. 리포트 출력-------------------------------------------------------
3-1 snapshot·리스트 확인
SQL>conn perfstat/perfstat
SQL>select SNAP_ID, SNAP_TIME,SNAP_LEVEL from STATS$SNAPSHOT;
3-2 리포트 출력
SQL>conn perfstat/perfstat
SQL>@spreport.sql
begin_snap:3-1에서부터 취득한 SNAP_ID
end_snap:3-1에서부터 취득한 SNAP_ID
report_name:report0001
4. 불필요 데이터의 삭제-------------------------------------------------------
4-1. SQL*Plus로 perfstat 유저에게 접속
4-2. 데이터 삭제의 스크립트, sppurge.sql를 실행
SQL> connect perfstat/perfstat
SQL> @$ORACLE_HOME/rdbms/admin/sppurge.sql
4-3. 삭제하는 snapshot의 범위를 지정하는 snap_id를 입력
예) Enter value for losnapid: 10
Enter value for hisnapid: 15
스냅샷 레벨 -------------------------------------------------------------------
LEVEL０ ：일반적인 성능 통계정보
LEVEL１ ：각 레벨 ＋ 어드바이스정보（R9.2.0～）
LEVEL５ ：LEVEL１＋ SQL문 통계정보
LEVEL６ ：LEVEL５＋ SQL상세 (실행계획 정보)（R9.0.1～）
LEVEL７ ：LEVEL６＋ 세그먼트 정보（R9.2.0～）
LEVEL１０：LEVEL７＋ 부모 Latch,자식 Latch정보
설정하는 스냅샷 레벨에 의해 수집되는 데이터 내용은 위와 같이 결정된다.
스냅샷 레벨이 높아질수록 보다 많은 데이터를 수집하지만 LEVEL10은 스냅샷 취득에 시간이 걸릴수 있다.
LEVEL７까지의 오버헤드는 미미하게 생기므로 테스트 전체의 성능에 영향을
미칠정도까지는 아니다.
인스톨시에 설정 되어있는 디폴트 LEVEL은 5이다.
LEVEL1 및 LEVEL７은 R9.2.0부터 새롭게 추가되었다.
각 버젼에서 제공하고 있는 LEVEL이외의 치를 설정하면 에러가 나므로 주의!!
8i → 3LEVEL：0,5,10
R9.0 → 4LEVEL：0,5,6,10
R9.2 → 6LEVEL：0,1,5,6,7,10
설정 파라메터-------------------------------------------------------------
보다 정확한 성능 분석을 위해서는 수집된 데이터에 시간정보가
추가되도록 TIMED_STATISTICS파라메터를 TRUE로 한다.(R9.0)
R9.2.0부터는 통계수집 레벨을 설정하는 신 파라메터
STATISTICS_LEVEL의 값을 확인한다. 설정치는 BASIC,TYPCIAL
ALL중에 하나이다. 디폴트는 TYPICAL로 스냅샷 레벨 1및 7 정보를
수집한다. 또한 이 값이 TYPICAL 혹은 ALL로 설정되어 있는 경우
TIMED_STATISTICS의 값은 자동적으로 TRUE로 된다.
출처 :
http://blog.naver.com/hirokorea/20026330097
1-1. 디렉토리 이동
cd $ORACLE_HOME/rdbms/admin
1-2.
sqlplus /nolog
1-3.
SQL>connect sys/change_on_install as sysdba
1-4.
SQL>@spcreate.sql
Password:perfstat
default tablespace:statspack
temporary tablespace:temp
2. snapshot 취득-------------------------------------------------------
주의＞스냅 레벨의 디폴트는 5
스냅 레벨의 조정은 아래의 snapshot·레벨의 설정 참조
SQL>conn perfstat/perfstat
SQL>execute statspack.snap
혹은 execute statspack.snap(i_snap_level =>5）
3. 리포트 출력-------------------------------------------------------
3-1 snapshot·리스트 확인
SQL>conn perfstat/perfstat
SQL>select SNAP_ID, SNAP_TIME,SNAP_LEVEL from STATS$SNAPSHOT;
3-2 리포트 출력
SQL>conn perfstat/perfstat
SQL>@spreport.sql
begin_snap:3-1에서부터 취득한 SNAP_ID
end_snap:3-1에서부터 취득한 SNAP_ID
report_name:report0001
4. 불필요 데이터의 삭제-------------------------------------------------------
4-1. SQL*Plus로 perfstat 유저에게 접속
4-2. 데이터 삭제의 스크립트, sppurge.sql를 실행
SQL> connect perfstat/perfstat
SQL> @$ORACLE_HOME/rdbms/admin/sppurge.sql
4-3. 삭제하는 snapshot의 범위를 지정하는 snap_id를 입력
예) Enter value for losnapid: 10
Enter value for hisnapid: 15
스냅샷 레벨 -------------------------------------------------------------------
LEVEL０ ：일반적인 성능 통계정보
LEVEL１ ：각 레벨 ＋ 어드바이스정보（R9.2.0～）
LEVEL５ ：LEVEL１＋ SQL문 통계정보
LEVEL６ ：LEVEL５＋ SQL상세 (실행계획 정보)（R9.0.1～）
LEVEL７ ：LEVEL６＋ 세그먼트 정보（R9.2.0～）
LEVEL１０：LEVEL７＋ 부모 Latch,자식 Latch정보
설정하는 스냅샷 레벨에 의해 수집되는 데이터 내용은 위와 같이 결정된다.
스냅샷 레벨이 높아질수록 보다 많은 데이터를 수집하지만 LEVEL10은 스냅샷 취득에 시간이 걸릴수 있다.
LEVEL７까지의 오버헤드는 미미하게 생기므로 테스트 전체의 성능에 영향을
미칠정도까지는 아니다.
인스톨시에 설정 되어있는 디폴트 LEVEL은 5이다.
LEVEL1 및 LEVEL７은 R9.2.0부터 새롭게 추가되었다.
각 버젼에서 제공하고 있는 LEVEL이외의 치를 설정하면 에러가 나므로 주의!!
8i → 3LEVEL：0,5,10
R9.0 → 4LEVEL：0,5,6,10
R9.2 → 6LEVEL：0,1,5,6,7,10
설정 파라메터-------------------------------------------------------------
보다 정확한 성능 분석을 위해서는 수집된 데이터에 시간정보가
추가되도록 TIMED_STATISTICS파라메터를 TRUE로 한다.(R9.0)
R9.2.0부터는 통계수집 레벨을 설정하는 신 파라메터
STATISTICS_LEVEL의 값을 확인한다. 설정치는 BASIC,TYPCIAL
ALL중에 하나이다. 디폴트는 TYPICAL로 스냅샷 레벨 1및 7 정보를
수집한다. 또한 이 값이 TYPICAL 혹은 ALL로 설정되어 있는 경우
TIMED_STATISTICS의 값은 자동적으로 TRUE로 된다.
출처 :
http://blog.naver.com/hirokorea/20026330097
2008년 2월 10일 일요일
DML Error Logging 기능의 활용방안
1.목적
-대용량 배치DML(INSERT, UPDATE,DELETE,MERGE) 실행시에 99% 가 완료되고 1% 남았을때 ORA-XXXX 에러에 의해서 전체건이 Rollback 되어버린다.
-대용량 배치는 시간에 매우예민한데 몇시간 걸리는 작업이 에러 몇건 때문에 전체가 Rollback 되면 손실이 크지않을수 없다.
-Oracle 10g R2 부터 정상적인 건들은 Commit 되고 에러난 건들만 나중에 따로 처리할 수 있는 문법이 추가 되었다.
-따라서 트랜잭션은 All Or Nothing 이란말은 옛말이 되어버렸다.
2.DML Error Logging 기능의 사용방법
1) Error Logging 테이블 생성
-타겟테이블의 Layout
CREATE TABLE raises
(emp_id NUMBER, sal NUMBER
CONSTRAINT check_sal CHECK(sal > 8000));
먼저 DBMS_ERRLOG 패키지의 EXECUTE 권한이 있어야 하고 Create Table 권한도 있어야 한다.
아래 예제를 실행시키면 Error Logging 테이블이 자동으로 생성된다.
예제)
SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(’raises’, ’errlog’);
위의 예제는 raises 테이블이 INSERT, UPDATE,DELETE 가 일어나는 Target 테이블이고
Error Logging 테이블명은 errlog 이다.
2) DML을 수행하면서 Error Logging 수행
아래 예제를 수행시키면서 Error Logging 테이블에서 나중에 작업을 식별해내기위해
‘my_bad’라는 식별자를 사용했음.
예제)
INSERT INTO raises
SELECT employee_id, salary*1.1 FROM employees
WHERE commission_pct > 0.2
LOG ERRORS INTO errlog (’my_bad’) REJECT LIMIT 10 ; -- 에러가 10건이 넘으면 rollback 됨
SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id, sal FROM errlog;
ORA_ERR_MESG$ ORA_ERR_TAG$ EMP_ID SAL
--------------------------- -------------------- ------ -------
ORA-02290: check constraint my_bad 161 7700
3)주의사항
-REJECT LIMIT 절은 UNLIMITED 로하는것이 권장사항임. -->무한대로 Error Logging 할수 있음.
-Parallel DML 수행시는 REJECT LIMIT 10 라고 설정했을경우 각 Process 마다 10건씩 Logging 함.
3. DML Error Logging 의 가능한 상황 및 제약사항
1) Error Logging 가능한 상황
-컬럼 길이가 너무클때
-컬럼제약사항 : (NOT NULL, unique, referential, and check constraints)
-트리거에서 에러가 날때
-데이터 타입 변환에 실패 했을때
-파티션 매핑에 실패했을경우
-MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)
2)제약사항 : 아래의 3가지경우는 Error Logging 이 안됨.
– Violated deferred constraints. (기본값이 NOT DEFERRABLE 이므로 이럴일은 거의 없음)
- Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.
(Parallel 이나 direct path Insert 시에 unique 인덱스의 Dup 에러는 체크하지 않음.
Update는 Parallel 이나 direct path 와 상관없이 unique 체크안함)
-You cannot track errors in the error logging table for LONG, LOB, or object type columns.
(LONG, LOB 타입은 에러체크를 하지않음)
4.Reference From Oracle Corp
1) Administrator's Guide10g Release 2 (10.2) :15-9
2) SQL Reference 10g Release 2 (10.2) : 18-62
5.참조사항 -Error Logging 테이블의 Layout
-대용량 배치DML(INSERT, UPDATE,DELETE,MERGE) 실행시에 99% 가 완료되고 1% 남았을때 ORA-XXXX 에러에 의해서 전체건이 Rollback 되어버린다.
-대용량 배치는 시간에 매우예민한데 몇시간 걸리는 작업이 에러 몇건 때문에 전체가 Rollback 되면 손실이 크지않을수 없다.
-Oracle 10g R2 부터 정상적인 건들은 Commit 되고 에러난 건들만 나중에 따로 처리할 수 있는 문법이 추가 되었다.
-따라서 트랜잭션은 All Or Nothing 이란말은 옛말이 되어버렸다.
2.DML Error Logging 기능의 사용방법
1) Error Logging 테이블 생성
-타겟테이블의 Layout
CREATE TABLE raises
(emp_id NUMBER, sal NUMBER
CONSTRAINT check_sal CHECK(sal > 8000));
먼저 DBMS_ERRLOG 패키지의 EXECUTE 권한이 있어야 하고 Create Table 권한도 있어야 한다.
아래 예제를 실행시키면 Error Logging 테이블이 자동으로 생성된다.
예제)
SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG(’raises’, ’errlog’);
위의 예제는 raises 테이블이 INSERT, UPDATE,DELETE 가 일어나는 Target 테이블이고
Error Logging 테이블명은 errlog 이다.
2) DML을 수행하면서 Error Logging 수행
아래 예제를 수행시키면서 Error Logging 테이블에서 나중에 작업을 식별해내기위해
‘my_bad’라는 식별자를 사용했음.
예제)
INSERT INTO raises
SELECT employee_id, salary*1.1 FROM employees
WHERE commission_pct > 0.2
LOG ERRORS INTO errlog (’my_bad’) REJECT LIMIT 10 ; -- 에러가 10건이 넘으면 rollback 됨
SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id, sal FROM errlog;
ORA_ERR_MESG$ ORA_ERR_TAG$ EMP_ID SAL
--------------------------- -------------------- ------ -------
ORA-02290: check constraint my_bad 161 7700
3)주의사항
-REJECT LIMIT 절은 UNLIMITED 로하는것이 권장사항임. -->무한대로 Error Logging 할수 있음.
-Parallel DML 수행시는 REJECT LIMIT 10 라고 설정했을경우 각 Process 마다 10건씩 Logging 함.
3. DML Error Logging 의 가능한 상황 및 제약사항
1) Error Logging 가능한 상황
-컬럼 길이가 너무클때
-컬럼제약사항 : (NOT NULL, unique, referential, and check constraints)
-트리거에서 에러가 날때
-데이터 타입 변환에 실패 했을때
-파티션 매핑에 실패했을경우
-MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)
2)제약사항 : 아래의 3가지경우는 Error Logging 이 안됨.
– Violated deferred constraints. (기본값이 NOT DEFERRABLE 이므로 이럴일은 거의 없음)
- Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.
(Parallel 이나 direct path Insert 시에 unique 인덱스의 Dup 에러는 체크하지 않음.
Update는 Parallel 이나 direct path 와 상관없이 unique 체크안함)
-You cannot track errors in the error logging table for LONG, LOB, or object type columns.
(LONG, LOB 타입은 에러체크를 하지않음)
4.Reference From Oracle Corp
1) Administrator's Guide10g Release 2 (10.2) :15-9
2) SQL Reference 10g Release 2 (10.2) : 18-62
5.참조사항 -Error Logging 테이블의 Layout
2008년 2월 9일 토요일
STATSPACK-개념
1.개요
1)Staspack은 oracle database에 대한 부하 및 resource 사용량의 trend 분석이나 성능 문제분석을 위하여 사용되는 tool로 과거 (BSTAT, ESTAT) 이 제공하던 기능을 수정 보완하여 oracle 8.1.6부터 제공되고 있는 tool 이다.
2) StatsPack 은 Oracle database 내에서 서버 성능에 관련된 data를capture하고 report를 생성하는 script들로 구성되어져 있다.
3) 한 시점의 Oracle database의 성능 data들이 ‘snapshot’ 으로관리된다.
4) Statspack data는 db에 저장된다.
5) Statspack report는 두 시점의 snasphot들로 부터 얻어진다.
-두시점의 snasphot은 피크타임의 10~20 분을 CAPTURE 하는것이 최적이다.
-예를 들면 온라인 쇼핑몰이 점심시간에 사용자가 폭주할경우 경우 낮12:30 에서 12~50 분의
데이터를 CAPTURE하여야 한다.
2.특징
1). Statspack은 Oracle DB내에서 특정 시간대의 데이터베이스에 대한 성능과 관련 데이터를 수집하여database에 저장하여 두고 이로 부터 성능 분석 report를 생성해 내는 script들로 구성이 되어 있습니다.
2). 한 시점의 성능 data들은 snapshot이라고 불려 집니다.
3). 이 Statspack data는 database 내에 저장이 됩니다.
4). Statspack report는 두 시점의 snapshots들로 부터 얻어집니다.
3.산출물
STATSPACK 리포트에는 크게 5가지의 보고서가 생성된다.
1)Summary Page :
-리포트의 가장 앞부분은 Summary page라 하여 튜닝의 대상을 좁히기 위해 유효한 내용,통계치가 집약되어 있다.
처음에 데이터 베이스 초기정보,개시와종료 스냅샷 정보 그리고 초기화 파라메터 파일에 지정되어 있는 캐쉬 사이즈에 대해 파라미터치를 표시한다.
2)로드 프로파일 :
-다른 스냅샷간에서 작성된 2개이상의 리포트를 사용하여 업무량을 비교할때 유효한 세그먼트이다. 단 시스템이 완전히 틀린 업무량을 처리하는 경우는 그들 리포트의 비교는 유효하지 않다.
-매초 통계정보(Per Second)부터는 두개의 리포트를 비교에 의해 입력부터 출력까지의 변수를 알수 있다. 예를들어 Redo size、Block changes、%Blocks changed per read 이 현저하게 증가한 경우라 한다면 insert/update/delete처리가 보다 많이 행해졌다는 것이 된다.
-동일하게 2개의 리포트에 트랜잭션마다의 통계정보 (Per Transacion)을 비교하는것에 의해 어플리케이션 특성의 현화를 식별하는 것이 가능!!
3)인스턴스 효율 :
-Oracle의 주요 파라메터를 조정한다. 파라메터에 대해서는 db_cache_size ,라이브러리 캐쉬 또는 SQL분석시간에 관해서는 shared_pool_size,Redo에 관해서는 log_buffer 등 각각의 항목에 관련 하는 파라미터를 재검토 해야합니다.여기서 현저히 낮은 치가 나오거나 파라메터를 변경해도 개선이 안되거하 하는 경우에는 리포트후에 출력된 관련 세그컨트를 조사하고 원인을 추구해간다.
4)Top 5 Wait Events :
-가장 중목할 섹션이다 여기에는 세션의 대기시간이 가장 긴 이벤트가 표시된다. 여기에 표시되는 각 이벤트의 설명 （「Oracle9i 데이터 베이스 성능 가이드 및 리퍼런스 」참조）를 보고 리포트의 다른 부분부터는 그것들에 관계하는 섹션만을 문제점으로 초점을 맞춘다. 효율보다 튜닝을 실행 하는것이 가능.
5)악성 SQL문에 대한 통계정보:
튜닝의 대상이되는 SQL 문을 식별하는 보고서임.
아래는 악성의 SQL 기준이 되는 지표임.
- 엑세스된 버퍼수
- 디스크의 엑세스 횟수
- 실행횟수
- 공유메모리량
- SQL문의 종류수
4.참조문헌및 싸이트
http://www.oracle.com/wocportal/page/wocprod/ver-1/ocom/global/kr/support/download/Tech-iSeminar_StatsPack.pdf
http://blog.naver.com/hirokorea/20026330097
1)Staspack은 oracle database에 대한 부하 및 resource 사용량의 trend 분석이나 성능 문제분석을 위하여 사용되는 tool로 과거 (BSTAT, ESTAT) 이 제공하던 기능을 수정 보완하여 oracle 8.1.6부터 제공되고 있는 tool 이다.
2) StatsPack 은 Oracle database 내에서 서버 성능에 관련된 data를capture하고 report를 생성하는 script들로 구성되어져 있다.
3) 한 시점의 Oracle database의 성능 data들이 ‘snapshot’ 으로관리된다.
4) Statspack data는 db에 저장된다.
5) Statspack report는 두 시점의 snasphot들로 부터 얻어진다.
-두시점의 snasphot은 피크타임의 10~20 분을 CAPTURE 하는것이 최적이다.
-예를 들면 온라인 쇼핑몰이 점심시간에 사용자가 폭주할경우 경우 낮12:30 에서 12~50 분의
데이터를 CAPTURE하여야 한다.
2.특징
1). Statspack은 Oracle DB내에서 특정 시간대의 데이터베이스에 대한 성능과 관련 데이터를 수집하여database에 저장하여 두고 이로 부터 성능 분석 report를 생성해 내는 script들로 구성이 되어 있습니다.
2). 한 시점의 성능 data들은 snapshot이라고 불려 집니다.
3). 이 Statspack data는 database 내에 저장이 됩니다.
4). Statspack report는 두 시점의 snapshots들로 부터 얻어집니다.
3.산출물
STATSPACK 리포트에는 크게 5가지의 보고서가 생성된다.
1)Summary Page :
-리포트의 가장 앞부분은 Summary page라 하여 튜닝의 대상을 좁히기 위해 유효한 내용,통계치가 집약되어 있다.
처음에 데이터 베이스 초기정보,개시와종료 스냅샷 정보 그리고 초기화 파라메터 파일에 지정되어 있는 캐쉬 사이즈에 대해 파라미터치를 표시한다.
2)로드 프로파일 :
-다른 스냅샷간에서 작성된 2개이상의 리포트를 사용하여 업무량을 비교할때 유효한 세그먼트이다. 단 시스템이 완전히 틀린 업무량을 처리하는 경우는 그들 리포트의 비교는 유효하지 않다.
-매초 통계정보(Per Second)부터는 두개의 리포트를 비교에 의해 입력부터 출력까지의 변수를 알수 있다. 예를들어 Redo size、Block changes、%Blocks changed per read 이 현저하게 증가한 경우라 한다면 insert/update/delete처리가 보다 많이 행해졌다는 것이 된다.
-동일하게 2개의 리포트에 트랜잭션마다의 통계정보 (Per Transacion)을 비교하는것에 의해 어플리케이션 특성의 현화를 식별하는 것이 가능!!
3)인스턴스 효율 :
-Oracle의 주요 파라메터를 조정한다. 파라메터에 대해서는 db_cache_size ,라이브러리 캐쉬 또는 SQL분석시간에 관해서는 shared_pool_size,Redo에 관해서는 log_buffer 등 각각의 항목에 관련 하는 파라미터를 재검토 해야합니다.여기서 현저히 낮은 치가 나오거나 파라메터를 변경해도 개선이 안되거하 하는 경우에는 리포트후에 출력된 관련 세그컨트를 조사하고 원인을 추구해간다.
4)Top 5 Wait Events :
-가장 중목할 섹션이다 여기에는 세션의 대기시간이 가장 긴 이벤트가 표시된다. 여기에 표시되는 각 이벤트의 설명 （「Oracle9i 데이터 베이스 성능 가이드 및 리퍼런스 」참조）를 보고 리포트의 다른 부분부터는 그것들에 관계하는 섹션만을 문제점으로 초점을 맞춘다. 효율보다 튜닝을 실행 하는것이 가능.
5)악성 SQL문에 대한 통계정보:
튜닝의 대상이되는 SQL 문을 식별하는 보고서임.
아래는 악성의 SQL 기준이 되는 지표임.
- 엑세스된 버퍼수
- 디스크의 엑세스 횟수
- 실행횟수
- 공유메모리량
- SQL문의 종류수
4.참조문헌및 싸이트
http://www.oracle.com/wocportal/page/wocprod/ver-1/ocom/global/kr/support/download/Tech-iSeminar_StatsPack.pdf
http://blog.naver.com/hirokorea/20026330097
2008년 2월 4일 월요일
Data Access Pattern - Index Combine
1.발생규칙
1) Bit Map 인덱스가 한테이블에 2개 이상일때 where 조건에
두인덱스에 해당하는 컬럼의 조건을 다사용할때 발생함.
2)9.2 밑의 버젼에서는 Index Merge Plan 이 발생함.
3)꼭 Bit Map 인덱스가 아니라도 Index Combine 이 발생할수 있음.
이경우에는 Bit Map Conversion 이 추가로 발생함.
2.엑세스 형태
-첫번째 인덱스의 와 두번째 인덱스의를 이용하여 두집합간에
AND, OR, MINUS, MERGE 연산을 하여 데이터를 엑세스한다.
3.적용범위
1) DW 나 대용량 배치인경우 적용
2)OLTP 인 경우는 인덱스 조건이 똑똑한경우 적용.
4.Plan 및 SQL
CREATE TABLE t1
(c1 NUMBER, c2 NUMBER);
CREATE BITMAP INDEX i1 ON t1 (c1);
CREATE BITMAP INDEX i2 ON t1 (c2);
1) BIT MAP OR
SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2
FROM t1
WHERE c1 = 0 OR c2 = 0;
SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1‘
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP INDEX (SINGLE VALUE) OF 'I1‘
5 3 BITMAP INDEX (SINGLE VALUE) OF 'I2'
2) BIT MAP AND
SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2
FROM t1
WHERE c1 = 0 AND c2 = 0;
SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1‘
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP AND
4 3 BITMAP INDEX (SINGLE VALUE) OF 'I1‘
5 3 BITMAP INDEX (SINGLE VALUE) OF 'I2'
3) BIT MAP MINUS
SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2
FROM t1
WHERE c1 = 0 AND NOT c2 = 0;
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1‘
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP MINUS
4 3 BITMAP MINUS
5 4 BITMAP INDEX (SINGLE VALUE) OF 'I1‘
6 4 BITMAP INDEX (SINGLE VALUE) OF 'I2‘ --> c2 IS NULL 에 대한 ACCESS
7 3 BITMAP INDEX (SINGLE VALUE) OF 'I2' --> c2 = 0 에 대한 ACCESS
# 위경우에 c2 IS NULL 항목이 NOT NULL 이면 6번 ACCESS 가 발생하지 않는다.
4) BIT MAP MERGE
SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2
FROM t1
WHERE c1 > 0 AND c2 = 0;
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1'
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP AND
4 3 BITMAP INDEX (SINGLE VALUE) OF 'I2‘
5 3 BITMAP MERGE
6 5 BITMAP INDEX (RANGE SCAN) OF 'I1'
1) Bit Map 인덱스가 한테이블에 2개 이상일때 where 조건에
두인덱스에 해당하는 컬럼의 조건을 다사용할때 발생함.
2)9.2 밑의 버젼에서는 Index Merge Plan 이 발생함.
3)꼭 Bit Map 인덱스가 아니라도 Index Combine 이 발생할수 있음.
이경우에는 Bit Map Conversion 이 추가로 발생함.
2.엑세스 형태
-첫번째 인덱스의 와 두번째 인덱스의를 이용하여 두집합간에
AND, OR, MINUS, MERGE 연산을 하여 데이터를 엑세스한다.
3.적용범위
1) DW 나 대용량 배치인경우 적용
2)OLTP 인 경우는 인덱스 조건이 똑똑한경우 적용.
4.Plan 및 SQL
CREATE TABLE t1
(c1 NUMBER, c2 NUMBER);
CREATE BITMAP INDEX i1 ON t1 (c1);
CREATE BITMAP INDEX i2 ON t1 (c2);
1) BIT MAP OR
SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2
FROM t1
WHERE c1 = 0 OR c2 = 0;
SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1‘
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP INDEX (SINGLE VALUE) OF 'I1‘
5 3 BITMAP INDEX (SINGLE VALUE) OF 'I2'
2) BIT MAP AND
SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2
FROM t1
WHERE c1 = 0 AND c2 = 0;
SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1‘
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP AND
4 3 BITMAP INDEX (SINGLE VALUE) OF 'I1‘
5 3 BITMAP INDEX (SINGLE VALUE) OF 'I2'
3) BIT MAP MINUS
SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2
FROM t1
WHERE c1 = 0 AND NOT c2 = 0;
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1‘
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP MINUS
4 3 BITMAP MINUS
5 4 BITMAP INDEX (SINGLE VALUE) OF 'I1‘
6 4 BITMAP INDEX (SINGLE VALUE) OF 'I2‘ --> c2 IS NULL 에 대한 ACCESS
7 3 BITMAP INDEX (SINGLE VALUE) OF 'I2' --> c2 = 0 에 대한 ACCESS
# 위경우에 c2 IS NULL 항목이 NOT NULL 이면 6번 ACCESS 가 발생하지 않는다.
4) BIT MAP MERGE
SELECT /*+ INDEX_COMBINE (t1 i1 i2) */ c1,c2
FROM t1
WHERE c1 > 0 AND c2 = 0;
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1'
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP AND
4 3 BITMAP INDEX (SINGLE VALUE) OF 'I2‘
5 3 BITMAP MERGE
6 5 BITMAP INDEX (RANGE SCAN) OF 'I1'
2008년 2월 3일 일요일
Data Access Pattern - Inlist Iterator
1.발생규칙
-OR 조건이나 IN 조건에 상수(변수)가 들어오면 발생함
2.Access 방식
-Concatenation 과 비슷하나 Union 으로 풀리지않고 반복수행한다.
-값이 상수나 변수로 공급될때만 발생한다.
3.적용범위
-Concatenation과 동일하나 Inlist Iterator 가 유리한경우가 많음
-OLTP 에 적용하고 대용량배치 SQL 에서는 피한다.
4.Hint
-특별한 힌트는 없지만 no_expand 힌트를 쓰면 Concatenation 으로 풀리지 않고
Inlist Iterator 로 풀릴가능성이 높다.
SELECT /*+ no_expand */
empno, ename, sal
FROM emp
WHERE empno IN (7501,7502,7503) ;
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID EMP
INDEX (RANGE SCAN) PK_EMP
-OR 조건이나 IN 조건에 상수(변수)가 들어오면 발생함
2.Access 방식
-Concatenation 과 비슷하나 Union 으로 풀리지않고 반복수행한다.
-값이 상수나 변수로 공급될때만 발생한다.
3.적용범위
-Concatenation과 동일하나 Inlist Iterator 가 유리한경우가 많음
-OLTP 에 적용하고 대용량배치 SQL 에서는 피한다.
4.Hint
-특별한 힌트는 없지만 no_expand 힌트를 쓰면 Concatenation 으로 풀리지 않고
Inlist Iterator 로 풀릴가능성이 높다.
SELECT /*+ no_expand */
empno, ename, sal
FROM emp
WHERE empno IN (7501,7502,7503) ;
SELECT STATEMENT
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID EMP
INDEX (RANGE SCAN) PK_EMP
Data Access Pattern - Concatenation
1.발생규칙
OR조건이나 IN 상수조건이 있는경우에 걸리는 두개의 조건을 개별적으로 검색하고, 나머지 조건도 전부 적용해서 검색 한 후, 나중에 이 결과를 합치게 된다.이 내용이 플랜에는 CONCATENATION으로 표현 된다.
2.Access 방식
SELECT /*+USE_CONCAT*/ columns
FROM tables
WHERE (조건1 OR 조건2)
AND 조건3;
위처럼 조건절이 되는경우 아래처럼 실행계획이 풀린다.
SELECT columnsFROM tables
WHERE 조건1
AND 조건3
UNION ALL
SELECT columns
FROM tables
WHERE 조건2
AND 조건3;
3.적용범위
-인덱스 조건이 똑똑하여 빠른 OUTPUT 이 보장될경우(OLTP)
-ORDER BY절이 있을 경우 결과에 대한 정렬 처리가 추가로 필요하게 되므로, 결과값이 많을 경우에는 불리하게 작용할 수 있다.
-대용량 배치쿼리에서는 피한다.
4.Hint
/*+USE_CONCAT*/
5.PLAN
SELECT /*+USE_CONCAT*/ columns
FROM tables
WHERE (조건1 OR 조건2)
AND 조건3;
CONCATENATION
TABLE ACCESS (BY ROWID) OF ‘TEST_TAB1’
INDEX (RANGE SCAN) OF ‘IDX1’
TABLE ACCESS (BY ROWID) OF ‘TEST_TAB1’
INDEX (RANGE SCAN) OF ‘IDX1’
Data Access Pattern - Index Skip Scan
1.발생규칙 :
-결합인덱스시 처음이나 중간의 조건이 빠졌을경우
-결합 인덱스에서 첫 번째 컬럼이 사용되지 않으면 무조건 인덱스 스캔이 불가능 하였다. 하지만 9i부터 인덱스 스킵 스캔으로 가능해짐.
2.Access 방식
-Index Skip Scan의 원리
① 어떤 테이블의 인덱스가 sal_typ(매출유형), item_cd(상품코드), sal_dt(매출일자)로 구성되어 있다고 가정했을 경우
② 쿼리의 조건에서 item_cd와 sal_dt 만 사용 되었다.
③ sal_typ의 값이 D(내수),E(수출),L(로컬) 이렇게 세 종류만 있다고 가정을 하였을 경우. Index Skip Scan을 적용하면..
④ sal_typ IN (‘D’,’E’,’L’) 의 조건을 추가한 것과 동일한 효과를 얻을 수 있다.
- 결국 생락 된 첫 번째 컬럼의 값이 조건 절에 자동으로 추가되는 것과 유사한 효과가 난다.
- WHERE sal_typ = ‘D’ and item_cd = … UNION ALL
WHERE sal_typ = ‘E’ and item_cd = … UNION ALL
WHERE sal_typ = ‘L’ and item_cd = … → 여기서 ‘D’,’E’,’L’을 논리적 서브 인덱스라고 한다.
3.적용범위
- Index Skip Scan은 서브 인덱스의 종류가 많지 않고, 뒤에 오는 컬럼의 종류가 많을 때 가장 좋은 결과를 얻을 수 있다.
4.Hint
SELECT /*+ INDEX_SS(miod_div miod_div_idx) */ i_bugt_cd
FROM miod_div
WHERE d_io = '20010403'
AND o_io = 6
AND i_io = '20'
AND i_io_div = '6200'
5.Plan
SELECT STATEMENT CHOOSE-Cost : 4519
TABLE ACCESS BY INDEX ROWID MATS.MIOD_DIV(1)
INDEX SKIP SCAN MATS.MIOD_DIV_IDX(NU) (I_MATR,D_IO,O_IO,I_IO,I_IO_DIV)
2008년 1월 31일 목요일
Data Access Pattern - Rowid
1.발생규칙
•Rowid가 조건으로 공급된 경우
•인덱스를 사용하여 Table 을 access 한경우
2.Access 방식
•Rowid를 이용해서 특정 Block의 특정 Row를 찾아간다
•가장 빠른 Access 방식이다.
3.적용범위
•max /min 일자를 찾아서 그일자에 해당하는 값을 select 할때(self join 시 사용)
4.Hint
select *
from emp
where rowid = :v_rid
-------------------------------------------------------
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (BY [INDEX] ROWID) OF 'EMP'
•Rowid가 조건으로 공급된 경우
•인덱스를 사용하여 Table 을 access 한경우
2.Access 방식
•Rowid를 이용해서 특정 Block의 특정 Row를 찾아간다
•가장 빠른 Access 방식이다.
3.적용범위
•max /min 일자를 찾아서 그일자에 해당하는 값을 select 할때(self join 시 사용)
4.Hint
select *
from emp
where rowid = :v_rid
-------------------------------------------------------
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (BY [INDEX] ROWID) OF 'EMP'
Data Access Pattern - Index Full Scan
1.발생규칙
•Optimizer가 Full Table Scan하고 Sort하는 것 보다는 Index Full Scan해 Sort작업을 따로
수행하지 않는 것이 유리하다고 판단한 경우
2.Access 방식
•해당 인덱스의 모든 Block을 한번에 한 Block씩 순차적으로 읽어 내려간다.(Single Block I/O)
3.적용범위
•건수가 많더라도 1건만 scan 하고 끝낼수 있을경우
•부분범위처리가 가능한경우
4.주의사항
•muti-block-I/O 가 아님(한BLOCK 씩만 읽을수 있음)
INDEX FAST FULL SCAN 과 다르므로 주의한다.(배치SQL 에서는 피한다.)
5.HINT
/*+ INDEX(테이블명 인덱스명) */
select /*+ index(a emp_idx05) */
empno, ename, job, hiredate
from emp a
where job = 'SALESMAN'
-------------------------------------------------------
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (BY [INDEX] ROWID) OF 'EMP'
INDEX (FULL SCAN) OF 'EMP_IDX05' (NON-UNIQUE)
•Optimizer가 Full Table Scan하고 Sort하는 것 보다는 Index Full Scan해 Sort작업을 따로
수행하지 않는 것이 유리하다고 판단한 경우
2.Access 방식
•해당 인덱스의 모든 Block을 한번에 한 Block씩 순차적으로 읽어 내려간다.(Single Block I/O)
3.적용범위
•건수가 많더라도 1건만 scan 하고 끝낼수 있을경우
•부분범위처리가 가능한경우
4.주의사항
•muti-block-I/O 가 아님(한BLOCK 씩만 읽을수 있음)
INDEX FAST FULL SCAN 과 다르므로 주의한다.(배치SQL 에서는 피한다.)
5.HINT
/*+ INDEX(테이블명 인덱스명) */
select /*+ index(a emp_idx05) */
empno, ename, job, hiredate
from emp a
where job = 'SALESMAN'
-------------------------------------------------------
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (BY [INDEX] ROWID) OF 'EMP'
INDEX (FULL SCAN) OF 'EMP_IDX05' (NON-UNIQUE)
2008년 1월 29일 화요일
Data Access Pattern - INDEX RANGE SCAN
1.발생규칙
•Non-Unique Index를 Access하는 경우
•Unique Index를 구성하고 있는 컬럼 중 일부 컬럼에만 값이 공급된 경우
•Unique Index에 Range 조건(like, between, >, <, >=, <=)으로 값이 공급되는 경우
2.Access 방식
•해당 조건을 만족하는 범위 + 아닌 값 하나(1PlusScan)를 읽게 된다.
•Range 조건이 들어온 경우 Index구성 순서상 이후에 있는 컬럼에 공급된 조건들은 작업범위를 줄이는데 작용하지 못한다. 예외상황:9i 이후부터 index skip scan
3.적용범위
•10만건 이하의 건수를 access 할때
•10만건 이상이라도 부분범위처리가 가능할때
•10만건 이상이라도 인덱스만 scan 하고 table access 가없을때
•주로 OLTP
4.HINT /*+ INDEX(테이블명 혹은 ALIAS 인덱스명) */
•실행계획
select * from emp where mgr > 7839;
-------------------------------------------------------
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (BY [INDEX] ROWID) OF 'T_EMP'
INDEX (RANGE SCAN) OF 'T_EMP_IDX01' (NON-UNIQUE)
•Non-Unique Index를 Access하는 경우
•Unique Index를 구성하고 있는 컬럼 중 일부 컬럼에만 값이 공급된 경우
•Unique Index에 Range 조건(like, between, >, <, >=, <=)으로 값이 공급되는 경우
2.Access 방식
•해당 조건을 만족하는 범위 + 아닌 값 하나(1PlusScan)를 읽게 된다.
•Range 조건이 들어온 경우 Index구성 순서상 이후에 있는 컬럼에 공급된 조건들은 작업범위를 줄이는데 작용하지 못한다. 예외상황:9i 이후부터 index skip scan
3.적용범위
•10만건 이하의 건수를 access 할때
•10만건 이상이라도 부분범위처리가 가능할때
•10만건 이상이라도 인덱스만 scan 하고 table access 가없을때
•주로 OLTP
4.HINT /*+ INDEX(테이블명 혹은 ALIAS 인덱스명) */
•실행계획
select * from emp where mgr > 7839;
-------------------------------------------------------
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (BY [INDEX] ROWID) OF 'T_EMP'
INDEX (RANGE SCAN) OF 'T_EMP_IDX01' (NON-UNIQUE)
Data Access Pattern - Index Unique Scan
1.발생규칙
•Unique Index를 구성하고 있는 모든 Key값에 대해서 Equal(=) 로 조건이 공급된 경우 발생한다
2.Access 방식
•해당 조건을 만족하는 값 하나만 읽는다
3.적용범위
•OLTP 의 화면에서 적용하느것이 최적임.
•한건만 읽어야 할경우.
4.Hint
- /*+ index(테이블명 또는 테이블별칭) */
-plan 상에서의 index unique scan
select *
from emp
where empno = 7790 ;
-------------------------------------------------------
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (BY [INDEX] ROWID) OF 'EMP'
INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
•Unique Index를 구성하고 있는 모든 Key값에 대해서 Equal(=) 로 조건이 공급된 경우 발생한다
2.Access 방식
•해당 조건을 만족하는 값 하나만 읽는다
3.적용범위
•OLTP 의 화면에서 적용하느것이 최적임.
•한건만 읽어야 할경우.
4.Hint
- /*+ index(테이블명 또는 테이블별칭) */
-plan 상에서의 index unique scan
select *
from emp
where empno = 7790 ;
-------------------------------------------------------
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (BY [INDEX] ROWID) OF 'EMP'
INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
2008년 1월 28일 월요일
Data Access Pattern - Index Fast Full Scan
>발생규칙
•Where절이나 Select절에 사용된 컬럼이 모두 하나의 인덱스에 구성된 컬럼인 경우
•결합Index의 경우 최소한 한 Column이 NOT Null로 지정되어 있어야 한다.
>적용범위
•FTS(FULL TABLE SCAN)과 같음.
>Hint
-/*+ index_ffs(테이블명(혹은 별칭) 인덱스명) */
>Plan 상에서의 Index Fast Full Scan
SELECT /*+ index_ffs(a emp_job_idx) */
empno, ename, job
FROM emp a
WHERE job = 'SALESMAN'
-------------------------------------------------------
SELECT STATEMENT GOAL: CHOOSE
INDEX (FAST FULL SCAN) OF 'EMP_JOB_IDX'
•Where절이나 Select절에 사용된 컬럼이 모두 하나의 인덱스에 구성된 컬럼인 경우
•결합Index의 경우 최소한 한 Column이 NOT Null로 지정되어 있어야 한다.
>Access 방식
•인덱스 Leaf Block을 한번에 DB_FILE_MULTIBLOCK_READ_COUNT에서 정한 크기씩 끝까지 읽어 내려가며 결과 값의 Sort가 보장되지 않는다
•Parallel로 수행 가능하다
•Full Table Scan보다 읽어야 할 Block의 수가 적어 유리하다.
>적용범위
•FTS(FULL TABLE SCAN)과 같음.
>Hint
-/*+ index_ffs(테이블명(혹은 별칭) 인덱스명) */
>Plan 상에서의 Index Fast Full Scan
SELECT /*+ index_ffs(a emp_job_idx) */
empno, ename, job
FROM emp a
WHERE job = 'SALESMAN'
-------------------------------------------------------
SELECT STATEMENT GOAL: CHOOSE
INDEX (FAST FULL SCAN) OF 'EMP_JOB_IDX'
피드 구독하기:
글 (Atom)
