태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'2010/11'에 해당되는 글 8건

  1. 2010.11.29 오라클 실행 계획을 보는 새로운 방법 (10)
  2. 2010.11.25 조인 순서에 대한 간단한 테스트 (3)
  3. 2010.11.24 오라클의 자동 메모리 크기 변경 기능을 써야 할까요?
  4. 2010.11.23 [공지] 인터파크에서 [오라클 성능 Q&A] 출판 이벤트를 진행한다고 합니다. (3)
  5. 2010.11.18 오라클 ACE와 함께 하는 [오라클 성능 Q&A] 출간! (12)
  6. 2010.11.09 아우터 조인 제거(Outer Join Elimination) (4)
  7. 2010.11.04 조인 순서 제어하기 2 (2)
  8. 2010.11.02 Oracle 11g의 Serial Direct Path Read와 _very_large_object_threshold 파라미터

오라클 실행 계획을 보는 새로운 방법

오라클/SQL 튜닝 2010.11.29 19:04
대부분의 오라클 클라이언트가 아래와 같은 포맷으로 실행 계획을 보여줍니다.

하지만 다음과 같은 진짜 트리 모양으로 실행 계획을 보는 것이 더 편할 때도 있습니다.

아래 블로그 포스트를 보면 트리 모양으로 표현한 실행 계획의 가독성이 때로는 훨씬 높다는 것을 알 수 있습니다. (영어 해석 귀찮으신 분들은 그림만 보시면 됩니다. ^^)

흔히 온라인 쿼리는 Left Deep 트리, 배치 쿼리는 Right Deep 트리 형태를 따르는 경우가 많으며, 인라인 뷰를 사용하는 경우에는 Bushy 트리로 유도할 수 있다는 식의 설명을 보는 경우가 있는데 위의 글에서 소개한 것과 같이 실행 계획 자체를 트리 모양으로 볼 수 있으면 좀 더 명확하게 이해할 수가 있겠군요.

클라이언트 툴들이 좀 더 똑똑하고 비주얼해지면 좋겠습니다. 아니면 그런 툴들이 이미 있는데 제가 잘 모를 수도 있겠습니다.

PS) 아래 오동규님의 블로그 포스트를 같이 참조하시면 이해하는데 더 도움이 되겠습니다.

저작자 표시
신고
Trackbacks 17 : Comments 10
  1. salvationism 2010.11.30 10:56 신고 Modify/Delete Reply

    긴 실행계획의 경우 화면상의 제약이 심할거 같은데 그것만 극복하면..
    해상도가 좋은 노트북이 필수가 될수도.. ㅎㅎ

    • 욱짜 2010.11.30 11:02 신고 Modify/Delete

      화면상의 제약을 극복하고 보기에도 편한 UI 디자인도 필요하겠는데요. 아무리 좋은 표현 방식도 실제로 사용하기에 불편하면 의미가 없을 것 같아요.

  2. 마농 2010.11.30 15:20 신고 Modify/Delete Reply

    토드에 있는 기능이네요.
    Explain Plan 탭에서 마우스 오른쪽 버튼 클릭 Display Mode > Graphic 선택

  3. 욱짜 2010.11.30 18:19 신고 Modify/Delete Reply

    비슷한 화면들이 Toad와 EM(Enteprise Manager)의 Tuning Pack 화면에서도 제공되고 있군요. 오렌지는 어떤지 모르겠습니다.

    사용자 편의성과 가독성을 갖추면 실행 순서를 이해하는데 좋은 도구가 될 수 있겠습니다.

    실행 순서대로 트리 노드를 따라가면서 하이라이트를 해주는 기능(애니메이션) 같은 것이 있으면 특히 교육용으로 좋겠어요.

  4. 양용성 2010.12.10 10:29 신고 Modify/Delete Reply

    SQLGate 에서 오라클,DB2 용 Visual Plan 을 제작중에 있습니다.

    SSMS 에서와 유사한 형태의 왼쪽에서 오른쪽으로 보는 방식과 탑다운 방식의 플랜을 제작중입니다.

    조만간 공개하겠습니다

  5. hellower 2010.12.23 17:54 신고 Modify/Delete Reply

    이미 마농님이 말씀하셨는데,
    toad 이 이미 구현되어 있으며 animation 기능까지 있답니다.

  6. 양용성 2011.01.25 17:50 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

  7. Yates33Ada 2011.11.11 11:10 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment


조인 순서에 대한 간단한 테스트

오라클/SQL 튜닝 2010.11.25 17:35
조인 순서, 즉 누가 드라이빙 테이블이 되어야 하느냐에 대한 간단하면서도 재미있는 테스트를 소개합니다.

아래와 같이 마스터(T1) - 디테일(T2) 관계를 가지는 테이블이 있습니다.

SQL> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production


SQL> create table t1(
  2  	     c1 number,
  3  	     c2 number,
  4  	     constraint t1_pk primary key (c1)
  5  );

Table created.

SQL> create table t2(
  2  	     c1 number,
  3  	     c2 number,
  4  	     c3 number,
  5  	     constraint t2_pk primary key (c1, c2),
  6  	     constraint t2_fk foreign key (c1) references t1(c1)
  7  );

