[Oracle] Tunning - SQL작성시에 주의 할 점

[Oracle] Tunning -  SQL작성시에 주의 할 점

 

일반적으로 database의 performance down문제의 원인중

80%는 잘못 설계된 SQL 및 indexes에 있다고 합니다.

 

여기서는 제가 알고 있는 SQL 및 index 설계 방법에 대해서 다루고자 합니다.

 

1. query가 되는 tables 및 indexes에 대해서 최신 statistics정보가 필요합니다.

 

이 이야기를 하기 전에 rule-based optimizer(RBO)와 cost-based optimizer(CBO)에 대해서

그리고 statistics의 활용에 대해서 잠깐 짚고 넘어가고자 합니다.

SQL을 실행하기 전에 optimizer라는 놈이 실행을 위한 계획(execution plan)을 작성하는데,

예전 Oracle9i시절까지는, SQL에 언급된 tables에 대해 statistics정보가 기록되어 있는 경우에는

CBO가 cost를 최소화하는 방향으로 execution plan을 작성하며,

statistics정보가 없는 경우에는 RBO가 execution plan을 작성하도록 되어 있었습니다.

 

그런데, Oracle10g부터는 RBO가 더 이상 support되지 않게 되었습니다(사라졌습니다).

즉, 모든 SQL은 CBO에 의해 execution plan이 작성되게 되었고,

이 statistics정보는 더더욱 중요해 지게 되었습니다.

이 statistics정보가 부정확할 경우, CBO는 optimal execution plan을 생성해 내지 못하게 되면서,

경우에 따라서는 1~2분에 끝날 query가 3~4시간 걸리는 경우도 발생하기도 합니다.

 

Oracle10g부터는 Scheduler가 자동으로 statistics를 수집해 주는데,

제 경험에 비추면 그다지 믿음직스럽지 않더군요.

가급적 Oracle10g부터 support되는 dbms_scheduler 및 dbms_stats packages를 이용해서

전체 user tables 및 indexes에 대한 statistics를 일주일에 1~7번정도 수집해 주는 것이 좋습니다.

 

statistics를 수집하는 방법으로 3가지 방법이 있는데,

(1) 앞에서 언급한 Oracle10g부터 지원되는 Automatic Statistics Gathering기능

(2) analyze table(or index) <object_name> estimate(compute) statistics를

필요한 objects에 대해 일일이 실행

(3) dbms_stats package를 이용

 

여기서 중요한 point가 하나 있는데,

Oracle8i 및 Oracle9i 시절까지는 애용되었던 analyze table ... compute statistics를

Oracle10g부터는 이용하지 말 것을 Oracle에서 recommend하고 있다는 것입니다.

그 이유는 analyze command보다 더 풍부하고 정확한 정보를 수집할 수 있는

dbms_stats라는 package가 Oracle10g부터는 지원되기 때문입니다.

 

 

dbms_stats에 대해서 더 할 얘기가 있지만, 다음에 기회에 있을 때 더 자세히 다루기로 하고,

결론은 dbms_stats를 이용해서 최신 statistics를 주기적으로 수집하도록 합시다.

이 부분은 programmer가 할 수 있는 부분은 아니므로 DBA가 수행해야 할 것이며,

빠뜨려서는 안되는 DBA의 일상적인 유지보수 업무중의 하나라고 보면 될 것 같습니다.

 

2. 모든 tables에 반드시 primary key를 생성합시다.

 

table의 전체 크기가 작거나 records수가 적은 table(1000건이하)은

primary key가 없어도 큰 상관은 없습니다.

 

3. foreign key constraint가 걸려있는 column에는 반드시 index를 생성합시다.

 

이 이유는, 참조되는 table에서 data를 삭제하려고 하는 경우,

삭제되기 전에 참조를 하고 있는 table의 data를 확인하는 과정이 내부적으로 수행되는데,

이 과정에서 index가 없으면 full scan을 수행하고 되고 그 만큼 performance는 떨어지게 됩니다.

 

EMP table(사원정보)과 DEPT table(부서정보)을 예로 들어서 알기 쉽게 설명해 보고자 합니다.

다들 아시다시피, EMP의 "deptno(부서번호)" column는 DEPT의 "deptno" column을 참조하도록

foreign key가 생성되어 있습니다.

 

DEPT에서 deptno=10 인 record를 삭제하는 delete문을 실행했을 경우,

삭제작업을 수행하기에 앞서, Oracle 내부적으로 EMP에 deptno=10 인 record가

존재하고 있는지 확인작업을 수행하게 됩니다.

현재 EMP의 "deptno" column에 index가 생성되어 있지 않다면,

