메뉴 건너뛰기

bysql.net

2.16_WCOTR*_(Where_Current_Of_To_Rowid)

ms 2011.09.26 23:26 조회 수 : 2180

2.16 WCOTR* ( Where Current Of To Rowid ) :

WHere Current Of 를 사용하여 Index Scan 을 회피하라.

 

PRO*C , PL/SQL 에서 많은 양의 데이터를 Cursor For Loop 로 선언 후 건건이 update/delete 를 처리한다.

집합 처리가 유리 하지만. 그건이 불가능 하거나 가능하나 sql이 너무 기어질 경우는 유지보수 관점에서 cursor for loop를 사용해야 한다.

많은 개발자들이 cursor for loop 내에서도 아직도 pk 인덱스나 unique 인덱스를 사용하여 dml 문을 실행한다. wcort 을 사용하면 인덱스 Access 단계를 제거 할수 있는데도 밀이다.

 

Cusor for loop  처리시 개발자들이 일반적으로 코딩 하는 방식의 비효율성을 설명 하고  solution 을 제시할 것이다.

 

EX )

 

create table t1 as

with generator as ( select /*+ materialize */ rownum as id 

                            from all_objects

                  where rownum <= 3000)

select /*+ ORDERED USE_NL(v2)*/

  10000 + rownum id,

   trunc( DBMS_RANDOM.VALUE(0,5000)) nl,

   rownum score,

   rpad('x',1000 ) probe_padding

from generator v1, generator v2

where rownum <= 100000;

 

alter table t1 add constraint t1_pk primary ke(ID);

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

 

 

1.Cursor for loop 를 사용하여 건건이 update 하는 가장 흔하게 볼수 있는 PL/SQL 패턴

 

declare sursor c is

select * from t1 from update;

begin for rec in c

loop

update /*+pk_index_text*/t1 -->10만 번 pk 인덱스를 사용하여 update

set score = score + 0

whre id = rec.id;

end loop;

 

commit;

end;

 

위 pl/sql의 elapsed time 은 11.80 초이며 이 수치는 10번을 실행하여평균값을 취한 것이다.

 

2.solution

declare sursor c is

select *

from t1

for update;

begin

for rec in c

loop

update/*+ current_of_text*/ t1 --> 10만번 current of 를 사용하여 update

set score = score + 0

where current of c;

end loop;

comit;

end;

 

위 pl/sql의 elapsed time 은 10.80 초 이며 이 수치 또한 10번을 실행한 평균값이다.

테스트의 정확성을 위해 buffer cache 를 flush 하였다.

 

pk 인덱스를 이용한 update 보다 current of 를 사용한 update 가 10% 정도 빠른것을 알수 있다.

그렇다면 10% 차이는 무엇인가.

v$sqlarea 에서 수행된 SQL 을 보면 알 수 있다.

 

select sql_text

from v$sqlarea

where sql_text like '%current_of_text%';

결과

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

update /*+ current_of_text*/ t1 set score = score + 0 where rowid = :b1;

 

위 결과처럼 where current of를 사용하면 rowid 를 이용하여 access 하는 것을 알 수 있다.

rowid access 는 한건을 access 하는 경우 오라클 에서 가장 빠른 access 방식이다.

 

WHERE CURRENT OF 를 WHERE ROWID = 로 변환하는 작업은 Oracle Transformer 가 변한한 것이아니다. 왜냐면 Transformer 가 변환한 SQL은 절대 v#sqlarea에 나타나지 않는다.

 

하지만 WHERE CURRENT OF 를 사용한 경우에는 위의 경우 처럼 조회가 가능하므로 Parsing 이전 단계에서 SQL을 변환한 것이다  이것은 PL/SQL이나 PRO*C의 컴파일러가 SQL을 수정한 것이다. 아래 처럼 10046 이벤츠를 이용해도 마찬가지로 미리 변환된 SQL을 볼수있다.

 

PARSING IN CURSOR #5len=73 uid=82 oct=6 lid=82 tim=3009594017 hv=1974126717 ad='22044754' sqlid='5p2jmajuupk3x'

UPDATE/*+ current_of_test*/ T1 SET SCORE =SCORE +0 WHERE ROWID = :B1

END OF SIMT

 

일반적인 경우 Transformer가 변환한 SQL은 10053 Trace 의 Unparsed Query 에서만 볼수 있다.

 

한가지 주의할 점은 WHERE CURRENT OF 를 사용하려면 Main Cursor가 반드시 SELECT FOR UPDATE 문이어야 한다는 것이다.

 

하지만 FOR UPDATE 가 없는 커서에서도 아래처럼 사용하면 같은 효과를 누릴 수 있다.

 

DECLARE 

 CURSOR c IS

   SELECT t1.ROWID,t1.*

      FROM t1;

BEGIN

  FOR rEC IN c

  LOOP

    UPDATE /*+ rowid_test*/ t1

      SET score = score + 0

  WHERE ROWID = rec.ROWID;

END LOOP;

 

COMMIT;

END;

 

SELECT FOR UPDATE 를 사용하지 않았음에도 불필요한 인덱스 Access를 제거하고 가장 빠른 엑세스 방법인 ROWID 를 사용할 수 있다.

악조건에서도 최선의 방법을 찾으려 노력해야 한다.

만약 이런 환경에서 PK인덱스를 사용하도록 코딩되어있다면 당장 달려가서 프로그램을 수정 하라고 말하고 싶다...;; 라고..저자는 말합니다....