| # 요약 | |
| ■ 조건절 pushdown ■ 조건절 pullup # union # union all # lnnvl ■ or - expansion # use_concat # no_expand _remove_aggr_subquery ■ optimazer 1. Rule Based Optimization(RBO) 2. Cost Based Optimization(CBO) ■ optimizer_mode - choose - rule - all_rows - first_rows - first_rows_n(1,10,100,1000) SQL문 > PASER > Query Transformer > Estimator > plan Generator 1. Query Transformer 2. Estimator - selectivily - cardinality - cost 3. Plan Generator ■ optimizer에게 영향을 주는 요소 ■ Histogram |
|
■ 조건절 pushdown
- group by 절에 포함된 복합 뷰 merging에 실패 했을 경우에 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽으로 넣음으로써 group by 해야할 데이터 양을 줄일 수 있다.
HR@ora11g> select /*+ gather_plan_statistics */ *
from (select department_id, sum(salary) sum_sal
from employees
group by department_id)
where department_id = 20;
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 | 2 |
| 1 | SORT GROUP BY NOSORT | | 1 | 1 | 1 |00:00:00.01 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"=20)
> 불필요한 I/O 유발하는 SQL문 > Transformation 작동
■ 조건절 pullup
- 조건절을 쿼리 블록 안으로 밀어 넣을 뿐만 아니라 안쪽에 있는 조건들을 바깥쪽으로 끄집어 내기도하는데 이를 조건절 pullup 이라고 한다.
HR@ora11g> select /*+ gather_plan_statistics */ a.department_id, a.sum_sal, b.max_sal, b.min_sal
from (select department_id, sum(salary) sum_sal
from employees
where department_id = 20
group by department_id) a,
(select department_id, max(salary) max_sal, min(salary) min_sal
from employees
group by department_id) b
where a.department_id = b.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 | | 1 |00:00:00.01 | 4 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 4 | 1280K| 1280K| 375K (0)|
| 2 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 3 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 2 | 943K| 943K| 691K (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 | | | |
| 6 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 7 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 2 | 833K| 833K| 694K (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 | | | |
|* 9 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
5 - access("DEPARTMENT_ID"=20)
9 - access("DEPARTMENT_ID"=20)
|| 동일
HR@ora11g> select /*+ gather_plan_statistics */ a.department_id, a.sum_sal, b.max_sal, b.min_sal
from (select department_id, sum(salary) sum_sal
from employees
where department_id = 20
group by department_id) a,
(select department_id, max(salary) max_sal, min(salary) min_sal
from employees
where department_id = 20 --
group by department_id) b
where a.department_id = b.department_id;
> 결국은 department_id = 20 구하기
# 대용량 테이블 + Full Scan + OR
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where job_id = 'IT_PROG' or department_id = 20;
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 | | 7 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 8 | 7 |00:00:00.01 | 7 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("JOB_ID"='IT_PROG' OR "DEPARTMENT_ID"=20))
> or 사용으로 Full Scan
↓ 분리
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where job_id = 'IT_PROG';
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 | | 5 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 6 | 5 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 6 | 5 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='IT_PROG')
+
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where department_id = 20;
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 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=20)
↓ or 보다는 union
# union
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where job_id = 'IT_PROG'
union
select *
from employees
where department_id = 20;
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 | | 7 |00:00:00.01 | 4 | | | |
| 1 | SORT UNIQUE | | 1 | 8 | 7 |00:00:00.01 | 4 | 2048 | 2048 | 2048 (0)|
| 2 | UNION-ALL | | 1 | | 7 |00:00:00.01 | 4 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 6 | 5 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 6 | 5 |00:00:00.01 | 1 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 | | | |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 | | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("JOB_ID"='IT_PROG')
6 - access("DEPARTMENT_ID"=20)
> 단점은 sort 작동
# union all
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where job_id = 'IT_PROG'
union all
select *
from employees
where department_id = 20;
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 | | 7 |00:00:00.01 | 6 |
| 1 | UNION-ALL | | 1 | | 7 |00:00:00.01 | 6 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 6 | 5 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 6 | 5 |00:00:00.01 | 2 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("JOB_ID"='IT_PROG')
5 - access("DEPARTMENT_ID"=20)
-> 단점은 중복데이터 발생
↓ 중복데이터 제외
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where job_id = 'IT_PROG'
and (department_id <> 20 or department_id is null);
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 | | 5 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 5 | 5 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 6 | 5 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("DEPARTMENT_ID"<>20 OR "DEPARTMENT_ID" IS NULL))
2 - access("JOB_ID"='IT_PROG')
> 우선순위 주의 : and > or
+
# lnnvl : 부정 or null값 찾기
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where job_id = 'IT_PROG'
and lnnvl(department_id = 20); -- ("DEPARTMENT_ID"<>20 OR "DEPARTMENT_ID" IS NULL)
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 | | 5 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 6 | 5 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 6 | 5 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LNNVL("DEPARTMENT_ID"=20)
2 - access("JOB_ID"='IT_PROG')
↓ 개선
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where job_id = 'IT_PROG'
and lnnvl(department_id = 20)
union all
select *
from employees
where department_id = 20;
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 | | 7 |00:00:00.01 | 6 |
| 1 | UNION-ALL | | 1 | | 7 |00:00:00.01 | 6 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 6 | 5 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 6 | 5 |00:00:00.01 | 2 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(LNNVL("DEPARTMENT_ID"=20))
3 - access("JOB_ID"='IT_PROG')
5 - access("DEPARTMENT_ID"=20)
|| 동일. lnnvl 사용하지 않은 SQL문
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where job_id = 'IT_PROG'
and (department_id <> 20 or department_id is null)
union all
select *
from employees
where department_id = 20;
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 | | 7 |00:00:00.01 | 6 |
| 1 | UNION-ALL | | 1 | | 7 |00:00:00.01 | 6 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 5 | 5 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 6 | 5 |00:00:00.01 | 2 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("DEPARTMENT_ID"<>20 OR "DEPARTMENT_ID" IS NULL))
3 - access("JOB_ID"='IT_PROG')
5 - access("DEPARTMENT_ID"=20)
■ or - expansion
- 사용자의 쿼리를 직접 바꾸지 않아도 옵티마이저가 or 조건을 full scan 처리가 아닌 index range scan을 수행하면서
union all 형태로 변경 처리해주는 기능
- Hint : use_concat : or - expansion을 유도한다.
no_expand : or - expansion을 유도하지 않겠다. -> Full Scan
# 기본
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where job_id = 'IT_PROG' or department_id = 20;
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 | | 7 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 8 | 7 |00:00:00.01 | 7 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("JOB_ID"='IT_PROG' OR "DEPARTMENT_ID"=20))
> job_id, department_id 인덱스가 걸려있는데 or 사용으로 Full Scan
# use_concat
HR@ora11g> select /*+ gather_plan_statistics use_concat */ *
from employees
where job_id = 'IT_PROG' or department_id = 20;
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 | | 7 |00:00:00.01 | 6 |
| 1 | CONCATENATION | | 1 | | 7 |00:00:00.01 | 6 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 6 | 5 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 6 | 5 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"=20)
4 - filter(LNNVL("DEPARTMENT_ID"=20))
5 - access("JOB_ID"='IT_PROG')
> CONCATENATION : union all 수행
# no_expand
HR@ora11g> select /*+ gather_plan_statistics no_expand */ *
from employees
where job_id = 'IT_PROG' or department_id = 20;
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 | | 7 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 8 | 7 |00:00:00.01 | 7 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("JOB_ID"='IT_PROG' OR "DEPARTMENT_ID"=20))
> 데이터 분포도에 따른 영향으로 인덱스 스캔이 무조건 좋은것만은 아니다.
HR@ora11g> select department_id, count(*) from hr.employees group by department_id order by 2 desc;
DEPARTMENT_ID COUNT(*)
------------- ----------
50 45
80 34
100 6
30 6
60 5
90 3
110 2
20 2
40 1
1
10 1
70 1
> 값의 분포도가 고르지않다.
# 직업별 그룹화
HR@ora11g> select job_id, count(*) from hr.employees group by job_id order by 2 desc
19 rows selected.
> 해당 데이터도 값의 분포도가 고르지않다.HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where job_id = 'IT_PROG' or department_id = 50;
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 | | 50 |00:00:00.01 | 10 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 48 | 50 |00:00:00.01 | 10 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("DEPARTMENT_ID"=50 OR "JOB_ID"='IT_PROG'))
HR@ora11g> select department_id, count(*) from hr.employees group by department_id order by 2 desc;
DEPARTMENT_ID COUNT(*)
------------- ----------
50 45
80 34
100 6
30 6
60 5
90 3
110 2
20 2
40 1
1
10 1
70 1
> 값의 분포도가 고르지않다.
HR@ora11g> select job_id, count(*) from hr.employees group by job_id order by 2 desc
19 rows selected.
> 해당 데이터도 값의 분포도가 고르지않다.
↓
# use_concat
HR@ora11g> select /*+ gather_plan_statistics use_concat */ *
from employees
where job_id = 'IT_PROG' or department_id = 50;
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 | | 50 |00:00:00.01 | 13 |
| 1 | CONCATENATION | | 1 | | 50 |00:00:00.01 | 13 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 6 | 5 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 6 | 5 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 43 | 45 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("JOB_ID"='IT_PROG')
4 - filter(("DEPARTMENT_ID"=50 AND LNNVL("JOB_ID"='IT_PROG')))
> 값의 분포도가 좋지 않아 오라클이 Full Scan 작동
↓
# union all
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where job_id = 'IT_PROG'
and lnnvl(department_id = 50)
union all
select /*+ index(e EMP_DEPARTMENT_IX) */ *
from employees
where department_id = 50;
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 | | 50 |00:00:00.01 | 13 |
| 1 | UNION-ALL | | 1 | | 50 |00:00:00.01 | 13 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 3 | 5 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 6 | 5 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(LNNVL("DEPARTMENT_ID"=50))
3 - access("JOB_ID"='IT_PROG')
4 - filter("DEPARTMENT_ID"=50)
> 데이터가 적은 테이블은 Full Scan이 더 낫다
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where department_id = 10;
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 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=10)
HR@ora11g> select /*+ gather_plan_statistics index(e EMP_DEPARTMENT_IX) */ *
from employees e
where department_id = 50;
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 | | 45 |00:00:00.01 | 9 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 9 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 45 | 45 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=50)
# 자신의 부서 평균 급여보다 더 많이 받는 사원들의 employee_id, salary, department_name을 출력해주세요.
HR@ora11g> select /*+ gather_plan_statistics */ e.employee_id, e.salary, d.department_name
from employees e, departments d
where e.department_id = d.department_id
and e.salary > (select avg(salary)
from employees
where 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 | | 39 |00:00:00.01 | 8 | | | |
|* 1 | VIEW | VW_WIF_1 | 1 | 106 | 39 |00:00:00.01 | 8 | | | |
| 2 | WINDOW BUFFER | | 1 | 106 | 106 |00:00:00.01 | 8 | 9216 | 9216 | 8192 (0)|
| 3 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.01 | 8 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 2 | | | |
| 5 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 1 | | | |
|* 6 | SORT JOIN | | 27 | 107 | 106 |00:00:00.01 | 6 | 9216 | 9216 | 8192 (0)|
| 7 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("VW_COL_4" IS NOT NULL)
6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> WINDOW BUFFER : 분석함수 사용
> transformation 작동
↓
# 히든 파라미터 수정
HR@ora11g> alter session set "_remove_aggr_subquery" = false;
Session altered.
HR@ora11g> select /*+ gather_plan_statistics */ e.employee_id, e.salary, d.department_name
from employees e, departments d
where e.department_id = d.department_id
and e.salary > (select avg(salary)
from employees
where 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 | | 39 |00:00:00.01 | 17 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 39 |00:00:00.01 | 17 | 976K| 976K| 838K (0)|
| 2 | MERGE JOIN | | 1 | 11 | 11 |00:00:00.01 | 8 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 2 | | | |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 1 | | | |
|* 5 | SORT JOIN | | 27 | 11 | 11 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 6 | VIEW | VW_SQ_1 | 1 | 11 | 12 |00:00:00.01 | 6 | | | |
| 7 | HASH GROUP BY | | 1 | 11 | 12 |00:00:00.01 | 6 | 889K| 889K| 1663K (0)|
| 8 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
| 9 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 9 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."SALARY">"AVG(SALARY)")
5 - access("ITEM_1"="D"."DEPARTMENT_ID")
filter("ITEM_1"="D"."DEPARTMENT_ID")
> 버퍼 늘어남, HASH GROUP BY
|| 동일
HR@ora11g> select /*+ gather_plan_statistics */ e2.employee_id, e2.last_name, d.department_name
from (select department_id, avg(salary) avg_sal
from employees
group by department_id) e1,
employees e2, departments d
where e1.department_id = d.department_id
and e2.department_id = d.department_id
and e2.salary > e1.avg_sal;
↓ 최종
# _remove_aggr_subquery
- crrelate subquery(상호관련서브쿼리)를 사용할 때 분석함수를 사용하여 서브쿼리를 제거한다.(10g)
HR@ora11g> alter session set "_remove_aggr_subquery" = true;
Session altered.
HR@ora11g> select /*+ gather_plan_statistics */ e.employee_id, e.salary, d.department_name
from employees e, departments d
where e.department_id = d.department_id
and e.salary > (select avg(salary)
from employees
where 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 | | 39 |00:00:00.01 | 8 | | | |
|* 1 | VIEW | VW_WIF_1 | 1 | 106 | 39 |00:00:00.01 | 8 | | | |
| 2 | WINDOW BUFFER | | 1 | 106 | 106 |00:00:00.01 | 8 | 9216 | 9216 | 8192 (0)|
| 3 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.01 | 8 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 2 | | | |
| 5 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 1 | | | |
|* 6 | SORT JOIN | | 27 | 107 | 106 |00:00:00.01 | 6 | 9216 | 9216 | 8192 (0)|
| 7 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("VW_COL_4" IS NOT NULL)
6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
■ optimazer
- 사용자가 요청한 SQL문을 가장 효율적이고 빠르게 수행할 수 있는 최저 비용의 처리경로를 선택해주는 엔진
1. Rule Based Optimization(RBO)
- 미리 정해진 규칙에 의한 순위에 따라 실행계획을 결정(순위 결정 방식)
- 데이터에 대한 통계 내지는 실제 SQL문을 수행할 경우에 소요될 비용에 대해 교려하지않음
예) 인덱스가 있다면 무조건 사용(테이블 크기, 인덱스의 효율등 무시)
- 10g부터는 RBO 지원 중단
- 조건절 컬럼에 인덱스가 있으면 무조건 인덱스 사용
- order by절에 사용된 컬럼에 인덱스가 있으면 무조건 인덱스 사용
- 부등호 조건의 인덱스 보다 between and 조건의 인덱스가 우선
예) select * from emp
where department_id >= 10 and salary between 1000 and 10000;
> >= 부등호보다 between~and 연산자 우선
> 1순위 Rowid Scan ... 15순위 Full Table Scan
2. Cost Based Optimization(CBO)
- Oracle 7부터 지원
- cost를 기반으로 SQL 최적화 수행
- 실제 SQL을 수행할 때 소요될 비용을 예측하고 그 값을 기준으로 실행 계획을 설정 -> 통계정보 필요
- Object 통계
- 테이블 통계 : dba_tables, sys.tab$
- 컬럼 통계 : dba_tab_columns, sys.col$
- 인덱스 통계 : dba_indexes, sys.ind$
- System 통계
- CPU 속도
- 디스크 I/O
■ optimizer_mode
- choose : 통계 정보가 있으면 all_rows, 통계정보가 없으면 rule (9i 기본값)
- rule : 통계정보와 상관 없이 RBO 사용 중
- all_rows : 전체 처리율의 최적화(10g 기본값), 정확한 결과값이 중요
DSS(의사결정시스템), batch성 업무가 있는 환경, Full Scan 유도
- first_rows : 최초 응답속도 최적화(빨리 찾기), OLTP환경, 인덱스 스캔 선호
- first_rows_n(1,10,100,1000) : 처음 결좌가 나올때까지의 시간을 줄이기 위해 최적화(n = 건)
n이 100을 넘으면 all_rows와 같다
SYS@ora11g> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
optimizer_mode string ALL_ROWS
# 시스템레벨 : first_rows를 하는건 위험성이있다.
alter system set optimzer_mode = first_rows;
# 세션레벨
alter session set optimzer_mode = first_rows_10;
HR@ora11g> select /*+ gather_plan_statistics */ *
from hr.employees
where department_id = 50;
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 | | 45 |00:00:00.01 | 9 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID"=50)
# first_rows(10)
HR@ora11g> select /*+ gather_plan_statistics first_rows(10) */ *
from hr.employees
where department_id = 50;
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 | | 45 |00:00:00.01 | 9 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 10 | 45 |00:00:00.01 | 9 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 45 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=50)
# all_rows : 속도무시, 정확한 계획으로 수행하달라.
HR@ora11g> select /*+ gather_plan_statistics all_rows */ *
from employees
where department_id = 50;
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 | | 45 |00:00:00.01 | 9 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID"=50)
# all_rows(10) : 10건을 최대한 빨리 찾아라
HR@ora11g> select /*+ gather_plan_statistics first_rows(10) */ *
from employees
where department_id = 50;
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 | | 45 |00:00:00.01 | 9 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 10 | 45 |00:00:00.01 | 9 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 45 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=50
■ optimizer에게 영향을 주는 요소
- 오라클 버전
- 옵티마이저 관련 파라미터
- 통계정보
- 옵티마이저 힌트
- SQL과 연산자
- INDEX 설계
- 제약조건
SQL문 > PASER > Query Transformer > Estimator > plan Generator
1. Query Transformer : SQL을 최적화 하기 쉬운 형태로 변환
2. Estimator
- selectivily : 전체 대상 중 행에 특정한 조건에 의해 선택될것으로 예상되는 row 비율 (E-rows)
(1/num_distinct)
- cardinality : 특정 액세스 단계를 거치고나서 출력될 것으로 예상되는 결과 건수
(총 로우수 * 선택도(selectivily) = num_rows/num_distinct)
- cost : 특정 명령문을 실행하는데 필요한 표준화된 I/O에 대한 옵티마이저의 최적 예측 비용
- _optimizer_cost_model = {I/O|CPU|choose}
- I/O 비용 (8i) : 싱글io와 멀티io를 같은 선상에 둠
- CPU 비용 (10g)
- 단, 시스템 통계가 있으면 cpu 비용, 없으면 i/o 비용(9i)
3. Plan Generator : 후보군이 될만한 실행계획들을 생성하고, 그 중에서 수행비용이 가장 저렴한 계획을 리턴해준다.
HR@ora11g> drop table emp purge;
Table dropped.
HR@ora11g> create table emp as select * from employees;
Table created.
# 통계 수집
# method_opt=>'FOR ALL COLUMNS SIZE 1' : Histogram 생성하지 않겠다.
HR@ora11g> exec dbms_stats.gather_table_stats('HR','EMP',method_opt=>'FOR ALL COLUMNS SIZE 1')
PL/SQL procedure successfully completed.
HR@ora11g> select t.num_rows, c.column_name, c.num_nulls, c.num_distinct,
1/c.num_distinct selectivity, num_rows/c.num_distinct cardinality
from user_tables t, user_tab_columns c
where t.table_name = 'EMP'
and c.table_name = t.table_name;
NUM_ROWS COLUMN_NAME NUM_NULLS NUM_DISTINCT SELECTIVITY CARDINALITY
---------- -------------------- ---------- ------------ ----------- -----------
107 EMPLOYEE_ID 0 107 .009345794 1
107 FIRST_NAME 0 91 .010989011 1.17582418
107 LAST_NAME 0 102 .009803922 1.04901961
107 EMAIL 0 107 .009345794 1
107 PHONE_NUMBER 0 107 .009345794 1
107 HIRE_DATE 0 98 .010204082 1.09183673
107 JOB_ID 0 19 .052631579 5.63157895
107 SALARY 0 59 .016949153 1.81355932
107 COMMISSION_PCT 72 7 .142857143 15.2857143
107 MANAGER_ID 1 18 .055555556 5.94444444
107 DEPARTMENT_ID 1 11 .090909091 9.72727273HR@ora11g> select job_id, count(*) from emp group by job_id;
JOB_ID COUNT(*)
---------- ----------
AC_MGR 1
AC_ACCOUNT 1
IT_PROG 5
ST_MAN 5
AD_ASST 1
PU_MAN 1
SH_CLERK 20
AD_VP 2
FI_ACCOUNT 5
MK_MAN 1
PR_REP 1
FI_MGR 1
PU_CLERK 5
SA_MAN 5
MK_REP 1
AD_PRES 1
SA_REP 30
HR_REP 1
ST_CLERK 20
# 플랜정보만 보기
HR@ora11g> explain plan for select * from emp where job_id = 'IT_PROG';
Explained.
HR@ora11g> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 414 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 6 | 414 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
ㄴ cardinality
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"='IT_PROG')
> selectivity = 1/num_distinct = 1/19 = .052631579
> cardinality = num_rows * selectivity = 107 * .052631579 = 5.63157895 = 6HR@ora11g> explain plan for select * from emp where job_id = 'SA_REP';
Explained.
HR@ora11g> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 414 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 6 | 414 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"='SA_REP')
> 컬럼별 그룹핑해야 값의 분포도 확인 가능 -> 전체 107건 job_id 칼럼의 고유값 19건
-> 값의 분포도가 고르지 않을 경우 -> 히스토그램을 생성해야 정확한 selectivity, cardinality 확인 가능하다.
■ Histogram
- 조건절에 자주 사용되는 컬럼들 중에 값의 분포도가 균일하지 않은 컬럼에 히스토그램을 생성한다.
- 히스토그램의 유형
- 도수분포히스토그램(Frequency) : 값의 수 = 버킷 개수(max 254)
- 높이균형히스토그램(Height-Balanced) : 값의 수 > 버킷 개수
- SIZE [integer | repeat | auto | skewonly]
- integer : 버킷개수를 설정 (1~254)
method_opt=>'FOR ALL COLUMNS SIZE 254' -- 모든 컬럼에 히스토그램 생성
method_opt=>'FOR COLUMNS SIZE 20 JOB_ID' -- JOB_ID 칼럼에 버킷 20개 만들고 히스토그램 생성
method_opt=>'FOR COLUMNS JOB_ID SIZE 20, DEPARTMENT_ID SIZE 12' -- 칼럼별 버킷 개수 설정
method_opt=>'FOR COLUMNS SIZE 20 JOB_ID, DEPARTMENT_ID' -- 여러 칼럼에 동일한 버킷 개수 설정
- repeat : 히스토그램이 기존에 생성되있던 컬럼에만 히스토그램 수집
method_opt=>'FOR ALL COLUMNS SIZE repeat'
- auto : 컬럼이 조건절에 사용되는 비중이 높은 컬럼(sys.col_usage$, col$)을 찾아 히스토그램 생성,
데이터 분포를 기준으로 자동결정
method_opt=>'FOR ALL COLUMNS SIZE auto'
- skewonly : 데이터 분포를 분석해 균일하지 않은 컬럼에 대해서 히스토그램 생성
# method_opt=>'FOR COLUMNS SIZE 20 JOB_ID'
HR@ora11g> exec dbms_stats.gather_table_stats('HR','EMP',method_opt=>'FOR COLUMNS SIZE 20 JOB_ID')
PL/SQL procedure successfully completed.
HR@ora11g> select column_name, num_distinct, num_buckets, histogram
from user_tab_col_statistics
where table_name = 'EMP';
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
-------------------- ------------ ----------- ---------------
EMPLOYEE_ID 107 1 NONE
FIRST_NAME 91 1 NONE
LAST_NAME 102 1 NONE
EMAIL 107 1 NONE
PHONE_NUMBER 107 1 NONE
HIRE_DATE 98 1 NONE
JOB_ID 19 19 FREQUENCY
SALARY 59 1 NONE
COMMISSION_PCT 7 1 NONE
MANAGER_ID 18 1 NONE
DEPARTMENT_ID 11 1 NONE
HR@ora11g> explain plan for select * from emp where job_id = 'IT_PROG';
Explained.
HR@ora11g> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 345 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 5 | 345 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"='IT_PROG')
HR@ora11g> explain plan for select * from emp where job_id = 'SA_REP';
Explained.
HR@ora11g> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 2070 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 30 | 2070 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"='SA_REP')
# 바인더 변수 사용 시
HR@ora11g> explain plan for select * from emp where job_id = :B1 ;
Explained.
HR@ora11g> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 414 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 6 | 414 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"=:B1)
> 바이너리 변수 처리 > 히스토그램을 무시한 결과값이 나온다.
> 값의 분포도가 좋지 않을 경우 변수처리는 안하는게 좋다.
> selectivity = 1/num_distinct
> cardinality = num_rows * selectivity
# job_id > :B1
HR@ora11g> explain plan for select * from emp where job_id > :B1 ;
Explained.
HR@ora11g> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 483 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 7 | 483 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID">:B1)
> selectivity = 0.05(5%) 오라클이 도저히 selectivity 계산을 할 수 없을 경우 5% 사용
> cardinality = num_rows * selectivity
# salary > :B1
HR@ora11g> explain plan for select * from emp where salary > :B1 ;
Explained.
HR@ora11g> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 345 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 5 | 345 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SALARY">TO_NUMBER(:B1))
> 히스토그램이 생성되지 않으면 오라클이 알아서 계산
# salary between :B1 and :B2
HR@ora11g> explain plan for select * from emp where salary between :B1 and :B2 ;
Explained.
HR@ora11g> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 207 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 207 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B2)>=TO_NUMBER(:B1))
2 - filter("SALARY">=TO_NUMBER(:B1) AND "SALARY"<=TO_NUMBER(:B2))
> 범위스캔
> selectivity = 0.025 히스토그램이 생성되지 않으면 오라클이 알아서 계산
> cardinality = num_rows * selectivity
# 비중이 높은 컬럼찾기
HR@ora11g> exec dbms_stats.gather_table_stats('HR','EMP',method_opt=>'FOR COLUMNS SIZE 20 JOB_ID')
HR@ora11g> select object_name,data_object_id from user_objects where object_name = 'EMP';
OBJECT_NAME DATA_OBJECT_ID
-------------------- --------------
EMP 88896
SYS@ora11g> select a.*, b.name
from sys.col_usage$ a, col$ b
where a.obj# = b.obj#
and a.intcol# = b.col#
and a.obj# = 88896;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP NAME
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- --------- ----------
88896 7 3 0 0 1 0 0 22-FEB-24 JOB_ID
88896 8 0 0 0 1 0 0 22-FEB-24 SALARY
'Oracle 11g > Tuning' 카테고리의 다른 글
| 92 Partition (1) | 2024.02.26 |
|---|---|
| 91 통계정보 (0) | 2024.02.23 |
| 89 Pushing Subquery, View Merging, Pushdown (0) | 2024.02.21 |
| 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 |