태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'Oracle'에 해당되는 글 32건

  1. 2008.04.02 Wait Analysis의 힘
  2. 2008.02.25 Oracle 10g의 재미있는 버그 - Index Rebuild 후 Logical Reads 증가 현상 (3)
  3. 2008.02.20 OPT_PARAM Hint - Statement Level에서 Optimizer Parameter 변경하기 (6)
  4. 2008.02.18 Oracle 10g의 Cost Based Push Predicate에 의한 성능 저하... (1)
  5. 2008.02.17 RTFM and BAAG - Oracle 사용자의 명제들 (1)
  6. 2008.02.14 Query Transformation의 재미있는 사례 - Oracle의 버그일까, 아니면...
  7. 2008.02.11 Oracle 10gR2의 Autotuned MBRC (2)
  8. 2008.02.05 Session cached cursors와 v$open_cursor (4)
  9. 2008.01.24 Oracle SQL 튜닝 기법: TCF - Tuning by Cardinality Feedback
  10. 2008.01.17 QB_NAME Hint의 편리함 - Oracle 10g

Wait Analysis의 힘

오라클 2008.04.02 22:09
OS Performance 튜닝에서 가장 각광받고 또 효율적인 방법은 무엇일까?
놀랍게도 정답은 대기 현상 분석, 즉 Wait Analysis이다.

오라클에서라면 Wait Event의 개념으로 인해 Wait Analysis가 매우 발달해있다. 하지만 OS라니?

아래에 그 증거가 있다. 아래 화면은 HP-UX 11i에서 glance를 이용해 특정 프로세스의 "Wait" 현상을 모니터링한 결과이다.
(HotKey로 s -> PID 입력 -> W)

사용자 삽입 이미지

위의 프로세스 Wait의 항목 정의에서 중요한 것은 다음과 같다.

- Semaphore: 이 값이 높다면 동기화 과정에서 성능 저하가 발생한다는 것을 의미한다. 오라클에서의 Latch나 Enqueue에 의한 Blocking과 같은 레벨이다.
- Cache: 이 값이 높다면 File Buffer Cache에서 성능 저하가 발생한다는 것을 의미한다. 오라클에서 Logical I/O와 같은 레벨이다.
- IO: 이 값이 높다면 Disk I/O에 의해 성능 저하가 발생한다는 것을 의미한다. 오라클에서의 Physical I/O와 같은 레벨이다.
- Priority: 이 값이 높다면 CPU 경쟁에 의한 성능 저하가 발생한다는 것을 의미한다.
- Virtual Mem: 이 값이 높다면 Paging In/Out, Swaping In/Out에 의한 성능 저하가 발생한다는 것을 의미한다.

Oracle에서 Hit Ratio에 의한 성능 분석이 이제 거의 사용되지 않는 고전적인 방식이 된 것처럼 OS에서도 사용률에 의한 성능 분석(CPU 사용률, 메모리 사용률 등등)보다는 위와 같이 대기 현상에 기반한 분석, 즉 Wait Analysis가 가장 효과적인 방법으로 인정받고 있는 추세이다.

Wait Analysis 방법론을 OS 성능 분석, 오라클 성능 분석, WAS 성능 분석등에서 보편적으로 사용할 수 있다는 사실을 기억하고 실제로 활용할 수 있기를 바래본다.




신고
Trackback 0 : Comment 0

Write a comment


Oracle 10g의 재미있는 버그 - Index Rebuild 후 Logical Reads 증가 현상

오라클 2008.02.25 13:35
얼마전 10.2.0.4 Patch Set이 발표되었는데 거기에 상당히 재미있는 버그가 보고되어 있다.
내용은 이렇다.

버그번호: 6455161
제목: Index Rebuild나 Table Truncate후 Logical Reads와 cache buffers chains latch 경합이 증가하는 현상

구체적인 현상은 이렇다.

다음과 같은 Access 경로를 가정해본다.

Nested Loop
   Table Full Scan (TabA)
    Index Range Scan (IndB)


이 경우 TabA 테이블의 로우 하나에 대해 indB 인덱스를 Root->Branch->Leaf 블록을 탐색한다. 만일 TabA가 10만건이라면 Root->Branch->Leaf 블록을 총 10만번 일일이 방문해야 하는 셈이다. 오라클이 이것에 의한 비효율을 줄이기 위해 Root 블록에 대해 Buffer Pinning 기법을 사용한다. 즉 Root 블록이 액세스될 때 이것을 Pin(말 그대로 buffer cache에서 밀려나지 않도록 고정시킨다는 의미)시킨다. 이렇게 Pin이 된 Root 블록은 메모리에서 밀려나지 않는다는 것이 보장되기 때문에 매번 새로 읽지 않고 한번 읽은 블록을 재활용할 수 있다. 따라서 Root 블록에 대한 반복적인 Logical Reads를 줄여주는 효과가 있다.

이렇게 Buffer Pinning이 동작하는 경우에는 session logical reads 지표 대신 buffer is pinned count 지표가 증가한다.

여기까지가 Oracle 9i부터의 일반적인 스토리다. 하지만 위의 버그에 의하면 10g에서는 Index Rebuild나 Table Truncate에 의해 Index 정보가 바뀌면 이 Buffer Pinning이 동작하지 않는다는 것이다. 더 정확하게 말하면 Index Rebuild나 Table Truncate에 의해 dba_objects.object_id와 dba_objects.data_object_id 가 달라지는 현상시 발생하면 Index Root 블록에 대한 Buffer Pinning이 동작하지 않게 된다.

머리털이 쭈뼛해지는 버그다. 만일 Nested Loop Join으로 많은 량의 데이터를 인덱스를 경유해 추출하는 쿼리를 사용하는 환경에서, 인덱스  경유 부하를 줄이기 위해 Index Rebuild를 주기적으로 수행하고 있다면? 오히려 쿼리 성능이 크게 저하되는 결과를 가져오게 된다.

아래 테스트 결과가 이를 잘 증명하고 있다.


UKJA@ukja10> @bug                                                                                        
UKJA@ukja10> select * from v$version;
BANNER                                                                     -----------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0    Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> -- create table
UKJA@ukja10> drop table t_bug1 purge;

Table dropped.

Elapsed: 00:00:00.01
UKJA@ukja10> drop table t_bug2 purge;

Table dropped.

Elapsed: 00:00:00.03
UKJA@ukja10>
UKJA@ukja10> create table t_bug1(id int);

Table created.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> create table t_bug2(id int, name char(10));

Table created.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10>
UKJA@ukja10> -- creat index
UKJA@ukja10> create index t_bug2_idx on t_bug2(id);

Index created.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> -- generate data
UKJA@ukja10> insert into t_bug1
  2  select level
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10> insert into t_bug2
  2  select level, 'name'
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

Elapsed: 00:00:00.07
UKJA@ukja10>
UKJA@ukja10> commit;

Commit complete.

Elapsed: 00:00:00.00
UKJA@ukja10>
UKJA@ukja10> -- delete half of t_bug2
UKJA@ukja10> delete from t_bug2 where mod(id, 2) = 0;

5000 rows deleted.

Elapsed: 00:00:00.09
UKJA@ukja10>
UKJA@ukja10> commit;

Commit complete.

Elapsed: 00:00:00.01
UKJA@ukja10>
UKJA@ukja10>
UKJA@ukja10> -- nested loop join
UKJA@ukja10> select /*+ gather_plan_statistics ordered use_nl(t_bug1 t_bug2) */ count(*)
  2  from t_bug1, t_bug2
  3  where t_bug1.id = t_bug2.id;

                                          COUNT(*)                         --------------------------------------------------                                                                       5000                                                       
Elapsed: 00:00:00.18
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                          --------------------------------------------------------------------------------------------
SQL_ID  8x1jvara1cwga, child number 0                                      -------------------------------------                                                      
select /*+ gather_plan_statistics ordered use_nl(t_bug1 t_bug2) */ count(*) from           
t_bug1, t_bug2 where t_bug1.id = t_bug2.id                                                 
Plan hash value: 934322507                                                 --------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |            |      1 |      1 |      1 |00:00:00.17 |   10043 |
|   2 |   NESTED LOOPS      |            |      1 |   5000 |   5000 |00:00:00.22 |   10043 |
|   3 |    TABLE ACCESS FULL| T_BUG1     |      1 |  10000 |  10000 |00:00:00.04 |      23 |
|*  4 |    INDEX RANGE SCAN | T_BUG2_IDX |  10000 |      1 |   5000 |00:00:00.12 |   10020 |
--------------------------------------------------------------------------------------------

