2024/02/07
상도형님꺼
※ hwm(High-Water Mart) 이동하는 과정에서 HW lock 경합이 발생할 수 있다.
이 경우 발생하는 대기 이벤트 ---> enq: HW - contention ---> system lock
★ Transaction 처리 순서
-
undo segment 할당
- 할당을 받지 못하는 경우, 발생하는 대기 이벤트 ---> enq : US - contention
-
undo segment header 에 transaction table slot 을 생성
- transaction 정보를 저장하는 곳, TXID(Transaction ID)를 생성한다.
-
Transaction 의 대상이 되는 블록을 데이터 버퍼 캐시의 실행계획을 통해서 찾는다.
- block header 에 ITL(Interested Transaction List)에 transaction entry 를 등록.
- 만약에 ITL에 entry 를 등록할 공간이 없다면 공간이 확보될 때까지 대기해야 한다.
이 때 발생한 대기 이벤트는 enq: TX - allocate ITL entry
-
변경할 블록들의 변경 정보를 PGA 영역에서 change vector 를 생성한다.
- undo segment header 정보(change vector #1) - undo block(change vector #2) - transaction 의 대상이 되는 블록(change vector #3)PGA 영역 안에 change vector 정보를 redo entry 라는 이름으로 redo log buffer 로 복사한다.
redo copy latch, redo allocation latch, redo writing latch 를 획득해야 한다.이 과정에서 latch 경합이 발생하면 - latch : redo copy - latch : redo allocation - latch : redo writing - log buffer space wait event 가 발생한다. - log file switch completion wait event 가 발생할 수 있다. log file switch completion wait event : 현재 리두 로그 파일이 꽉차서 로그 스위치가 발생하는 경우, LGWR 은 이 작업이 끝날 때까지 대기해야 한다. -
이전 이미지(값)에 대한 정보를 undo block 에 기록하고 데이터 블록을 변경한다.
변경된 block 은 dirty 상태가 된다. ---> 메모리에 있는 내용과 디스크에 있는 내용이 다른 상태
변경된 block header 에 CR(Consistent Read) 블록으로 작성해 놓는다.
변경하는 행(row)에 대해서 lock 을 생성한다.
다른 세션에서 이 행(row)에 대해서 lock 을 걸고 있으면 기다려야 한다.
이 때 발생한 대기 이벤트는 enq: TX - row lock contention
★ commit
- SCN(System Commit Number) 할당, commit 정보를 redo log buffer 에 저장
- 언그 세그먼트 헤더의 트랜잭션 테이블에 commit 이 완료되었다는 정보를 저장
- transaction entry, lock 해제
- LGWR 이 작동된다. ---> redo log buffer 의 내용을 리두로그파일에 기록
★ Latch
- 가벼운 lock(light-weight lock)
- 공유 메모리 영역(SGA)을 보호하기 위한 동기화 객체
★ lock
- latch 보다는 무거운 동기화 객체
- 데이터베이스와 관련된 객체(object)를 보호하는 동기화 객체
- enqueue lock
<< hr session >>
HR@ora11g> drop table hr.emp purge;
drop table hr.emp purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
HR@ora11g> create table hr.emp as select * from hr.employees;
Table created.- update 작업
- 테이블에 대해서는 TM lock, 트랜잭션 대상 행(row)에 대해서는 TX lock 즉 두 가지의 lock 이 걸린다.
HR@ora11g> update hr.emp ---> 테이블(table)에 대해서 lock
set salary = salary * 1.1
where employee_id = 100; 2 3 ---> 행(row)에 대해서 lock
1 row updated.
<< sys session >>
SYS@ora11g> drop table hr.emp purge;
drop table hr.emp purge
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SYS@ora11g> select sid from v$session where username = 'HR';
SID
----------
26
SYS@ora11g> SELECT * FROM V$lock where sid = 26;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
4956B8A8 4956B8D4 26 TO 79605 1 3 0 449 0
4956BC1C 4956BC48 26 AE 100 0 4 0 477 0
F6FBC784 F6FBC7B4 26 TM 88675 0 3 0 365 0
48F53F88 48F53FC8 26 TX 983044 839 6 0 365 0
TYPE : TM
ID1 : OBJECT ID
select * from dba_objects where object_id = 88675;
select * from v$session where username = 'HR';
select sid from v$session where username = 'HR';
SELECT * FROM V$lock where sid = 26;
SELECT * FROM v$lock where sid = 188; ---> 다른 HR 세션을 열어서 동일한 UPDATE 문 실행
SELECT * FROM dba_objects where object_id = 88675;
select * from v$lock where sid in (26,188);TYPE : TX
ID1 : undo segment 번호 + transaction slot 번호
ID2 : transaction slot sequence
LMODE > 0 이면 LOCK 을 보유 중인 세션
REQUEST > 0 이면 LOCK 을 요청 중인 세션
CTIME : 현재 LOCK 모드가 허용된 이후의 시간(second), 즉 lock 을 보유하거나 요청한 이후부터의 시간(초)
BLOCK : 현재 LOCK 이 다른 LOCK 을 블로킹하고 있는지 여부
1 : 다른 LOCK 을 블로킹 중.
0 : 다른 LOCK 을 블로킹하지 않음.
LMODE
-
Row Share(RS), Sub Share(SS), 2
LOCK 된 테이블에 대한 동시 액세스를 허용하지만 세션이 배타적(exclusive) 액세스를 위해 전체 테이블 LOCK 하는 것은
금지한다.
LOCK TABLE hr.emp IN ROW SHARE MODE; -
Row eXclusive(RX), Sub eXclusive(SX), 3 : Row Share 와 동일하지만 shared 모드에서도 lock 을 금지한다.
Row eXclusive(RX) lock 은 데이터 갱신, 입력, 삭제시 자동으로 획득한다.
LOCK TABLE hr.emp IN ROW EXCLUSIVE MODE; ---> 수동으로
-
Share(S), 4 : 동시 query 는 허용하지만 lock 된 테이블에 대한 변경은 금지한다.
테이블에서 인덱스를 생성하려면 share lock 이 필요하며 자동으로 요청한다.unique, primary key 충돌시 발생 LOCK TABLE hr.emp IN SHARE MODE; ---> 수동으로 -
Share Row eXclusive(SRX), Share Sub eXclusive(SSX) 5
전체 테이블을 query 하는데 사용되며 다른 유저가 테이블 행을 query 하는 것은 허용하지만 해당 테이블을 share mode
에서 lock 하거나 행(row)을 갱신하는 것은 금지한다.row cache lock(sequence nocache 속성에서 nextval 을 호출할 때마다 dictionary 변경) LOCK TABLE hr.emp IN SHARE ROW EXCLUSIVE MODE; -
eXclusive(X), 6 : LOCK 된 테이블에서의 query 는 허용하지만 해당 테이블에서의 다른 작업은 금지한다.
즉 테이블에 DDL 작업 시에 exclusive lock 이 필요하다.LOCK TABLE hr.emp IN ROW EXCLUSIVE MODE;
★ Redo 기능
- Database 복구를 목적으로 설계
- Database 에 적용된 모든 변경 사항에 대한 이력 저장
- DML/DDL/Recursive SQL 에 의해 변경된 모든 Data 이력 (nologging 제외)
- DDL Text 저장 (DML Text 제외)
★ LGWR에 의한 Redo 기록
- Redo Buffer 내용을 Redo Log File 에 기록하는 시점
- 매 3초 마다
- Log Buffer 의 1/3 또는 1MB 가 저장될 때
- User Process 가 Commit 또는 Rollback 으로 Transaction 을 종료할 때 (Log Force at Commit)
- DBWR Process 에 의해 신호를 받을 때 (write ahead logging)
★ Log Force at Commit
- Transaction 과 관련된 모든 Redo Record 를 Log File 에 저장 후 Commit 완료
★ Write Ahead Log
- Data Buffer 에 기록하기 전에 Log Buffer 에 먼저 기록
- Data File 에 기록하기 전에 Log File 에 먼저 기록
8i : 버전 까지는 redo allocation latch 시스템에 1개여서 오직 하나의 프로세스만이 redo buffer 에서 메모리 할당할 수
있었다.
9i : shared redo strands : redo buffer 의 영역을 일정 개수로 분할해서 사용. 분할된 영역을 strand 라고 한다.
_log_parallelism 파라미터의 값을 이용해서 redo strand 의 개수를 지정.(cpu수/8) 권장사항
각 strand 마다 redo allocation latch 사용
10g : - shared redo strands 개수를 오라클이 동적으로 관리한다. _log_parallelism_dynamic 히든 파라미터를 TRUE로
설정.
- private redo strands 기능을 사용함으로써 리두데이터를 pga 영역에서 change vector 를 생성하는것이 아닌
shared pool 에 private strands 영역에 저장하며 이 영역에 저장된 로그 데이터는 redo buffer 를 거치지 않고
redo log file 에 저장함으로써 latch 경합을 최소화한다. _log_private_parallelism 히든 파라미터의 값을
true 로 설정하면 private redo strands 기능이 활성화된다.10gR2 : private redo strands 를 위한 공간은 LOG_BUFFER 내에 생성되며 _log_pivate_mul 에 지정된 비율만큼(5퍼센트)
을 private redo strands 공간으로 사용. zero copy redo 라고도 한다.
- 히든 파라미터 조회
SQL> SELECT a.ksppinm Parameter, b.ksppstvl Value
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx
AND a.ksppinm in ('_log_parallelism_dynamic','_log_private_mul');
PARAMETER VALUE
-------------------------------------------------- ----------
_log_parallelism_dynamic TRUE
_log_private_mul 5redo log buffer latch
- latch : redo copy
- latch : redo allocation
- latch : redo writing
============================================================================================================
create table hr.redo_table(id number, name char(100));
insert into hr.redo_table(id,name)
select object_id, object_name
from dba_objects;
select t.used_ublk, t.used_urec
from v$transaction t, v$session s
where s.sid = (select sid from v$mystat where rownum = 1)
and s.taddr = t.addr;- 리두 모니터링 쿼리
select n.name, sum(s.value)
from v$sesstat s, v$statname n
where n.name in ('redo entries','redo size','redo synch writes','redo writes','redo blocks written',
'redo log space requests','redo log space wait time')
and s.statistic# = n.statistic#
and s. sid = (select sid from v$mystat where rownum = 1)
group by n.name;- 리두모니터링 통계 정보
redo entries : redo entry 가 redo log buffer 에 기록된 횟수
redo size : redo size(byte)
redo log space requests : redo log buffer 에 redo entry 들을 LGWR 가 redo log file 에 쓰려고 하는데 log file
이 꽉차서 log switch 를 발상한 횟수
redo log space wait time : redo log space requests 에 소요된 시간(1/100 초)
redo synch writes : commit, rollback 에 의해 수행된 redo write 수
redo blocks written : redo log file 에 write 된 redo log block 수
redo writes : LGWR 수행한 수
select blocks, bytes/1024/1024 mb
from dba_segments
where segment_name = 'REDO_TABLE';SYS@ora11g> rollback;
Rollback complete.
SYS@ora11g> select n.name, sum(s.value)
from v$sesstat s, v$statname n
where n.name in ('redo entries','redo size','redo synch writes','redo writes','redo blocks written',
'redo log space requests','redo log space wait time')
and s.statistic# = n.statistic#
and s. sid = (select sid from v$mystat where rownum = 1)
group by n.name; 2 3 4 5 6 7
NAME SUM(S.VALUE)
-------------------------------------------------- ------------
redo entries 189
redo size 45004
redo log space requests 0
redo log space wait time 0
redo synch writes 2
redo blocks written 0
redo writes 0
SYS@ora11g> truncate table hr.redo_table;
Table truncated.
SYS@ora11g> select blocks, bytes/1024/1024 mb
from dba_segments
where segment_name = 'REDO_TABLE'; 2 3
BLOCKS MB
---------- ----------
8 .0625<< NEW SESSION >>
SYS@ora11g> select n.name, sum(s.value)
from v$sesstat s, v$statname n
where n.name in ('redo entries','redo size','redo synch writes','redo writes','redo blocks written',
'redo log space requests','redo log space wait time')
and s.statistic# = n.statistic#
and s. sid = (select sid from v$mystat where rownum = 1)
group by n.name; 2 3 4 5 6 7
NAME SUM(S.VALUE)
---------------------------------------------------------------- ------------
redo entries 0
redo size 0
redo log space requests 0
redo log space wait time 0
redo synch writes 0
redo blocks written 0
redo writes 0# logging or nologging
SYS@ora11g> select logging from dba_tables where table_name = 'REDO_TABLE';
LOG
---
YES
SYS@ora11g> alter table hr.redo_table nologging;
Table altered.
SYS@ora11g> select logging from dba_tables where table_name = 'REDO_TABLE';
LOG
---
NO
SYS@ora11g> select n.name, sum(s.value)
from v$sesstat s, v$statname n
2 3 where n.name in ('redo entries','redo size','redo synch writes','redo writes','redo blocks written',
'redo log space requests','redo log space wait time')
and s.statistic# = n.statistic#
and s. sid = (select sid from v$mystat where rownum = 1)
group by n.name; 4 5 6 7
NAME SUM(S.VALUE)
---------------------------------------------------------------- ------------
redo entries 6
redo size 2340
redo log space requests 0
redo log space wait time 0
redo synch writes 1
redo blocks written 0
redo writes 0
<< NEW SESSION >>
SYS@ora11g> select n.name, sum(s.value)
from v$sesstat s, v$statname n
where n.name in ('redo entries','redo size','redo synch writes','redo writes','redo blocks written',
'redo log space requests','redo log space wait time')
and s.statistic# = n.statistic#
and s. sid = (select sid from v$mystat where rownum = 1)
group by n.name; 2 3 4 5 6 7
NAME SUM(S.VALUE)
---------------------------------------------------------------- ------------
redo entries 0
redo size 0
redo log space requests 0
redo log space wait time 0
redo synch writes 0
redo blocks written 0
redo writes 0
2024/02/08
■ TX LOCK
▶ 1. 특정 행을 변경하고자 하는 경우
enq : TX - row lock contention
- 작업 테이블생성
drop table hr.emp purge;
create table hr.emp
as select * from hr.employees;[sess_1]
- update작업
update hr.emp set salary = 2000 where employee_id = 200;[sess_2]
- delete작업 : lock이 걸린다
delete from hr.emp where employee_id = 200;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
-------------
21 2039 HR 186
enq: TX - row lock contention 8suw0gu1fumpv
bmbdnqq9s0m7q
SYS@ora11g> select sql_text from v$sql where sql_id = 'bmbdnqq9s0m7q';
SQL_TEXT
--------------------------------------------------------------------------------
create table hr.emp
SYS@ora11g> select sql_text from v$sql where sql_id = '8suw0gu1fumpv';
SQL_TEXT
--------------------------------------------------------------------------------
delete from hr.emp where employee_id = 200
- blocking_session으로 확인
select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where sid in (select blocking_session from v$session);
---
186 975 HR SQL*Net message from client 0xwwv6ba9d01c- lock정보
SYS@ora11g> select * from v$lock where sid in(9,186) and type in ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
CTIME BLOCK
---------- ----------
F6F1EB28 F6F1EB58 186 TM 88883 0 3 0
881 0
3E3E9074 3E3E90B4 186 TX 131084 2529 6 0
881 1
TYPE : TM
ID1 : object id
TYPE : TX
ID1 : undo segment번호 + transaction slot 번호
ID2 : transaction slot sequence 번호
selct sid, type, id1, id2, lmode, request,
block, to_char(trunc(id1/power(2,16))) usn,
bitland(id1,to_number('ffff','xxxx')) + 0 slot,
id2 sqn
from v$lock
where sid in(9,186) and type in ('TX','TM');
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(9,186) and type in ('TX','TM'); 2 3 4 5 6
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
USN SLOT SQN
---------------------------------------- ---------- ----------
186 TM 88883 0 3 0 0
1 23347 0
186 TX 131084 2529 6 0 1
2 12 2529
SYS@ora11g> select segment_name from dba_rollback_segs where segment_id = 2;
SEGMENT_NAME
------------------------------
_SYSSMU2_1139286244$
SYS@ora11g> select object_name, object_type, data_object_id, object_id
from dba_objects
where object_id = 88883; 2 3
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE DATA_OBJECT_ID OBJECT_ID
------------------- -------------- ----------
EMP
TABLE 88883 88883
- 행 wait 한 오브젝트(데이터)번호,파일번호,블록번호,로우 슬롯번호
select sid, serial#, username, blocking_session, event,
row_wait_obj# as obj_no, -- row 번호
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%';
----------------------------------------------------------
HR 186 enq: TX - row lock contention 88883 4 539 2- file id를 이용하여 wait event걸린거 위치확인
select tablespace_name, file_name
from dba_data_files where file_id =4;- rowid 만들기
- restricted rowid(v7) 6byte
#block번호.#rowslot번호.#file번호
- extended rowid(8v) 10byte
#object번호(6자리) + #rowslot번호(3자리) + #block번호(6자리) + #rowslot번호(3자리)
SYS@ora11g> select
dbms_rowid.rowid_create(0,88883,4,539,2) as "restricted rowid",
dbms_rowid.rowid_create(1,888 2 83,4,539,2) as "extended rowid"
from dual; 3 4
restricted rowid extended rowid
------------------ ------------------
0000021B.0002.0004 AAAVszAAEAAAAIbAAC
- rowid를 이용하여 hang걸린거 조회
SYS@ora11g> select * from hr.emp where rowid ='AAAVszAAEAAAAIbAAC';
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
200 Jennifer Whalen
JWHALEN 515.123.4444 17-SEP-03 AD_ASST 9431.79
101 10
select * from hr.emp where rowid ='AAAVszAAEAAAAIbAAC'[sess_1]
- delete table
: emp 테이블에 대해 TM LOCK TYPE이 걸리고 LOCK MOD RX, SX, 3 걸린다.
transaction 대상 행에 대해서는 TX LOCK TYPE 걸리고 LOCK MODE X, 6 걸린다.
HR@ora11g> delete from hr.emp where employee_id = 200;
1 row deleted.[sess_2]
- drop table
: emp 테이블에 대해 TM LOCK TYPE이면서 LOCK MODE는 RX,SX,3 걸려 있어서 오류발생
HR@ora11g> drop table hr.emp purge;
drop table hr.emp purge
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired▶ 2. 특정해에서 unique key, primary key 에 해당하는 데이터를 입력 수정 하는 경우
enq : TX - row lock contention
- 테이블에 unique index 생성
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.[sess_1]
- insert
HR@ora11g> insert into hr.unique_test(id) values(1);
1 row created.[sess_2]
- insert 하면 unique 제약조건 에러가 아니라 행이걸린다.
HR@ora11g> insert into hr.unique_test(id) values(1);
-- WAITINGtransaction 해당하는 행에 대해서 TX LOCK TYPE 이면 LMODE 는 X, 6 수행이 되면서 동일한 값이 다른 세션에서 입력이 된경우 REQUEST S(Share), 4로 대기한다.
select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where event like '%TX%';
---
21 2039 HR 186 enq: TX - row lock contention cuz65hgum0qns c84ynr1u8hbs2[sess_1]
HR@ora11g> commit;
Commit complete.[sess_1]
HR@ora11g> insert into hr.unique_test(id) values(1);
insert into hr.unique_test(id) values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (HR.UNIQUE_TEST_IDX) violated만약에 session 1에서 rollback을 수행 한 경우에는 session 2 수행 된다.
▶ 3. 변경하고자 하는 블록의 ITL에 자신에 해당하는 트랜잭션 엔트리를 등록하고자 하는 경우
enq : TX - allocate ITL entry
● 1. 작업
- initrans 1 maxtrans 2 pctfree 0 으로 테이블생성
: 하지만 maxtrans 값은 255로 고정
SYS@ora11g> create table hr.itl_table(id number, l_name varchar2(1000), f_name varchar2(1000))
initrans 1 maxtrans 2 pctfree 0;
- ini_trans
: block header에 transaction layer에 생성된 transaction slot의 수- maxtrans
: data가 저장되어 있는 free 공간에 생성될 수 있는 transaction slot의 최대수
, 필요할때 free 공간이 있으면 생성해서 사용하다가 transaction 이 종료되면 자동으로 해제된다.- pct_free
: 최초로 블록에 데이터가 저장될때 pctfree값을 제외 시키고 입력한다.
- 이유?
- 기존ROW들의 증가분때문에 남겨놓은 free 영역
만약에 free 공간이 없으면 다른 블록이 이전을 해야한는 문제가 발생(row migration)- mxxtrans 값을 보장하기 위해서
만약에 transaction slot 생성하지 못하면 트랜잭션을 수행한 세션에서는 대기 하는 일이 발생한다.enq : TX - allocate ITL entry
- 확인
SYS@ora11g> select ini_trans, max_trans, pct_free from dba_tables
where table_name = 'ITL_TABLE'; 2
INI_TRANS MAX_TRANS PCT_FREE
---------- ---------- ----------
1 255 0
- insert
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;
SYS@ora11g> commit;SYS@ora11g> select id, rowid, dbms_rowid.rowid_block_number(rowid)
from hr.itl_table order by 3;
-----------------------
5 AAAVs+AAEAAADqjAAA 15011
6 AAAVs+AAEAAADqjAAB 15011
7 AAAVs+AAEAAADqjAAC 15011
8 AAAVs+AAEAAADqjAAD 15011
9 AAAVs+AAEAAADqkAAA 15012
10 AAAVs+AAEAAADqkAAB 15012
1 AAAVs+AAEAAADqnAAA 15015
2 AAAVs+AAEAAADqnAAB 15015
3 AAAVs+AAEAAADqnAAC 15015
4 AAAVs+AAEAAADqnAAD 15015- 3개의 세션에서 update문 실행
[sess_1]
HR@ora11g> update hr.itl_table
set l_name = rpad('y',1000,'y'), f_name = rpad('a',1000,'a')
where id = 1;[sess_2]
HR@ora11g> update hr.itl_table
set l_name = rpad('y',1000,'y'), f_name = rpad('a',1000,'a')
where id = 2;[sess_3]
HR@ora11g> update hr.itl_table
set l_name = rpad('y',1000,'y'), f_name = rpad('a',1000,'a')
where id = 3;- 트랜잭션 event 확인
: 두번째 세션때문에 트랜잭션 슬롯이 걸려있다.
low level lock이 아니다.지금 걸려있는것은 sql_id 로 봐야하고
이미세션이 끝난 blocking_session으로 볼라면 prev_sql_id로 봐야한다.
SYS@ora11g> select sid, serial#, username, blocking_session, event, sql_id, prev_sql_id
from v$session
where event like '%TX%';
---
21 2039 HR 162 enq: TX - allocate ITL entry 2k3c52543mxfb cuz65hgum0qns
- 해결방법
: initrans 값, pct_free 값 조정
- 모든세션 롤백
rollback;● 2. enq: TX - allocate ITL entry 해결방법
SYS@ora11g> select ini_trans, max_trans, pct_free from dba_tables
where table_name = 'ITL_TABLE'; 2
INI_TRANS MAX_TRANS PCT_FREE
---------- ---------- ----------
1 255 0- initrans 값, pct_free 값 조정
하지만, block parameter 값을 수정한 경우 새로운 block부터 적용된디.
기존블록은 그대로 문제가 생길수잇다.
-> 따라서 재배치 작업해줘야한다.
SYS@ora11g> alter table hr.itl_table initrans 2 pctfree 10;
Table altered.
SYS@ora11g> select ini_trans, max_trans, pct_free from dba_tables
where table_name = 'ITL_TABLE'; 2
INI_TRANS MAX_TRANS PCT_FREE
---------- ---------- ----------
2 255 10● 3. 재배치 작업 move
기존 block에도 새로운 block parameter 를 적용하려면 테이블을 재구성해야한다.
SYS@ora11g> alter table hr.itl_table move;
Table altered.👉 하지만
테이블을 재구성하면 기존 행들의 rowid가 바뀐다.
인덱스는 다시 재구성해줘야한다.
● 4. rebuild 작업 해야한다.
온라인중에 index하는것
alter index 인덱스명 rebuild online;■ 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_multiblock_read_count 크기가 128이고
extent가 64k 블록크기가 8k이면
-> 8개씩 2번 올려진다.
SYS@ora11g> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 123full table scan으로 인해서 과도한 multi block I/O 발생시에 나온다.
- db file scattered read wait event 발생 할 수 있다.
따라서 v$seession 모니터링은 필수이다.
불필요한 redo buffer에 기록할것은 (ex)insert)
hint를 작성헤서 no logging모드로 작성- 실행 계획과 통계에 대한 자세한 정보를 제공
HR@ora11g> set autot traceonly exp- 확인
1 - filter("EMPLOYEE_ID"=100)
: 처음부터 끝까지(high water mark)까지 스캔하겠다.
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=2LGWR 할때
controlfile을 보고
current한 그룹
- full table scan 속도를 개선
- 병렬 작업 (parallel 또는 parallel_index 힌트를 사용)
병렬처리하면 data buffer cache를 덜 access한다- db_file_multiblock_read_count 파라미터의 값을 조정하자.
alter session set db_file_multiblock_read_count=128;
- full table scan하는데 프로세서를 2개 사용하여 조회하겠다.
select /*+ full(e) parallel(e,2) */ * from emp e;■ rowid scan
- user rowid, index rowid 를 이용하여 소량의 데이터 검색시 유용하다.
- single block I/O 수행
- db file sequential read wait event 발생할 수 있다.
- hr.emp 테이블의 데이터들의 로우id, 오브젝트id, 파일번호, 블락번호, row슬롯번호
select employee_id, rowid,
dbms_rowid.rowid_object(rowid) as data_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;
-------------------------------------------
198 AAAVtDAAEAAAAIrAAA 88899 4 555 0
199 AAAVtDAAEAAAAIrAAB 88899 4 555 1
200 AAAVtDAAEAAAAIrAAC 88899 4 555 2
201 AAAVtDAAEAAAAIrAAD 88899 4 555 3
202 AAAVtDAAEAAAAIrAAE 88899 4 555 4HR@ora11g> set autot traceonly exp
HR@ora11g> select * from hr.emp where rowid ='AAAVtDAAEAAAAIrAAA';
Execution Plan
----------------------------------------------------------
Plan hash value: 1116584662
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:0
1 |
| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 69 | 1 (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
---■ index scan
참고자료
: https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=gglee0127&logNo=221336088285
▶ 1. index range scan
:B-tree 인덱스의 가장 일반적이고 정상적인 형태의 액세스 방식입니다. 인덱스 루트에서 리프 블록까지 수직적 탐색을 하고, 필요한 범위만큼 수평적 탐색하는 스캔 방식입니다.
인덱스를 Range Scan 하려면, 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 합니다.
인덱스
root block -> branch block -> leaf block 까지
수직으로 탐색한 후 leaf block 에서 필요한 범위만 스캔 방식 (one plus one scan)
- one plus one scan
: root->branch->leaf->block의 rowid로 cursor에 올려놓고 ->leaf 다른것없는지확인
다 latch를 잡는다.
=> 성능이 떨어진다.그래서 오라클에서 해결
: 다시 어차피 latch를 할꺼니깐
buffer pinning 을 잡는다
latch점유시간 줄이고, I/O 성능이 좋아진다.
HR@ora11g> create index hr.emp_idx on hr.emp(employee_id);
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'; 2 3 4
INDEX_NAME UNIQUENES
------------------------------ ---------
COLUMN_NAME
--------------------------------------------------------------------------------
EMP_IDX NONUNIQUE
EMPLOYEE_ID
- index걸린 employee_id를 where절에 두고 scan
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 | 69 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 69 | 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)
- 인덱스가 걸려져있지만 hint를 써서 fullscan으로 조회하기
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 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 69 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)- 인덱스가 걸려져있지만 형변환 함수를 사용해서 full scan으로 조회된다.
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 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 69 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(TO_CHAR("EMPLOYEE_ID"))=100)
- index 유도하는 hint
HR@ora11g> select /*+ index(e emp_idx) */ * from hr.emp e where employee_id =100;
- index range scan으로 유도하는 hint
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 | 69 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 69 | 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)
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 | 69 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 69 | 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)
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'; 2 3 4
INDEX_NAME UNIQUENESS
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
EMP_IDX UNIQUE
EMPLOYEE_ID
HR@ora11g> col column_name format a15
HR@ora11g> 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'; 2 3 4 5
COLUMN_NAME CONSTRAINT_NAME C SEARCH_CONDITION INDEX_NAME
--------------- ------------------------------ - -------------------------------------------------------------------------------- ------------------------------
LAST_NAME SYS_C0011586 C "LAST_NAME" IS NOT NULL
EMAIL SYS_C0011587 C "EMAIL" IS NOT NULL
HIRE_DATE SYS_C0011588 C "HIRE_DATE" IS NOT NULL
JOB_ID SYS_C0011589 C "JOB_ID" IS NOT NULL
EMPLOYEE_ID EMP_ID_PK P EMP_IDX▶ 2. index unique scan
- 컬럼에 유일한 값으로 인덱스가 생성된 경우 사용된다.
- 비교연산자는
=사용할때만 사용된다 - unique index라고 하더라도 범위스캔을 수행할 경우 index range scan 으로 수행된다.
: Index Unique Scan은 수직적 탐색으로만 데이터를 찾는 스캔 방식입니다. Unique 인덱스를 '=' 조건으로 탐색하는 경우에 동작합니다. Unique 인덱스가 존재하는 컬럼은 중복 값이 존재하지 않습니다. 따라서 동일('=')하다는 조건으로 검색할 때, 데이터를 한 건 찾고 나서는 더 이상 탐색할 필요가 없습니다.
: Unique 인덱스라고 해도 범위(between, >, <, like) 조건으로 검색하게 되면, 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 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 69 | 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
: root ~ leaf 까지 쿼리를 분리해서 실행계획을 만든다.
ex) where emp_id in (1,100) 이면 2개만들어진다.
👉 loop ~ leaf 까지 너무길면 실행계획이 range scan 나온다
이때 => index를 재배치(rebuild) 해야한다.
- 범위스캔
INLIST ITERATOR
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 | 138 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 138 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_IDX | 2 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"=100 OR "EMPLOYEE_ID"=200)- in 연산자를 쓰더라도 성능을 위하여 내부적으로 unique scan을 하여 아래의 쿼리를 실행하는것
HR@ora11g> select * from hr.emp where employee_id = 100
union all
select * from hr.emp where employee_id = 200; 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 1345714510
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 138 | 2 (0)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 69 | 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 | 69 | 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)
- union을 쓰면 중복을 다시 SORT해야하므로 실행계획의 한단계가 추가된다.
HR@ora11g> select * from hr.emp where employee_id = 100
union
select * from hr.emp where employee_id = 200; 2 3
Execution Plan
----------------------------------------------------------
Plan hash value: 28680880
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 138 | 4 (50)| 00:00:01 |
| 1 | SORT UNIQUE | | 2 | 138 | 4 (50)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 69 | 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 | 69 | 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)
- last_name 컬럼 index 생성
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'; 2 3 4
INDEX_NAME UNIQUENESS COLUMN_NAME
------------------------------ ------------------------------ ---------------
EMP_IDX UNIQUE EMPLOYEE_ID
EMP_NAME_IDX NONUNIQUE LAST_NAME
- last_name select 실행계획보기 (range scan)
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 | | 1 | 69 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 69 | 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')
- 인덱스가 걸린컬럼과 아닌컬럼과 select
filter : full table scan이 된다.
=> 따라서 자주사용되는 컬럼들이면 조합인덱스를 만들자
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 | 69 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 69 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_NAME_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FIRST_NAME"='Steven')
2 - access("LAST_NAME"='KING')▶ 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);
- 인덱스확인
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'; 2 3 4
INDEX_NAME UNIQUENESS 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 | 69 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 69 | 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')▶ 5. index full scan
: Index Full Scan은 수직적 탐색 없이 인덱스 리프 블록 처음부터 끝까지 수평적으로 탐색하는 방식입니다. 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택됩니다.
테이블(여러개의 블록)보다 상대적으로 인덱스가 작은 블록수로 만들어졌으므로 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 | 1605 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | EMP_NAME_IDX | 107 | 1605 | 1 (0)| 00:00:01 |- count select할때 index로 찾는것이 블록의수가 작으므로 효율적이다.
- 전체건수를 셀때
EMP_IDX : pk가 걸려있는 I/O를 사용한다
- 전체건수를 셀때
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 |
--------------------------------------------------------------------
- 전체건수를 select할때 사용
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 |
-------------------------------------------------------------------------
index full scan
- single block i/o
- 정렬보장
- 속도 느림
- db file sequential read
index fast full scan
- multi block i/o
- 정렬 안됨
- 속도 빠름
- db file scattered read
👉 index full scan 보다 index fast full scan이 더 빠른이유?
- multi block i/o
- db_file_multiblock_read_count
- 병렬처리가 가능
▶ 6. index fast full scan
HR@ora11g> select /*+ index_ffs(e emp_idx) parallel_index(e, emp_idx, 2) */ count(*) from hr.emp e;
Execution Plan
----------------------------------------------------------
Plan hash value: 518463889
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 107 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | INDEX FAST FULL SCAN| EMP_IDX | 107 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------
DB 공부를 하고 있는 사람입니다. 글을 읽어주셔서 감사하고 더 좋은 글을 쓰기 위해 노력하겠습니다 :)