위의 처리는 table full-scan을 통해 이루어지게 되며,

 

record수가 상당히 많다면 delete문을 실행한 후 한참 뒤에나 결과를 볼 수 있게 됩니다.

이 처리 시간 문제 이외에도, 이 처리를 수행하는 동안 Share Table Lock(S)이

EMP table전체에 걸리게 되어, 다른 transaction에 긴 시간 영향을 줄 수도 있습니다.

 

그러므로 record수가 많은 table일수록, foreign key에 해당하는 column에 대해

index를 생성할 필요가 있습니다.

 

4. WHERE, ORDER BY, GROUP BY, TOP, DISTINCT clause에 사용되는 column에 대해서

index를 생성합시다.

 

5. 복수의 columns를 포함하는 index(composite index)를 작성할 때에

cardinality(selectivity)가 높은 순(내림차순)으로 index를 작성합니다.

여기서 cardinality라 함은 값의 종류가 많은 정도를 나타내는 용어로,

cardinality가 높다고 함은, 값의 종류가 많다는 것을 의미하게 됩니다.

간단한 예로, "냉장고 제조사"와 "냉장고 모델NO"를 비교하면

후자 쪽이 전자보다 cardinality가 높다고 말합니다.

비슷한 용어로 selectvity라는 말이 있는데, 정확한 차이는 모르겠습니다.

아시는 분은 덧글 남겨주시면 감사하겠습니다.

이제 원래의 얘기로 돌아와서, 예를 들어 "부서", "사원NO", "성별" 이라는

3개의 columns에 대해 하나의 index를 작성하고자 할 때에,

cardinality가 높은 순서대로 정렬하면, "사원NO", "부서", "성별" 이라는 순서가 됩니다.

이 순서를 다음과 같이 index생성시에 반영하도록 합니다.

→ create index idx01 on emp(사원NO, 부서, 성별);

위의 예는 각 column의 cardinality를 조사해보지 않고도 금방 알 수 있지만,

그렇지 않은 경우, 다음과 같이 각 column의 cardinality를 조사할 수 있다.

---------------------------------------------------------------------------------------

SQL> select count(*) as total_records,

count(distinct column_01) as column_01_records,
count(distinct column_02) as column_02_records,
count(distinct column_03) as column_03_records,
from <table_name>;

 

 

total_records	column_01_records	column_02_records	column_03_records
1,556,429	1,366,083	26,803	6,233

 

---------------------------------------------------------------------------------------

참고로 index의 column순서는 user_ind_columns의 column_position이라는

column을 통해 확인할 수 있으며,

위와 같이 idx01 index를 생성했다면, 아래와 같은 2개의 indexes는 작성할 필요가 없습니다.

왜냐하면 idx01이 그 역할을 대신해 줄 수 있기 때문입니다.

→ create index idx02 on emp(사원NO);

create index idx03 on emp(사원NO, 부서);

 

하지만 아래와 같은 indexes는 idx01이 역할을 대신해 줄 수 없기 때문에,

필요하다면 별도로 생성해 주어야 한다.

→ create index idx02 on emp(부서, 성별);

create index idx03 on emp(사원NO, 성별);

create index idx03 on emp(성별);

create index idx03 on emp(부서);

 

결론을 말씀드리면, composite index의 경우 column순서가 index성능에 큰 영향을

미치며, 동일한 columns를 포함한 index라고 해도 해당 column들의 순서가 틀리다면

서로 완전히 다른 index라는 것입니다.

6. HAVING clause를 가급적 사용하지 맙시다.

왜냐하면 HAVING clause은 HAVING clause의 조건을 제외한 모든 조건들에 따라

값이 추출된 이후에 적용되기 때문입니다.

만약 HAVING clause의 조건을 WHERE clause에 쓸 수 있다면 그렇게 하는 것이 좋습니다.

7. 복잡한 query를 tuning하는 경우, sub-query를 먼저 tuning하는 편이 좋습니다.

왜냐하면 sub-query의 performance가 안좋으면 query전체의 성능에 큰 영향을

미치기 때문입니다. 만약 sub-query의 기능을 join을 통해서 대신할 수 있다면,

join으로 대체하는 편이 performance가 향상되는 경우가 많이 있습니다.

다음의 예를 소개하고자 합니다.

sub-query를 사용한 아래의 실행결과를 보면,

"recursive calls=58", "consistent gets=27"라는 결과가 나왔습니다.

SQL> select ename from emp 2 where deptno = (select deptno from dept where dname like 'SALE%'); ENAME ALLEN ... JAMES 6 rows selected.   Execution Plan Plan hash value: 2858400319 ... Statistics 58 recursive calls 0 db block gets 27 consistent gets 0 physical reads ...

 

