2024년 2월 15일

 

★ 포트폴리오 만들기

 

1. select 문의 처리단계

2. DML 문 처리단계 (update)

3. commit 이 발생하면 어떤 일이 일어나는가? (단계별로)

4. 체크포인트가 발생하면 어떤 백그라운드 프로세스가 일을 하는가?

5. 문장 튜닝

6. 인덱스 정리, 조인의 방법론 정리, 서브쿼리 정리

---

 

1 교시

 

★ row migration

- 업데이트 시에 기존 블록 안에서 데이터 양이 늘어나려고 하는데

  더 이상 그 블록 안에 증가분을 위한 free 공간이 없어서 row 전체가 다른 블록으로 이전되는 현상

 

★ row chaining

- 인서트 시에 한 블록 안에 한 행의 값이 다 들어가지 못하고 여러 블록에 걸쳐서 저장되는 현상

 

테이블 만들기

HR@ora11g> create table hr.mig_table(id number, l_name varchar2(2000), f_name varchar2(2000));

Table created.

 

1000 건 데이터 인서트

- decode(mod(level,3),1,null,rpad('x',2000,'x')):

level (현재 행의 수) 를 3으로 나눈 나머지가 1인 경우에는 null 반환 / 그 외의 경우에는 길이 2000 짜리 문자열 x 반환

- decode(mod(level,3),1,null,rpad('x',1000,'x')):

level 을 3으로 나눈 나머지가 1인 경우에는 null 반환 / 그 외의 경우에는 길이가 1000인 문자열 x 반환

 

HR@ora11g> insert into hr.mig_table(id, l_name, f_name) select level, decode(mod(level,3),1,null,rpad('x',2000,'x')),decode(mod(level,3),1,null,rpad('x',1000,'x')) from dual connect by level <= 1000;

1000 rows created.

 

HR@ora11g> commit;

Commit complete.

 

통계정보 확인하기

(통계수집을 하지 않은 상태에서 통계정보를 확인해봤자 null 값으로 보인다. 그래서 데이터 1건도 안나옴)

HR@ora11g> select num_rows, blocks, chain_cnt from user_tables where table_name = 'MIG_TABLE';

 

  NUM_ROWS     BLOCKS  CHAIN_CNT   --- row migration 혹은 row chain 발생 정보를 보여준다.
--------------------------------------------------------

 

통계정보 수집

HR@ora11g> exec dbms_stats.gather_table_stats('HR', 'MIG_TABLE');

PL/SQL procedure successfully completed.

 

통계정보 확인

HR@ora11g> select num_rows, blocks, chain_cnt from user_tables where table_name = 'MIG_TABLE';

  NUM_ROWS     BLOCKS  CHAIN_CNT
--------------------------------------------------------
           1000                370             0

---

 

2교시

 

row migration 을 유도해 보자.

 

인덱스 생성하기

HR@ora11g> create index mig_table_idx on mig_table(id);
Index created.

 

row migration 이 발생하도록 업데이트

HR@ora11g> update hr.mig_table set l_name = rpad('x',2000,'x'), f_name=rpad('x',1000,'x') where mod(id,3)=1;
334 rows updated.

 

커밋

HR@ora11g> commit;
Commit complete.

 

통계정보 수집 (통계정보는 마지막 수집한 시점으로 보인다. 다시 보고 싶으면 다시 수집하기) 

HR@ora11g> exec dbms_stats.gather_table_stats('HR', 'MIG_TABLE');

PL/SQL procedure successfully completed.

 

통계정보 확인

HR@ora11g> select num_rows, blocks, chain_cnt from user_tables where table_name = 'MIG_TABLE';

 

  NUM_ROWS     BLOCKS  CHAIN_CNT  
--------------------------------------------------------
      1000                    622            0             --- migration 에 대한 통계는 수집하지 않기 때문에 이렇게는 확인할 수 없다.

 

<<sys 새로 열기>>

 

시스템 레벨 통계정보 보기

