| # 요약 | |
| Nested Loop Join ■ Sort Merge Join |
|
| # 예시 1 |
SYS@ora11g> drop table hr.emp purge;
SYS@ora11g> drop table hr.dept purge;
SYS@ora11g> drop table hr.loc purge;
SYS@ora11g> create table hr.emp nologging as select * from hr.employees;
Table created.
SYS@ora11g> create table hr.dept nologging as select * from hr.departments;
Table created.
SYS@ora11g> create table hr.loc nologging as select * from hr.locations;
Table created.
SYS@ora11g> exec dbms_stats.gather_table_stats('hr','emp')
PL/SQL procedure successfully completed.
SYS@ora11g> exec dbms_stats.gather_table_stats('hr','dept')
PL/SQL procedure successfully completed.
SYS@ora11g> exec dbms_stats.gather_table_stats('hr','loc')
PL/SQL procedure successfully completed.
-- 테이블 정보
SYS@ora11g> select table_name, num_rows, blocks, avg_row_len, last_analyzed
from user_tables
where table_name in ('EMP','DEPT','LOC');
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANAL
------------------------------ ---------- ---------- ----------- ---------
DEPT 27 4 21 19-FEB-24
EMP 107 5 69 19-FEB-24
LOC 23 4 49 19-FEB-24
-- 인덱스 정보
SYS@ora11g> select a.table_name, a.constraint_name, b.column_name, a.constraint_type, a.search_condition, a.r_constraint_name, a.index_name
from user_constraints a, user_cons_columns b
where a.constraint_name = b.constraint_name
and a.table_name in ('EMP','DEPT','LOC');
TABLE_NAME CONSTRAINT_NAME COLUMN_NAME C SEARCH_CONDITION R_CONSTRAINT_NAME INDEX_NAME
---------- --------------- --------------- - ----------------------------- ----------------- ----------
DEPT SYS_C0011668 DEPARTMENT_NAME C "DEPARTMENT_NAME" IS NOT NULL
EMP SYS_C0011664 LAST_NAME C "LAST_NAME" IS NOT NULL
EMP SYS_C0011665 EMAIL C "EMAIL" IS NOT NULL
EMP SYS_C0011666 HIRE_DATE C "HIRE_DATE" IS NOT NULL
EMP SYS_C0011667 JOB_ID C "JOB_ID" IS NOT NULL
LOC SYS_C0011669 CITY C "CITY" IS NOT NULL
HR@ora11g> select /*+ gather_plan_statistics */ e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and e.employee_id = 100;
LAST_NAME JOB_ID DEPARTMENT_NAME CITY
------------------------- ---------- ------------------------------ ------------------------------
King AD_PRES Executive Seattle
1 rows selected.
-- 실행계획
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 8 | 922K| 922K| 1180K (0)|
| 2 | MERGE JOIN CARTESIAN| | 1 | 23 | 23 |00:00:00.01 | 5 | | | |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 4 | BUFFER SORT | | 1 | 23 | 23 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0) |
| 5 | TABLE ACCESS FULL | LOC | 1 | 23 | 23 |00:00:00.01 | 2 | | | |
| 6 | TABLE ACCESS FULL | DEPT | 1 | 27 | 27 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "D"."LOCATION_ID"="L"."LOCATION_ID")
3 - filter("E"."EMPLOYEE_ID"=100)
> e.employee_id = 100 비조인 조건
> 1건 찾는데 Full Scan, Used-Memory 사용
# Nested Loop Join 적용
HR@ora11g> select /*+ gather_plan_statistics leading(e,d,l) use_nl(d) use_nl(l) */
e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and e.employee_id = 100;
LAST_NAME JOB_ID DEPARTMENT_NAME CITY
------------------------- ---------- ------------------------------ ------------------------------
King AD_PRES Executive Seattle
HR@ora11g> 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 | | 1 |00:00:00.01 | 10 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 10 |
| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL| EMP | 1 | 1 | 1 |00:00:00.01 | 4 |
|* 4 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 5 | TABLE ACCESS FULL | LOC | 1 | 1 | 1 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."EMPLOYEE_ID"=100)
4 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
5 - filter("D"."LOCATION_ID"="L"."LOCATION_ID")
> 버퍼 수 늘어남
# e.employee_id에 인덱스 설정 : 제약 조건과 분리
HR@ora11g> create unique index hr.emp_idx on hr.emp(employee_id);
Index created.
HR@ora11g> alter table hr.emp add constraint emp_pk primary key (employee_id) using index hr.emp_idx;
Table altered.
HR@ora11g> select a.table_name, a.constraint_name, b.column_name, a.constraint_type, a.search_condition,
a.r_constraint_name, a.index_name
from user_constraints a, user_cons_columns b
where a.constraint_name = b.constraint_name
and a.table_name in ('EMP');
TABLE_NAME CONSTRAINT_NAME COLUMN_NAME C SEARCH_CONDITION R_CONSTRAINT_NAME INDEX_NAME
-------------------- ------------------------------ -------------------- - ------------------------- ----------------- ----------
EMP SYS_C0011664 LAST_NAME C "LAST_NAME" IS NOT NULL
EMP SYS_C0011665 EMAIL C "EMAIL" IS NOT NULL
EMP SYS_C0011666 HIRE_DATE C "HIRE_DATE" IS NOT NULL
EMP SYS_C0011667 JOB_ID C "JOB_ID" IS NOT NULL
EMP EMP_PK EMPLOYEE_ID P EMP_IDX
HR@ora11g> select ix.index_name, ix.uniqueness, ic.column_name
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name = 'EMP';
INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ --------- --------------------
EMP_IDX UNIQUE EMPLOYEE_ID
HR@ora11g> select /*+ gather_plan_statistics leading(e,d,l) use_nl(d) use_nl(l) */
e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and e.employee_id = 100;
LAST_NAME JOB_ID DEPARTMENT_NAME CITY
------------------------- ---------- ------------------------------ ------------------------------
King AD_PRES Executive Seattle
HR@ora11g> 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 | | 1 |00:00:00.01 | 8 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 8 |
| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 5 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 4 | INDEX UNIQUE SCAN | EMP_IDX | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 5 | TABLE ACCESS FULL | DEPT | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 6 | TABLE ACCESS FULL | LOC | 1 | 1 | 1 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."EMPLOYEE_ID"=100)
5 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
6 - filter("D"."LOCATION_ID"="L"."LOCATION_ID")
# d.department_id 인덱스 설정
HR@ora11g> create unique index hr.dept_idx on hr.dept(department_id);
Index created.
HR@ora11g> alter table hr.dept add constraint dept_pk primary key (department_id) using index hr.dept_idx;
Table altered.
HR@ora11g> select a.table_name, a.constraint_name, b.column_name, a.constraint_type, a.search_condition, a.r_constraint_name, a.index_name
from user_constraints a, user_cons_columns b
where a.constraint_name = b.constraint_name
and a.table_name in ('DEPT');
TABLE_NAME CONSTRAINT_NAME COLUMN_NAME C SEARCH_CONDITION R_CONSTRAINT_NAME INDEX_NAME
-------------------- ------------------------------ -------------------- - ------------------------- ------------------------------ ------------------------------
DEPT SYS_C0011668 DEPARTMENT_NAME C "DEPARTMENT_NAME" IS NOT NULL
DEPT DEPT_PK DEPARTMENT_ID P DEPT_IDX
HR@ora11g> select ix.index_name, ix.uniqueness, ic.column_name
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name = 'DEPT';
INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ --------- --------------------
DEPT_IDX UNIQUE DEPARTMENT_ID
HR@ora11g> select /*+ gather_plan_statistics leading(e,d,l) use_nl(d) use_nl(l) */
e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and e.employee_id = 100;
LAST_NAME JOB_ID DEPARTMENT_NAME CITY
------------------------- ---------- ------------------------------ ------------------------------
King AD_PRES Executive Seattle
HR@ora11g> 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 | | 1 |00:00:00.01 | 7 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 7 |
| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 4 | INDEX UNIQUE SCAN | EMP_IDX | 1 | 1 | 1 |00:00:00.01 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 6 | INDEX UNIQUE SCAN | DEPT_IDX | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 7 | TABLE ACCESS FULL | LOC | 1 | 1 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."EMPLOYEE_ID"=100)
6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
7 - filter("D"."LOCATION_ID"="L"."LOCATION_ID")
# l.location_id 인덱스 설정
HR@ora11g> create unique index hr.loc_idx on hr.loc(location_id);
Index created.
HR@ora11g> alter table hr.loc add constraint loc_pk primary key (location_id) using index hr.loc_idx;
Table altered.
HR@ora11g> select a.table_name, a.constraint_name, b.column_name, a.constraint_type, a.search_condition, a.r_constraint_name, a.index_name
from user_constraints a, user_cons_columns b
where a.constraint_name = b.constraint_name
and a.table_name in ('LOC');
TABLE_NAME CONSTRAINT_NAME COLUMN_NAME C SEARCH_CONDITION R_CONSTRAINT_NAME INDEX_NAME
-------------------- ------------------------------ -------------------- - ------------------------- ------------------------------ ------------------------------
LOC SYS_C0011669 CITY C "CITY" IS NOT NULL
LOC LOC_PK LOCATION_ID P LOC_IDX
HR@ora11g> select ix.index_name, ix.uniqueness, ic.column_name
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name = 'LOC';
INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ --------- --------------------
LOC_IDX UNIQUE LOCATION_ID
HR@ora11g> select /*+ gather_plan_statistics leading(e,d,l) use_nl(d) use_nl(l) */
e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and e.employee_id = 100;
LAST_NAME JOB_ID DEPARTMENT_NAME CITY
------------------------- ---------- ------------------------------ ------------------------------
King AD_PRES Executive Seattle
HR@ora11g> 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 | | 1 |00:00:00.01 | 6 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 6 |
| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 4 | INDEX UNIQUE SCAN | EMP_IDX | 1 | 1 | 1 |00:00:00.01 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 6 | INDEX UNIQUE SCAN | DEPT_IDX | 1 | 1 | 1 |00:00:00.01 | 1 |
| 7 | TABLE ACCESS BY INDEX ROWID | LOC | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 8 | INDEX UNIQUE SCAN | LOC_IDX | 1 | 1 | 1 |00:00:00.01 | 1 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."EMPLOYEE_ID"=100)
6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
> NESTED LOOPS + INDEX 설계 필수
| # 예시 2 |
HR@ora11g> select /*+ gather_plan_statistics */ e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Seattle';
18 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 18 |00:00:00.01 | 9 | | | |
|* 1 | HASH JOIN | | 1 | 15 | 18 |00:00:00.01 | 9 | 967K| 967K| 1187K (0)|
| 2 | MERGE JOIN | | 1 | 4 | 21 |00:00:00.01 | 4 | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| LOC | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 4 | INDEX FULL SCAN | LOC_IDX | 1 | 23 | 23 |00:00:00.01 | 1 | | | |
|* 5 | SORT JOIN | | 1 | 27 | 21 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)|
| 6 | TABLE ACCESS FULL | DEPT | 1 | 27 | 27 |00:00:00.01 | 2 | | | |
| 7 | TABLE ACCESS FULL | EMP | 1 | 107 | 107 |00:00:00.01 | 5 | | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
3 - filter("L"."CITY"='Seattle')
5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
filter("D"."LOCATION_ID"="L"."LOCATION_ID")# Nested Loop Join 적용
HR@ora11g> select count(*) from loc where city = 'Seattle';
COUNT(*)
----------
1
HR@ora11g> select /*+ gather_plan_statistics leading(l,d,e) use_nl(d) use_nl(e) */
e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Seattle';
18 rows selected.
HR@ora11g> 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 | | 18 |00:00:00.01 | 72 |
| 1 | NESTED LOOPS | | 1 | 15 | 18 |00:00:00.01 | 72 |
| 2 | NESTED LOOPS | | 1 | 4 | 21 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL| LOC | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 4 | TABLE ACCESS FULL| DEPT | 1 | 4 | 21 |00:00:00.01 | 4 |
|* 5 | TABLE ACCESS FULL | EMP | 21 | 4 | 18 |00:00:00.01 | 65 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("L"."CITY"='Seattle')
4 - filter("D"."LOCATION_ID"="L"."LOCATION_ID")
5 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> outer : loc
> inner : dept
# 인덱스 설정
HR@ora11g> create index hr.loc_city_idx on hr.loc(city);
Index created.
HR@ora11g> create index hr.dept_loc_idx on hr.dept(location_id);
Index created.
Index HR@ora11g> create index hr.emp_dept_idx on hr.emp(department_id);
Index created.
HR@ora11g> select ix.index_name, ix.uniqueness, ic.column_name
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name in ('EML','DEPT','LOC');
INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ --------- --------------------
DEPT_IDX UNIQUE DEPARTMENT_ID
DEPT_LOC_IDX NONUNIQUE LOCATION_ID
LOC_IDX UNIQUE LOCATION_ID
LOC_CITY_IDX NONUNIQUE CITY
HR@ora11g> select /*+ gather_plan_statistics leading(l,d,e) use_nl(d) use_nl(e) */
e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Seattle';
18 rows selected.
HR@ora11g> 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 | | 18 |00:00:00.01 | 18 |
| 1 | NESTED LOOPS | | 1 | 15 | 18 |00:00:00.01 | 18 |
| 2 | NESTED LOOPS | | 1 | 40 | 18 |00:00:00.01 | 15 |
| 3 | NESTED LOOPS | | 1 | 4 | 21 |00:00:00.01 | 9 |
| 4 | TABLE ACCESS BY INDEX ROWID| LOC | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 5 | INDEX RANGE SCAN | LOC_CITY_IDX | 1 | 1 | 1 |00:00:00.01 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 21 |00:00:00.01 | 6 |
|* 7 | INDEX RANGE SCAN | DEPT_LOC_IDX | 1 | 4 | 21 |00:00:00.01 | 3 |
|* 8 | INDEX RANGE SCAN | EMP_DEPT_IDX | 21 | 10 | 18 |00:00:00.01 | 6 |
| 9 | TABLE ACCESS BY INDEX ROWID | EMP | 18 | 4 | 18 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("L"."CITY"='Seattle')
7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> 3 + 8 -> JOIN -> 실행계획 상 batch I/O 발생
# Inner쪽 테이블에 대해 Batch I/O가 발생안되게 하자(Table Prefetch)
HR@ora11g> select /*+ gather_plan_statistics leading(l,d,e) use_nl(d) no_nlj_batching(e) */
e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Seattle';
18 rows selected.
HR@ora11g> 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 | | 0 | | 0 |00:00:00.01 | 0 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 4 | 18 |00:00:00.01 | 18 |
| 2 | NESTED LOOPS | | 1 | 40 | 40 |00:00:00.01 | 15 |
| 3 | NESTED LOOPS | | 1 | 4 | 21 |00:00:00.01 | 9 |
| 4 | TABLE ACCESS BY INDEX ROWID| LOC | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 5 | INDEX RANGE SCAN | LOC_CITY_IDX | 1 | 1 | 1 |00:00:00.01 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 21 |00:00:00.01 | 6 |
|* 7 | INDEX RANGE SCAN | DEPT_LOC_IDX | 1 | 4 | 21 |00:00:00.01 | 3 |
|* 8 | INDEX RANGE SCAN | EMP_DEPT_IDX | 21 | 10 | 18 |00:00:00.01 | 6 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("L"."CITY"='Seattle')
7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> INDEX RANGE SCAN : 하나씩 I/O
> EMP(Inner)가 8번 위가 아닌 NESTED LOOPS 위에 있다?
-> Table Prefetch : 메모리에 데이터가 없어 디스크에서 데이터를 한번에 작업
> 차라리 조인해서 결과셋을 한번에 올리기 -> 진화한게 Batch I/O : 물리적 I/O 시, 메모리 없을 때 효과적
> Cluster Factor가 좋으면 Buffer Pinning 발생하여 더 효율적
- Multi Block I/O : Full Scan으로 데이터가 연속일 때 작업
| # 예시 3 |
HR@ora11g> select /*+ gather_plan_statistics */ e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Seattle'
and e.job_id = 'AD_VP';
LAST_NAME JOB_ID DEPARTMENT_NAME CITY
------------------------- ---------- ------------------------------ ------------------------------
Kochhar AD_VP Executive Seattle
De Haan AD_VP Executive Seattle
HR@ora11g> 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 | | 2 |00:00:00.01 | 14 |
| 1 | NESTED LOOPS | | 1 | 1 | 2 |00:00:00.01 | 14 |
| 2 | NESTED LOOPS | | 1 | 40 | 18 |00:00:00.01 | 12 |
| 3 | NESTED LOOPS | | 1 | 4 | 21|00:00:00.01 | 7 |
| 4 | TABLE ACCESS BY INDEX ROWID| LOC | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 5 | INDEX RANGE SCAN | LOC_CITY_IDX | 1 | 1 | 1 |00:00:00.01 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 21 |00:00:00.01 | 4 |
|* 7 | INDEX RANGE SCAN | DEPT_LOC_IDX | 1 | 4 | 21 |00:00:00.01 | 2 |
|* 8 | INDEX RANGE SCAN | EMP_DEPT_IDX | 21 | 10 | 18 |00:00:00.01 | 5 |
|* 9 | TABLE ACCESS BY INDEX ROWID | EMP | 18 | 1 | 2 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("L"."CITY"='Seattle')
7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
9 - filter("E"."JOB_ID"='AD_VP')
> 결과는 2건 > 불필요한 I/O가 많다 > 처음부터 2건을 찾자
# Index + Nested Loop Join
HR@ora11g> create index hr.emp_job_idx on hr.emp(job_id);
Index created.
HR@ora11g> select ix.index_name, ix.uniqueness, ic.column_name
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name in ('EMP','DEPT','LOC');
INDEX_NAME UNIQUENES COLUMN_NAME
------------------------------ --------- --------------------
DEPT_IDX UNIQUE DEPARTMENT_ID
DEPT_LOC_IDX NONUNIQUE LOCATION_ID
EMP_IDX UNIQUE EMPLOYEE_ID
EMP_DEPT_IDX NONUNIQUE DEPARTMENT_ID
EMP_JOB_IDX NONUNIQUE JOB_ID
LOC_IDX UNIQUE LOCATION_ID
LOC_CITY_IDX NONUNIQUE CITY
HR@ora11g> select /*+ gather_plan_statistics leading(e,d,l) use_nl(d) use_nl(l) */
e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Seattle'
and e.job_id = 'AD_VP';
2 rows selected.
HR@ora11g> 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 | | 2 |00:00:00.01 | 13 |
| 1 | NESTED LOOPS | | 1 | 1 | 2 |00:00:00.01 | 13 |
| 2 | NESTED LOOPS | | 1 | 6 | 2 |00:00:00.01 | 11 |
| 3 | NESTED LOOPS | | 1 | 6 | 2 |00:00:00.01 | 8 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 6 | 2 |00:00:00.01 | 4 |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IDX | 1 | 6 | 2 |00:00:00.01 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 2 | 1 | 2 |00:00:00.01 | 4 |
|* 7 | INDEX UNIQUE SCAN | DEPT_IDX | 2 | 1 | 2 |00:00:00.01 | 2 |
|* 8 | INDEX RANGE SCAN | LOC_CITY_IDX | 2 | 1 | 2 |00:00:00.01 | 3 |
|* 9 | TABLE ACCESS BY INDEX ROWID | LOC | 2 | 1 | 2 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."JOB_ID"='AD_VP')
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access("L"."CITY"='Seattle')
9 - filter("D"."LOCATION_ID"="L"."LOCATION_ID")
> 8번 LOC_CITY_IDX가 9번 LOC 테이블 위에 위치하면서 3번과 조인? -> Batch I/O 발생
# Nested Loop Join + Inner 테이블의 Index 변경 : use_nl_with_index(l loc_idx)
HR@ora11g> select /*+ gather_plan_statistics leading(e,d,l) use_nl(d) use_nl(l) use_nl_with_index(l loc_idx) */
e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Seattle'
and e.job_id = 'AD_VP';
2 rows selected.
HR@ora11g> 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 | | 2 |00:00:00.01 | 12 |
| 1 | NESTED LOOPS | | 1 | 1 | 2 |00:00:00.01 | 12 |
| 2 | NESTED LOOPS | | 1 | 6 | 2 |00:00:00.01 | 10 |
| 3 | NESTED LOOPS | | 1 | 6 | 2 |00:00:00.01 | 8 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 6 | 2 |00:00:00.01 | 4 |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IDX | 1 | 6 | 2 |00:00:00.01 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 2 | 1 | 2 |00:00:00.01 | 4 |
|* 7 | INDEX UNIQUE SCAN | DEPT_IDX | 2 | 1 | 2 |00:00:00.01 | 2 |
|* 8 | INDEX UNIQUE SCAN | LOC_IDX | 2 | 1 | 2 |00:00:00.01 | 2 |
|* 9 | TABLE ACCESS BY INDEX ROWID | LOC | 2 | 1 | 2 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("E"."JOB_ID"='AD_VP')
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
9 - filter("L"."CITY"='Seattle')
1
/ \
조인결과 집합→ 2 9
/ \
조인결과 집합→ 3 8
/ \
4 6
| |
5 7
| # 예시 4 |
# leading(e,d) use_nl(d)
HR@ora11g> select /*+ gather_plan_statistics leading(e,d) use_nl(d) */
e.employee_id, e.job_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;
106 rows selected.
HR@ora11g> 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 | | 106 |00:00:00.01 | 129 |
| 1 | NESTED LOOPS | | 1 | 106 | 106 |00:00:00.01 | 129 |
| 2 | NESTED LOOPS | | 1 | 107 | 106 |00:00:00.01 | 23 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 13 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 107 | 1 | 106 |00:00:00.01 | 10 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 106 | 1 | 106 |00:00:00.01 | 106 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> d.department_id : outer : 중복성이 없는 컬럼
# leading(d,e) use_nl(e)
HR@ora11g> select /*+ gather_plan_statistics leading(d,e) use_nl(e) */
e.employee_id, e.job_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;
HR@ora11g> 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 | | 106 |00:00:00.01 | 37 |
| 1 | NESTED LOOPS | | 1 | 106 | 106 |00:00:00.01 | 37 |
| 2 | NESTED LOOPS | | 1 | 270 | 106 |00:00:00.01 | 24 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 11 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 27 | 10 | 106 |00:00:00.01 | 13 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 106 | 4 | 106 |00:00:00.01 | 13 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> Buffer Pinning 작동
> 애당초 비조인성격이 없는 조인조건문은 Nested Loop Join으로 하면 안된다.
■ Sort Merge Join
- 조인되는 건수가 많을 때 유리하다.
- sort에 대한 성능 문제가 발생할 수 있다.
- use_merge(), leading()
- 단계
1. 각각의 조인 키 컬럼을 기준으로 양쪽 집합을 정렬한다.
2. 정렬된 양쪽 집합을 서로 merge 한다.
- First, Second
- sort_area_size
| # 예시 5 |
HR@ora11g> select /*+ gather_plan_statistics */ e.employee_id, e.job_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem |1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 18 | | | |
| 1 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.01 | 18 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 12 | | | |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 6 | | | |
|* 4 | SORT JOIN | | 27 | 107 | 106 |00:00:00.01 | 6 | 6144 |6144 | 6144 (0)|
| 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> 2 : First
> 4 : Second
↓
First Table
HR@ora11g> select department_id, department_name
from departments
order by department_id;
Second Table : department_id 중복성이 많은 곳이라 범위검색하기 좋다
HR@ora11g> select department_id, employee_id, job_id
from employees
order by department_id;
# leading(e,d) use_merge(d)
HR@ora11g> select /*+ gather_plan_statistics leading(e,d) use_merge(d) */
e.employee_id, e.job_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 12 | | | |
| 1 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.01 | 12 | | | |
| 2 | SORT JOIN | | 1 | 107 | 107 |00:00:00.01 | 6 | 6144 | 6144 | 6144 (0)|
| 3 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
|* 4 | SORT JOIN | | 107 | 27 | 106 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 6 | | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> 버퍼는 줄었지만 메모리 사용량이 늘어 남
# leading(e,d,l) use_merge(d) use_merge(l)
HR@ora11g> select /*+ gather_plan_statistics leading(e,d,l) use_merge(d) use_merge(l) */
e.employee_id, e.job_id, d.department_name, l.city, l.street_address
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id;
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 18 | | | |
| 1 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.01 | 18 | | | |
| 2 | SORT JOIN | | 1 | 106 | 106 |00:00:00.01 | 12 | 9216 | 9216 | 8192 (0)|
| 3 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.01 | 12 | | | |
| 4 | SORT JOIN | | 1 | 107 | 107 |00:00:00.01 | 6 | 6144 | 6144 | 6144 (0)|
| 5 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
|* 6 | SORT JOIN | | 107 | 27 | 106 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 7 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 6 | | | |
|* 8 | SORT JOIN | | 106 | 23 | 106 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 9 | TABLE ACCESS FULL | LOCATIONS | 1 | 23 | 23 |00:00:00.01 | 6 | | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
filter("D"."LOCATION_ID"="L"."LOCATION_ID")
> 중복성있는 테이블을 우선으로하면 sort join 또 수행한다
# leading(l,d,e) use_merge(d) use_merge(e)
HR@ora11g> select /*+ gather_plan_statistics leading(l,d,e) use_merge(d) use_merge(e) */
e.employee_id, e.job_id, d.department_name, l.city, l.street_address
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id;
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 14 | | | |
| 1 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.01 | 14 | | | |
| 2 | SORT JOIN | | 1 | 27 | 27 |00:00:00.01 | 8 | 2048 | 2048 | 2048 (0)|
| 3 | MERGE JOIN | | 1 | 27 | 27 |00:00:00.01 | 8 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 23 | 19 |00:00:00.01 | 2 | | | |
| 5 | INDEX FULL SCAN | LOC_ID_PK | 1 | 23 | 19 |00:00:00.01 | 1 | | | |
|* 6 | SORT JOIN | | 19 | 27 | 27 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 7 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 6 | | | |
|* 8 | SORT JOIN | | 27 | 107 | 106 |00:00:00.01 | 6 | 6144 | 6144 | 6144 (0)|
| 9 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
filter("D"."LOCATION_ID"="L"."LOCATION_ID")
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> LOC_ID_PK 인덱스로 정렬이 되어 있어 Sort Join 대신 Full Scan
# leading(l,d,e) use_nl(d) use_nl(e)
HR@ora11g> select /*+ gather_plan_statistics leading(l,d,e) use_nl(d) use_nl(e) */
e.employee_id, e.job_id, d.department_name, l.city, l.street_address
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id;
106 rows selected.
HR@ora11g> 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 | | 106 |00:00:00.01 | 56 |
| 1 | NESTED LOOPS | | 1 | 106 | 106 |00:00:00.01 | 56 |
| 2 | NESTED LOOPS | | 1 | 270 | 106 |00:00:00.01 | 41 |
| 3 | NESTED LOOPS | | 1 | 27 | 27 |00:00:00.01 | 27 |
| 4 | TABLE ACCESS FULL | LOCATIONS | 1 | 23 | 23 |00:00:00.01 | 11 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 23 | 1 | 27 |00:00:00.01 | 16 |
|* 6 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 23 | 4 | 27 |00:00:00.01 | 11 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 27 | 10 | 106 |00:00:00.01 | 14 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 106 | 4 | 106 |00:00:00.01 | 15 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
| # 예시 6 |
HR@ora11g> drop table hr.emp purge;
Table dropped.
HR@ora11g> create table hr.emp nologging
as select rownum emp_id, last_name, first_name, hire_date, job_id, salary, department_id
from hr.employees, (select rownum emp_id from dual connect by level <= 1000);
Table created.# leading(e,d) use_merge(d)
HR@ora11g> select /*+ gather_plan_statistics leading(e,d) use_merge(d) */
e.emp_id, e.job_id, d.department_name
from emp e, dept d
where e.department_id = d.department_id;
106000 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106K|00:00:00.16 | 688 | 683 | | | |
| 1 | MERGE JOIN | | 1 | 118K| 106K|00:00:00.16 | 688 | 683 | | | |
| 2 | SORT JOIN | | 1 | 119K| 106K|00:00:00.11 | 686 | 683 | 3809K| 839K| 3385K (0)|
| 3 | TABLE ACCESS FULL| EMP | 1 | 119K| 107K|00:00:00.06 | 686 | 683 | | | |
|* 4 | SORT JOIN | | 106K| 27 | 106K|00:00:00.03 | 2 | 0 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL| DEPT | 1 | 27 | 27 |00:00:00.01 | 2 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> 시간 소요 > 악성코드의 지름길
# leading(d,e) use_merge(e)
HR@ora11g> select /*+ gather_plan_statistics leading(d,e) use_merge(e) */
e.emp_id, e.job_id, d.department_name
from emp e, dept d
where e.department_id = d.department_id;
106000 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106K|00:00:00.05 | 710 | 683 | | | |
| 1 | MERGE JOIN | | 1 | 118K| 106K|00:00:00.05 | 710 | 683 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 27 | 27 |00:00:00.01 | 24 | 0 | | | |
| 3 | INDEX FULL SCAN | DEPT_IDX | 1 | 27 | 27 |00:00:00.01 | 12 | 0 | | | |
|* 4 | SORT JOIN | | 27 | 119K| 106K|00:00:00.04 | 686 | 683 | 3809K| 839K| 3385K (0)|
| 5 | TABLE ACCESS FULL | EMP | 1 | 119K| 107K|00:00:00.01 | 686 | 683 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> 버퍼는 늘었지만 시간 단축이 더 효율적
# leading(e,l,d) use_merge(l) use_merge(d)
HR@ora11g> select /*+ gather_plan_statistics leading(e,l,d) use_merge(l) use_merge(d) */
e.emp_id, e.job_id, d.department_name, l.city, l.street_address
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id;
106000 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106K|00:00:14.69 | 1058 | 18805 | 18185 | | | | |
| 1 | MERGE JOIN | | 1 | 118K| 106K|00:00:14.69 | 1058 | 18805 | 18185 | | | | |
| 2 | SORT JOIN | | 1 | 2753K| 2438K|00:00:15.00 | 1056 | 18805 | 18185 | 159M| 4260K| 47M (1)| 143K|
| 3 | MERGE JOIN CARTESIAN| | 1 | 2753K| 2461K|00:00:00.42 | 688 | 683 | 0 | | | | |
| 4 | TABLE ACCESS FULL | EMP | 1 | 119K| 107K|00:00:00.02 | 686 | 683 | 0 | | | | |
| 5 | BUFFER SORT | | 107K| 23 | 2461K|00:00:00.19 | 2 | 0 | 0 | 2048 | 2048 | 2048 (0)| |
| 6 | TABLE ACCESS FULL | LOC | 1 | 23 | 23 |00:00:00.01 | 2 | 0 | 0 | | | | |
|* 7 | SORT JOIN | | 2438K| 27 | 106K|00:00:00.46 | 2 | 0 | 0 | 2048 | 2048 | 2048 (0)| |
| 8 | TABLE ACCESS FULL | DEPT | 1 | 27 | 27 |00:00:00.01 | 2 | 0 | 0 | | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "D"."LOCATION_ID"="L"."LOCATION_ID")
filter(("D"."LOCATION_ID"="L"."LOCATION_ID" AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"))
# leading(d,l,e) use_merge(l) use_merge(e)
HR@ora11g> select /*+ gather_plan_statistics leading(d,l,e) use_merge(l) use_merge(e) */
e.emp_id, e.job_id, d.department_name, l.city, l.street_address
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id;
106000 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106K|00:00:00.08 | 690 | 683 | | | |
| 1 | MERGE JOIN | | 1 | 118K| 106K|00:00:00.08 | 690 | 683 | | | |
| 2 | SORT JOIN | | 1 | 27 | 27 |00:00:00.01 | 4 | 0 | 2048 | 2048 | 2048 (0)|
| 3 | MERGE JOIN | | 1 | 27 | 27 |00:00:00.01 | 4 | 0 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 27 | 27 |00:00:00.01 | 2 | 0 | | | |
| 5 | INDEX FULL SCAN | DEPT_LOC_IDX | 1 | 27 | 27 |00:00:00.01 | 1 | 0 | | | |
|* 6 | SORT JOIN | | 27 | 23 | 27 |00:00:00.01 | 2 | 0 | 2048 | 2048 | 2048 (0)|
| 7 | TABLE ACCESS FULL | LOC | 1 | 23 | 23 |00:00:00.01 | 2 | 0 | | | |
|* 8 | SORT JOIN | | 27 | 119K| 106K|00:00:00.06 | 686 | 683 | 3809K| 839K| 3385K (0)|
| 9 | TABLE ACCESS FULL | EMP | 1 | 119K| 107K|00:00:00.01 | 686 | 683 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
filter("D"."LOCATION_ID"="L"."LOCATION_ID")
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
# leading(l,d,e) use_merge(d) use_merge(e)
HR@ora11g> select /*+ gather_plan_statistics leading(l,d,e) use_merge(d) use_merge(e) */
e.emp_id, e.job_id, d.department_name, l.city, l.street_address
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id;
106000 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106K|00:00:00.07 | 690 | 683 | | | |
| 1 | MERGE JOIN | | 1 | 118K| 106K|00:00:00.07 | 690 | 683 | | | |
| 2 | SORT JOIN | | 1 | 27 | 27 |00:00:00.01 | 4 | 0 | 2048 | 2048 | 2048 (0)|
| 3 | MERGE JOIN | | 1 | 27 | 27 |00:00:00.01 | 4 | 0 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| LOC | 1 | 23 | 19 |00:00:00.01 | 2 | 0 | | | |
| 5 | INDEX FULL SCAN | LOC_IDX | 1 | 23 | 19 |00:00:00.01 | 1 | 0 | | | |
|* 6 | SORT JOIN | | 19 | 27 | 27 |00:00:00.01 | 2 | 0 | 2048 | 2048 | 2048 (0)|
| 7 | TABLE ACCESS FULL | DEPT | 1 | 27 | 27 |00:00:00.01 | 2 | 0 | | | |
|* 8 | SORT JOIN | | 27 | 119K| 106K|00:00:00.06 | 686 | 683 | 3809K| 839K| 3385K (0)|
| 9 | TABLE ACCESS FULL | EMP | 1 | 119K| 107K|00:00:00.01 | 686 | 683 | | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
filter("D"."LOCATION_ID"="L"."LOCATION_ID")
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
# leading(l,d,e) use_merge(d) use_merge(e) -> TRACE
HR@ora11g> alter session set tracefile_identifier = 'merge';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select /*+ gather_plan_statistics leading(l,d,e) use_merge(d) use_merge(e)*/
e.emp_id, e.job_id, d.department_name, l.city, l.street_address
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id;
106000 rows selected.
HR@ora11g> alter session set events '10046 trace name context off';
Session altered.
HR@ora11g> !
[oracle@oracle diag]$ cd $ORACLE_BASE/diag/rdbms/ora11g/ora11g/trace
[oracle@oracle trace]$ ls *merge.trc
ora11g_ora_30198_merge.trc
[oracle@oracle trace]$ tkprof ora11g_ora_30198_merge.trc merge_30198.txt sys=no
[oracle@oracle trace]$ cat merge_30198.txt
...
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 7068 0.17 0.17 683 690 0 106000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7070 0.17 0.18 683 691 0 106000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
106000 106000 106000 MERGE JOIN (cr=690 pr=683 pw=0 time=149943 us cost=1267 size=10072075 card=118495)
27 27 27 SORT JOIN (cr=4 pr=0 pw=0 time=66 us cost=7 size=1404 card=27)
27 27 27 MERGE JOIN (cr=4 pr=0 pw=0 time=94 us cost=6 size=1404 card=27)
19 19 19 TABLE ACCESS BY INDEX ROWID LOC (cr=2 pr=0 pw=0 time=32 us cost=2 size=759 card=23)
19 19 19 INDEX FULL SCAN LOC_IDX (cr=1 pr=0 pw=0 time=8 us cost=1 size=0 card=23)(object id 88842)
27 27 27 SORT JOIN (cr=2 pr=0 pw=0 time=48 us cost=4 size=513 card=27)
27 27 27 TABLE ACCESS FULL DEPT (cr=2 pr=0 pw=0 time=11 us cost=3 size=513 card=27)
106000 106000 106000 SORT JOIN (cr=686 pr=683 pw=0 time=88567 us cost=1260 size=3951189 card=119733)
107000 107000 107000 TABLE ACCESS FULL EMP (cr=686 pr=683 pw=0 time=7808 us cost=193 size=3951189 card=119733)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 7068 0.00 0.00
direct path read 18 0.00 0.00
SQL*Net message from client 7068 1.12 5.18
********************************************************************************
'Oracle 11g > Tuning' 카테고리의 다른 글
| 89 Pushing Subquery, View Merging, Pushdown (0) | 2024.02.21 |
|---|---|
| 88 Hash Join, unnest, eliminate_join, SEMI JOIN, ANTI JOIN (0) | 2024.02.20 |
| 86 PGA, Sort Operation, Join, Table Prefetch, Batch I/O (0) | 2024.02.16 |
| 85 Row Chaining, Row Migration, B*tree, Bitmap Index, session_cached_cursors (0) | 2024.02.15 |
| 84 Serial direct read, Cluster Factor (0) | 2024.02.14 |