Table created.

SQL> create index t2_n1 on t2(c3);

Index created.
마스터 T1은 10건입니다. 그리고 디테일 T2는 10,000건입니다.
 
SQL> insert into t1
  2  select
  3  	     level,
  4  	     level
  5  from
  6  	     dual
  7  connect by level <= 10
  8  ;

10 rows created.

SQL> insert into t2
  2  select
  3  	     mod(level,10)+1,
  4  	     level,
  5  	     mod(level,1000)
  6  from
  7  	     dual
  8  connect by level <= 10000
  9  ;

10000 rows created.

SQL> exec dbms_stats.gather_table_stats(user, 't1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user, 't2');

PL/SQL procedure successfully completed.
마스터 T1은 10건이고, C3 = 1 에 해당하는 디테일 T2도 10건입니다.
SQL> select count(*) from t1;

  COUNT(*)
----------
        10

SQL> select count(*) from t2 where c3 = 1;

  COUNT(*)
----------
        10
여기서 질문! 두 테이블을 NL 조인으로 조인하는 경우 드라이빙 테이블은 무엇이 되어야 할까요?
  • 테이블 T1은 크기가 작으므로 T1이 드라이빙이 되어야 한다.
  • 테이블 T2가 필터링이 좋으므로(10000건 중 10건) T2가 드라이빙이 되어야 한다.
  • 둘다 실제 건수는 10건이므로 전혀 무관하다.
아래 결과를 보기전에 잠깐 생각을 해보시기 바랍니다...

아래에 결과가 있습니다.

SQL> select /*+ gather_plan_statistics
  2  		     leading(t1) use_nl(t2) index(t2) index(t2 t2(c3)) */
  3  	     t1.c1, t1.c2, t2.c2, t2.c3
  4  from
  5  	     t1, t2
  6  where
  7  	     t1.c1 = t2.c1
  8  	     and t2.c3 = 1
  9  ;


         C1         C2         C2         C3
---------- ---------- ---------- ----------
         2          2          1          1
         2          2       1001          1
         2          2       2001          1
         2          2       3001          1
         2          2       4001          1
         2          2       5001          1
         2          2       8001          1
         2          2       9001          1
         2          2       6001          1
         2          2       7001          1

10 rows selected.

-----------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |     10 |     118 |
|   1 |  NESTED LOOPS                |       |      1 |        |     10 |     118 |
|   2 |   NESTED LOOPS               |       |      1 |     10 |    100 |      18 |
|   3 |    TABLE ACCESS FULL         | T1    |      1 |     10 |     10 |       8 |
|*  4 |    INDEX RANGE SCAN          | T2_N1 |     10 |     10 |    100 |      10 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T2    |    100 |      1 |     10 |     100 |
-----------------------------------------------------------------------------------

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

   4 - access("T2"."C3"=1)
   5 - filter("T1"."C1"="T2"."C1")


SQL> select /*+ gather_plan_statistics
  2  		     leading(t1) use_nl(t2) index(t2 t2(c1, c2)) */
  3  	     t1.c1, t1.c2, t2.c2, t2.c3
  4  from
  5  	     t1, t2
  6  where
  7  	     t1.c1 = t2.c1
  8  	     and t2.c3 = 1
  9  ;

        C1         C2         C2         C3
---------- ---------- ---------- ----------
         2          2          1          1
         2          2       1001          1
         2          2       2001          1
         2          2       3001          1
         2          2       4001          1
         2          2       5001          1
         2          2       6001          1
         2          2       7001          1
         2          2       8001          1
         2          2       9001          1

10 rows selected.


-----------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |     10 |     278 |
|   1 |  NESTED LOOPS                |       |      1 |        |     10 |     278 |
|   2 |   NESTED LOOPS               |       |      1 |     10 |  10000 |      57 |
|   3 |    TABLE ACCESS FULL         | T1    |      1 |     10 |     10 |       8 |
|*  4 |    INDEX RANGE SCAN          | T2_PK |     10 |   1000 |  10000 |      49 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T2    |  10000 |      1 |     10 |     221 |
-----------------------------------------------------------------------------------

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

   4 - access("T1"."C1"="T2"."C1")
   5 - filter("T2"."C3"=1)


SQL> select /*+ gather_plan_statistics
  2  		     leading(t2) use_nl(t1) index(t2) */
  3  	     t1.c1, t1.c2, t2.c2, t2.c3
  4  from
  5  	     t1, t2
  6  where
  7  	     t1.c1 = t2.c1
  8  	     and t2.c3 = 1
  9  ;

        C1         C2         C2         C3
---------- ---------- ---------- ----------
         2          2          1          1
         2          2       1001          1
         2          2       2001          1
         2          2       3001          1
         2          2       4001          1
         2          2       5001          1
         2          2       8001          1
         2          2       9001          1
         2          2       6001          1
         2          2       7001          1

10 rows selected.


------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |     10 |      27 |
|   1 |  NESTED LOOPS                 |       |      1 |        |     10 |      27 |
|   2 |   NESTED LOOPS                |       |      1 |     10 |     10 |      17 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T2    |      1 |     10 |     10 |      13 |
|*  4 |     INDEX RANGE SCAN          | T2_N1 |      1 |     10 |     10 |       3 |
|*  5 |    INDEX UNIQUE SCAN          | T1_PK |     10 |      1 |     10 |       4 |
|   6 |   TABLE ACCESS BY INDEX ROWID | T1    |     10 |      1 |     10 |      10 |
------------------------------------------------------------------------------------

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

   4 - access("T2"."C3"=1)
   5 - access("T1"."C1"="T2"."C1")

예상하신대론가요?

해석은 각자의 몫! @_@

저작자 표시
신고
Trackbacks 5 : Comments 3
  1. 조용중 2010.11.26 01:53 신고 Modify/Delete Reply

    정답을 볼려고 끝까지 내려봤는데 "해석은 각자의 몫"...읽기만하다가 오랜만에 '타의적'으로 생각볼수 있어서 보람있었습니다. ㅎㅎㅎ

    • 욱짜 2010.11.26 11:09 신고 Modify/Delete

      해석에 어려움은 없으셨죠?

      곧 좀 더 재밌는 테스트로 이 주제를 또 다루겠습니다.

  2. tohappy 2011.01.05 15:37 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment


오라클의 자동 메모리 크기 변경 기능을 써야 할까요?

오라클/기타 2010.11.24 11:17
오라클이 버전업되면서 가장 신경쓰는 것 중 하나가 관리의 자동화입니다.

그 중 대표적인 것이 메모리 자동 관리입니다. Oracle 10g에서는 SGA 및 하부 컴포넌트(공유 풀과 버퍼 캐시 등)의 크기를 자동으로 조정하는 SGA 크기 관리 기능이 추가되었고, 11g에서는 PAT(PGA Aggregate Target)의 크기까지 관리해주는 기능이 추가되었죠.

제가 가지는 한가지 의문 중 하나는, 메모리 자동 관리 기능을 써야 하나?입니다.

결론은 애석하게도 부정적입니다.

  • 공유 풀과 버퍼 캐시의 크기가 변경되는 과정에서 불필요한 경합(래치 경합과 뮤텍스 경합)이 발생할 확률이 높습니다.
  • 특정 컴포넌트(가령 공유 풀)의 크기가 불필요하게 커지는 현상이 생길 수 있습니다.
  • 11g의 자동 메모리 관리 기능을 사용하면 PAT(PGA Aggregate Target)의 값이 바뀔 수 있고, 이로 인해 실행 계획이 변경되는 쿼리들이 생길 수 있습니다.
엔터프라이즈 급의 데이터베이스라면 매뉴얼한 최적화 과정이 반드시 필요합니다. 여기에는 메모리 크기 설정도 포함됩니다. 따라서 오라클 엔진에게 자동 메모리 관리 기능을 맡김으로써 불필요한 위험을 초래할 필요는 없다고 봅니다.

일정한 스킬을 보유한 DBA 그룹이 없는 중소 규모의 데이터베이스라면 자동 메모리 관리 기능이 유용할 수도 있을 것입니다. 단, 이런 경우라도 위에서 언급한 문제들이 언제든지 발생할 수 있기 때문에 여전히 보수적인 자세를 취할 필요가 있겠지요.

저작자 표시
신고
Trackbacks 25 : Comment 0

Write a comment


[공지] 인터파크에서 [오라클 성능 Q&A] 출판 이벤트를 진행한다고 합니다.

오라클/기타 2010.11.23 14:20
저렴하게 구입할 수 있는 기회라고 생각되어 공지 올립니다.

자세한 내용은 아래를 참조하세요.

  • 출간을 기념하여 인터파크 도서에서 더블 적립 이벤트를 실시합니다.
  • 이벤트 기한은 11월 23일 ~ 12월 10일입니다.
  • 현재 인터파크 도서에서 10%할인( -2,500원), 5%적립(-1,130P)에 판매중입니다. 고객분들은 22,500원에 결재하시고, 1,130P의 적립금을 받게 됩니다.
  • 이번 이벤트 기회에 구입하시게 되면, 추가 적립 2,000원을 적립해 드립니다. 적립금은 이벤트 종료 후, 12월 13일에 일괄적으로 해당 ID에 지급됩니다.
  • 인터파크 도서에서 구입하신 후, 아래 추가 적립금 신청하기를 클릭하시어, 주문번호를 남겨주시면 더블적립 이벤트 종료 후에, 일괄적으로 적립해 드리도록 하겠습니다.
아래 링크를 이용하시면 됩니다.

[바로 구매하기]

[적립금 신청하기]

이런 더블 적립 이벤트도 가끔 하는군요. ㅎㅎ

저작자 표시
신고
Trackbacks 15 : Comments 3
  1. 애독자 2010.11.23 15:39 신고 Modify/Delete Reply

    ㅡ.ㅡ 교보문고에서 오전에 주문하고 벌써 택배사 이동중이라 취소도 안되고... 좀더 빨리 주셨음 ^^ 암튼 잘 보겠습니다.

    • 욱짜 2010.11.23 17:24 신고 Modify/Delete

      아. 애석하군요.

      저도 오늘 오후에야 전달받았습니다. ㅎㅎ

      책을 보시다가 생기시는 의문은 언제든지 Ask Exem을 통해 문의해주세요.

  2. url 2012.03.28 14:37 Modify/Delete Reply

    관리자의 승인을 기다리고 있는 댓글입니다

Write a comment


오라클 ACE와 함께 하는 [오라클 성능 Q&A] 출간!

오라클/기타 2010.11.18 17:43
Ask 엑셈의 운영을 시작한 것은 제가 오라클을 본격적으로 공부하면서 가장 큰 도움을 얻었던 Ask Tom에서 받은 자극때문이었습니다. 저런 실용적이면서도 높은 수준의 논의가 온라인으로 이루어지다니!

Ask 엑셈은 여러가지 한계를 드러내기도 했지만(그리고 그 한계는 제 자신의 한계이기도 합니다), 지난 1년 반동안의 운영으로 제법 만족할 만한 수준의 질문과 답변들이 쌓이고 있습니다. 그리고 약 1년 동안의 질문과 답변 중 공유할만한 것들을 모아서 책으로 냈습니다.(아직 서점에 풀릴려면 시간이 좀 더 필요합니다만...)


오라클 ACE와 함께 하는 [오라클 성능 Q&A]

이전에 냈던 약간은 무겁고, 체계적인 내용에서 벗어나서 실제로 많은 분들이 궁금해했던 내용을 정리하면 조금 더 재미있게 지식을 주고 받을 수 있지 않을까하는 생각입니다. B급 영화 한편을 보는 느낌이라고 할까요? 그렇게 봐주시면 좋겠습니다.

부담없이 재미있게 읽을 수 있는 오라클 성능 관련 책을 찾으신다면(이런게 가능할까요? ㅎㅎ), 이 책을 한번 펼쳐보셔도 좋겠습니다.

PS) 위의 책을 마무리하면서 현재 다음 번 책을 준비하고 있습니다. 제목은 아마 [오라클 성능 트러블슈팅의 기초]가 될 것 같습니다. 목차는 다음과 같습니다.

  1. 기본개념 및 툴
  2. 시스템, 세션, SQL 분석
  3. 대기이벤트 분석
  4. 힙 메모리 분석
  5. 콜 트리 분석
  6. 기타 유틸리티
