Microsoft PowerPoint - Oracle Data Access Pattern.ppt

Size: px

Start display at page:

Download "Microsoft PowerPoint - Oracle Data Access Pattern.ppt"

선자 부
5 years ago
Views:

1 Special Key Note Oracle Data Access Pattern ( 주 ) 오픈메이드컨설팅 오동규수석컨설턴트 1

2 What is Data Access Pattern? > 데이터를 I/O 하는방식 Index Scan Full Table Scan Rowid 2

3 Why is The Pattern Important? >SQL 의성능을좌지우지함. >SQL 성능이슈의 1/3 이 Data Access Pattern 이잘못되어발생 > 모든 DBMS 의 Data Access Pattern이유사함. Index Scan Full Table Scan Rowid 3 가지용도를알아야한다. 3

4 How do You learn Data Access Pattern? Only Book? Probably Not! Then How? 4

5 Oracle Data Access Pattern- 목차 Here it is EveryThing About Oracle Data Access Pattern Rowid Index Unique Scan Index Range Scan Index Inlist Iterator Index Skip Scan Index Full Scan Index Fast Full Scan Bit Map Index Combination Full Table Scan Index 사용 5

6 Oracle Data Access Pattern- 상세목차 Rowid Index Unique Scan Index Range Scan Index Range Scan Descending Index Range Scan (min/max) Index Inlist Iterator Index Inlist Iterator Descending Index Skip Scan Index Skip Scan Descending Index Full Scan Index Full Scan Descending Index Full Scan (min/max) Index Fast Full Scan BIT MAP OR BIT MAP AND BIT MAP MINUS BIT MAP MERGE Full Table Scan Index Fast Full Scan 은예외적으로 Full Table Scan 처럼 Multi I/O 를사용함 Bit Map Index Combination 의 4 가지 Pattern 6

7 Rowid > 발생조건 > 특징 > 적용 Rowid가조건으로공급된경우 인덱스를사용하여 Table 을 access 한경우 Rowid를이용해서해당테이블의특정 의특정 Row를찾아간다. 가장빠른 Access 방식이다. max /min 일자를찾아서그일자에해당하는값을 select 할때 주로 self join 시사용 > Hint /*+ rowid( 테이블명또는테이블별칭 ) */ 7

8 Rowid- 예제 ( 인라인뷰사용 ) 요구사항 : 부서별로가장최근에입사한사원정보를한명씩출력하시오. select b.empno, b.ename, b.deptno, b.job, b.hiredate from (select substr(max(to_char(hiredate,'yyyymmdd') ROWID), 9) as rid group by deptno) a, emp b where b.rowid = a.rid Id Operation Name Starts A-Rows A-Time Buffers Reads Used-Mem NESTED LOOPS :00: VIEW :00: HASH GROUP BY :00: K (0) 4 TABLE ACCESS FULL EMP :00: TABLE ACCESS BY USER ROWID EMP :00:

9 Rowid- 예제 ( 서브쿼리사용 ) 요구사항 : 부서별로가장최근에입사한사원정보를한명씩출력하시오. select b.empno, b.ename, b.deptno, b.job, b.hiredate b where b.rowid in ( select substr(max(to_char(hiredate,'yyyymmdd') ROWID), 9) group by deptno) Id Operation Name Starts A-Rows A-Time Buffers Reads Used-Mem NESTED LOOPS :00: VIEW VW_NSO_ :00: HASH UNIQUE :00: K (0) 4 HASH GROUP BY :00: K (0) 5 TABLE ACCESS FULL EMP :00: TABLE ACCESS BY USER ROWID EMP :00:

10 Index Unique Scan > 발생조건 > 특징 > 적용 Unique 인덱스및 PK 인덱스의모든컬럼에대하여 Where 절에 = 조건으로상수및변수가들어올때발생됨. 해당조건을만족하는하나의 row 만 Scan 한다. 빠른성능 OLTP 시스템 건단위로처리해야하는경우 > Hint /*+ index( 테이블명또는테이블별칭인덱스명 ) */ select /*+ index(emp pk_emp) */ empno, ename where empno = 7782 ; 10