하지만 join으로 변경한 아래의 결과를 보면,

"recursive calls=9", "consistent gets=16"로 보다 나은 결과가 나온 것을 알 수 있습니다.

SQL> select ename from emp, dept 2 where emp.deptno = dept.deptno and dname like 'SALE%'; ENAME ALLEN ... JAMES 6 rows selected.   Execution Plan Plan hash value: 615168685 ... Statistics 9 recursive calls 0 db block gets 16 consistent gets 0 physical reads 0 redo size 625 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 6 rows processed

 

 

8. 만약 WHERE clause에서 column명에 SUBSTR함수를 사용할 일이 있다면,

그 대신에 LIKE를 사용해서 변경하도록 합시다.

---------------------------------------------------------------------------------------

SQL> select ename from emp where SUBSTR(job, 1, 4) = 'SALE';

---------------------------------------------------------------------------------------

위의 SQL의 경우, "job" column에 index가 걸려있다고 해도 index를 타지 않게 되지만,

아래와 같이 변경함으로서 index가 활용될 수 있습니다(index range scan).

---------------------------------------------------------------------------------------

SQL> select ename from emp where job LIKE 'SALE%';

---------------------------------------------------------------------------------------

 

만약 위의 조건을 <job like '%SALE%'>와 같이 %로 시작하게 되면 index는

사용되지 않으므로, SUBSTR을 LIKE로 바꾼다고 해도 성능향상은 기대할 수 없습니다.

9. WHERE clause에 많은 OR가 사용되는 복잡한 query의 경우, UNION 혹은 UNION ALL을

사용하여 복수의 query로 나눔으로 해서 판독성 및 성능향상에 도움되는 경우가 있습니다.

---------------------------------------------------------------------------------------

SQL> select empno, ename from emp where mgr = 7839 or job = 'MANAGER';

 

위의 SQL문을 UNION을 사용해서 다음과 같이 변경할 수도 있다.

---------------------------------------------------------------------------------------

SQL> select empno, ename, mgr, job from emp where mgr = 7839

2 union

3 select empno, ename, mgr, job from emp where job = 'MANAGER';

---------------------------------------------------------------------------------------

참고로 이 경우에는 단순한 형태의 query라서 전자의 query가 후자보다 더 나은 성능을 보입니다.

10. WHERE clause의 조건에 사용되는 column에 함수 혹은 연산자가 사용되는 경우,

해당 column에 index가 걸려 있다고 해도 해당 index가 활용되지 않습니다.

이런 경우에는 왼쪽 column에 함수 혹은 연산자가 사용되지 않도록 조건을 변형하거나

function-based index를 사용하도록 합니다.

(1) 조건을 변형하는 예

------------------------------------------------------------------------------------

SQL> select empno, ename from emp where sal *2 > sal + 5000;

------------------------------------------------------------------------------------

 

위의 query를 다음과 같이 변경함으로서 "sal" column에 걸려있는 index가 작동하게 된다.

------------------------------------------------------------------------------------

 

SQL> select empno, ename from emp where sal > (sal + 5000)/2;

------------------------------------------------------------------------------------

 

(2) function-based index를 활용하는 예

------------------------------------------------------------------------------------

 

SQL> select empno, ename from emp where to_char(hiredate,'yyyy') = '2009';

------------------------------------------------------------------------------------

 

설령 hiredate에 index가 걸려 있다고 하더라도 위의 query는 해당 index를 활용하지

못합니다. 아래와 같이 function-based index를 생성함으로서 index를 활용할 수 있게 됩니다.

------------------------------------------------------------------------------------

 

SQL> create index hdate_idx on emp(to_char(hiredate,'yyyy'));

------------------------------------------------------------------------------------

 

11. SELECT할 때는 필요한 columns만 지정하도록 합니다. SELECT * FROM... 을 사용하는

경우, 불필요한 columns까지 추출되어 query실행시간도 오래 걸리게 되며,

network부하도 증가하게 됩니다.

12. WHERE clause에 사용되는 값들에 대한 따옴표(single quotation)사용을 정확하게 하도록

합니다. 숫자를 따옴표로 둘러싸거나 문자를 따옴표로 둘러싸지 않거나 하는 경우,

query 실행시에 error가 발생하는 경우도 있지만 발생하지 않고 그대로 실행되는 경우도

있습니다. 후자의 경우 index가 제대로 활용되지 않는 경우가 발생할 수 있으므로,

따옴표는 정확하게 사용하도록 합시다.

좀 다른 이야기지만, 최근에 저희 회사의 일부 database를 10gR2에서 11gR1으로 upgrade를

