Extreme DataBase Lab: 2월 2008

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)

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

2008년 2월 22일 금요일

Outer Join 의 재조명

Outer Join 의 Lateral Views 로의 변환

목적 : 많은 사람들이 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 SQL 2003 의 'lateral view'를 지원하지 못하고 내부적으로만 사용하고 있다.

버그인가 아니면 정확한 결과인가?

아래 예제를 보고 헷갈릴수밖에 없는 이유를 설명한다.

데이터는 다음과 같다.

1) EMP 테이블

SELECT EMPNO, ENAME, DEPTNO
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 개념을 적용하여 쿼리를 작성하면 된다.

그렇다면 아래와 같은 쿼리는 결과가 어떻게 나올것인가

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/

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

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 항목에서는 튜닝방법대로 했을경우 옵티마이져가 어떻게 반응하는지를 보여준다.
위의 예제에서는 통계정보를 생성하면 실행계획이 바뀐다는점을 알려주고 있다.
신기하지 않은가?

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

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

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

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'

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

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’을 논리적 서브 인덱스라고 한다.