82 TX LOCK, Full Table Scan, Rowid Scan, Index Scan

# 요약

■ TX LOCK     1~2. enq : TX - row lock contention     3. enq : TX - allocate itl entry         테이블 재구성 alter table 테이블 move         인덱스 재구성 alter index 인덱스 rebuild online; ■ Full Table Scan ■ Rowid Scan ■ Index Scan     1. Index Range Scan     2. Index Unique Scan     3. Inlist Iterator     4. Composite Index 조합 인덱스     5. Index Full Scan select /*+ 내용 */    힌트 set autot traceonly exp 실행계획 ON set autot off           실행계획 OFF

 

 

---

 

 

■ TX LOCK

1. 특정 행을 변경하고자 하는 경우     enq : TX - row lock contention

HR1> HR@ora11g> drop table hr.emp purge; Table dropped. HR@ora11g> create table hr.emp as select * from hr.employees; Table created. HR@ora11g> select employee_id, salary from hr.emp where employee_id = 200; EMPLOYEE_ID     SALARY ----------- ----------         200       4400 HR@ora11g> update hr.emp set salary = 2000 where employee_id = 200; 1 row updated. HR2> HR@ora11g> delete from hr.emp where employee_id = 200; wait... SYS> # 이벤트 발생 확인 SYS@ora11g> select sid,serial#,username,blocking_session,event,sql_id,prev_sql_id from v$session where event like '%TX%';     SID    SERIAL# USERNAME   BLOCKING_SESSION EVENT                               SQL_ID        PREV_SQL_ID ------- ---------- ---------- ---------------- ----------------------------------- ------------- -------------      26        591 HR                       27 enq: TX - row lock contention       8suw0gu1fumpv 70aq84vtjsk4r - EVENT like '%TX%' 또는 '%enq%' # 이벤트가 발생하여 대기중인 SQL문 확인 SYS@ora11g> select sql_text from v$sql where sql_id ='8suw0gu1fumpv'; SQL_TEXT -------------------------------------------------- delete from hr.emp where employee_id = 200 # 이벤트를 발생시킨 원인 확인 SYS@ora11g> select sid,serial#,username,blocking_session,sql_id,prev_sql_id from v$session where sid in (select blocking_session from v$session);     SID    SERIAL# USERNAME   BLOCKING_SESSION SQL_ID        PREV_SQL_ID ------- ---------- ---------- ---------------- ------------- -------------      27        923 HR                                        0xwwv6ba9d01c # 이벤트를 발생시킨 SQL문 확인 SYS@ora11g> select sql_text from v$sql where sql_id ='0xwwv6ba9d01c'; SQL_TEXT -------------------------------------------------- update hr.emp set salary = 2000 where employee_id = 200 # TX, TM 타입의 LOCK 정보 확인 SYS@ora11g> select * from v$lock where sid in (26,27) and type in ('TX','TM'); ADDR     KADDR        SID TYPE           ID1        ID2      LMODE    REQUEST      CTIME      BLOCK -------- -------- ------- ------- ---------- ---------- ---------- ---------- ---------- ---------- 4956C3DC 4956C408      26 TX          327710       2979          0          6        647          0 F6CC25E8 F6CC2618      26 TM           88739          0          3          0        647          0 F6CC25E8 F6CC2618      27 TM           88739          0          3          0        664          0 F6CC25E8 F6CC2618      27 TM            5187          0          3          0        664          0 48F4685C 48F4689C      27 TX          327710       2979          6          0        664          1 - LMODE 3 : Shared mode에서도 lock을 금지         6 : exclusive. query는 허용, 같은 테이블에서의 다른 작업은 금지. - REQUEST 6 : Lock 요청 중 - BLOCK 1 : 다른 Lock을 블로킹 중 - TYPE : TM    ID1 : object ID - TYPE : TX    ID1 : undo segment 번호 + transaction slot 번호    ID2 : transaction slot sequence #  SYS@ora11g> select sid, type, id1, id2, lmode, request, block,     to_char(trunc(id1/power(2,16))) usn,     bitand(id1, to_number('ffff','xxxx'))+0 slot,     id2 sqn from v$lock  where sid in (26,27) and type in ('TX','TM');     SID TYPE           ID1        ID2      LMODE    REQUEST      BLOCK USN              SLOT        SQN ------- ------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------      26 TX          327710       2979          0          6          0 5                  30       2979      26 TM           88739          0          3          0          0 1               23203          0      27 TM           88739          0          3          0          0 1               23203          0      27 TM            5187          0          3          0          0 0                5187          0      27 TX          327710       2979          6          0          1 5                  30       2979 - USN  : Undo Segmanet No - SLOT : Transaction Slot No - SQN  : Transaction Slot Sequence# # Undo 정보 SYS@ora11g> select segment_name from dba_rollback_segs where segment_id = 5; SEGMENT_NAME ----------------------- _SYSSMU5_784711609$ # ID1 = OBJECT_ID 객체찾기 SYS@ora11g> select object_name, object_type, data_object_id, object_id from dba_objects where object_id = 88739; OBJECT_NAME          OBJECT_TYPE         DATA_OBJECT_ID  OBJECT_ID -------------------- ------------------- -------------- ---------- EMP                  TABLE                        88739      88739 - OBJECT_ID       최초 생성번호 - DATA_OBJECT_ID  현 변호, rowid에서 사용 # 대기 중인 행 정보 SYS@ora11g> select sid,serial#,username,blocking_session,event,         row_wait_obj# as obj_no,         row_wait_file# as file_no,         row_wait_block# as block_no,         row_wait_row# as row_no from v$session where event like '%TX%';     SID   SERIAL# USERNAME   BLOCKING_SESSION EVENT                           OBJ_NO FILE_NO BLOCK_NO  ROW_NO ------- --------- ---------- ---------------- ------------------------------ ------- ------- -------- -------      26       591 HR                       27 enq: TX - row lock contention    88739       4      147       2 > 충돌된 행 정보 찾기 4개 컬럼 (10g) -> 조함하여 row id 생성 가능 SYS@ora11g> select tablespace_name, file_name from dba_data_files where file_id = 4; TABLESPACE_NAME FILE_NAME --------------- -------------------------------------------------- USERS           /u01/app/oracle/oradata/ora11g/users01.dbf SYS@ora11g> select dbms_rowid.rowid_create(0, 88739, 4, 147, 2) as "RESTRICTED ROWID",        dbms_rowid.rowid_create(1, 88739, 4, 147, 2) as "EXTENDED ROWID" from dual; RESTRICTED ROWID   EXTENDED ROWID ------------------ ------------------ 00000093.0002.0004 AAAVqjAAEAAAACTAAC restricted rowid(v7) 제한된 rowid, 6byte   : #block.#rowslot.#file extended rowid(v8)   확장된 rowid, 10byte  : #object(6)+#file(3)+#block(6)+#rowslot(3) SYS@ora11g> select employee_id, salary from hr.emp where rowid = 'AAAVqjAAEAAAACTAAC'; EMPLOYEE_ID     SALARY ----------- ----------         200       4400 HR1> HR@ora11g> rollback; Rollback complete. HR2> 1 row deleted. HR@ora11g> rollback; Rollback complete. HR1> HR@ora11g> delete from hr.emp where employee_id = 200; > emp 테이블에 대해 TM Lock type이 걸리고 Lock mode RX, SX, 3 걸린다. = 데이터 갱신, 입력, 삭제 Lock > transaction 대상 행에 대해서는 TX Lock type이 걸리고 Lock mode X, 6 걸린다. = query는 허용, 다른 작업 금지 HR2> HR@ora11g> drop table hr.emp purge; ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired > emp 테이블에 대해 TM Lock type이면서 Lock mode RX, SX, 3 걸려 있어서 오류 발생 HR1> HR@ora11g> rollback; Rollback complete. HR2> HR@ora11g> drop table hr.emp purge;

 

 

