'Oracle' 카테고리의 글 목록 (4 Page)

Science of DataBase 1주년

Oracle/News 2009. 7. 2. 19:30

Science of DataBase가 운영을 시작한지 1년이 지났습니다.
정확히 2008년 6월 부터 시작했으니 이제 만으로 1년이 되었습니다.
블로그를 만들 당시의 의도는 데이터 모델링, 데이터 품질, 데이터 모델 패턴, Performance Tuning 등을 모두 다루려고 하였으나 어쩌다 보니 Performance Tuning 위주로 나가게 되었습니다. 프로젝트에서 이슈된 사항들 위주로 하다보니 그렇게 된것인데 추후에 모델링 프로젝트시 이슈가 생기면 데이터 모델링, 데이터 모델 패턴등도 올리도록 하겠습니다.

Science of DataBase를 방문해주시는 분들 중에는 이방면에서 유명한 분들도 많이 계십니다.

Optimizing Oracle Optimizer 의 저자 조동욱님

Transaction Internals in Oracle 10gR2 의 저자 김시연님

오라클 성능 고도화 원리와 해법 I 의 저자 조시형님

비용기반의 오라클 원리 의 역자 김정삼님

이분들은 아주 실력이 뛰어나고 성품 또한 아주 훌륭한 분들 이어서 본받을점이 많이 있습니다.
이분들과 때때로 토론과 논쟁을 하는 과정에서 오류를 발견하고 수정하여 블로그가 많이 발전한것 같습니다.
사실 개인적인 바램은 이런 고수 분들이 회사 차원을 떠나 사심없이 모여서 한국의 OAKTABLE NETWORK 를 만드는 것입니다. 이것이 가능 할지는 모르겠습니다. 물론 어렵겠지요. 한분 한분의 허락을 받아야 되기 때문 입니다.

Science of DataBase는 개인 블로그 입니다.
저는 개인공간에 회사 차원의 메세지를 올리는것을 무척 싫어합니다. 앞으로도 상업적인 메세지는 올리지 않도록 노력 하겠습니다. 하지만 지금 집필중인 튜닝 책은 예외로 할 작정입니다. 회사차원이라기 보다는 개인차원의 노력이 많이 필요하기 때문에 책이 나오면 블로그에 소개를 올릴려고 합니다.

사실 요즘 블로그 관리가 힘들어 졌습니다.
맡은 프로젝트 + 집필작업 + 블로그 관리 + 개인적인 Study 의 4중고(?)에 시달리고 있습니다. 하지만 방문해주시는 분들을 위해서라도 꾸준히 주평균 1회 정도 글을 올릴려고 노력 하고 있습니다. Science of DataBase는 다은 블로그와는 달리 화려하지도 않고 방문자수도 많지 않습니다. 하지만 방문해주신 모든 분들이 DataBase를 사랑하시는 분들입니다. 방문 해주시는 한분 한분이 저에게는 소중하신분들 이십니다.

작년 6월달에 하루 평균 방문자수 3명으로 시작하여 요즈음은 평일 평균 120 명정도로 꾸준히 방문자수가 늘고 있습니다. 블로그는 독자들과의 소통이 중요한데 혹시 Science of DataBase 에게 바라는 점, 아쉬운점, 개선사항 등이 있는 분은 주저하지 마시고 코멘트를 남겨주시기 바랍니다.
독자와 함께 만들어 가는 Blog야 말로 진정한 의사소통의 장소가 되지 않을까요?

앞으로 더 좋은글로 보답 하겠습니다.
감사합니다.

오동규 배상

'Oracle > News' 카테고리의 다른 글

볼륨매니저는 역사 속으로 (12)	2009.09.30
오라클은 몇년이나 갈까? (4)	2009.07.31
오라클 공룡기업으로 거듭나는가? (0)	2009.04.21

Posted by extremedb

,

스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기.

Oracle/SQL Pattern 2009. 6. 29. 11:33

부분범위 처리 혹은 페이징 처리시 탁월한 힘을 발휘하는것이 스칼라 서브쿼리이다. 여러가지 테스트 결과 스칼라 서브쿼리는 부분범위처리시 가장 좋다는 Nested Loop 조인보다 성능상 좀더 유리하다. 과다호출로 몸살을 앟고 있는 사용자 함수를 스칼라 서브쿼리로만 바꾸어도 튜닝이 된다. 또한 View Merging 이나 JPPD(Join Push Predicate Down) 등의 성공여부를 고민할 필요도 없으므로 특히 개발자 들에게 인기가 많은것 또한 사실이다.

모든 기능은 용도에 맞게 사용해야 한다.
이렇게 많이 활용되고 있는 스칼라 서브쿼리도 한계는 있다. Multi Column, Multi Row 을 RETURN 하는 것은 만들수가 없다는 것이다. 물론 Pipilined Funtion 으로 해결이 가능하지만 모든경우에 이것을 만들기란 여간 힘든것이 아니다. 바로 이럴때 사용가능한것이 Cast + MultiSet 함수를 이용한 스칼라 서브쿼리이다. MultiSet 함수는 Oracle 8.1.7 부터 사용이 가능하다. 필자가 이기능을 소개하는 이유는 실무에서 이런기능을 꼭 써야하는 경우임에도 Pipelined 함수등을 사용하는것을 많이 보아서이다. Pipelined 등의 함수를 사용해야 할때는 자주사용하는 SQL 이 아주 길어서 Network 의 부하가 생길때 혹은 모듈로서 공유를 해야될때 이다.

그럼 한번 만들어보자.
아래의 스크립트는 customers 테이블과 sales 테이블을 이용하여 MultiSet 스칼라 서브쿼리를 구현한것이다.
Cast + MultiSet 스칼라 서브쿼리를 사용하려면 먼저 type 2개를 만들어야 한다.

CREATE OR REPLACE TYPE SALES_CUST_TYPE AS OBJECT
(
PROD_COUNT NUMBER(5),
CHANNEL_COUNT NUMBER(2), --> 스칼라 서브쿼리가 RETURN 할 TYPE 선언
AMOUNT_TOT NUMBER
)
;

CREATE OR REPLACE TYPE ARRAY_SALES_CUST --> 위에서 선언한 TYPE 을 배열로 선언
AS VARRAY(1) OF SALES_CUST_TYPE ; --> 고객별로 여러건이 RETURN 될 경우 값을 넉넉히 줄것.
--> 아래의 예제는 고객당 1건만 RETURN 하므로 VARRAY 에 1을 준것이다.

이렇게 2개의 type 을 선언하면 모든 준비가 끝난다. Pipilined Funtion 의 코딩량에 비하면 사용하기가 훨신 간편함을 알수 있다. 그럼 이제 SQL 을 만들어 보자.

SELECT C.CUST_ID, C.CUST_YEAR_OF_BIRTH
,S.PROD_COUNT, S.CHANNEL_COUNT, S.AMOUNT_TOT
FROM (
   SELECT /*+ FULL(C) */
C.CUST_ID, C.CUST_YEAR_OF_BIRTH,
CAST( MULTISET ( SELECT count(distinct s.PROD_ID) AS PROD_COUNT
   ,count(distinct s.CHANNEL_ID) AS CHANNEL_COUNT
   ,sum(s.AMOUNT_SOLD) AS AMOUNT_TOT
FROM sh.sales s
WHERE s.cust_id = c.cust_id
   ) AS ARRAY_SALES_CUST --> 위에서 선언한 배열 TYPE 이용.
) AS SALES_CUST --> MultiSet 함수의 Alias 선언
FROM sh.customers c
) C,
TABLE(SALES_CUST) S --> 위에서 선언한 MultiSet 함수의 Alias 를 테이블로 이용함.
WHERE S.PROD_COUNT <> 0 ; --> 스칼라 서브쿼리내의 특정컬럼을 조건절에 이용한 예제임.

SQL 의 결과 :

결과는 성공적이다. 성능면에서도 스칼라서브쿼리와 동일하다. 이것은 마치 스칼라서브쿼리의 장점과 PIPELINED TABLE 함수의 장점을 취한것과 같다. 하지만 대용량 배치 SQL 에서는 Hash 조인이나 Sort Merge 조인보다 성능이 좋지 못하므로 주의 해야한다.

결론 :
Cast + MultiSet 스칼라 서브쿼리는 부분범위 처리시 혹은 페이징 처리시에 탁월한 성능을 발휘하며 스칼라 서브쿼리의 단점인 Multi Column, Multi Row 을 RETURN 할수 있다. 뿐만아니라 View Merging 이나 JPPD (Join Predicate Push Down) 이 불가능할 경우의 훌륭한 해결책이 될수 있다.

P.S :
위의 예제는 Multi Column을 RETURN 하지만 Multi Row 를 RETURN 하지 않는다. 독자 여러분이 위의 Cast + MultiSet 스칼라 서브쿼리를 Multi Column, Multi Row 를 RETURN 하는것으로 바꾸어 보라. 백견이 불여일행 이다.

'Oracle > SQL Pattern' 카테고리의 다른 글

오라클 Regular Expressions 완전정복 (22)	2009.10.07
간단한 집합개념 Test (7)	2009.07.06
Model 절에 대하여 (10)	2009.05.28
SQL 포기하지마라. (7)	2009.05.28
Upgrade Select For Update (3)	2009.05.19

Posted by extremedb

,

천당과 지옥의 차이

Oracle/TroubleShooting 2009. 6. 17. 19:47

얼마전에 ORACLE DBMS ShutDown 과 관련한 회의가 있었는데 문제는 아래와 같다.

지옥이 시작되다.
악성 SQL 이 하나 있었다. 그 SQL 은 한번수행시 평균 Elapsed Time 이 0.95 초 정도 걸리고 자주 사용되는 중요한 SQL 인데 DBA 가 조인방법을 바꾸어 Elapsed Time 을 0.8 초로 줄였다. 조인방법만 바꾸었을 뿐인데 성능이 10% 이상 향상 되었다. 그리고 그 DBA 는 튜닝된 SQL을 개발자에게 운영 시스템에 반영하라고 지시하였다. 그 SQL을 테스트 해본 개발자는 성능이 빨라진것을 확인하고 해당 힌트를 적용하여 운영시스템에 반영하였다.

여기서 부터 지옥이 시작되었다. 잘운영되던 DBMS 가 버벅이기 시작한것이다.
결국 운영할수 없는 지경에 까지 이르러서 운영조직은 ORACLE DBMS ShutDown을 결정하였고 튜닝된 SQL 을 원복시켰다.
다시 천당이 되었다. 결국 원래의 SQL 은 문제가 없었고 DBA 가 튜닝한 SQL 이 악성 SQL 이었던 셈이다.

메모리 사용량을 계산해보자.
원인은 튜닝된 SQL 이었다. 그 SQL 은 Peak Time 에 무려 초당 700 번이나 실행되었고 한번 수행시 사용되는 HASH AREA SIZE 를 계산해보니 5MB 정도를 소비하였다. 하나의 SQL 이 초당 3.5 Giga Byte(700 * 5MB) 를 소모한 것이다. 전체 PGA 의 1/3 이 넘는 메모리를 하나의 SQL 이 소모해버린것이다. 당연히 그 SQL 을 제외한 다른 SQL은 PGA 영역의 메모리를 사용하려고 줄을 서게 될것이고 시스템 전반적인 성능이 저하될 것이다. 그뿐인가? 원래 0.95 초 걸리던 SQL 도 수행속도가 1초가 넘어버렸다.
주로 개발자 출신의 DBA 가 이와같은 실수를 많이 저지른다. 시스템 엔지니어 출신의 DBA 는 절대 이런실수가 없다. 물론 개발자 출신 DBA 의 장점은 헤아릴수 없이 많다.

메모리 증설이 해결책인가?
결국 Hash 조인을 을 Nested Loop 조인으로 바꾸고 Access Path를 파악하여 적절한 인덱스를 생성하는것으로 사태는 진정 되었다. 운영조직은 현재 메모리를 증설할 계획이라고 한다. 하지만 메모리 증설로 해결되는것은 미시적 관점이며 거시적 관점에서 해결책은 되지 못한다. 자주 사용하는 SQL 을 튜닝 할때마다 메모리를 증설 할것인가?

위의 문제해결과정이 우리에게 주는 교훈은 무엇인가?
수행속도의 최적화 혹은 Logical Reads의 수를 줄이는것이 항상 튜닝의 목표가 아니라는 것이다. 자원(CPU, MEMORY) 등은 한정적이다. 따라서 이자원들을 전체 시스템관점에서 적절하게 분배하는것 또한 튜닝의 목표가 되어야 한다.
자주 사용되는 SQL을 튜닝할때 Hash Join 을 남발하지 말아야 한다. Hash 조인은 조인횟수를 획기적으로 줄여주지만 반대급부로 메모리 소모가 심하다. 물론 0.95 초 걸리던 SQL 이 0.001 초만에 끝난다면 그방법이 고려될수도 있다. 자원을 독점하는 시간과 SQL 의 수행속도가 현저하게 줄어들었으므로...

수행속도의 최적화가 항상 튜닝의 목표인가?
튜닝의 목표는 물론 Response Time 을 줄이는것 혹은 Logical Read 등을 줄이는 것에 있다.
하지만 항상 추가적으로 고려해야 할것이 전체 시스템 관점에서 자원의 효율적인 배분이다. 이것이 시스템 튜닝의 기본이다.
생각해보라. 같은시간대에 수행되는 야간배치 SQL 이 여러개 있고 그중에 하나가 Parallel 힌트를 다음과 같이 사용하였다면 얼마나 끔찍한 일이 일어날 것인가?

INSERT /*+ Parallel(B 256) */ INTO ~
SELECT /*+ Parallel(A 256) */
FROM ~ ;

천당과 지옥의 차이는 힌트와 같은 아주 조그만 코드에서도 좌우될수 있다.

'Oracle > TroubleShooting' 카테고리의 다른 글

한방 Query를 사용하지 말아야 할 때 (17)	2010.02.19
유명한 Function Based Index 의 버그 (9)	2009.11.19
SQL 튜닝이 불가한 경우 (12)	2009.09.16
Sequence 의 오작동 (무조건 채번되는 Sequence) (0)	2009.02.19
Sequence 의 오작동 (번호를 건너뛰는 Sequence) (4)	2009.02.10

Posted by extremedb

,

11g DBMS_STATS 패키지 성능개선의 3가지 원리

Oracle/Optimizer 2009. 6. 15. 13:00

1번부분은 오류가 있으므로 코멘트를 반드시 참조하기 바란다.(2009.06.20)

DBMS_STATS 정녕 필요악인가?
오라클 DBMS의 모든 통계정보는 DBMS_STATS 패키지에 의해서 관리된다고 할수 있다. 오라클이 제공하는 모든 패키지가 중요하지만 가장 중요한 패키지 하나만 선정하라고 하면 주저없이 이 패키지를 꼽는다. 하지만 이패키지에는 모든사람이 알고 있는 치명적인 단점이 하나 있다. 바로 통계정보 생성 속도가 느리다는 것이다. 하지만 11g 에서 몇가지의 단점을 보완하였다.

11g 에서 성능이 빨라지다.
오늘은 11g 에서 DBMS_STATS 패키지의 개선사항에 대하여 알아보려 한다. 11g 에서 개선된 것은 크게 3가지 이다.
1. 컬럼 단위의 MIN/MAX 통계정보 생성에 의한 SQL 성능향상.
DBMS_STATS 패키지를 이용한 통계정보 생성시 컬럼단위의 MIN/MAX 값을 생성한다는것을 아는가?
11g 부터 MIN/MAX aggregation 이 포함된 SQL 을 만나면 컬럼단위의 통계정보를 참조함으로서 SQL 의 성능을 향상 시킨다. 예를 들어 다음과 같은 SQL 을 수행시킬경우 10g 에 비하여 약 30% 의 성능향상을 얻을수 있다.

select min(c2), max(c2), min(c3), max(c3), min(c6), max(c6),
……
min(c32), max(c32)
from CUSTOMERX;

통계정보로 부터 컬럼별로 MIN/MAX 값을 참조한후에 테이블을 scan 하면서 MIN~ MAX 값에서 벗어나는 값들만 MIN/MAX 함수를 적용하여 보여주면 되기 때문이다. 이것은 DBMS_STATS 패키지 자체의 성능향상이 아니라 DBMS_STATS 패키지에 의한 Aggregation SQL 의 성능 향상인 셈이다.

2. NDV(Number Of Distinct Value) 통계정보 생성시의 성능향상.
NDV 값을 생성하기 위해서는 다음과 같은 SQL 이 수행되어야만 한다.

select count(distinct c1), count(distinct c2), ..
from T

위와 같은 SQL 은 테이블 SIZE 가 클경우 심각한 SORT 부하를 발생시킨다. 그렇다고 NDV 생성시 ESTIMATE 1% 로 할경우 데이터에 NULL 이 많다거나 컬럼값이 하나로 편중되는 현상이 심할경우 부정확한 NDV 를 생성할수 있다. 이것은 SQL 의 성능에 부정적으로 작용할수 있다.
11g 부터는 새로운 hash-based algorithm 을 추가하였다. 10g 에서 Sort Group By 대신에 Hash Group By 를 적용하면서 성능이 향상된것과 같은 개념이다. 주의 해야될사항은 새로운 hash-based algorithm 을 사용하려면 Sampling 을 하면 안된다는 것이다. Sampling을 하지 않으면 전체데이터를 scan 하지만 수행시간은 오히려 Sampling 보다 같거나 빠르다. 아래는 오라클사에서 테스트 한 결과이다.

DataBase Ver Elapsed Time (s)
----------------------------- ----------------
Oracle Database 10g Release 2 10% 2155
Oracle Database 10g Release 2 100% 24242
Oracle Database 11g 1516

10g R2 에서 Sampling 10% 로 한것보다 오히려 11g 에서 전체건에 대하여 생성하는것이 더빠르다는 결론이 나왔다. 또한 NDV 의 정확성 또한 거의 100% 에 가깝다. 새로운 algorithm을 사용할경우 획기적인 성능향상과 정확성을 기대할수 있다. 자세한 내용은 아래의 White Paper 를 참조하라.