아... 무거운 주제인데요. 가능한 재미있게 내용을 전달할 수 있도록 노력 중입니다. 건투를 빌며!
저작자 표시
신고
Trackbacks 4 : Comments 12
  1. eddy 2010.11.18 18:11 신고 Modify/Delete Reply

    책의 일부 내용이 담긴 pdf를 보았습니다.
    그 내용으로 미루어보아 책의 내용이 상당히 기대됩니다.

    또한 늘 새로운 시도를 하시는 모습이 참 좋아보입니다.

    화이팅! ^^

    • 욱짜 2010.11.18 18:18 신고 Modify/Delete

      감사합니다.

      이 책을 내면서 B급 영화를 만드는 감독들의 고민을 이해하게 됐습니다. :)

      샘플 PDF는 편집과 교정이 완료되기 전의 버전입니다. 실제의 책은 훨씬 예쁘고 보기 편할 것입니다.

  2. salvationism 2010.11.18 19:11 신고 Modify/Delete Reply

    이번 세미나와 관련된 것 이군요.
    ask엑셈글은 다 본거같은데.. 재밌게 보도록 하겠습니다^^

    • 욱짜 2010.11.18 19:17 신고 Modify/Delete

      감사합니다.

      온라인에서는 지면 관계상 다루지 못했던 보다 상세한 설명과 테스트 케이스를 싣는데 주력했습니다. 재미있어야 할텐데요.

  3. 강정식 2010.11.19 11:30 신고 Modify/Delete Reply

    또 좋은 책을 출간하시는군요 ^^
    출간되는 날에 바로 사 봐야겠네요..

  4. 김재명 2010.11.19 23:55 신고 Modify/Delete Reply

    의도하신대로 부담없이 재미있을 것 같습니다.
    서점에 나오면 블로그에 글 다시 올려주세요. :)

  5. extremedb 2010.11.21 21:58 신고 Modify/Delete Reply

    축하드립니다.
    또 하나의 작품을 만드셨네요.

    • 욱짜 2010.11.22 10:57 신고 Modify/Delete

      부끄럽습니다. 여러분들이 질문한 것 중 책으로 옮길 수 있을만한 주제를 골라서 정리한 것에 불과합니다.

      앞으로도 더 좋은 많은 질문과 답변이 쌓여서 그것이 계속 공유되고 발전되었으면 좋겠습니다.

  6. 애독자 2010.11.23 10:21 신고 Modify/Delete Reply

    언제나 새로운 지식의 갈증에 청량한 음료수를 넣어주시는 욱짜님의 내공은 대한민국 SQL 발전의 밑거름이십니다. 건승하세요...

    • 욱짜 2010.11.23 13:35 신고 Modify/Delete

      표현이 시적이신데요.

      부끄럽습니다. 테스트하기를 좋아하고, 그 결과를 문장으로 나타내기를 좋아하는 엔지니어로 봐주시면 좋겠습니다.

  7. 2dong0 2010.12.02 17:39 신고 Modify/Delete Reply

    파랑이, 빨강이, 노랑이에 이어 이번에 하양이까지 질렀습니다.^^ ㅋㅋ
    아직 책을 받진 못했지만 기대하고 있습니다.^^
    앞으로도 좋은 책 부탁드립니다.^^

    • 욱짜 2010.12.02 17:51 신고 Modify/Delete

      감사합니다. 최소한 한가지라도 새롭고 실용적인 것을 전달할 수 있으면 좋겠습니다.