11 Index Unique Scan- 예제 select empno, ename where empno = 7782 ; EMPNO 인덱스 인덱스 EMP 테이블 테이블 7782 CLARK MANAGER Id Operation Name Starts E-Rows A-Rows A-Time Buffers TABLE ACCESS BY INDEX ROWID EMP :00: * 2 INDEX UNIQUE SCAN PK_EMP :00: Predicate Information (identified by operation id): access("empno"=7782) 11

12 Index Range Scan > 발생조건 Non-Unique Index 를 Access 하는경우 Unique Index를구성하고있는컬럼중일부컬럼에만값이공급된경우 Unique Index에 Range 조건 (like, between, >, <, >=, <=) 으로값이공급되는경우 > 특징 해당조건을만족하는범위 + 아닌값하나 (1PlusScan) 를읽게된다. Range 조건이들어온경우 Index구성순서상이후에있는컬럼에공급된조건들은작업범위를줄이는데작용하지못한다. 예외상황 :9i 이후부터 index skip scan 수행속도가 Range 에의해서좌우된다. > 적용 10만건이하의건수를 access 할때 10만건이상이라도부분범위처리가가능할때 10만건이상이라도인덱스만 scan 하고 table access 가없을때 주로 OLTP > Hint /*+ index( 테이블명또는테이블별칭인덱스명 ) */ /*+ index_desc( 테이블명또는테이블별칭인덱스명 ) */ 12

13 Index Range Scan- 예제 EMPNO 인덱스 인덱스 EMP 테이블 테이블 7839 KING NULL. select empno, ename, mgr where empno BETWEEN 7782 AND 7839; 7782 CLARK SCOTT Plus Scan Id Operation Name Starts E-Rows A-Rows A-Time Buffers TABLE ACCESS BY INDEX ROWID EMP :00: * 2 INDEX RANGE SCAN PK_EMP :00: Predicate Information (identified by operation id): access("empno">=7782 AND "EMPNO"<=7839) 13

14 Index Range Scan Descending - 예제 select empno, ename, mgr where empno BETWEEN 7782 AND 7839 EMPNO 인덱스 7698 인덱스 EMP 테이블 테이블 7839 KING NULL CLARK order by empno desc SCOTT Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads TABLE ACCESS BY INDEX ROWID EMP :00: * 2 INDEX RANGE SCAN DESCENDING PK_EMP :00: Predicate Information (identified by operation id): access("empno"<=7839 AND "EMPNO">=7782) 14

15 Index Range Scan (min/max)- 예제 EMPNO 인덱스 인덱스 select max(empno) where empno between 7782 and Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads SORT AGGREGATE :00: FIRST ROW :00: * 3 INDEX RANGE SCAN (MIN/MAX) PK_EMP :00: Predicate Information (identified by operation id): access("empno">7839) 15

16 Index Inlist Iterator > Iteration > 발생조건 > 특징 > 적용 > Hint 의미 : 같은일을반복함 OR 조건이나 IN 조건에상수 ( 변수 ) 가들어오면발생함 Concatenation 과비슷하나 Union 으로풀리지않고반복수행된다. 값이상수나변수로공급될때만발생한다. 수행속도가반복횟수에의해서좌우된다. Iteration 에의해 Index range scan 및 index unique scan 이발생됨 Concatenation 과동일하나 Inlist Iterator 가유리함. Index Range Scan 이더유리한경우가있으므로무조건적용하지말것. OLTP 에적용하고대용량배치 SQL 에서는피한다. /*+ NUM_INDEX_KEYS( 테이블명인덱스명컬럼개수 ) */ Oracle 부터사용가능 인덱스 Layout: EMP_JOB_MGR_SAL_IDX (JOB_ID, MANAGER_ID, SALARY) SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) */ a.* FROM hr.employees a WHERE job_id = :v_job AND manager_id IN (:v_manager1, :v_manager2) 인덱스의 2 번째컬럼까지 Inlist Iterator 로 scan 하라는의미 16

17 Index Inlist Iterator- 예제 EMPNO 인덱스 인덱스 EMP 테이블 테이블 7839 KING NULL. select empno, ename, mgr where empno in (7782, 7839); 혹은 1 select empno, ename, mgr 2 where (empno = 7782 or empno = 7839); CLARK Id Operation Name Starts E-Rows A-Rows A-Time Buffers INLIST ITERATOR :00: TABLE ACCESS BY INDEX ROWID EMP :00: * 3 INDEX UNIQUE SCAN PK_EMP :00: Predicate Information (identified by operation id): access(("empno"=7499 OR "EMPNO"=7839)) 17

