■ B-Tree 구조 인덱스
1. 정의
- 우리가 자주 사용하는 index의 일반적인 형태 (root-branch-leaf)
- Balanced tree 의 약자
- Binary tree 의 약자
- 즉, Balanced binary Search Tree 구조이다. (균형 이진 탐색 트리)
- 테이블이 크고 대부분의 쿼리가 테이블에서 2~4% 미만의 행을 검색할 때 유용하다.
2. B-Tree 생성기준
1) where 조건절에 자주 사용되는 컬럼
2) 유일키값으로 구성되어 있는 컬럼
3) 열의 null값은 제외하고 인덱스 생성해야 함 (null은 스캔 안함)
4) order by 절에 자주 사용되는 컬럼
- 인덱스가 생성될 때 정렬되어서 생성되기 때문에 min, max 함수를 사용할때 cost가 적다
■ Bitmap 구조 인덱스
1. 정의
- where 조건절에 자주 사용되는 컬럼들 중에 중복성이 많은 컬럼 선정
2. Bitmap 생성기준
1) where 조건절에 자주 사용되는 컬럼 중 중복성이 많은 컬럼
2) 열의 null값도 포함하고 인덱스 생성해야 함 (null도 스캔이 진행)
3. 생성 방법
create bitmap index 인덱스이름 on 테이블(컬럼);
4. b-tree와 bitmap차이점
| b-tree | bitmap |
| 유일키 값으로 데이터 있는 컬럼 | 중복성이 많은 컬럼 |
| DML 비용이 저렴 (row level lock만) |
DML 비용이 큼 (row level lock +index segment level lock) |
| and 연산자 효율적 or 연산자 비효율적 |
and 연산자, or 연산자 모두 효율적 |
| null을 포함하지 않음 | null을 포함 |
| OLTP 업무에 유용 (Online Transaction Processing) |
DW(Data Warehouse), DSS(Decision Support System)업무에 유용 |
# b-tree
- key column, rowid로 구성
# bitmap
- 남, 여, null 로 3개 index를 생성 / 시작 rowid / 끝 rowid / bitmap(1,0)으로 구성
bitmap 최대단점:
1) 유일키 컬럼은 안좋음
2) bitmap의 rowlock을 걸지 못함 따라서 segment단위로 lock을 걸기때문에 DML작업이 빈번하면 비효율적(wait이 많아짐)이라 조회성으로 사용해야함
[예시]
table 설문
column 성별 결혼 자동차
시작rowid 남 기혼 y
여 미혼 y
남 미혼 n
남 기혼 n
끝 rowid null 기혼 y
| 성별 | 시작rowid | 끝rowid | bitmap |
| 남 | #1 | #100 | 10110 |
| 여 | #1 | #100 | 01000 |
| null | #1 | #100 | 00001 |
select *
from 설문t
where 성별 is null;
000001 여기서 1 걸리는 것만 출력 !
| 결혼여부 | 시작 rowid | 끝 rowid | bitmap |
| 기혼 | #1 | #100 | 10011 |
| 미혼 | #1 | #100 | 01100 |
그럼
select *
from 설문 table
where 성별 = '남'
and 결혼여부 = '기혼';
을 조회하면
성별: 10110
결혼: 10011
vxxvx
1번째랑 4번째가 우리가 찾는 데이터
[예시 1] and 조건
1. 인덱스 생성
create index emp_sal_idx on hr.emp(salary);
2. 인덱스 조회
select * from user_ind_columns where table_name='EMP';
3. index_combine 힌트 사용
select /*+ gather_plan_statistics index_combine(e EMP_ID_PK EMP_SAL_IDX) */ employee_id, salary
from emp e
where employee_id = 100
and salary = 10000;--> 실행계획 Bitmap conversion
4. 직전 실행계획조회
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
[예시 2] or 조건
1. index_combine 힌트 사용
select /*+ gather_plan_statistics index_combine(e EMP_ID_PK EMP_SAL_IDX) */ employee_id, salary
from emp e
where employee_id = 100
or salary = 10000;--> 실행계획 Bitmap conversion
2. 직전 실행계획조회
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 3 | 5 |00:00:00.01 | 4 |
| 2 | BITMAP CONVERSION TO ROWIDS | | 1 | | 5 |00:00:00.01 | 2 |
| 3 | BITMAP OR | | 1 | | 1 |00:00:00.01 | 2 |
| 4 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.01 | 1 |
|* 5 | INDEX RANGE SCAN | EMP_SAL_IDX | 1 | | 4 |00:00:00.01 | 1 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.01 | 1 |
|* 7 | INDEX RANGE SCAN | EMP_IDX | 1 | | 1 |00:00:00.01 | 1 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SALARY"=10000)
7 - access("EMPLOYEE_ID"=100)-- bitmap 방식으로 access한 것을 조회
■ session_cached_cursors
: 오라클은 session 내에서 3번 이상 수행된 SQL문에 해당하는 Library Cache에 handle과 LCO에 대한 포인터 정보를 저장해놓는다.
왜 Why? Library Cache영역의 library cache latch 점유시간(탐색시간)을 줄이기 위함이다.
show parameter session_cached_cursors
select *
from hr.emp
where id=100;를 user process가 server process의 pga의 cursor에 받아서 parse,execute,fetch진행.
(가정) 나중에 (1시간 뒤) 다시 같은 sql검색하고 또 1시간뒤에 검색하고 하는 경우 (총3번)
이때, Cursor가 자주 사용하는 실행계획(LCO)에 대한 Pointer정보를 가질 수 있다 50개.
for latch점유시간을 줄이기 위해서!!
[실습]
1. Flush
<sys sess>
alter system flush shared_pool;
2. select문 입력
<hr sess>
select last_name, salary, job_id from hr.employees where employee_id = 100;
3. 조금 전 던진 select문장 찾기
<sys sess>
select parse_calls, executions, users_opening
from v$sql
where sql_text='select last_name, salary, job_id from hr.employees where employee_id = 100';
parse_calls = parse를 발생한 횟수
executions = execution을 발생한 횟수
users_opening = cache에 저장되어 있는 문장을 사용한 횟수
이때, users_opening은 0으로 나와야한다 why? 아직 3번반복안해서 cursor_cache가 안됏기때문
4. 다시 select문 입력
<hr sess>
select last_name, salary, job_id from hr.employees where employee_id = 100;
5. 다시 조회
select parse_calls, executions, users_opening
from v$sql
where sql_text='select last_name, salary, job_id from hr.employees where employee_id = 100';
-- users_opening은 0이어야 한다
6. 다시 select문 입력
<hr sess>
select last_name, salary, job_id from hr.employees where employee_id = 100;
7. 다시 조회
select parse_calls, executions, users_opening
from v$sql
where sql_text='select last_name, salary, job_id from hr.employees where employee_id = 100';
--> 이때 users_opening(cursor가 cache저장)이 되어야 하는게 맞음. 즉, 보이는 사진이 맞는 화면
[실습2]
1. Flush
<sys sess>
alter system flush shared_pool;
2. select문 입력
<hr sess>
select employee_id, hire_date, commission_pct from hr.employees where employee_id = 197;
3. 조금 전 던진 select문장 찾기
<sys sess>
select parse_calls, executions, users_opening
from v$sql
where sql_text='select employee_id, hire_date, commission_pct from hr.employees where employee_id = 197';
parse_calls = parse를 발생한 횟수
executions = execution을 발생한 횟수
users_opening = cache에 저장되어 있는 문장을 사용한 횟수
이때, users_opening은 0으로 나와야한다 why? 아직 3번반복안해서 cursor_cache가 안됏기때문
4. 다시 select문 입력
<hr sess>
select employee_id, hire_date, commission_pct from hr.employees where employee_id = 197;
5. 다시 조회
<sys sess>
select parse_calls, executions, users_opening
from v$sql
where sql_text='select employee_id, hire_date, commission_pct from hr.employees where employee_id = 197';
-- users_opening은 0이어야 한다
6. 다시 select문 입력
<hr sess>
select employee_id, hire_date, commission_pct from hr.employees where employee_id = 197;
7. 다시 조회
<sys sess>
select parse_calls, executions, users_opening
from v$sql
where sql_text='select employee_id, hire_date, commission_pct from hr.employees where employee_id = 197';
--> 이때 users_opening(cursor가 cache저장)이 되어야 하는게 맞음. 즉, 보이는 사진이 맞는 화면
[ session_cached_cursor 발동된 것 조회 ]
# 시스템 통계정보
select a.name, b.value
from v$statname a, v$mystat b
where b.statistic# = a.statistic#
and a.name in ('session cursor cache hits','parse count(total)');--> mystat테이블: 내가 발생시킨 통계정보
# 위 시스템 통계 조회
===================================
session cursor cache hits 3375
parse count (total) 1533
===================================
-- 동일 sql이 3회 반복 안되서 아직 cache cursor가 생성이 안되어 아직 증가 안됨
select last_name, salary, job_id from hr.employees where employee_id = 100;
# 시스템 통계 조회
===================================
session cursor cache hits 3375
parse count (total) 1535
===================================
-- 동일 sql이 3회 반복 안되서 아직 cache cursor가 생성이 안되어 아직 증가 안됨
select last_name, salary, job_id from hr.employees where employee_id = 100;
# 시스템 통계 조회
===================================
session cursor cache hits 3376
parse count (total) 1537
===================================
--> session cache cursor 이 돌아가면 session cursor cache hits는 증가
select last_name, salary, job_id from hr.employees where employee_id = 100;
# 시스템 통계 조회
===================================
session cursor cache hits 3378
parse count (total) 1539
===================================
--> session cache cursor 이 돌아가면 session cursor cache hits는 증가
select last_name, salary, job_id from hr.employees where employee_id = 100;
# 시스템 통계 조회
===================================
session cursor cache hits 3380
parse count (total) 1541
===================================
--> session cache cursor 이 돌아가면 session cursor cache hits는 증가
'Data Base > SQL 튜닝' 카테고리의 다른 글
| batch i/o, table prefetch, 옵티마이저 (0) | 2024.02.17 |
|---|---|
| PGA, 자동 PGA 메모리 관리 (1) | 2024.02.17 |
| row chaining, row migration (1) | 2024.02.17 |
| CLUSTERING FACTOR (0) | 2024.02.17 |
| Serial direct read (1) | 2024.02.17 |