3. 파티션 적용 테이블에서 Incremental NDV 생성기능.
파티션 적용테이블에서 거래내역이나 이력데이터를 보관할경우 대부분이 일자로 Range 파티션을 하게된다.
Global 통계정보를 생성하려면 10g 까지는 2단계의 과정을 거쳐야 했다.
첫번째로는 파티션 레벨의 통계정보를 생성 하기위해 각파티션을 scan 하는작업수행.
두번째로는 Global 통계정보를 생성하기위해 전체 파티션을 scan 하는 작업수행.
정말 비효율적이지 않는가? 같은 파티션을 두번씩 읽은 셈이다.

10g 에서는 아래처럼 granularity를 옵션으로 사용하거나 아니면 수동으로 2번 돌려야 했다. 아래예제 에서는
estimate_percent 를 10% 로 한정 하였다.

exec dbms_stats.gather_table_stats('CUST', 'CUSTOMERX', estimate_percent =>10, granularity =>'GLOBAL and PARTITION');

위의 방법대로 하면 전체 파티션을 내부적으로 2번씩 scan 해야 하므로 성능면에서 최악이다.
경험 많은 DBA 는 새로이 추가된 파티션의 통계정보만 생성하고 Global 통계정보를 생성함으로 전체 파티션에 대하여 2번씩 scan 하는 비효율을 제거한다. 아래처럼 새로 추가된 파티션을 명시하면 된다.

exec dbms_stats.gather_table_stats('CUST', 'CUSTOMERX', 'PART10', estimate_percent =>10, granularity =>'PARTITION');
exec dbms_stats.gather_table_stats('CUST', 'CUSTOMERX', estimate_percent =>10, granularity =>'GLOBAL');

하지만 2번째의 방법으로도 Global 통계정보의 생성을 위한 전체 파티션 scan은 막을수 있는 방법이 없었다.
이러한 성능 이슈 때문에 11g 에서는 2단계의 과정이 없어지고 과정이 하나로 줄었다. 쉽게 말하면 각파티션 통계정보를 생성해놓고 Global 통계정보 생성시 파티션의 통계정보를 sum 하여 이용한다는 것이다. 또한 특정파티션을 지정하지 않아도 아래와 같이 gather_table_stats 을 수행하기전에 set_table_prefs를 수행하게 되면 바뀌거나 새로이 insert 된 파티션만 scan 을 하게된다.

11g :
exec dbms_stats.set_table_prefs('CUST', 'CUSTOMERX', 'INCREMENTAL', 'TRUE');
exec dbms_stats.gather_table_stats(‘CUST', 'CUSTOMERX', granularity =>'GLOBAL and PARTITION');

11g 에서는 estimate_percent를 10% 로 한정 하지 않았기 때문에 성능 어떨지 궁금하지 않은가?
아래의 테스트 결과를 보자.

DataBase Ver Elapsed Time (s)
-------------------------------    ----------------
Oracle Database 11g Incremental    1.25
Oracle Database 10g Release 2 10%    2152 --> 전체를 2번 scan 한 경우
Oracle Database 10g Release 2 10% Manual 1058 --> 추가된 파티션만 명시한 경우

10g 에서 추가된 파티션만 명시한 경우에 비하여 무려 800 배 정도 빨라졌다. 대단하지 않은가?
이것은 3가지의 효과가 서로 상호작용한 효과이다.
1.Global 통계정보 생성시 2단계의 작업(각 파티션의 작업 + Global 작업) 이 1단계로 축소되고
2.Imcremental NDV 작업이 가능 해졌다는것
3.미리 언급한 hash-based algorithm 의 효과

결론 :
10g 까지 DBMS_STATS 패키지는 그중요성에 비하여 느린 성능이슈 때문에 Necessary Evil (필요악) 으로 인식되어 왔다. 하지만 드디어 11g 에서 몇가지의 개선사항으로 좀더 귀여운 모습을 하고 우리에게 돌아왔다. 11g 로 업그레이드 할이유가 몇가지 더 생긴것이다.

Reference : DSS Performance in Oracle 11g

편집후기 : 위에 링크된 백서는 DBMS_STATS 패키지 뿐만 아니라 11g 의 매뉴얼에 없는 여러가지 비급이 실려 있으므로 필독 하기 바란다.

백서내용 요약
1.Result Cache 신기능.
2.Composite Partitioning 신기능.
3.Full Outer Join 개선사항.
4.Nested Loop Join 개선사항.

'Oracle > Optimizer' 카테고리의 다른 글

Group By 를 먼저 수행하고 Join 하라( Group By Placement ) (4)	2009.08.24
Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down) (9)	2009.08.12
PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라. (2)	2009.06.09
Query Transformation Internal ( about JPPD using Lateral View ) (0)	2009.04.20
오라클에 트랜스포머가 있다? (4)	2009.04.15

Posted by extremedb

,

PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라.

Oracle/Optimizer 2009. 6. 9. 14:54

Query Transformer 의 냉대
튜닝을 하는 많은 사람들이 PM 의 개념을 모른다는 결과가 나왔다. 정기모임 술자리에서 즉석으로 설문을 하였는데 결과는 충격적이었다. 참석자 10명은 DBA, 튜너, DB 컨설턴트 등등 DB 전문가들의 모임이라고 할수 있는데 단 한명도 아는사람이 없었다.

필자가 충격적이라고 한 이유는 그모임의 많은 사람들이 왠만한 SQL 의 COST 를 계산할수 있는 내공을 가진 사람들이 었기 때문이다. 다행히 JPPD 나 VIEW MERGING, Unnesting 과 같이 튜닝 책에 소개 되는 간단한 변환들은 알고 있었다. 하지만 Query Transformer 가 푸배접을 받고 있다는 생각은 지울수가 없었다.

Query Transformer 는 그 중요성이 옵티마이져의 50% 를 차지한다. 왜그럴까? 옵티마이져의 3대 Components 는 Query Transformer, Cost Estimator, Plan Generator 이지만 이중에서 우리가 연구할수 있는 것은 Query Transformer 와 Cost Estimator 이며 Plan Generator 의 비밀은 오라클사의 DBMS 설계자/개발자만이 알수 있는 영역이기 때문이다. 또한 SQL 이 Transformer 에 의하여 변형되고 재작성 되기 때문에 성능에 직접적인 영향을 끼친다. 대부분의 경우 Query Transformation 이 발생하면 성능에 긍정적인 영향을 끼치지만 예외적으로 부정정인 영향을 줄수가 있으므로 가능한 Query Transformer에 대하여 상세히 알아야 한다.

어찌되었건 모임에서 PM 관련 내용을 블로그에 올리겠다는 약속을 하였다.

PM ( Predicate Move Around ) 이란?

인라인뷰가 여러 개 있고 각각의 where 절에 공통적인 조건들이 있다고 가정하자.
이럴경우에 모든 인라인뷰의 where 절에 똑 같은 조건들을 반복해서 사용해야 할까?
물론 그렇게 해야 하는 경우가 있지만 아래의 경우에는 그렇지 않음을 알수 있다..

SELECT /*+ qb_name (v_outer) */

v1.*

FROM (SELECT /*+ qb_name (IV1) no_merge */

e1.*, d1.location_id

FROM employee e1, department d1

WHERE e1.department_id = d1.department_id

AND d1.department_id = 30

) v1,

(SELECT /*+ qb_name (IV2) no_merge */

d2.department_id, AVG (salary) avg_sal_dept

FROM employee e2, department d2, loc l2

WHERE e2.department_id = d2.department_id

AND l2.location_id = d2.location_id

GROUP BY d2.department_id

) v2

WHERE v1.department_id = v2.department_id

AND v1.salary > v2.avg_sal_dept ;

----------------------------------------------------------+----------------------------------+

----------------------------------------------------------+----------------------------------+

| 0 | SELECT STATEMENT | | | | 5 | |

| 1 | HASH JOIN | | 1 | 176 | 5 | 00:00:01|

| 2 | VIEW | | 1 | 28 | 2 | 00:00:01|

| 3 | HASH GROUP BY | | 1 | 21 | 2 | 00:00:01|

| 4 | NESTED LOOPS | | | | | |

| 5 | NESTED LOOPS | | 6 | 126 | 2 | 00:00:01|

| 6 | NESTED LOOPS | | 1 | 14 | 1 | 00:00:01|

| 7 | INDEX RANGE SCAN | DEPT_IX_01 | 1 | 11 | 1 | 00:00:01|

| 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 23 | 69 | 0 | |

| 9 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX| 6 | | 0 | |

| 10 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 42 | 1 | 00:00:01|

| 11 | VIEW | | 6 | 888 | 2 | 00:00:01|

| 12 | NESTED LOOPS | | | | | |

| 13 | NESTED LOOPS | | 6 | 474 | 2 | 00:00:01|

| 14 | INDEX RANGE SCAN | DEPT_IX_01 | 1 | 11 | 1 | 00:00:01|

| 15 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX| 6 | | 0 | |

| 16 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 6 | 408 | 1 | 00:00:01|

----------------------------------------------------------+----------------------------------+

Predicate Information:

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

1 - access("V1"."DEPARTMENT_ID"="V2_DEPT"."DEPARTMENT_ID")

1 - filter("V1"."SALARY">"V2_DEPT"."AVG_SAL_DEPT")

7 - access("D2"."DEPARTMENT_ID"=30)

8 - access("L2"."LOCATION_ID"="D2"."LOCATION_ID")

9 - access("E2"."DEPARTMENT_ID"=30)

14 - access("D1"."DEPARTMENT_ID"=30)

15 - access("E1"."DEPARTMENT_ID"=30)

다른뷰에 조건이 추가되었다.

Predicate Information을 보면 두번째 뷰(v2 ) 에 "D2"."DEPARTMENT_ID"=30 조건과 "E2"."DEPARTMENT_ID"=30 조건이 파고들어 간 것을 알수 있다. 이 현상 때문에 D1 과 E1 에서 인덱스를 사용하였는데 결과는 성능면에서 아주 성공적이다. 그렇다면 오라클은 어떤 과정을 거쳐서 이작업을 진행하였을까?

10053 trace 정보를 보면 PM의 진행과정이 매우 상세하게 나와 있다.

