태터데스크 관리자

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

태터데스크 메시지

저장하였습니다.

'오라클'에 해당되는 글 7건

  1. 2008.02.25 Oracle 10g의 재미있는 버그 - Index Rebuild 후 Logical Reads 증가 현상 (3)
  2. 2008.02.20 OPT_PARAM Hint - Statement Level에서 Optimizer Parameter 변경하기 (6)
  3. 2008.02.18 Oracle 10g의 Cost Based Push Predicate에 의한 성능 저하... (1)
  4. 2008.02.17 RTFM and BAAG - Oracle 사용자의 명제들 (1)
  5. 2007.08.29 AWR 써야 하나 말아야 하나...
  6. 2007.08.10 Oracle 11g 다운로드 가능!!! (2)
  7. 2007.07.27 Oracle CR Read의 마법을 풀어봅시다. (6)

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


AWR 써야 하나 말아야 하나...

오라클 2007.08.29 17:19
오라클 10g에서 추가된 AWR(Automatic Workload Repository)는 지금까지 오라클에 추가된 기능들 중 성능 관리 측면에서 가장 현신적이라고 할 수 있다. 성능 이력 데이터를 보관함으로써 기존에는 불가능했던 마법같은 일들(예를 들어 자동 진단. ADDM)이 가능해졌다.

성능 분석에 필요한 모든 데이터의 상세한 이력을 자동으로 관리해준다니... 얼마나 놀라운 발상인가.

하지만, 일부 운영 시스템에서 AWR이 지닌 버그나 성능 저하 등의 문제 때문에 AWR 기능 자체를 비활성화시키는 경우를 종종 볼 수 있었다.

무지막지한 STATISTICS_LEVEL = BASIC 값 부여!!! -,.-

흠... 이것이 옳은 일일까? 모르겠다.

하지만 나의 제안은 이렇다.

  • AWR은 쓰지 않기에는 너무나 좋은 기능이다.
  • 하지만 성능 저하 등의 문제가 있다.
  • 따라서 성능 저하를 최소화시키면서 AWR 본연의 기능은 그대로 쓰자
다행히 몇 개의 히든 파리미터를 통해 AWR의 부하를 조절할 수 있다. AWR에서 가장 많은 양의 데이터를 차지하는 ASH(Active Session History)의 양을 조절하는 것이다.

  • _ASH_ENABLE: FALSE로 하면 ASH 기능을 끌 수 있다. 만일 Active Session History 까지는 필요 없다고 하면 이 파라미터를 쓸 수 있다.
  • _ASH_DISK_WRITE_ENABLE: FALSE로 하면 ASH를 저장하는 기능만 끌 수 있다. 즉 V$ACTIVE_SESSION_HISTORY 뷰는 여전히 사용하되, DBA_HIST_ACTIVE_SESS_HISTORY 뷰는 사용하지 않는다.
  • _ASH_DISK_FILTER_RATIO: 기본값은 10. 즉, 메모리:디스크 비율이 10:1이 되게끔 저장한다. 만일 ASH가 1초 간격으로 수집된다면 디스크에는 10초 간격으로 저장된다. 따라서 이 값을 크게 하면 부하를 줄일 수 있다. 가령 60으로 변경하면 1분 간격으로 디스크에 저장된다. 이 파라미터 조정을 통해 부하를 조절하는 것이 가장 이상적이라고 판단된다.
  • _ASH_SAMPLING_INTERVAL: 기본값은 1000(ms). 즉 ASH를 1초 단위로 수집한다. 이 값을 크게 하면 ASH의 양이 줄고 그 만큼 부하도 줄게 된다.

만일 AWR을 통째로 사용하지 않고 있다면 위의 방법을 적용해보는 것은 어떨까...?


신고
tags : ASH, AWR, Oracle, 오라클
Trackback 0 : Comment 0

Write a comment


Oracle 11g 다운로드 가능!!!

오라클 2007.08.10 19:01

http://www.oracle.com

비록 Linux 버전만 있지만, 11g 정식버전 다운로드가 가능해졌다.

아마 조만간 Unix 버전, Windows 버전이 올라오겠지...

더불어 11g 문서도 같이 올라와 있다.

바야흐로 11g의 시대가 도래했다.

이번 11g은 10g의 확장판이면서, 초대용량 DB를 지원하기 위한 다양한 기능들이 추가되었다.

앞으로 많은 분석과 연구의 날들이 기다리고 있을 듯...