2. 특정 행에서 unique key, primary key에 해당하는 데이터를 입력, 수정하는 경우     enq : TX - row lock contention

HR@ora11g> create table hr.unique_test(id number); Table created. HR@ora11g> create unique index hr.unique_test_idx on hr.unique_test(id); Index created. HR1> HR@ora11g> insert into hr.unique_test(id) values(1); 1 row created. HR2> HR@ora11g> insert into hr.unique_test(id) values(1); wait... > 중복데이터, 오류안나고 대기?? > HR1에서 같은 작업중이니 기다려라 > transaction에 해당하는 행에 대해서 TX Lock type이면서 lmode는 X, 6 수행이 되면서      동일한 값이 다른 세션에서 입력이 된 경우 REQUEST S(Share), 4 로 대기 한다. SYS> SYS@ora11g> select sid,serial#,username,blocking_session,event,sql_id,prev_sql_id from v$session where event like '%TX%';     SID    SERIAL# USERNAME   BLOCKING_SESSION EVENT                                         SQL_ID ------- ---------- ---------- ---------------- --------------------------------------------- ------------- PREV_SQL_ID -------------      26        591 HR                       27 enq: TX - row lock contention                 cuz65hgum0qns c84ynr1u8hbs2 SYS@ora11g> select * from v$lock where sid in (26,27) and type in ('TX','TM'); ADDR     KADDR        SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK -------- -------- ------- ---- ---------- ---------- ---------- ---------- ---------- ---------- 4956BC98 4956BCC4      26 TX       393232       3231          0          4        188          0 F6F89BB8 F6F89BE8      26 TM        88719          0          3          0        188          0 F6F89BB8 F6F89BE8      27 TM        88719          0          3          0        188          0 48F21538 48F21578      26 TX       458772       2248          6          0        188          0 48F792AC 48F792EC      27 TX       393232       3231          6          0        188          1 HR1> HR@ora11g> commit; HR2> ORA-00001: unique constraint (HR.UNIQUE_TEST_IDX) violated > 만약 HR1에서 rollback을 수행한 경우에는 HR2에서 insert가 수행된다.