SYS@ora11g> select * from v$sysstat where name = 'table fetch continued row';

STATISTIC#         NAME                   CLASS       VALUE    STAT_ID
--------------------------------------------------------------------------------------------------------
       417 table fetch continued row        64            40223     1413702393

      --- row migration 이 발생할 때마다

          (정확히는 row migration 이 발생한 블록에 액세스하는 순간)

          table fetch continued row 의 횟수가 증가함

          (row migration 이 발생한 row 의 수만큼 증가)

table fetch continued row.txt

0.00MB

 

세션 레벨 통계정보 보기

SYS@ora11g> select to_char(sysdate,'yyyy-mm-dd hh24:ss:ss') day, a.sid, vss.username, a.name, a.value
    from (
    select vst.sid,vst.value,vsn.name,vsn.statistic#
    from v$statname vsn, v$sesstat vst
    where vsn.statistic# = vst.statistic#
    order by vst.value desc) a, v$session vss
    where a.sid = vss.sid
    and vss.username = 'HR'
    and a.name =  'table fetch continued row'; 

 

DAY                                  SID    USERNAME                 NAME                             VALUE
--------------------------------------------------------------------------------------------------------------------------
2024-02-15 10:00:00        181          HR              table fetch continued row                4

 

<<hr 에서>>

 

인덱스 스캔이 유도되도록 하기:

1000 건 데이터를 모두 인덱스를 통해 찾음 > random I/O 매우 많이 발생

HR@ora11g> select /*+ index(t mig_table_idx) */ count(l_name) from hr.mig_table t where id > 0;

COUNT(L_NAME)
-------------
         1000

 

<<sys>>

 

시스템 레벨 통계정보 보기

SYS@ora11g> select * from v$sysstat where name = 'table fetch continued row';

 

STATISTIC# NAME
---------- ----------------------------------------------------------------
     CLASS      VALUE    STAT_ID
---------- ---------- ----------
       417 table fetch continued row
        64      40557 1413702393

 

before

시스템 레벨 통계정보 보기

SYS@ora11g> select * from v$sysstat where name = 'table fetch continued row';

STATISTIC# NAME
---------- ----------------------------------------------------------------
     CLASS      VALUE    STAT_ID
---------- ---------- ----------
       417 table fetch continued row
        64      40223 1413702393

 

세션 레벨 통계정보 보기

SYS@ora11g> select to_char(sysdate,'yyyy-mm-dd hh24:ss:ss') day, a.sid, vss.username, a.name, a.value
    from (
    select vst.sid,vst.value,vsn.name,vsn.statistic#
    from v$statname vsn, v$sesstat vst
    where vsn.statistic# = vst.statistic#
    order by vst.value desc) a, v$session vss
    where a.sid = vss.sid
    and vss.username = 'HR'
    and a.name =  'table fetch continued row';

DAY                        SID USERNAME
------------------- ---------- ------------------------------
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
2024-02-15 11:04:04        181 HR
table fetch continued row                                               338

 

before

세션 레벨 통계정보 보기

SYS@ora11g> select to_char(sysdate,'yyyy-mm-dd hh24:ss:ss') day, a.sid, vss.username, a.name, a.value
    from (
    select vst.sid,vst.value,vsn.name,vsn.statistic#
    from v$statname vsn, v$sesstat vst
    where vsn.statistic# = vst.statistic#
    order by vst.value desc) a, v$session vss
    where a.sid = vss.sid
    and vss.username = 'HR'
    and a.name =  'table fetch continued row'; 

 

DAY                        SID USERNAME
------------------- ---------- ------------------------------
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
2024-02-15 10:00:00        181 HR
table fetch continued row                                             4

 

문제가 되는 SQL 문 찾기

SYS@ora11g> select s.prev_sql_id, s.prev_child_number, v.sql_text
from v$session s, v$sql v
where s.username = 'HR'
and s.prev_sql_id = v.sql_id
and s.prev_child_number = v.child_number;

