<aside> 💡 2023/02/15 21:00 ~ 22:30
</aside>
create table BIG_TABLE
(
id NUMBER GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1),
owner varchar2(255) not null,
object_name varchar2(255) ,
created DATE not null
);
alter table BIG_TABLE add constraint big_table_pk primary key (id);
--데이터 생성및 부풀리기(1000만건 될 때까지 여러번 실행)
insert into BIG_TABLE (owner, object_name, created)
select owner, object_name, created FROM all_objects order by dbms_random.value;
--중간중간 갯수세어보세요
select count(*) from BIG_TABLE;
create index x01 on BIG_TABLE(owner, created);
-- 통계정보 수집
begin
dbms_stats.gather_table_stats
( ownname => USER
, tabname => 'BIG_TABLE'
, estimate_percent => 100
, block_sample => true
, method_opt => 'for all columns size auto'
);
end;
select table_name, num_rows, last_analyzed from user_tables where table_name = 'BIG_TABLE'; -- 통계정보 수집여부
-- 실행계획이랑 SQL트레이스 확인해보기
SELECT /*+ gather_plan_statistics */ * from big_table where owner='APPQOSSYS';
SELECT * FROM table(dbms_xplan.display_cursor(null, null, 'allstats last'));
-- 클러스터링팩터 조회쿼리
select INDEX_NAME, TABLE_NAME, CLUSTERING_FACTOR from dba_indexes where table_name = 'BIG_TABLE'; -- pk: 131,952 n1: 7,807,312
-- 왜 TFS(TABLE FULL SCAN) / INDEX (FULL/RANGE) SCAN 인가
-- 왜 INDEX FULL SCAN / INDEX RANGE SCAN인가
-- 왜 INDEX 로 (PK / X01) 이 선택됐는가
-- 왜 SORT가 발생해서 전체범위처리가 됐는가
-- 왜 옵티마이저모드를 변경한 것만으로도 index힌트를 사용한 실행계획과 동일한 실행계획을 갖는가
-- 왜 owner='SYS' 와 owner='APPQOSSYS'가 다른 실행결과를 갖는가
A-0 select /*+ 힌트없음 */ * from big_table X order by owner; -- Table Full Scan [전체범위처리] 5.69s
A-0-1 select /*+ index(X) */ * from big_table X order by owner; -- index full scan of pk(id) [전체범위처리] 132.53s
A-0-2 select /*+ FIRST_ROWS */ * from big_table X order by owner; -- index full scan of x01 [부분범위처리] 0.96s
A-1. select /*+ 힌트없음 */ * from big_table X order by created; --Table Full Scan [전체범위처리]
A-1-1 select /*+ index(X) */ * from big_table X order by created; --Index Full Scan of pk [전체범위처리]
A-1-2 select /*+ FIRST_ROWS */ * from big_table X order by created; --Table Full Scan [전체범위처리]
A-2. select /*+ 힌트없음 */ * from big_table X where owner='SYS' order by created; --Table Full Scan [전체범위처리]
A-2-1.select /*+ index(X) */ * from big_table X where owner='SYS' order by created; --Index Full Scan of pk [전체범위처리]
A-2-2.select /*+ FIRST_ROWS */ * from big_table X where owner='SYS' order by created; --Index RangeScan of x01 [부분범위처리]
A-3. select /*+ 힌트없음 */ * from big_table X where owner='APPQOSSYS' order by created; --Index RangeScan of x01 [부분범위처리]
A-3-1.select /*+ index(X) */ * from big_table X where owner='APPQOSSYS' order by created; --Index RangeScan of x01 [부분범위처리]
A-3-2.select /*+ FIRST_ROWS */ * from big_table X where owner='APPQOSSYS' order by created; --Index RangeScan of x01 [부분범위처리]
B-1. select /*+ 힌트없음 */ * from big_table X order by id; -- Index Full Scan of pk [부분범위처리]
B-1-1.select /*+ index(X) */ * from big_table X order by id; -- Index Full Scan of pk [부분범위처리]
B-1-2.select /*+ FIRST_ROWS */ * from big_table X order by id; -- Index Full Scan of pk [부분범위처리]
B-1-3.select /*+ index(X x01) */ * from big_table X order by id; -- Index Full Scan of pk [전체범위처리]
B-2. select /*+ 힌트없음 */ * from big_table X where owner = 'SYS' order by id; -- Index Full Scan of pk [부분범위처리]
B-2-1.select /*+ index(X) */ * from big_table X where owner = 'SYS' order by id; -- Index Full Scan of pk [부분범위처리]
B-2-2.select /*+ FIRST_ROWS */ * from big_table X where owner = 'SYS' order by id; -- Index Full Scan of pk [부분범위처리]
(23.02.24추가) -- 성능고도화2권 p.368
A-4. select /*+ 힌트없음 */ * from big_table X order by owner, created; --Table Full Scan [전체범위처리]
A-4-1.select /*+ FIRST_ROWS */ * from big_table X order by owner, created; -- Index Full Scan of x01 [부분범위처리]
select * from DBA_TAB_COLUMNS;
실행통계, 세션통계