오늘은 Sequence 의 오작동 (번호를 건너뛰는 Sequence) 라는 글에 이어서
Sequence 사용시 주의해야 되는 또다른 상황을 설명한다.
예전에 고객사에서 일어난 일이다.
Sequence 채번시 IF 조건에 따라 채번 할수도 혹은 안할수도 있는상황에서
Sequence 가 IF 조건을 무시하고 무조건 채번된다는 보고가 있었다.
아래는 T1 테이블에 INSERT 하는 아주 간단한 SQL 로서 t1_temp 테이블의 id 컬럼값이 null 이면 채번하고
null 이 아니면 id 컬럼값을 그대로 사용하는 로직이 들어있다.

insert into T1 (id, x_padding)
select  decode( id, null, my_seq.nextval, id) as id --> id 가 null 이면 nextval 아니면 id 그대로 사용.
       ,x_padding
  from t1_temp;

하지만 DECODE 문과 관계없이 Sequence 는 무조건 채번되었다.
상황을 재현하기 위하여 테이블및 시퀀스를 생성한다.

CREATE SEQUENCE my_seq                             --> 5만 1번부터 시작하는 SEQUENCE 생성
  START WITH 50001
  CACHE 20;
 
create table t1_temp as                                     --> 작업용 테이블 생성
with generator as (   select  /*+ materialize */  
                                        rownum as id
                               from all_objects
                            where rownum <= 3000 )
select case when rownum > 50000 then null     --> 10만건을 생성 하는데 5만번 까지만 채번함.
                  else rownum
       end id,
       rpad('x',1000)      x_padding
  from generator    v1,
         generator    v2
 where rownum <= 100000; 

create table T1 as               --> 작업용 테이블을 이용하여 10만건을 insert 할 테이블 생성
select *
  from t1_temp
 where rownum < 1; 
 
alter table T1 add constraint T1_PK primary key(id);   --> PK 생성

이제 테이블및 Sequence 가 생성되었으므로 맨위의 SQL 을 실행하고 결과를 SELECT 해보자

insert into T1 (id, x_padding)
select  decode( id, null, my_seq.nextval, id) as id --> id 가 null 이면 nextval 아니면 id 그대로 사용.
       ,x_padding
  from t1_temp;

commit;

select count(*)
  from T1
 where id between 50001 and 100000;


위 count 쿼리의 결과는 0 이다.
분명히 5만1번부터 10만번까지 시퀀스로 채번이 되어 5만건이 count 되어야 한다.
뭔가 이상하지 않은가?
이것을 Sequence 버그라고 생각하는 사람도 있지만 필자는 조금 다르게 생각한다.
이유는 Sequence 의 원리에 있다.
Sequence 는 where 절 조건을 만족하면 무조건 채번이 되어버린다.
즉 decode 나 case 문 등의 분기 로직은 where 절이 아니라 select 절에 있기 때문에 무조건 채번 되는 것이다.
그렇다면 조건에 따라서 채번할수 있는 방법은 없는것일까?

Solution 1:
아래와 같은 간단한 함수를 만들어서 call 한다.

CREATE OR REPLACE function my_func return number as
    v_seq number;
begin

       select my_seq.nextval
         into v_seq
         from dual; 
 
return v_seq;

end;
/

insert into T1 (id, x_padding)
select  decode(id, null, my_func, id) as id        --> id 가 null 인 경우만 채번함수가 실행됨.
       ,x_padding
  from t1_temp;


Solution 2:
insert 문을 id 컬럼 기준으로 null 일 때와 null 이 아닐때로 분리한다.

insert into T1 (id, x_padding)
select  my_seq.nextval
       ,x_padding
  from t1_temp
where id is null
;

insert into T1 (id, x_padding)
select  id
        ,x_padding
  from t1_temp
where id is not null
;

한가지 주의사항은 많은수의 Function call 이 부담이 되는 상황이라면 2번방법을 택해야 한다.
과도한 Function call 은 DBMS 에 무리를 주기 때문이다.
 
편집후기: 위의 글중에 일부가 오류가 있어서 일부 수정되었음을 밝혀둔다.

다중 INSERT 문을 사용해야 한다는 의견이 있었다.

INSERT FIRST                                                             
  WHEN id is not null THEN                                                    
    INTO t1 VALUES( id, x_padding)  
  WHEN id is null THEN                                                  
    INTO t1 VALUES( my_seq.nextval, x_padding)                           
select  id
        ,x_padding
  from t1_temp                  
;

가능할 것처럼 보이지만 위의 SQL 로도 해결이 안된다는걸 알아야 한다.
Posted by extremedb
,

고객사의 DBA 로 부터 Sequence가 오작동 한다는 보고를 받았다.
현상은 Sequence 의 NEXTVAL 을 하면 몇만건이 없어진다는데 있었다.
일단 필자는 NOORDER 로 설정되어 있는지 질문을 하였으나 시스템이 RAC 가 아니었다.

