by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

조회 컬럼과 변수의 데이터 타입을 맞추자.




조회 컬럼과 변수의 데이터 타입을 맞추자

  • Where 절에 사용된 컬럼과 조회 변수 값의 데이터 타입이 다르기 때문에 발생하는 경우가 많다.
    (컬럼 :VARCHAR2, 조건 :NUMBER)
 
1. 잘못 생성된 통계정보에 의한 Optimizer 의 판단 오류 (해당 컬럼의 데이터 분포도까지도 고르지 않아서..) 

2. DB 서버의 파라미 터 설정 중 Bind Peeking 에 의해서 발생하는 경우..

3. Where 절에 쓰인 컬럼에 대해 인텍스가 없는 경우 ..
 
 
SELECT  registeryn 
FROM  tb_crm_register 
WHERE  registerno  =  : b1

SELECT  STATEMENT  - ALL  ROWS- Cost  Estimate:3352 
      TABLE  ACCESS  FULL  :CUSR.TB_CRM_REGISTER 
 

통계정보의 변형 확인.
1. 실제 테이블의 데이터 건수와 테이블 통계정보의 NUM_ROWS 건수 비교.
2. 적정인덱스 존재 유무 확인
3. REGISTERNO 컬럼의 NUM_DISTINCT 값과 TB_CRM_REGISTER 테이블의 전체 건수가
같은 것으로 보아 REGISTERNO = : Bl 조건으로 추줄될 데이터는 평균 1 건

TABLE 전체 건수 (NUM_ROWS 5)/ COLUMN NDV = 612492 / 612492 = 1

마지막으로 우리가 확인해봐야할것은 :Bl 의 데이터 타입과컬럼의 데이터 타입이
달라내부적인 컬럼 변형이 발생하는지 여부를 점검

10g 이상에서는 V$SQL_BIND_CAPTURE 와 DBA_HIST_SQLBIND 뷰를 이용해서 sQL
이 수행될 때 사용하였던 Bind 값에 대한 정보를 확인

SELECT name, Last_captured, datatype, value_string
FROM V$SQL_BIND_CAPTURE
WHERE hash_va1ue = :v_hash_va1ue ;

V$SQL_BIND_CAPTURE 의 컬럼중 DATATYPE 의 값이 2 인 경우는 변수의 데이터 타입
이 NUMBER 임을 의미하므로 :B1 의 데이터 타입은 NUMBER 타입 (DATATYPE 이 1 인
경우는 CHAR 이다.)임을 알 수 있다.

  • FULL Table scan 으로 해석된 이유
    Bind 변수와컬럼의 데이터 타입이 다를경우, ORACLE은 한쪽을형 변환 후 비교하게 된다.
    이때 형 변환 우선순위에 의해 변수 쪽의 데이터 타입을 형 변환 하지 않고,
    컬럼쪽의 데이터 타입을 변형하여 인텍스 컬럼에 대한 변형이 발생하게 된다.
    이 때문에 인덱스를 사용하지 못하고 FULL Table scan 을 하게 된 것이다
 


Script. 테스트 데이터 생성용
DROP TABLE T1 PURGE;

< T1 >

■ 생성 요건
- T1 테이블의 전체 건수는 1,000,000 ROWS
- C1은 NUMBER형이며 UNIQUE한 값을 갖음. 
- C2데이터 타입은 문자형이며, YYYYMMDD포멧을 갖는 날자데이터임. 

■ 테이블 생성
CREATE TABLE T1
AS
SELECT 1000000-( LEVEL -1) C1, TO_CHAR(SYSDATE-( LEVEL -1),'YYYYMMDD') C2
FROM DUAL
CONNECT BY LEVEL <= 1000000 ;

■ 각 칼럼에 인덱스 생성 및 통계정보 수집
CREATE INDEX IDX_T1_01 ON T1(C1);
CREATE INDEX IDX_T1_02 ON T1(C2);

BEGIN
      dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',
                                    TABNAME=>'T1',
                                    ESTIMATE_PERCENT=>99,
                                    METHOD_OPT=>'FOR ALL INDEXED COLUMNS',
                                    GRANULARITY=>'ALL',
                                    CASCADE=>TRUE,
                                    NO_INVALIDATE=>FALSE) ;
END;
/