PREV_SQL_ID       PREV_CHILD_NUMBER       SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
4y2m5q8hhv0r4                 0          select /*+ index(t mig_table_idx) */ count(l_name) from hr.mig_table t where id > 0

 

해당 SQL 문의 플랜 정보 확인하기:

row migration 이 발생한 테이블 찾기

(1번 sort aggregate 는 그룹함수를 썼다는 뜻)

 

SYS@ora11g> select * from table(dbms_xplan.display_cursor('4y2m5q8hhv0r4'));

 

 

 

SYS@ora11g> select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';

  NUM_ROWS     BLOCKS  CHAIN_CNT
--------------------------------------------------
      1000                    622          0                --- 이렇게는 row migration 통계수집을 할 수 없다.

 

row migration 통계수집

SYS@ora11g> analyze table hr.mig_table compute statistics;
Table analyzed.

 

row migration 통계 확인: row migration 의 개수만큼 나온다.

SYS@ora11g> select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';

  NUM_ROWS     BLOCKS  CHAIN_CNT
---------------------------------------------------------
      1000                    622           334

 

---

 

row migration 해결방법: re-org (재배치)

 

1. 테이블의 object 번호 확인하기

SYS@ora11g> select object_id, data_object_id from dba_objects where object_name = 'MIG_TABLE';

 OBJECT_ID DATA_OBJECT_ID
----------------------------------------------
     90035              90035

 

2. 테이블 재구성

(테이블을 재구성하는 순간 저 테이블은 DML 작업이 막힌다 - 순간적이지만 업무 마비)

 

SYS@ora11g> alter table hr.mig_table move;
Table altered.

 

3. object 번호 확인하기: data_object_id 가 바뀌어 있다 = 인덱스에 문제가 생겼다

SYS@ora11g> select object_id, data_object_id from dba_objects where object_name = 'MIG_TABLE';

 OBJECT_ID DATA_OBJECT_ID
----------------------------------------------
     90035          90037

 

4. 인덱스 찾아보기

SYS@ora11g> select index_name, status from dba_indexes where table_name='MIG_TABLE';

INDEX_NAME                     STATUS
------------------------------------------------------
MIG_TABLE_IDX                  UNUSABLE        --- INDEX 가 unsable 상태가 됨.

 

5. 인덱스도 재구성하기

(drop 후 다시 create 하는 것도 방법이지만 인덱스가 너무 클 경우에는 재구성하는 것이 낫다.

왜냐하면 인덱스를 생성하면 정렬이 발생하는데 데이터가 클 경우에는 좋지 않다.)

SYS@ora11g> alter index hr.mig_table_idx rebuild online;
Index altered.

(online 옵션: 운영 중에 인덱스 재구성이 가능하도록 하기)

 

6. 인덱스 상태 확인:

valid 상태로 잘 바뀌어 있다.

 

SYS@ora11g> select index_name, status from dba_indexes where table_name = 'MIG_TABLE';

INDEX_NAME                     STATUS
------------------------------------------------------
MIG_TABLE_IDX                  VALID          --- valid 상태

 

7. 다시 row migration 통계 수집

SYS@ora11g> analyze table hr.mig_table compute statistics;
Table analyzed.

 

8. row migration 통계 확인

 

SYS@ora11g> select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';

  NUM_ROWS     BLOCKS  CHAIN_CNT
------------------------------------------------------
      1000                   518          0

 

before

row migration 통계 확인: row migration 의 개수만큼 나온다.

SYS@ora11g> select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';

  NUM_ROWS     BLOCKS  CHAIN_CNT
---------------------------------------------------------
      1000                    622           334

 

쓰는 블록은 많은데 row는 너무 듬성듬성 들어가 있으면 > full table scan 시 성능이 나빠진다.

(해결방법 : 테이블 re-org = 앞부분에서부터 비어있는 블록에 row 들을 꽉꽉 채워넣고 HWM 딱 맞게 찍음)

 

★ 주의!