==> t_bug2_idx 인덱스에 대한 Logical Reads가 10,000인것에 유의하자. t_bug1 테이블의 10,000건에 대해 Index 깊이(2)만큼 읽으면 20,000 블록이 정상이다. 하지만 buffer pinning에 의해 Root 블록이 Pin됨으로써 실제로는 절반에 불과한 10,000 블록만을 읽는다.
                                                                           Predicate Information (identified by operation id):                        ---------------------------------------------------                          4 - access("T_BUG1"."ID"="T_BUG2"."ID")
                                                                                           
25 rows selected.

Elapsed: 00:00:00.06
UKJA@ukja10>
UKJA@ukja10>
UKJA@ukja10> -- Rebuild index
UKJA@ukja10> alter index t_bug2_idx rebuild;

Index altered.

Elapsed: 00:00:00.01
                              
UKJA@ukja10> -- nested loop join
UKJA@ukja10> select /*+ gather_plan_statistics ordered use_nl(t_bug1 t_bug2) */ count(*)
  2  from t_bug1, t_bug2
  3  where t_bug1.id = t_bug2.id;

                                          COUNT(*)                         -------------------------------------------------- 
5000                                         

Elapsed: 00:00:00.20
UKJA@ukja10>
UKJA@ukja10> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                          --------------------------------------------------------------------------------------------
SQL_ID  8x1jvara1cwga, child number 0                                      -------------------------------------                                                      
select /*+ gather_plan_statistics ordered use_nl(t_bug1 t_bug2) */ count(*) from t_bug1,   
t_bug2 where t_bug1.id = t_bug2.id                                                         
Plan hash value: 934322507                                                 --------------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |            |      1 |      1 |      1 |00:00:00.20 |   20042 |
|   2 |   NESTED LOOPS      |            |      1 |   5000 |   5000 |00:00:00.23 |   20042 |
|   3 |    TABLE ACCESS FULL| T_BUG1     |      1 |  10000 |  10000 |00:00:00.04 |      23 |
|*  4 |    INDEX RANGE SCAN | T_BUG2_IDX |  10000 |      1 |   5000 |00:00:00.15 |   20019 |
--------------------------------------------------------------------------------------------

==> t_bug2_idx 인덱스에 대한 Logical Reads가 20,000으로 증가한 것을 알 수 있다. Root 블록에 대한 Pinning이 발생하지 않기 때문이다.
                                                                                          
Predicate Information (identified by operation id):                        ---------------------------------------------------                           4 - access("T_BUG1"."ID"="T_BUG2"."ID")
                                                                                           
25 rows selected.

Elapsed: 00:00:00.06
UKJA@ukja10>
UKJA@ukja10> spool off


위의 테스트 결과는 Index Rebuild를 함부로 해서는 안되는 또 하나의 이유가 될 만하다.

만일 시스템에서 위와 같은 문제가 발생한다면? 불행하게도 Workaround는 10.2.0.4로 Patch를 하거나, Index Rebuild 대신 Index Drop/Create를 수행하고, Table Truncate 후에도 Index를 Drop/Create하는 것이다. 이런 중요한 버그에 대한 Patch가 10g의 수명이 다되어가는 이제야 나왔다는 것이 의아할 따름이다.

신고
Trackback 0 : Comments 3
  1. Mr. Park 2008.03.03 15:52 신고 Modify/Delete Reply

    interim patch 가 존재 하네요 6455161

  2. 욱짜 2008.03.03 17:59 신고 Modify/Delete Reply

    좋은 정보 감사합니다.

  3. genious 2008.04.07 17:52 신고 Modify/Delete Reply

    10.2.0.2 에서 AIX용 patch는 없어서..이거이거~

Write a comment


OPT_PARAM Hint - Statement Level에서 Optimizer Parameter 변경하기

오라클 2008.02.20 17:18
간혹 다음과 같은 요구사항이 생길 수 있다.

"특정 Statement 레벨로 Optimizer Parameter를 변경할 수는 없을까?"

가령 _b_tree_bitmap_plans 파라미터는 BTree Index Scan을 Bitmap 연산으로 변환해주는 기능을 활성화/비활성화하는 기능을 제공한다. Bitmap Conversion은 대부분의 경우 성능에 유리하지만 간혹 특정 쿼리는 이것을 비활성화하는 것이 나을 때가 있다. 가령

alter session set "_b_tree_bitmap_plans" = false;

와 같이 파라미터를 변경하는 것이다. 하지만 여기에는 심각한 문제가 있다. Session 레벨이나 System 레벨에서 값은 변경하는 것은 다른 쿼리들에도 영향을 미친다. 원치 않은 부작용이 우려되는 것이다. 다행히 소스 코드를 마음대로 수정할 수 있어서 쿼리 수행 직전/직후에 Disable/Enable시키는 것이 가능하다면 문제가 되지 않을 것이다. 하지만 이것이 여의치 않고(어떤 이유에서든) 쿼리를 수정하는 것만이 가능하다면?
(요즘 Java Programming에서 유행하는 iBatis나 Hibernate 같은 Framework에서는 이런 사례가 흔하게 발생할 수 있다)

이런 경우는 SQL Statement를 처음부터 다시 작성하지 않고 간단하게 다음과 같은 힌트를 부여하면 된다.

select /*+ opt_param('_b_tree_bitmap_plans', 'false') */ ....

OPT_PARAM 힌트는 10gR2에서 처음 소개되었다. 이름 그대로 Optimizer와 관련된 Parameter 값을 Statement 레벨에서 제어하는 힌트다.

OPT_PARAM 힌트는 매우 강력한 도구다. 따라서 Optimizer의 Parameter에 대한 정확한 이해를 바탕으로 잘 사용해야 한다.