-컬럼 데이터 타입_ VARCHAR2(8)
-변수 데이터 타입 - NUMBER
SELECT *
FROM T1
WHERE c2 = 20110124 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    26 |   645   (9)| 00:00:08 |
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    26 |   645   (9)| 00:00:08 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("C2")=20110124)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2632  consistent gets
          0  physical reads
          0  redo size
        481  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


-컬럼 데이터 타입 - number
-변수 데이터 타입 - varchar2(8)
SELECT *
FROM T1
WHERE c1 = '111111';


Execution Plan
----------------------------------------------------------
Plan hash value: 3018735338

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    13 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     1 |    13 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1_01 |     1 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=111111)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        485  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

컬럼과 변수의 데이터 타입이 다른 경우 성능 문제가 발생할수도 발생하지 않을 수도 있다.
하지만 발생하면 대단히 커다란 성능 문제 야기 할수도...
또한 조인 조건이 안맞아 발생할수도...
==> 설계시 주의를 기울이자.
(표준화를 통한 도메인 관리 - > 산출물 : 도메인 정의서...)

추가테스트

1.잘못된 통계정보로 플랜에 영향을 미쳐 인덱스 스캔을 안하는 예
drop table t1 purge ; 
create table t1 (c1  number,  c2  char(100),  c3  char(100)) ; 
create index T1_IND1  on  t1(c1) ; 

insert into t1 
select level, 'dummy' , 'dummy' from  dual 
connect by level <= 10000 ;

commit;

select * from t1 where c1 between 1 and 10000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1828387482

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         | 11304 |  2395K|    20   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      | 11304 |  2395K|    20   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IND1 | 11304 |       |    20   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">=1 AND "C1"<=10000)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1627  consistent gets
          0  physical reads
          0  redo size
    2196952  bytes sent via SQL*Net to client
       7741  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed




exec dbms_stats.gather_table_stats(user, 'T1');


select * from t1 where c1 between 1 and 10000;


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |  2011K|    82   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   | 10000 |  2011K|    82   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1">=1 AND "C1"<=10000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        946  consistent gets
          0  physical reads
          0  redo size
     175350  bytes sent via SQL*Net to client
       7741  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed


delete from t1;

select * from t1 where c1 between 1 and 10000;

SQL> select * from t1 where c1 between 1 and 10000;

선택된 레코드가 없습니다.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |  2011K|    82   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   | 10000 |  2011K|    82   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1">=1 AND "C1"<=10000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


exec dbms_stats.gather_table_stats(user, 'T1');

SQL> select * from t1 where c1 between 1 and 10000;

선택된 레코드가 없습니다.


Execution Plan
----------------------------------------------------------
Plan hash value: 1828387482

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |   217 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |   217 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IND1 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">=1 AND "C1"<=10000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

2.바인드피킹으로 인한 인덱스를 못타는 예에 대한 테스트
alter system flush buffer_cache;

ALTER SYSTEM FLUSH SHARED_POOL;


drop table t2 purge ; 
create table t2 (c1  number,  c2  char(100),  c3  char(100)) ; 
create index T2_IND1 on t2(c1) ; 



insert into t2 
select 10000000, 'dummy' , 'dummy' from  dual 
connect by level <= 99990;

insert into t2 
select level, 'dummy' , 'dummy' from  dual 
connect by level <= 10 ;

commit;

exec dbms_stats.gather_table_stats(user, 'T2');

Alter Session Set "_optim_peek_user_binds" = True;


var z1 number
exec :z1 := 1

Select Count(*) from T2 where c1 = :z1;


var z1 number
exec :z1 := 10000000

Select Count(*) from T2 where c1 = :z1;


반복

alter system flush buffer_cache;

ALTER SYSTEM FLUSH SHARED_POOL;


drop table t2 purge ; 
create table t2 (c1  number,  c2  char(100),  c3  char(100)) ; 
create index T2_IND1 on t2(c1) ; 



insert into t2 
select 10000000, 'dummy' , 'dummy' from  dual 
connect by level <= 99990;

insert into t2 
select level, 'dummy' , 'dummy' from  dual 
connect by level <= 10 ;

commit;

exec dbms_stats.gather_table_stats(user, 'T2');

Alter Session Set "_optim_peek_user_binds" = True;


var z1 number
exec :z1 := 10000000

Select Count(*) from T2 where c1 = :z1;


var z1 number
exec :z1 := 1

Select Count(*) from T2 where c1 = :z1;
3.상수가 아닌 바인드변수가 조건절에 있을때 확인방법