PM: Considering predicate move-around in query block V_OUTER (#1)

**************************

Predicate Move-Around (PM)

**************************

PM: Passed validity checks.

PM: Pulled up predicate "V1"."DEPARTMENT_ID"=30

from query block IV1 (#2) to query block V_OUTER (#1)

PM: Pushed down predicate "D2"."DEPARTMENT_ID"=30

from query block V_OUTER (#1) to query block IV2 (#3)

PM 은 순서가 중여하다.
10053 trace 내용을 분석하여 수행과정을 살펴보자.

1. 먼저 PM 이 수행될수 있는지 검사한다.

2. V1 에서 WHERE 조건 d1.department_id = 30 를 바깥쪽 메인쿼리로 이동시킨다.

이것을 Predicate pull up 이라고 한다.

3. 메인쿼리로 옮겨진 where 조건을 v2 에 복사한다.

이것을 Predicate Push down 이라고 한다.

4. 최종 결과에서 중복된 조건절이 존재하면 삭제한다..

V1, V2 가 메인쿼리(V_OUTER)에서 department_id 기준으로 조인되고 있기 때문에 조건절을 V_OUTER 로 빼낸 다음에 V2 에 조건절을 밀어 넣고 있다. 이것은 JPPD 기능과 유사한 면이 있지만 Predicate pull up 이 반드시 먼저 일어나야 한다는 점과 Hash 조인등에서도 PM 이 발생한다는 점에서 엄연히 다르다. 오라클은 여러분이 모르는 사이에 조건절을 이리 저리 옮겨 다니면서 SQL 의 최적화를 시도하고 있다.

PM 아무때나 발생하나?
그럼 PM 이 어떤 조건일 경우 발생하는지 짐작할수 있겠는가?

다음과 같은 조건일 경우 PM 이 발생된다.

1. 뷰(혹은 인라인뷰) 가 2개 혹은 그이상이 되어야 한다.(예제에서 V1, V2 가 있음)

2. 특정 인라인뷰내의 조건이 존재하고 뷰의 바깥쪽에서 조건을 사용한 컬럼으로 조인이 발생할 경우에 발생된다. 예제에서 는 d1.department_id = 30 으로 V1 내부에 조건이 존재하고 V_OUTER 에서 department_id 로 V2 와 조인을 하고 있다.

3. VIEW MERGE 가 발생하지 않아야 한다. Merging 이 발생되면 PM 대신에 Transitive Predicates 가 발생된다.

4. 파라미터 _PRED_MOVE_AROUND 가 true 로 지정이 되어 있어야 한다.

결론 :
제목에서 보듯이 PM 의 개념은 매우 간단하다. Where 조건을 다른뷰에 이동시키는 기능이며 Heuristic Transformatin 의 대표적인 예제이다.
오늘 올린 글은 현재 집필중인 책의 내용인데 일부를 먼저 공개하기로 결정 하였다.

편집후기 : JPPD 와 PM 이 헷갈린다는 보고가 들어왔다. 둘다 WHERE 조건이 PUSH 되는것이지만 가장 결정적이 차이점은 JPPD 는 Predicate pull up 기능이 없다는 것이다. 아주 명확하게 구분할수 있다.

'Oracle > Optimizer' 카테고리의 다른 글

Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down) (9)	2009.08.12
11g DBMS_STATS 패키지 성능개선의 3가지 원리 (3)	2009.06.15
Query Transformation Internal ( about JPPD using Lateral View ) (0)	2009.04.20
오라클에 트랜스포머가 있다? (4)	2009.04.15
Access Sub Query 의 함정 (4)	2008.11.19

Posted by extremedb

,

Model 절에 대하여

Oracle/SQL Pattern 2009. 5. 28. 21:19

SQL 포기하지마라. 라는 글에서 Model 절을 언급한바 있다.
독자중에 좀더 자세히 알고 싶어 하는 분들이 있어서 개인적으로 정리한 자료를 올리게 되었다.
하지만 배포시에는 출처를 꼭 밝혀주기

바란다.
아래파일은 upload 시에 200 KB 한계가 있으므로 3개로 나누어 올리지만
3개를 모두 다운로드 받아 압축을 풀면 하나의 파일로 생성되므로 문제가 없을 것이다.

invalid-file

'Oracle > SQL Pattern' 카테고리의 다른 글

간단한 집합개념 Test (7)	2009.07.06
스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기. (7)	2009.06.29
SQL 포기하지마라. (7)	2009.05.28
Upgrade Select For Update (3)	2009.05.19
응용력 발휘하기 (2)	2009.03.20

Posted by extremedb

,

SQL 포기하지마라.

Oracle/SQL Pattern 2009. 5. 28. 01:13

SQL 은 방정식을 풀기에 적합한 언어인가?
우연히 방정식을 푸는 요건이 있었는데 상황은 아래와 같았다.
요건은 "정수 X 를 정수 Y 로 계속 나누되 N 번 나눈 결과와 N-1 번 나눈 결과의 차이가 0.25 보다 작으면
멈추고 그때까지의 나눈값과 LOOP 횟수( N )를 출력하라" 였다. 물론 X 와 Y 는 변수이다.
이해를 쉽게 하기 위하여 왼쪽의 그림을 보자.

왼쪽의 그림을 설명하면 50을 2 로 8번 나누면 0.1953 인데 이것을 7번 나눈값으로 빼면 결과는 0.25 이하이다.

따라서 원하는 출력값은 0.1953 과 8 번(LOOP 횟수) 인 것이다.

이요건을 들은 개발자들은 저마다 자바나 PL/SQL 로 개발 하면 된다고 생각했다.

하지만 이기능을 요구한 사람의 말에 따르면 SELECT 쿼리결과로 나오는 것이 가장 좋다.

하지만 불가능하면 자바나 PL/SQL 등으로 구현해도 된다는 것이었다.

여러분이 만약 이러한 요구사항을 해결해야 한다면?

퀴즈라고 생각하고 머릿속에서 해법을 생각해보라.

포기하지마라
사실 필자는 이문제가 성능이슈가 아니었고 SQL을 제법 잘사용하는 개발자들과 DBA 가 회의에 참석하였으므로 직접 나서지 않았는데 한마디 말 때문에 흥분하기 시작했다.

개발자1 : 이것은 SQL 로는 안됩니다. Loop 도 돌려야 하고 Loop 내에서 빠져나가는 로직도 추가해야 되기 때문입니다.
필자 : SQL 로 하는것을 포기 하지마십시오.
개발자1 : 네?
개발자2 : 5분이면 PL/SQL 함수로 구현이 가능할거 같습니다.
그함수를 SQL 에서 call 하면 됩니다. 괜히 고생하지 마시죠.
필자 : 5분이라구요?....그렇다면 두분이서 작성한 코드를 10분뒤에 저에게 보여주세요.

이렇게 해서 회의가 잠시중단이 되었다.
사실 필자는 자주 흥분하는편이 아닌데 언제부터인지 SQL 로는 불가능하며 비효율적이라는 소리를 들을때면 이상하게 자극이 된다. 어쩔수 없는 엔지니어 출신의 비애인것 같다.

It's Time to overhaul!
시간이 되어 개발자들이 작성한 코드를 검토해 보았다.

CREATE OR REPLACE function sf_calc(v_value number, v_division number)
return varchar2 as
   v_iteration pls_integer := 0;
   v_result number;
   v_result_before number;
BEGIN
   v_result := v_value;
   FOR v_counter IN 1..999999999 LOOP

v_result := v_result / v_division;
v_iteration := v_counter;
EXIT WHEN v_iteration > 1 and v_result_before - v_result <= 0.25;
v_result_before = v_result ;
   END LOOP;
   return '결과 : ' ||to_char(v_result) || ' , '||
'Loop 횟수 : '|| to_char(v_iteration);
END;

위의 함수는 약간 손볼곳이 있지만 그래도 결과에 충실하다. 위의 함수를 SQL 에서 사용하면 아래와 같다.

select sf_calc(50, 2) from dual;

결과 : .1953125 , Loop 횟수 : 8

함수의 길이도 길지않고 이정도면 괜찮다고 말할수 있다. 하지만 또다른 비효율이 숨어 있었다.
결과를 comma (,) 기준으로 잘라서 2 컬럼으로 보여줘야 하는 문제가 여전히 남는다.
그렇다면 필자가 작성한 아래의 SQL 로 바꾸는 것은 어떤가?

select S as "결과", N + 1 as "Loop 횟수" From DUAL
model
dimension by (1 X)
measures ( :v_value S, 0 N) --> :v_value 에 50 대입
rules iterate (999999999) Until (PREVIOUS(S[1])-S[1]<=0.25)
( S[1] = S[1]/ :v_division, --> ::v_division 에 2 대입
N[1] = ITERATION_NUMBER );

결과 Loop 횟수
----------- -----------
0.1953125 8

Model 절 문법이 어렵다고 들었다.
결과가 2컬럼으로 분리되어 정확히 나왔다. 코딩의 시간은 단 1분 이며 코드의 길이는 어떤가? 이정도면 충분히 짧다.
개발자들이 유지보수가 어렵다는 문제를 들고 나왔다. 1분만 투자하면 문법을 익힐수 있음에도 문법이 어렵다는 것은 미신이다.
현재 오라클이 11g 까지 나왔지만 각각의 문법들은 몇분만 투자하면 다 알수 있는 정도이다.
물론 성능면에서 신기능의 실행계획을 분석하는것은 다른 이야기 이다.
간단히 문법을 설명하면 아래와 같다.

1. rules iterate(999999999) 라는 것은 괄호 안의 작업(S[1] = ... 이후생략)을 999999999 번 반복해서 실행한다의 의미이다.
2. Until 이라는것은 Loop 를 빠져나오기 위한 조건을 지정 하는것이다.
3. PREVIOUS(N) 라는 것은 N-1 번째의 값을 의미한다. 따라서 PREVIOUS(S[1])-S[1]<=0.25 의 의미는
"N -1 번째 :v_value 값에서 N 번째 :v_value 값을 뺀 차이가 0.25 이하일때" 인 것이다.
4. S[1] 의 값은 최초에는 :v_value 와 같지만 S[1]/ :v_division 로 계산된 값을 Loop 시마다 대입하므로 값이 계속 바뀐다.
5. N[1] 의 값은 Loop 시마다 1 증가한다. 예약어 ITERATION_NUMBER 의 기능때문이다.
하지만 최초의 값이 0부터 시작되므로 select 시에 1을 더해서 보여줘야 한다.
6. measures ( :v_value S, 0 N) 라는 표현은 단순히 S[1] 과 N[1] 을 사용하기 위하여 단순히 alias 를 정의한것에 불과하다.

주의사항 :
여기서 dimension by 는 아무런 뜻이 없다. 하지만 숫자 1 대신에 다른값을 사용할 경우 SQL 이 종료되지 않으므로 주의해야 한다.

Model 절로 무엇을 할수 있나?
Model 절과 관련된 미신중의 하나는 Time series, 수요예측, 판매예측, 기상예측 등등 주로 이전실적에 여러가지 factor 를 추가하여 미래를 예측하는데 사용하거나 SQL 에서 축이동(Pivot/Unpivot) 등의 용도로만 사용된다고 알고 있다는 것이다. 하지만 위의 SQL 은 이것들중 어디에도 포함되지 않는다. 다시말하면 복잡한 연립방정식을 푸는데 제일좋은 것이 Model 절이다.

결론:
MODEL 절은 오래전(6년전)에 나온 SQL 문법이지만 아직 늦지 않았다. 필자가 확인한 결과 아직도 모르는 사람들이 많이 있으므로 지금 익힌다면 선구자가 될것이다. SQL 은 여러분이 생각하는것보다 훨씬더 강력하고 효율적임을 알수있다. 위의 SQL과 같이 간단하지만 강력한 기능들이 많이 준비되어 있다. 예를들면 X-Query, Regular Expression, Pivot/UnPivot 등이 그것이다. 무기의 종류(SQL)를 여러개 준비한 사람과 기관총 하나만 가지고 있는 사람의 차이는 어마어마 하다. 준비된 사람은 요구조건에 따라서 단검을 사용할것인지 탱크를 사용할것인지 그것도 아니라면 F16 전투기를 사용할것인지 상황에 따라서 맞는 무기를 선택할수 있다. SQL 공부 여기서 포기할것인가?

'Oracle > SQL Pattern' 카테고리의 다른 글

스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기. (7)	2009.06.29
Model 절에 대하여 (10)	2009.05.28
Upgrade Select For Update (3)	2009.05.19
응용력 발휘하기 (2)	2009.03.20
Hypothetical Functions 은 어떨때 써야하나? (3)	2008.10.25

Posted by extremedb

,

분석함수를 이용한 TOP SQL은 튜닝이 불가한가?

Oracle/SQL Tuning 2009. 5. 22. 01:17

대부분의 TOP SQL 은 ROWNUM 을 이용하여 부분범위처리 하는 개념임을 알것이다.
하지만 RANK 등의 분석함수를 이용하여 TOP 처리를 하는경우가 많이 있다.
이런경우에 대부분의 개발자및 DBA 들이 범하는 오류가 있으므로 오늘은 그부분을 바로잡고자 한다.
환경은 Oracle 10.2.0.4 이며 모든 예제는 오라클의 SH 스키마의 SALES 테이블을 이용한다.

들어가며
ROWNUM 을 이용한 TOP SQL과 분석함수를 이용한 것의 다른점이 무엇인가?.
분석함수를 이용하면 1등이 여러명일때도 처리가 가능 하지만 ROWNUM 을 이용할경우 단한건만 가져오므로 처리가 불가능하다는 것이다. 아래는 그러한 경우에 분석함수를 하여 간단하게 해결한 경우이다.

SELECT /*+ gather_plan_statistics */
*
FROM (SELECT cust_id, prod_id, time_id, amount_sold,
RANK () OVER (ORDER BY amount_sold DESC) top_sales
FROM sh.sales a)
WHERE top_sales <= :V_RANK; --> 숫자 1 대입

먼저 매출액기준으로 순위를 만든후에 인라인뷰의 밖에서 FILTER 하는 형태의 SQL 이다.
금액기준으로 가장 많이 팔린 상품과 해당고객 그리고 해당매출일자 등을 SELECT 하고 있다.

----------------------------------------------------------------------------------------------
| Id | Operation    | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |    1 |    108 |00:00:01.09 |    1718 |    |
|* 1 | VIEW    | |    1 |    108 |00:00:01.09 |    1718 |    |
|* 2 | WINDOW SORT PUSHED RANK| |    1 |    109 |00:00:01.09 |    1718 | 45M (0)|
| 3 |    PARTITION RANGE ALL | |    1 |    918K|00:00:00.01 |    1718 |    |
| 4 | TABLE ACCESS FULL    | SALES | 28 |    918K|00:00:00.01 |    1718 |    |
----------------------------------------------------------------------------------------------

A-Rows 항목을 보면 1등이 108 건임을 알수 있다.

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TOP_SALES"<=:V_RANK)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<=:V_RANK)

울며 겨자먹기
약 91만건을 full scan 하였으며 일량(Buffers)과 메모리 사용량(Used-Mem)이 엄청나다.
분명히 문제가 있다.
분석함수로 TOP SQL 을 구현할경우 이와 관련된 일반적인 3가지 미신(오류)이 있다.

첫번째 오류로는 일반적으로 금액컬럼에 인덱스를 만들지 않는다는 것이다.

두번째 오류로는 금액컬럼에 인덱스를 만들어도 분석함수를 사용하였으므로 인덱스를 타지 않는다는 것이다.
(참고로 분석함수로는 Function Based Index를 만들수 없다.)

세번째 오류가 가장 심각하다.
인덱스를 사용하지 못할 바에야 전체건을 sort 해야하므로 울며 겨자먹기로 순위컬럼을 모델에 추가하고 그컬럼에 인덱스를 생성하며 데이터가 바뀔때마다 실시간으로 순위컬럼에 UPDATE 하는방식을 많이 사용하고 있다.
이런것을 추출속성이라고 하는데 정합성을 해칠수 있으므로 물리모델링시에 최소화 시켜야 한다.
그렇다면 이러한 문제를 어떻게 풀수 있을까?

먼저 AMOUNT_SOLD에 인덱스를 하나 만든다.

CREATE INDEX SH.SALES_IDX01 ON SH.SALES
(AMOUNT_SOLD);

SELECT /*+ gather_plan_statistics FIRST_ROWS(1) */
*
FROM (SELECT prod_id, amount_sold,
RANK () OVER (ORDER BY amount_sold DESC) top_sales
FROM sh.sales a)
WHERE top_sales <= :V_RANK; --> 숫자 1 대입

-----------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | |    1 |    108 |00:00:00.01 |    11 |
|* 1 | VIEW    | |    1 |    108 |00:00:00.01 |    11 |
|* 2 | WINDOW NOSORT STOPKEY    | |    1 |    108 |00:00:00.01 |    11 |
| 3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES |    1 |    109 |00:00:00.01 |    11 |
| 4 | INDEX FULL SCAN DESCENDING | SALES_IDX01 |    1 |    109 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TOP_SALES"<=:V_RANK)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<=:V_RANK)

해결책은 인덱스 생성
금액 컬럼으로 인덱스를 하나 만들었을 뿐인데 성능과 정합성이라는 두가지 이슈를 모조리 해결 하였다.
일량이(Buffers ) 170 배정도 차이가 나며 메모리 사용은 하지도 않는다.
분석함수 사용시 인라인뷰의 바깥에서 filtering 시의 비밀은 3가지로 요약할수 있다.

1.해당컬럼 컬럼에 인덱스가 있을경우 rownum 과 같이 Stop Key Operation 이 발생 한다는 것이다.

2.Predicate Information 을 보면 뷰를 만들기 전에 filter 하는 것을 볼수 있다.(id 기준으로 2번)
이때 인덱스를 사용하는 것이다.

3.id 기준으로 2번 Operation 을 보면 WINDOW NOSORT ~ 가 있다.
이것은 인덱스가 이미 정렬이 되어있기 때문에 Sort 가 필요없다는 뜻이다.

마치 Sort Group By 시에 해당컬럼이 인덱스로 생성되어 있을경우 NOSORT 가 나오는 원리와 같다.
하지만 인덱스가 없으면 Full Table Scan + filter 만 일어난다. 이때 WINDOW SORT PUSHED RANK Operation 이 발생하게 된다.(가장 위의 인덱스가 없을때의 plan 을 참조 할것)

ROWNUM VS RANK
WINDOW NOSORT STOPKEY 사용시 PLAN 을 보면 1건 더 SCAN했음을 알수 있다.
즉 1위그룹은 108 건 이지만 109 건을 scan 하였고 WINDOW FILTER 과정에서 1건이 제거되었다.
하지만 ROWNUM 을 이용한다면 어떻게 될까?
아래는 ROWNUM 으로 108 건(1위그룹의 전체건수)을 가져오는 예제이다.

SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(1) */ *
FROM (SELECT prod_id, amount_sold
FROM sh.sales a
ORDER BY amount_sold DESC )
WHERE ROWNUM <= :V_RANK --> 108 대입;

-----------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |    1 |    108 |00:00:00.01 |    10 |
|* 1 | COUNT STOPKEY | |    1 |    108 |00:00:00.01 |    10 |
| 2 | VIEW | |    1 |    108 |00:00:00.01 |    10 |
| 3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES |    1 |    108 |00:00:00.01 |    10 |
| 4 | INDEX FULL SCAN DESCENDING | SALES_IDX01 |    1 |    108 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<:V_RANK)

정확히 108 건만 SCAN 했음을 알수 있다.
이것이 ROWNUM 과의 차이이다. ROWNUM 은 정확히 건수를 잘라내지만 WINDOW STOP KEY 는 한건을 더읽어야만 한다.
하지만 이정도(1건)의 비효율은 눈감아 줄수 있다. 하지만 아래의 경우는 비효율이 심하므로 반드시 피해야 한다.

SELECT /*+ gather_plan_statistics FIRST_ROWS(1) */
*
FROM (SELECT prod_id, amount_sold,
RANK () OVER (ORDER BY amount_sold DESC) top_sales
FROM sh.sales a)
WHERE top_sales < :V_RANK; --> 숫자 2 대입

-----------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |    1 |    108 |00:00:00.01 |    13 |
|* 1 | VIEW    | |    1 |    108 |00:00:00.01 |    13 |
|* 2 | WINDOW NOSORT STOPKEY    | |    1 |    115 |00:00:00.01 |    13 |
| 3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES |    1 |    116 |00:00:00.01 |    13 |
| 4 | INDEX FULL SCAN DESCENDING | SALES_IDX01 |    1 |    116 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TOP_SALES"<:V_RANK)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<:V_RANK)

RANK <= 1과 RANK < 2 는 결과는 같다. 하지만 성능은?
WHERE 절에서 = 을 제거하고 < 로 바꾸었으므로 :V_RANK 변수에 2 를 대입 해야 같은결과를 넏을수 있다.
결과는 <= 를 사용한 것과 같지만 일량(Buffers)이 11 에서 13 으로 늘었다.
이것또한 분석함수를 이용한 TOP SQL 처리시 기억해야할 특징중의 하나인데 :V_RANK 변수에 2를 대입 하면 2위그룹까지 SCAN 한다.(정확히 말하면 3위그룹의 첫번째 ROW 까지 SCAN 한다.)
따라서 WINDOW STOPKEY 사용시에 = 를 생략하면 안된다.

결론:
여러 사이트에서 분석함수에 관한 몇가지 오해 때문에 성능이 나빠짐은 물론이고 정합성이 위배될수도 있는 일들이 벌어지고 있다.
두가지만 기억하자.
1.분석함수로 TOP SQL 처리시 반드시 인덱스를 만들것. (빠른성능을 원한다면)
2.RANKING 비교시 <= 를 사용할것.

편집후기 : 주의사항이 있으므로 반드시 댓글을 참고할것.

'Oracle > SQL Tuning' 카테고리의 다른 글

Connect By VS ANSI SQL (7)	2010.02.11
USE_CONCAT 힌트 제대로 알기 (5)	2009.07.17
Top SQL-( Rownum 의 정확한 사용법 ) (2)	2009.03.26
인덱스없는 컬럼의 Order by 시 페이징 처리는 효율적인가? (2)	2008.08.26
다양한 검색조건의 튜닝방법 (3)	2008.08.05

Posted by extremedb

,

Upgrade Select For Update

Oracle/SQL Pattern 2009. 5. 19. 01:17

어느 회식 자리에서
오늘(5월 18일) 어느 사이트의 회식자리에서 프로젝트의 PM 옆자리에 앉게 되었다.
나중에 알고보니 그 PM 은 술자리 기피대상 1호 였는데 이유는 "공장"(?) 이야기를 너무 많이 한다는 것이었다.
필자는 운이 좋게도(?) 자리가 그사람 옆이라 공장 이야기를 많이 들을수 있었다.

PM : 요즘 배치프로그램을 보면 영화 매트릭스의 스미스(악당 요원)가 생각이 나네요.

필자 : 왜 그렇게 생각하세요?

PM: 해도 해도 너무해요. 예전에는 년배치, 분기배치, 월배치, 일배치 가 배치의 전부 였지요.
하지만 90년대 후반부터 하루에 3~4번 도는 배치가 생기기 시작했습니다.
이제는 시간당 한번 실행되는 배치도 성에 안차나 봅니다.
심지어 최근에는 Near Real Time 배치(1~10분 주기)가 많아져 온라인 프로그램과 연관된 lock 문제가
많이 발생하고 있습니다.
Near Real Time 배치는 처리건수가 많지는 않지만 select for update를 사용하는 경우가 많은데
바로 이것이 문제 입니다.

그게 스미스랑 무슨 상관이지?
그 PM 은 우리가 물어보기도 전에 배치프로그램과 스미스 요원과의 연관성을 설명 하였다.
그의 주장은 영화 메트릭스 1~ 3편을 보면 스미스가 지속적으로 upgrade 되는 것을 볼수 있는데 그것은 아래의 3가지로 설명이 가능하다는 것이다..
1편에서의 돌연변이 --> 매트릭스의 아버지격인 컴퓨터 아키텍쳐의 통제에서 벗어난 행동을 한다.
이것은 하루에 N 번 도는 배치에 해당한다.

2편에서의 복제기능 --> 다른사람을 스미스로 복제 해버린다.
이것은 시간당 한번 실행되는 배치에 해당한다.

3편에서의 SuperMan --> 자유자재로 날아다니는 것은 물론이고 드디어 매트릭스를 벗어나 인간세계(Zion) 에 까지 영향을 미친다. 이것은 Near Real Time 배치에 해당하며 드디어 온라인 프로그램과의 충돌을 의미 한다는 것이다.

우리는 그 PM의 이야기를 듯고 한동안 웃음바다가 되었다. 아주 멋진 비유이다.
필자가 생각해보아도 요즘은 Near Real Time 배치가 대세가 되었다.
사람의 욕심은 끝이 없어서 각종 하드웨어와 소프트웨어의 성능이 좋아질수록 거의 실시간으로 배치가 실행되고 배치의 결과물도 아주 짧은 시간에 나오기를 원하는 것이다. 그러다 보면 온라인 프로그램과의 충돌이 자주 일어날수 밖에 없다.

최대의 실수
아래의 짧은 이야기가 오늘 최대실수가 될줄이야....
필자 : "하지만 스미스가 있으면 The One(Neo) 도 있습니다."
PM : "네? 무슨 말인지 설명해 주시죠."
허걱 술자리에서.. 칠판이나 SQL PLUS 등도 없는데 설명을 해야만 하다니....
드디어 필자가 그 PM 의 마법에 걸려들고야 말았다.

아래는 예제는 필자의 이야기를 증명한것이다. 물론 회식 자리에서는 전부 말로 설명 하였다.

정말 배치프로그램은 무한정 기다려야 하나?
배치프로그램에서 최초의 SQL은 Select For Update 문이고 이것은 온라인 프로그램의 Update 문과 같은 테이블을 사용한다고 가정하자. 또한 온라인 UPDATE 의 WHERE 절이 배치 SQL 의 WHERE 절에 속하거나 걸쳐 있다면 동시에 온라인과 배치가 수행될수 없는 구조이다.
혹자는 SELECT FOR UPDATE 에 WAIT N 이나 NOWAIT 옵션을 주면 되지 않냐고 반문할지 모른다. 물론 그렇게 하면 무작정 기다리지는 않는다. 하지만 온라인 프로그램의 UPDATE 문이 3 초안에 COMMIT이나 ROLLBACK 되지 않으면 Select for Update wait 문에서 아래의 예제처럼 에러가 발생한다.

SESSION 1 :
SQL> UPDATE EMP
2 SET SAL = SAL * 1.1
3 WHERE EMPNO = 7566; --> 1건 UPDATE, 7566 번은 부서번호가 20번임에 유의하자.

1 row updated
...SESSION 1 에서 다른 작업(다른 SQL)이 진행중이며 아직 COMMIT 이 안된 상태임 ...

SESSION 2 : --> SESSION 1 에서 COMMIT 안된 상태에서 수행됨.
SQL> SELECT * FROM EMP
2 WHERE DEPTNO = 20 --> 부서번호 20번 전체 SELECT
3 FOR UPDATE WAIT 3; --> 3초간 기다린후에 에러가 발생됨.

ORA-30006: 리소스 사용 중. WAIT 시간 초과로 획득이 만료됨.

위의 에러는 해당 사이트에서 자주 발생 된다고 한다.

해결책 : The One IS SKIP LOCKED Option
이렇게 에러가 발생하게 되면 배치프로그램의 EXCEPTION 이 매우 지저분 하게 된다.
EXCEPTION 에서 특별 처리를 하거나 아니면 다음주기(1~10분)을 기다렸다가 다시 시도 해야 되는데 이때에도 온라인 프로그램이 UPDATE 하고 있지 않기를 바라는수 밖에 없다.
너무나 억울하지 않은가?
다른세션에서 EMP 테이블을 1건 UPDATE 했을뿐인데 부서번호 20번에 해당하는 전체건에 대하여
SELECT FOR UPDATE 를 할수 없는것이다.

그렇다면 이문제를 어떻게 해결할수 있겠는가?
ORACLE 11g 를 사용한다면 SKIP LOCKED 옵션을 사용할수 있다.

SESSION 2 :
SQL> SELECT * FROM EMP
2 WHERE DEPTNO = 20
3 FOR UPDATE SKIP LOCKED;

--> What a option! U can select for update entire row except the row which has been aready locked!

EMPNO ENAME    JOB MGR HIREDATE    SAL    COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH    CLERK    7902 1980-12-17 800.00 20
7788 SCOTT    ANALYST    7566 1987-04-19    3000.00 20
7876 ADAMS    CLERK    7788 1987-05-23    1100.00 20
7902 FORD ANALYST    7566 1981-12-03    3000.00 20

환상적이지 않은가?
LOCK 이걸린 7566 을 제외한 20번 부서 전체건에 대하여 성공적으로 LOCK 을 발생시켰다.
이기능을 2003 년에 발견하였지만 undocumnted 기능으로 분류되어 있었으므로 그때는 명시적으로 이기능을 사용하지 않았다. 하지만 여러분이 10g 에서 AQ(Advanced Queuing) 기능을 사용하고 있고 dequeue 옵션으로 no_wait 을 사용했다면 여러분의 의지와는 상관없이 오라클은 SKIP LOCKED 옵션을 사용한것이다.

결론 :
11g 에서 드디어 SKIP LOCKED 이 11g 매뉴얼에 모습을 드러내었다.
이것은 대단한 Upgrade 이다. 특히 아래와 같이 천건 혹은 만건씩 처리되는 상주(Near Real Time)배치 SQL 에서 말이다.

SELECT *
FROM TAB1
WHERE 배치처리완료 = 'N'
AND ROWNUM <= 1000
FOR UPDATE SKIP LOCKED;

온라인 프로그램에서 1건이 UPDATE 되었다고 해도 나머지 999 건은 처리가 될것이므로 ...

주의사항 : 9i 및 10g 에서 명시적으로 SKIP LOCKED 옵션을 사용하면 안된다.
기능을 숨겨 놓은데는 다 이유가 있다. (NEO 가 1편부터 날라다닌것은 아니다.)
술자리에서는 공장 이야기를 하지말자. NEO 가 있다는 것을 증명하기가 너무 힘들다...^^

'Oracle > SQL Pattern' 카테고리의 다른 글

Model 절에 대하여 (10)	2009.05.28
SQL 포기하지마라. (7)	2009.05.28
응용력 발휘하기 (2)	2009.03.20
Hypothetical Functions 은 어떨때 써야하나? (3)	2008.10.25
Hierarchical Queries (3)	2008.06.23

Posted by extremedb

,

Nested Loop Join 성능향상과 관련된 2가지 원리

Oracle/Data Join Method 2009. 5. 9. 03:12

한 선지자에 의하여 9i/10g 그리고 11g 에서 Nested Loop Join 수행시 buffer pinning 효과에 의한 성능향상이 증명된바 있다.
물론 그글은 명불허전 이다.(해당글 링크:http://ukja.tistory.com/166)
이미 밝혀진 원리에 대해서 증명이나 검증하는것은 더이상 과학이 아니다. 그리고 재미도 없다. 따라서 오늘은
buffer pinning 에 의한 성능개선이 아닌 또다른 성능개선에 대한 2가지 원리 대해서 연구해보자.

이제부터 버젼별로 변경된 Nested Loop Join 의 실행계획과 그에 따르는 원리에 대하여 알아보려 한다. 모든 예제는 오라클 설치시 자동으로 설치되는 SH Schema의 customers 테이블과 sales 테이블을 이용한다.
먼저 인덱스를 하나 만든다.

drop index sh.sales_cust_ix;
create index sh.sales_cust_ix on sh.sales(cust_id);

이어서 buffer cache 를 비우고 SQL 을 실행한다.
고객 테이블을 full scan 하고 sales 테이블과 Nested Loop Join 을 수행한다.

alter system flush buffer_cache;

select /*+ gather_plan_statistics */ count(*)
from (select /*+ no_merge full(c) use_nl(c s) */
   s.cust_id,
   s.time_id,
   c.cust_year_of_birth
   from sh.customers c,
   sh.sales s
   where c.cust_id = s.cust_id
   and c.cust_year_of_birth between 1960 and 1980
   and s.channel_id between 2 and 3
   and s.prod_id < 18);

select * from
table(dbms_xplan.display_cursor(null,null, 'allstats last -rows +outline -predicate' ));

Oracle 8i Plan --> just normal
------------------------------------------------
| Id | Operation    |
---------------------------------------------- |
| 1 | SORT AGGREGATE    |
| 2 | VIEW |
| 3 | NESTED LOOPS |
| 4 |    TABLE ACCESS FULL |
| 5    TABLE ACCESS BY GLOBAL INDEX ROWID|
| 6 |    INDEX RANGE SCAN    |
------------------------------------------------

특별 할것 없는 전통적인 Nested Loop Join 이다.
이제 9i 및 10g 의 plan 을 보자.

9i & 10g Plan --> table prefetch
-------------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE    | |    1 |00:00:05.67 | 245K| 1454 |
| 2 | VIEW | | 8269 |00:00:05.66 | 245K| 1454 |
| 3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 8269 |00:00:05.62 | 245K| 1454 |
| 4 | NESTED LOOPS | |    327K|00:00:02.83 | 41304 | 1454 |
| 5 |    TABLE ACCESS FULL | CUSTOMERS | 20010 |00:00:00.12 |    1457 | 1454 |
| 6 |    INDEX RANGE SCAN    | SALES_CUST_IX |    307K|00:00:00.88 | 39847 |    0 |
-------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
... 중간생략
NLJ_PREFETCH(@"SEL$2" "S"@"SEL$2")
END_OUTLINE_DATA
*/

Oracle 9i 에서 table prefetch 기능이 나오다.
Inner(후행) 테이블의 위치가 Nested Loop Join 위로 올라가 버렸다. 그리고 오라클이 내부적으로
NLJ_PREFETCH 힌트를 사용하였다. 이것은 어떤 의미를 가지고 있을까?
이러한 현상에 대한 원리는 single block I/O request 에 의한 physical read 시 block 을 prefetch(미리 읽는 작업) 한다는데 있다.
여기서 physical read 란 buffer cache 에 데이터가 없어서 disk 에서 데이터를 read 하는것을 의미한다.
어차피 scan할 data 이므로 미리 엑세스할 물리적 주소를 여러개(운반단위) 모은다음 한번에 read 햐여 buffer cache 에 올리게 되는것이다. 여기서 주의할점은 multi block I/O 를 하는것이 아니라 single block I/O 여러개(운반단위만큼)가 동시에 진행된다는 것이다. 이것을 Vector IO 라고 부른다.(Batch IO 라고도 함) 바로 여기에 성능개선 효과가 있는것이다. 이기능에 의해서 rowid 에 의한 테이블 access 는 8i 에 비해서 상당한 개선 효과가 있는 것이다.(Operation ID 로는 3번이 여기 해당된다.)

이것을 증명하기 위해 v$sesstat 에서 SQL 수행전과 수행후의 value 증가분을 비교해보면 아래와 같다.

NAME DIFF
---------------------------------------- ----------
undo change vector size    2840
physical read IO requests    3812
... 중간생략
physical reads cache prefetch    1344

위에서 보는것과 같이 table prefetch 가 발생하였다. 위의 테스트는 11g 에서 수행된것인데 9i 의 살행계획과 실행통계도 10g 와 대동소이 하다. 11g 에서 이전 버젼(9i/10g) 번젼의 plan 을 나타나게 하려면 NO_NLJ_BATCHING(테이블명) 힌트를 사용하면 된다. 9i 나 10g 에서의 후행 테이블 prefetch에 의한 성능 개선효과는 11g 에 와서야 완벽한 모습을 갖추게 된다.

11g Plan --> Index Vector I/O
------------------------------------------------------------------------------------------------------
|Id | Operation | Name    | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | |    1 |00:00:04.82 | 245K| 1454|
| 2 | VIEW    | | 8269 |00:00:04.81 | 245K| 1454|
| 3 |    NESTED LOOPS | | 8269 |00:00:04.79 | 245K| 1454|
| 4 | NESTED LOOPS    | |    307K|00:00:01.56 | 41304 | 1454|
| 5 |    TABLE ACCESS FULL    | CUSTOMERS | 20010 |00:00:00.08 |    1457 | 1454|
| 6 |    INDEX RANGE SCAN | SALES_CUST_IX |    307K|00:00:00.47 | 39847 |    0|
| 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 8269 |00:00:01.93 | 203K|    0|
------------------------------------------------------------------------------------------------------

Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
... 중간생략
NLJ_BATCHING(@"SEL$2" "S"@"SEL$2")
END_OUTLINE_DATA
*/

궁하면 통한다.
이상하지 않은가? 테이블이 2개 인데 Nested Loop Join 이 하나가 아닌 2개가 되어버렸다. 또한 NLJ_PREFETCH 힌트가 사라지고 NLJ_BATCHING 힌트로 대체 되었다.
이러한 현상이 의미하는 바는 무엇일까?
9i/10g 에서 table prefetch 기능이 추가되었지만 index scan 에 관해서는 그런기능이 없었다.
드디어 11g 에서 index scan 시 Vector IO 가 가능해졌다. 궁하면 통한다고 했던가? 오라클이 Nested Loop Join 에 대하여 지속적으로 개선해왔다는것을 알수있다.
참고로 NO_NLJ_BATCHING 힌트를 사용하면 9i/10g 의 Plan 으로 돌아가게 된다.

그러면 11g 의 버젼에서 v$sesstat 통계를 보자.

NAME DIFF
---------------------------------------- ----------
Batched IO vector block count    3758
Batched IO vector read count 50
... 이후 생략

위에서 보듯이 Batched IO 란것이 생겼다.
Batched IO (혹은 Vector IO) 기능에 힘입어 table prefetch 에 이어서 11g 에서는 index scan 의 성능까지 향상되었다.

주의사항 : 위에서 수행한 모든 테스트는 Physical read 시에만 해당된다. 위의 예제 스크립트에 buffer cache 를 flush 한 이유도 여기에 있다.

결론:
오라클 9i, 10g 및 11g 에서 개선된 Nested Loop Join 의 원리는 다음과 같다.
첫번째는 9I/10g 에서 후행 테이블의 TABLE ACCESS BY INDEX ROWID Operation의 작업속도가 개선되었다는것과 두번째로 11g 에서 후행 테이블의 인덱스 scan 속도까지 획기적으로 개선되었다는 것이다. 이것은 table prefetch 기능과 Vector I/O 기능에 의해서 각각 구현 되었다. 이기능들과 별도로 이글의 서두에서 이야기한 buffer pinning 기능까지 덤으로 따라오게 되었다.

앞으로 11g 를 사용시 과거처럼 Batch 용 SQL 에서 무조건 hash 조인을 남발하지 말았으면 한다.
조인건수가 많지 않고 후행 테이블에 적당한 인덱스가 있을 경우에 최소한 Nested Loop Join 과 성능비교를 해보아야 하지않을까?

<편집후기 : 위 테스트는 11g 에서 테스트 되었음을 다시한번 밝혀둡니다.>

'Oracle > Data Join Method' 카테고리의 다른 글

같은 테이블을 두 번 읽었지만 일량이 틀려요 (16)	2011.01.14
Oracle 조인 방법 25가지 (15)	2010.02.26
Hash Join Right (Semi/Anti/Outer) 의 용도 (17)	2009.03.02
Full Outer Join 의 비밀 (5)	2009.02.23
Hash 조인시 Bushy tree Plan 유도하기 (4)	2008.11.01

Posted by extremedb

,

About DBMS_XPLAN - 2.포맷 설정하기

Oracle/Performance Analysis 2009. 4. 28. 11:09

DBMS_XPLAN 패키지의 장점은 포맷을 자유로이 설정한다는것
작년 겨울에 About DBMS_XPLAN - 1.실행계획 이라는 글에서 실행계획의 세부항목을 소개한바 있다.
이 패키지의가장 뛰어난 특징은 사용자가 출력 포맷을 설정하여 원하는 정보만 얻을수 있다는 것이다.
따라서 이번에는 DBMS_XPLAN 패키지 사용시 포맷설정을 자유롭게 하기 위한 Format Controller를 소개하려 한다.

Format Controller는 아래와 같이 3가지 종류가 있다.

1) 기본 Format Controller : 반드시 적용되어야 하는 기본적인 Controller 이다.
적용하지 않더라도 자동으로 기본값으로 적용된다.

2)세부 Format Controller: 기본 포맷정보에 의해서 표시되거나 생략되는 되는 세부적인 포맷을 Control 한다.
이 Control은 + 표시로 추가하거나 - 표시로 생략이 가능하다.