해당 Sequence를 분석해보니 CACHE 가 10만이 잡혀 있었고 MAXVALUE가 99999999 으로 되어 있었으며
NOCYCLE 로 설정된 상태였다.
계산상으로 1년정도가 지나면 MAXVALUE 에 도달해서  해당 Sequence를 select 하는 순간 error 가
날수 있는  상황 이었다.

특이한점은 Sequence의 사용주기였다.
토, 일,월~목요일날 전혀 사용하지 않고 금요일에만 일괄배치로 최대 몇만건이 들어올수 있다는 것이었다.

그렇다면 이런 현상의 원인은 무었일까?
원인은 아주 간단하다.
자주 사용하지 않는 OBJECT 는 Shared Pool 에서 밀려난다.
밀려난 Sequence는 Caching 된 번호들이 메모리상에서 없어지게 됨으로 번호가 건너뛰는 것 같이 느낀것이다.
결국은 Sequence의 오작동이 아닌 자연스러운 현상인 것이다.

그렇다면 위와같이 Sequence의 사용주기가 긴(일주일에 한번) 시스템에서 이런현상을 방지하려면 어떤 방법이 있을까? 
이런경우는 dbms_shared_pool.keep 을 사용하는 것이 정답이다.
아래의 예제를 보자.

dbms_shared_pool.keep('SCOTT.PKG_PAYCALCULATE', 'P');

PKG_PAYCALCULATE 라는 패키지를  Shared Pool 에 keep 시켰다.

'P' 의 의미는 PL/SQL 로 만들어진 프로그램이라는 뜻이고 들어올수 있는 인자값은 아래와 같다.

P   :package/procedure/function
Q   :sequence
R   :trigger
T   :type
JS  :java source
JC  :java class
JR  :java resource
JD  :java shared data 


dbms_shared_pool.keep 사용후에 아래와 같은 스크립트로 적재여부를 확인 할수 있다.

SELECT  owner, NAME, TYPE, sharable_mem, loads, kept, executions, locks, pins
  FROM  v$db_object_cache
 WHERE  TYPE IN ('PACKAGE', 'PACKAGE BODY', 'JAVA CLASS')
   AND  OWNER = 'SCOTT'
ORDER BY 3, 7
;

dbms_shared_pool.keep을 사용하기 전에 아래와 같은 스크립트로 Object 의 크기를 확인하는 습관을 들여야 한다.

SELECT  owner, name, trunc((sum(parsed_size)+sum(code_size))/1024)||'K'
  FROM  dba_object_size
 WHERE  owner = 'SCOTT'
  AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'JAVA CLASS')
GROUP BY owner, name;



한가지 주의사항은 alter system flush shared_pool 명령을 수행해도 이미 keep 시킨 object는
Shared Pool 에서 떨어지지 않는다.
Aging Out 시키는 유일한 방법은 dbms_shared_pool.unkeep 을 사용하는 것이다.

Posted by extremedb
,

Min/Max 처리시 인덱스가 있을경우 대부분의 Plan 이 자동으로 INDEX RANGE SCAN (MIN/MAX) - FIRST ROW 처리가 된다.
위의 처리가 지원 되면서 부터는 전통적인 튜닝 방법인 INDEX_DESC 힌트 사용후에 WHERE 절에 ROWNUM = 1 을 추가하는 방식이 더이상 필요없는걸까?
모든규칙에는 예외가 있다는 말을 기억하자.
파티션이 되어있는 테이블은 PLAN 상에 INDEX RANGE SCAN (MIN/MAX) - FIRST ROW 로 처리가 되어
이상이 없는것 처럼 보이지만 비효율이 있다.
심지어 INDEX_DESC + ROWNUM = 1 조합도 비효율이 있다.
아래의 스크립트를 보자.

아래는 전형적인 MAX 일자를 구하는 SQL 이다.
계좌번호별로의 특정일자보다 작은일자중에 MAX 일자를 구하는 로직이다.
인덱스는 계좌번호 + 거래일자 로 되어있고 해당테이블은 거래일자기준으로 월별로 RANGE 파티션이 적용되어 있다.
물론 인덱스는 Global 이 아니다.
 

explain plan for
select max(거래일자)
from   일별계좌거래내역 b
where  b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729';

select plan_table_output
from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
Plan hash value: 654469635
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |    23 |    34   (0)|       |       |
|   1 |  SORT AGGREGATE               |                     |     1 |    23 |            |       |       |
|   2 |   PARTITION RANGE ITERATOR    |                     |   128 |  2944 |    34   (0)|    67 |     1 |
|   3 |    FIRST ROW                  |                     |   128 |  2944 |    34   (0)|       |       |
|   4 |     INDEX RANGE SCAN (MIN/MAX)| PK_일별계좌거래내역 |   128 |  2944 |    34   (0)|    67 |     1 |
----------------------------------------------------------------------------------------------------------