신고
tags : 11g, Oracle, 오라클
Trackback 0 : Comments 2
  1. 멀더엄마 2007.08.10 19:22 신고 Modify/Delete Reply

    OWI 11g 준비해야겠네~

  2. 욱짜 2007.08.10 19:55 신고 Modify/Delete Reply

    또? 같은 주제로는 안적지롱~

Write a comment


Oracle CR Read의 마법을 풀어봅시다.

오라클 2007.07.27 20:20

일반적인 사실

오라클은 블록 읽기 작업시 기본적으로 CR을 수행한다.

CR은 Consistent Read(일관된 읽기)의 약자로 Query가 시작하는 시점의 SCN과 호환되는 일관된 버전의 데이터, 즉 과거 버전의 데이터를 읽는다는 것을 의미한다.

이를 그림으로 표현하면 다음과 같다.

위의 그림을 요약해서 설명하면...

  • Session A가 블록 X의 특정 로우를 변경하면 블록 X는 Dirty 블록이 된다.
  • Session B가 Dirty 블록 X를 읽을려고 하면 오라클은 블록 X의 과거 버전인 CR 블록을 만들고롤백을 수행한다. 즉, Dirty상태로 되기 전의 데이터를 적용해서 과거 버전의 데이터를 만든다. (이 과정을 CR 카피라고 부른다)
  • Session B는 이 CR 블록을 읽어 들인다.

여기까지가 일반적으로 알려진 사실이다.

호기심이 많은 여러분을 위해 좀 더 깊은 곳까지 가보자.

좀 더 깊은 곳의 진실

오라클에서 블록의 상태를 보는 가장 좋은 방법은Fixed View 중 하나인 X$BH 뷰를 보는 것이다. 이 중에서 CR 작업의 정체를 파악하려면 다음과 같은 컬럼들의 의미를 알아야 한다.

  • DBARFIL : 데이터파일 번호
  • DBABLK : 블록 번호
  • STATE : 블록 상태. 3 인 경우가 CR이다. 0 = Free, 1 또는 2 가 Current 상태의 블록이다(즉 CR의 반대)
  • OBJ : 오브젝트 번호. dba_all_objects.data_object_id 또는 object_id와 조인된다.
  • CR_SCN_BAS, CR_SCN_WRP : CR 블록인 경우 SCN 정보
  • CR_UBA_FIL, CR_UBA_BLK : CR 블록인 경우 롤백에 사용된 언두 정보

다음과 같은 스크립틀 통해 X$BH 뷰를 조회한다.

xbh.sql

select obj, dbarfil, dbablk, state, cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk
from sys.xm$bh where obj in (오브젝트 번호들...)
order by obj

이제 간단한 테스트를 통해 실제로 오라클이 CR 블록을 어떻게 관리하는지 알아보자.

-- cr_test 테이블 만들고 1건 Insert

CREATE TABLE cr_test(id INT PRIMARY KEY, name VARCHAR2(10));

INSERT INTO index_test VALUES(1, 'name1');

COMMIT;

SELECT data_object_id FROM all_objects WHERE object_name = 'CR_TEST'

==> 59262, 즉 오브젝트 아이디 = 59262

SELECT dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) FROM cr_test;

==> 4, 319 즉 파일 번호 = 4, 블록 번호= 319

-- 우선 버퍼 캐시를 Flush
ALTER SYSTEM FLUSH BUFFER_CACHE

이 상태에서세 개의 세션 Session A, B, C에서 다음과 작업을 수행하면 xbh.sql 스크립트를 이용해 4/319 블록의 상태를 확인해보자

Session A: SELECT * FROM cr_test;

Session C: @xbh

--> 4/319 블록이 1번(Current) 상태로 존재함을 알 수 있다. 아직 Dirty 상태가 아니기 때문이다.

-- Update를 수행해서 Dirty 블록을 만든다.

Session A: UPDATE cr_test SET name = 'name2';

Session C : @xbh

--> 4/319 블록에 대해 CR 블록(State=3)이 생겼음을 알 수 있다. 즉 Update에 의해 Dirty 블록을 만들어지는 시점에 오라클은 하나의 CR 카피본을 만든다.

-- 이제 Session B에서 Dirty 블록에 대해 읽기를 수행한다.

Session B : select * from cr_test;

--> CR 블록이 하나 더 생겼음을 알 수 있다(맨위). 이 CR 블록은 기존의 CR블록(SCN_BAS가 399558603인)의 카피본이며 실제 롤백시 수행되었음(CR_UBA_FIL = 2)을 알 수 있다.