3)실행통계 Format Controller: 이 Control을 적용하면 실행시의 PGA 통계를 출력한다.

이제 한가지씩 상세히 살펴보자.

1) 기본 Format Controller
1.basic    : 가장 기본적인 포맷으로서 id, Operation, Object Name을 출력한다.
2.typical : basic 옵션에서 한발더 나아가서 옵티마이져가 에상할수 있는 모든것들을 보여준다.
   출력되는 정보로는 예상 row, 예상 bytes, 예상 temporary space 사용량, cost, 예상시간,
   Predicate Information(Operation 별로 access 및 filter 정보) 이다.
3.serial : typical 과 같으나 parallel 쿼리사용시 관련 정보가 나오지 않는다.
4.all : plan 정보는 typical 과 같으나 plan 이외의 정보중에서 Outline Data 정보를 제외하고 전부 출력한다.
5.advanced : all 과 같지만 Peeked Binds, Outline Data, note 등을 더보여준다.

2) 세부 Format Controller
1.alias :Operation 별로 쿼리블럭명과 object alias 를 control 한다.
   plan 의 하단에 위치하며 쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우 유용하다.
2.bytes : plan 상의 E-Bytes 정보를 control 한다.
3.cost :plan 상의 Cost (%CPU)를 control 한다.
4.note : 결과중 가장 마지막에 위치하며 여러가지 유용한 정보를 보여준다.
예를 들면 dynamic sampling 이 사용되었는지의 혹은 plan_table 이 old 버젼이므로 새로만들어야
한다는 등의 유용한 정보를 나타낸다.
5.outline : Outline Data를 control 한다. USER 가 작성한 힌트와 옵티마이져가 추가한 내부적인 힌트들이
   포함된다. 쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우 유용하다.
6.parallel : PARALLEL 쿼리인경우 TQ, IN-OUT, and PQ Distrib 등의 정보를 control 한다.
7.partition :파티션 ACCESS 가 포함된경우 Pstart(시작 파티션) and Pstop(종료 파티션) 등의 정보를 control 한다.
8.peeked_binds : BIND 변수의 값을 control 한다. 단 _optim_peek_user_binds 파라미터의 값이 TRUE 로
되어있는 경우만 해당되며 파라미터는 세션단위로 수정이 가능하다. EXPLAIN PLAN 을
사용한 경우에는 나타나지 않는다.
9.predicate : Predicate Information을 control 한다. Operation 별로 access 및 filter 정보를 나타낸다.
   일반적인 튜닝시 가장 눈여겨 보아야할 정보이다.
