| # 요약 | |
| ■ PGA ■ 자동 PGA 메모리 관리 ■ sort operation ■ 조인 방법 1. nested loop join 2. sort merge join 3. hash join ■ table prefetch (9iR2) ■ batch I/O (11g) |
|
■ PGA
SQL 수행 도중 데이터 정렬이 필요할 때 오라클은 PGA 메모리 sort area를 할당하며 완료여부에 따라 두가지 유형으로 수행된다.
- 메모리 소트 (in memory sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료,
internal sort
- 디스크 소트 (to dist sort) : 할당받은 sort area 내에서 정렬을 완료하지 못해 디스크공간까지 사용,
external sort
sort area 내에서 데이터 정렬을 마무리하는 것이 최적이다.(optimal), 양이 많을 때는 정렬된 중간 결과집합(sort run)을
temporary tablespace의 temp segment에 임시 저장한다.
- optimal sort : sort 작업이 PGA의 sort_area_size 안에서 이루어지는 것
- onepass sort : sort 대상 집합(sort run)이 디스크에 한번만 쓰여지는 것
- multipass sort : sort 대상 집합(sort run)이 디스크에 여러번 쓰여짐으로써 디스크 I/O가 많이 발생하여 성능이 저하된다.
■ 자동 PGA 메모리 관리
- pga_aggregate_target 파라미터를 기반으로 작업영역에 할당되는 PGA 메모리 양이 동적으로 조정된다.
- 기본값 : 10MB 또는 SGA 크기의 20% 중에 더 큰 값으로 설정
alter session set workarea_size_policy = auto;
- 수동관리 (8i)
alter session set workarea_size_policy = manual;
alter session set sort_area_size = 1M;
alter session set hash_area_size = 1M;
alter session set create_bitmap_area_size = 1M;
alter session set bitmap_merge_area_size = 1M;
-- server process > pga > sql 작업영역 = workarea_size_policy
# 히든 파라미터 포함 정보
SYS@ora11g> select a.ksppinm name, b.ksppstvl value
from x$ksppi a, x$ksppsv b
where a.indx = b.indx
and lower(a.ksppinm) in ('workarea_size_policy','sort_area_size','_smm_max_size');
NAME VALUE
--------------------------------------------- --------------------------------------------------
sort_area_size 1048576 byte (1MB)
workarea_size_policy AUTO
_smm_max_size 48332 Kbyte (50MB)
# 각 서버 프로세스가 사용가능한 work area 최대 크기는 _smm_max_size 파리미터로 확인(KB단위)
HR@ora11g> create table emp_temp
as select * from employees,
(select rownum no from dual connect by level <= 1000);
Table created.
HR@ora11g> select count(*) from emp_temp;
COUNT(*)
----------
107000
HR@ora11g> select /*+ gather_plan_statistics */ *
from (select rownum as id, employee_id from emp_temp order by employee_id)
where id = 1;
ID EMPLOYEE_ID
---------- -----------
1 198
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 4yy8ff8yp198d, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from (select rownum as id,
employee_id from emp_temp order by employee_id) where id = 1
Plan hash value: 1963048918
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |Starts|E-Rows|A-Rows| A-Time |Buffers|Reads| OMem | 1Mem| Used-Mem |
--------------------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1| | 1 |00:00:00.02| 1148| 1145| | | |
|* 1| VIEW | | 1| 92220| 1 |00:00:00.02| 1148| 1145| | | |
| 2| SORT ORDER BY | | 1| 92220| 107K|00:00:00.03| 1148| 1145| 2887K| 760K| 2566K (0)|
| 3| COUNT | | 1| | 107K|00:00:00.02| 1148| 1145| | | |
| 4| TABLE ACCESS FULL| EMP_TEMP| 1| 92220| 107K|00:00:00.01| 1148| 1145| | | |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
- E-Rows : 각 operation이 끝났을 때 return되는 건수(예상치)
- A-Rows : 각 operation이 끝났을 때 return되는 건수(실제)
- A-Time : 실제 실행 시간
- Buffers : 각 operation이 disk에 메모리에서 읽은 block 수
- Reads : 각 operation이 disk에 읽은 block 수
- Writes : 각 operation이 disk에 write한 block 수
- OMem : optimal sort에 필요한 예상 정렬 작업영역의 크기
- 1Mem : one pass sort에 필요한 예상 정렬 작업영역의 크기
- Used-Mem : 실제로 사용된 정렬 작업 영역의 크기
(0) : optimal sort
(1) : onepass sort
(2~) : muntipass sort
- Used-Temp : 메모리가 부족하여 temporary space가 사용된 공간 크기(kb단위)
# workarea_size_policy = manual
HR@ora11g> alter session set workarea_size_policy = manual;
Session altered.
HR@ora11g> alter session set sort_area_size = 1048576;
Session altered.
HR@ora11g> select /*+ gather_plan_statistics */ *
from (select rownum as id, employee_id from emp_temp order by employee_id)
where id = 1;
ID EMPLOYEE_ID
---------- -----------
1 198
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 4yy8ff8yp198d, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ * from (select rownum as id,
employee_id from emp_temp order by employee_id) where id = 1
Plan hash value: 1963048918
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.18 | 1152 | 1358 | 213 | | | | |
|* 1 | VIEW | | 1 | 92220 | 1 |00:00:00.18 | 1152 | 1358 | 213 | | | | |
| 2 | SORT ORDER BY | | 1 | 92220 | 107K|00:00:00.18 | 1152 | 1358 | 213 | 1917K| 660K| 1009K (1)| 2048 |
| 3 | COUNT | | 1 | | 107K|00:00:00.02 | 1148 | 1145 | 0 | | | | |
| 4 | TABLE ACCESS FULL| EMP_TEMP | 1 | 92220 | 107K|00:00:00.01 | 1148 | 1145 | 0 | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
> 메모리를 작게 구성하니 onepass sort 발생, 2048kb 공간 사용
HR@ora11g> alter session set sort_area_size = 0;
Session altered.
>> 0으로 해도 오라클이 최저 크기로 만든다
HR@ora11g> select /*+ gather_plan_statistics */ *
from (select rownum as id, employee_id from emp_temp order by employee_id)
where id = 1;
ID EMPLOYEE_ID
---------- -----------
1 198
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 4yy8ff8yp198d, child number 2
-------------------------------------
select /*+ gather_plan_statistics */ * from (select rownum as id,
employee_id from emp_temp order by employee_id) where id = 1
Plan hash value: 1963048918
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |Starts|E-Rows|A-Rows | A-Time |Buffers|Reads|Writes|OMem |1Mem|Used-Mem |Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| | 1 |00:00:03.91| 1208| 1679| 534| | | | |
|* 1 | VIEW | | 1| 92220| 1 |00:00:03.91| 1208| 1679| 534| | | | |
| 2 | SORT ORDER BY | | 1| 92220| 107K|00:00:03.91| 1208| 1679| 534|2007K|670K|99328 (12| 3072 |
| 3 | COUNT | | 1| | 107K|00:00:00.02| 1148| 1145| 0| | | | |
| 4 | TABLE ACCESS FULL| EMP_TEMP| 1| 92220| 107K|00:00:00.01| 1148| 1145| 0| | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
> 0으로 설정해도 99328kb는 사용 됨, 12번 I/O 발생(multipass sort)
> temp 공간을 사용함으로써 Writes 발생
SYS@ora11g> select sql_id, child_number from v$sql
where sql_text like '%gather_plan_statistics%' and sql_text not like '%v$sql$';
SQL_ID CHILD_NUMBER
------------- ------------
4yy8ff8yp198d 0
4yy8ff8yp198d 1
4yy8ff8yp198d 2
# sql workarea 사용량
SYS@ora11g> select child_number, last_execution, last_memory_used, last_tempseg_size
from v$sql_workarea where sql_id = '4yy8ff8yp198d';
CHILD_NUMBER LAST_EXECUTION LAST_MEMORY_USED LAST_TEMPSEG_SIZE
------------ ---------------------------------------- ---------------- -----------------
2 12 PASSES 99328 3145728
1 1 PASS 1033216 2097152
0 OPTIMAL 2627584
HR@ora11g> alter session set sort_area_size = 0;
Session altered.
HR@ora11g> alter session set tracefile_identifier = 'sort';
Session altered.
HR@ora11g> alter session set events '10046 trace name context forever, level 8';
Session altered.
HR@ora11g> select *
from (select rownum as id, employee_id from emp_temp order by employee_id)
where id = 1;
ID EMPLOYEE_ID
---------- -----------
1 198
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 *sort.trc
ora11g_ora_21353_sort.trc
[oracle@oracle trace]$ tkprof ora11g_ora_21353_sort.trc sort_21353.txt sys=no
[oracle@oracle trace]$ cat sort_21353.txt
...
select *
from (select rownum as id, employee_id from emp_temp order by employee_id)
where id = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.07 0.84 1679 1148 60 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.07 0.84 1679 1150 60 1
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
---------- ---------- ---------- ---------------------------------------------------
1 1 1 VIEW (cr=1148 pr=1679 pw=534 time=840050 us cost=1531 size=2397720 card=92220)
107000 107000 107000 SORT ORDER BY (cr=1148 pr=1679 pw=534 time=843902 us cost=1531 size=1198860 card=92220)
107000 107000 107000 COUNT (cr=1148 pr=1145 pw=0 time=15499 us)
107000 107000 107000 TABLE ACCESS FULL EMP_TEMP (cr=1148 pr=1145 pw=0 time=6411 us cost=320 size=1198860 card=92220)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
direct path read 26 0.00 0.00
direct path write temp 534 0.02 0.79
direct path read temp 274 0.00 0.00
SQL*Net message from client 2 4.55 4.55
********************************************************************************
direct path read : 데이터버퍼캐시 사용안하고 커서로 바로 읽기 25번 I/O 발생
direct path write temp : 템프에 내린 I/O 수, 멀티패스 소트 때문에 발생
direct path read temp : 템프에서 읽은 I/O 수, 멀티패스 소트때문에 발생
■ sort operation
HR@ora11g> select /*+ gather_plan_statistics */ * from employees order by salary desc;
107 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID f02nc674zaj5s, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from employees order by salary desc
Plan hash value: 3447538987
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 6 | | | |
| 1 | SORT ORDER BY | | 1 | 107 | 107 |00:00:00.01 | 6 | 16384 | 16384 |14336 (0)|
| 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
---------------------------------------------------------------------------------------------------------------------
HR@ora11g> select /*+ gather_plan_statistics */ department_id, sum(salary) from employees group by department_id;
DEPARTMENT_ID SUM(SALARY)
------------- -----------
110 20308
100 51608
30 24900
7000
50 156400
40 6500
80 304500
20 20900
70 10000
90 36000
10 4400
60 28800
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID afpw0rwy4hmw2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ department_id, sum(salary) from employees group by department_id
Plan hash value: 1192169904
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 |00:00:00.01 | 6 | | | |
| 1 | HASH GROUP BY | | 1 | 11 | 12 |00:00:00.01 | 6 | 1078K| 1078K| 220K (0)|
| 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
---------------------------------------------------------------------------------------------------------------------
> HASH GROUP BY : 나누기를 통해 그룹화, 정렬X
HR@ora11g> select /*+ gather_plan_statistics */ distinct department_id from employees;
DEPARTMENT_ID
-------------
110
100
30
50
40
80
20
70
90
10
60
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 8sqgj5ks87pv6, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ distinct department_id from employees
Plan hash value: 3042654289
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 |00:00:00.01 | 6 | | | |
| 1 | HASH UNIQUE | | 1 | 11 | 12 |00:00:00.01 | 6 | 1159K| 1159K| 212K (0)|
| 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
---------------------------------------------------------------------------------------------------------------------
> HASH UNIQUE : hash 알고리즘, 정렬X
SYS@ora11g> select a.ksppinm name, b.ksppstvl value
from x$ksppi a, x$ksppsv b
where a.indx = b.indx
and lower(a.ksppinm) in ('_gby_hash_aggregation_enabled');
NAME VALUE
--------------------------------------------- --------------------------------------------------
_gby_hash_aggregation_enabled TRUE
# 해시가 아닌 소트 그룹바이로 사용하겠다 (세션 레벨)
HR@ora11g> alter session set "_gby_hash_aggregation_enabled" = false;
Session altered.
HR@ora11g> select /*+ gather_plan_statistics */ department_id, sum(salary) from employees group by department_id;
DEPARTMENT_ID SUM(SALARY)
------------- -----------
10 4400
20 20900
30 24900
40 6500
50 156400
60 28800
70 10000
80 304500
90 36000
100 51608
110 20308
7000
12 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID afpw0rwy4hmw2, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ department_id, sum(salary) from
employees group by department_id
Plan hash value: 2107619104
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 |00:00:00.01 | 6 | | | |
| 1 | SORT GROUP BY | | 1 | 11 | 12 |00:00:00.01 | 6 | 3072 | 3072 | 2048 (0)|
| 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
---------------------------------------------------------------------------------------------------------------------
HR@ora11g> select /*+ gather_plan_statistics */ distinct department_id from employees;
DEPARTMENT_ID
-------------
10
20
30
40
50
60
70
80
90
100
110
12 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 8sqgj5ks87pv6, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ distinct department_id from employees
Plan hash value: 4280862484
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 |00:00:00.01 | 6 | | | |
| 1 | SORT UNIQUE | | 1 | 11 | 12 |00:00:00.01 | 6 | 3072 | 3072 | 2048 (0)|
| 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
---------------------------------------------------------------------------------------------------------------------
#
HR@ora11g> alter session set "_gby_hash_aggregation_enabled" = true;
Session altered.
HR@ora11g> select /*+ gather_plan_statistics */ employee_id, last_name from employees where job_id = 'ST_CLERK'
minus
select employee_id, last_name from employees where salary > 10000;
20 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID a4fwd6r159wp2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ employee_id, last_name from
employees where job_id = 'ST_CLERK' minus select employee_id, last_name
from employees where salary > 10000
Plan hash value: 1571493292
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.01 | 8 | 1| | | |
| 1 | MINUS | | 1 | | 20 |00:00:00.01 | 8 | 1| | | |
| 2 | SORT UNIQUE | | 1 | 6 | 20 |00:00:00.01 | 2 | 1| 3072 | 3072 | 2048 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 6 | 20 |00:00:00.01 | 2 | 1| | | |
|* 4 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 6 | 20 |00:00:00.01 | 1 | 1| | | |
| 5 | SORT UNIQUE | | 1 | 50 | 14 |00:00:00.01 | 6 | 0| 3072 | 3072 | 2048 (0)|
|* 6 | TABLE ACCESS FULL | EMPLOYEES | 1 | 50 | 14 |00:00:00.01 | 6 | 0| | | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("JOB_ID"='ST_CLERK')
6 - filter("SALARY">10000)
HR@ora11g> select /*+ gather_plan_statistics */ employee_id, last_name from employees where job_id = 'ST_CLERK'
intersect
select employee_id, last_name from employees where salary > 10000;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID gyh4aznh5q5qp, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ employee_id, last_name from
employees where job_id = 'ST_CLERK' intersect select employee_id,
last_name from employees where salary > 10000
Plan hash value: 189309529
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 8 | | | |
| 1 | INTERSECTION | | 1 | | 0 |00:00:00.01 | 8 | | | |
| 2 | SORT UNIQUE | | 1 | 6 | 20 |00:00:00.01 | 2 | 3072 | 3072 | 2048 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 6 | 20 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 6 | 20 |00:00:00.01 | 1 | | | |
| 5 | SORT UNIQUE | | 1 | 50 | 14 |00:00:00.01 | 6 | 3072 | 3072 | 2048 (0)|
|* 6 | TABLE ACCESS FULL | EMPLOYEES | 1 | 50 | 14 |00:00:00.01 | 6 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("JOB_ID"='ST_CLERK')
6 - filter("SALARY">10000)
> 유니온, minus, intersect -> 소트 오퍼레이션 발생
- union -> union all + not exists
- minus -> not exists
- intersect -> exists
HR@ora11g> select /*+ gather_plan_statistics reading(d e) use_merge(d) */ e.*, d.*
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'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID cssar1xw2zj7u, child number 0
-------------------------------------
select /*+ gather_plan_statistics reading(d e) use_merge(d) */ e.*, d.*
from employees e, departments d where e.department_id = d.department_id
Plan hash value: 1343509718
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 18 | 2| | | |
| 1 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.01 | 18 | 2| | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 12 | 2| | | |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 6 | 1| | | |
|* 4 | SORT JOIN | | 27 | 107 | 106 |00:00:00.01 | 6 | 0| 16384 | 16384 |14336 (0)|
| 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | 0| | | |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
- 인덱스 생성 시 발생
HR@ora11g> create index hr.emp_idx on hr.emp(employee_id);
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
> 재귀호출 select employee_id
from hr.emp
order by employee_id;
> 내부 select 문장 작동하여 소트 오퍼레이션 발생
- 통계수집할 때
■ 실행계획
1. data access 방법
2. join 방법
3. join 순서
■ 조인 방법
1. nested loop join
- 조인의 건수가 적을 때 유리하다.
- 인덱스를 통해서 데이터를 액세스하는 조인
- use_nl
> random I/O
2. sort merge join
- 조인되는 건수가 많을 때 유리하다.
- sort에 대한 성능문제가 발생할 수 있다.
- use_merge
3. hash join
- 조인되는 건수가 많을 때 유리하다.
- hash 알고리즘이 수행된다.
- use_hash
■ 조인 순서에 관련된 힌트
1. ordered : from절에 나열된 테이블 순서대로 조인의 순서가 결정된다.
2. leading : leading 힌트 안에 나열한 테이블 순서대로 조인의 순서가 결정된다.
HR@ora11g> select /*+ gather_plan_statistics */ e.last_name, e.salary, e.job_id, d.department_name
from departments d, employees e
where d.department_id = e.department_id
and e.employee_id = 100;
LAST_NAME SALARY JOB_ID DEPARTMENT_NAME
------------------------- ---------- ---------- ------------------------------
King 2000 AD_PRES Executive
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID g4aan7ty7ayrh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ e.last_name, e.salary, e.job_id,
d.department_name from departments d, employees e where d.department_id
= e.department_id and e.employee_id = 100
Plan hash value: 2782876085
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 3 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."EMPLOYEE_ID"=100)
5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
HR@ora11g> select /*+ gather_plan_statistics use_nl(e,d) */ e.last_name, e.salary, e.job_id, d.department_name
from departments d, employees e
where d.department_id = e.department_id
and e.employee_id = 100;
LAST_NAME SALARY JOB_ID DEPARTMENT_NAME
------------------------- ---------- ---------- ------------------------------
King 2000 AD_PRES Executive
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID fmdgg9zhun78x, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_nl(e,d) */ e.last_name, e.salary,
e.job_id, d.department_name from departments d, employees e where
d.department_id = e.department_id and e.employee_id = 100
Plan hash value: 2782876085
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 3 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."EMPLOYEE_ID"=100) <- 메인, outer table / driving table
5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID") <- 서브, inner table / driven table
- INDEX UNIQUE SCAN : 1 : 데이터가 작아서 루프~리프가 한곳에 있어 I/O 1번 발생
- 해석 : 3 - 2 - 5 - 4 - 1, 높고 깊은 레벨 부터
employee_id = 100 -> employees -> d.department_id -> departments = I/O 4번
HR@ora11g> select /*+ gather_plan_statistics ordered use_nl(e,d) */
e.last_name, e.salary, e.job_id, d.department_name
from departments d, employees e
where d.department_id = e.department_id
and e.employee_id = 100;
LAST_NAME SALARY JOB_ID DEPARTMENT_NAME
------------------------- ---------- ---------- ------------------------------
King 2000 AD_PRES Executive
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 2hb85gda0v7nk, child number 0
-------------------------------------
select /*+ gather_plan_statistics ordered use_nl(e,d) */ e.last_name,
e.salary, e.job_id, d.department_name from departments d,
employees e where d.department_id = e.department_id
and e.employee_id = 100
Plan hash value: 3818396904
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 16 | 5 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 16 | 5 |
| 2 | NESTED LOOPS | | 1 | 270 | 1 |00:00:00.01 | 15 | 5 |
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 7 | 4 |
| 4 | BITMAP CONVERSION TO ROWIDS | | 27 | | 1 |00:00:00.01 | 8 | 1 |
| 5 | BITMAP AND | | 27 | | 1 |00:00:00.01 | 8 | 1 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | 27 | | 27 |00:00:00.01 | 4 | 0 |
|* 7 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 27 | 10 | 27 |00:00:00.01 | 4 | 0 |
| 8 | BITMAP CONVERSION FROM ROWIDS| | 27 | | 6 |00:00:00.01 | 4 | 1 |
|* 9 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 27 | 10 | 106 |00:00:00.01 | 4 | 1 |
| 10 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 1 | 1 |00:00:00.01 | 1 | 0 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("E"."EMPLOYEE_ID"=100)
9 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
- ordered : from 절 나열된 테이블 순서대로
- use_nl(d) : 이너쪽 테이블만 기술
HR@ora11g> select /*+ gather_plan_statistics leading(e,d) use_nl(d) */
e.last_name, e.salary, e.job_id, d.department_name
from departments d, employees e
where d.department_id = e.department_id
and e.employee_id = 100;
LAST_NAME SALARY JOB_ID DEPARTMENT_NAME
------------------------- ---------- ---------- ------------------------------
King 2000 AD_PRES Executive
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 2891shwry3v9x, child number 0
-------------------------------------
select /*+ gather_plan_statistics leading(e,d) use_nl(d) */
e.last_name, e.salary, e.job_id, d.department_name from
departments d, employees e where d.department_id =
e.department_id and e.employee_id = 100
Plan hash value: 2782876085
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 3 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."EMPLOYEE_ID"=100)
5 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
- leading(e,d) from 절 순서 상관없이 (아우터,이너) 순서를 표시
- use_nl(d) : 이너 테이블 표시
HR@ora11g> select /*+ gather_plan_statistics leading(e,d,l) use_nl(d,l) */
e.last_name, e.salary, e.job_id, d.department_name, l.city
from departments d, employees e, locations l
where d.department_id = e.department_id
and d.location_id = l.location_id
and e.employee_id = 100;
LAST_NAME SALARY JOB_ID DEPARTMENT_NAME CITY
------------------------- ---------- ---------- ------------------------------ ------------------------------
King 2000 AD_PRES Executive Seattle
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 6anfpughbgxy6, child number 0
-------------------------------------
select /*+ gather_plan_statistics leading(e,d,l) use_nl(d,l) */
e.last_name, e.salary, e.job_id, d.department_name, l.city
from departments d, employees e, locations l where
d.department_id = e.department_id and d.location_id =
l.location_id and e.employee_id = 100
Plan hash value: 2352596526
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 6 | 2 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 6 | 2 |
| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 4 | 0 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |
|* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 | 0 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 2 | 0 |
|* 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 | 0 |
| 7 | TABLE ACCESS BY INDEX ROWID | LOCATIONS | 1 | 1 | 1 |00:00:00.01 | 2 | 2 |
|* 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."EMPLOYEE_ID"=100)
6 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
- leading(e,d,l) use_nl(d,l) = leading(e,d,l) use_nl(d) use_nl(l)
- NESTED LOOPS 2번 : 안쪽부터 수행
- 4 - 3 - 6 - 5 - 2 - 8 - 7 - 1 - 0
1. 테이블의 인덱스 체크
HR@ora11g> select * from user_ind_columns where table_name in ('EMPLOYEES','DEPARTMENTS','LOCATIONS');
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ -------------------- -------------------- --------------- ------------- ----------- -------
DEPT_ID_PK DEPARTMENTS DEPARTMENT_ID 1 22 0 ASC
DEPT_LOCATION_IX DEPARTMENTS LOCATION_ID 1 22 0 ASC
2. 비조인조건술어에 해당하는 데이터의 건수
HR@ora11g> select count(*) from hr.departments where location_id = 2500;
COUNT(*)
----------
1
HR@ora11g> select /*+ gather_plan_statistics leading(d,e) use_nl(e) */
e.last_name, e.salary, e.job_id, d.department_name
from departments d, employees e
where e.department_id = d.department_id
and d.location_id = 2500;
34 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 0hafsb9tm89tm, child number 0
-------------------------------------
select /*+ gather_plan_statistics leading(d,e) use_nl(e) */
e.last_name, e.salary, e.job_id, d.department_name
from departments d, employees e where e.department_id =
d.department_id and d.location_id = 2500
Plan hash value: 432925905
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 34 |00:00:00.01 | 11 |
| 1 | NESTED LOOPS | | 1 | 10 | 34 |00:00:00.01 | 11 |
| 2 | NESTED LOOPS | | 1 | 10 | 34 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 34 |00:00:00.01 | 4 |
| 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 34 | 10 | 34 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."LOCATION_ID"=2500)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
outer : DEPARTMENTS
inner : EMPLOYEES
이너쪽 인덱스 EMP_DEPARTMENT_IX 부터 조인 > batch I/O
read2, buffer 2 : 디스크에서 읽으면서 메모리에 올림 총 2번 io 발생
■ table prefetch (10g)
- 디스크 I/O를 수행하려면 비용이 많이 들기 때문에 한번에 I/O call이 필요한 시점에 곧이어 읽을 가능성이 높은
block들을 data buffer cache에 미리 적재해 두는 기능
- inner쪽에 non unique index를 range scan 시에 발생한다.
- index range scan은 single block 발생하는 이때 발생하는 이벤트 db file sequential read 이벤트가 발생(피지컬IO)할 수 있는데 table prefetch 기능이 수행되면 db file paralle reads 이벤트가 발생한다.
# optimizer_features_enable('10.2.0.5') : 옵티마이저 버전 낮추기
HR@ora11g> select /*+ optimizer_features_enable('10.2.0.5')
gather_plan_statistics
leading(d,e)
use_nl(e)
*/
e.last_name, e.salary, e.job_id, d.department_name
from departments d, employees e
where e.department_id = d.department_id
and d.location_id = 2500;
34 rows selected.
- outer :
- inner :
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 268aj816su8bz, child number 0
-------------------------------------
select /*+ optimizer_features_enable('10.2.0.5')
gather_plan_statistics leading(d,e)
use_nl(e) */ e.last_name,
e.salary, e.job_id, d.department_name from departments d,
employees e where e.department_id = d.department_id
and d.location_id = 2500
Plan hash value: 1096811146
-------------------------------------------------------------------------------------------------------------
| 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 | EMPLOYEES | 1 | 10 | 34 |00:00:00.01 | 11 |
| 2 | NESTED LOOPS | | 1 | 10 | 36 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 34 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."LOCATION_ID"=2500)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
# 옵티마이저 버전 찾기
HR@ora11g> alter session set optimizer_features_enable = '9.2.100';
ERROR:
ORA-00096: invalid value 9.2.100 for parameter optimizer_features_enable, must be from among 11.2.0.4.1,
11.2.0.4, 11.2.0.3, 11.2.0.2, 11.2.0.1, 11.1.0.7, 11.1.0.6, 10.2.0.5, 10.2.0.4, 10.2.0.3, 10.2.0.2, 10.2.0.1,
10.1.0.5, 10.1.0.4, 10.1.0.3, 10.1.0, 9.2.0.8, 9.2.0, 9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4, 8.1.3, 8.1.0,
8.0.7, 8.0.6, 8.0.5, 8.0.4, 8.0.3, 8.0.0
HR@ora11g> select /*+ optimizer_features_enable('9.0.0')
gather_plan_statistics
leading(d,e)
use_nl(e)
*/
e.last_name, e.salary, e.job_id, d.department_name
from departments d, employees e
where e.department_id = d.department_id
and d.location_id = 2500;
34 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 88wq86y38ss7y, child number 0
-------------------------------------
select /*+ optimizer_features_enable('9.0.0')
gather_plan_statistics leading(d,e)
use_nl(e) */ e.last_name,
e.salary, e.job_id, d.department_name from departments d,
employees e where e.department_id = d.department_id
and d.location_id = 2500
Plan hash value: 2912831499
-----------------------------------------------------------------------------------------------------------
| 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| EMPLOYEES | 1 | 10 | 34 |00:00:00.01 | 15 |
| 2 | NESTED LOOPS | | 1 | 5 | 36 |00:00:00.01 | 11 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 7 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 34 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."LOCATION_ID"=2500)
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Note
-----
- cpu costing is off (consider enabling it)
HR@ora11g> select /*+ optimizer_features_enable('8.0.0')
gather_plan_statistics
leading(d,e)
use_nl(e)
*/
e.last_name, e.salary, e.job_id, d.department_name
from departments d, employees e
where e.department_id = d.department_id
and d.location_id = 2500;
34 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 1af2nkd13v4gt, child number 0
-------------------------------------
select /*+ optimizer_features_enable('8.0.0')
gather_plan_statistics leading(d,e)
use_nl(e) */ e.last_name,
e.salary, e.job_id, d.department_name from departments d,
employees e where e.department_id = d.department_id
and d.location_id = 2500
Plan hash value: 2912831499
-----------------------------------------------------------------------------------------------------------
| 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| EMPLOYEES | 1 | 10 | 34 |00:00:00.01 | 15 |
| 2 | NESTED LOOPS | | 1 | 10 | 36 |00:00:00.01 | 11 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 7 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 34 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."LOCATION_ID"=2500)
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
Note
-----
- cpu costing is off (consider enabling it)
■ batch I/O
- inner쪽 인덱스와 조인하면서 중간 결과 집합을 만든 후에 inner 쪽 테이블괄 일괄(batch) 처리한다.
- 힌트 : nlj_batching, no_nlj_batching
HR@ora11g> select /*+ gather_plan_statistics
leading(d,e)
no_nlj_batching(e)
*/
e.last_name, e.salary, e.job_id, d.department_name
from departments d, employees e
where e.department_id = d.department_id
and d.location_id = 2500;
34 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 | EMPLOYEES | 1 | 10 | 34 |00:00:00.01 | 11 |
| 2 | NESTED LOOPS | | 1 | 10 | 36 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 34 |00:00:00.01 | 4 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."LOCATION_ID"=2500)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> table prefetch
'Oracle 11g > Tuning' 카테고리의 다른 글
| 88 Hash Join, unnest, eliminate_join, SEMI JOIN, ANTI JOIN (0) | 2024.02.20 |
|---|---|
| 87 Nested Loop Join, Sort Merge Join (0) | 2024.02.19 |
| 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 |
| 83 Explain Plan, AutoTrace, v$sql_plan, Buffer Pinning, SQL TRACE (0) | 2024.02.13 |