SQL> var b1 number
SQL> exec :b1 := 20110124

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

SQL> SELECT * FROM T1  WHERE c2 = :b1;

        C1 C2
---------- --------
    998975 20110124


Execution Plan
----------------------------------------------------------
Plan hash value: 715881508

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     2 |    26 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |     2 |    26 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T1_02 |     2 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"=:B1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       2632  consistent gets
          0  physical reads
          0  redo size
        481  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



SQL> set pagesize 200
SQL> set linesize 200
SQL> var b1 number
SQL> exec :b1 := 20110124

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

SQL>
SQL> alter session set events '10046 trace name context forever, level 12';

세션이 변경되었습니다.

SQL> SELECT * FROM T1 WHERE c2 = :b1;

        C1 C2
---------- --------
    998975 20110124

SQL> alter session set events '10046 trace name context off';

세션이 변경되었습니다.

SQL> exit

D:\>tkprof orcl_ora_4360.trc 1.txt sys=no explain=scott/tiger

SELECT * 
FROM
 T1 WHERE c2 = :b1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.01       0.05          0          0          0           0
Fetch        2      0.21       0.22          0       2632          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.23       0.28          0       2632          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54  (SCOTT)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL T1 (cr=2632 pr=0 pw=0 time=479 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'T1' (TABLE)
      0    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'IDX_T1_02' (INDEX)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.00          0.00
  



select SQL_ID,OPERATION,OPTIONS,FILTER_PREDICATES 
from V$SQL_PLAN_STATISTICS_ALL where SQL_ID = 'c4b48fw24xs87';

SQL_ID		OPERATION	OPTIONS	FILTER_PREDICATES
c4b48fw24xs87	TABLE ACCESS	FULL	TO_NUMBER("C2")=:B1


SELECT
     RPAD('(' || p.ID || ' ' || NVL(p.parent_id,'0') || ')',8) || '|' ||
     RPAD(LPAD (' ', 2*p.DEPTH) || p.operation || ' ' || p.options,50,'.') ||
     NVL2(p.object_owner||p.object_name, '(' || p.object_owner|| '.' || p.object_name || ') ', '') ||
     'Cost:' || p.COST || ' ' || NVL2(p.bytes||p.CARDINALITY,'(' || p.bytes || ' bytes, ' || p.CARDINALITY || ' rows)','') || ' ' ||
     NVL2(p.partition_id || p.partition_start || p.partition_stop,'PId:' || p.partition_id || ' PStart:' ||
     p.partition_start || ' PStop:' || p.partition_stop,'') ||
     'io cost=' || p.io_cost || ',cpu_cost=' || p.cpu_cost AS PLAN
FROM v$sql_plan p
WHERE p.sql_id='79hfxz3mnnrmb'
AND plan_hash_value='3617692013'
ORDER BY p.id, p.parent_id;

(0 0)   |SELECT STATEMENT .................................Cost:645  io cost=,cpu_cost=
(1 0)   |  TABLE ACCESS FULL...............................(SCOTT.T1) Cost:645 (26 bytes, 2 rows) io cost=587,cpu_cost=339079612

4.표준용어 사전(네이밍즈 파일), 도메인정의서

표준용어 사전
데이터 요소에 규칙을 적용해 표준용어를 정의하면 간결하고, 고유하고, 일관성 있게 엔티티명과 속성명을 관리할 수 있다. 
또한 영문명과 영문 약어명을 이용하여 논리적 속성명이 물리적 컬럼명으로 자동 대응되도록 설정할 수 있다. 
즉, 논리설계만으로도 물리적인 테이블과 컬럼 설계를 일관성 있게 자동 생성할 수 있다. 

도메인정의서
도메인 정의를 이용하면 업무적으로 동일한 속성들이 같은 데이터 타입과 길이를 가지도록 할 수 있다
(다른 속성으로 정의되는 것도 방지한다)
도메인이란 엔티티타입 내의 속성에 대한 데이터타입과 크기, 제약 사항을 지정하는 것 
속성이 일관된 규칙에 따라 데이터 타입과 크기가 부여 됨으로 모델의 관리가 용이함 
정의하는 작업도 중요하지만 도메인이 변경, 추가시에 따른 일관성 유지도 필요함 

--데이터 설계의 핵심은 표준화와 도메인 정의(http://www.nextstep.co.kr/17)펌펌펌


문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.