10.projection : projection information을 control 한다. Operation 별로 select 되는 컬럼정보를 나타낸다.
11.remote : DBLINK 를 사용힐때 REMOTE 쿼리의 수행정보를 control 한다.
12.rows : plan 상의 E-Rows수를 control 한다.

3) 실행통계 Format Controller
이정보들은 DBMS_XPLAN.DISPLAY 함수에는 적용되지 않는다. 왜냐하면 explain plan 은 쿼리가 실제 수행되는것이 아니므로 실행통계정보가 없기 때문이다.
또한 DBMS_XPLAN.DISPLAY_CURSOR 나 DBMS_XPLAN.DISPLAY_AWR 등의 함수 수행시에도 GATHER_PLAN_STATISTICS 힌트를 주거나 아니면 파라미터 STATISTICS_LEVEL = ALL 로 되어 있어야 출력이 가능하다.

1.allstats : I/O 통계정보(Buffers, Reads, Writes)와 PGA 통계정보(OMem, 1Mem , Used-Mem, Used-Tmp,
Max-Tmp 등)를 동시에 control 한다.
2.iostats : I/O 통계정보(Buffers, Reads, Writes)를 control 한다.
3.last : 실행통계 출력시 이 control을 명시하면 가장마지막에 수행된 실행통계를 출력한다.
이 control을 명시하지 않으면 실행통계의 누적치를 출력하므로 주의가 필요하다.
4.memstats :PGA 통계정보(OMem, 1Mem , Used-Mem, Used-Tmp, Max-Tmp 등)를 control 한다.
5.runstats_last : iostats control 과 last control 을 합친것과 같다.
이 control은 Oracle 10g Release 1 에서만 사용할수 있다.
6.runstats_tot : iostats control과 동일하다. 이 control은 Oracle 10g Release 1 에서만 사용할수 있다.

주의사항 : runstats_last 와 runstats_tot 를 제외한 4가지의 control은 Oracle 10g Release 2 에서만 사용할수 있다.

그럼 이제 적용해볼까?
위에서 설명한 Controller 를 이용하여 Format 을적용해보자.

SElECT /*+ gather_plan_statistics */ *
FROM EMP E
WHERE E.DEPTNO = :B1
AND ROWNUM <= 100
ORDER BY EMPNO;

이후로는 위의 SQL 은 동일하므로 생략된다.

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'advanced allstats last' ) );

아래의 plan 은 지면관계상 잘려서 2줄로 나타내었음을 이해해주기 바란다.

----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp |
----------------------------------------------------------------------------------
| 1 | SORT ORDER BY    |    |    1 |    100 | 2600 | 153M|
|* 2 | COUNT STOPKEY |    |    1 |    | | |
| 3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    1 | 2002K|    49M| |
|* 4 | INDEX RANGE SCAN    | EMP_N1 |    1 | 2003K| | |
----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------
19034 (1)| 00:03:49 |    9 |00:00:00.01 | 4 | 2048 | 2048 | 2048 (0)|
|    |    9 |00:00:00.01 | 4 | | |    |
4126 (1)| 00:00:50 |    9 |00:00:00.01 | 4 | | |    |
   989 (1)| 00:00:12 |    9 |00:00:00.01 | 3 | | |    |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / E@SEL$1

Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_bloom_filter_enabled' 'false')
OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1)
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) "E"."EMPNO"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7]
   2 - "E"."EMPNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7], "E"."DEPTNO"[NUMBER,22]
   3 - "E"."EMPNO"[NUMBER,22], "E"."EMPNO_VARCHAR"[VARCHAR2,40], "E"."JOB"[VARCHAR2,2], "E"."HIREDATE"[DATE,7], "E"."DEPTNO"[NUMBER,22]
   4 - "E".ROWID[ROWID,10], "E"."DEPTNO"[NUMBER,22]

'advanced allstats last' 포맷은 출력되는 정보가 너무많아
'advanced allstats last' 포맷을 적용하였으므로 DBMS_XPLAN.DISPLAY_CURSOR 가 보여줄수 있는 모든
정보를 출력 하였다. 단 지면 관계상 가장 처음에 나오는 SQL TEXT 와 sql_id, child number, plan_hash_value 등은 생략하였다. 많은정보를 생략하였음에도 불구하고 일반적인 튜닝시 필요가 없는 정보가 모두 출력되고 말았다.

이제 위에서 정의된 각 Controller 를 이용하여 여러분만의 Format 을 만들어보자.
필자의 경우 가장 선호하는 포맷은 아래의 두가지 이다.

권장되는 포맷유형 2가지

1.쿼리변형이 없는 단순 쿼리 튜닝의 경우:

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +predicate'));

포맷을 'allstats last -rows +predicate' 로 주었으므로 예측 row 수(E-row) 가 생략되고 실행통계와
Predicate Information 만을 출력한다.
아래의 plan 또한 너무길어 지면관계상 2줄로 나타내었다.

-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time |
-------------------------------------------------------------------------------
| 1 | SORT ORDER BY    |    |    1 |    9 |00:00:00.01 |
|* 2 | COUNT STOPKEY |    |    1 |    9 |00:00:00.01 |
| 3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    1 |    9 |00:00:00.01 |
|* 4 | INDEX RANGE SCAN    | EMP_N1 |    1 |    9 |00:00:00.01 |
-------------------------------------------------------------------------------

-------------------------------------
   Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------
   4 | 2048 | 2048 | 2048 (0)|
   4 | | |    |
   4 | | |    |
   3 | | |    |
-------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)

깔끔하게 꼭필요한 정보만 출력 되었다.

2.쿼리변형이 발생하거나 복잡한 쿼리 튜닝의 경우.

SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +alias +outline +predicate'));

'allstats last -rows +alias +outline +predicate' 포맷을 사용하면 Query Block Name / Object Alias 정보와 Outline Data 정보가 추가로 출력된다.
아래의 plan 도 지면관계상 2줄로 나타내었다.

-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time |
-------------------------------------------------------------------------------
| 1 | SORT ORDER BY    |    |    1 |    9 |00:00:00.01 |
|* 2 | COUNT STOPKEY |    |    1 |    9 |00:00:00.01 |
| 3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    1 |    9 |00:00:00.01 |
|* 4 | INDEX RANGE SCAN    | EMP_N1 |    1 |    9 |00:00:00.01 |
-------------------------------------------------------------------------------

-------------------------------------
   Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------
   4 | 2048 | 2048 | 2048 (0)|
   4 | | |    |
   4 | | |    |
   3 | | |    |
-------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / E@SEL$1

Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_bloom_filter_enabled' 'false')
OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1)
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMP"."DEPTNO"))
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=100)
   4 - access("E"."DEPTNO"=:B1)

쿼리변형이 발생한경우나 뷰(혹은 인라인뷰) 등을 튜닝할경우 아주 적합한 옵션이다. 하지만 특이한경우
Column Projection Information 이 필요한경우도 있다. 이경우는 +projection 포맷을 추가해주면 된다.

자신만의 적절한 포맷이 필요해
출력되는 정보의 양이 너무 많으면 소화 하기가 힘들고 너무 적으면 튜닝하기가 어려워진다. SQL이 아무리 복잡하고 다양한 경우가 있더라도 2~3 가지의 Format 조합으로도 충분하다. 여러분 각자의 입맛에 맞는 Format 을 개발해보길 바란다. 물론 그러기 위해서는 각각의 Controller 들과 친해질 필요가 있다.

'Oracle > Performance Analysis' 카테고리의 다른 글

Oracle에 SSD(Flash Disk)를 사용한다면 (8)	2011.05.04
Query Transformation - Filter Push Down (5)	2009.03.17
Internal Hint Transformation (0)	2009.03.09
About DBMS_XPLAN - 1.실행계획 (9)	2008.12.18
AWR, ADDM, ASH 성능보고서 가장쉽게 출력하기 (2)	2008.10.16

Posted by extremedb

,

오라클 공룡기업으로 거듭나는가?

Oracle/News 2009. 4. 21. 09:49

오라클, 선마이크로시스템즈 인수
"세계 2위의 소프트웨어 업체인 오라클은 서버와 저장장치 등 하드웨어 사업부문에 진출, IBM과 휴렛패커드(HP)등과 경쟁하게 됐다. 이와 함께 오라클은 선마이크로가 보유하고 있는 '자바 프로그래밍' 언어 기술과 대용량 전용 컴퓨터 운영 체계(OS)인 '솔라리스'에 대한 소유권도 확보하게 된다. 선마이크로 측에 따르면 자바 언어는 전세계적으로 약 8억대의 PC에서 운영되고 있으며 600만의 개발자들이 있는 것으로 알려졌다."
<기사 출처 :아시아 경제>

더아상 DBMS 업체로 보기 힘들어
OS, 미들웨어, 서버, 저장장치, 프로그래밍 언어... 이로서 IBM 이나 HP 와 3강체제가 확립되었고 MS 와는 프로그래밍 언어로서 대결하게 되었다. 프로그래밍 언어로서도 현재는 자바가 닷넷보다 우월한 위치에 있다. 이정도 되면 오라클이 더이상 DBMS 업체라고 부르기 힘들지 않을까?

중요한것은 시너지 효과
이제 오라클사 단독으로 프로젝트를 진행할수 있게 되었다. 미들웨어 업체나 OS 업체 , 저장장치 등등 의 타업체에게 도움을 받지 않아도 된다는 이야기다. 하지만 정작 중요한것은 ? 시너지 효과이다. 오라클이 시너지 효과를 발휘할지 두고 볼일이다.

육식 공룡이 아닌 상생의 공룡으로 거듭나길
몸집은 거대해졌지만 주위의 모든것을 먹어치워버리는 육식공룡이 아닌 영화 아이스 에이지에 나오는 그런 친근한, 협력업체를 껴안을수 있는 다정다감한 공룡으로 다시태어나길 기대한다.

<그림-영화 아이스 에이지의 포스터에서 발췌>

'Oracle > News' 카테고리의 다른 글

볼륨매니저는 역사 속으로 (12)	2009.09.30
오라클은 몇년이나 갈까? (4)	2009.07.31
Science of DataBase 1주년 (22)	2009.07.02

Posted by extremedb

,

Query Transformation Internal ( about JPPD using Lateral View )

Oracle/Optimizer 2009. 4. 20. 01:30

2007년 가을이었던가? 사내 컨설턴트로 부터 조금 어려운 질문이 들어왔다.
"다른종류의 쿼리변환은 모두 내가 수동으로 쿼리를 만들수 있습니다. 하지만 JPPD 는 쿼리를 만들수 없습니다.
혹시 오라클이 JPPD 를 적용한 SQL 을 보여줄수 있는지요?"

JPPD 는 수도없이 봐왔지만 막상 개념을 적용한 SQL 을 내손으로 작성하려 하니 전혀 작성할수가 없었다.
여러분이 알만한 미국및 영국의 유명한 컨설턴트들과 접촉을 해보았지만 역시 그들도 마찬가지였다. 필자는 이문제로 1주간 고생을한 끝에 직접 원리를 알아내었다. 따라서 어떤 메뉴얼에도 어떤 튜닝책에도 이런 이야기는 없음을 먼저 밝혀둔다.

JPPD ? 그게뭐야?
쿼리변환의 중요성을 알았으므로 이제 쿼리변환중에 가장 자주나오는 Unnesting 과 JPPD 의 개념을 알아보자.
Unnesting 이란 서브쿼리를 인라인뷰로 만들어 from 절로 끌어올리는 쿼리변환을 의미한다. JPPD 란 (Join Predicate Push Down)의 약자로서 인라인뷰 외부의 조인 조건이 인라인뷰 안쪽으로 파고드는 것을 의미한다.
물론 인라인뷰는 대신에 뷰로 사용해도 마찬가지 이다.

그럼 쿼리변환을 한번 해보자.
지난번 오라클에 트랜스포머가 있다? 라는 글에서 다단계 쿼리변환(Unnesting + View Merging) 사례를 설명한바 있다. 이번에는 다단계 쿼리변환 이면서 서브쿼리 Unnsting 후에 View Merging 이 실패하는 경우에 JPPD가 수행되는 사례를 알아보자.

환경 : Oracle 10.2.0.4

select /*+ gather_plan_statistics PUSH_PRED(OUTER) */
outer.*
from (SELECT * FROM emp outer
UNION ALL
SELECT * FROM emp outer) OUTER
where outer.sal > ( select /*+ QB_NAME(SUB) UNNEST */ avg(inner.sal)
from emp inner
where inner.deptno = outer.deptno
) ;

---------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS    | |    1 | 10 |00:00:00.03 |    27 |
| 2 | VIEW | VW_SQ_1 |    1 |    5 |00:00:00.02 | 7 |
| 3 |    HASH GROUP BY | |    1 |    5 |00:00:00.02 | 7 |
| 4 | TABLE ACCESS FULL    | EMP |    1 | 14 |00:00:00.02 | 7 |
| 5 | VIEW | |    5 | 10 |00:00:00.01 |    20 |
| 6 |    UNION ALL PUSHED PREDICATE | |    5 | 10 |00:00:00.01 |    20 |
|* 7 | TABLE ACCESS BY INDEX ROWID| EMP |    5 |    5 |00:00:00.01 |    11 |
|* 8 |    INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 5 |
|* 9 | TABLE ACCESS BY INDEX ROWID| EMP |    5 |    5 |00:00:00.01 | 9 |
|* 10 |    INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter("OUTER"."SAL">"VW_COL_1")
   8 - access("OUTER"."DEPTNO"="DEPTNO")
   9 - filter("OUTER"."SAL">"VW_COL_1")
10 - access("OUTER"."DEPTNO"="DEPTNO")

위실행계획은 쿼리변환이 2단계로 쿼리변환이 수행되었다.
지금부터 과정을 하나하나 살펴보자.

1.단계 : Unnesting 수행
먼저 서브쿼리가 인라인뷰로 바뀌어 from 절로 올라간다.
그리고 쿼리의 바깥쪽에 WHERE 조건이 생성되며 조인절도 생성된다.
이것은 아래의 쿼리와 같다.

select /*+ gather_plan_statistics LEADING(SUB OUTER) USE_NL(OUTER) NO_PUSH_PRED(OUTER) */
   outer.*
from (SELECT * FROM SI31041.emp outer    --> JPPD not yet
   UNION ALL
   SELECT * FROM SI31041.emp outer) OUTER ,
   ( select deptno, avg(sal) AS VW_COL_1
from emp
   group by deptno
   ) SUB
where outer.sal > SUB.VW_COL_1
and outer.deptno = SUB.deptno ;

------------------------------------------------------------------------------
| Id | Operation    | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------
| 1 | NESTED LOOPS    |    |    1 | 12 |00:00:00.01 |    50 |
| 2 | VIEW |    |    1 |    3 |00:00:00.01 | 7 |
| 3 |    HASH GROUP BY |    |    1 |    3 |00:00:00.01 | 7 |
| 4 | TABLE ACCESS FULL| EMP |    1 | 14 |00:00:00.01 | 7 |
|* 5 | VIEW |    |    3 | 12 |00:00:00.01 |    43 |
| 6 |    UNION-ALL |    |    3 | 84 |00:00:00.01 |    43 |
| 7 | TABLE ACCESS FULL| EMP |    3 | 42 |00:00:00.01 |    22 |
| 8 | TABLE ACCESS FULL| EMP |    3 | 42 |00:00:00.01 |    21 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(("OUTER"."SAL">"SUB"."VW_COL_1" AND "OUTER"."DEPTNO"="SUB"."DEPTNO"))

위의 Predicate Information을 보면 서브쿼리가 인라인뷰로 바뀌었지만 아직 인라인뷰 내로 WHERE 조건및
조인조건이 파고들지 못했다. 따라서 뷰를 만들고 FILTER 처리가 된것이다.

2단계: JPPD 수행
조인조건및 WHERE 조건이 UNION ALL 로 분리된 각각의 SQL 에 파고든다.
최종적으로 완성된 쿼리는 아래와 같다.

select /*+ push_pred (outer) */
outer.*
from (select /*+ unnest qb_name (sub) */
avg(inner.sal) vw_col_1,inner.deptno deptno
   from emp inner
group by inner.deptno
) vw_sq_1,
( select *    --> JPPD OK using lateral view
   from emp outer
where outer.deptno=vw_sq_1.deptno --> 조건이 인라인뷰 내로 들어옴 (Lateral view)
and outer.sal>vw_sq_1.vw_col_1
union all
   select *
   from emp outer
where outer.deptno=vw_sq_1.deptno --> 조건이 인라인뷰 내로 들어옴(Lateral view)
and outer.sal>vw_sq_1.vw_col_1
) outer

JPPD 의 비밀이 풀리다!
위의 SQL 에서 이상한점을 발견할수 있는가?
인라인뷰 OUTER 에서 다른 인라인뷰 VW_SQ_1 의 컬럼을 참조하고 있다. 이것은 놀라운 일이다. 인라인뷰 내에서 마치 스칼라 서브쿼리처럼 from 절의 다른 테이블 혹은 다른 인라인뷰의 정보를 사용한것이다. 바로 이것이 Lateral View 의 개념이다. 다시말하면 Lateral View란 스칼라 서브쿼리처럼 사용할수 있는 "스칼라 인라인뷰" 라고 생각하면 된다. 위의 SQL 을 보면 아래의 실행계획과 같을수 밖에 없다는것을 알수 있다.

---------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS    | |    1 | 10 |00:00:00.03 |    27 |
| 2 | VIEW | VW_SQ_1 |    1 |    5 |00:00:00.02 | 7 |
| 3 |    HASH GROUP BY | |    1 |    5 |00:00:00.02 | 7 |
| 4 | TABLE ACCESS FULL    | EMP |    1 | 14 |00:00:00.02 | 7 |
| 5 | VIEW | |    5 | 10 |00:00:00.01 |    20 |
| 6 |    UNION ALL PUSHED PREDICATE | |    5 | 10 |00:00:00.01 |    20 |
|* 7 | TABLE ACCESS BY INDEX ROWID| EMP |    5 |    5 |00:00:00.01 |    11 |
|* 8 |    INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 5 |
|* 9 | TABLE ACCESS BY INDEX ROWID| EMP |    5 |    5 |00:00:00.01 | 9 |
|* 10 |    INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter("OUTER"."SAL">"VW_COL_1")
   8 - access("OUTER"."DEPTNO"="DEPTNO")
   9 - filter("OUTER"."SAL">"VW_COL_1")
