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 이다.)임을 알 수 있다.
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
컬럼과 변수의 데이터 타입이 다른 경우 성능 문제가 발생할수도 발생하지 않을 수도 있다.
하지만 발생하면 대단히 커다란 성능 문제 야기 할수도...
또한 조인 조건이 안맞아 발생할수도...
==> 설계시 주의를 기울이자.
(표준화를 통한 도메인 관리 - > 산출물 : 도메인 정의서...)
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
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;
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
표준용어 사전
데이터 요소에 규칙을 적용해 표준용어를 정의하면 간결하고, 고유하고, 일관성 있게 엔티티명과 속성명을 관리할 수 있다.
또한 영문명과 영문 약어명을 이용하여 논리적 속성명이 물리적 컬럼명으로 자동 대응되도록 설정할 수 있다.
즉, 논리설계만으로도 물리적인 테이블과 컬럼 설계를 일관성 있게 자동 생성할 수 있다.
도메인정의서
도메인 정의를 이용하면 업무적으로 동일한 속성들이 같은 데이터 타입과 길이를 가지도록 할 수 있다
(다른 속성으로 정의되는 것도 방지한다)
도메인이란 엔티티타입 내의 속성에 대한 데이터타입과 크기, 제약 사항을 지정하는 것
속성이 일관된 규칙에 따라 데이터 타입과 크기가 부여 됨으로 모델의 관리가 용이함
정의하는 작업도 중요하지만 도메인이 변경, 추가시에 따른 일관성 유지도 필요함
--데이터 설계의 핵심은 표준화와 도메인 정의(http://www.nextstep.co.kr/17)펌펌펌