Write a comment


아우터 조인 제거(Outer Join Elimination)

오라클/옵티마이저 2010.11.09 13:12
아우터 조인에서 다음과 같은 패턴의 오류가 종종 발생하는 것 같습니다.
select *
from t1, t2
where t1.c1 = t2.c1 (+)
         and t2.c2 = 1
;
위의 쿼리의 문제는 무엇일까요?

아우터 조인 자체가 불필요하다는 것입니다. 아우터 조인에 의해서 얻은 데이터 중 t1에만 있고 t2에는 없는 데이터의 t2.c2는 NULL입니다. 따라서 t2.c2 = 1 조건이 있다는 것은 아우터 조인에 의해 생긴 데이터중 t2가 존재하지 않는(NULL인) 데이터를 제거한다는 것과 같은 의미입니다. 즉 위의 쿼리는 아래 쿼리와 논리적으로 동일합니다.

select *
from t1, t2
where t1.c1 = t2.c1
         and t2.c2 = 1
;
하지만 아우터 조인의 고유의 특성 때문에 위의 두 쿼리는 전혀 다른 실행 계획을 가지게 됩니다.

아우터 조인의 특징 중 하나는 조인의 방향이 고정되어 있다는 것입니다.

가령 { where t1.c1 = t2.c1(+) }으로 정의되었다면, 반드시 테이블 t1이 드라이빙 테이블이 됩니다. 따라서 { t2.c2 = 1 } 조건은 드라이빙 조건으로 사용될 수 없죠. 따라서 Hash Join + Table Full Scan이 선택될 확률이 매우 높습니다.