18 Index Inlist Iterator Descending- 예제 EMPNO 인덱스 인덱스 EMP 테이블 테이블 7839 KING NULL. select empno, ename, mgr where empno in (7839, 7782) order by empno desc; 2 혹은 select empno, ename, mgr 1 where (empno = 7839 or empno = 7782) order by empno desc; CLARK Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads INLIST ITERATOR :00: TABLE ACCESS BY INDEX ROWID EMP :00: * 3 INDEX RANGE SCAN DESCENDING PK_EMP :00: Predicate Information (identified by operation id): access(("empno"=7499 OR "EMPNO"=7839)) 18

19 Index Skip Scan > 발생조건 > 특징 > 적용 > Hint 결합인덱스시처음이나중간의조건이빠졌을경우 9i 이전까지는결합인덱스에서첫번째컬럼이사용되지않으면인덱스의두번째컬럼부터는인덱스스캔이불가능하였다. 9i 부터 Index Skip Scan 으로빠진조건을제외한나머지조건을 scan 하는것이 가능해짐. 기본적으로 index range scan 과특징이유사함 인덱스가 col1 + col2 + col3 로되어있을때 col1 컬럼조건이 where 조건에서빠진경우에 col2 + col3 인덱스를만든것과비슷한효과를 낼수있음. 위와비슷하게 col2 컬럼조건이 where 조건에서빠진경우에도 col1 + col3 인덱스를만든것과비슷한효과를낼수있음. 주로좁은범위를 scan 할때 Skip 되는컬럼의값의종류가많지않을때 /*+ index_ss( 테이블명또는테이블별칭인덱스명 ) */ 19

20 Index Skip Scan - 예제 Index Skip Scan 인덱스 Lay out : mgr + job + deptno select /*+ index_ss(emp IX_EMP_N1) */ empno, ename, mgr where job = 'CLERK' and deptno between 10 and Id Operation Name Starts E-Rows A-Rows A-Time Buffers TABLE ACCESS BY INDEX ROWID EMP :00: * 2 INDEX SKIP SCAN IX_EMP_N :00: Predicate Information (identified by operation id): access("job"='clerk' AND "DEPTNO">=10 AND "DEPTNO"<=30) filter(("job"='clerk' AND "DEPTNO">=10 AND "DEPTNO"<=30)) mgr + job + deptno 결합 CLERK CLERK CLERK MANAGER 결합인덱스 NULL 20 CLERK 20 Filter Skip CLERK Plus Scan select empno, ename, mgr where mgr in (7902, 7698, 7839, 7566, 7782, 7788) and job = 'CLERK' and deptno between 10 and 30 Index skip scan 의효과는왼쪽처럼 mgr 의모든조건을추가한것과같음. 20

21 Index Skip Scan Descending- 예제 인덱스 Lay out : mgr + job + deptno mgr + job + deptno 결합 결합인덱스 select /*+ index_ss_desc(emp IX_EMP_N1) */ empno, ename, mgr where job = 'CLERK' and deptno between 10 and 30 order by mgr desc, job desc, deptno desc CLERK 30 CLERK 10 CLERK NULL MANAGER 20 CLERK 20 CLERK 30 1 Plus Scan Filter Skip Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads TABLE ACCESS BY INDEX ROWID EMP :00: * 2 INDEX SKIP SCAN DESCENDING IX_EMP_N :00: Predicate Information (identified by operation id): access("job"='clerk' AND "DEPTNO">=10 AND "DEPTNO"<=30) filter(("job"='clerk' AND "DEPTNO">=10 AND "DEPTNO"<=30)) 21

22 Index Full Scan > 특징 > 적용 해당인덱스의모든 을한번에한 씩순차적으로읽어내려간다.(Single I/O) 데이터가가적고전체건에대하여인덱스컬럼으로 sort 해야하는경우 min/max 를구할경우 Scan 해야할데이터가가많은경우는부분범위처리가가능하고인덱스컬럼으로 sort 를해야하는경우만적용할것. > Hint /*+ index( 테이블명또는테이블별칭인덱스명 ) */ 직접적으로 full scan 을유도하는힌트는없음. 22