PS) 몇가지 테스트를 해보면 모든 Optimizer 관련 파라미터에 대해서 이 힌트가 다 작동하는 것은 아니다. 실제 사용시에는 철저한 테스가 필요하다.
신고
Trackback 0 : Comments 6
  1. 욱짜 2008.02.27 17:46 신고 Modify/Delete Reply

    ^^ 고맙습니다. 한국에서도 이 포럼에서 활동하는 분들이 많아졌으면 좋겠네요.

  2. 오동규 2008.02.27 20:24 신고 Modify/Delete Reply

    이파라미터 관련해서 포럼에 재미있는 논쟁을 하셨더군요.
    하도 어이가 없어서 제가 좀 거들었습니다.
    http://forums.oracle.com/forums/thread.jspa?messageID=2369693&#2369693
    위주소를 클릭하니 포럼으로 들어가지 않고 copy 후 주소창에 붙여넣으니 들어가네요.
    Mister O 가 제 아이디 입니다.

  3. McLaren 2008.04.25 13:34 신고 Modify/Delete Reply

    안녕하세요 Bitmap Conversion에 대해 질문이 있습니다.

    제가 DBA로 있을 때 9.2.0.4에서 특정 SQL이 NL로 풀리던 것이
    Index Rebuild 작업을 하고 나서 open을 하니
    Bitmap Conversion 으로 실행계획이 풀려 낭패를 본적이 있습니다. DB내리고 힌트 추가해 해결했습니다
    오라클 버그라고 판명이 났습니다만
    그때의 그 충격과 공포는 이루말할 수가 없습니다 --;;

    9207에서 부턴가 (10g는 당연없겠죠?) 에서는 그런 위험성이 없다고 합니다만,
    글에서 말씀하신 간혹 특정 Query는 비활성화하는
    것이 나을때가 있다라고 말씀하신 상황에 대해서
    정리 좀 해주실 수 있는지요?

    부탁드립니다.

  4. 욱짜 2008.04.26 07:04 신고 Modify/Delete Reply

    특별히 정리된 목록을 가지고 있지는 않습니다. 글에서의 의도는 Oracle이 Bitmap Conversion이 유리하다고 판단하는 것이 잘못된 판단일 경우가 여전히 종종 있다는 것을 의미하는 것이구요.

    참, 그리고 말씀하신 버그의 번호를 알 수 있을까요?

  5. oracler 2008.05.10 23:20 신고 Modify/Delete Reply

    특정 SQL 이라는 상황이라는것에 주목합니다.
    IT 라는게 참~~ 때로는 뜬구름 잡기가 될수 있는데요.

    그런 상황을 가상적으로 만들어서 재연하는것은 매우 번거롭고 어려운 일이므로
    이미 벌어진 상황을 샘플로 하는것이 효율적이고 쉬울수 있습니다.

    무슨 말이냐면

    과거 NL 로 풀리던것이 rebuild 로 인해 다르게 풀렸다는것을 알았다는것은
    (수많은 SQL 들의 개별 SQL 별로 과거 플랜의 히스토리를 전부 알수 있는 방법은 없고
    index rebuild 로 영향을 받는 쿼리는 한두개가 아닐것이므로)

    갑자기 플랜이 다르다는것을 알았다는건
    문제가 된 SQL 이 원래 특이한 SQL 이라서 애초에 처음부터 관리대상이었다는 말이 되는데요.

    그렇다면 바로 그 SQL 이 "특정 SQL" 이라는것의 sample 이 될수 있는것 아닐까요.

    McLaren 님이 SQL/플랜/통계정보나 파라메터등 관련 팩터을 올려주시면 고맙겠습니다.

  6. McLaren 2008.06.03 17:12 신고 Modify/Delete Reply

    답변이 늦어 죄송합니다.
    제가 회사를 옮겼더니 메타링크 id가 잠겨버렸네요..
    고로, SQL, 플랜, 통계정보를 올려드리지 못하겠네요..ㅡㅡ;;

    참고로 9.2.0.4 버전에 RBO 였구요.
    파티션 테이블을 Reorg 했습니다.

    해결책으로 _b_tree_bitmap_plans 파라미터 값을 false로 바꾼 기억이 나네요^^

    별로 도움이 되지 않았네요~

Write a comment


Oracle 10g의 Cost Based Push Predicate에 의한 성능 저하...

오라클 2008.02.18 20:50
Oracle 10g에 소개된 혁신적인 기능 중 하나가 Cost Based Query Transformation(CBQT)이다. Oracle은 Query의 성능을 최적화하기 위해 Optimization 단계 전에 다양한 Query Transformation을 시도한다. 그 중 대표적인 것들이 Simple View Merging, Complex View Merging, Subquery Unnesting, Push Predicate 등이다.

Oracle 9i까지는 이러한 Query Transformation은 Rule Base로 작동한다. 즉, 특정한 룰을 가지고 룰을 만족하면 항상 Transformation을 시도한다. 비록 CBO로 Optimization을 수행하더라도 Transformation은 여전히 Rule Base로 동작하는 셈이다.

Oracle 10g에서는 이러한 개념이 완전히 바뀌어서, 이제는 Query Transformation 자체도 Cost Base로 동작한다. 즉, Transformation을 함으로써 쿼리의 비용(Cost)이 낮아지는 경우에만 Transformation을 수행하게 된다. 대부분의 경우 이것은 성능 개선에 도움이 된다. 불필요한 Transformation에 의해 쿼리의 성능이 오히려 느려질 수도 있기 때문이다. 하지만 드물게 특정 상황에서는 CBQT에 의해 Transformation이 이루어지지 않음으로써 Query 수행히 늦어지는 경우가 발생한다.

이런 상황은 Oracle 9i에서 Oracle 10g로 업그레이드를 수행했을 때 특히 문제가 된다. 9i에서는 Nested Loop로 잘 돌아가던 Query가 10g에서는 갑자기 Hash Join으로 풀리면서 성능이 느려지는 현상이 발생할 수 있다.

간단한 테스트를 통해 이 현상을 재연해보자.
아래 테스트는 Push Predicate가 Cost Based로 이루어지는 경우 어떤 문제가 발생할 수 있는지를 잘 보여준다.

-- 버전 확인
OWI@joss> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

-- 우선 필요한 Object들을 생성한다.
OWI@joss> create table t1
  2  as select rownum as id, mod(rownum, 5)+1 as n, object_name as name
  3  from all_objects where rownum <= 100;

테이블이 생성되었습니다.

OWI@joss> create table t2
  2  as select rownum as id, rownum as id2, object_name as name
  3  from all_objects where rownum <= 20000;

테이블이 생성되었습니다.

OWI@joss> create table t3
  2  as select * from t2;

테이블이 생성되었습니다.

OWI@joss> create index t2_idx on t2(id, id2);

인덱스가 생성되었습니다.

OWI@joss> create index t3_idx on t3(id, id2);

인덱스가 생성되었습니다.

-- 통계 정보를 생성한다. 상황을 간단하게 하기 위해 histogram을 생성하지 않는다.
OWI@joss> begin
  2           dbms_stats.gather_table_stats(user,'t1',
  3               method_opt=>'for all columns size 1', cascade=>true);
  4
  5           dbms_stats.gather_table_stats(user,'t2',
  6               method_opt=>'for all columns size 1', cascade=>true);
  7
  8           dbms_stats.gather_table_stats(user,'t3',
  9               method_opt=>'for all columns size 1', cascade=>true);
 10  end;
 11  /

PL/SQL 처리가 정상적으로 완료되었습니다.

-- View를 생성한다. t2 테이블과 t3 테이블을 조인하는 간단한 뷰이다.
OWI@joss> create or replace view v1
  2  as
  3  select  t2.id id2_1,
  4           t2.id2 id2_2,
  5           t3.id id3_1,
  6           t3.id2 id3_2,
  7           t2.name name2,
  8           t3.name name3
  9  from t2, t3
 10  where
 11           t2.id = t3.id
 12           and t2.id2 = t3.id2;

뷰가 생성되었습니다.

-- Cost based predicate push를 Enable한다.(기본값이 True이다)
OWI@joss> alter session set "_optimizer_push_pred_cost_based" = true;

세션이 변경되었습니다.

-- View Merging을 방지하기 위해 Outer Join을 사용하는 것에 유의하자.
-- View Merging이 발생하면 Push Predicate이 발생하지 않기 때문이다.
OWI@joss> select /*+ gather_plan_statistics cost_based */
  2           t1.name, v1.name2, v1.name2
  3  from t1, v1
  4  where
  5           t1.n = 1
  6           and t1.id = v1.id2_1(+);
-- 출력 결과는 생략

--
OWI@joss> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

------------------------------------------------------------------------------
| Id  | Operation               | Name   | Starts | E-Rows | A-Rows |Buffers |
------------------------------------------------------------------------------
|*  1 |  HASH JOIN OUTER        |        |      1 |     20 |     20 |    168 |
|*  2 |   TABLE ACCESS FULL     | T1     |      1 |     20 |     20 |      3 |
|   3 |   VIEW                  | V1     |      1 |  20000 |  20000 |    165 |
|*  4 |    HASH JOIN            |        |      1 |  20000 |  20000 |    165 |
|   5 |     INDEX FAST FULL SCAN| T3_IDX |      1 |  20000 |  20000 |     59 |
|   6 |     TABLE ACCESS FULL   | T2     |      1 |  20000 |  20000 |    106 |
------------------------------------------------------------------------------
                                                                                                       
Predicate Information (identified by operation id):
---------------------------------------------------                                                                                                 
   1 -  access("T1"."ID"="V1"."ID2_1")
   2 - filter("T1"."N"=1)
   4 - access("T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2")
                                                                                     
                                                              

25 개의 행이 선택되었습니다.

위의 예를 보면 실행 계획의 비효율성이 존재하는 것을 확인할 수 있다.

1. 2번째 단계에서 t1 테이블에서 단 20건의 데이터만이 추출된다.
2. 이 20건의 데이터를 이용해서 v1에 대해 Nested Loop 조인을 수행하는 것이 가장 바람직할 것이다.
3. 하지만, v1 뷰에는 t1.n = 1 에 해당하는 조건을 인식시킬 수 없으므로
4. Oracle은 t1과 v1을 Hash Join으로 수행한다.

