[DB] SQL 튜닝 강의 3일차

오늘 허겁지겁 뛰어오고 볼팬도 없고 옆에는 여전히...

열악한 환경에도 불구하고 오늘도 강의를 수강하러 가산까지 왔습니다.

 

오늘은 Optimizer에 대해서 먼저 수업이 진행되겠습니다.

 

표현식 : 사칙연한이나 함수들이 표현된 식. 표현식에 대한 연산을 먼저 처리합니다.

select * from emp where sal=30000/10;

위와 같은 식에서 30000/10을 3000으로 먼저 계산을 하고 Select문을 수행하게 됩니다.

 

 

view를 참조하여도 위의 화면과 같이 실행계획은 보면 Optimizer가 더 효율적으로 수행을 하기 위해 직접 Table을 참조하는 것을 확인 할 수 있습니다.

 

 CREATE TABLE emp2 AS select * from emp;

CTAS라고 테이블을  테이블 조회해서 만드는 문장. 자주 쓸 것이라고 설명을 해 주셨습니다.

table 처럼 사용할 수 없으면 결과의 view를 만들고 처리합니다. ??

<서브쿼리의 사용>

SELECT *
FROM dept
WHERE deptno IN ( SELECT deptno FROM emp );

 

위의 화면은 서브쿼리를 사용했음에도 불구하고 Optimizer가 더 효율적인 작업을 위해서 JOIN을 실시한 실행계획 화면입니다.

"인위적으로 실행 계획을 바꾸려고 하지 말라"는 말씀을 지난번에 이어 강조하셨습니다. 즉, 위에서 본 30000/10 과 같은 처리를 명시적으로 지시하지 않아도 Optimizer는 알아서 효율적으로 동작을 한다는 것을 강조하셨습니다.

실행계획은 위에서 아래로, 왼쪽에서 오른쪽으로 보는게 좋습니다.

들여쓰기가 되어있고 위에서부터 실행이 되지만 자식이 있으면 자식 먼저 수행이 됩니다.

각 단계를 row source라고 하고 각 단계에서는 row set을 반환합니다.

 

[ RBO Access path ]

CBO를 사용하는 것을 권장하지만 타산지석으로 RBO를 보고 CBO의 장점을 찾고 다른점을 비교하여 볼 수 있도록 살펴보는 것이라는 안내로 시작하셨습니다.

상식적이지만 융통성이 없음.

Ex> 비행기가 빠른 운송수단임은 참이다. 하지만 태풍이 김포공항에 닥쳐오는데 비행기를 타러 가는 것은 현명하다고 할 수 있는가?

ex> Index가 있으면 사용함.  -> Table의 크기나 효율등을 무시함.

 

RBO 우선순위 결정 방식 (56page -> 2,3,5,6,7번은 볼 필요가 없음)

  Cluster table -> Oracle에서는 사용하지 않음. 취지는 좋습니다.

  table space를 만드는 경우에 Table Space 위에 Cluster를 만들어 미리 공간을 할당하는 느낌입니다. 즉. 섹션을 분할하는 것과 같은 개념입니다.

장점도 있지만 단점도 있습니다. 먼저 공간 설정을 잘못하면 확장되면서 더 성능이 떨어지고 DATA의 입력이 잘못 된 경우 값만 update하면 되는 것이 아니라 실제로 cluster에서 이동을 시켜야 함.

rowID(행의 주소)는 명시적으로 조회해야 나옵니다.

 

 

위의 화면은 RowID를 조회한 결과화면입니다.

table과 Index는 따로 존제하는 객체입니다. Index는 RowID를 갖고 있습니다. 따라서 인덱스로 찾아서 해당 RowID를 확인하여 table을 찾아 갈 수 있습니다.

빠르게 Data를 조회하려면 rowID를 조회하면 되지만 직접적으로 이용하기는 쉽지않다.

 

직접적으로 RowID를 주면 빠르지만 불편하기 때문에 간접적으로 효과를 보기 위해서 Index를 사용하는 것입니다.

Oracle의 5대 제약조건

<기본키(PK) 유니크 낫널(Not NULL) 외래키(FK) ??>

 

위의 화면은 RBO순위결정방식 4번에 있는 PK로 조회한 것을 실행계획으로 확인 한 화면입니다. 즉, 조건을 사용할 때 PK, UK column을 사용하면 효율이 뛰어나고 따라서 위와같은 것을 활용 할 필요가 있습니다.

====================================2========================================================

 4) Path 9: Single-Column Indexes

SQL> drop index emp_job_deptno_ix ;

SQL> create index emp_job_ix on emp(job);

SQL> SELECT *
FROM emp
WHERE job = 'ANALYST';

결과 화면입니다. index를 100건읽고 99건 버리고 1건만 읽는 경우를 설명 해 주셨습니다.

EX> 사원번로가 PK이면 사원번호로 조회를 하여 바로 찾을 수 있습니다.