!!!!!!!!!!!!!!!!!!!!!!!!!! move 작업을 하기 전에 대상 테이블에 프리 공간이 있는지를 꼭 체크해야 한다 !!!!!!!!!!!!!!!!!!!!!!

(!!!!!!!!!!!!!!!!!!!!!!!!!!! 얼마만큼?! 테이블의 크기만큼 !!!!!!!!!!!!!!!!!!!!!!!!!!!)

 

만약 테이블스페이스에 공간이 없으면 > 데이터파일 추가

그런데 데이터파일도 추가를 못하면? 다른 테이블스페이스로 이동시키기

(이관작업 + re-org 병행)

 

다른 테이블스페이스로 이동 (이관작업과 re-org 를 동시에 - 3교시에서 자세히)

SYS@ora11g> alter table hr.mig_table move tablespace insa_tbs;
Table altered.

 

---

 

구간 찍기

 

시스템 레벨 통계정보 보기
SYS@ora11g> select * from v$sysstat where name = 'table fetch continued row';

STATISTIC# NAME
---------- ----------------------------------------------------------------
     CLASS      VALUE    STAT_ID
---------- ---------- ----------
       417 table fetch continued row
        64      40560 1413702393

 

세션 레벨 통계정보 보기

row migration 이 발생한 것처럼 보여도 그렇지 않다.

 

SYS@ora11g> select to_char(sysdate,'yyyy-mm-dd hh24:ss:ss') day, a.sid, vss.username, a.name, a.value
    from (
    select vst.sid,vst.value,vsn.name,vsn.statistic#
    from v$statname vsn, v$sesstat vst
    where vsn.statistic# = vst.statistic#
    order by vst.value desc) a, v$session vss
    where a.sid = vss.sid
    and vss.username = 'HR'
    and a.name =  'table fetch continued row'; 

DAY                        SID USERNAME
------------------- ---------- ------------------------------
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
2024-02-15 11:22:22        181 HR
table fetch continued row                                               338

 

<<hr>>

HR@ora11g> select /*+ index(t mig_table_idx) */ count(l_name) from hr.mig_table t where id > 0;

COUNT(L_NAME)
-------------
         1000

 

<<sys>>

SYS@ora11g> select to_char(sysdate,'yyyy-mm-dd hh24:ss:ss') day, a.sid, vss.username, a.name, a.value
    from (
    select vst.sid,vst.value,vsn.name,vsn.statistic#
    from v$statname vsn, v$sesstat vst
    where vsn.statistic# = vst.statistic#
    order by vst.value desc) a, v$session vss
    where a.sid = vss.sid
    and vss.username = 'HR'
    and a.name =  'table fetch continued row';

DAY                        SID USERNAME
------------------- ---------- ------------------------------
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
2024-02-15 11:28:28        181 HR
table fetch continued row                                               338

 

구간을 찍어봤더니 변화가 없다: row migration 이 해결되었다는 뜻

---

 

3교시

 

★ row migration 이 발생하지 않도록 다른 테이블스페이스로 이동시키기

 

테이블이 현재 어느 곳에 있는지 확인하기

SYS@ora11g> select owner, segment_name, segment_type, tablespace_name, bytes, blocks from dba_segments where owner = 'HR' and segment_name in ('MIG_TABLE', 'MIG_TABLE_IDX');

OWNER  SEGMENT_NAME     SEGMENT_TYPE    TABLESPACE_NAME       BYTES     BLOCKS
--------------------------------------------------------------------------------------------------------------------------------------
HR                MIG_TABLE                  TABLE                         USERS                    65536            8
HR              MIG_TABLE_IDX            INDEX                         USERS                   5242880        640

 

이관하려는 테이블스페이스에 공간이 있는지 확인하기
SYS@ora11g> select round(sum(bytes)/1024/1024) mb from dba_free_space where tablespace_name = 'EXAMPLE';

 

테이블을 다른 테이블스페이스로 이동