해석 : 뭔가 특별히 이상할것이 없는 PLAN  이다. 즉 예측 실행계획으로는 정확한 정보를 알수가 없다.
       단지 Rows 컬럼과 Pstart/Pstop 컬럼이 좀 이상하긴 하다.

그러면 위의 쿼리를 다른방식으로 좀더 자세히 살펴보자.
SQL TRACE 를 사용해도 되지만 여기서는 dbms_xplan.display_cursor 로 실행된 SQL 의 Plan을 구한다.  

1. 원본쿼리

select /*+ gather_plan_statistics  */
       max(거래일자)
from   일별계좌거래내역 b
where  b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729';

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical ALLSTATS LAST'));


 
Plan hash value: 654469635
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |                     |      1 |       |       |      1 |      91 |
|   2 |   PARTITION RANGE ITERATOR    |                     |      1 |    67 |     1 |     11 |      91 |
|   3 |    FIRST ROW                  |                     |     67 |       |       |     11 |      91 |
|*  4 |     INDEX RANGE SCAN (MIN/MAX)| PK_일별계좌거래내역 |     67 |    67 |     1 |     11 |      91 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("B"."계좌번호"='1234567890' AND "B"."거래일자"<'20080729')
  
해석 : Plan 테이블의 Starts 컬럼을 보면 min/max 구하는 작업을 모든파티션마다 67번 반복하였다.
       Pstart, Pstop 컬럼에서도 보듯이 67 개의 파티션을 모두 엑세스 하였다.
       그결과 buffers 컬럼에 91 블럭이 나왔다  
  
  
  
2. 1번쿼리에서 index_desc 힌트와 where 절에 rownum = 1 을 추가한다.(전통적인 튜닝방법) 

select /*+ gather_plan_statistics index_desc(b) */ --> 힌트추가
       max(거래일자)
from   일별계좌거래내역 b
where  b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729'
and    rownum = 1;                                 --> where 절 추가

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical ALLSTATS LAST'));

Plan hash value: 4244920012
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Starts | Pstart| Pstop | A-Rows | Buffers |
----------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |                     |      1 |       |       |      1 |       6 |
|*  2 |   COUNT STOPKEY                |                     |      1 |       |       |      1 |       6 |
|   3 |    PARTITION RANGE ITERATOR    |                     |      1 |    67 |     1 |      1 |       6 |
|*  4 |     INDEX RANGE SCAN DESCENDING| PK_일별계좌거래내역 |      2 |    67 |     1 |      1 |       6 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM=1)
   4 - access("B"."NMS_LN_ACNO"='1234567890' AND "B"."CAM_CLC_BAS_DT"<'20080729')
 
해석 : Pstart, Pstop 컬럼을 보면 67 개의 파티션을 모두 엑세스 한거 같지만 rownum = 1 이 추가되어 있으므로 Starts 컬럼을 보면 min/max 구하는 작업을 단 두번만 하였으므로  buffers 컬럼에 6 블럭이 나왔고
Starts 컬럼으로 예상해 보면 단 두개의 파티션만을 엑세스 하였다.
하나의 파티션만 엑세스 해야 하지만 Index Range Scan 시에 1 row 를 더 Scan 하는것과 같은 이치라고 할수 있다.
   
 
3. 특정파티션만 access 하게 쿼리를 b.거래일자 >= '20070701' 추가함. (최적의 방법)

select /*+ gather_plan_statistics index(b) */
       max(거래일자)
from   일별계좌거래내역 b
where  b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729'
and    b.거래일자 >= '20080701';           --> 파티션을 특정월로 고정함

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical ALLSTATS LAST'));


 
Plan hash value: 161631393
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |                     |      1 |       |       |      1 |       3 |
|   2 |   PARTITION RANGE SINGLE      |                     |      1 |    67 |    67 |      1 |       3 |
|   3 |    FIRST ROW                  |                     |      1 |       |       |      1 |       3 |
|*  4 |     INDEX RANGE SCAN (MIN/MAX)| PK_일별계좌거래내역 |      1 |    67 |    67 |      1 |       3 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("B"."NMS_LN_ACNO"='1234567890' AND "B"."CAM_CLC_BAS_DT"<'20080729' AND
              "B"."CAM_CLC_BAS_DT">='20080701')
 
해석 : Pstart, Pstop 컬럼에서 보듯이 단하나의 파티션(67번째 파티션) 에서 min/max 작업을 하였다.
       그결과 buffers 컬럼에 3 블럭이 나왔다.


4 결론
min/max - first row Plan 은 index_desc + rownum 과 성능상 같을수 있으나 파티션이 적용된 테이블이면서 Local 인덱스를 사용하는경우 전체파티션에 대하여 min/max - First Row 처리를 하므로 index_desc + rownum 처리를 하여야 비효율을 없앨수 있다. 하지만 이경우도 최적은 아니므로 특정파티션을 지정하여야 단 하나의 파티션만 엑세스 한다는 점을 기억하자.  

Posted by extremedb
,