반면에 { where t1.c1 = t2.c1 }으로 정의되었다면, 테이블 t1과 t2 중 로우 수가 작은 것이 드라이빙 테이블이 될 것입니다. 따라서 { t2.c2 = 1 } 조건이 드라이빙 조건이 될 수 있습니다. 따라서 테이블 t2를 드라이빙으로 하는 Nested Loops Join + Index Range Scan이 선택될 활률이 높습니다.

단, 여기서 오라클이 두 가지 편법을 사용합니다.

  1. 아우터 조인(Outer Join)을 이너 조인(Inner Join)으로 바꿀려고 시도합니다. 이것을 아우터 조인 제거(Outer Join Elimination)라고 합니다. 위에서 예로 든 아우터 쿼리는 아우터 조인 제거의 대상이 됩니다.
  2. 조인 순서를 바꿀려고 시도합니다. { where t1.c1 = t2.c1(+) } 조인은 항상 t1 -> t2 의 순서를 따라야하지만, Hash Outer Join인 경우에 한해서는 t2를 드라이빙으로 바꿀 수 있습니다. 즉 가능한 적은 크기의 데이터가 드라이빙 위치에 오도록 바꿀 수 있습니다.
위의 두가지 편법에 의해 어느 정도 문제가 해결되기는 하지만, 조인 방향이 고정되어 있다는 아우터 조인의 특성에 기인하는 본질적인 문제는 항상 조심해야합니다. 아우터 조인을 잘못 사용하면 성능이 느리다다는 불평 중 상당 수가 이 특징으로 인해 나타나는 것으로 봅니다.

아우터 조인의 특성으로 인해 발생하는 재미있는 문제가 Ask 엑셈에 등록되어 있어서 소개합니다.

위에서 소개한 개념을 이해한 뒤라면 매우 재미있게 읽을 수 있을 것 같습니다.
저작자 표시
신고

'오라클 > 옵티마이저' 카테고리의 다른 글