만일 Predicate Push가 이루어져서 t1.n = 1 에 해당하는 t1.id 값이 v1 뷰안에 Push되면 매우 효율적인 실행 계획을 만들 수 있다. 아래 테스트 결과를 보자.

-- Cost Based Push Predicate를 Disable한다.
OWI@joss> -- disable cost based predicate pushing
OWI@joss> alter session set "_optimizer_push_pred_cost_based" = false;

세션이 변경되었습니다.

OWI@joss>
OWI@joss> select /*+ gather_plan_statistics no_cost_based */
  2           t1.name, v1.name2, v1.name2
  3  from t1, v1
  4  where
  5           t1.n = 1
  6           and t1.id = v1.id2_1(+);

출력 결과는 생략

OWI@joss> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Starts | E-Rows | A-Rows |Buffers|
------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS OUTER            |        |      1 |     20 |     20 |     75|
|*  2 |   TABLE ACCESS FULL            | T1     |      1 |     20 |     20 |      5|
|   3 |   VIEW PUSHED PREDICATE        | V1     |     20 |      1 |     20 |     70|
|   4 |    NESTED LOOPS                |        |     20 |      1 |     20 |     70|
|   5 |     TABLE ACCESS BY INDEX ROWID| T2     |     20 |      1 |     20 |     45|
|*  6 |      INDEX RANGE SCAN          | T2_IDX |     20 |      1 |     20 |     25|
|*  7 |     INDEX RANGE SCAN           | T3_IDX |     20 |      1 |     20 |     25|
------------------------------------------------------------------------------------
 
                                                                                    

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."N"=1)
   6 - access("T2"."ID"="T1"."ID")
   7 - access("T2"."ID"="T3"."ID" AND "T2"."ID2"="T3"."ID2")

                   

                                                                                        
위의 결과를 보면

1. 3번 단계에서 VIEW PUSHED PREDICATE 가 발생하는 것을 확인할 수 있다. Starts 값이 20이라는 것에서 t1.n = 1 조건에 해당하는 20건에 대해 Nested Loop로 Push가 이루어지는 것을 알 수 있다.
2. 무엇보다 큰 차이는 6번 단계의 Predicate Information에서 t2.id = t1.id 조건이 Access Predicate로 추가된 것을 알 수 있다.
3. 즉, Predicate Push에 의해 v1 뷰안에 t1.id 조건이 Push된 것이다.
4. 이로 인해 효율적인 Nested Loop 조인이 이루어지고 일량도 168 블록에서 75 블록으로 크게 줄었다.

위의 예는 Push Predicate가 발생해야 함에도 불구하고, Cost Based Push Predicate에 의해 발생하지 않고 그로 인해 성능이 저하되는 전형적인 사례이다. 이런 사례들 때문에 Oracle 9i에서 10g로 업그레이드후 예상치 않게 성능이 저하되는 현상이 발생하는 것으로 보고되는 것이다. Cost Based Query Transformation과 관련해서 가장 이슈가 되는 두 파라미터가 _optimizer_cost_based_transformation(View merging/subquery unnesting 등을 제어)가 _optimizer_push_pred_cost_based(Predicate Push를 제어)이다.

위의 간단한 테스트 결과가 Query 성능을 분석할 때 실행 계획과 Predicate 정보가 얼마나 중요한지 또 하나의 예로 활용되었으면 한다.

더 자세한 정보는 아래 URL을 참조한다.

http://wiki.ex-em.com/index.php/OPTIMIZER_PUSH_PRED_COST_BASED
http://wiki.ex-em.com/index.php/OPTIMIZER_COST_BASED_TRANSFORMATION



신고
Trackback 0 : Comment 1
  1. 허거덩왕자 2009.11.26 00:05 신고 Modify/Delete Reply

    좋은정보감사합니다~

Write a comment


RTFM and BAAG - Oracle 사용자의 명제들

오라클 2008.02.17 21:57
RTFM은 IT 사용자에게 널리 알려진 용어 중 하나다.

RTFM  - Read The Fucking Manual - 이런 젠장, 매뉴얼 좀 읽어 보시오.


Online Forum에서 이루어지는 질문의 80% 이상이 이미 매뉴얼에 있는 내용이라고 해서 생긴 말이다. 오라클이 제공하는 매뉴얼을 보면 고개가 끄덕여질 수 밖에 없다. 오라클 매뉴얼은 하나 하나가 너무나 뛰어난 교육 교재이고, 우리가 알아야 할 지식의 80% 이상을 제공해 준다.
(이런 의미에서 제대로 된 매뉴얼이 거의 없는 국내의 IT 환경은 얼마나 열악한가? 그 빌어먹을 매뉴얼조차 제대로 없으니...)

최근에 유행하는 또 하나의 명제가 있다.

BAAG

언뜻 그 뜻을 알기 어렵다.

BAAG - Battle Against Any Guesswork - 추측을 배제하시오


Oracle 성능 전문가 중 한명이 추측에 의한 잘못된 성능 진단이 난무하는 현실을 막기 위해 만든 용어이며, 공식 사이트는 다음과 같다.
http://www.battleagainstanyguess.com/

추측을 최소화하기 위해 많은 노력들을 하겠지만, 내가 했던 결과나 내가 아는 다른 사람들의 결과를 곱씹어 보면 추측(Guesswork)을 얼마나 남용했는지 얼굴이 붉어질 정도다. 창의적 추측(Creative Guesswork)라는게 있지 않느냐고 반문하겠지만, 여기서의 Guesswork는 엄밀한 검증이나 정확한 지식 없이 함부로 추론하고 결론을 내리는 행위를 말하는 것으로 해석하면 무난하겠다.

BAAG - 우리가 유념해야 할 새로운 명제가 되었으면 좋겠다.

신고
Trackback 0 : Comment 1
  1. HobbsAlfreda26 2011.11.19 05:03 Modify/Delete Reply

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

Write a comment


Query Transformation의 재미있는 사례 - Oracle의 버그일까, 아니면...

오라클 2008.02.14 14:33
Oracle의 Query 실행 엔진은 크게 두 단계의 작업을 거쳐서 실행 계획을 생성한다.

Transformation: Query를 Rewrite하는 단계이다. View Merge, Subquery Unnest, Join Predicate Pushing, Constraint Check 등을 통해 Oracle은 원래 쿼리를 Rewrite한다.
Optimization: Transform된 Query에 대해 통계 정보 등을 이용해서 최적화된 실행 계획을 생성한다.

Query Transformation은 매우 다양한 방식으로 동작한다. 그 중에서 잘 알려지지 않은 것이 Constraint에 의한 Transformation이다. Constraint에 의한 Query Transformation에 대해서는 앞서 Predicate와 관련된 글에서 잠깐 다룬 적이 있다.

Foreign Key Constraint에 의한 Query Transformation 때문에 잘못된 결과가 나오는 이상한 현상을 통해 이 문제를 한번 다루어 보자.

-- PK/FK 관계를 지닌 테이블을 생성한다.
OWI@joss> CREATE TABLE parent_tab (parent_id NUMBER NOT NULL); 
OWI@joss> ALTER TABLE parent_tab ADD CONSTRAINT parent_pk PRIMARY KEY (parent_id);
OWI@joss> CREATE TABLE child_tab
(child_id NUMBER NOT NULL,
parent_id NUMBER NOT NULL);

OWI@joss> ALTER TABLE child_tab
ADD CONSTRAINT child_pk PRIMARY KEY (child_id);

OWI@joss> ALTER TABLE CHILD_TAB 
ADD CONSTRAINT child_fk FOREIGN KEY (parent_id)
REFERENCES parent_tab (parent_id);
-- 값을 삽입한다. (parent_id = 1, 2)
OWI@joss> INSERT INTO parent_tab VALUES (1);
OWI@joss> INSERT INTO parent_tab VALUES (2);
OWI@joss> INSERT INTO child_tab VALUES (1,1);
OWI@joss> INSERT INTO child_tab VALUES (2,1);
OWI@joss> INSERT INTO child_tab VALUES (3,2);
OWI@joss> INSERT INTO child_tab VALUES (4,2);