하지만 사원번호를 모른다고 할 때 이름으로 먼저 검색을 하고 부서 번호 조건도 같은 사람이 있을 경우 즉, PK를 못쓰고 해당 조건에 맞는 행을 조회하기 위해서는 테이블을 다 읽고나서 걸러내는 작업입니다. 어제 설명과 같이 Data는 읽지 않을수록 좋습니다.(성능의 면에서) 하지만 Composite Index가 단일 Column index보다 좋다는 것 입니다.

7) Path 12: Sort Merge Join

SQL> SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno;

위의 조인 한 결과 화면입니다. 마지막 Where문을 WHERE emp.deptno = dept.deptno+0; 과 같이 약간의 트릭을 써서 수행을 할 수도 있습니다.

10) Path 15: Full Table Scan
SQL> SELECT *
FROM emp;

 

위의 경우는 어느 조건도 주지 않아서 Table의 모든 행을 조회한 화면입니다.

 

[ CBO Access path ]

 Oracle 7에서부터 도입, 지속적으로 향상되어 일반적으로 RBO보다 우수합니다.

CBO는 데이터에 대한 각종 통계를 사용합니다. 이 통계는 Table과 Index에 관한 통계정보입니다.

1) SELECT 문장에서의 SAMPLE 절

-- EMP 테이블 5% 질의

SELECT * FROM emp SAMPLE (5);

 

위의 화면을 보면 CBO를 사용한 실행계획은 RBO와 달리 cost정보가 더 보입니다.

 

2) BY GLOBAL INDEX ROWID

SQL> CREATE TABLE SALES
(ID NUMBER(5),
NAME VARCHAR2(10))
PARTITION BY RANGE (ID)
(PARTITION SALES_P1 VALUES LESS THAN (101),
PARTITION SALES_P2 VALUES LESS THAN (201),
PARTITION SALES_P3 VALUES LESS THAN (301),
PARTITION SALES_P4 VALUES LESS THAN (MAXVALUE));

SQL> CREATE INDEX sales_id_ix ON sales(id)
GLOBAL PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (201),
PARTITION p3 VALUES LESS THAN (MAXVALUE));

 

3) BY LOCAL INDEX ROWID
SQL> drop index sales_id_ix;
SQL> CREATE INDEX sales_id_ix ON sales(id) LOCAL;
SQL> select /*+ index(sales) */ * from sales where id = 1;

5) INDEX FAST FULL SCAN

SQL> conn / as sysdba

SQL> alter user sh identified by sh account unlock;

SQL> conn sh/sh

SQL> set autot trace exp

SQL> select CUST_id from CUSTOMERS;

full scan과 fast full scan은 약간의 차이가 있고 추후에 설명 예정이라고 하셨습니다.

 

====================================  3   ==========================================

교육에 관하여... 60%정도 아는 상태에서 듣는 것이 가장 좋다고 생각하셨습니다.

튜닝같은 경우는 특히 다른 강의를 들어볼 수 있다면 비교를 하면서 들어보면 또 다른 Style을 배울 수 있습니다.

강사님께서는 본인을 담백하게 튜닝을 하는 사람이라고 예를 들어주셨습니다. 원칙을 지키면서 담백하게 하는 것이 장기적으로 볼 때에는 도움이 많이 될 것이라 하셨습니다. 설탕같은 조미료를 많이 넣은 음식을 먹으면 늙어 고생한다는 것이 튜닝과도 유사하다고 하셨습니다.

 

7) Bitmap Operation

SQL> conn scott/tiger

SQL> drop index emp_job_ix;

SQL> create index emp_job_ix on emp(job);

SQL> create index emp_mgr_ix on emp(mgr);

SQL> set autot on

SQL> select /*+ index_combine(emp emp_job_ix emp_mgr_ix ) */ *
from emp
where job='CLERK' and mgr=7788;

SQL> select /*+ index_combine(emp emp_job_ix emp_mgr_ix ) */ *
from emp
where job='CLERK' or mgr=7788;

 

미리 Index가 conversion 되있는 경우가 더 빠릅니다. 위의 경우가 그러한 경우입니다. 각각의 RowID를 추출하여 bit값으로 바꾸고 Bit And연산을 하여 그 행을 찾을 수 있는 고유 값이 나오는데 그것을 RowID로 바꾸어 해당 행을 찾아갑니다. 위의 결과는 힌트를 주어 인위적으로 만든 실행계획입니다. 힌트를 주지 않는 경우에는 Index를 하나만 사용하는 것을 확인 할 수 있습니다.

Minus 연산

SQL> select /*+ index(emp emp_job_bix emp_mgr_bix ) */ *
from emp
where job='CLERK' and mgr<>7788;

 

위의 화면은 두가지 조건이 아닌 것을 검색하는 내용으로 해당 비트를 빼는것을 실행계획에서 확인할 수 있습니다.

3번째 시간.txt

3번째 시간에는 거의 실습 화면을 보면서 설명을 해 주셔서 실습