23 Index Full Scan Scan - 예제 select empno order by empno 1 EMPNO 인덱스 2 인덱스 Id Operation Name Starts E-Rows A-Rows A-Time Buffers INDEX FULL SCAN PK_EMP :00:

24 Index Full Scan Scan Descending - 예제 인덱스를거꾸로 Scan 함 select empno order by empno desc 3 EMPNO 인덱스 2 인덱스 Id Operation Name Starts E-Rows A-Rows A-Time Buffers INDEX FULL SCAN DESCENDING PK_EMP :00:

25 Index Full Scan (min/max)- 예제 select max(empno) EMPNO 인덱스 인덱스 Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads SORT AGGREGATE :00: INDEX FULL SCAN (MIN/MAX) PK_EMP :00: 아래의 SQL 을수행한효과와같음 select /*+ index_desc(emp pk_emp) */ empno WHERE empno > 0 and rownum =

26 Index Fast Full Scan > 발생조건 > 특징 > 적용 > Hint Where 절이나 Select 절에사용된컬럼이모두하나의인덱스에구성된컬럼인경우 결합 Index 의경우최소한한 Column 이 NOT Null 로지정되어있거나 where 절에명시적으로 not null 조건을부여해야한다. 한번에 DB_FILE_MULTIBLOCK_READ_COUNT 에서정한크기씩끝까지읽어내려가며결과값의 Sort 가보장되지않는다.(Multi I/O) Full Table Scan 보다읽어야할 의수가적어항상유리하다. 넓은범위검색 주로배치쿼리나 OLAP 에서사용됨 /*+ index_ffs( 테이블명또는테이블별칭인덱스명 ) */ 26

27 Index Fast Full Scan Scan - 예제 select /*+ index_ffs(emp pk_emp) */ empno 1 EMPNO 인덱스 인덱스 2 Multi- I/O 3 Multi- I/O Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads INDEX FAST FULL SCAN PK_EMP :00:

28 Bit Map Index Combination > 발생조건 > 특징 > 적용 > Hint Bit Map 인덱스가한테이블에 2 개이상일때 where 조건에두인덱스에해당하는컬럼의조건을 where 절에서모두사용할때발생함. 9.2 미만버젼에서는 Index Merge Plan 이발생함. 반드시 Bit Map 인덱스가아니라도 Index Combination 이발생할수있음. 이경우에는 Bit Map Conversion 이추가로발생함. 첫번째인덱스의와두번째인덱스의를이용하여두집합간에 AND, OR, MINUS, MERGE 연산을하여데이터를엑세스한다. 주로 DW 나대용량배치인경우적용 OLTP 인경우는두개의인덱스조건이모두똑똑한경우적용가능. 하지만하나의조건만으로 filter 되어나오는결과건수와두개의조건으로 filter 되는나오는결과건수가비슷하다면비효율이발생함으로무조건적용해서는 안됨. /*+ INDEX_COMBINE( 테이블명또는테이블별칭인덱스 1, 인덱스 2) */ 28

29 Bit Map Index Combination - 예제 인덱스 LayOut : IX_EMP_N2 : mgr IX_EMP_N3 : deptno select /*+ index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */ empno, ename where mgr = 7839 or deptno = 10 Bit Map Or Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads TABLE ACCESS BY INDEX ROWID EMP :00: BITMAP CONVERSION TO ROWIDS :00: BITMAP OR :00: BITMAP CONVERSION FROM ROWIDS :00: * 5 INDEX RANGE SCAN IX_EMP_N :00: BITMAP CONVERSION FROM ROWIDS :00: * 7 INDEX RANGE SCAN IX_EMP_N :00: Predicate Information (identified by operation id): access("deptno"=10) 7 - access("mgr"=7839) 29