-- Constraint을 잠시 Disable한다.
OWI@joss> ALTER TABLE child_tab MODIFY CONSTRAINT child_fk DISABLE VALIDATE;

-- parent_tab에서 parent_id = 1인 데이터를 삭제한다. child_tab에서 parent_id = 1 인 데이터는 고아(Orphant)가 된다.<-- 여기에 Trick이 있다.
OWI@joss> DELETE FROM parent_tab  WHERE parent_id = 1;
OWI@joss> COMMIT;

-- Constraint를 다시 Enable 한다.
OWI@joss>  ALTER TABLE child_tab MODIFY CONSTRAINT child_fk ENABLE VALIDATE;


-- 이제 다음과 같은 쿼리를 수행한다.
-- parent_id = 1인 데이터가 parent_tab에서 삭제되었으므로 결과는 (3,2),(4,2) 두 건만이 나와야 한다.
OWI@joss> select child_tab.*
       from child_tab, parent_tab
      where child_tab.parent_id = parent_tab.parent_id;

한지만 실제 수행 결과는 다음과 같다. 2건이 아닌 네건이 나온다. 전혀 잘못된 결과가 나오는 것이다.
  CHILD_ID  PARENT_ID
---------- ----------
1 1
2 1
3 2
4 2

왜 이런 현상이 발생할까? 실행 계획을 보면 그 이유를 알 수 있다.

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 104 | 13 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| CHILD_TAB | 4 | 104 | 13 (0)| 00:00:01 |
-------------------------------------------------------------------------------
위의 실행 계획을 보면 parent_tab으로는 아예 가지도 않는 것을 확인할 수 있다. Oracle은 FK Constraint 조건을 이용해 Query를 다음과 같이 Transformation해 버린 것이다.
OWI@joss> select * from child_tab;
Oracle은 FK Constraint 조건이 있으므로 굳이 parent_tab까지 가지 않아도 원하는 결과를 얻을 수 있는 것으로 간주한 것이다. 하지만 우리는 간단한 조작을 통해 child_tab 테이블에 고아(Orphant) 데이터를 만들었다. 이런 이유로 전혀 잘못된 쿼리 결과가 나오게 된다.

아래와 같이 Transformation이 발생하지 않게끔 힌트를 주면 Nested Loop Join이 발생하고 정상적인 결과를 얻을 수 있다.
OWI@joss> select /*+ NO_QUERY_TRANSFORMATION */ child_tab.*
2 from child_tab, parent_tab
3 where child_tab.parent_id = parent_tab.parent_id
4 ;

CHILD_ID PARENT_ID
---------- ----------
3 2
4 2

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 13 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 78 | 13 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| CHILD_TAB | 4 | 104 | 13 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PARENT_PK | 1 | 13 | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------
위의 현상을 버그라고 봐야 할까? 아마 사람들마다 다른 결론을 낼 것이다.
개인적인 생각으로는 버그라기 보다는 Query Transformation에 의한 원하는 않은 부작용으로 보는 것이 맞을 거 같다. 물론 넓은 의미에서는 버그이겠지만...

Oracle은 매우 적극적으로 Query Transformation을 수행한다는 사실을 기억할 필요가 있겠다. 더구나 10g부터는 Cost Based Transformation(CBO가 아닌 CBT) 기능이 추가되어 이전 버전과 다른 방식으로 Transformation이 발생하게 된다. 항상 실행 계획을 확인해서 Transformation이 발생하는지, 발생한다면 어떤 유형의 Transformation이 발생했는지 확인하는 습관이 필요할 것이다.




신고
Trackback 0 : Comment 0

Write a comment


Oracle 10gR2의 Autotuned MBRC

오라클 2008.02.11 17:34
Oracle에서 MBRC(Multi Block Read Count) 만큼 간단 명료한 개념이 있을까? 말 그대로 Multi Block I/O(Full Table Scan, Index Fast Full Scan 등)를 수행할 때 한번에 읽어 들일 블록수를 결정한다.

하지만, 역사적인 이유 때문에 지금은 가장 헷갈리는 개념이 되어 버렸다. 지금 이렇게 정리를 하고 있는 나 스스로도 좀 짜증이 날 정도로 개념이 혼용되고 있다. 역사적인 이유라고 하니, Oracle이 어떤 역사에 의해 MBRC의 개념을 복잡하게 만들었는지 확인해 보자.

1. 9i 이전까지는 오직 하나의 MBRC만이 있었다. db_file_multiblock_read_count라는 이름의 파라미터에 의해 결정되었으며, Optimizer의 비용 계산과 실행 시의 Mullti Block I/O 모두에 이 값이 사용되었다. 여기서 비극이 잉태된 것인데, Multi Block I/O 성능을 높이려고 하면 Optimizer의 비용 계산에 영향을 주는 아이러니한 상황이 생기는 것이다.

2. 9i에서 System Statisitics가 소개되면서 System MBRC라는 개념이 탄생했다. System Statistics를 수집하면 v$filestat과 v$sysstat을 참조해서 현재 시스템에서 보편적으로 사용되는 MBRC 값을 추출한다. 이 값은 Optimizer의 비용 계산에만 사용된다. 반면 db_file_multiblock_read_count 값은 Multi Block I/O 수행시에만 사용된다. 비로소 Optimizer 용과 실행용, 두 가지의 MBRC가 생긴 것이다.
하지만, 9i에서 System Statistics가 잘 사용되지 않아서 이 사실이 널리 공유되지 못했다.

3. 위의 개념은 10gR1까지 그대로 계승된다.

4. 10gR2에서 Oracle은 또 한번 개념을 흔들었다.
우선, System Statistics의 사용 방식은 이전 버전과 동일하다. 하지만 다음과 같은 두 개의 히든 파라미터가 소개되었다. _db_file_optimizer_read_count_db_file_exec_read_count. 전자는 Optimizer의 비용 계산에 사용되는 MBRC이고 후자는 Multi Block I/O 실행시에 사용되는 MBRC이다. 따라서 System Statistics가 없다고 하더라도 우리는 이 두 개의 히든 파라미터를 이용해서 이전보다 더 디테일한 제어가 가능해졌다.
또 다른 변화는 db_file_multiblock_read_count 파라미터 초기값의 변화이다. 10gR1까지는 항상 "8"이 기본값이다. 하지만 10gR2부터는 오라클이 판단하기에 사용가능한 최적값이 기본값이 된다. 따라서 이 값은 시스템마다 다르며 보통 50 ~ 128 사이의 값이다. 이 경우 _db_file_exec_read_count 값은 최적값(=db_file_multiblock_read_count)로, _db_file_optimizer_read_count 값은 8로 설정되어 큰 값의 MBRC가 Optimizer의 비용 계산에 영향을 주는 것을 방지한다. 이러한 기능을 흔히 Autotuned MBRC라고 부른다. 즉, 10gR2에서는 MBRC 값이 시스템에 최적화되어 기본 설정된다. 이 값은 Multi Block I/O를 실행하는데만 사용되므로 System Statistics에 의한 MBRC는 여전히 중요한 의미를 지닌다.

위의 스토리가 다 이해가 되는가? 너무나 구구 절절해서 적으면서도 약간 짜증이 날 정도이다. 긍정적인 점은 Oracle이 최적의 MBRC를 스스로 찾을 수 있도록 개선되고 있다는 것이다. DBA가 신경쓸 것은 System Statistics를 수집할 지 말지만을 결정하는 것이라고 보면 정확할 것이다. 물론 버전마다 기본 행동 방식이 이렇게 흔들리는 것은 DBA나 성능 전문가로서는 여간 성가신 일이 아니다. 하지만 결국 Oracle의 성능을 보다 합리적으로 관리하자는 목적을 가지고 있는 것이니 잘 활용하자는 것만이 우리에게 남겨진 몫일 것이다.

Autotuned MBRC에 대한 자세한 내용은 아래 글을 참조한다.

http://wiki.ex-em.com/index.php/DB_FILE_MULTIBLOCK_READ_COUNT

PS1) Oracle 10gR2 Reference Manual에 위의 내용이 아래와 같이 잘 설명되어 있다.