아우터 조인 제거(Outer Join Elimination)  (4) 2010.11.09
Trackback 1 : Comments 4
  1. 궁금이 2010.11.15 09:13 신고 Modify/Delete Reply

    위의 글은 t1이 1이고 t2가 M 이라는 상황에서 진행된것인가요 아우터조인은 알듯하면서도 헷갈릴때가 있어서요

    • 욱짜 2010.11.15 19:07 신고 Modify/Delete

      최초의 질문을 보면 동일한 테이블(T)에 T1, T2의 알리아스를 붙여서 사용하고 있습니다. 아마 테스트용 데이터를 만드는 과정에서 편의상 그렇게 한 것 같습니다.

      아우터 조인에서 "헷갈리는 부분"을 정리해주시면 그 문제를 심도깊에 논의해보는 것도 재미있겠는데요.

  2. salvationism 2010.11.16 10:21 신고 Modify/Delete Reply

    여기에 질문 내용이 맞는지 모르겠는데 ask엑셈 글을 스마트폰으로 보면 어떤글은 제대로 보이는데 어떤글은 html소스.그 자체로 보입니다 수정되면참 편할거같습니다^^

    • 욱짜 2010.11.16 11:04 신고 Modify/Delete

      저희들도 그 문제를 인식했는데, 내부적으로 사용하는 공개소프트웨어의 문제라서 추적이 쉽지 않은 상태입니다.

      HTML 소스가 보이는 경우 새로 고침(리도르)하시면 정상적으로 보이실겁니다.

Write a comment


조인 순서 제어하기 2

오라클/SQL 튜닝 2010.11.04 15:16
아래 포스트에서 조인 순서 제어에 대한 짤막한 이야기를 한 바 있습니다. 이번에는 서브 쿼리가 포함된 조금 더 복잡한 예를 보겠습니다.

우선 아래와 같이 마스터 테이블 M1과 디테일 테이블 D1, D2, D3를 만듭니다.

create table m
as
select level as c1, level as c2
from dual connect by level <= 1000;

create table d1
as
select mod(level,1000) as c1, level as c2
from dual connect by level <= 10000;


create table d2
as
select mod(level,1000) as c1, level as c2
from dual connect by level <= 20000;

create table d3
as
select mod(level,1000) as c1, level as c2
from dual connect by level <= 30000;

create index m_n1 on m(c1);
create index m_n2 on m(c2);
create index d1_n1 on d1(c1);
create index d2_n1 on d2(c1);
create index d3_n1 on d3(c1);

@gather m
@gather d1
@gather d2
@gather d3
M, D1, D3를 조인하고, D2를 서브쿼리를 이용해 필터(NO_UNNEST 힌트를 통해 강제로 Unnesting을 막음)하는 쿼리가 있습니다. 우리가 원하는 것은 { M -> D1 -> D2 -> D3 }의 순서로(이것이 최상의 조인 순서로 가정하고) 만드는 것입니다.

1. PUSH_SUBQ 힌트를 이용해 Subquery를 Push하면 아래와 같이 { M -> D2 -> D1 -> D3 }의 조인 순서를 따릅니다.

explain plan for
select /*+ use_nl(d1 d3) index(d1) index(d3) */
	*
from
	m, d1, d3
where
	m.c1 = d1.c1
	and m.c1 = d3.c1
	and exists (select /*+ no_unnest index(d2) push_subq */ null from d2 where m.c1 = d2.c1 and d2.c2 between 1 and 1000)
	and m.c2 between 1 and 100
	and d1.c1 = 1
;

TPACK@ukja1120> select * from table(dbms_xplan.display(format=>'basic'));

--------------------------------------------------
| Id  | Operation                        | Name  |
--------------------------------------------------
|   0 | SELECT STATEMENT                 |       |
|   1 |  NESTED LOOPS                    |       |
|   2 |   NESTED LOOPS                   |       |
|   3 |    NESTED LOOPS                  |       |
|   4 |     TABLE ACCESS BY INDEX ROWID  | M     |
|   5 |      INDEX RANGE SCAN            | M_N1  |
|   6 |       TABLE ACCESS BY INDEX ROWID| D2    |
|   7 |        INDEX RANGE SCAN          | D2_N1 |
|   8 |     TABLE ACCESS BY INDEX ROWID  | D1    |
|   9 |      INDEX RANGE SCAN            | D1_N1 |
|  10 |    INDEX RANGE SCAN              | D3_N1 |
|  11 |   TABLE ACCESS BY INDEX ROWID    | D3    |
--------------------------------------------------
2. NO_PUSH_SUBQ 힌트를 이용해 Subquery Pushing을 못하게 하면 { M -> D1 -> D3 -> D2 }의 조인 순서를 따릅니다.
explain plan for
select /*+ use_nl(d1 d3) index(d1) index(d3) */
	*
from
	m, d1, d3
where
	m.c1 = d1.c1
	and m.c1 = d3.c1
	and exists (select /*+ no_unnest index(d2) no_push_subq */ null from d2 where m.c1 = d2.c1 and d2.c2 between 1 and 1000)
	and m.c2 between 1 and 100
	and d1.c1 = 200
;

-------------------------------------------------
| Id  | Operation                       | Name  |
-------------------------------------------------
|   0 | SELECT STATEMENT                |       |
|   1 |  FILTER                         |       |
|   2 |   NESTED LOOPS                  |       |
|   3 |    NESTED LOOPS                 |       |
|   4 |     NESTED LOOPS                |       |
|   5 |      TABLE ACCESS BY INDEX ROWID| M     |
|   6 |       INDEX RANGE SCAN          | M_N1  |
|   7 |      TABLE ACCESS BY INDEX ROWID| D1    |
|   8 |       INDEX RANGE SCAN          | D1_N1 |
|   9 |     INDEX RANGE SCAN            | D3_N1 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | D3    |
|  11 |   TABLE ACCESS BY INDEX ROWID   | D2    |
|  12 |    INDEX RANGE SCAN             | D2_N1 |
-------------------------------------------------
3. 흐음... 다음과 같이 QB_NAME 힌트와 LEADING 힌트를 사용하면? QB_NAME 힌트로 제어가 힘들어보입니다.
explain plan for
select /*+ use_nl(d1 d3) index(d1) index(d3) leading(m d1 @sq d3 ) */
	*
