| # 요약 | |
| ■ Row Chaining ■ Row Migration analyze table hr.mig_table compute statistics; ■ B * tree ■ Bitmap Index ■ session_cached_cursors |
|
■ Row Chaining
insert시에 한 블록 안에 한행의 값이 다 저장 못하고 여러 블록으로 걸쳐서 저장되는 현상
> 해결방법 : 모델링, 테이블 구조 재구성
1행 평균 바이트 from user_tables where AVG_ROW_LEN
■ Row Migration
- update시에 증가하려고 하는데 free 공간이 없어서 다른 블록으로 이전되는 현상
> 기존 헤더에 이전되었다는 정보 있음
> 리프 > 기존 헤더에서 이전되었다는 정보 > 이동한 정보 = i/o 2번, latch 2번 = 성능 저하
> rowid scan일 때 문제 발생 > 미리 방지? pctfree
HR@ora11g> create table hr.mig_table(id number, l_name varchar2(2000), f_name varchar2(2000));
Table created.
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.
HR@ora11g> select num_rows, blocks, chain_cnt from user_tables where table_name = 'MIG_TABLE';
NUM_ROWS BLOCKS CHAIN_CNT
---------- ---------- ----------
HR@ora11g> execute 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
- CHAIN_CNT : row migration + row chain
HR@ora11g> create index mig_table_idx on hr.mig_table(id);
Index created.
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> execute 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
> row migration 발생했을텐데?
# system level 통계 정보 (전후 구간으로 검토)
SYS@ora11g> select * from v$sysstat where name = 'table fetch continued row';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- --------------------------------------------- ---------- --------- ----------
417 table fetch continued row 64 7895 1413702393
> 기존헤더에 액세스해서 이전된 block으로 찾아갈 때마다 value 값 증가
# session level 통계 정보 (전후 구간으로 검토하기 위해 시간 추가, 세션 종료 시 초기화)
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:54:54 179 HR table fetch continued row 307
# 인덱스 스캔으로 유도
HR@ora11g> select /*+ index(t mig_table_idx) */ count(l_name)
from hr.mig_table t where id > 0;
COUNT(L_NAME)
-------------
1000
SYS@ora11g> select * from v$sysstat where name = 'table fetch continued row';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- --------------------------------------------- ---------- --------- ----------
417 table fetch continued row 64 8437 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:15:15 179 HR table fetch continued row 641
>
system level value +542
session level value +334 -> 작업 행 수만큼 row migration 발생
SYS@ora11g> select s.prev_sql_id, s.prev_child_number, v.sql_text
from v$session s, v$sql v
where s.prev_sql_id = v.sql_id
and s.prev_child_number = v.child_number
and s.username = 'HR';
PREV_SQL_ID PREV_CHILD_NUMBER SQL_TEXT
------------- ----------------- --------------------------------------------------
7bwscvpmwraq8 0 select /*+ index(t mig_table_idx) */ count(l_name)
from hr.mig_table t where id > 0
> row migration 발생한 sql문
SYS@ora11g> select * from table(dbms_xplan.display_cursor('7bwscvpmwraq8'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 7bwscvpmwraq8, child number 0
-------------------------------------
select /*+ index(t mig_table_idx) */ count(l_name) from
hr.mig_table t where id > 0
Plan hash value: 2915741377
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 337 (100)| |
| 1 | SORT AGGREGATE | | 1 | 2005 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| MIG_TABLE | 1000 | 1958K| 337 (0)| 00:00:05 |
|* 3 | INDEX RANGE SCAN | MIG_TABLE_IDX | 1000 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID">0)
- sort aggregate : 그룹함수 사용했구나, 정렬X
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 통계수집
SYS@ora11g> analyze table hr.mig_table compute statistics;
Table analyzed.
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 해결? > 재배치
SYS@ora11g> select object_id, data_object_id from dba_objects where object_name = 'MIG_TABLE';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
88778 88778
# row migration이 발생한 테이블을 재구성하면 해결할 수 있다.
# 꼭 테이블 재구성하기 전에 테이블에 속한 테이블스페이스에 프리공간이 있는지 체크하고 수행한다.
SYS@ora11g> alter table hr.mig_table move;
Table altered.
> CTAS(복제) 임시공간 만들어 앞으로 이동(insert-delete-rename) dml 사용 - 리두 사용
> pctfree 제외하고 데이터를 앞으로 배치, rowid 변경
> 리오브 작업 전 대상 테이블스페이스에 free 공간 체크 후 진행하자.
> 다른 유저 DML wait
사용 블럭 수
전체 로우 수
한행 평균 바이트 값
/8k
시타스는 노로깅모드, 디스크만 사용, 메모리 미사용
SYS@ora11g> select object_id, data_object_id from dba_objects where object_name = 'MIG_TABLE';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
88784 88786
- OBJECT_ID : 처음 생성 시 번호
- DATA_OBJECT_ID : 재구성 후 현재 번호 -> rowid에 사용
> rowid 변경 > index UNUSABLE 상태
SYS@ora11g> select index_name, status from dba_indexes where table_name = 'MIG_TABLE';
INDEX_NAME STATUS
------------------------------ -------------
MIG_TABLE_IDX UNUSABLE
> drop - create 방식은 정렬작업이 진행되기 때문에 보통 작은 데이터에서 사용
# index 재구성하는 방법
SYS@ora11g> alter index hr.mig_table_idx rebuild online;
Index altered.
- index 재구성 : UNUSABLE 상태 시, BLEVEL 높을 경우
SYS@ora11g> select index_name, status from dba_indexes where table_name = 'MIG_TABLE';
INDEX_NAME STATUS
------------------------------ -------------
MIG_TABLE_IDX VALID
SYS@ora11g> analyze table hr.mig_table compute statistics;
Table analyzed.
SYS@ora11g> select num_rows, blocks, chain_cnt from dba_tables where table_name = 'MIG_TABLE';
NUM_ROWS BLOCKS CHAIN_CNT
---------- ---------- ----------
1000 518 0
# 다른 테이블스페이스로 이관 작업 + 재구성
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 9249 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:25:25 179 HR table fetch continued row 641
HR@ora11g> select /*+ index(t mig_table_idx) */ count(l_name)
from hr.mig_table t where id > 0;
COUNT(L_NAME)
-------------
1000
SYS@ora11g> select * from v$sysstat where name = 'table fetch continued row';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- --------------------------------------------- ---------- --------- ----------
417 table fetch continued row 64 9249 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:09:09 179 HR table fetch continued row 641
> 변화 없다.
- table fetch continued row : row migration + row chain
# 테이블 용량확인
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 5242880 640
HR MIG_TABLE_IDX INDEX USERS 65536 8
# 테이블스페이스 프리공간
SYS@ora11g> select round(sum(bytes)/1024/1024) mb
from dba_free_space where tablespace_name = 'EXAMPLE';
MB
----------
21
# 테이블을 다른 테이블스페이스로 이동
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.
> 인덱스가 UNUSABLE 상태라 속도 저하 중이니 운영중이라도 빨리 해결하자
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
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 object_id, data_object_id from dba_objects where object_name = 'MIG_TABLE';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
88784 88805
HR@ora11g> select /*+ index(t mig_table_idx) */ count(l_name)
from hr.mig_table t where id > 0;
COUNT(L_NAME)
-------------
1000
# 테이블 통계 수집
HR@ora11g> exec dbms_stats.gather_table_stats('hr','mig_table')
PL/SQL procedure successfully completed.
또는
SYS@ora11g> analyze table hr.mig_table compute statistics;
- 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') "LAST_ANALYZED"
from dba_tables
where table_name = 'MIG_TABLE';
TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN CHAIN_CNT LAST_ANALYZED
--------------- ---------- ---------- ----------- ---------- -------------------
EXAMPLE 1000 518 3006 0 2024-02-15 13:47:37
■ B * tree
- Balanced tree
- Binary tree
- Balanced Binary Search Tree (균형 이진 탐색 트리)
# B * tree 구조의 인덱스 생성 기준
- where 조건절에서 자주 사용되는 컬럼 중에 유일키값으로 구성되어 있는 컬럼
- 열에 null값은 제외하고 인덱스를 생성, null에서 인덱스 스캔을 하지 않는다.
- 테이블이 크고 대부분의 쿼리가 테이블에서 2 ~ 4%미만의 행을 검색할 때 유용하다.
- order by절에 자주 사용되는 컬럼 (이미 정렬이 되어있어 인덱스만 검색하면 됨, min,max도 포함)
■ Bitmap Index
- where 조건절에 자주 사용되는 컬럼들 중에 중복성이 많은 컬럼을 선정(성별, 결혼여부 등)
- 열에 null값을 포함해서 인덱스를 생성하기때문에 null에서 인덱스 스캔이 수행된다.
- index segment level lock : dml이 잦을 경우 단점
HR@ora11g> create index hr.emp_idx on hr.emp(employee_id);
HR@ora11g> create index emp_sal_idx on emp(salary);
Index created.
HR@ora11g> select * from user_ind_columns where table_name = 'EMP';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------ -------------------- -------------------- --------------- ------------- ----------- -------
EMP_SAL_IDX EMP SALARY 1 22 0 ASC
EMP_IDX EMP EMPLOYEE_ID 1 22 0 ASC
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
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 7rv16wt9vzrhq, child number 1
-------------------------------------
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
Plan hash value: 3050648138
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 2 |
| 1 | BITMAP CONVERSION TO ROWIDS | | 1 | 1 | 0 |00:00:00.01 | 2 |
| 2 | BITMAP AND | | 1 | | 0 |00:00:00.01 | 2 |
| 3 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.01 | 1 |
|* 4 | INDEX RANGE SCAN | EMP_IDX | 1 | | 1 |00:00:00.01 | 1 |
| 5 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.01 | 1 |
|* 6 | INDEX RANGE SCAN | EMP_SAL_IDX | 1 | | 4 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEE_ID"=100)
6 - access("SALARY"=10000)
# Bitmap Index 생성
create bitmap index t_table_idx on t_table(column);
비프리 구조 인덱스 : rowid
비트맵 구조 인덱스 : 0,1 조합...
값 시작rowid 끝rowid bitmap
ㄴ데이터 범위 1,0로 행 개수만큼
데이터웨어하우징, exedata, ... 등에서 많이 사용
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 2000
150 10000
156 10000
169 10000
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 57dwtr2ptsugt, child number 0
-------------------------------------
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
Plan hash value: 3344826655
----------------------------------------------------------------------------------------------------------
| 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)
| B * tree | Bitmap Index |
| 유일키값으로 데이터 있는 컬럼 | 중복성이 많은 컬럼 |
| DML 비용이 저렴 (row level lock) | DML 비용이 크다 (데이터는 row level lock, bitmap index는 segment level lock) |
| OR 연산자에서는 비효율적 | OR 연산자에서도 효율적 |
| null에 대해서 인덱스 스캔 안함 | null에 대해 인덱스 스캔 한다 |
| OLPT(Online Transaction Processing) | DW(Data Warehouse), DSS(Decision Support System, 의사결정시스템) |
■ session_cached_cursors
오라클은 session 내에서 3번 이상 수행된 SQL문에 해당하는 Library Cache에 handle과 LCO에 대한 포인터 정보를
저장해놓는다.
Library Cache 영역의 탐색하는 시간이 줄어들어 "latch 보유 시간을 줄일 수 있다."
- 프로그램 레벨에서 효과적
SYS@ora11g> show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
session_cached_cursors integer 50 (개)
SYS@ora11g> alter system flush shared_pool;
SYS@ora11g> alter system flush shared_pool;
SYS@ora11g> alter system flush shared_pool;
System altered.
HR@ora11g> select last_name, salary, job_id from hr.employees where employee_id = 100;
LAST_NAME SALARY JOB_ID
------------------------- ---------- ----------
King 2000 AD_PRES
SYS@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
... select 문 3번 실행 시
PARSE_CALLS EXECUTIONS USERS_OPENING
----------- ---------- -------------
3 3 1
- PARSE_CALLS : 파싱을 발생한 건수
- USERS_OPENING : cache에 저장되어있는 문장을 사용한 횟수
# v$mystat : 내 세션에서 발생한 통계 정보
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 853
parse count (total) 210
HR@ora11g> select last_name, salary, job_id from hr.employees where employee_id = 100;
LAST_NAME SALARY JOB_ID
------------------------- ---------- ----------
King 2000 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 855
parse count (total) 212
Database Concepts
Indexes are schema objects that can speed access to table rows. Index-organized tables are tables stored in an index structure.
docs.oracle.com
SQL Tuning Guide
An access path is a technique used by a query to retrieve rows from a row source.
docs.oracle.com
Data Warehousing Guide
docs.oracle.com
'Oracle 11g > Tuning' 카테고리의 다른 글
| 87 Nested Loop Join, Sort Merge Join (0) | 2024.02.19 |
|---|---|
| 86 PGA, Sort Operation, Join, Table Prefetch, Batch I/O (0) | 2024.02.16 |
| 84 Serial direct read, Cluster Factor (0) | 2024.02.14 |
| 83 Explain Plan, AutoTrace, v$sql_plan, Buffer Pinning, SQL TRACE (0) | 2024.02.13 |
| 82 TX LOCK, Full Table Scan, Rowid Scan, Index Scan (1) | 2024.02.08 |