As of Oracle Database 10g release 2, the default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.
Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.

Even though the default value may be a large value, the optimizer will not favor large
plans if you do not set this parameter. It would do so only if you explicitly set this parameter to a large value.

PS2) 만일 10gR2에서 db_file_multiblock_read_count 값이 의외로 작게 설정되어 있다면 Parameter File에서 강제로 이 파라미터 항목을 삭제하고 Oracle을 재시작하면 Autotuned MBRC 값으로 지정된다.
신고
Trackback 0 : Comments 2
  1. adenkang 2009.08.03 20:00 신고 Modify/Delete Reply

    안녕하세요?

    블로그를 통해서 좋은 정보를 많이 얻고 있습니다. system statistics에 대해서 조금 혼동스러운 부분이 있어서 질문을 합니다. 블로그에서는 system statistics에 대해서 수집하는 것을 권장하고 있습니다. Oracle에서 생기는 변화를 봐도 이 기능을 수집해서 사용하는 것이 저도 좋을 것으로 판단이 됩니다. (물론 실무에서 적용해보지 않았기 때문에 100% 확신은 없습니다.)
    Optimizing Oracle Optimizer 책에 이 부분에 대해서 high-end storage에서 disk cache로 인해서 실행가능한 값이 설정되지 않는다는 언급이 있어서 그렇다면 결국 system statistics를 수집해도 인위적으로 조절을 해줘야 하는 것인데 그렇다면 어떻게 조절을 하는 것이 좋을지??
    실무 경험 사례가 있다면 조언 부탁드립니다.

  2. 욱짜 2009.08.03 21:17 신고 Modify/Delete Reply

    adenkang님 반갑습니다.

    System Statistics를 바라보는 제 정확한 관점은 "Index Scan과 Full Table Scan의 비용을 결정하는 각종 파라미터들(db_file_multiblock_read_count와 optimizer_index_cost_adj, optimizer_index_caching 등)의 자동화 버전"으로 요약할 수 있습니다. 즉 파라미터를 통해서 매뉴얼하게 관리해주어야 했던 것을 조금 더 자동화시킨 것에 불과합니다.

    자동화했기 때문에 더 편리하죠. 수동보다는 오토가 편한 것과 같은 원리입니다. 하지만 문제는 아직 Oracle이라는 소프트웨어가 자동차의 오토기어만큼 진화하지 못했다는 것입니다. 따라서 아직 많이 부족합니다. 또는 자동차라는 단순한 모델에 비해 소프트웨어가 지니는 복잡성 때문에 자동화하는 것이 아직은 어렵다고 봐도 좋습니다. 지금의 System Statistics 모델은 과도기적인 위치에 있다고 봅니다. 따라서 과감하게 사용해보는 경우도 있고, 적용을 꺼리는 경우도 아주 많습니다.

    어떤 사이트에 갔더니 Oracle 엔지니어가 System Statistics는 문제가 많으니 쓰지 말라고 했다는군요. 이것을 말 그대로 받아들이면 정말 웃지못할 희극적인 상황입니다. 실제 상황은 더 복잡했을 것이고 그렇게 말할 수 밖에 없었던 어떤 사유가 있을 것이라고 믿고 있습니다만...

    System Statistics를 완전히 자동으로 맡길 수가 없어서 어쩔 수 없이 수동으로 값을 바꾼다는 사실은 원래 목적에 완전히 위배되는 것입니다. 그래서 개인적으로 추천하지 않습니다. 하지만 현실은 훨씬 복잡하죠. 여기에 대한 상세한 논의가 이미 공론화된 바가 있습니다. 아래 URL을 읽어 보시고 추가적인 논의를 하시면 좋겠습니다.

    http://jonathanlewis.wordpress.com/2007/04/30/system-statistics/
    http://jonathanlewis.wordpress.com/2007/05/20/system-stats-strategy/
    http://jonathanlewis.wordpress.com/2007/10/17/system-statistics-3/

Write a comment


Session cached cursors와 v$open_cursor

오라클 2008.02.05 15:18
누가 이런 질문을 한 적이 있다.

"PL/SQL 내에서 Cursor를 선언해서 사용했는데, PL/SQL 수행이 끝난 후에 v$open_cursor 뷰를 보면 아직 Cursor가 Close되지 않고 Open 상태로 남아 있다. 원래 그런 건가? 아니면 버그인가? Cursor가 계속 Open 되어 있으면 문제가 되지 않는가? 언제 이 Cursor가 닫히는가?"

정답은 Session Cached Cursor와 관련이 있다.

Session Cached Cursor는 한 세션 내에서 세번 이상 수행된 Cursor을 Cache하는 기능을 의미한다. Cache된 Cursor는 Session이 Close되거나 LRU 알고리즘에 의해 최신 Cursor에 자리를 양보할 때 까지 유지된다. 즉, 우리가 강제로 Cursor를 Close 하더라도 실제로는 Cursor를 계속 Open해서 재활용할 수 있도록 해주는 것이다. Session Cached Cursor 영역은 일반 SQL Cursor 뿐만 아니라 PL/SQL에서 사용된 Cursor들을 Cache하는 역할도 같이 수행한다.  이렇게 함으로써 Cursor를 매번 Open하고 Close하는 오버헤드를 줄이게 된다.

하나의 Session이 Cache할 수 있는 Cursor의 수는 SESSION_CACHED_CURSORS 파라미터에 의해 결정된다.

아래 예를 보자.

-- 버전 정보
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

-- cursor.sql
declare
v_cursor sys_refcursor;
v_id number;
begin
for idx in 1 .. 10 loop  -- 같은 Cursor를 여러 번 수행
open v_cursor for 'select /* open_cursor */ * from t_session t';
fetch v_cursor into v_id;
close v_cursor;
end loop;
end;
/

-- check.sql. Cursor가 Open되어 있는지 확인
select sql_id, sql_text from v$open_cursor
where upper(sql_text) like 'SELECT /* OPEN_CURSOR%'
/

-- Session A. session_cached_cursors 파라미터 값이 20인 경우
alter session set session_cached_cursors=20;
@cursor.sql

-- Session B
@check.sql
8mt5gcq2a5wjx select /* open_cursor */ * from t_session t
--> Cursor를 Close했음에도 불구하고 Open되어 있다.

-- Session A. session_cached_cursors 파라미터 값이 0인 경우
alter session set session_cached_cursors=0;
@cursor.sql

-- Session B
@check.sql
No rows selected
<-- Cursor가 Close 상태이다.

위의 테스트 결과를 보면 알 수 있듯이 Cached된 Cursor들은 명시적으로 Close하더라도
실제로는 Close가 되지 않는 것을 확인할 수 있다.
이런 현상 때문에 V$OPEN_CURSOR 뷰에서 계속 관찰이 되는 것이다.

9.2.0.5까지는 PL/SQL에서 사용된 Cursor의 Cache를 결정하는 파라미터는 OPEN_CURSOR 였다.
하지만 그 이후로는 SESSION_CACHED_CURSORS 파라미터에 의해 결정된다.

Oracle Reference Manual에 이런 사실이 잘 표현되어 있다.

-- In Oracle 9i Reference Manual
OPEN_CURSORS specifies the maximum number of open cursors
(handles to private SQL areas) a session can have at once.
You can use this parameter to prevent a session from opening an
excessive number of cursors. This parameter also constrains the size of
the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as
statements are reexecuted by a user.

-- In Oracle 10g Reference Manual
This parameter(SESSION_CACHED_CURSORS) also constrains the size of
the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as
statements are re-executed by a user.

9.2.0.5 이후에는 SESSION_CACED_CURSORS 파라미터 값을 충분히 키워줄 이유가 되는 것이다.
SESSION_CACHED_CURSORS 파라미터에 대한 자세한 설명은 아래 URL을 참조한다.

http://wiki.ex-em.com/index.php/SESSION_CACHED_CURSORS