3. 변경하고자하는 블록의 ITL에 자신에 해당하는 트랜잭션 엔트리(slot)를 등록하고자하는 경우     enq : TX - allocate ITL entry

SYS> SYS@ora11g> create table hr.itl_table(id number,l_name varchar2(1000), f_name varchar2(1000))  initrans 1 maxtrans 2 pctfree 0; Table created. - initrans : block header에 transaction layer에 생성된 transaction slot의 수 - maxtrans : data가 저장되어있는 free 공간에 생성될 수 있는 최대 transaction slot의 수                 필요할 때 free 공간이 있으면 생성해서 사용하다가 transaction이 종료되면 자동으로 해제된다. - pctfree : 최초로 블록에 데이터가 저장될 때 pctfree 값(%)을 제외 시키고 입력한다.             1. 기존의 행의 값들의 증가분때문에 남겨 놓은 free 영역                 만약에 free 공간이 없으면 다른 블록이 이전을 해야하는 문제가 발생                     row migration이 발생 할 때             2. maxtrans 값을 보장하기 위해서                 만약에 transaction slot을 생성하지 못하면 트랜잭션을 수행한 세션에서는 대기하는 일이 발생한다.                     enq : TX - allocate ITL entry SYS@ora11g> select ini_trans, max_trans, pct_free from dba_tables where table_name = 'ITL_TABLE';  INI_TRANS  MAX_TRANS   PCT_FREE ---------- ---------- ----------          1        255          0 SYS@ora11g> insert into hr.itl_table(id,l_name,f_name) select level, rpad('x',1000,'x'), rpad('z',1000,'z') from dual connect by level <= 10; 10 rows created. SYS@ora11g> commit; Commit complete. SYS@ora11g> select id, rowid, dbms_rowid.rowid_block_number(rowid) from hr.itl_table order by 3;         ID ROWID              DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ---------- ------------------ ------------------------------------          5 AAAVqRAAEAAAEXLAAA                                17867          6 AAAVqRAAEAAAEXLAAB                                17867          7 AAAVqRAAEAAAEXLAAC                                17867          8 AAAVqRAAEAAAEXLAAD                                17867          9 AAAVqRAAEAAAEXOAAA                                17870         10 AAAVqRAAEAAAEXOAAB                                17870          1 AAAVqRAAEAAAEXPAAA                                17871          2 AAAVqRAAEAAAEXPAAB                                17871          3 AAAVqRAAEAAAEXPAAC                                17871          4 AAAVqRAAEAAAEXPAAD                                17871 HR1> HR@ora11g> update hr.itl_table set l_name = rpad('y',1000,'y'), f_name = rpad('a',1000,'a') where id = 5; 1 row updated. > initrans 1 잡아 사용 HR2> HR@ora11g> update hr.itl_table set l_name = rpad('y',1000,'y'), f_name = rpad('a',1000,'a') where id = 6; 1 row updated. > pctfree 공간 사용 HR3> HR@ora11g> update hr.itl_table set l_name = rpad('y',1000,'y'), f_name = rpad('a',1000,'a') where id = 7; wait... > 왜 못하고 있지? SYS> SYS@ora11g> select sid,serial#,username,blocking_session,event,sql_id,prev_sql_id from v$session where event like '%TX%';     SID    SERIAL# USERNAME   BLOCKING_SESSION EVENT                                         SQL_ID ------- ---------- ---------- ---------------- --------------------------------------------- ------------- PREV_SQL_ID -------------      23      48333 HR                       27 enq: TX - allocate ITL entry                  8up8cm5r9rghs dyk4dprp70d74 > HR2 때문에 HR3 대기 중 SYS@ora11g> select sid,serial#,username,blocking_session,sql_id,prev_sql_id from v$session where sid in (select blocking_session from v$session);     SID    SERIAL# USERNAME   BLOCKING_SESSION SQL_ID        PREV_SQL_ID ------- ---------- ---------- ---------------- ------------- -------------      27        923 HR                                        3635umwnmxj1s > 문제 원인 SYS@ora11g> select sql_text from v$sql where sql_id ='3635umwnmxj1s'; SQL_TEXT -------------------------------------------------- update hr.itl_table set l_name = rpad('y',1000,'y' ), f_name = rpad('a',1000,'a') where id = 5 HR1> + HR2> + HR3> HR@ora11g> rollback; Rollback complete. SYS> SYS@ora11g> select ini_trans, max_trans, pct_free from dba_tables where table_name = 'ITL_TABLE';  INI_TRANS  MAX_TRANS   PCT_FREE ---------- ---------- ----------          1        255          0 >> enq: TX - allocate ITL entry 해결 방안 # 테이블 재구성 SYS@ora11g> alter table hr.itl_table initrans 2 pctfree 10; Table altered. > block parameter 값을 수정한 경우 새로운 block부터 적용된다. SYS@ora11g> select ini_trans, max_trans, pct_free from dba_tables where table_name = 'ITL_TABLE';  INI_TRANS  MAX_TRANS   PCT_FREE ---------- ---------- ----------          2        255         10 # 기존 block에도 새로운 block parameter를 적용하려면 테이블을 재구성해야한다. SYS@ora11g> alter table hr.itl_table move; Table altered. # 테이블을 재구성하면 기존 행들의 rowid가 바뀐다. 인덱스는 꼭 재구성해야한다. SYS@ora11g> alter index hr.itl_table_id rebuild online; HR1> HR@ora11g> update hr.itl_table set l_name = rpad('y',1000,'y'), f_name = rpad('a',1000,'a') where id = 5; 1 row updated. HR2> HR@ora11g> update hr.itl_table set l_name = rpad('y',1000,'y'), f_name = rpad('a',1000,'a') where id = 6; 1 row updated. HR3> HR@ora11g> update hr.itl_table set l_name = rpad('y',1000,'y'), f_name = rpad('a',1000,'a') where id = 7; 1 row updated.