from
	m, d1, d3
where
	m.c1 = d1.c1
	and m.c1 = d3.c1
	and exists (select /*+ qb_name(sq) no_unnest index(d2) no_push_subq */ null from d2 where m.c1 = d2.c1 and d2.c2 between 1 and 1000)
	and m.c2 between 1 and 100
	and d1.c1 = 200
;

-------------------------------------------------
| Id  | Operation                       | Name  |
-------------------------------------------------
|   0 | SELECT STATEMENT                |       |
|   1 |  FILTER                         |       |
|   2 |   NESTED LOOPS                  |       |
|   3 |    NESTED LOOPS                 |       |
|   4 |     NESTED LOOPS                |       |
|   5 |      TABLE ACCESS BY INDEX ROWID| M     |
|   6 |       INDEX RANGE SCAN          | M_N1  |
|   7 |      TABLE ACCESS BY INDEX ROWID| D1    |
|   8 |       INDEX RANGE SCAN          | D1_N1 |
|   9 |     INDEX RANGE SCAN            | D3_N1 |
|  10 |    TABLE ACCESS BY INDEX ROWID  | D3    |
|  11 |   TABLE ACCESS BY INDEX ROWID   | D2    |
|  12 |    INDEX RANGE SCAN             | D2_N1 |
-------------------------------------------------
4. 이 때 이전 글 조인 순서 제어하기에서 소개한 방법을 쓸 수 있습니다. 아래와 같이 Subquery 내에서 WHERE m.c1 + 0*d1.c1 = d2.c1 조건을 사용하면 D1이 항상 D2의 선행이 되어야 합니다. 그리고 PUSH_SUBQ 힌트에 의해 D3 보다는 앞으로 가게 됩니다. 따라서 { M -> D1 -> D2 -> D3 }의 순서로 액세스하게 할 수 있습니다.
explain plan for
select /*+ use_nl(d1 d3) index(d1) index(d3) */
	*
from
	m, d1, d3
where
	m.c1 = d1.c1
	and m.c1 = d3.c1
	and exists (select /*+ no_unnest index(d2) push_subq */ null from d2 where m.c1 + 0*d1.c1 = d2.c1 and d2.c2 between 1 and 10)
	and m.c2 between 1 and 100
	and d1.c1 = 200
;

--------------------------------------------------
| Id  | Operation                        | Name  |
--------------------------------------------------
|   0 | SELECT STATEMENT                 |       |
|   1 |  NESTED LOOPS                    |       |
|   2 |   NESTED LOOPS                   |       |
|   3 |    NESTED LOOPS                  |       |
|   4 |     TABLE ACCESS BY INDEX ROWID  | M     |
|   5 |      INDEX RANGE SCAN            | M_N1  |
|   6 |     TABLE ACCESS BY INDEX ROWID  | D1    |
|   7 |      INDEX RANGE SCAN            | D1_N1 |
|   8 |       TABLE ACCESS BY INDEX ROWID| D2    |
|   9 |        INDEX RANGE SCAN          | D2_N1 |
|  10 |    INDEX RANGE SCAN              | D3_N1 |
|  11 |   TABLE ACCESS BY INDEX ROWID    | D3    |
--------------------------------------------------
오라클과 같이 힌트가 풍부하지 않은 다른 DBMS에서도 위와 같은 방법을 사용하면 조인 순서를 거의 원하는 대로 제어할 수 있지 않을까요?
저작자 표시
신고

'오라클 > SQL 튜닝' 카테고리의 다른 글

오라클 실행 계획을 보는 새로운 방법  (10) 2010.11.29
조인 순서에 대한 간단한 테스트  (3) 2010.11.25
조인 순서 제어하기 2  (2) 2010.11.04
조인 순서 제어하기  (3) 2010.10.25
비주얼 SQL 튜닝?  (6) 2010.10.21
Trackback 0 : Comments 2
  1. hellower 2010.11.08 16:23 신고 Modify/Delete Reply

    참고로, 서브쿼리를 푼후에
    아래와 같이 하면 query block 과 leading 으로도 조인 순서를 M -> D1 -> D2 -> D3 이렇게 바꿀수 있습니다.

    SELECT /*+ use_nl(d1 d3) index(d1) index(d3) leading(@SEL$5DA710D3 m@sel$1 d1@sel$1 d2@sel$2 d3@sel$1) */
    *
    FROM
    m, d1, d3
    WHERE
    m.c1 = d1.c1
    AND m.c1 = d3.c1
    AND EXISTS (SELECT /*+ unnest index(d2) */ NULL FROM d2 WHERE m.c1 = d2.c1 AND d2.c2 BETWEEN 1 AND 1000)
    AND m.c2 BETWEEN 1 AND 100
    AND d1.c1 = 200
    ;