신고
Trackback 0 : Comments 4
  1. 욱짜 2008.02.13 08:41 신고 Modify/Delete Reply

    위의 테스트 결과는 10g에서만 의미가 있습니다. 9i까지는 다른 결과가 나올 수 있습니다. 버전별로 내부 메커니즘이 다르기 때문에 생기는 현상입니다.

  2. 이민규 2008.06.03 11:35 신고 Modify/Delete Reply

    안녕하세요 select sid, count(*) from v$open_cursor group by sid 의 결과와 파라메터 입니다.


    아래 보시면 파라메터 값이 100인데 cursor가 300개에 달합니다.
    이렇다면 파라메터를 300 이상으로하는 것이 성능을 향상 시킬까요?

    SID COUNT(*)
    ---------- ----------
    6130 292
    9315 296
    6215 297
    9766 298


    show parameter cached

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    session_cached_cursors integer 100

  3. 이민규 2008.06.03 11:41 신고 Modify/Delete Reply

    참고로 v$sesstat 에서 값은 매우 작습니다. 그렇다면

    v$open_cursor와 session cursor cache count 의 차이는 왜 나는 걸까요?

    저는 튜닝시 session cursor cache count를 바탕으로 session_cache_cursor를 술였습니다.

    288 : session cursor cache count

    SID STATISTIC# VALUE
    ---------- ---------- ----------
    6146 288 10
    6147 288 9
    6149 288 9
    6150 288 10
    6151 288 14
    6152 288 9
    6153 288 12
    6154 288 7
    6156 288 14
    6157 288 14
    6091 288 10

  4. 욱짜 2008.06.03 13:06 신고 Modify/Delete Reply

    session_cached_cursors의 값을 open_cursors나 v$open_cursor에 비례해서 맞출 필요는 없습니다. v$open_cursor에서 Count가 290개라는 것은 특정 작업을 수행했거나 현재 수행하면서 현재 Open 상태로 볼 수 있는 Cursor가 290개라는 것을 의미합니다.(여기에는 현재 Cache되거나 Keep된 Cursor들도 포함됩니다)

    즉 v$open_cursor = (현재 진짜로 Open되어 있는 Cursor들) + (Cache된 Cursor들) + (Keep된 Cursor들) 로 관찰된다고 보면 될 거 같습니다. 이 Max 값을 OPEN_CURSORS 파라미터로 제어하게 되구요.

    SESSION_CACHED_CURSORS 파라미터는 실제로 Cache Hit가 생기는 정도를 예상해서 적절하게 세팅하면 됩니다. 일단 OPEN_CURSORS와 무관하게 세팅한다고 봐도 무방할 겁니다. OPEN_CURSORS는 그냥 적절히 크게 지정해주면 됩니다.

    v$open_cursor에서 Cursor가 Open되어 있다는 것은 무엇을 의미할까?가 기술적으로는 더 중요한 문제입니다. v$open_cursor의 정의를 v$fixed_view_definition을 통해서 보면 v$kgllk(Library Cache Lock)에서 namespace가 0, 즉 Cursor인 것을 필터링한 결과라는 것을 알 수 있습니다. 즉, Shared Pool에 있는 Cursor Type의 Library Cache Object(LCO)에 대해 Library Cache Lock을 점유하고 있는 상태를 Cursor가 Open되었다고 부릅니다.

    v$open_cursor 뷰에서 관찰되었다는 것은 현재 특정 Cursor Type의 LCO를 사용하기 위해 Library Cache Lock을 점유하고 있는 상태를 의미합니다. Cursor를 Cache하거나 Keep한다는 것은 Library Cache Lock을 놓지 않고 계속 점유해서 LCO가 Shared Pool에서 밀려나지 않도록 방지한다는 것을 의미하구요.(여기에 대해서는 나중에 기회가 있으면 자세하게 논의하기로 하구요...)

Write a comment


Oracle SQL 튜닝 기법: TCF - Tuning by Cardinality Feedback

오라클 2008.01.24 20:10
Wolfgang Bretiling이라는 스위스를 기반으로 활동하는 Consultant가 있다. 이 사람의 전문 분야는 Oracle + PeopleSoft의 튜닝이다.

PeopleSoft과 같은 ERP 솔루션들은 Oracle과 무관하게, 아니 Oracle을 무시해서 설계된 Application으로 유명하다. 모든 DBMS에서 일관되게 동작 가능한 Application에 의한 필요악이라고나 할까... 이런 3rd Party Application의 튜닝에서 가장 큰 애로 사항이 쿼리를 직접 수정하는 것이 불가능하다는 것이다. 때문에 간혹 Oracle 관점에서의 튜닝이 불가능한 것으로 간주하기도 한다.

이 Wolfgang이라는 사람은 이런 상황에 착안해서 자신만의 쿼리 튜닝 기법을 개발했다. 하지만 쿼리 수정이 전혀 불가능한 상태에서 어떻게 (비교적) 자유롭게 쿼리를 튜닝할 수 있단 말인가?
놀랍게도 이 사람은 많은 시스템에서의 경험과 Oracle Optimizer에 대한 심도 깊은 지식을 기반으로 이것을 가능하게 하는 단순하면서도 심오한 방법을 체계화했다.

그 이름이 바로 TCF - Tuning by Cardinality Feedback이다. 풀어쓰면 "실행 예상 계획과 실제 실행 계획의 차이(Cardinality Feeback)에 의한 쿼리 튜닝 기법"이다. TCF는 다음과 같은 가정에 근거한다.

  • Oracle의 CBO는 Cardinality만 정확하면 나름대로 최적의 실행 계획을 생성한다.
  • Oracle의 통계 해석에서 오는 몇 가지 오류로 인해 Cardinality 계산이 비현실적인 경우가 있다.
  • 따라서, Oracle이 Cardinality를 잘 계산할 수 있도록 힌트를 주면 많은 경우 정상적인 실행 계획을 만들 것이다.


이런 가정 하에서 DBMS_STATS.SET_XXX 류의 메소드를 이용해서 Oracle이 최적의 Cardinality를 계산할 수 있도록 통계 값을 보정시켜주는 것이 이 TCF의 핵심이다. Oracle이 필요로 하는 Cardinality를 추론하게 해주는 것이 Explain Plan(실행 예상 계획)과 Execution Plan(실제 실행 계획)을 비교하고 해석하는 능력이다.

마침 Oracle 10g부터는 gather_plan_statistics 힌트나 statistics_level = all 과 함께 dbms_xplan.display_cursor 함수를 사용하면 TCF 적용에 필요한 데이터(실행 예상 로우 건수와 실제 실행 로우 건수)를 손쉽게 구할 수 있으니 이 Wolfgang의 TCF의 뛰어난 점을 Oracle이 수용한 것처럼 생각될 정도이다.

아래 URL에서 TCF 사용에 필요한 모든 이론적 도구를 얻을 수 있다.
http://www.centrexcc.com/papers.html

쿼리를 직접 수정할 수 없는 3rd party Application 튜닝에 최적의 도구를 하나 확보하게 된 셈이다.

PS) 이 TCF는 실제로 많은 엔지니어들이 자신도 인식하지 못하고 사용하고 있는 방법이다. 이런 것을 체계화하고 이론적 무장을 갖추는 능력이 부족한 우리 현실이 아쉬울 뿐이다.
신고
Trackbacks 7 : Comment 0

Write a comment


QB_NAME Hint의 편리함 - Oracle 10g

오라클 2008.01.17 14:51
Oracle 10g에서 QB_NAME 이라는 유용한 힌트가 추가되었다. QB_NAME은 Query Block에 사용자가 직접 별명(이름)을 부여하는 것을 가능하게 해주는 힌트이다.

오라클은 SQL Text를 여러 개의 Query Block 으로 나누어 관리한다. 가령 하나의 SQL Text에 Inline View가 하나 포함되어 있다면 내부적으로 두 개의 Query Block이 존재한다.

아주 간단한 예제만으로도 이 힌트가 얼마나 유용한지 알 수 있다.

아래와 같은 쿼리의 실행 계획을 보자.

explain plan for select t1.id1, t2.name2, x.id4, x.name5,
    (select count(*) from t1 s where s.id1 = t1.id1) as id1_1
from t1, t2, t3, t5,
    (select t4.id4, t5.name5
        from t4, t5
        where t4.id4 = t5.id5 and t5.name5 like '%c%') x