10 - access("OUTER"."DEPTNO"="DEPTNO")

오라클만이 Lateral View를 사용할수 있다.
아쉽게도 Lateral View 는 오라클만이 내부적으로 사용할수 있다. 필자나 여러분이 사용할 경우 에러가 발생한다. 그렇다면 위의 SQL은 어디서 나온것인가? 그것은 쿼리 트랜스포머의 쿼리변환작업을 10053 이벤트를 이용하여 Trace 파일에서 추출한 결과 이다.

결론:
이상으로 우리는 2가지 사실을 알아내었다.
첫번째는 서브쿼리 Unnsting 후에 View Merging 이 실패하는 경우에 JPPD를 시도한다는것.
두번째는 쿼리 트랜스포머는 JPPD 수행시 Lateral View를 사용한다는것이다.
마지막으로 가까운 미래에 Lateral View를 User가 직접 사용할수 있는날을 기대하면서 이글을 마치고자 한다.

Further Reading :
Lateral View 개념 : http://scidb.tistory.com/search/lateral%20view
SubQuery Unnesting : http://scidb.tistory.com/entry/SubQuery-Using-Method-1

'Oracle > Optimizer' 카테고리의 다른 글

11g DBMS_STATS 패키지 성능개선의 3가지 원리 (3)	2009.06.15
PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라. (2)	2009.06.09
오라클에 트랜스포머가 있다? (4)	2009.04.15
Access Sub Query 의 함정 (4)	2008.11.19
오라클은 얼마나 똑똑한가? (3)	2008.10.10

Posted by extremedb

,

오라클에 트랜스포머가 있다?

Oracle/Optimizer 2009. 4. 15. 09:59

부제목: 다단계 쿼리변환 (Muti-Phase Query Transformation)

SF 영화 트랜스포머를 보면 자동차가 로봇으로 변환하는 과정이 있다. 자동차와 로봇간의 변환과정은 아주 현란하다 못해 활홍하여 시청자자로 하여금 넋을 놓고 빠져들게 한다. 컴퓨터그래픽(CG) 기술의 발전 덕분이다.

변환과정이 있어야 지구를 지킬수 있어
만약 이 영화에서 자동차가 로봇으로 변환을 못한다고 상상해보자. 악한 로봇이 쳐들어와도 싸울수가 없고 격렬한 전투장면도 사라진다. 이래서는 영화가 재미없을 뿐더러 지구를 지킬수도 없다. 그럼 오라클에서 Query Transformer 가 없어진다면 어떻게 될까? 마찬가지로 Query 의 상당부분을 튜닝할수 없게 되어 전체 시스템이 느려지게된다. Query Transformer 의 목적은 성능향상에 있다.

오라클에도 트랜스포머가 있다.
오라클 Optimizer 에서 Query Transformer 는 3대 Components 로서 아주 중요한 위치에 있다.
먼저 Query Transformer 를 이해하기 위해서 Optimizer 구조를 살펴볼 필요가 있다.

먼저 Query Parser 가 SQL 을 검사하여 넘겨주면 Transformer 가 SQL 을 변신시켜서 Estimator 에 넘겨준다.
이때 Estimator는 통계정보등을 참조하여 가장 낮은 cost 를 갖는 SQL 을 찾아내어 Plan Generator 에 넘겨주고 실행계획을 완성하게 된다. 사실 위의 그림은 오라클 Performance Tuning Guide 에 있는 그림 이지만 잘못된 것이 있다. Query Transformer 가 Estimator 에게 주는 SQL 은 하나이상이 될수 있으므로 Estimator 와 Plan Generator 의 관계처럼 반복적인 Loop 가 있어야 한다.

변환과정도 로봇에 따라 다양하다.
트랜스포머에서 주인공 로봇의 변환과정은 아주 복잡하다. 하지만 소형 악당 로봇이 카세트 레코더로 변환하는 과정을 유심히 보았는가? 이 과정은 매우 간단하다. 오라클의 쿼리변환(Query Transformation) 과정도 간단한 것에서 부터 아주 복잡한 과정을 거치는 것 까지 다양하다.

구슬이 서말이라도 꿰어야 보배
오늘은 조금 어려운 다단계 쿼리변환-(Muti-Phase-Query Transformation)에 대하여 알아보려 한다.
참고로 아래의 글이 이해하기 힘든 독자는 필자의 이전글 Using Sub query Method (Sub query Flattening ) 과 Using Sub query Method( Filter / Access sub Query ) 를 먼저 읽어보기 바란다.
그럼 각 단계별로 변환과정을 보자.

1 단계 : 원본 쿼리
자신이 속한 부서의 평균급여 보다 돈을 많이 받는 사원을 추출하는 예제이다.

select /*+ gather_plan_statistics */ outer.*
from emp outer
where outer.sal > ( select /*+ NO_UNNEST */ avg(inner.sal)
from emp inner
where inner.deptno = outer.deptno
);

--------------------------------------------------------------------------------------------
| Id | Operation | Name    | Starts | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
|* 1 | FILTER | |    1 |    5 |00:00:00.01 |    16 |
| 2 | TABLE ACCESS FULL | EMP |    1 | 14 |00:00:00.01 | 8 |
| 3 | SORT AGGREGATE    | |    5 |    5 |00:00:00.01 | 8 |
| 4 |    TABLE ACCESS BY INDEX ROWID| EMP |    5 | 13 |00:00:00.01 | 8 |
|* 5 | INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">)
   5 - access("INNER"."DEPTNO"=:B1)

전통적인 Filter Subquery(확인자 SubQuery) 이다.

2.단계 : 서브쿼리를 인라인뷰로 바꿔라.
이 단계에서 unnest 힌트를 사용함으로서 Subquery 가 인라인뷰로 바뀌며 서브쿼리가 없어진다. 이때 메인쿼리의 건수를 유지하기 위해 인라인뷰에 group by 가 추가된다.

select /*+ gather_plan_statistics */ outer.*
from emp outer
where outer.sal > ( select /*+ QB_NAME(SUB) UNNEST */ avg(inner.sal)
from emp inner
where inner.deptno = outer.deptno
);

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name    | Starts | A-Rows | A-Time | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP |    1 |    5 |00:00:00.01 |    16 |    |
| 2 | NESTED LOOPS    | |    1 | 19 |00:00:00.09 |    10 |    |
| 3 |    VIEW | VW_SQ_1 |    1 |    5 |00:00:00.01 | 7 |    |
| 4 | HASH GROUP BY | |    1 |    5 |00:00:00.01 | 7 | 1622K (0)|
| 5 |    TABLE ACCESS FULL    | EMP |    1 | 14 |00:00:00.01 | 7 |    |
|* 6 |    INDEX RANGE SCAN | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 3 |    |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">"VW_COL_1")
   6 - access("DEPTNO"="OUTER"."DEPTNO")
   filter("OUTER"."DEPTNO" IS NOT NULL)

이것은 Optimizer가 쿼리를 아래처럼 변형시킨것이다.

select /*+ gather_plan_statistics */
   outer.*
from emp outer,
   ( select deptno, avg(sal) AS VW_COL_1
      from emp
group by deptno
) A
where outer.sal > A.VW_COL_1
   and outer.deptno = A.deptno ;

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name    | Starts | A-Rows | A-Time | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP |    1 |    5 |00:00:00.01 |    16 |    |
| 2 | NESTED LOOPS    | |    1 | 19 |00:00:00.13 |    10 |    |
| 3 |    VIEW | |    1 |    5 |00:00:00.01 | 7 |    |
| 4 | HASH GROUP BY | |    1 |    5 |00:00:00.01 | 7 | 1622K (0)|
| 5 |    TABLE ACCESS FULL    | EMP |    1 | 14 |00:00:00.01 | 7 |    |
|* 6 |    INDEX RANGE SCAN | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 3 |    |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OUTER"."SAL">"A"."VW_COL_1")
   6 - access("OUTER"."DEPTNO"="A"."DEPTNO")
   filter("OUTER"."DEPTNO" IS NOT NULL)

2단계의 원본 쿼리와 Plan 이 일치함을 알수 있다.

3단계 : 인라인뷰를 해체하라.
MERGE 힌트를 사용함으로서 2단계에서 Unnesting 된 인라인뷰를 해체하여 조인으로 바뀌었다. 이것을 View Merging 이라고 부른다.

select /*+ gather_plan_statistics MERGE(@SUB) */
outer.*
from emp outer
where outer.sal > ( select /*+ QB_NAME(SUB) UNNEST */ avg(inner.sal)
from emp inner
where inner.deptno = outer.deptno
);

다시말하면 위의 쿼리를 Optimizer가 아래처럼 재작성 한것이다.

select /*+ gather_plan_statistics */
   outer.deptno deptno,outer.sal sal,
   outer.empno empno
   from emp inner,
emp outer
where inner.deptno=outer.deptno
group by inner.deptno, outer.rowid, outer.empno, outer.sal, outer.deptno
having outer.sal > avg(inner.sal) ;

메인쿼리의 결과집합을 보존하기위하여 rowid 로 Group by 를 한것에 유의하자.
두개의 Query Plan 은 동일하며 아래와 같다.

-----------------------------------------------------------------------------------------------
| Id | Operation    | Name    | A-Rows | A-Time | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------
|* 1 | FILTER    | |    5 |00:00:00.01 |    12 |    |
| 2 | HASH GROUP BY    | | 13 |00:00:00.01 |    12 | 1103K (0)|
| 3 |    MERGE JOIN    | | 51 |00:00:00.01 |    12 |    |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 13 |00:00:00.01 | 5 |    |
|* 5 |    INDEX FULL SCAN | IX_EMP_N3 | 13 |00:00:00.01 | 1 |    |
|* 6 | SORT JOIN    | | 51 |00:00:00.01 | 7 | 2048 (0)|
|* 7 |    TABLE ACCESS FULL | EMP | 13 |00:00:00.01 | 7 |    |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">AVG("INNER"."SAL"))
   5 - filter("INNER"."DEPTNO" IS NOT NULL)
   6 - access("INNER"."DEPTNO"="OUTER"."DEPTNO")
   filter("INNER"."DEPTNO"="OUTER"."DEPTNO")
   7 - filter("OUTER"."DEPTNO" IS NOT NULL)

드디어 1~3 단계에 걸친 Query Transformation 단계가 완성 되었다. 그결과는 성능면에서 대성공이다. Buffers(읽은 Block수) 가 16(원본쿼리) 에서 12 로 약 25% 감소했다.

오라클 트랜스포머는 악성쿼리와 싸워...
오라클 Query Transformer 는 SQL 을 멋지게 변화시켰다. 이모든 과정을 개발자가 해야한다고 상상해보자.
개발자들에게 전체과정을 이해시키는 교육과정이 추가되어야 하고 개발속도는 몇배나 느려질것이다. 이는 프로젝트의 Risk 가 될것이다. 하지만 오라클 Query Transformer 가 있으므로 악당 로봇이 아닌 악성쿼리와 멋지게 싸워서 이길수 있는 것이다.

편집후기 :
Query Transformation 을 하려면 반드시 unnesting 이나 merge 힌트를 써야 하는지 질문이 들어왔다. 대부분의 경우 Query Transformer 가 자동으로 변환과정을 수행해준다. 하지만 이것이 가끔 제대로 수행이 안될수 있으므로 이럴경우에만 명시적으로 힌트를 사용하는것이 바람직하다.

'Oracle > Optimizer' 카테고리의 다른 글

PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라. (2)	2009.06.09
Query Transformation Internal ( about JPPD using Lateral View ) (0)	2009.04.20
Access Sub Query 의 함정 (4)	2008.11.19
오라클은 얼마나 똑똑한가? (3)	2008.10.10
Using Sub query Method( Filter / Access sub Query ) (9)	2008.09.29

Posted by extremedb

,

Top SQL-( Rownum 의 정확한 사용법 )

Oracle/SQL Tuning 2009. 3. 26. 13:42

PAGE 처리 할때나 아니면 부분범위 처리를 할때 우리는 TOP SQL 을 많이 사용한다.
오늘은 rownum 을 이용한 TOP N건 처리시 몇가지 함정이 있으므로 정확한 사용법에 대해서 알아보고자 한다.
먼저 환경을 만들기 위하여 테이블및 인덱스를 생성 한다.
환경 : ORACLE 11.1.0.6

create table scott.big_emp as    --> BIG_EMP 생성
select rownum as empno, a.ename, a.job, a.mgr, a.hiredate, a.sal + rownum as sal, a.comm, a.deptno
from scott.emp a,
   (select level as no from dual connect by level <= 2000) b
;

create index scott.ix_emp_01 on scott.big_emp (deptno, sal) --> deptno, sal 로 구성된 결합 인덱스 생성
;

create table scott.new_dept as    --> NEW_DEPT 생성
select a.* , a.deptno as page_count --> 추가적으로 page_count 컬럼생성
from scott.dept a
;

alter table scott.new_dept add --> PK 및 UNIQUE 인덱스 생성
( constraint pk_new_dept primary key (deptno) using index );

begin --> 통계정보 생성
dbms_stats.gather_table_stats('scott', 'big_emp', cascade => true);
dbms_stats.gather_table_stats('scott', 'new_dept', cascade => true);
end;

EMP 와 DEPT 를 이용하여 테이블및 인덱스를 생성 하였고 new_dept 테이블에는 부서별로 PAGE COUNT 라는 컬럼이 추가적으로 생성 되었다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics */ e.*
from (select /*+ index_desc(e IX_EMP_01) */
e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname
   from big_emp e,
new_dept d
   where e.deptno = d.deptno
and d.deptno = 20
order by e.sal desc) e
where rownum < :v_page_count --> :v_page_count 에 숫자 20을 대입한다.
;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

20번 부서에 해당하는 사원중에서 급여가 큰순으로 20건만 보겠다는 의미이다.

------------------------------------------------------------------------------------------------
| Id | Operation | Name    | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | |    1 | 20 |00:00:00.01 | 8 |
| 2 | VIEW    | |    1 | 20 |00:00:00.01 | 8 |
| 3 |    NESTED LOOPS | |    1 | 20 |00:00:00.01 | 8 |
| 4 | TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |    1 |    1 |00:00:00.01 | 2 |
|* 5 |    INDEX UNIQUE SCAN    | PK_NEW_DEPT |    1 |    1 |00:00:00.01 | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID | BIG_EMP |    1 | 20 |00:00:00.01 | 6 |
|* 7 |    INDEX RANGE SCAN DESCENDING| IX_EMP_01 |    1 | 20 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------

위 PLAN 상의 A-Rows 에 나타나듯이 부서번호 20번에 해당하는 모든건을 엑세스및 조인한것이 아니라
정확히 BIG_EMP 쪽을 20 건만 scan 하였다.
이것이 COUNT STOPKEY 의 위력이다.
즉 BIG_EMP 테이블에 deptno = 20 조건을 만족하는 건이 아무리 많더라도 20 건만 읽고 끝낸다는 것이다.

하지만 rownum < :v_page_count 조건대신에 부서별로 PAGE COUNT 를 테이블에 저장 하고
그 값을 이용하여 TOP SQL 을 작성 해야 한다면 이야기가 달라진다.
아래의 SQL 을 보면 부서별로 PAGE COUNT 를 지정 하고 있다.

select deptno, page_count
from new_dept;

<결과>
DEPTNO PAGE_COUNT
------ ----------
10 10
20 20
30 30
40 40

20번 부서의 경우 20 건만 보겠다는 의미이다.
이제 NEW_DEPT 테이블의 PAGE_COUNT 값으로 TOP SQL 을 작성해서 실행 해보자.

select /*+ gather_plan_statistics */ *
from (select /*+ index_desc(e IX_EMP_01) */
e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname, d.page_count
from big_emp e,
new_dept d
where e.deptno = d.deptno
and d.deptno = 20
order by e.sal desc) a
where rownum < a.page_count ;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 1 | COUNT | |    1 | 20 |00:00:00.01 |    6269 |
|* 2 | FILTER    | |    1 | 20 |00:00:00.01 |    6269 |
| 3 |    VIEW    | |    1 | 10000 |00:00:00.09 |    6269 |
| 4 | NESTED LOOPS | |    1 | 10000 |00:00:00.07 |    6269 |
| 5 |    TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |    1 |    1 |00:00:00.01 | 2 |
|* 6 | INDEX UNIQUE SCAN    | PK_NEW_DEPT |    1 |    1 |00:00:00.01 | 1 |
| 7 |    TABLE ACCESS BY INDEX ROWID | BIG_EMP |    1 | 10000 |00:00:00.04 |    6267 |
|* 8 | INDEX RANGE SCAN DESCENDING| IX_EMP_01 |    1 | 10000 |00:00:00.01 |    29 |
-------------------------------------------------------------------------------------------------

어이 없게도 20번 부서에 해당하는 전체건(10000 건)을 다 SCAN 하였다.
COUNT STOPKEY Operation 대신에 FILTER Operation 이 사용되었기 때문이다.
아래처럼 ROWNUM 조건을 인라인뷰 내로 밀어 넣어도 결과는 마찬가지 이다.

select /*+ gather_plan_statistics */ *
from (select /*+ index_desc(e IX_EMP_01) */
e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname, d.page_count
from big_emp e,
new_dept d
where e.deptno = d.deptno
and d.deptno = 20
and rownum <= d.page_count
order by e.sal desc)
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last'));