---

■ Full Table Scan
- 많은 양의 데이터 검색시 유용함
- 첫번째 블록부터 마지막 사용한 블록(High Water Mark)까지 읽어오는 방식
- Multi Block I/O 발생한다.
- 한번에 I/O call 방식할 때 db_file_multiblock_read_count 설정되어있는 블록 수만큼 읽어 들이기 위해서는
    extent 안에 db_file_multiblock_read_count 설정되어있는 갯수만큼 있을 경우에 나름 Multi Block I/O 성능이
    좋을 수 있다.
- db file scattered read wait event 발생할 수 있다.
    full table scan으로 인해서 과도한 multi block i/o 발생 시에 나온다.

SYS@ora11g> show parameter db_file_multiblock_read_count NAME                                 TYPE    VALUE ------------------------------------ ------- ------------------------------ db_file_multiblock_read_count        integer 81 HR> HR@ora11g> create table hr.emp as select * from hr.employees; # 실행계획 보기 HR@ora11g> set autot traceonly exp HR@ora11g> select * from hr.emp where employee_id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |   133 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| EMP  |     1 |   133 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------  ㄴ * 표시가 있다면 밑에 정보 확인 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("EMPLOYEE_ID"=100)         ㄴ filter 방식이면 Full Scan Note -----    - dynamic sampling used for this statement (level=2) # full table scan 속도를 개선 1. 병렬 작업(parallel 또는 parallel_index 힌트를 사용)     -> 많이 사용      데이터 버퍼캐시를 덜 사용 = 다이렉트       2. db_file_multiblock_read_count 파리미터의 값을 조정하자 alter session set db_file_multiblock_read_count = 128; select /*+ full(e) parallel(e,2) */ * from emp e; 이 테이블을 무조건 풀스캔, 프로세서 2개 띄어서 읽자. 인덱스 못하게 full parallel 같이 사용