SYS@ora11g> alter table hr.mig_table move tablespace example;
Table altered.

 

인덱스를 다른 테이블스페이스로 이동

SYS@ora11g> alter index hr.mig_table_idx rebuild online tablespace example;
Index altered.

 

테이블이 잘 옮겨졌는지 확인

SYS@ora11g> select owner, segment_name, segment_type, tablespace_name, bytes, blocks from dba_segments where owner = 'HR' and segment_name in ('MIG_TABLE', 'MIG_TABLE_IDX');

OWNER       SEGMENT_NAME       SEGMENT_TYPE       TABLESPACE_NAME       BYTES     BLOCKS
-------------------------------------------------------------------------------------------------------------------------------------------------
HR              MIG_TABLE_IDX                INDEX                            EXAMPLE                   65536            8
HR                 MIG_TABLE                      TABLE                           EXAMPLE                 5242880        640

 

인덱스 확인해보기 (valid 상태)

SYS@ora11g> select index_name, status, blevel from dba_indexes where table_name = 'MIG_TABLE';

INDEX_NAME                     STATUS       BLEVEL
----------------------------------------------------------------------

MIG_TABLE_IDX                  VALID             1

 

SYS@ora11g> select /*+ gather_plan_statistics index(t mig_table_idx) */ count(l_name) from hr.mig_table t where id > 0;

COUNT(L_NAME)
--------------------------
         1000

 

★ 인덱스를 재구성해야 할 때:
인덱스의 depth 가 깊을 때 (blevel이 높을 때) - 성능상 좋지 않다. rebuild 필수

 

★ 앞으로 rowid 는 '파일번호 & 데이터오브젝트번호' 라고 기억하자.

rowid 18 자리 = 데이터 오브젝트 번호 6자리 / 파일번호 3자리 / 블록번호 6자리 / row slot 번호 3자리

 

move 하면 데이터오브젝트번호가 바뀌므로 (데이터 오브젝트 번호뿐만 아니라 블록번호도, row slot 번호도)

인덱스는 깨진다. (move tablespace 하면 다 바뀜)

move 하면 무조건 인덱스는 rebuild 해야 한다.

---

 

4교시

 

테이블 통계 수집: 두 개의 명령어로 수행할 수 있다.

 

exec dbms_stats.gater_table_stats('HR', 'MIG_TABLE');
또는

SYS@ora11g> analyze table hr.mig_table compute statistics;
Table analyzed.

row chaining, row migration 정보도 같이 수집하려면

SYS@ora11g> select tablespace_name, num_rows, blocks, avg_row_len, chain_cnt, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name = 'MIG_TABLE';