--------------------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 1 | VIEW    | | 10071 | 7898 (1)| 20 |00:00:00.01 |    6269 |
| 2 | COUNT    | |    |    | 20 |00:00:00.01 |    6269 |
|* 3 |    FILTER    | |    |    | 20 |00:00:00.01 |    6269 |
| 4 | NESTED LOOPS | | 10071 | 7898 (1)| 10000 |00:00:00.11 |    6269 |
| 5 |    TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |    1 | 1 (0)|    1 |00:00:00.01 | 2 |
|* 6 | INDEX UNIQUE SCAN    | PK_NEW_DEPT |    1 | 0 (0)|    1 |00:00:00.01 | 1 |
| 7 |    TABLE ACCESS BY INDEX ROWID | BIG_EMP | 10071 | 7897 (1)| 10000 |00:00:00.08 |    6267 |
|* 8 | INDEX RANGE SCAN DESCENDING| IX_EMP_01 | 10071 |    27 (0)| 10000 |00:00:00.05 |    29 |
--------------------------------------------------------------------------------------------------------------

그렇다면 이런 경우에 어떻게 TOP 처리를 할수 있겠는가?
이 문제는 ROWNUM 에 대한 기본을 알면 해결된다.
TOP N건 처리시에 ROWNUM 과의 비교대상은 반드시 상수(혹은 변수) 이어야 한다.
그렇지 않다면 TOP 처리는 실패한다.
아래의 SQL은 이 문제를 해결하였다.

select /*+ gather_plan_statistics */ *
from (select /*+ index_desc(e IX_EMP_01) */
e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname, d.page_count
from big_emp e,
new_dept d
where e.deptno = d.deptno
and d.deptno = 20
order by e.sal desc)
where rownum <= (select page_count from new_dept where deptno = 20) ;

select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last'));

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name    | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | |    |    | 20 |00:00:00.01 |    10 |
| 2 | VIEW    | | 10071 | 7898 (1)| 20 |00:00:00.01 | 8 |
| 3 |    NESTED LOOPS | | 10071 | 7898 (1)| 20 |00:00:00.01 | 8 |
| 4 | TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |    1 | 1 (0)|    1 |00:00:00.01 | 2 |
|* 5 |    INDEX UNIQUE SCAN    | PK_NEW_DEPT |    1 | 0 (0)|    1 |00:00:00.01 | 1 |
| 6 | TABLE ACCESS BY INDEX ROWID | BIG_EMP | 10071 | 7897 (1)| 20 |00:00:00.01 | 6 |
|* 7 |    INDEX RANGE SCAN DESCENDING| IX_EMP_01 | 10071 |    27 (0)| 20 |00:00:00.01 | 3 |
| 8 | TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |    1 | 1 (0)|    1 |00:00:00.01 | 2 |
|* 9 |    INDEX UNIQUE SCAN    | PK_NEW_DEPT |    1 | 0 (0)|    1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------------------

PLAN 에서 보는것과 같이 서브쿼리를 사용하여 PAGE COUNT 를 상수화 하였다.
즉 rownum <= (서브쿼리) 형태가 되면 COUNT STOPKEY Operation 을 정상적으로 사용할수 있다.
"같은 테이블을 반복하여 읽지마라." 라는 튜닝의 기본적인 원칙이 깨지는 순간이다.
하지만 모든 규칙은 예외가 있음을 기억해야 한다.

결론 :
위에서 보는 바와 같이 COUNT STOPKEY 와 FILTER Operation 은 천당과 지옥의 차이이다.
몇천만건 혹은 몇억건 이상인 환경에서 FILTER Operation 이 나온다면?
더이상 그 프로그램은 사용할수 없을것이다.
TOP SQL 처리시 주의사항은 ROWNUM 과 비교시 항상 상수(혹은변수)와 비교하거나 서브쿼리등을 이용하여 상수화 한후에 비교 하여야 한다는 것이다.

'Oracle > SQL Tuning' 카테고리의 다른 글

USE_CONCAT 힌트 제대로 알기 (5)	2009.07.17
분석함수를 이용한 TOP SQL은 튜닝이 불가한가? (5)	2009.05.22
인덱스없는 컬럼의 Order by 시 페이징 처리는 효율적인가? (2)	2008.08.26
다양한 검색조건의 튜닝방법 (3)	2008.08.05
DBMS_ADVANCED_REWRITE (1)	2008.04.15

Posted by extremedb

,

응용력 발휘하기

Oracle/SQL Pattern 2009. 3. 20. 13:17

SQL 작성시 응용력을 필요로 하는경우가 많이 있다.
아래의 예제도 그런상황중의 하나이다.
일별매출실적 테이블에서 sysdate 기준으로 현재월의 첫번째 월요일부터 현재월의 마지막 금요일 까지의 실적을 구해야 되는 요구사항이 있다.
문제의 핵심은 해당월의 첫번째 월요일과 마지막 금요일을 구하는 것이다.
2009년 3월 기준으로 생각하면 첫번째 월요일은 2009년 3월 2일 마지막 금요일은 2009 년 3월 27일 이다.

아래의 SQL 을 보자.

select substr(TO_CHAR(기준일자, 'YYYYMMDD'), 1,6) as 년월 , sum(매출실적) as 월실적
from 일별매출실적
where 기준일자 between :현재월의 첫번째 월요일 and :현재월의 마지막 금요일
group by substr(TO_CHAR(기준일자, 'YYYYMMDD'), 1,6)
;

위 SQL 은 변수를 2개 사용하고 있다.
:현재월의 첫번째 월요일과 :현재월의 마지막 금요일을 구하는 로직은 개발자가 자바 프로그램에서 작성할 예정이라고 한다.
그렇게 작성 할수는 있지만 로직이 길어지고 유지보수도 어려워지므로 Oracle DBMS 기능을 이용하여 쉽게 구하는 방법이 있는지 필자에게 물어보러 온 것이다.
아래의 SQL 을 보자

select next_day(trunc(sysdate,'mm')-1,'월요일') first_monday,
trunc(next_day(last_day(sysdate)-7,'금요일')) last_monday
from dual
;

FIRST_MONDAY LAST_MONDAY
------------ -----------
2009-03-02 2009-03-27

last_day, next_day 함수와 trunc 함수를 적절히 활용하면 문제를 쉽게 풀수 있다.
간단히 설명 하면 첫번째 월요일은 trunc(sysdate,'mm')-1 로 전달의 마지막 일자를 구하고 next_day 함수를 사용하여 다음 월요일을 구하면 된다.
마지막 금요일은 last_day 함수를 사용하여 해당월의 마지막 일자에서 7일을 뺀 결과를 next_day 함수를 사용하여 다음 금요일을 구하면 되는 것이다.

이렇게 하여 아래와 같은 simple 한 SQL 이 완성되었다.

select substr(TO_CHAR(기준일자, 'YYYYMMDD'), 1,6) as 년월 , sum(매출실적) as 월실적
from 일별매출실적
where 기준일자 between next_day(trunc(sysdate,'mm')-1,'월요일')
and trunc(next_day(last_day(sysdate)-7,'금요일'))
group by substr(TO_CHAR(기준일자, 'YYYYMMDD'), 1,6)
;

결론:
문제의 핵심을 분석하고 DBMS 의 기능을 최대한 활용하여 상황을 빨리 해결 하는 능력이 필요하다.
문제해결 능력은 분석력 + 지식 + 응용력 이다.
해당 SQL 의 경우 분석이나 지식보다는 응용력을 잘 발휘 해야되는 상황인 것 이다.

'Oracle > SQL Pattern' 카테고리의 다른 글

SQL 포기하지마라. (7)	2009.05.28
Upgrade Select For Update (3)	2009.05.19
Hypothetical Functions 은 어떨때 써야하나? (3)	2008.10.25
Hierarchical Queries (3)	2008.06.23
가로를 세로로 바꾸기 (0)	2008.05.27

Posted by extremedb

,

Query Transformation - Filter Push Down

Oracle/Performance Analysis 2009. 3. 17. 15:10

FPD(Filter Push Down)이란 뷰 바깥쪽의 조건을 뷰안으로 진입 시킨다는 의미이다.
FPD 는 자체로도 작동하지만 Subquery Flattening, View Merging, Join Predicate Pushdown 등이 진행될 때에도 부가적으로 실행되는 아주 중요한 Query Transformation 기법 이다.
FPD 와 관련하여 튜너및 DBA 들이 잘못알고 있는 사실이 있으므로 이것을 바로 잡고자 한다.
no_merge 힌트를 사용하면 뷰 바깥쪽의 조건들이 뷰 내로 파고들지 못한다는 미신이 있다.
과연 이것이 맞는 이야기인가?
먼저 테스트를 위하여 테이블을 하나 만들어야 한다.
환경 Oracle 11.1.0.6

CREATE TABLE scott.BIG_EMP AS --> big_emp 생성
SELECT ROWNUM AS EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL + rownum as SAL, A.COMM, A.DEPTNO
FROM scott.EMP A,
(SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2000) B;

ALTER TABLE scott.BIG_EMP --> PK 생성
ADD ( CONSTRAINT PK_BIG_EMP PRIMARY KEY (EMPNO) USING INDEX );

CREATE INDEX scott.BIG_EMP_SAL_IX ON scott.BIG_EMP (SAL); --> SAL 항목에 인덱스 생성

dbms_stats.gather_table_stats('SCOTT', 'BIG_EMP', cascade => true);

아래의 SQL 을 본다면 no_merge 힌트는 Filter Push Down(조건절을 뷰안으로 진입)을 방해하지 않음을 알수 있다.

select /*+ gather_plan_statistics */
   deptno, max_sal
from (select /*+ no_merge index(big_emp big_emp_sal_ix) */
   deptno, max(SAL) max_sal
from big_emp
   group by deptno)
where max_sal > 32900
;

--------------------------------------------------------------------------------------------------
| Id | Operation    | Name | Starts | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | VIEW    |    |    1 |    1 |00:00:00.01 | 3 |
|* 2 | FILTER |    |    1 |    1 |00:00:00.01 | 3 |
| 3 |    HASH GROUP BY |    |    1 |    1 |00:00:00.01 | 3 |
| 4 | TABLE ACCESS BY INDEX ROWID| BIG_EMP    |    1 |    7 |00:00:00.01 | 3 |
|* 5 |    INDEX RANGE SCAN    | BIG_EMP_SAL_IX |    1 |    7 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(MAX("SAL")>32900)
   5 - access("SAL">32900)

재미있는 점은 뷰 바깥의 max_sal > 32900 조건을 뷰내로 진입시키면서 아래처럼 where 절을 2단계로 추가 하였다.

1단계: having 절로 변환 --> having max(SAL) > 32900
2단계 :where 조건으로 변환 --> where sal > 32900

실제로 10053 Event 정보를 분석해보면 FPD(Filter Push Down) 가 2단계로 진행된다는 것을 알수 있다.
그러면 옵티마이져가 변경한 SQL 보자.

select /*+ gather_plan_statistics */
deptno, max_sal
from (select /*+ index(big_emp BIG_EMP_SAL_IX) */
   deptno, max(sal) max_sal
from big_emp
   where sal > 32900 --> 옵티마이져가 FPD 로 조건을 생성함.
   group by deptno
   having max(sal) > 32900 --> 옵티마이져가 FPD 로 조건을 생성함.
   )
;

--------------------------------------------------------------------------------------------------
| Id | Operation    | Name | Starts | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | VIEW    |    |    1 |    1 |00:00:00.01 | 3 |
|* 2 | FILTER |    |    1 |    1 |00:00:00.01 | 3 |
| 3 |    HASH GROUP BY |    |    1 |    1 |00:00:00.01 | 3 |
| 4 | TABLE ACCESS BY INDEX ROWID| BIG_EMP    |    1 |    7 |00:00:00.01 | 3 |
|* 5 |    INDEX RANGE SCAN    | BIG_EMP_SAL_IX |    1 |    7 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(MAX("SAL")>32900)
5 - access("SAL">32900)

Plan 및 Predicate Information 가 원본쿼리와 완전히 일치함을 알수 있다.

오히려 이런 경우는 원본쿼리에 merge 힌트를 사용하는것이 더 위험하다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics qb_name(outer) */
deptno, max_sal
from (select /*+ MERGE qb_name(inner) index(big_emp BIG_EMP_SAL_IX) */
deptno, max(sal) max_sal
from big_emp
group by deptno
)
where max_sal > 32900
;

--------------------------------------------------------------------------------
| Id | Operation | Name    | Starts | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------
|* 1 | FILTER | |    1 |    1 |00:00:00.02 | 177 |
| 2 | HASH GROUP BY | |    1 |    3 |00:00:00.02 | 177 |
| 3 |    TABLE ACCESS FULL| BIG_EMP |    1 | 28000 |00:00:00.03 | 177 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(MAX("SAL")>32900)

PLAN 정보의 Buffers(read 한 블럭수) 를 보면 일량이 3에서 177 로 늘어난 것을 알수 있다.
Group By 된 뷰가 merge 힌트에 의해서 해체 되었다.
Merge 되는 경우는 having 절만 생성되며 where 조건은 추가적으로 생성되지 않음을 알수 있다.
따라서 다음과 같은 SQL 로 변환된것을 알수 있다.

select deptno,
max(sal) max_sal
from big_emp
group by deptno
having max(sal) > 32900 ; --> 추가적인 where 절이 없음

위와 같이 변경된것을 Complex View Merging 이라고 한다.
Complex 가 추가적으로 붙은 이유는 group by 절이 있는 뷰를 Merge(해체) 했기 때문이다.

결론 :
집계함수를 사용하는 뷰의 바깥에서 집계함수의 결과를 filter 할경우 merge 힌트를 사용하면 뷰가 해체 되면서 Having 절로 변환이 되지만 where 조건은 생성되지 않으므로 인덱스가 있다고 해도 사용할수 없음을 알수 있다.
오히려 이런 경우는 no_merge 힌트를 사용하여 FPD 를 유도하는 것이 정답인 것이다.

'Oracle > Performance Analysis' 카테고리의 다른 글

Oracle에 SSD(Flash Disk)를 사용한다면 (8)	2011.05.04
About DBMS_XPLAN - 2.포맷 설정하기 (0)	2009.04.28
Internal Hint Transformation (0)	2009.03.09
About DBMS_XPLAN - 1.실행계획 (9)	2008.12.18
AWR, ADDM, ASH 성능보고서 가장쉽게 출력하기 (2)	2008.10.16

Posted by extremedb

,

Internal Hint Transformation

Oracle/Performance Analysis 2009. 3. 9. 20:09

Hash Join Right (Semi/Anti/Outer) 의 용도 라는 글에서 Internal Hint Transformation 개념을 설명한적이 있다.
오늘은 예제 몇가지에 대하여 소개하려 한다.
먼저 Internal Hint Transformation 이 왜 일어나는지 설명하기 위하여 힌트의 종류를 나누어 보자.
오라클 Performace Tuning Guide 에 보면 힌트의 Type 에 대해서 아래와 같이 분류하고 있다.

1.Single Table Hints : 하나의 테이블이나 인덱스에 대하여 사용하는 힌트
   index 관련 힌트나 use_nl, use_merge, use_hash 등이 여기 속한다.
2.Multi Table Hints : 여러 테이블이나 블럭에 대하여 사용하는 힌트.
   leading 힌트나 index_join, index_combine 등이 여기에 해당된다.
3.Query Block Hints: 하나의 쿼리블럭에 사용하는 힌트
   STAR_TRANSFORMATION, UNNEST, MERGE, PUSH_PRED, USE_CONCAT, NO_EXPAND 등이 여기에 해당된다.
4.Statement Hints : 전체 SQL 단위로 사용하는 힌트
all_rows, first_rows_n 등이 여기에 해당된다.

가장 흔한 힌트변환은 use_nl(a, b) 를 leading(a b) use_nl(b) 로 바꾸는 것이다.
use_nl 은 Single Table Hints 이기 때문이다.
또한 use_nl_with_index 등을 사용하여도 각각 index, use_nl 힌트등으로 바꾸어 버린다.
이중에서 가장 극적인 Internal Hint Transformation 예제는 Index_Combine 이다.
아래 예제를 보자.

환경 :10.2.0.4

먼저 Bit map 인덱스를 2개 만든다.
create bitmap index IX_EMP_N2 on emp(mgr);
create bitmap index IX_EMP_N3 on emp(deptno);

dbms_stats.gather_table_stats(user, 'EMP', cascade => true);

select /*+ gather_plan_statistics index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */
empno, mgr, deptno
from emp
where NOT( mgr = 7698 )
and deptno = 20;

select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last'))

오라클은 위의 힌트를 어떻게 바꿀까?
dbms_xplan.display_cursor 의 결과중에 Outline Data 를 보면 아래와 같다.
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_bloom_filter_enabled' 'false')
OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1)
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1""EMP"@"SEL$1" AND(("EMP"."DEPTNO") MINUS(("EMP"."MGR")) MINUS_NULL(("EMP"."MGR"))))
END_OUTLINE_DATA
*/

이상하지 않은가?
Bitmap_tree 라는 힌트는 사용하지도 않았다.
오라클은 Index_Combine 힌트를 Bitmap_Tree 힌트로 바꾼것이다.
이 암호와도 같은 힌트를 간단히 바꾸면 아래와 같다.
BITMAP_TREE(emp and( (emp.deptno) minus((emp.mgr)) minus_null((emp.mgr))))
간단히 설명하면 deptno = 20 을 만족하는 집합에서 mgr = 7698 을 만족하는 집합을 뺴주는것(minus) 이다.
그렇다면 남은 minus_null 힌트는 무엇일까?
이힌트는 부정형으로 Bit Map 비교시에만 나타난다.
다시말하면 bit map 연산시 mgr 이 7698 이 아닌것을 나타내면 mgr is null 인 데이터가 포함이 되어 버린다.
따라서 mgr is null 인 집합도 빠져야 하기 때문에 옵티마이져는 minus_null(mgr) 힌트를 사용한 것이다.
이것은 Bit map Operation 의 특성에서 나온것이다.
참고로 where 절에 mgr is not null 이라고 명시하거나 혹은 not null Constraints 를 주게되면 minus_null 힌트는 사라진다.