하기 위해 test를 진행중입니다. 10gR2환경에서는 일부의 query에 대해 WHERE clause에

사용된 문자열을 따옴표로 둘러싸지 않았음에도 불구하고 실행시에 error가 발생하지

않았는데,11gR1환경에서는 error가 발생하는 문제가 발생했습니다.

 

13. IS NULL을 사용하게 되면 full-scan을 수행하게 됩니다. 그러므로 IS NULL사용시에는

주의할 필요가 있습니다. (참고로 IS NOT NULL은 index가 활용됩니다)

IS NULL을 사용하지 않기 위한 방법이 있는데,

실제 NULL이 아닌 NULL에 해당하는 값(논리적인 NULL)을 설정하는 것입니다.

"default" clause를 통해 해당 column에 값이 설정되지 않았을 때에는

NULL에 상응하는 약속된 값이 들어가도록 할 수 있습니다.

 

14. MINUS 보다는 NOT EQUAL을 사용하는 편이 성능면에서 좋습니다.

 

MINUS 를 사용한 예	select employee_id, manager_id,first_name, last_name from employees minus select outer.employee_id, outer.manager_id, outer.first_name, outer.last_name from employees outer, employees inner where inner.employee_id = outer.manager_id and inner.salary > 10000;
NOT EXISTS 를 사용한 예	select employee_id, manager_id, first_name, last_name from employees outer where not exists(select * from employees inner where inner.employee_id = outer.manager_id and inner.salary > 10000);

 

15. database link를 사용해서 access하는 remote table과의 join은 되도록 삼가하도록

합니다. 왜냐하면 join을 하기 위해 모든 remote table의 전체 data를 전송받아야

하기 때문에, 해당 remote table의 records수가 적다면 큰 문제 없지만,

그렇지 않은 경우, network부하도 많이 걸리며, query실행에 많은 시간이 걸리게 됩니다.

16. not equal(<> or !=)의 사용을 되도록 삼가합니다. 이 not equal을 사용하는 경우,

full-scan을 수행하는 경우가 많기 때문에 되도록 ">", "<", "IN", "OR"를 사용하는

방향으로 query를 수정하도록 합니다.

17. record의 존재유무를 체크하고자 하는 것이라면, "select count(*) from ..." 이 아닌

"select <any_column> from ... where rownum < 2"를 사용하도록 합니다.

18. 다른 view를 참조하는 view를 생성할 때는 주의합니다.

 

왜냐하면 view라는 것은 실체(data)를 가지지 않는, 단지 "정의"만을 가지는 것이기 때문에

view에서 view를 참조하면 겉으로 보기에는 simple할 지 몰라도

내부적으로는 굉장히 복잡하고 비효율적으로 동작할 수 있기 때문입니다.

 

19. 한 개의 query내에서 join되는 table의 수는 6개이하로 하는 것이 좋습니다.

 

복수의 tables이 specify되어서 join이 필요한 query가 있는 경우

optimizer가 join순서에 대한 모든 경우의 수에 대해 비용(cost)을 계산하여

그 중에서 가장 저렴한 join순서를 결정하게 됩니다.

예를 들어 A,B,C라는 3개의 tables를 포함하는 query가 실행된다면,

A-B-C, A-C-B, B-A-C, B-C-A, C-A-B, C-B-A라는

최대 6개의 join에 대한 경우의 수(3!=3*2*1 : 3 factorial)가 발생합니다.

 

6개의 tables가 조인되는 경우에는 최대 6!(720)개의 경우의 수가 발생하며,

7개의 tables가 조인되는 경우에는 최대 7!(5240)개의 경우의 수가 발생하는데,

join순서가 720개를 넘게 되면,

각각의 join순서에 대해 일일이 cost를 산출하는 것은 시간이 너무 많이 소비되기 때문에,

전부가 아닌 몇 개의 경우의 수에 대해서만 cost를 산출한 후

그 중에서 cost가 낮은 것을 사용하게 됩니다.

 

20. view를 생성할 때 반드시 필요하지 않는 이상 distinct나 order by를 사용하지 맙시다.

 

다들 아시다시피, view는 실체를 가지지 않기 때문에

view를 정의할 때 order by를 사용한다고 해서

data가 sort된 형태로 저장되거나 하는 것이 아니며,

view가 query에서 참조될 때에 sort를 수행하게 됩니다.

그러므로 sort를 필요로 하는 distinct 나 order by를 view 정의시에 specify하게 되면,

sort를 필요로 하지 않는 query시에도 sort가 수행되기 때문에

response는 나빠지게 됩니다.

 

http://blog.naver.com/PostView.nhn?blogId=puchi203&logNo=140140478877