B-Tree 구조, Bitmap 구조 인덱스, session_cached_cursors

■ 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는 증가