30 Bit Map Index Combination - 예제 Bit Map And 인덱스 LayOut : IX_EMP_N2 : mgr IX_EMP_N3 : deptno select /*+ index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */ empno, ename where mgr = 7839 and deptno = Id Operation Name Starts E-Rows A-Rows A-Time Buffers Reads TABLE ACCESS BY INDEX ROWID EMP :00: BITMAP CONVERSION TO ROWIDS :00: BITMAP AND :00: BITMAP CONVERSION FROM ROWIDS :00: * 5 INDEX RANGE SCAN IX_EMP_N :00: BITMAP CONVERSION FROM ROWIDS :00: * 7 INDEX RANGE SCAN IX_EMP_N :00: Predicate Information (identified by operation id): access("deptno"=10) 7 - access("mgr"=7839) 30

31 Bit Map Index Combination - 예제 인덱스 drop 후 bit map index 생성 create bitmap index IX_EMP_N2 on emp (mgr); create bitmap index IX_EMP_N3 on emp (deptno); select /*+ index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */ empno, mgr, deptno where mgr > 7600 and deptno > Id Operation Name Starts A-Rows A-Time Buffers Used-Mem TABLE ACCESS BY INDEX ROWID EMP :00: BITMAP CONVERSION TO ROWIDS :00: BITMAP AND :00: BITMAP MERGE :00: (0) * 5 BITMAP INDEX RANGE SCAN IX_EMP_N :00: BITMAP MERGE :00: (0) * 7 BITMAP INDEX RANGE SCAN IX_EMP_N :00: Predicate Information (identified by operation id): access("deptno">20) filter("deptno">20) 7 - access("mgr">7600) filter("mgr">7600) Bit Map Merge 31

32 Bit Map Index Combination - 예제 인덱스 drop 후 bit map index 생성 create bitmap index IX_EMP_N2 on emp (mgr); create bitmap index IX_EMP_N3 on emp (deptno); select /*+ index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */ empno, mgr, deptno where NOT( mgr = 7698 ) and deptno = 20 Bit Map Minus Id Operation Name Starts A-Rows A-Time Buffers Reads TABLE ACCESS BY INDEX ROWID EMP :00: BITMAP CONVERSION TO ROWIDS :00: BITMAP MINUS :00: BITMAP MINUS :00: * 5 BITMAP INDEX SINGLE VALUE IX_EMP_N :00: * 6 BITMAP INDEX SINGLE VALUE IX_EMP_N :00: * 7 BITMAP INDEX SINGLE VALUE IX_EMP_N :00: Predicate Information (identified by operation id): access("deptno"=20) 6 - access("mgr"=7698) 7 - access("mgr" IS NULL) 32

33 Full Table Scan > 발생조건 > 특징 > 적용 > Hint 아무런조건없이 Table 을읽게한경우 인덱스가걸려있지않은컬럼에대해서조건을주고 Table 을읽게한경우 인덱스가걸려있는컬럼에조건을부여했을지라도 Optimizer 가 Full Table Scan 이유리하다고판단한경우 테이블의첫 Row 가들어있는 부터 HWM(High Water Mark) 까지읽는다 DB_FILE_MULTIBLOCK_READ_COUNT 가 16 이면한번 i/o 할때 16 block 씩 scan 한다. (Multi I/O) 넓은범위검색 주로배치쿼리나 OLAP 에서사용됨 /*+ full( 테이블명또는테이블별칭인덱스명 ) */ 33

34 Full Table Scan- 예제 select /*+ full(emp) */ empno, ename 1 EMP 테이블 테이블 2 Multi- I/O 3 Multi- I/O Id Operation Name Starts E-Rows A-Rows A-Time Buffers TABLE ACCESS FULL EMP :00:

35 Oracle Data Access Pattern- 정리 Rowid Index Unique Scan Index Range Scan Index Inlist Iterator Index Skip Scan Index Full Scan Index Fast Full Scan Bit Map Index Combination Full Table Scan Index 사용 35

36 Special Key Note Oracle Data Access Pattern 감사합니다. 36

Microsoft PowerPoint - Oracle Data Join Method.pptx [읽기 전용]

Microsoft PowerPoint - Oracle Data Join Method.pptx [읽기 전용] Special Key Note Oracle Data Join Method ( 주 ) 오픈메이드컨설팅 오동규수석컨설턴트 1 What is Join? JOIN is Multiply. 2 Why is the Join Method so important? 잘못사용하면큰재앙이따른다.( 위의그림처럼 ) 두개의집합을연결할수있는유일한수단. Join Method 는모든 DBMS