예전에 Relational Database Index Design and the Optimizers 라는 글과
중급 개발자들이 보아야 할 오라클 책 이라는 글에서 DBMS 및 오라클 관련책을 추천한바 있다.
이번에는  Cost-Based Oracle Fundamentals 의 저자인 Jonathan Lewis 가 DBMS 관련 책 8권을 추천하였다.
우연히도 필자가 추천한 책 2권을 Jonathan Lewis도 도 추천 하였다.
아래의 주소를 참조하기 바란다.

http://jonathanlewis.wordpress.com/2009/01/14/books/

편집후기: Comments 부분에도 책 여러권이 등장 하므로 꼭 읽어보기 바란다.
Posted by extremedb
,
한 개발자가 Full Outer Join 에 대한 실행계획에 대하여 질문을 해왔다.
그개발자는 아래와 같은 이야기를 설명 하였다.

하나의 테이블 기준으로 outer Join 을 한다
Union all
반대편 테이블을 기준으로 다시 outer join 을 한다.

여러분들은 위의 이야기를 어떻게 생각하는가?
개발자의 이야기는 반(윗부분)은 맞고 반(아랫부분)은 틀리다.

환경은 Oracle 10g 버젼 10.2.0.4 이다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics */ a.empno, a.ename, b.dname
  from  emp a full outer join dept b
    on  (a.deptno= b.deptno) ;

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   1 |  VIEW                          |           |      1 |     14 |      26 |          |
|   2 |   UNION-ALL                    |           |      1 |     14 |      26 |          |
|   3 |    NESTED LOOPS OUTER          |           |      1 |     14 |      23 |          |
|   4 |     TABLE ACCESS FULL          | EMP       |      1 |     14 |       8 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     14 |     13 |      15 |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT   |     14 |     13 |       2 |          |
|   7 |    MERGE JOIN ANTI             |           |      1 |      0 |       3 |          |
|   8 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      4 |       2 |          |
|   9 |      INDEX FULL SCAN           | PK_DEPT   |      1 |      4 |       1 |          |
|* 10 |     SORT UNIQUE                |           |      4 |      4 |       1 | 2048  (0)|
|* 11 |      INDEX FULL SCAN           | IX_EMP_N2 |      1 |     13 |       1 |          |
-------------------------------------------------------------------------------------------


윗부분은 분명히 Outer 조인으로 풀렸지만 아래부분은 ANTI 조인으로 풀렸으므로 이것은 서브쿼리를 사용한것이다.
즉 옵티마이져는 아래처럼 Query Transformation 을 하게 되는 것이다.
 
select /*+ gather_plan_statistics */ *
from (
       select a.empno, a.ename, b.dname
       from  emp a, dept b
       where a.deptno = b.deptno(+)
       union all
       select null, null, a.dname
       from dept a
       where not exists (select 1
                                    from emp b
                                where  b.deptno = a.deptno
                                )
       ) ;

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   1 |  VIEW                          |           |      1 |     14 |      26 |          |
|   2 |   UNION-ALL                    |           |      1 |     14 |      26 |          |
|   3 |    NESTED LOOPS OUTER          |           |      1 |     14 |      23 |          |
|   4 |     TABLE ACCESS FULL          | EMP       |      1 |     14 |       8 |          |
|   5 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     14 |     13 |      15 |          |
|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT   |     14 |     13 |       2 |          |
|   7 |    MERGE JOIN ANTI             |           |      1 |      0 |       3 |          |
|   8 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      4 |       2 |          |
|   9 |      INDEX FULL SCAN           | PK_DEPT   |      1 |      4 |       1 |          |
|* 10 |     SORT UNIQUE                |           |      4 |      4 |       1 | 2048  (0)|
|* 11 |      INDEX FULL SCAN           | IX_EMP_N2 |      1 |     13 |       1 |          |
-------------------------------------------------------------------------------------------

실행계획이 Full Outer Join 과 똑같음을 알수 있다.
하지만 이것도 어디까지나 파라미터 _optimizer_native_full_outer_join 가 off 일때 까지의 이야기이다.

아래의 스크립트를 보자.

/*+ opt_param('_optimizer_native_full_outer_join', 'force') */

위의 힌트를 사용하거나 아니면 아래처럼 ALTER SESSION 을 하면 된다.

alter session set "_optimizer_native_full_outer_join" = 'force';

select /*+ gather_plan_statistics */
      a.empno, a.ename, b.dname
from  emp a full outer join dept b
  on  (a.deptno= b.deptno) ;

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------
|   1 |  VIEW                 | VW_FOJ_0 |      1 |     14 |00:00:00.01 |      15 |          |
|*  2 |   HASH JOIN FULL OUTER|          |      1 |     14 |00:00:00.01 |      15 |  573K (0)|
|   3 |    TABLE ACCESS FULL  | DEPT     |      1 |      4 |00:00:00.01 |       7 |          |
|   4 |    TABLE ACCESS FULL  | EMP      |      1 |     14 |00:00:00.01 |       8 |          |
----------------------------------------------------------------------------------------------


_optimizer_native_full_outer_join 파라미터를 force 로 하자 ANTI 조인마져 사라졌다.
이기능은 Native hash full outer join 이라고 불리고 11g 부터는 기본이 force 로 되어있다.


결론: Full Outer Join 은 10g 버젼 까지는 Outer Join + Union all + Anti Jojn 이지만
      11g 부터는 성능이 향상된 Native hash full outer join 를 사용할수 있음을 기억하자.
Posted by extremedb
,

오늘은 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
,