where t1.id1 = t2.id2
    and t2.id2 in (select id3 from t3 where name3 like '%b%')
    and t2.id2 = x.id4
    and t3.id3 = t1.id1
    and t5.name5 = t1.name1;
   
select * from table(dbms_xplan.display(null,null));


----------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     1 |   113 |    14  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE         |        |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN      | T1_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |  HASH JOIN              |        |     1 |   113 |    14  (15)| 00:00:01 |
|*  4 |   HASH JOIN SEMI        |        |     1 |   106 |    12  (17)| 00:00:01 |
|   5 |    NESTED LOOPS         |        |     1 |    86 |     9  (12)| 00:00:01 |
|*  6 |     HASH JOIN           |        |     1 |    73 |     8  (13)| 00:00:01 |
|*  7 |      HASH JOIN          |        |     1 |    53 |     6  (17)| 00:00:01 |
|   8 |       NESTED LOOPS      |        |     1 |    33 |     3   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL| T5     |     1 |    20 |     2   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN | T4_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS FULL | T2     |     1 |    20 |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS FULL  | T1     |     1 |    20 |     2   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN    | T3_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|* 14 |    TABLE ACCESS FULL    | T3     |     1 |    20 |     2   (0)| 00:00:01 |
|  15 |   TABLE ACCESS FULL     | T5     |     1 |     7 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

실행 계획을 해석하는데 숙달된 전문가가 아니라면 아마 지레 겁이 날 것이다. 서너 개의 인라인 뷰와 서브 쿼리만으로 실행 계획이 금방 매우 복잡해진다. 가장 큰 문제는 실행 계획의 특정 오퍼레이션(1,2,...,15)이 SQL Text의 어느 부분과 매칭되는지 직관적으로 파악하기가 어렵다는 것이다.

QB_NAME 힌트는 이런 복잡한 쿼리의 실행 계획을 해석하는데 아주 유용한 도구가 된다. QB_NAME 힌트를 이용한 예는 다음과 같다.

-- Statistics Level 을 All로 변경한다.
alter session set statistics_level = all;

-- QB_NAME 힌트 부여
explain plan for select /*+ qb_name(main) */ t1.id1, t2.name2, x.id4, x.name5,
    (select /*+ qb_name(scalar) */ count(*) from t1 s where s.id1 = t1.id1) as id1_1
from t1, t2, t3, t5,
    (select /*+ qb_name(inline) */ t4.id4, t5.name5
        from t4, t5
        where t4.id4 = t5.id5 and t5.name5 like '%c%') x
where t1.id1 = t2.id2
    and t2.id2 in (select /*+ qb_name(subquery) */ id3 from t3 where name3 like '%b%')
    and t2.id2 = x.id4
    and t3.id3 = t1.id1
    and t5.name5 = t1.name1;

-- Cursor에서 ALL stats를 조회한다.
select * from table(dbms_xplan.display(null,null, 'ALL'));

----------------------------------------------------------------------------------
| Id  | Operation               | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     1 |   113 |    14  (15)| 00:00:01 |
|   1 |  SORT AGGREGATE         |        |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN      | T1_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |  HASH JOIN              |        |     1 |   113 |    14  (15)| 00:00:01 |
|*  4 |   HASH JOIN SEMI        |        |     1 |   106 |    12  (17)| 00:00:01 |
|   5 |    NESTED LOOPS         |        |     1 |    86 |     9  (12)| 00:00:01 |
|*  6 |     HASH JOIN           |        |     1 |    73 |     8  (13)| 00:00:01 |
|*  7 |      HASH JOIN          |        |     1 |    53 |     6  (17)| 00:00:01 |
|   8 |       NESTED LOOPS      |        |     1 |    33 |     3   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL| T5     |     1 |    20 |     2   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN | T4_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS FULL | T2     |     1 |    20 |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS FULL  | T1     |     1 |    20 |     2   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN    | T3_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|* 14 |    TABLE ACCESS FULL    | T3     |     1 |    20 |     2   (0)| 00:00:01 |
|  15 |   TABLE ACCESS FULL     | T5     |     1 |     7 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SCALAR
   2 - SCALAR       / S@SCALAR
   3 - SEL$EA1A1EE6
   9 - SEL$EA1A1EE6 / T5@INLINE
  10 - SEL$EA1A1EE6 / T4@INLINE
  11 - SEL$EA1A1EE6 / T2@MAIN
  12 - SEL$EA1A1EE6 / T1@MAIN
  13 - SEL$EA1A1EE6 / T3@MAIN
  14 - SEL$EA1A1EE6 / T3@SUBQUERY
  15 - SEL$EA1A1EE6 / T5@MAIN

QB_NAME 힌트를 통해 부여한 별명을 통해 정확하게 어떤 Operation이 SQL Text의 어느 부분과 연결되어 있는지 매우 직관적으로 파악할 수 있다. 이 기능을 이용하면 매우 길고 복잡한 쿼리에서도 문제가 되는 부분을 좀 더 쉽게 파악할 수 있다.

가령 매우 복잡한 쿼리에서 특정 인라인 뷰가 실행 계획에서 어느 부분에 해당하는지 파악하려면 다음과 같이 QB_NAME 힌트를 부여하면 된다.

select
 ... { very complex query here } ...
from ( ... ( select /*+ QB_NAME(problematic_query) */ ... ) ) a, ( ... ( ... ) ) b,
    c, d, ...


QB_NAME 힌트의 또 하나의 위대한 점은 우리가 부여한 별칭을 조회 뿐만 아니라 "사용"도 가능하다는 것이다. 아래 예를 보면...

-- 우리가 부여한 subquery 이름을 이용해 no_unnest 힌트를 부여한다.
explain plan for select /*+ qb_name(main) no_unnest(@subquery) */ t1.id1, t2.name2, x.id4, x.name5,
    (select /*+ qb_name(scalar) */ count(*) from t1 s where s.id1 = t1.id1) as id1_1
from t1, t2, t3, t5,
    (select /*+ qb_name(inline) */ t4.id4, t5.name5
        from t4, t5
        where t4.id4 = t5.id5 and t5.name5 like '%c%') x
where t1.id1 = t2.id2
    and t2.id2 in (select /*+ qb_name(subquery) */ id3 from t3 where name3 like '%b%')
    and t2.id2 = x.id4
    and t3.id3 = t1.id1
    and t5.name5 = t1.name1;

select * from table(dbms_xplan.display(null,null, 'ALL'));

-- Subquery Unnesting이 Disable 됨으로써 Filter 조건이 사용되었다.
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    93 |    13  (16)| 00:00:01 |
|   1 |  SORT AGGREGATE              |        |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN           | T1_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |  FILTER                      |        |       |       |            |          |
|*  4 |   HASH JOIN                  |        |     1 |    93 |    12  (17)| 00:00:01 |
|   5 |    NESTED LOOPS              |        |     1 |    86 |     9  (12)| 00:00:01 |
|*  6 |     HASH JOIN                |        |     1 |    73 |     8  (13)| 00:00:01 |
|*  7 |      HASH JOIN               |        |     1 |    53 |     6  (17)| 00:00:01 |
|   8 |       NESTED LOOPS           |        |     1 |    33 |     3   (0)| 00:00:01 |
|*  9 |        TABLE ACCESS FULL     | T5     |     1 |    20 |     2   (0)| 00:00:01 |
|* 10 |        INDEX RANGE SCAN      | T4_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS FULL      | T2     |     1 |    20 |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS FULL       | T1     |     1 |    20 |     2   (0)| 00:00:01 |
|* 13 |     INDEX RANGE SCAN         | T3_IDX |     1 |    13 |     1   (0)| 00:00:01 |
|  14 |    TABLE ACCESS FULL         | T5     |     1 |     7 |     2   (0)| 00:00:01 |
|* 15 |   TABLE ACCESS BY INDEX ROWID| T3     |     1 |    20 |     1   (0)| 00:00:01 |
|* 16 |    INDEX RANGE SCAN          | T3_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

이 유용한 힌트를 복잡한 쿼리에서 활용할 수 있는 기회를 갖기를 바래본다.


신고
Trackback 0 : Comment 0

Write a comment

티스토리 툴바