TABLESPACE_NAME       NUM_ROWS    BLOCKS AVG_ROW_LEN  CHAIN_CNT   TO_CHAR(LAST_ANALYZ
-------------------------------------------------------------------------------------------------------------------------------------------------------
EXAMPLE                                  1000                   518               3013                0                 2024-02-15 13:51:03

 

HWM 까지 사용한 블록의 수가 나온다.

SYS@ora11g> select owner, segment_name, segment_type, tablespace_name, bytes, blocks from dba_segments where owner = 'HR' and segment_name in ('MIG_TABLE', 'MIG_TABLE_IDX');

OWNER  SEGMENT_NAME     SEGMENT_TYPE    TABLESPACE_NAME       BYTES     BLOCKS
--------------------------------------------------------------------------------------------------------------------------------------
HR                MIG_TABLE                  TABLE                         USERS                    65536            8
HR              MIG_TABLE_IDX            INDEX                         USERS                   5242880        640

 

--- 640 : 전체 블록 수

 

★ B * tree 구조 인덱스

- B 는 Balanced 혹은 Binary 의 약자

- Balanced Binary Search Tree (균형 이진 탐색 트리)

 

※ B * tree 구조의 인덱스 생성 기준

- where 절에서 자주 사용되는 컬럼이면서 유일키 값으로 구성되어 있는 컬럼

- 컬럼에 null 값은 제외하고 인덱스를 생성한다.

  (null 에서 인덱스 스캔하지 않는다.)

- 테이블이 크고 대부분의 쿼리가 테이블에서 2~4% 미만의 행을 검색할 때 유용하다.

- order by 절에 자주 사용되는 컬럼 : 굳이 sorting 할 필요가 없다.

  (왜냐하면 인덱스는 이미 sort 된 값이기 때문에)

 

★ 비트맵 인덱스 Bitmap index

- 생성 기준 : where 절에 자주 사용되면서 + 중복성이 많은 컬럼에 생성

- 열에 null 값을 포함해서 인덱스를 생성하기 때문에, null 에 대해서도 인덱스 스캔이 수행된다.

 

인덱스 추가

HR@ora11g> create index hr.emp_idx on hr.emp(employee_id);

Index created.

 

어느 어느 컬럼에 index 가 걸려 있었는지 체크하기

HR@ora11g> select * from user_ind_columns where table_name = 'HR';
no rows selected

 

HR@ora11g> create index emp_sal_idx on emp(salary);
Index created.

 

어떤 컬럼에 인덱스가 걸려 있는지 확인:

인덱스가 2개 걸려 있는 것이 보인다.

HR@ora11g> select * from user_ind_columns where table_name = 'EMP';

 

통계 수집: no rows selected 라고 나오는 게 정상

HR@ora11g> 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;
no rows selected

 

바로 직전에 수행한 SQL 문에 대한 실행계획 확인

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

---

 

5교시

 

칠판 그림

천만 건의 데이터가 들어있는 설문 테이블이 하나 있다고 하자.

 

사원번호	성별	결혼 여부
1	남	기혼	시작 rowid: #1
2	여	미혼
3	남	기혼
4	남	기혼
5	여	기혼
6	여	미혼
7	남	미혼
8	null	기혼	성별이 null
...	...	...	...
			끝 rowid: #1천만

 

이 테이블에서, 성별은 남자이고 기혼인 사람을 빨리 찾으려면

어떻게 인덱스를 걸어야 할까?

지금처럼 컬럼들에 중복성이 많은 데이터가 들어 있을 때에는

비트리 인덱스보다는 비트맵 인덱스를 거는 것이 낫다.

 

예를 들어, 성별 컬럼에

create bitmap index 설문_성별_idx on 설문테이블(성별);

create bitmap index 설문_결혼여부_idx on 설문테이블(결혼여부);

--- 이렇게 비트맵 인덱스를 걸었다고 하자.

(비트맵 인덱스를 걸 때는 bitmap 키워드를 꼭 써주어야 한다)

 

그러면 기혼 남성을 찾는 select 문이 들어왔을 때

성별 컬럼 인덱스는 시작 rowid 행부터 끝 rowid 행까지의 모든 데이터에 대해

남자를 1, 여자를 0 으로 표시하고

결혼여부 컬럼 인덱스는 시작 rowid 행부터 끝 rowid 행까지의 모든 데이터에 대해

기혼을 1, 미혼을 0 으로 표시한다. 그러면

성별 컬럼에서는 1행부터 8행까지           10110010 이고

결혼여부 컬럼에서는 1행부터 8행까지    10111001

 

1행	2행	3행	4행	5행	6행	7행	8행
1	0	1	1	0	0	1	0
1	0	1	1	1	0	0	1

두 개의 인덱스 값이 동시에 1이면 (1행, 3행, 4행) 그게 곧 남자 & 기혼인 사람이 된다.

(여자이면서 미혼인 사람을 찾을 때는 인덱스 값이 여자 = 1, 남자 = 0 / 미혼 = 1, 기혼 = 0 이 된다)

 

데이터가 많고 중복성이 있는 테이블에서는

이렇게 비트맵 인덱스를 이용하면 빠르게 원하는 데이터를 찾을 수 있다.

 

아까 그 SQL 문을 and 조건이 아닌 or 조건으로 바꿔서 해보기

HR@ora11g> 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;

EMPLOYEE_ID     SALARY
---------------------------------
        204      10000
        100      24000
        150      10000
        156      10000
        169      10000

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

 

★ 비트리 인덱스 vs 비트맵 인덱스

B * tree index	비트맵 인덱스
데이터가 유일한 값으로 들어가 있는 컬럼에 거는 것이 좋다.	중복성이 많은 컬럼에 거는 것이 좋다.
DML 비용이 저렴하다.	DML 비용이 크다. (데이터에 대해서는 row level lock 이 걸리고, 비트맵 인덱스에는 segment 레벨의 락이 걸린다)
OR 연산자를 사용하면 비효율적이다.	OR 연산자를 사용하는 것도 효율적이다.
null 을 포함하지 않는다. (null 에 대해서 인덱스 스캔하지 않는다)	null 을 포함해서 인덱스 스캔한다.
OLTP 성 환경에 맞는 인덱스 (OLTP = OnLine Transaction Processing)	DW, DSS 환경에 맞는 인덱스 (DW: Data Warehouse / DSS: Decision Support System)

 

---

 

6교시

 

★ session_cached_cursors

오라클은 세션 내에서 3번 이상 수행된 SQL 문의 LC 핸들과 LCO 에 대한 포인트 정보를 저장해 놓는다.

이렇게 하면 LC 영역을 탐색하는 시간이 줄어들어 래치 점유시간을 줄일 수 있다.

(한 세션당 최대 50개까지 가지고 있을 수 있다)

 

SYS@ora11g> show parameter session_cached_cursors

 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     50

 

쉐어드 풀 플러시

SYS@ora11g> alter system flush shared_pool;
System altered.

 

/

 

select 문장 수행해보기

HR@ora11g> select last_name, salary, job_id from hr.employees where employee_id = 100;

LAST_NAME                SALARY        JOB_ID
-----------------------------------------------------------------
King                                24000          AD_PRES

 

HR@ora11g> 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       EXECUTIONS       USERS_OPENING
----------------------------------------------------------------------------------
           1                             1                                 0

 

PARSE_CALLS: 파싱을 발견한 건수

USERS_OPENING: 캐시에 저장되어 있는 문장을 사용한 횟수

(같은 문장을 3번 이상 반복해야 캐시에 저장되므로 처음에는 0, 3번이 되는 순간에는 1이 되어야 정상 /

1은 캐시에 저장이 되었다는 의미)

---

 

7교시

 

HR@ora11g> 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)');

