2019 - 02 - 15 (금)
SGA & PGA
SGA
-
여러 프로세스가 동시에 접근하는 메모리 영역
-
모든 DB서버 프로세스가 공유하는 메모리 영역
-
모든 사용자가 공유하여 사용하는 메모리 영역
-
공유풀(라이브러리 캐시 + 데이터 사전 캐시), 데이터버퍼캐시, 리두로그버퍼 등으로 구성됨
-
SGA가 클수록 HDD 사용 필요성이 줄어들게 되므로 성능이 향상됨. (일종의 Cache)
PGA
-
인스턴스의 메모리 영역
-
사용자마다 개별적으로 할당되는 메모리 영역
-
정렬, 셰션, 커서, 변수에 관한 정보를 저장하는 공간
-
개별 서버 프로세스가 가지는 전용 메모리 영역
SQL 처리 과정
-
사용자의 SQL 문장 실행 : 오라클 서버측 리스너가 서버 프로세스로 SQL문장 전달
-
SQL 파싱 : 클라이언트에서 보내온 SQL 구문이 서버프로세스 PGA 내부의 Private SQL Area 에서 문법 체크를 우선 진행하고, 테이블 및 컬럼이 있는지, 해당 유저가 테이블 및 컬럼을 select할 권한이 있는지를 Data Dictionary를 통해 체크한다.
Shared pool 을 통해서 동일한 SQL 문장이 있다면 Library Cache 의 parse-tree와 Query Execution Plan을 가지고 와서 실행한다. (Soft Parsing이라고 함)
Cursor & Oracle Memory
-
커서는 SELECT 또는 DML 조작시 클라이언트 프로세스에서 참조하는 PGA또는 SGA의 Library Cache내 전용 SQL 영역을 가리키는 포인터 또는 핸들이다.
-
SQL 커서는 오라클 서버 운영모드가 Dedicate Server환경이면 PGA, MTS 환경이면 SGA의 Library Cache에 위치한다.
-
명시적 커서는 PL/SQL을 작성하는 개발자가 명시적으로 Declare 절에 선언하여 사용하는 커서이며 다중행을 다룰 때 사용한다.
Literal SQL & Bind Variable SQL (Soft Parsing / Hard Parsing)
리터럴 SQL 은 WHERE 절 작성시 비교값에 문자/상수 같은 상수값을 하드코딩으로 작성한 것이다.
Bind Variable SQL 은 WHERE 절의 특정값을 표시하는 자리에 바인드 변수 형태로 표시한 것을 말한다.
리터럴 SQL문을 많이 사용하면 하드파싱의 빈도를 높이게 되어 Library Cache 내에서 Cache 되는 SQL문들이 자주 age out 하게 되므로 주기를 빠르게 하고 Dictionary Cache의 사용률을
높이게 된다. 이러한 Shared SQL Area의 SQL문 중에서 리터럴 SQL 문들을 찾아서 Bind Variable을 이용한 방법으로 바꾸어야 성능향상에 도움이 된다.
튜닝 도구 : SQL AUTOTRACE
SET AUTOTRACE를 사용하기 위해서 PLAN_TABLE 이 존재해야하며 구문을 활성화 하기 위해 해준다.
SET AUTOTRACE OFF : 기본값으로 AUTOTRACE를 수행하지 않는다.
SET AUTOTRACE ON EXPLAIN : 실행계획만을 출력
SET AUTOTRACE ON STATISTICS : 통계정보만을 출력
SET AUTOTRACE ON : 옵티마이저의 실행계획과 통계정보, 질의의 실행 결과를 함께 출력
SET AUTOTRACE TRACEONLY [EXPLAIN] : SET AUTOTRACE ON과 유사하지만 옵티마이저의 실 행계획과 통계정보를 출력, 만약 EXPAIN이라고 하면 실행계획만 출력한다
SET AUTOTRACE ON
-- 비화성화 SET AUTOTRACE OFF
[DB_BLOCK_GETS 및 CONSISTENCE GET]
Consistent Mode : 읽기 일관성이 보장된 상태에서 데이터 블록을 읽는 것인데 대부분 SELECT 쿼리가 해당됨
쿼리가 시작된 시점을 기준으로 일관성있게 읽어 들이며 읽는 도중에 값이 바뀌어도 쿼리시자점의 값으로 읽는다.
그러므로 SCN의값을 확인하면서 읽는데 값이 변경되면 SCN 번호가 증가되므로 이 경우에는 Rollback Segment에서 과거의 블록을 읽는다.
AUTOTRACE에는 consistent gets로 표시된다.
튜닝 도구 : DBMS_XPLAN
사용법
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
sql>set autotrace off
sql>explain plan
for
(쿼리문)
select * from table(dbms_xplan.display);
실행계획 해석(predicate)
실행계획은 해당 SQL을 수행하기 위한 절차이며 오라클 옵티마이저에 의해 만들어 진다.
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 338 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 13 | 338 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 13 | 169 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP | 13 | 169 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
위와 같은 plan 에서 옆에 * 가 있으면 predicate 가 있다는 의미이다.
predicate란 인덱스 접근시의 컬럼 액세스 정보, 조인 정보, filter 정보를 각 Opreation 단위로 나 타낸 것이다.
access predicate : 데이터 블록을 어떤 방식으로 Access해서 읽었는지를 나타내는 것이다.
filter predicate : 데이터 블록을 읽고 나서 데이터를 어떻게 필터링 했는지를 나타낸다.
초기화 파라미터 OPTIMIZER_MODE
RBO : Rule based optimizer : rule exists (level 1 ~ 15) CBO : Cost based optimizer : (dbms_stats ==> statistics)
- OPTIMIZER_MODE
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
sql>SHOW OPTIMIZER_MODE
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_mode string ALL_ROWS
기본적으로 ALL_ROWS 모드이다. ALL_ROWS 는 통계정보의 유무와 상관없이 모든 SQL 문에 CBO를 이용하며 최대 처리량을 목표로한다.
- CHOOSE : RBO 혹은 CBO 를 하나 선택한다.
통계 정보의 이용가능 여부에 따라 결정하는데 통계정보가 이용 가능하면 CBO를 사용하고 불가능한 경우에는 RBO를 사용한다.
만약 데이터딕셔너리에 액세스 되는 테이블 중 적 어도 하나의 테이블에 대한 통계 정보가 있는 경우엔 CBO를 사용하며 최대 처리량(ALL_ROWS)을 목표로 최적화를 수행한다.
딕셔너리에 약간의 통계 정보만을 가지고 있다면 CBO가 사용될 수 있지만 옵티마이저는 통계 정보가 없는 부분에 대해서는 통계 정보를 추측해야 하며 이는 최선의 실행 계획이라기보다 차선 의 실행 계획이라고 볼 수 있다.
--CHOOSE 모드로 변경
ALTER SESSION SET OPTIMIZER_MODE =CHOOSE;ALTER SESSION SET OPTIMIZER_MODE =CHOOSE;
CBO와 RBO중 하나를 선택한다. 이용가능 여부에 따라 결정한다. 통계정보를 이용 가능하면 CBO 아니면 RBO를 사용한다. ALL_ROWS 와 마찬가지로 CBO를 사용하며 최대 처리량을 목표로 최적화를 수행한다.
-
FIRST_ROWS_(1 10 100 1000)
통계정보의 유무와 상관없이 모든 SQL문에 대해 CBO를 이용하며 처음 N개의 레코드를 가장 빨리 추출할 수 있는최적의 경로를 찾음
- FIRST_ROWS
옵티마이저는 첫번째 레코드의추출을 가장 빠르게 하는 최적의 경로를 찾는다.
- RULE
통계정보의 유무와 관계없이 모든 SQL에 RBO를 사용하도록 한다.
규칙 기반 옵티마이저 (RBO : Rule-Based Optimizer)
오라클 DB가 RBO로 동작하는 경우는 옵티마이저 모드가 RULE 이거나 CHOOSE 이면서 인덱스에 대한 통계정보가 없는 경우이다.
- 규칙 기반 옵티마이저의 우선 순위
- rowid에 의한 single row access
rowid에 의한 단일 행 검색이 우선순위가 가장 높다.
ex) select * from emp where rowid =’XXXX’;
- cluster join에 의한 single row access
같은 클러스터 내에 있는 테이블을 조인할 때 이용한다.
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
sql>select ename, dname, sal
from emp e, dept d
where e.deptno = d.deptno
and e.empno = 8888;
SELECT ename, job FROM emp WHERE ename = ‘신희준’;
WHERE절에 출현하는 칼럼에 인덱스가 있으면 무조건 인덱스를 이용한다.
인덱스를 이용하는 것 이 FULL SCAN하는 것보다 우선 순위가 높으므로 먼저 ENAME 인덱스를 이용하여 ‘신희준’ 을 랜덤 액세스 한 후 원본 테이블 레코드의 주소값인 ROWID를 찾는다.
다시 그 주소값(ROWID)을 이 용하여 원본 테이블 레코드에 접근 후 JOB 칼럼값을 추출하는 실행계획을 세운다.
사실 이 테이 블의 경우 데이터가 몇 건 안되니까 한번에 FULL SCAN하여 가지고 오는 것이 훨씬 빠른데도 인 덱스를 이용한다
이러한 경우 CBO가 더 효율적일 수 있게 된다.
비용 기반 옵티마이저 (CBO : Cost_Based Optimizer )
비용기반 옵티마이저(CBO)는 실제로 가장 빠르게 실행되는 방법의 Cost를 계산하여 실행 경로를 선택하는 방법으로 DB의 버전이 높아질수록 더욱 정교하게 발전되고 있는 방법이며 오라클에서 권장하고 있다.
하지만 옵티마이저가 가장 최선의 실행 경로를 찾기 위해서는 테이블의 크기, 레코드 수, 컬럼 값의 분포도와 같은 테이블의 통계정보(부가적인 정보)를 제공해 주어야 정상적으로 잘 동작한다 고 할 수 있다.
테이블이나 인덱스에 통계 정보가 있는지에 대한 여부는 USER_TABLES, DBA_TABLES, USER_INDEXES, DBA_INDEXES와 같은 뷰의 LAST_ANALYZED 컬럼의 값을 통해 알 수 있는데, NULL 값을 가지는 경우에는 통계 정보가 없다고 보면 된다
Optimizer Mode를 변경하는 힌트 (Rule)
EMP 테이블의 DEPTNO 칼럼에 현재 인덱스가 생성되지 않은 상태이다. RBO에서 조인시 칼럼인덱스가 없는쪽이 드라이빙 테이블이 되니 확인해 보라.
–EMP 테이블의 DEPTNO 칼럼에 인덱스가 있다면 옵티마이저가 사용하지 못하도록 숨기자.
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
--인덱스 확인
sql>desc user_indexes;
sql>ALTER INDEX IDX_EMP_DEPTNO INVISIBLE; -- 인덱스가 있어도 invisible로 하면 못잡는다. ( index를 invisible로 해놓고 쿼리를 돌리면 hash join이 됨 - CBO)
sql>SELECT /*+ RULE*/
e.empno,
e.ename,
d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
sql>select
a.table_name, a.index_name, a.visibility, a.last_analyzed,
b.column_name, b.column_position
from user_indexes a , user_ind_columns b
where a.table_name = b.table_name
and a.index_name = b.index_name
and a.table_name in ('EMP', 'DEPT')
;
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
sql> select table_name, last_analyzed from user_tables where table_name in ('EMP','DEPT');
TABLE_NAME LAST_ANALYZE
------------------------------------------------------------ ------------
DEPT 11-FEB-19
EMP 11-FEB-19
select *
from emp a, dept d
where a.deptno = d.deptno
-
테이블을 읽는 순서가 어떤게 맞을것인가?
-
FROM EMP, DEPT
-
FROM DEPT, EMP
-
EMP(ROW 14 column 8) DEPT(ROW 4 COLUMN 3)
–> 읽어야할 데이터가 dept가 훨신 적기 때문에 DEPT 부터 읽어야 함.
- 일반적으로는 뒤에서 부터 읽기 때문에 dept를읽고 emp를 읽는다. 어떤 경우에는 앞에 위치한 EMP를 읽고 DEPT를 읽는다.
예외:) DEPT INDEX가 있는데 EMP INDEX가 없을 경우에 EMP 테이블을 먼저 읽는다.
Optimizer Mode를 변경하는 힌트 (ALL_ROWS)
ALL_ROWS는 CBO의 기본값이며 전체 데이터를 가지고오는데 있어 비용이 가장 적게드는 실행계획을 만들어 내므로 FULL TABLE SCAN할 가능성이 많다. 그러므로 OLTP성업무보다 Batch성 업무에 적합하다.
ALL_ROWS 힌트가 제성능을 발휘하기 위해서 통계정보가 필요하다.
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
sql>SELECT /*+ ALL_ROWS*/ e.empno, e.ename, d.dname
FROM myemp1 e, mydept1 d
WHERE e.deptno = d.deptno;
Optimizer Mode 를 변경하는 힌트 (CHOOSE)
CHOOSE CBO의 ALL_ROWS 로 동작하지만 통계정보가 없다면 RBO가 된다.
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
sql>SET AUTOTRACE EXPLAIN
--통계정보 생성
sql>ANALYZE TABLE myemp1 COMPUTE STATISTICS;
실행계획 SQL 연산 (AND-EQUAL)
ROW 연산이며 인덱스에 의해 반환되는 값들(ROWID)을 병합해서 공통적인 값을 반환하는 연산이다.
실행계획 SQL 연산 (CONCATEATION)
반환된 로우를 유니온처럼 합산하는 연산
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
--테이블에 생성되어있는 인덱스 및칼럼 확인
sql>SELECT a.index_name, a.column_name b.visibility
FROM user_ind_columns a, user_indexes b
WHERE a.table_name = 'EMP'
AND a.index_name = b.index_name;
-- 인덱스가 없으면 생성
sql>create index idx_emp_job ON EMP(job);
sql>create index idx_emp_deptno on EMP(deptno);
-- RBO로 변경
sql>ALTER SESSION SET OPTIMIZER_MODE =RULE;
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
sql>select *
from emp
where job = 'SALESMAN' and DEPTNO IN(20,40)
;
Microsoft Windows [Version 10.0.17134.590]
(c) 2018 Microsoft Corporation. All rights reserved.
sql>SELECT *
FROM EMP
WHERE (JOB = 'SALESMAN' AND DEPTNO =20)
OR (JOB = 'SALESMAN' AND DEPTNO =40)
위 둘은 같은 실행계획을 가진다. (성능이 똑같다.)
Execution Plan
----------------------------------------------------------
Plan hash value: 3388938709
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | CONCATENATION | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP |
| 3 | AND-EQUAL | |
|* 4 | INDEX RANGE SCAN | IDX_EMP_DEPTNO |
|* 5 | INDEX RANGE SCAN | IDX_EMP_JOB |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP |
| 7 | AND-EQUAL | |
|* 8 | INDEX RANGE SCAN | IDX_EMP_DEPTNO |
|* 9 | INDEX RANGE SCAN | IDX_EMP_JOB |
-------------------------------------------------------
위 같은 결과가 나온다.
이 때 OR 확장(CONCATENATION) 을 막기위해 no_expand 힌트를 사용한다.
select /*+ no_expand */ *
from emp
WHERE job = 'SALESMAN'
AND DEPTNO IN(20,40);
Execution Plan
----------------------------------------------------------
Plan hash value: 3286489891
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP_JOB | 3 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=20 OR "DEPTNO"=40)
2 - access("JOB"='SALESMAN')
PREDICATE를 보면 DEPTNO가 20인 데이터를 인덱스를 이용하여 추출하며 JOB이 ‘SALESMAN’이면서 DEPTNO 가 40인 데이터를 서로합산하여 결과를 만들어냄을 알 수 있다.
실행계획 SQL 연산(COUNT)
COUNT 연산은 PSEUDO COLUMNS(의사 칼럼)이 WHERE절이 아닌 SELECT 문장에 나타날 때 실행계획에 나타나는 SQL연산이다.
실행계획 SQL 연산 (FILTER)
SQL ROW 연산인데 WHERE 조건 절에서 인덱스를 사용하지 못할 때 발생한다. NESTED LOOP 방식으로 해석할 수 있는데 서브쿼리라면 메인쿼리 로우를 하나씩 읽을 때 마다 서브쿼리를 한번씩 실행하는 형태이다.
FILTER OPERATION은 IN, NOT IN, EXISTS, NOT EXISTS 를 사용하는 경우 발견할 수 있는 OPERATION 이며 NESTED LOOP JOIN과 유사하다.
SELECT /*+ RULE */ ENAME, SAL, JOB
FROM MYEMP1 A
WHERE SAL = (SELECT MIN(SAL)
FROM MYEMP1 B
WHERE B.DEPTNO = A.DEPTNO);
Execution Plan
----------------------------------------------------------
Plan hash value: 2687344997
--------------------------------------
| Id | Operation | Name |
--------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | TABLE ACCESS FULL | MYEMP1 |
| 3 | SORT AGGREGATE | |
|* 4 | TABLE ACCESS FULL| MYEMP1 |
--------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"= (SELECT MIN("SAL") FROM "MYEMP1" "B" WHERE
"B"."DEPTNO"=:B1))
4 - filter("B"."DEPTNO"=:B1)
바깥쪽 메인쿼리에서 한건씩 읽어서 읽은 레코드의 급여가 자신의 속한 부서의 최소급여와 같은지를 반복적으로 비교한다.(중첩 루프 방식으로 해석)
실행계획 SQL연산 (HASH ANTI-JOIN)
ANTI 조인은 조인의 대상이 되는 테이블과 일치하지 않는 데이터를 추출하는 연산이다. NOT IN, NOT EXISTS, MINUS 등이 있을 때 나타나는 실행계획 연산자이다.
SQL> set linesize 130
SQL> set autotrace on
SQL> select count(e1.ename)
2 from myemp1 E1
3 WHERE ENAME NOT IN (SELECT ENAME FROM MYEMP1_OLD E2);
COUNT(E1.ENAME)
---------------
8333335
Execution Plan
----------------------------------------------------------
Plan hash value: 1210096379
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | | 40795 (1)| 00:08:10 |
| 1 | SORT AGGREGATE | | 1 | 27 | | | |
|* 2 | HASH JOIN RIGHT ANTI NA| | 10M| 257M| 41M| 40795 (1)| 00:08:10 |
| 3 | TABLE ACCESS FULL | MYEMP1_OLD | 1666K| 22M| | 1592 (2)| 00:00:20 |
| 4 | TABLE ACCESS FULL | MYEMP1 | 10M| 123M| | 25172 (1)| 00:05:03 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"="ENAME")
NOT IN 을 별다른 힌트없이 실행하면 HASH JOIN RIGHT ANTI NA 연산을 한다.
- HASH_AJ 힌트를 사용할 경우
SQL> SELECT count(e1.ename)
2 from myemp1 E1
3 WHERE ENAME NOT IN(SELECT /* + HASH_AJ*/ ENAME
4 FROM MYEMP1_OLD E2);
COUNT(E1.ENAME)
---------------
8333335
Execution Plan
----------------------------------------------------------
Plan hash value: 1210096379
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | | 40795 (1)| 00:08:10 |
| 1 | SORT AGGREGATE | | 1 | 27 | | | |
|* 2 | HASH JOIN RIGHT ANTI NA| | 10M| 257M| 41M| 40795 (1)| 00:08:10 |
| 3 | TABLE ACCESS FULL | MYEMP1_OLD | 1666K| 22M| | 1592 (2)| 00:00:20 |
| 4 | TABLE ACCESS FULL | MYEMP1 | 10M| 123M| | 25172 (1)| 00:05:03 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"="ENAME")
실행계획 SQL 연산 (HASH SEMI-JOIN)
세미 조인은 보통 EXISTS 를 사용하는 서브쿼리의 형태로 나타나며 이러한 경우 서브 쿼리에 인덱스가 존재하지 않는다면 상당히 비효율적인데 이러한 서브쿼리에 인덱스가 없는 경우 SEMI-JOIN이 일어나도록 유도한다면 성능의 향상을 꽤할 수있다. 즉, 인덱스 없이 EXISTS를 사용하는 쿼리라면 HASH_SJ or MERGE_SJ or NL_SJ 힌트를 이용하여 세미조인이 일어나도록 푸는 것이 좋다.
실행 계획 SQL 연산 (INDEX RANGE SCAN)
WHERE절에 인덱스 구성 컬럼이 ‘<’ OR ‘>’ 와 같이 범위 제한 연산자에 의해 이용되거나 BETWEEN OR LIKE 와 같은 조건 절에 이용될 때 INDEX RANGE SCAN을 하게 된다. 만약 결합 인덱스라면 범위 제한자에 사용되는 컬럼은 인덱스 구성 컬럼 중 선두 컬럼 이어야 하며 조건을 만족하는 첫번째 레코드를 인덱스 블록에서 추출 후 조건에 맞는 데이터가 나올 때 까지 계속 스캔하는 나가는 방식으로 INDEX, INDEX_ASC 힌트를 사용할 때 나타나는 연산자이다.
인덱스가 없는 경우 search
SQL> SELECT ENAME, SAL FROM EMP WHERE SAL > 1000;
ENAME SAL
-------------------- ----------
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
FORD 3000
ENAME SAL
-------------------- ----------
MILLER 1300
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 120 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 12 | 120 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL">1000)
인덱스 생성 후 search
SQL> create index idx_emp_sal ON EMP(sal);
Index created.
SQL> SELECT ENAME, SAL FROM EMP WHERE SAL > 1000;
ENAME SAL
-------------------- ----------
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
SCOTT 3000
FORD 3000
ENAME SAL
-------------------- ----------
KING 5000
12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 946658323
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 120 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 12 | 120 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP_SAL | 12 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SAL">1000)
참고로 기본 인덱스를 생성하면 오름차순으로 생성이된다. 특정 컬럼에 대해 인덱스가 구성되어 있다면 인덱스 생성할 때 내림차순이라고 명시하지 않으면 컬럼 값에 대해 오름차순으로 인덱스가 구성되므로 인덱스를 경유하게 되면 자동으로 오름차순 형태로 데이터가 추출되게 된다.
실행계획 SQL연산 (MERGE JOIN)
MERGE JOIN은 조인 시 각 테이블을 따로 정렬 후 그 결과를 병합 하는 집합 연산으로 SORT MERGE JOIN 이라고도 한다. BATCH성 작업에 효율적이다. OLTP 성능에는 좋지 않다.
SQL> SHOW PARAMETER OPTIMIZER_MODE
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_mode string ALL_ROWS
** CASE1
SQL> SELECT /*+ RULE*/ COUNT(E.ENAME)
2 FROM MYEMP1 E, MYDEPT1 D
3 WHERE TO_CHAR(E.DEPTNO) = TO_CHAR(D.DEPTNO);
COUNT(E.ENAME)
--------------
10000002
Execution Plan
----------------------------------------------------------
Plan hash value: 3462355838
----------------------------------------
| Id | Operation | Name |
----------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | MERGE JOIN | |
| 3 | SORT JOIN | |
| 4 | TABLE ACCESS FULL| MYDEPT1 |
|* 5 | SORT JOIN | |
| 6 | TABLE ACCESS FULL| MYEMP1 |
----------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(INTERNAL_FUNCTION("E"."DEPTNO")=INTERNAL_FUNCTION("D"."DEP
TNO"))
filter(INTERNAL_FUNCTION("E"."DEPTNO")=INTERNAL_FUNCTION("D"."DEP
TNO"))
Note
-----
- rule based optimizer used (consider using cbo)
** CASE2
SQL> SELECT /*+ USER(MERGE(D E)*/ COUNT(E.ENAME)
2 FROM MYEMP1 E, MYDEPT1 D
3 WHERE TO_CHAR(E.DEPTNO) = TO_CHAR(D.DEPTNO);
COUNT(E.ENAME)
--------------
10000002
Execution Plan
----------------------------------------------------------
Plan hash value: 2924099767
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 25258 (1)| 00:05:04 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | HASH JOIN | | 700K| 2050K| 25258 (1)| 00:05:04 |
| 3 | INDEX FULL SCAN | PK_MYDEPT1 | 7 | 14 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| MYEMP1 | 10M| 9765K| 25186 (1)| 00:05:03 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(INTERNAL_FUNCTION("E"."DEPTNO")=INTERNAL_FUNCTION("D"."DEPTN
O"))
[위의 힌트에 따른 쿼리실행속도를 비교해보면 CASE 1 에 비해 CASE 2의 성능이 훨씬 좋다]
오라클 인덱스
* 비트리 인덱스 : 가장 많이 사용되는 인덱스 형태이며 구조가 자료구조의 TREE 형태와 비슷하다.
기본적인 Algorithm을 이용하는데 주로 온라인 업무의 적은 데이터 처리에 유 리하고 분포도가 나쁜 데이터 처리는 성능에 악영향을 줄 수 있다.
하나의 ROW를 검색 시 어느 ROW나 동일한 양의 블록을 ACCESS하게 되므로 검색 시간이 동일하다.
* 비트맵 인덱스 : 정보저장의 최소단위인 비트를 이용하여 칼럼 값을 간결하게 저장하고 이를 이용하여 자동으로 ROWID를 생성하는 구조를 가지며 분포도가 나쁜 칼럼, NOT, OR를 사용하는 경우 탁월한 성능을 낸다.
정보저장의 최소단위인 비트를 이용하여 칼럼 값을 간결하게 저장하고 이를 이용하여 자동으 로 ROWID를 생성하는 구조를 가지며 분포도가 나쁜 칼럼, NOT, OR를 사용하는 경우 탁월한 성능을 낸다.
B*Tree 인덱스가 NULL값을 보관하지 않는 것과는 달리 Bitmap 인덱스는 NULL값에 대한 BIT 값을 저장하여 비트리 인덱스의 NULL문제를 해결했으며 AND, OR 연산시 비트연산을 빠르게 수행한다.
By William Scott [WHERE 조건절 Tip]
-
Where 절을 검토한다 Where 절은 크게 Rows를 제한 하는 것과 Table간의 Join에 사용된다.
-
2후보 Driving Table 을 정한다. 가장 적은 Rows 를 가져오는 [가장 제한적 (where) 절을 가진] 테이블을 선택 후보가 2개 이상 시에는 한개 찍기
-
From 절의 모든 Table 을 List 하고, 후보 Driving Table을 시작으로 하여, Join 조건을 이용하여 모든 Table 간의 Join을 진행한다. 이것은 후보 Driving Table로 시작된, Database 가 접근 해야만 하는 테이블 순서 리스트를 보여준다.
-
Driving Table 의 인덱스를 찾고 인덱스 혹은 Full Table Scan을 이용하는 것이 좋을지 결정하고 Optimizer가 선택한 Index 보다 좋은 Index가 있다면 인덱스 Hint 사용을 통해서 보다 좋은 Index를 사용하도록 한다.
-
제한적 Where절을 가진 Table을 우선적으로 Join 하려고 한다.
-
Union 보다는 Union All 을 사용하라
-
Outer Join, IS NULL 을 이용한 안티조인보다는 NOT IN, NOT EXISTS 를 사용하자.
-
레코드 필터링을 위해서 HAVING 보다는 WHERE 를 사용하자.
-
WHERE 절에 선행 INDEX칼럼을 명시하라.