Session B 에서 십여 차례 select * from cr_test 쿼리를 수행한다.

--> CR 블록이 최대 5개까지만 만들어지는 것을 확인할 수 있다.Select 요청이 있을 때마다 가장 가까운 CR 블록을 이용해 새로운 CR 카피본을 만들고 필요한 경우 롤백을 수행한다. 5개를 초과하면 가장 오래된 CR 블록은 밀려난다. 이 값은 _DB_BLOCK_MAX_CR_DBA 파마리터에 의해 결정된다.

아래 결과는 Session B에서 계속해서 Select를 수행한 결과인데 CR_SCN_BAS가 최근 값을 모두 바뀐 것을 확인할 수 있다.

오라클이 CR 블록을 유지하는 방식은 아래와 같이 설명할 수 있다.

Time Session1 Session2

T0 …

T1 Update (Dirty block)

T2 Select CR 1(T2) 생성

T3 Select CR 2(T3) 생성

T4 Select CR 3(T4) 생성

T5 Select CR 4(T5) 생성

T6 Select CR 6(T6) 생성

T7 Select CR 7(T7) 생성 -->CR1 제거

T8 Select CR 8(T8) 생성--> CR2 제거

여기서 다음과 같은 기본적인 질문들을 던질 수 있다.

1. 왜 오라클은 최근 5개의 CR 블록을 계속 갱신하면서 유지하는가?

이에 대한 대답은 CR 작업을 좀 더 효율적으로 하기 위해서이다. 즉, 최대 5개까지 가장 최근의 CR 블록을 유지함으로써 과거 버전 읽기 작업에 드는 부하를 최소화하기 위함이다. 오라클은 Select 요청에 맞는 CR 블록을 생성하기 위해 CR 카피를 만들고, Undo로부터 롤백을 수행하는 일련의 작업을 수행하는데, 이 작업의 부하를 최소화하기 위해 버퍼 캐시에 최근의 CR 블록을 여러 개 유지하는 것이다.

2. 하나의 블록당 여러 개의 CR 블록을 유지함으로써 메모리의 낭비를 유발하지 않는가?

실제로 그렇다. 오라클의 특정 버전에서는 CR 블록 수가 너무 많아져서 버퍼 캐시의 효율성이 떨어지는 현상이 실제로 있었다. 하지만 최신 버전의 오라클은 CR 블록의 위치를 LRU 리스트의 꼬리쪽으로 위치시킴으로써 메모리 부족 현상이 생길때 되도록 빨리 밀려나도록 해준다.

CR, CR, CR, ...

오라클의 CR 블록과 Consistent Read는 다른 DB에서는 잘 지원하지 못하는 MVRC(Multi Versioning Read Consistency)를 가능하게 하는 핵심 메커니즘이다.

여러분은 오라클을 잘 알고 있다고 생각하는가!!!그렇다면 여기서 제시한 CR 작업에 대한 이해를 여러분의 지식 주머니안에 넣고 다녀야 할 것이다. ^^

더불어 아래에 필자가 저술한 책을 한번씩 읽어 보길 권한다. 책 광고 아니냐고? 책 광고 맞다. 하지만 읽게 되면 여러분의 내공이 아마 한 갑자 정도는 늘어날 것이다...

신고
tags : Oracle, 오라클
Trackback 0 : Comments 6
  1. 멀더엄마 2007.07.30 11:44 신고 Modify/Delete Reply

    좀 도와 주십쇼..... ㅋㄷㅋㄷ

  2. 쏘심이 2007.10.09 19:53 신고 Modify/Delete Reply

    우리 서울사람 이에요~~

  3. 멋쟁이 2007.10.10 19:02 신고 Modify/Delete Reply

    맞다.. 내공 쌓인다..

  4. 고구마 2008.12.12 12:58 신고 Modify/Delete Reply

    그림파일 링크가 깨진건지 안보이네요..
    저만 그런가요?

  5. 욱짜 2008.12.13 00:43 신고 Modify/Delete Reply

    블로그를 네이버에서 티스토리로 이관하는 과정에서 이미지에 오류가 생겨서 그렇습니다.

    나중에 기회가 되면 더 쉽게 설명하는 기회를 갖도록 하겠습니다.

  6. 오라퍼그 2010.06.24 15:52 신고 Modify/Delete Reply

    감사합니다.
    책을 한번보고. 이글을 오늘 다시 보게되니. 조금은 기억이 나네요. ㅎㅎ
    내공이 내 머리속에 기억으로 있으면 좋겠습니다.

Write a comment

티스토리 툴바