SQL 아키텍처
## Error in loadNamespace(name): there is no package called 'webshot'SQL 처리 단계
-
Parse
1.1 SYNTAX, SEMETIC, Privilege 체크
1.2 Soft Parsing
1.3 Hard Parsing -
Bind
-
Execute
-
Fetch
PARSE
실행계획이 만들어지는 단계
-
Syntax, Sementic, Previlige
Query문의 문법, 의미, 권한을 체크한다. Syntax는 Server Process의 엔진에서 검사를 하지만 Sementic과 Previlige는 Data dictionary table에 있는 dictionary 정보가 필요하다. 그래서 Data dictionary cache를 바라보고 있으며 어떤 User인지, 어떤 table을 찾는지, 어떤 제약 조건이 걸린 column인지 등 문법을 제외한 모든 것을 확인한다.
## Error in loadNamespace(name): there is no package called 'webshot'-
Soft Parsing
동일한 SQL문이 Shared Pool 안의 Library cache에 LCO(Library cahce object)가 있는지 조회한다. 있다면 LCO를 sharing하고, 없다면 Hard parsing 단계로 넘어간다.
## Error in loadNamespace(name): there is no package called 'webshot'-
Hard Parsing
Soft parsing이 실패하면 Hard parsing이 실행된다. 이때 LCO를 만들기 위한 메모리 할당과 실행 계획이 생성된다.
## Error in loadNamespace(name): there is no package called 'webshot'BIND
쿼리에 변수처리가 되어 있으면 변수에 실제값을 입력하는 단계이다. 쿼리에 변수처리를 하는 이유는 soft parsing을 유도하기 위해서이다. 하지만 변수처리를 하는 것이 좋지 않은 경우(FULL SCAN이 더 효율적인 경우)도 있으니 적절히 사용하는 것이 좋다.
- 참고: 변수처리를 하더라도 optimizer가 histogram을 보고 자동으로 scan을 하는 경우가 있다.(Adapted Cursor Sharing)
EXECUTE
실행계획의 무효화가 발생하지 않는 마지막 처리 단계이다. DML문은 Fetch단계로 넘어가지 않고 이 단계에서 처리를 마친다.
FETCH
찾고자하는 ROW가 존재하는 BLOCK이 Data buffer cache에 있는지를 실행계획을 통해 찾는다.
## Error in loadNamespace(name): there is no package called 'webshot'실행계획이 무효화 될 때는 다음과 같다.
- DDL 작업 시
- INDEX 생성 시
- 통계
실행계획
-----HR SESSION-----
--sample table 생성
CREATE TABLE emp
AS SELECT * FROM employees;
select * from emp where employee_id = 100;
-----DBA SESSION-----
--shared pool cache 삭제
ALTER SYSTEM FLUSH SHARED_POOL;
--sql id 확인
SELECT * FROM v$sql --Shared pool안의 library cache
WHERE sql_text like 'select * from emp%' --LCO에서 이와 같은 동일의 쿼리문을 찾음
AND sql_text NOT LIKE '%v$sql%';
--parse_calls: 수가 높을수록 자주 쓴다고 생각할 수 있음
--object_status: 실행계획을 무효/유효화가 됐는지 볼 수 있음
--실행계획 확인1
SELECT *
FROM v$sql_plan
WHERE sql_id ='0t028xxgcphg0'; --v$sql에서 찾은 sql_id로 검색함
--실행계획 확인2
SELECT * FROM TABLE(dbms_xplan.display_cursor('0t028xxgcphg0'));
dbms_xplan package를 사용하여 실행계획을 보는 것이 보기 편하다. 아래는 질의 결과이다. 이 실행계획의 cost는 Parse단계에서의 실행계획(cpu 사용량, i/o발생 등 제외)이니 실제 cost는 아니다.
Plan hash value: 1252232671
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_ID_PK | 1 | | 0 (0)| |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
- 들여쓰기가 된 순으로 먼저 해석해야한다.
- predicate information은 2가지 경우가 있다.
- filter 술어: rowid를 찾지 못해 block을 바로 찾을수가 없다. -> 보통 full scan 사용
- access 술어: rowid를 알고 있어서 어느 block에 있는지 안다. -> rowid scan 사용
EXPLAIN PLAN (실행계획 확인)
>Syntax
EXPLAIN PLAN FOR (쿼리문)
SELECT * FROM TABLE(dbms_xplan.display(null,null,'typical')); --가장 보기 좋음
SELECT * FROM TABLE(dbms_xplan.display(null,null,'basic'));
SELECT * FROM plan_table;
Explain Plan이란?
- Optimizer가 SQL문 실행에 사용하는 실행계획 정보를 확인할 수 있음
- plan_table이 필요하고 이 table에 저장을 해야함
SQL> @%ORACLE_HOME%\rdbms\admin\utlxplan.sql
-- 10g Version부터는 자동 설치가 되어있다.
plan_table을 확인하려면 아래 쿼리로 검색하자.
SELECT *
FROM all_synonyms
WHERE synonym_name = 'PLAN_TABLE';
통계수집
실행계획을 만들 때 통계정보가 필요하다. 그 통계정보는 수동으로 수집해야 한다. 없을 시에는 oracle이 자동으로 dynamic sampling을 해서 실행계획을 만들기 때문에 통계정보를 수집해야 하는 경우가 있다.
--자신의 통계정보 확인
SELECT num_rows, blocks, avg_row_len --한행의 평균 BYTE 값
FROM user_tables
WHERE table_name = 'EMP';
Block, extent, Segment, Multiblock read count 등의 정보를 확인하려면?
SELECT * FROM dba_segments WHERE segment_name = 'EMP';
SELECT * FROM user_segments WHERE segment_name = 'EMP';
--block, extent, segment 정보 확인
SHOW PARAMETER db_file_multiblock_read_count
--multiblock을 읽을 때 한번에 처리할 수 있는 숫자
--예시로 만든 emp테이블은 extent에 종속되어서 8개밖에 메모리로 읽지 못함
--현재 session에 read_count를 변경하려면?
ALTER SESSION SET db_file_multiblock_read_count = 128;
통계 수집 권한 확인
--통계 수집을 할 수 있는 권한 확인
SELECT * FROM user_tab_privs;
--결과의 DBMS_STATS은 통계수집을 할 수 있는 PACKAGE
--통계 수집
EXEC dbms_stats.gather_table_stats('hr','emp')
--hr: 소유자 이름 / emp: gathering해야하는 table
--즉시 무효화 옵션
EXEC dbms_stats.gather_table_stats('hr','emp',no_invalidate=>False)
통계수집을 했을 때 latch 기능이 실행되고 실행계획이 무효화가 되지만 바로 되지는 않는다. 그래서 no_invalidate를 사용하여 즉시 무효화를 시킬 수 있다.
통계정보 확인
실제 수행한 실행계획을 처리한 블락의 수를 확인하기 위해 dbms_xplan.display_cursor 를 사용하기 위한 5가지 권한
--DBA SESSION
GRANT SELECT ON v_$session TO hr; --session 모니터링
GRANT SELECT ON v_$sql TO hr; --sql문장을 볼 수 있는 view
GRANT SELECT ON v_$sql_plan TO hr; --plan을 보는 view
GRANT SELECT ON v_$sql_plan_statistics TO hr; --통계정보를 보는 view
GRANT SELECT ON v_$sql_plan_statistics_all TO hr; --모든 정보를 보는 view
--USER SESSION
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE employee_id = 100;
SELECT *
FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
>결과
SQL_ID d7xxw2jwtsp9z, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM employees WHERE
employee_id = 100
Plan hash value: 1833546154
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
--Root하고 leaf가 같은 block 안에 있음을 알 수 있다.
- Starts: 수행한 횟수
- E-Rows: 예측 ROW수
- A-Rows: 실제 ROW수
- A-Time: 수행한 시간
- Buffers: 메모리에서 찾은 블락 i/o수(들여쓰기 된 순으로 누적개념)
- Reads: Physical하게 읽은 블락 i/o(누적값이나 Buffer에 포함됌)
SQL_ID 6nwjj4da08t0k, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM emp WHERE department_id =
50
Plan hash value: 919097248
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 45 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | EMP_DEPT_IDX | 1 | 10 | 45 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
위 결과에서 보면 leaf block은 45번 건들어야 되는데 Buffer는 그만큼 나오지 않았다.(Buffer pinning 발생했음을 알 수 있다.) 또한 block에서 실제값을 찾는 i/o가 줄어든 이유는 찾고자 하는 값들이 같은 block에 있는 경우 그 block에 buffer pinning을 찍어서 i/o가 발생하지 않는다. 그래서 1번의 leaf block i/o와 2번의 실제 값을 찾는 block i/o가 발생했다. buffer pinning이 잘 돌아가는 경우 clustering factor가 좋다고 한다.
Buffer Pinning
Index scan에서 한번 tree 구조를 거치거나 leaf block에서 연속된 값이 같은 block에 있을 때 pinned해서 I/O를 발생시키지 않게 한다. buffer pinning이 걸린 block은 Hash 함수에 던져서 hash 값을 찾는 수고를 방지한다.
만약 Index range scan했을 때 buffer값이 row값과 비슷하게 나온다면(clustring factor가 나쁘다면) full scan으로 유도하는 것이 좋다. 또한 table의 row수와 clustering factor가 같다면 random i/o가 발생하여 range scan은 좋지 않다.(참고 INDEX RANGE SCAN)
SELECT /*+ gather_plan_statistics
full(e) */ *
FROM emp e
WHERE department_id = 50;
SELECT *
FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
Clustering factor
index (range) scan 하는 동안 access 하게 되는 table의 block 수. clustering factor를 좋게하려면 CTAS 로 테이블을 다시 만들어야 한다. 예를 들어 clustering factor가 좋지 않은 column을 좋게하고 싶다면 좋지 않은 해당 column을 기준으로 테이블을 새로 생성해야 한다.
--샘플테이블 생성
CREATE TABLE c_table
AS SELECT * FROM all_objects
ORDER BY object_id;
SELECT * FROM c_table;
--인덱스 설정
CREATE INDEX c_obj_idx ON c_table(object_id);
CREATE INDEX c_obj_name_idx ON c_table(object_name);
--통계수집
EXECUTE dbms_stats.gather_table_stats('hr','c_table');
--통계정보 확인
SELECT num_rows, blocks FROM user_tables WHERE table_name = 'C_TABLE';
--clustering factor 확인
SELECT index_name, leaf_blocks, clustering_factor
FROM user_indexes
WHERE table_name = 'C_TABLE';
>결과
INDEX_NAME LEAF_BLOCKS CLUSTERING_FACTOR
------------------- ---------------- ----------------------
C_OBJ_IDX 16 96
C_OBJ_NAME_IDX 31 3039
C_OBJ_IDX의 경우 block의 수와 비슷하게 나와 index range scan이 효율적임을 알 수 있다. 반대로 C_OBJ_NAME_IDX의 경우는 table의 row의 수와 비슷하게 나와 index scan보다 full scan이 좋다는 것을 알 수 있다. 이제 실제 clustering facter가 어떻게 적용되는지 알아보자.
-- c_obj_idx로 index scan
SELECT /*+ gather_plan_statistics index(c c_obj_idx) */ COUNT(*)
FROM c_table c
WHERE object_id >= 0
AND object_name >= ' ';
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
>결과
SQL_ID f5bhfzkawr3fp, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics index(c c_obj_idx) */ COUNT(*) FROM
c_table c WHERE object_id >= 0 AND object_name >= ' '
Plan hash value: 1213134236
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 113 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 113 |
|* 2 | TABLE ACCESS BY INDEX ROWID| C_TABLE | 1 | 7330 | 7330 |00:00:00.01 | 113 |
|* 3 | INDEX RANGE SCAN | C_OBJ_IDX | 1 | 7330 | 7330 |00:00:00.01 | 17 |
----------------------------------------------------------------------------------------------------
table access한 i/o를 계산하면 96(113-17)이 나온다. 위의 clustering factor와 같음을 알 수 있다. 이제 위에서 clustering factor가 좋지 않은 c_obj_name_idx 인덱스로 scan을 해보자
-- c_obj_name_idx로 index scan
SELECT /*+ gather_plan_statistics index(c c_obj_name_idx) */ COUNT(*)
FROM c_table c
WHERE object_id >= 0
AND object_name >= ' ';
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
>결과
SQL_ID 44hs410r9xurp, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics index(c c_obj_name_idx) */ COUNT(*)
FROM c_table c WHERE object_id >= 0 AND object_name >= ' '
Plan hash value: 586128974
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3071 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3071 |
|* 2 | TABLE ACCESS BY INDEX ROWID| C_TABLE | 1 | 7330 | 7330 |00:00:00.01 | 3071 |
|* 3 | INDEX RANGE SCAN | C_OBJ_NAME_IDX | 1 | 7330 | 7330 |00:00:00.01 | 32 |
table access에서 발생한 i/o는 3039(3071-32)와 위의 clustering factor가 같다. 이를 통해 optimizer가 custering factor를 바라보고 실행계획을 만든다라는 것을 알 수 있다.
HINT
/*+ 힌트 이름 */ 으로 사용한다.
FULL SCAN HINT
SELECT /*+ full(e) */ * FROM emp e; -- full scan으로 유도
SELECT /*+ full(e) */ employee_id, last_name FROM emp e;
INDEX SCAN HINT
SELECT /*+ index(e emp_dept_dix) */ *
FROM emp e WHERE department_id =10; --index scan유도
SELECT /*+ index_rs(e emp_dept_dix) */ *
FROM emp e WHERE department_id =10; --range scan으로 유도
SELECT /*+ index_ss(e emp_last_first_idx) */ *
FROM emp e WHERE first_name = 'Steven'; --skip scan으로 유도
SELECT /*+ index_ffs(e emp_id_pk) */ COUNT(*)
FROM emp e; --fast full scan으로 유도
--fast full scan으로 가장 빠르게 처리하려면?
EXPLAIN PLAN FOR
SELECT /*+ index_ffs(e emp_id_pk)
parallel_index(e, emp_id_pk,2) */
count(*)
FROM emp e; --병렬처리(full scan만 가능)
SELECT /*+ index_desc(e emp_id_pk) */ employee_id
FROM emp e; --INDEX 정렬
테이블 이름이 길 경우 테이블에 별칭을 사용하여 쓰도록 하자. 인덱스 명 또한 잘 찾아서 제대로 사용하자
병렬처리 HINT
SELECT /*+ full(e) parallel(e,2) */ * FROM emp e;
parallel의 용법
- 나누어서 처리. 2개의 프로세서(cpu)를 띄워서 반씩 나눠서 block을 올린다.
- Direct read. disk에서 data buffer cache를 거치지 않고 바로 cursor로 가서 active set을 만든다.
- 병렬처리는 무조건 multiblock i/o를 발생시킬 때 사용(full table scan/fast full scan)
만약 힌트를 사용하여 full스캔을 유도했는데 무시될 경우, 형을 바꾸는 함수를 바꿔서 full 스캔으로 설정한다.
실행계획 HINT
--실행계획을 분리시켜서 수행
SELECT /*+ use_concat */ *
FROM employees
WHERE job_id = 'IT_PROG'
OR department_id = 20;
--실행계획을 분리하지 말고 하나로 수행
SELECT /*+ no_expand */ *
FROM employees
WHERE job_id = 'IT_PROG'
OR department_id = 20;
INDEX
기억하지 못하는 rowid를 대신 기억해준다. Primary key와 Unique 제약조건을 설정할 경우 인덱스는 unique index로 자동으로 생성된다. 인덱스의 이름은 제약조건 이름으로 따라간다. 또한 index를 설정한 컬럼에 NULL값이 있다면 그 데이터는 index가 걸리지 않는다. 아래는 index를 확인할 수 있는 쿼리문이니 Tuning을 위해서 기억해두도록 하자.
>Syntax
CREATE INDEX 인덱스이름
ON 유저.테이블이름(해당 컬럼)
CREATE UNIQUE INDEX 인덱스이름
ON 유저.테이블이름(해당 컬럼)
SELECT * FROM user_ind_columns WHERE table_name = 'EMP'; --컬럼에 index 걸려있는지 확인
SELECT * FROM user_indexes WHERE table_name = 'EMP'; --unique을 조건 확인할 수 있음
조인을 써서 한번에 볼 수 있다.
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';
인덱스를 컬럼에 설정하고 걸려있는지 확인해보자.
ALTER TABLE emp ADD CONSTRAINT emp_id_pk primary key(employee_id);
SELECT * FROM user_ind_columns WHERE table_name = 'EMP';
SELECT * FROM user_indexes WHERE table_name = 'EMP'
EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM emp;
SELECT * FROM table(dbms_xplan.display(null,null,'typical'));
왜 emp table을 full scan하지 않고 특정 컬럼의 index로 scan을 하는가?
:emp table은 block이 많으니 건수를 세기엔 비용이 많이 든다. 그럴바에 작은 block을 쓰는 index에 not null 제약조건이 걸려있는 아무 컬럼을 선택해서 건수를 센다.
함수기반 INDEX
함수를 사용해서 인덱스를 타게하고 싶을 때 사용한다. 예를 들어, 입력 단위에서 대소문자 구분을 하지 않고 입력했을 때 효과를 볼 수 있다. 문장을 튜닝할 때 되도록이면 문장을 재구성해서 사용하고 재구성 할 수 없을 시에 함수기반의 index를 사용하자.
SELECT /*+ gather_plan_statistics */ COUNT(*)
FROM emp
WHERE lower(last_name) = 'king';
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
>결과
SQL_ID dpa44y9za2xwm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM emp WHERE
lower(last_name) = 'king'
Plan hash value: 3476320319
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 32 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 32 |
|* 2 | INDEX FAST FULL SCAN| EMP_IND_LAST_NAME | 1 | 107 | 200 |00:00:00.01 | 32 |
-----------------------------------------------------------------------------------------------------
함수 기반 index를 사용해보자.
CREATE INDEX emp_lower_name_idx ON emp(lower(last_name));
SELECT /*+ gather_plan_statistics */ COUNT(*)
FROM emp
WHERE lower(last_name) = 'king';
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
>결과
SQL_ID dpa44y9za2xwm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM emp WHERE
lower(last_name) = 'king'
Plan hash value: 2946388071
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN| EMP_LOWER_NAME_IDX | 1 | 107 | 200 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------
다음 쿼리문으로 함수기반의 index를 확인할 수 있다.
select * from user_indexes where table_name = 'EMP';
select * from user_ind_columns where table_name = 'EMP'; --함수기반 index는 가상컬럼을 만들어서 이름이 특이함
select * from user_ind_expressions where table_name = 'EMP'; --함수기반 index의 표현식을 확인하는 방법
INDEX ORGANIZATED TABLE (인덱스)
CREATE TABLE emp_non_iot
AS SELECT rownum emp_id, last_name, first_name, job_id, hire_date, salary, commission_pct, email, department_id
FROM employees,
(SELECT rownum emp_id FROM dual CONNECT BY LEVEL <= 10000)
ORDER BY dbms_random.value;
EXEC dbms_stats.gather_table_stats('hr','emp_non_iot')
SELECT num_rows, blocks
FROM user_tables
WHERE table_name = 'EMP_NON_IOT';
ALTER TABLE emp_non_iot
ADD CONSTRAINT emp_non_iot_pk PRIMARY KEY(emp_id);
SELECT index_name, blevel, leaf_blocks, clustering_factor
FROM user_indexes
WHERE table_name = 'EMP_NON_IOT';
--blevel: leaf block까지 도달하기에 거치는 block 수
--clustering factor가 block수와 비슷하면 좋다고 할 수 있다. -> index scan유도 (row의 수와 같으면 좋지 않음 -> full scan 유도)
ALTER SESSION SET statistics_level = all;
SELECT * FROM emp_non_iot WHERE emp_id = 1;
SELECT *
FROM emp_non_iot
WHERE emp_id BETWEEN 1 AND 100;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
--1~100번사원을 찾는데 clustering factor가 나빠서 table access시 ramdom i/o가 높아짐 -> 어짜피 pk값으로만 조회를 하니까 인덱스 구성테이블을 만들자
--인덱스 구성테이블(table을 index화해서 만든 테이블)
--인덱스에서 해당 데이터를 찾으면 table access를 하지 않고 index table에서 데이터를 추출함
--pk로 조회를 하는 테이블만 인덱스 구성테이블을 만들자
CREATE TABLE emp_Iot
(emp_id, last_name, first_name, job_id, hire_date, salary, commission_pct, email, department_id,
CONSTRAINT emp_iot_pk PRIMARY KEY(emp_id)) --pk
ORGANIZATION INDEX --index 구성테이블을 만드는 형태
AS SELECT * FROM emp_non_iot;
EXEC dbms_stats.gather_table_stats('hr','emp_iot')
SELECT num_rows, blocks
FROM user_tables
WHERE table_name = 'EMP_IOT';
--index 성격이여서 block이 없음
SELECT index_name, index_type, blevel, leaf_blocks, clustering_factor
FROM user_indexes
WHERE table_name = 'EMP_IOT';
--iot_top: 인덱스 구성테이블
SELECT * FROM emp_iot WHERE emp_id = 1;
SELECT *
FROM emp_iot
WHERE emp_id BETWEEN 1 AND 100;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
SCAN 방식
SCAN방식을 알려면 실행계획을 확인해야한다.
SELECT * FROM TABLE(dbms_xplan.display(null,null,'typical')); --자주 사용
SELECT * FROM TABLE(dbms_xplan.display(null,null,'basic'));
SELECT * FROM plan_table;
만약 컬럼이 non-unique index일 경우 통계수집을 하여서 full scan을 사용할지 index scan을 사용할지 선택해서 사용하도록 하자.
1. FULL TABLE SCAN
- 많은 양의 데이터를 검색할 때 유용하다.
- Multi block i/o 발생함
- Multi block은 db_file_multiblock_read_count에 설정되는 값을 기준으로 여러 block을 한번에 메모리로 읽을 수 있다.
- 병렬처리를 할 수 있다.
2. ROWID SCAN
- By user rowid, By index rowid를 이용하여 소량의 데이터를 검색할 때 사용
- Single block i/o가 발생
2.1 BY USER ROWID
직접 rowid를 검색하여 해당 데이터를 검색한다. rowid는 총 18자리로 이루어져 있으며 obj id(6)/file id(3)/block id(6)/row slot(3) 로 구성되어 있다.
>Ex
SELECT rowid, employee_id
FROM emp;
SELECT *
FROM emp
WHERE rowid = 'AAAFBPAAEAAAAIjAAF';
2.2 BY INDEX SCAN
- 기억하지 못하는 rowid를 기억해주는 index로 값을 찾는 방식.
INDEX SCAN은 SINGLE BLOCK I/O를 발생시킨다. 속도는 MULTI BLOCK I/O보다 느리지만 정렬이 보장되어서 순서대로 화면에 뿌려주길 원한다면 index full scan을 쓰는 것이 좋다. 하지만 건수를 세는 거라면 index fast full scan을 사용하자.
B * Tree 구조
- leaf block을 빨리 찾아가기 위해 만든 B * Tree 구조를 사용
- 원하는 block을 찾아 row단위로 cursor에 저장시켜 active set을 만듦
- Tree 구조에서 block들을 Shared pool에서 관리하는 DBA(Data Block Address)로 찾아감
1. INDEX UNIQUE SCAN
- 컬럼에 유일한 값으로 인덱스가 구성되어 있을 때
- 비교 연산자는 =(euql)을 사용할 때
--index 생성
ALTER TABLE emp ADD CONSTRAINT emp_id_pk PRIMARY KEY(employee_id); --pk조건
EXPLAIN PLAN FOR SELECT * FROM emp where employee_id = 100; --equl을 사용
SELECT * FROM TABLE(dbms_xplan.display(null,null,'typical'));
2. INDEX RANGE SCAN (INDEX FULL SCAN)
- 인덱스의 leaf block에서 필요한 범위만 스캔하는 방식
- one plus one scan 방식
- 비교연산자가 =(equl) 일지라도, 인덱스의 조건이 non-unique일 때 실행됨
아래 쿼리를 비교하여 full scan과 range scan의 차이를 알아보자
EXPLAIN PLAN FOR SELECT * FROM emp where employee_id >= 100;
SELECT * FROM TABLE(dbms_xplan.display(null,null,'typical'));
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 107 | 7383 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID">=100)
다른 컬럼의 정보까지 알아야 하니 data가 다 들어 있는 TABLE에 들어가서 찾아야 된다. 그리고 single block i/o가 발생하는 RANGE SCAN은 EMP 테이블의 ROW만큼 I/O가 발생되니 FULL SCAN이 성능이 더 좋다.
EXPLAIN PLAN FOR SELECT employee_id FROM emp where employee_id >= 100;
SELECT * FROM TABLE(dbms_xplan.display(null,null,'typical'));
Plan hash value: 659100359
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 428 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| EMP_ID_PK | 107 | 428 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPLOYEE_ID">=100)
key column만 확인하면 되서 table에 들어가서 데이터를 찾지 않아도 된다. 그래서 index 스캔으로 끝내는게 좋기 때문에 range scan이 효율적이다.
EXPLAIN PLAN FOR SELECT * FROM emp where department_id = 10;
SELECT * FROM TABLE(dbms_xplan.display(null,null,'typical')); --full scan
--인덱스 생성
CREATE INDEX emp_dept_idx
ON emp(department_id);
EXPLAIN PLAN FOR SELECT * FROM emp where department_id = 10;
SELECT * FROM TABLE(dbms_xplan.display(null,null,'typical')); --range scan
위 쿼리에서 department_id에 index조건을 생성했다. emp 테이블에서 department_id는 유일한 값만 들어가는 컬럼이 아니기 때문에 Non-unique 조건으로 index가 생성되었음을 알 수 있다. 그래서 Range scan으로 실행계획이 만들어졌다.
INDEX RANGE SCAN DESCENDING
EXPLAIN PLAN FOR
SELECT *
FROM emp
WHERE employee_id > 0
ORDER BY employee_id desc;
SELECT * FROM table(dbms_xplan.display(null,null,'typical'));
SELECT /*+ index_desc(e emp_id_pk) */ employee_id
FROM emp e;
위를 활용해서 최대값/최소값을 찾아보자. index는 오름차순 정렬이 되어있으니 내림차순으로 정렬해서 1번째 값을 뽑아낸다. max로 뽑아내게 되면 모든 값을 비교하지만 max값을 쓴 컬럼에 index가 걸려져있다면 index full scan(min/max)로 자동으로 돌아간다.
SELECT /*+ index_desc(e emp_id_pk) */ employee_id
FROM emp e
WHERE rownum <= 1;
SELECT /*+ index_asc(e emp_id_pk) */ employee_id
FROM emp e
WHERE rownum <= 1;
EXPLAIN PLAN FOR
SELECT MAX(employee_id) FROM emp;
>결과
Plan hash value: 2881024856
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| EMP_ID_PK | 1 | 4 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
하지만 min/max를 같이 사용한다면 index full scan이 이루어져 필요없는 leaf block까지 체크를한다. 그래서 set 연산자를 사용해서 index full scan(min/max)로 유도한다.
EXPLAIN PLAN FOR
SELECT MAX(employee_id), MIN(employee_id) FROM emp;
--아래로 바꾼다.
EXPLAIN PLAN FOR
SELECT MAX(employee_id) FROM emp
UNION ALL
SELECT MIN(employee_id) FROM emp;
3. INLIST ITERATOR
- IN연산자와 OR연산자를 사용했을 때 발생
- 인덱스를 반복하는 실행계획 (Root-Branch-Leaf tree구조를 반복해서 수행)
- 값의 범위가 떨어져 있을 때 사용 (연속된 값일 경우 사용하면 좋지 않음)
- 연속된 값을 inlist iterator하면 leaf block을 계속 건들어야해서 range scan가 더 효율적이다. 왜냐하면 range scan은 buffer pinning이 발생하여 leaf block을 찾는 i/o가 발생하지 않는다.
EXPLAIN PLAN FOR SELECT * FROM emp where employee_id IN (100,200);
/*
SELECT * FROM emp where employee_id = 100
UNION ALL
SELECT * FROM emp where employee_id = 200;
IN연산자의 실행계획을 생각할 시 SET OPERATOR를 사용했다고 생각하자.
*/
--실행계획 확인
SELECT * FROM TABLE(dbms_xplan.display(null,null,'typical'));
Plan hash value: 852545703
------------------------------------------------------------------------------------------
| 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_ID_PK | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"=100 OR "EMPLOYEE_ID"=200)
만약 연속된 값이라면 아래와 같이 튜닝을 하자
EXPLAIN PLAN FOR SELECT * FROM emp where employee_id IN (100,101,102);
--위와 같이 연속된 값일 경우에 range scan이 훨씬 효율적이므로 BETWEEN연산자를 사용하자
EXPLAIN PLAN FOR SELECT * FROM emp where employee_id BETWEEN 100 AND 102;
SELECT * FROM TABLE(dbms_xplan.display(null,null,'typical'));
4. INDEX SKIP SCAN
선행컬럼이 중복성이 많을 경우, 후행컬럼은 중복성이 적은 데이터일 수록 SKIP SCAN이 잘 돌아간다. 중복성 체크는 DISTINCT()를 사용해서 체크 할 수 있다.
CREATE INDEX emp_last_first_idx ON emp(last_name, first_name);
SELECT *
FROM user_ind_columns
WHERE table_name = 'EMP';
EXPLAIN PLAN FOR
SELECT *
FROM emp
WHERE first_name = 'Steven';
SELECT * FROM table(dbms_xplan.display(null,null,'typical'));
Plan hash value: 1123681716
--------------------------------------------------------------------------------------------------
| 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 SKIP SCAN | EMP_LAST_FIRST_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FIRST_NAME"='Steven')
filter("FIRST_NAME"='Steven')
5. INDEX FAST FULL SCAN
INDEX SCAN이지만 MULTI BLOCK I/O가 발생한다. 그래서 정렬은 발생하지 않고 속도가 빠르다.
>Syntax
/*+ index_ffs(테이블명 또는 테이블 별칭 인덱스) */