아래의 Predicate Information 를 보면 상세히 알수 있다.

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("DEPTNO"=20)    --> (("EMP"."DEPTNO")
   6 - access("MGR"=7698)    --> MINUS(("EMP"."MGR"))
   7 - access("MGR" IS NULL) --> MINUS_NULL(("EMP"."MGR"))

결론 : Query Transfomation 에는 Internal Hint Transformation 도 포함 되어야 한다.
   Internal Hint Transformation 는 힌트의 용법을 정확히 지키지 않으면 거의 모든곳에서 나타날수 있다.
   또한 용법을 정확히 사용하여도 내부적으로 변환시키는 경우가 많이 있다.
   그러나 이런것이 나타난다고 해서 걱정할 필요는 없다.
   또한 Internal 힌트를 사용할 필요도 없다.
단지 "옵티마이져가 내부적으로 이런일을 하고 있다." 라고 알고 있으면 당황하지 않을 것이다.

'Oracle > Performance Analysis' 카테고리의 다른 글

Oracle에 SSD(Flash Disk)를 사용한다면 (8)	2011.05.04
About DBMS_XPLAN - 2.포맷 설정하기 (0)	2009.04.28
Query Transformation - Filter Push Down (5)	2009.03.17
About DBMS_XPLAN - 1.실행계획 (9)	2008.12.18
AWR, ADDM, ASH 성능보고서 가장쉽게 출력하기 (2)	2008.10.16

Posted by extremedb

,

Hash Join Right (Semi/Anti/Outer) 의 용도

Oracle/Data Join Method 2009. 3. 2. 22:51

<2009.03.11 : 아래 내용중의 Right 의 의미는 Oracle 10g Performance Tuning Guide 19-21 에 나온대로 "올바른" 이란 뜻이 아니라 "Left 혹은 Right Outer Join 시의 Right" 의 의미로 바꿉니다.
하지만 이글의 핵심인 "이전버젼까지는 항상 후행집합으로 되던것이 10g 부터는 선행집합이 될수 있다." 는 바뀐것이 없습니다. >

오늘은 Hash Join Right (Semi/Anti/Outer) 의 용도에 대하여 알아보려한다.
Oracle 10g 부터 Hash Join 은 서서히 변화하기 시작하였다.
특히 Hash Join Right (Semi/Anti/Outer) 기능을 사용하여 대용량 집합의 Join 시 획기적인 성능향상을 이루었다.
Hash Join 에서 Right 옵션이 붙음으로서 획기적인 성능향상을 이루었다는 이유는 무엇일까?
Semi/Anti Join은 항상 메인 쿼리가 수행된 후 서브쿼리의 데이터를 체크하는 방식이다.
따라서 Semi/Anti Join 의 경우 서브쿼리는 항상 후행집합이 될수 밖에 없다.
Hash Outer Join 의 경우도 마찬가지로 (+) 표시가 붙는 쪽의 집합은 항상 후행집합이 될수 밖에 없었다.
하지만 10g 부터 Hash Join Right (Semi/Anti/Outer) 기능이 나오게 되면서 서브쿼리 혹은 아우터 Join 되는 쪽의 집합이 선행집합이 될수 있다.
이때 Right 의 뜻은 left 집합 대신에 right(후행집합)을 선행집합으로 하겠다는 뜻이다.
9i 까지 Hash Join (Semi/Anti/Outer)의 경우에 눈물을 머금고 대량의 집합을 선행처리할수 밖에 없었지만 이제는 자유롭게 선행집합을 선택할수 있는것이다.
국내외 튜닝책을 막론하고 이막강한 기능에 대하여 제대로 다루는 것을 본적이 없다.
왜냐하면 초대용량 DB(VLDB)에서 Join Method 를 튜닝해본 사람만이 이 기능이 얼마나 중요한지 피부로 느낄수 있기 때문이다.
아래의 스크립트를 보자.
환경 : 10.2.0.4

1.테이블을 생성하고 Aanlyze 를 한다.

1.테이블 생성
CREATE TABLE BIG_EMP AS
SELECT ROWNUM AS EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM, A.DEPTNO
FROM EMP A,
(SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2000) B;

ALTER TABLE BIG_EMP
ADD ( CONSTRAINT PK_BIG_EMP PRIMARY KEY (EMPNO) USING INDEX );

dbms_stats.gather_table_stats(user, 'BIG_EMP', cascade => true);

2.Hash Semi Join 을 유도한다.

select a.empno, a.sal
from big_emp a
where exists (select /*+ use_hash(b) */
      b.deptno
      from dept b
   where b.deptno = a.deptno
) ;

--------------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts |Cost (%CPU)| A-Rows | A-Time | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN RIGHT SEMI| |    1 |    4 (25)| 26000 |00:00:00.06 | 176 | 813K (0)|
| 2 | INDEX FULL SCAN    | PK_DEPT |    1 |    1 (0)|    4 |00:00:00.01 | 1 |    |
|* 3 | TABLE ACCESS FULL | BIG_EMP |    1 |    2 (0)| 26000 |00:00:00.01 | 175 |    |
--------------------------------------------------------------------------------------------------------

위의 통계정보를 보면 176 블럭을 scan 했으며 Hash area size 를 813 K를 사용했다는걸 알수 있다.
작은 용량의 테이블인 DEPT 를 Driving 집합(Build Input) 으로 선택하고 BIG_EMP 테이블을
후행(Probe) 테이블로 Hash 조인 함으로서 최적의 조인이 되었다.
그렇다면 Hash Join Right Semi 를 사용하지 않으면 어떻게 될것인가?
Subquery Unnesting 기능을 이용하면 작은 용량의 테이블인 DEPT 를 Driving 집합(Build Input) 으로 선택할수는 있다.
하지만 아래처럼 약간의 손해를 감수해야 한다.

select /*+ gather_plan_statistics ordered */ a.empno, a.sal
from big_emp a
where exists (select /*+ use_hash(b) */
b.deptno
from dept b
where b.deptno = a.deptno
);

위의 SQL 을 보면 강제로 MAIN 쿼리에 ordered 힌트를 주어 Semi Join 이 아닌 SubQuery Unnesting 이 되었다.
ordered 힌트를 사용한 이유는 서브쿼리가 Semi Join 에 실패할 경우 Subquery Unnesting 을 시도하게 되는데
이때 서브쿼리블럭이 From 절의 가장 좌측으로 오기 때문이다.
사용자가 ordered 힌트등을 사용하면 오라클 내부적인 leading 힌트와 Swap_join_inputs 힌트 등이 Override 되어 무시된다.
따라서 Semi Join 이 아닌 Subquery Unnesting 되는 것이다.
이제 Plan 을 보자.

------------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts |Cost (%CPU)| A-Rows | A-Time | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | |    1 |    5 (40)| 26000 |00:00:00.37 | 176 | 808K (0)|
| 2 | SORT UNIQUE | |    1 |    1 (0)|    4 |00:00:00.01 | 1 | 2048 (0)|
| 3 |    INDEX FULL SCAN | PK_DEPT |    1 |    1 (0)|    4 |00:00:00.01 | 1 |    |
|* 4 | TABLE ACCESS FULL| BIG_EMP |    1 |    2 (0)| 26000 |00:00:00.31 | 175 |    |
------------------------------------------------------------------------------------------------------

처음 예제와 조인순서와 Scan 한 블럭수및 Hash area size 사용량은 대동소이 하지만 Subquery Unnesting 이 발생하여 불필요한 Sort 가 발생 하였다.
위의 SQL 의 경우 Subquery Unnesting 은 메인쿼리의 결과집합을 보존하기 위하여 Sort Unique 혹은 Hash Unique 작업이 추가적으로 발생된다.
Subquery Unnesting 이 항상 나쁜것은 아니지만 대용량 집합간의 조인시는 엄청난 부담이 될수 밖에 없다.
서브쿼리쪽은 Sort Unique 작업이 추가적으로 필요하기 때문이다.
그렇다고 덩치가 큰 BIG_EMP를 선행테이블로 할수도 없는것이다.
이것이 바로 Hash Join Right Semi 가 10g 에서 나타난 이유이다.
그렇다면 이럴 경우에 강제로 Hash Join Right Semi 를 발생시키려면 어떻게 해야 되겠는가?
이럴때 간단하게 사용할수 있는것이 QB_NAME 을 이용한 Global 힌트와 USE_HASH 및 SWAP_JOIN_INPUT 힌트이다.
아래의 스크립트를 보자.

select /*+ gather_plan_statistics LEADING(A) USE_HASH(@sub B) SWAP_JOIN_INPUTS(@sub B) */ a.empno, a.sal
from big_emp a
where exists (select /*+ qb_name(sub) */
b.deptno
from dept b
where b.deptno = a.deptno
);

---------------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN RIGHT SEMI| |    1 | 4 (25)| 26000 |00:00:01.05 | 176 | 813K (0)|
| 2 | INDEX FULL SCAN    | PK_DEPT |    1 | 1 (0)|    4 |00:00:00.01 | 1 |    |
|* 3 | TABLE ACCESS FULL | BIG_EMP |    1 | 2 (0)| 26000 |00:00:00.99 | 175 |    |
---------------------------------------------------------------------------------------------------------

다시 정상적인 Hash Join Right Semi 로 돌아왔다.
간단히 힌트를 설명하자면 QB_NAME 은 쿼리블럭명을 강제로 지정하는 힌트이고 Swap_join_inputs 힌트는
Probe 쪽 집합(후행 집합) 을 강제로 Build Input 집합(선행집합) 으로 바꾸는 힌트이다.
그리고 Use_hash 힌트에 대하여 한마디 하자면 원래 Use_hash 힌트는 후행 집합에 대해서만 사용하는 힌트이다.
하지만 USE_HASH(A B) 이런식으로 사용해도 ORACLE 이 힌트를 아래처럼 변환시켜버린다.
USE_HASH(A B) --> LEADING(A B) USE_HASH(B)
오라클사에서 명시적인 용어가 없기 때문에 필자는 이것을 Internal Hint Transformation 이라 부른다.
다음에 기회가 되면 Internal Hint Transformation 에 대하여 글을 올릴까 한다.

결론 : 10g 부터 나온 Hash Join Right (Semi/Anti/Outer) 기능을 적재적소에 활용하면 대용량 집합간의 join 성능을 획기적으로 향상시킬수 있다.

참고로 Hash Join Right Anti Plan 으로 유도하는 것은 Exists 대신 Not Exists 로 바꾸면 된다.
Hash Join Right Outer 를 유도하는 예제는 아래와 같다.

select /*+ LEADING(A) USE_HASH(B) SWAP_JOIN_INPUTS(B) */ a.empno, a.sal
from big_emp a,
dept b
where a.deptno = b.deptno(+)

저작자표시 비영리 동일조건

'Oracle > Data Join Method' 카테고리의 다른 글

Oracle 조인 방법 25가지 (15)	2010.02.26
Nested Loop Join 성능향상과 관련된 2가지 원리 (17)	2009.05.09
Full Outer Join 의 비밀 (5)	2009.02.23
Hash 조인시 Bushy tree Plan 유도하기 (4)	2008.11.01
Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝) (3)	2008.10.30

Posted by extremedb

,

Full Outer Join 의 비밀

Oracle/Data Join Method 2009. 2. 23. 21:00

한 개발자가 Full Outer Join 에 대한 실행계획에 대하여 질문을 해왔다.
그개발자는 아래와 같은 이야기를 설명 하였다.

하나의 테이블 기준으로 outer Join 을 한다
Union all
반대편 테이블을 기준으로 다시 outer join 을 한다.

여러분들은 위의 이야기를 어떻게 생각하는가?
개발자의 이야기는 반(윗부분)은 맞고 반(아랫부분)은 틀리다.

환경은 Oracle 10g 버젼 10.2.0.4 이다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics */ a.empno, a.ename, b.dname
from emp a full outer join dept b
on (a.deptno= b.deptno) ;

-------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
| 1 | VIEW    | |    1 | 14 |    26 |    |
| 2 | UNION-ALL    | |    1 | 14 |    26 |    |
| 3 |    NESTED LOOPS OUTER    | |    1 | 14 |    23 |    |
| 4 | TABLE ACCESS FULL    | EMP |    1 | 14 | 8 |    |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT    | 14 | 13 |    15 |    |
|* 6 |    INDEX UNIQUE SCAN | PK_DEPT | 14 | 13 | 2 |    |
| 7 |    MERGE JOIN ANTI | |    1 |    0 | 3 |    |
| 8 | TABLE ACCESS BY INDEX ROWID| DEPT    |    1 |    4 | 2 |    |
| 9 |    INDEX FULL SCAN | PK_DEPT |    1 |    4 | 1 |    |
|* 10 | SORT UNIQUE    | |    4 |    4 | 1 | 2048 (0)|
|* 11 |    INDEX FULL SCAN | IX_EMP_N2 |    1 | 13 | 1 |    |
-------------------------------------------------------------------------------------------

윗부분은 분명히 Outer 조인으로 풀렸지만 아래부분은 ANTI 조인으로 풀렸으므로 이것은 서브쿼리를 사용한것이다.
즉 옵티마이져는 아래처럼 Query Transformation 을 하게 되는 것이다.

select /*+ gather_plan_statistics */ *
from (
   select a.empno, a.ename, b.dname
   from emp a, dept b
   where a.deptno = b.deptno(+)
   union all
   select null, null, a.dname
   from dept a
   where not exists (select 1
from emp b
where b.deptno = a.deptno
)
   ) ;

-------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
| 1 | VIEW    | |    1 | 14 |    26 |    |
| 2 | UNION-ALL    | |    1 | 14 |    26 |    |
| 3 |    NESTED LOOPS OUTER    | |    1 | 14 |    23 |    |
| 4 | TABLE ACCESS FULL    | EMP |    1 | 14 | 8 |    |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT    | 14 | 13 |    15 |    |
|* 6 |    INDEX UNIQUE SCAN | PK_DEPT | 14 | 13 | 2 |    |
| 7 |    MERGE JOIN ANTI | |    1 |    0 | 3 |    |
| 8 | TABLE ACCESS BY INDEX ROWID| DEPT    |    1 |    4 | 2 |    |
| 9 |    INDEX FULL SCAN | PK_DEPT |    1 |    4 | 1 |    |
|* 10 | SORT UNIQUE    | |    4 |    4 | 1 | 2048 (0)|
|* 11 |    INDEX FULL SCAN | IX_EMP_N2 |    1 | 13 | 1 |    |
-------------------------------------------------------------------------------------------
실행계획이 Full Outer Join 과 똑같음을 알수 있다.
하지만 이것도 어디까지나 파라미터 _optimizer_native_full_outer_join 가 off 일때 까지의 이야기이다.

아래의 스크립트를 보자.

/*+ opt_param('_optimizer_native_full_outer_join', 'force') */

위의 힌트를 사용하거나 아니면 아래처럼 ALTER SESSION 을 하면 된다.

alter session set "_optimizer_native_full_outer_join" = 'force';

select /*+ gather_plan_statistics */
a.empno, a.ename, b.dname
from emp a full outer join dept b
on (a.deptno= b.deptno) ;

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------
| 1 | VIEW | VW_FOJ_0 |    1 | 14 |00:00:00.01 |    15 |    |
|* 2 | HASH JOIN FULL OUTER|    |    1 | 14 |00:00:00.01 |    15 | 573K (0)|
| 3 |    TABLE ACCESS FULL | DEPT |    1 |    4 |00:00:00.01 | 7 |    |
| 4 |    TABLE ACCESS FULL | EMP    |    1 | 14 |00:00:00.01 | 8 |    |
----------------------------------------------------------------------------------------------

_optimizer_native_full_outer_join 파라미터를 force 로 하자 ANTI 조인마져 사라졌다.
이기능은 Native hash full outer join 이라고 불리고 11g 부터는 기본이 force 로 되어있다.

결론: Full Outer Join 은 10g 버젼 까지는 Outer Join + Union all + Anti Jojn 이지만
11g 부터는 성능이 향상된 Native hash full outer join 를 사용할수 있음을 기억하자.

저작자표시 비영리 동일조건

'Oracle > Data Join Method' 카테고리의 다른 글

Nested Loop Join 성능향상과 관련된 2가지 원리 (17)	2009.05.09
Hash Join Right (Semi/Anti/Outer) 의 용도 (17)	2009.03.02
Hash 조인시 Bushy tree Plan 유도하기 (4)	2008.11.01
Hash 조인의 튜닝시 주의사항(Work Area 의 튜닝) (3)	2008.10.30
Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter) (6)	2008.10.12

Posted by extremedb

,


	오동규 블로그 odong91@naver.com by extremedb

'Oracle'에 해당되는 글 120건

'Oracle > News' 카테고리의 다른 글

'Oracle > SQL Pattern' 카테고리의 다른 글

'Oracle > TroubleShooting' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > SQL Pattern' 카테고리의 다른 글

'Oracle > SQL Pattern' 카테고리의 다른 글

'Oracle > SQL Tuning' 카테고리의 다른 글

'Oracle > SQL Pattern' 카테고리의 다른 글

'Oracle > Data Join Method' 카테고리의 다른 글

'Oracle > Performance Analysis' 카테고리의 다른 글

'Oracle > News' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > SQL Tuning' 카테고리의 다른 글

'Oracle > SQL Pattern' 카테고리의 다른 글

'Oracle > Performance Analysis' 카테고리의 다른 글

'Oracle > Performance Analysis' 카테고리의 다른 글

'Oracle > Data Join Method' 카테고리의 다른 글

'Oracle > Data Join Method' 카테고리의 다른 글

카테고리

최근에 올라온 글

최근에 달린 댓글

글 보관함

링크

태그목록

최근에 받은 트랙백

티스토리툴바