[DB] SQL 튜닝 강의 4일차

<< 강사님의 들어가는말 >>

튜닝의 경우에는 여러 사람의 이야기를 들어보는 것이 좋습니다. 그 이유는 제테크와 비슷 한데 각각의 상황에 맞게 튜닝을 해야하기 때문입니다.

기본적으로 해야하는 것들도 있지만 개개인에 맞는 튜닝또한 존재하기 때문입니다.

튜닝은 정답이 있는것이 아닙니다. 

100점을 맞는 그러한 것이 거의 불가능 하다고 볼 수 있습니다. 그 이유는 한 부분을 고치면 다른 부분이 상대적으로 성능이 저하될 수 있기 때문입니다.

튜닝은 80점 정도 맞으면 합격점이라고 생각합니다.

튜닝의 목표!?

모니터링을 매일 하는것과 튜닝을 매일 하는것은 다르다. 계속해서 튜닝만을 할 수 없기 때문입니다. 그래서 목표를 설정하고 얼마만큼 튜닝을 할 것인지가 중요합니다. 

4) RBO 동작의 조정

(1) FROM 절에서 테이블 순서 변경

SQL> select /*+rule */ e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno
and e.empno=7788
and d.deptno=10;

 

 

Rule의 경우에는 조건이 동일한 경우 즉, Table을 접근하여 찾는 조건이 동일한 경우에만 뒷쪽의 Table을 먼저 읽습니다.

select /*+rule */ e.ename, d.dname
from dept d, emp e
where e.deptno=d.deptno
and e.empno=7788
and d.deptno=10;

 

위의 경우는 반대로 뒤에있는 emp table을 먼저 읽습니다.

AND는 앞에서부터 , OR의 경우는 뒤에서부터 읽습니다.

 (2) WHERE 절에서 술어 순서 변경

SQL> select /*+ rule */ * from dept
where deptno=10 and deptno=20;

SQL> select /*+ rule */ * from dept
where deptno=10 or deptno=20;

rule에서는 driven table이 될 뒤쪽 테이블에 좋은 조건의 Table을 써주는것이 좋습니다.

(3) syntax 트릭의 사용

SQL> select /*+rule */ e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;

SQL> select /*+rule */ e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno + 0;

위의 것과 아래의 것을 비교해보면 아래것이 consistent gets는 줄고 Memory sort는 늘었습니다.

select /*+ index_join(emp pk_emp emp_mgr_ix ) */ empno, mgr
from emp
where empno=7900 and mgr=7698;

 

위의 화면을 보면 View가 보이지만 그것은 아래 2번에서 hash Join을 하여 만든 결과값을 보는 것입니다.

 

(11) NATIVE_FULL_OUTER_JOIN (11g)

SQL> conn scott/tiger

SQL> set autot on

SQL> select *
from emp e full outer join dept d
on e.deptno=d.deptno;

양쪽 다 만족하지 않는 결과를 볼 때 사용합니다. hint를 사용하지 않아도 아래와 같습니다. 하지만 그 아래의 No_NATIVE_FULL_OUTER_JOIN 은 이전의 방식으로 table을 1번 초과하여 읽을 수도 있습니다.

SQL> select /*+ NATIVE_FULL_OUTER_JOIN */ *
from emp e full outer join dept d
on e.deptno=d.deptno;

SQL> select /*+ NO_NATIVE_FULL_OUTER_JOIN */ *
from emp e full outer join dept d
on e.deptno=d.deptno;


outer join은 Ansi 것을 사용하시기를 권장합니다. 그 이유는 oracle은 검색 조건에도 +기호를 붙여야해서 실수를 유발할 수 있습니다.

FULL_OUTER_JOIN 은 ansi것을 사용해야 table을 여러번 읽는것을 막을 수 있습니다.

 

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

9. CBO 통계 수집

1) 테이블 통계

SQL> conn scott/tiger

SQL> set autot off

SQL> create table test2
as
select * from emp;

SQL> select count(*) from test2;

SQL> desc user_table;

SQL> select table_name, num_rows, blocks
from user_tables;

 

 

optimizer 통계 정보가 실시간으로 업데이트 되는것이 아닙니다. 매일 10시에 해당 정보를 수집합니다. 스케쥴러가 돌아가서 작업을 수행합니다.

SQL> exec dbms_stats.gather_table_stats('SCOTT','TEST2')

SQL> select table_name, num_rows, blocks
from user_tables;

SQL> insert into test2 select * from test2;

SQL> commit;

SQL> select count(*) from test2;

SQL> select table_name, num_rows, blocks
from user_tables;

위의 문장들을 수행하고 다시 확인을 하면

 

28행이 있는것을 확인을 했음에도 불구하고 통계정보는 14건으로만 조회되는 것을 확인할 수 있습니다.

optimizer정보는 실시간 교통정보와 유사한데 실시간으로 업데이트가 되지 않아서 문제가 생길 수 있습니다. 스케줄러를 비활성화 시키면 지속적으로 Optimizer 통계정보를 관리 해 주어야합니다.

 

꿀팁!!!!  SQL>에서  '/'를 입력하면 이전 query를 재 수행함...

index는 필요 악이라고 하셨습니다. 항상 좋은 성능을 내는것은 아니지만 필요할 때도 분명 있기 때문입니다.

 

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

히스토그램, 통계정보 수집. ANALYZE와 DBMS_STATS

통계수집은 중요하다!!!! 통계수집이 SQL 튜닝에서 가장 중요하다.

통계를 다 지우고 새로 수집을 할 수도 있습니다. 통계정보를 모두 지워버리면 오히려 성능이 안좋았던 DB에서 성능이 나아지는 경우도 있습니다.

그 이유는 잘못된 통계정보가 DB의 성능을 저하시킬 수 있기 때문입니다.

table의 경우에는 삭제된 공간을 바로 재사용을 하지만 Index의 경우는 그러지 못하므로 rebuilding을 하는 경우가 있습니다.

통계수집은 1차적으로 DBA, 2차적으로는 개발자에게 책임이 있고 매우 매우매우 중요합니다.

index의 경우는 생성을 할 때 통계정보를 모아줍니다.

원칙은 10%이상 바뀌면 통계정보를 수집을 해주는 것을 권장합니다.

통계정보를 수집하게 되면 DB Level로 수집을 하게되면, auto로 수집을 하면 통계정보가 없거나 10%이상 변경된 것들만 통계정보를 모읍니다. ANALYZE는 위의 조건같은 것을 구분하지 않고 모으기 때문에 성능이 떨어질 수 있습니다.

버전에 따라서 주의를 할 필요는 있습니다. 통계정보를 모으는 것은 귀찮고 번거롭고 부하도 많이 발생하지만 꼭 필요한 작업입니다.

 

압축!!

내일은 그동안 수업내용을 꿰는 시간. 구슬이 서말이라도 꿰어야 보배라고 했으니까요...