---

■ rowid scan
- user rowid, index rowid를 이용하여 소량의 데이터 검색 시 유용하다.
- single block I/O 수행
- DB file sequential read wait event 발생할 수 있다.

select  employee_id,         rowid,          dbms_rowid.rowid_object(rowid) as object_id,         dbms_rowid.rowid_relative_fno(rowid) as file_no,         dbms_rowid.rowid_block_number(rowid) as block_no,         dbms_rowid.rowid_row_number(rowid) as row_slot_no from hr.emp; EMPLOYEE_ID ROWID               OBJECT_ID    FILE_NO   BLOCK_NO ROW_SLOT_NO ----------- ------------------ ---------- ---------- ---------- -----------         198 AAAVqdAAEAAAEXLAAA      88733          4      17867           0         199 AAAVqdAAEAAAEXLAAB      88733          4      17867           1         200 AAAVqdAAEAAAEXLAAC      88733          4      17867           2         201 AAAVqdAAEAAAEXLAAD      88733          4      17867           3         202 AAAVqdAAEAAAEXLAAE      88733          4      17867           4         203 AAAVqdAAEAAAEXLAAF      88733          4      17867           5         204 AAAVqdAAEAAAEXLAAG      88733          4      17867           6         205 AAAVqdAAEAAAEXLAAH      88733          4      17867           7         206 AAAVqdAAEAAAEXLAAI      88733          4      17867           8         100 AAAVqdAAEAAAEXLAAJ      88733          4      17867           9 ...         188 AAAVqdAAEAAAEXLABh      88733          4      17867          97         189 AAAVqdAAEAAAEXMAAA      88733          4      17868           0         190 AAAVqdAAEAAAEXMAAB      88733          4      17868           1         191 AAAVqdAAEAAAEXMAAC      88733          4      17868           2 SYS@ora11g> set autot traceonly exp HR@ora11g> select * from hr.emp where employee_id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |   133 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| EMP  |     1 |   133 |     3   (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("EMPLOYEE_ID"=100) Note -----    - dynamic sampling used for this statement (level=2) >> 가장 빠르지만 rowid 알기 쉽지 않다

---

■ index scan

1. index range scan
인덱스 root block -> branch block -> leaf block 까지 수직으로 탐색한 후 
leaf block에서 필요한 범위만 스캔하는 방식(one plus one scan)

	HR@ora11g> create index hr.emp_idx on hr.emp(employee_id); HR@ora11g> select ix.index_name, ix.uniqueness, ic.column_name from user_indexes ix, user_ind_columns ic where ix.index_name = ic.index_name and ix.table_name = 'EMP'; INDEX_NAME                     UNIQUENES COLUMN_NAME ------------------------------ --------- -------------------- EMP_IDX                        NONUNIQUE EMPLOYEE_ID HR@ora11g> set autot traceonly exp HR@ora11g> select * from hr.emp where employee_id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 1472992808 --------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |     1 |   133 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |   133 |     2   (0)| 00:00:01 | I/O |*  2 |   INDEX RANGE SCAN          | EMP_IDX |     1 |       |     1   (0)| 00:00:01 | 인덱스 스캔 --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("EMPLOYEE_ID"=100)         ㄴ access : 해당 데이터의 block이 어디에 있는지 안다.          Note -----    - dynamic sampling used for this statement (level=2) > NONUNIQUE이면 INDEX RANGE SCAN one plus one scan 발생하면 느려지니 buffer pinning (latch 점유, i/o 덜 발생) # 실행계획 보기 종료 HR@ora11g> set autot off
	HR@ora11g> set autot traceonly exp HR@ora11g> select /*+ full(e) */ * from hr.emp e where employee_id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |   133 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| EMP  |     1 |   133 |     3   (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("EMPLOYEE_ID"=100) Note -----    - dynamic sampling used for this statement (level=2)
	HR@ora11g> select * from hr.emp where to_number(employee_id) = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 3956160932 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |   133 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| EMP  |     1 |   133 |     3   (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(TO_NUMBER(TO_CHAR("EMPLOYEE_ID"))=100) Note -----    - dynamic sampling used for this statement (level=2) >> 1건 찾는데 풀스캔. 성능 저하
	HR@ora11g> select /*+ index(e emp_idx) */ * from hr.emp e where employee_id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 1472992808 --------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |     1 |   133 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |   133 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | EMP_IDX |     1 |       |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("EMPLOYEE_ID"=100) Note -----    - dynamic sampling used for this statement (level=2)
# INDEX RANGE SCAN 유도
	HR@ora11g> select /*+ index_rs(e emp_idx) */ * from hr.emp e where employee_id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 1472992808 --------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |     1 |   133 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |   133 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | EMP_IDX |     1 |       |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("EMPLOYEE_ID"=100) Note -----    - dynamic sampling used for this statement (level=2)

 

/*+ */ : 힌트

	HR@ora11g> drop index hr.emp_idx; Index dropped. HR@ora11g> create unique index hr.emp_idx on hr.emp(employee_id); Index created. HR@ora11g> alter table hr.emp add constraint emp_id_pk primary key(employee_id) using index hr.emp_idx; Table altered. HR@ora11g> select ix.index_name, ix.uniqueness, ic.column_name from user_indexes ix, user_ind_columns ic where ix.index_name = ic.index_name and ix.table_name = 'EMP'; INDEX_NAME                     UNIQUENES COLUMN_NAME ------------------------------ --------- -------------------- EMP_IDX                        UNIQUE    EMPLOYEE_ID select c.column_name, u.constraint_name, u.constraint_type, u.search_condition, u.index_name from user_constraints u, user_cons_columns c where u.constraint_name = c.constraint_name and u.table_name = 'EMP'; COLUMN_NAME          CONSTRAINT_NAME                C SEARCH_CONDITION          INDEX_NAME -------------------- ------------------------------ - ------------------------- ------------------------------ LAST_NAME            SYS_C0011602                   C "LAST_NAME" IS NOT NULL EMAIL                SYS_C0011603                   C "EMAIL" IS NOT NULL HIRE_DATE            SYS_C0011604                   C "HIRE_DATE" IS NOT NULL JOB_ID               SYS_C0011605                   C "JOB_ID" IS NOT NULL EMPLOYEE_ID          EMP_ID_PK                      P                           EMP_IDX
2. index unique scan - 컬럼에 유일한 값으로 인덱스가 생성된 경우 사용된다. - 비교연산자는 = 사용할때만 사용된다. - unique index라고 하더라도 범위스캔을 수행할 경우 index range scan 으로 수행된다.
	HR@ora11g> set autot traceonly exp HR@ora11g> select * from hr.emp where employee_id = 100; Execution Plan ---------------------------------------------------------- Plan hash value: 2466118986 --------------------------------------------------------------------------------------- | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |         |     1 |   133 |     1   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     1 |   133 |     1   (0)| 00:00:01 | |*  2 |   INDEX UNIQUE SCAN         | EMP_IDX |     1 |       |     0   (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("EMPLOYEE_ID"=100)

3. Inlist Iterator : 내부적으로 select문을 분리

	HR@ora11g> select * from hr.emp where employee_id in (100,200); Execution Plan ---------------------------------------------------------- Plan hash value: 1651504986 ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |     2 |   266 |     2   (0)| 00:00:01 | |   1 |  INLIST ITERATOR             |         |       |       |            |          | |   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |     2 |   266 |     2   (0)| 00:00:01 | |*  3 |    INDEX UNIQUE SCAN         | EMP_IDX |     1 |       |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("EMPLOYEE_ID"=100 OR "EMPLOYEE_ID"=200)                                 ㄴ select문이 2개, 내부적으로 select문을 분리 Note -----    - dynamic sampling used for this statement (level=2)
	HR@ora11g> select * from hr.emp where employee_id = 100 union all select * from hr.emp where employee_id = 200; Execution Plan ---------------------------------------------------------- Plan hash value: 1345714510 ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |     2 |   266 |     2   (0)| 00:00:01 | |   1 |  UNION-ALL                   |         |       |       |            |          | |   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |   133 |     1   (0)| 00:00:01 | |*  3 |    INDEX UNIQUE SCAN         | EMP_IDX |     1 |       |     0   (0)| 00:00:01 | |   4 |   TABLE ACCESS BY INDEX ROWID| EMP     |     1 |   133 |     1   (0)| 00:00:01 | |*  5 |    INDEX UNIQUE SCAN         | EMP_IDX |     1 |       |     0   (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    3 - access("EMPLOYEE_ID"=100)    5 - access("EMPLOYEE_ID"=200)
	HR@ora11g> select * from hr.emp where employee_id = 100 union select * from hr.emp where employee_id = 200; Execution Plan ---------------------------------------------------------- Plan hash value: 28680880 ----------------------------------------------------------------------------------------- | Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT              |         |     2 |   266 |     4  (50)| 00:00:01 | |   1 |  SORT UNIQUE                  |         |     2 |   266 |     4  (50)| 00:00:01 | |   2 |   UNION-ALL                   |         |       |       |            |          | |   3 |    TABLE ACCESS BY INDEX ROWID| EMP     |     1 |   133 |     1   (0)| 00:00:01 | |*  4 |     INDEX UNIQUE SCAN         | EMP_IDX |     1 |       |     0   (0)| 00:00:01 | |   5 |    TABLE ACCESS BY INDEX ROWID| EMP     |     1 |   133 |     1   (0)| 00:00:01 | |*  6 |     INDEX UNIQUE SCAN         | EMP_IDX |     1 |       |     0   (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    4 - access("EMPLOYEE_ID"=100)    6 - access("EMPLOYEE_ID"=200)
	HR@ora11g> create index hr.emp_name_idx on hr.emp(last_name); Index created. HR@ora11g> select ix.index_name, ix.uniqueness, ic.column_name from user_indexes ix, user_ind_columns ic where ix.index_name = ic.index_name and ix.table_name = 'EMP'; INDEX_NAME                     UNIQUENES COLUMN_NAME ------------------------------ --------- -------------------- EMP_IDX                        UNIQUE    EMPLOYEE_ID EMP_NAME_IDX                   NONUNIQUE LAST_NAME HR@ora11g> select * from hr.emp where last_name = 'King'; Execution Plan ---------------------------------------------------------- Plan hash value: 2039216739 -------------------------------------------------------------------------------------------- | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |              |     2 |   266 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |     2 |   266 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | EMP_NAME_IDX |     2 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("LAST_NAME"='King') Note -----    - dynamic sampling used for this statement (level=2)
	HR@ora11g> select * from hr.emp where last_name = 'King' and first_name = 'Steven'; Execution Plan ---------------------------------------------------------- Plan hash value: 2039216739 -------------------------------------------------------------------------------------------- | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |              |     1 |   133 |     2   (0)| 00:00:01 | |*  1 |  TABLE ACCESS BY INDEX ROWID| EMP          |     1 |   133 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | EMP_NAME_IDX |     2 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("FIRST_NAME"='Steven')    ->     2 - access("LAST_NAME"='King')        Note -----    - dynamic sampling used for this statement (level=2) > 인덱스가 걸린 컬럼 and 인덱스 없는 컬럼의 문제점     first_name은 인덱스 없어서 full scan 발생 > 자주 묶이는 칼럼이라면 차라리 조합 인덱스를 만들자

4. 조합 인덱스 - where절에 자주 사용되는 칼럼들을 하나의 인덱스 생성 - 선행 컬럼을 잘 만들어야 한다.     기준 우선순위 : 혼자서도 자주 사용되는 컬럼                   범위를 줄일 수 있는 컬럼

HR@ora11g> drop index hr.emp_name_idx; Index dropped. HR@ora11g> create index hr.emp_name_idx on hr.emp(last_name, first_name); Index created. HR@ora11g> select ix.index_name, ix.uniqueness, ic.column_name, ic.column_position from user_indexes ix, user_ind_columns ic where ix.index_name = ic.index_name and ix.table_name = 'EMP'; INDEX_NAME                     UNIQUENES COLUMN_NAME          COLUMN_POSITION ------------------------------ --------- -------------------- --------------- EMP_IDX                        UNIQUE    EMPLOYEE_ID                        1 EMP_NAME_IDX                   NONUNIQUE FIRST_NAME                         2 EMP_NAME_IDX                   NONUNIQUE LAST_NAME                          1 HR@ora11g> select * from hr.emp where last_name = 'King' and first_name = 'Steven'; Execution Plan ---------------------------------------------------------- Plan hash value: 2039216739 -------------------------------------------------------------------------------------------- | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |              |     1 |   133 |     2   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |     1 |   133 |     2   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | EMP_NAME_IDX |     1 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("LAST_NAME"='King' AND "FIRST_NAME"='Steven') Note -----    - dynamic sampling used for this statement (level=2)

5. index full scan

HR@ora11g> select last_name, first_name from hr.emp; Execution Plan ---------------------------------------------------------- Plan hash value: 1448724716 --------------------------------------------------------------------------------- | Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------- |   0 | SELECT STATEMENT |              |   107 |  2782 |     1   (0)| 00:00:01 | |   1 |  INDEX FULL SCAN | EMP_NAME_IDX |   107 |  2782 |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------- Note -----    - dynamic sampling used for this statement (level=2) HR@ora11g> select count(*) from hr.emp; Execution Plan ---------------------------------------------------------- Plan hash value: 660937672 -------------------------------------------------------------------- | Id  | Operation        | Name    | Rows  | Cost (%CPU)| Time     | -------------------------------------------------------------------- |   0 | SELECT STATEMENT |         |     1 |     1   (0)| 00:00:01 | |   1 |  SORT AGGREGATE  |         |     1 |            |          | |   2 |   INDEX FULL SCAN| EMP_IDX |   107 |     1   (0)| 00:00:01 | -------------------------------------------------------------------- Note -----    - dynamic sampling used for this statement (level=2) - SORT AGGREGATE : 그룹함수를 사용했구나 select c.column_name, u.constraint_name, u.constraint_type, u.search_condition, u.index_name from user_constraints u, user_cons_columns c where u.constraint_name = c.constraint_name and u.table_name = 'EMP'; COLUMN_NAME          CONSTRAINT_NAME                C SEARCH_CONDITION          INDEX_NAME -------------------- ------------------------------ - ------------------------- -------------------- LAST_NAME            SYS_C0011602                   C "LAST_NAME" IS NOT NULL EMAIL                SYS_C0011603                   C "EMAIL" IS NOT NULL HIRE_DATE            SYS_C0011604                   C "HIRE_DATE" IS NOT NULL JOB_ID               SYS_C0011605                   C "JOB_ID" IS NOT NULL EMPLOYEE_ID          EMP_ID_PK                      P                           EMP_IDX

6. index fast full scan

HR@ora11g> select /*+ index_ffs(e emp_idx) */ count(*) from hr.emp e; Execution Plan ---------------------------------------------------------- Plan hash value: 3644266057 ------------------------------------------------------------------------- | Id  | Operation             | Name    | Rows  | Cost (%CPU)| Time     | ------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |         |     1 |     2   (0)| 00:00:01 | |   1 |  SORT AGGREGATE       |         |     1 |            |          | |   2 |   INDEX FAST FULL SCAN| EMP_IDX |   107 |     2   (0)| 00:00:01 | ------------------------------------------------------------------------- Note -----    - dynamic sampling used for this statement (level=2) # index full scan - single block i/o - 정렬 보장 - 속도 느림 - DB file sequential read # index fast full scan - multi block i/o - 정렬 안됨 - 속도 빠름 - db file scatteted read # index full scan보다 index fast full scan이 더 빠른 이유? - multi block i/o     db_file_multiblock_read_count - 병령처리가 가능 HR@ora11g> select /*+ index_ffs(e emp_idx) [parallel_index(e,emp_idx,2) */ count(*) from hr.emp e; Execution Plan ---------------------------------------------------------- Plan hash value: 3644266057 ------------------------------------------------------------------------- | Id  | Operation             | Name    | Rows  | Cost (%CPU)| Time     | ------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |         |     1 |     2   (0)| 00:00:01 | |   1 |  SORT AGGREGATE       |         |     1 |            |          | |   2 |   INDEX FAST FULL SCAN| EMP_IDX |   107 |     2   (0)| 00:00:01 | ------------------------------------------------------------------------- Note -----    - dynamic sampling used for this statement (level=2)