NAME                                                                 VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                      530
parse count (total)                                                 556

 

- v$mystat: 내 세션에서 발생한 통계 정보

SQL 문장 수행하고 > 통계정보 보기 3번 반복

HR@ora11g> select last_name, salary, job_id from hr.employees where employee_id = 100;

LAST_NAME                     SALARY JOB_ID
------------------------- ---------- ----------
King                           24000 AD_PRES

HR@ora11g> 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)');

NAME                                                                 VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                      530
parse count (total)                                                 558

 

HR@ora11g> select last_name, salary, job_id from hr.employees where employee_id = 100;

LAST_NAME                     SALARY JOB_ID
------------------------- ---------- ----------
King                           24000 AD_PRES

 

HR@ora11g> 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)');

NAME                                                                 VALUE
--------------------------------------------------------------------------
session cursor cache hits                                      530
parse count (total)                                                 560

 

HR@ora11g> select last_name, salary, job_id from hr.employees where employee_id = 100;

LAST_NAME            SALARY JOB_ID
-----------------------------------------------------
King                           24000 AD_PRES

 

HR@ora11g> 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)');

NAME                                                                 VALUE
--------------------------------------------------------------------------
session cursor cache hits                                      532
parse count (total)                                                 562

 

원래 session cache cursors 가 1씩 증가해야 하는데

난 왜 첫번째에는 안증가했다가 두번째에 한꺼번에 2 증가하지...