Write a comment


Oracle 11g의 Serial Direct Path Read와 _very_large_object_threshold 파라미터

오라클/트러블슈팅 2010.11.02 11:29
아래 포스트를 통해서 Oracle 11g의 Serial Direct Path Read를 제어하는 방법을 소개한 바 있습니다. 위글의 핵심은 10949 진단 이벤트를 이용하면 Serial Direct Path Read를 비활성화할 수 있다는 것입니다.

최근에 Serial Direct Path Read를 제어하는 또 하나의 히든 파라미터를 알게 되었습니다. 바로 _VERY_LARGE_OBJECT_THRESHOLD 히든 파라미터입니다. 이 값이 "500"이면 세그먼트의 크기가 500MB 이상이면 10949 진단 이벤트에 무관하게 Serial Direct Path Read를 사용하라는 의미입니다. 테스트를 해보면 정확하게는 500MB가 아니고 그 근방의 값인듯 합니다. 어떤 문서에는 80%라고 하는데 좀 더 확인이 필요합니다. 하여간 요점은 지나치게 큰 테이블은 가능한 Serial Direct Path Read를 사용하라는 것입니다. 매우 합리적인 결정으로 보입니다.

간단한 테스트 사례를 통해 설명해보겠습니다.

1. 오라클 버전은 11.2.0.1입니다.

SQL> select * from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
2. _VERY_LARGE_OBJECT_THRESHOLD 파라미터의 값은 500(MB)입니다.
SQL> @para very_large_object
SQL> set echo off
old   9: and i.ksppinm like '%&1%'
new   9: and i.ksppinm like '%very_large_object%'

NAME                           VALUE                IS_DEFAUL SES_MODIFI SYS_MODIFI
------------------------------ -------------------- --------- ---------- ----------
DESCRIPTION
-------------------------------------------------------------------------------------
_very_large_object_threshold   500                  TRUE      true       deferred
upper threshold level of object size for direct reads
3. 약 104MB 크기의 테이블 T_VLOT를 만들고, 세션 레벨에서 94MB를 _VERY_LARGE_OBJECT_THRESHOLD 파라미터의 값으로 지정합니다.
SQL> create table t_vlot
  2  as
  3  select
  4  	rpad('x',2000) as c1,
  5  	rpad('x',2000) as c2,
  6  	rpad('x',2000) as c3,
  7  	rpad('x',2000) as c4
  8  from dual
  9  connect by level <= 6500
 10  ;

Table created.

SQL> col tsize new_value tsize
SQL> select trunc(blocks*8*1024/1024/1024) - 10 as tsize
  2  from dba_segments
  3  where owner = user and segment_name = 'T_VLOT'
  4  ;

     TSIZE
----------
        94

SQL> 
SQL> alter session set "_very_large_object_threshold" = &tsize;
old   1: alter session set "_very_large_object_threshold" = &tsize
new   1: alter session set "_very_large_object_threshold" =         94

Session altered.
4. 10949 진단 이텐트를 활성화하고 T_VLOT 테이블에 대해 Table Full Scan을 수행합니다. 그리고 10046 진단 이벤트를 통해 대기 이벤트를 관찰합니다.
SQL> -- even when 10949 is enabled
SQL> alter session set events '10949 trace name context forever, level 1';

Session altered.

SQL> alter system flush buffer_cache;

System altered.

SQL> exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

PL/SQL procedure successfully completed.

SQL> select count(*) from t_vlot;

  COUNT(*)
----------
      6500

SQL> exec tpack.end_diag_trace(userenv('sid'), 10046);

PL/SQL procedure successfully completed.

SQL> select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF', 'sys=no'));
5. 아래에 그 결과가 있습니다. 10946 이벤트를 활성화했지만 direct path read대기 이벤트가 기록되는 것을 알 수 있습니다. Serial Direct Path Read가 동작한 것입니다.
SQL ID: 1n87ukuyyv5h2
Plan Hash: 2969598161
select count(*)
from
 t_vlot


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          1          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.09       2.86      13000      13004          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.09       2.86      13001      13005          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 97

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=13004 pr=13000 pw=0 time=0 us)
   6500   TABLE ACCESS FULL T_VLOT (cr=13004 pr=13000 pw=0 time=60657 us cost=3575 size=0 card=6473)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         1        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  direct path read                              412        0.04          2.73
  asynch descriptor resize                        1        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
비록 진단 이벤트와 히든 파라미터를 통해 Serial Direct Path Read를 완전히 비활성화할 수 있지만, 이 기능은 기본적으로 좋은 기능입니다. Batch I/O 성능이 뛰어난 대부분의 스토리지에서 좋은 성능을 보여줄 것을 기대할 수 있습니다. 운영 환경에서 여러 팩터를 고려해서 적절히 제어하면 될 것입니다.
저작자 표시
신고
Trackback 0 : Comment 0

Write a comment

티스토리 툴바