웹 개발과 관련된 모든 것들

Automatic SQL Tuning in Oracle Database 10g(SQL Tuning Advisor) 

이번 강좌에서는 오라클 10g의 새 특징인 자동 SQL 튜닝기능에 대해 알아 보도록 하겠습니다. 

Normal Mode에서 오라클 옵티마이저는 아주 짧은 시간에 최적의 실행 계획을 계산해 내야 합니다. 그러므로 항상 최선의 실행 계획을 만들어 낼 수는 없다 이겁니다~^^ 

Oracle 10g는 옵티마이저가 튜닝 모드에서 실행될 수 있도록 하여 추가적인 통계 정보를 모아 추후에는 튜닝된 최적의 실행 계획을 만들어 낼 수 있도록 지원 합니다. 물론 이러한 프로세스는 하나의 SQL문장에 대해 몇 분이 걸릴지도 모르므로 리소스를 많이 잡아 먹는 경향이 있습니다. 

튜닝모드에서 옵티마이저가 하는 일에 대해 정리해 볼까요? 

- 통계 분석(statistics Analysis) : 옵티마이저는 오래전에 만들어진 통계 정보나 또는 통계 정보가 없는 부분에 대해 통계 정보를 생산 하는 것을 권고 하며 SQL Profile안에 부가적인 객체에 대한 통계 정보를 저장 합니다. 

- SQL Profileing : CBO(Cost Base Optimizer)로 수행될 때 SQL문을 위해서는 부가 정보들이 필요 합니다. 이러한 SQL문의 정보들을 SQL 프로파일이라는 형태로 수집해 놓습니다. 이러한 SQL 프로파일이 필요할 때마다 SQL Tuning Advisor 에 의해 업데이트됩니다. 

- 실행 경로 분석(Access Path Analysis) : 어떤 인덱스를 통해 데이터를 접근하여 추출할지를 결정 합니다. 필요하다면 SQL Access Advisor를 호출해 인덱스에 대한 권고를 요구하기도 합니다. 

- SQL 구조 분석(SQL Structure Analysis) : SQL문이 비효율적인 실행 계획을 생성할 경우 같은 결과를 보여줄 수 있는 비슷한 SQL문을 생성해 권고하는 역할을 합니다. 

이러한 Automatic SQL Tuning 특징은 EM(Enterprise Manager)의 “Advisor Central” 을 이용해서도 사용 할 수 있으며 또한 PL/SQL의 DBMS_SQLTUNE 패키지를 이용해서도 사용 가능 합니다. 본 강좌에서는 PL/SQL에 초점을 맞추어서 진행토록 하겠습니다. 

SQL Tuning Advisor 

SQL Tuning API에 접근 하기 위해서는 ADVISOR라는 권한이 있어야 합니다. 

아래처럼 하면 됩니다. 

SQL>CONN sys/password AS SYSDBA 
SQL>GRANT ADVISOR TO scott; 
SQL>CONN scott/tiger 

SQL Tuning Advisor를 사용하기 위한 첫 번째 단계는 CREATE_TUNING_TASK를 이용하여 새로운 tuning task를 만드는 일입니다. 분석되고자 하는 SQL 문장은 AWR이나 CURSOR CACHE, SQL Tuning set 또는 매뉴얼하게 만들어져 검색 될 수 있습니다. 

SET SERVEROUTPUT ON 

-- Tuning task created for specific a statement from the AWR. 
DECLARE 
l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
begin_snap => 764, 
end_snap => 938, 
sql_id => '19v5guvsgcd1v', 
scope => DBMS_SQLTUNE.scope_comprehensive, 
time_limit => 60, 
task_name => '19v5guvsgcd1v_AWR_tuning_task', 
description => 'Tuning task for statement 19v5guvsgcd1v in AWR.'); 
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/ 

-- Tuning task created for specific a statement from the cursor cache. 
DECLARE 
l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
sql_id => '19v5guvsgcd1v', 
scope => DBMS_SQLTUNE.scope_comprehensive, 
time_limit => 60, 
task_name => '19v5guvsgcd1v_tuning_task', 
description => 'Tuning task for statement 19v5guvsgcd1v.'); 
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/ 

-- Tuning task created from an SQL tuning set. 
DECLARE 
l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
sqlset_name => 'test_sql_tuning_set', 
scope => DBMS_SQLTUNE.scope_comprehensive, 
time_limit => 60, 
task_name => 'sqlset_tuning_task', 
description => 'Tuning task for an SQL tuning set.'); 
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/ 

-- Tuning task created for a manually specified statement. 
DECLARE 
l_sql VARCHAR2(500); 
l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
l_sql := 'SELECT e.*, d.* ' || 
'FROM emp e JOIN dept d ON e.deptno = d.deptno ' || 
'WHERE NVL(empno, ''0'') = :empno'; 

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
sql_text => l_sql, 
bind_list => sql_binds(anydata.ConvertNumber(100)), 
user_name => 'scott', 
scope => DBMS_SQLTUNE.scope_comprehensive, 
time_limit => 60, 
task_name => 'emp_dept_tuning_task', 
description => 'Tuning task for an EMP to DEPT join query.'); 
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/ 


만약 TASK_NAME 파라미터에 값이 있다면 SQL tune task의 식별자로서 사용 되구요, 생략된다면 시스템에서 “TASK_1478” 등과 같이 만들어서 리턴 합니다. 

NVL이 SQL 문장에 사용되다면 옵티마이저로부터 반작용을 유발 하며 부가적으로 그러한 테이블에 관한 통계정보를 지우는 것도 가능 합니다. 

EXEC DBMS_STATS.delete_table_stats('SCOTT','EMP'); 

위에서 tuning task에 대해 정의를 했는데 그 다음으로 할 일은 EXECUTE_TUNING_TASK procedure를 이용하는 일 입니다. 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task'); 

물론 아래처럼 task를 중단하거나 재시작 하거나 취소하거나 하는 것들이 가능 합니다. 

-- Interrupt and resume a tuning task. 
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task'); 
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task'); 

-- Cancel a tuning task. 
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task'); 

-- Reset a tuning task allowing it to be re-executed. 
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task'); 
다음과 같이 tuning task는 DBA_ADVISOER_LOG라는 뷰를 통해서 확인 가능 합니다. 

SQL>SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT'; 

TASK_NAME STATUS 
------------------------------ ----------- 
emp_dept_tuning_task COMPLETED 

tuning task가 성공적으로 수행 되면 REPORT_TUNING_TASK라는 함수를 통해 권고를 확인 할 수 있습니다. 

SET LONG 10000; 
SET PAGESIZE 1000 
SET LINESIZE 200 
SELECT DBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_task') AS recommendations FROM dual; 
SET PAGESIZE 24 

결과는 아래와 같습니다. 

RECOMMENDATIONS 
-------------------------------------------------------------------------------- 
GENERAL INFORMATION SECTION 
------------------------------------------------------------------------------- 
Tuning Task Name : emp_dept_tuning_task 
Scope : COMPREHENSIVE 
Time Limit(seconds): 60 
Completion Status : COMPLETED 
Started at : 05/06/2004 09:29:13 
Completed at : 05/06/2004 09:29:15 

------------------------------------------------------------------------------- 
SQL ID : 0wrmfv2yvswx1 
SQL Text: SELECT e.*, d.* FROM emp e JOIN dept d ON e.deptno = d.deptno 
WHERE NVL(empno, '0') = :empno 

------------------------------------------------------------------------------- 
FINDINGS SECTION (2 findings) 
------------------------------------------------------------------------------- 

1- Statistics Finding 
--------------------- 
Table "SCOTT"."EMP" and its indices were not analyzed. 

Recommendation 
-------------- 
Consider collecting optimizer statistics for this table and its indices. 
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE) 

Rationale 
--------- 
The optimizer requires up-to-date statistics for the table and its indices 
in order to select a good execution plan. 

2- Restructure SQL finding (see plan 1 in explain plans section) 
---------------------------------------------------------------- 
The predicate NVL("E"."EMPNO",0)=:B1 used at line ID 2 of the execution plan 
contains an __EXPRESSION!__ on indexed column "EMPNO". This __EXPRESSION!__ prevents 
the optimizer from selecting indices on table "SCOTT"."EMP". 

Recommendation 
-------------- 
Rewrite the predicate into an equivalent form to take advantage of 
indices. Alternatively, create a function-based index on the __EXPRESSION!__. 

Rationale 
--------- 
The optimizer is unable to use an index if the predicate is an inequality 
condition or if there is an __EXPRESSION!__ or an implicit data type conversion 
on the indexed column. 

------------------------------------------------------------------------------- 
EXPLAIN PLANS SECTION 
------------------------------------------------------------------------------- 

1- Original 
----------- 
Plan hash value: 1863486531 

---------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
---------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 107 | 4 (0)| 00:00:01 | 
| 1 | NESTED LOOPS | | 1 | 107 | 4 (0)| 00:00:01 | 
| 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 3 (0)| 00:00:01 | 
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 | 
| 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 | 
---------------------------------------------------------------------------------------- 

Note 
----- 
- dynamic sampling used for this statement 

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


1 row selected. 

Tuning task는 다음처럼 DROP 합니다. 

BEGIN 
DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task'); 
DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_tuning_task'); 
DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqlset_tuning_task'); 
DBMS_SQLTUNE.drop_tuning_task (task_name => 'emp_dept_tuning_task'); 
END; 
/ 

출처 : http://javacan.tistory.com/69

흔히 close()를 하지 않아서 발생하는 자원 누수 현상을 줄여주는 Connection 클래스를 만들어본다.

JDBC API 사용시 흔히 하는 개발자의 실수

JDBC API를 사용하여 데이터베이스 프로그래밍을 할 때 가장 많이 사용되는 코드는 아마도 다음과 같은 형태일 것이다.


그런데 위와 같은 프로그래밍을 할 때 흔히 하는 실수가 close()를 제대로 해 주지 않는 것이다. 특히, 하나의 메소드에서 5-10개의 (PreparedStatement를 포함한)Statement와 ResultSet을 사용하는 경우에는 개발자의 실수로 같은 Statement를 두번 close() 하고 한두개의 Statement나 ResultSet은 닫지 않는 실수를 하곤 한다. 이처럼 close() 메소드를 알맞게 호출해주지 않을 경우에는 다음과 같은 문제가 발생한다.

1. Statement를 닫지 않을 경우, 생성된 Statement의 개수가 증가하여 더 이상 Statement를 생성할 수 없게 된다.
2. close() 하지 않으므로 불필요한 자원(네트워크 및 메모리)을 낭비하게 된다.
3. 커넥션 풀을 사용하지 않는 상황에서 Connection을 닫지 않으면 결국엔 DBMS에 연결된 새로운 Connection을 생성할 수 없게 된다.

위의 문제중 첫번째와 두번째 문제는 시간이 지나면 가비지 콜렉터에 의해서 해결될 수도 있지만, 만약 커넥션 풀을 사용하고 있다면 그나마 가비지 콜렉션도 되지 않는다. 따라서 커넥션 풀을 사용하는 경우 Statement와 ResultSet은 반드시 닫아주어야만 한다. 하지만, 제아무리 실력이 뛰어난 개발자라 할지라도 각각 수십에서 수백줄로 구성된 수십여개의 .java 파일을 모두 완벽하게 코딩할 수는 없으며, 따라서 한두군데는 close()를 안 하기 마련이다. 운이 좋으면 빨리 찾을 수 있겠지만, 그렇지 않다면 close() 안한 부분을 찾는 데 몇십분, 몇시간, 심한 경우 1-2일 정도가 걸리기도 한다.

Statement를 자동으로 닫아주는 MVConnection 클래스 구현

실제로 필자도 앞에서 언근했던 문제들 때문에 고생하는 사람들을 종종 봐 왔었으며, 그때마다 그 버그를 고치기 위해서 소스 코드를 일일이 찾아보는 노가다를 하는 개발자들을 보기도 했다. 그래서 만든 클래스가 있는데, 그 클래스의 이름을 MVConnection이라고 붙였다. 이름이야 여러분의 입맛에 맛게 수정하면 되는 것이므로, 여기서는 원리만 간단하게 설명하도록 하겠다.

먼저, MVConnection을 구현하기 전에 우리가 알고 있어야 하는 기본 사항이 있다. JDBC API를 유심히 읽어본 사람이라면 다음과 같은 내용을 본 적이 있을 것이다.

• Statement를 close() 하면 Statement의 현재(즉, 가장 최근에 생성한) ResultSet도 close() 된다.
• ResultSet은 그 ResultSet을 생성한 Statement가 닫히거나, 또는 executeQuery 메소드를 실행하는 경우 close() 된다.

MVConnection은 바로 이 두가지 특성을 사용한다. 위의 두 가지 특징을 정리하면 결국 Statement만 알맞게 닫아주면 그와 관련된 ResultSet은 자동으로 닫힌다는 것을 알 수 있다. 따라서 ConnectionWrapper 클래스는 Connection이 생성한 Statement들만 잘 보관해두었다가 각 Statement를 닫아주기만 하면 되는 것이다. PreparedStatement나 CallableStatement는 Statement를 상속하고 있으므로 따로 처리할 필요 없이 Statement 타입으로 모두 처리할 수 있으므로, PreparedStatement와 CallableStatement를 위한 별도의 코드는 필요하지 않다.

다음은 MVConnection 클래스의 핵심 코드이다.


위 코드를 보면 Statement를 저장하기 위한 List와 그 List에 저장된 Statement 객체를 모두 닫아주는 closeAll() 이라는 메소드가 정의되어 있다. 바로 이 List와 closeAll() 메소드가 이 MVConnection 클래스의 핵심이다. Statement를 생성해주는 메소드(createStatement, prepareCall, prepareStatement)를 보면 생성된 Statement를 statemetList에 추가해주는 것을 알 수 있다. 이렇게 저장된 Statement는 실제로 Connection을 닫을 때, 즉 Connection의 close() 메소드를 호출할 때 닫힌다. (코드를 보면 close() 메소드에서 closeAll() 메소드를 먼저 호출하고 있다.) 따라서, close() 메소드만 호출하면 그와 관련된 모든 Statement와 ResultSet은 자동으로 닫히게 된다.

위 코드에서 다른 메소드들은 모두 다음과 같이 간단하게 구현된다.


MVConnection은 java.sql.Connection을 implements 하기 때문에, 그 자체가 Connection으로 사용될 수 있다. 따라서 MVConnection을 사용한다고 해서 특별히 코드가 많이 변경되지는 않으며 다음과 같이 전체적으로 코드가 단순하게 바뀐다.


때에 따라서는 Connection을 close() 하지 않고 커넥션 풀에 되돌려 놔야 할 때가 있다. 그런 경우에는 다음과 같은 형태의 코드를 사용하면 된다.


즉, Connection을 닫지 않는 경우에는 위와 같이 커넥션 풀에 반환하기 전에 closeAll() 메소드 하나만을 호출해주면 된다. 그러면 Connection과 관련된 모든 Statement, ResultSet 등이 닫히게 된다.

결론

필자의 경우는 이 글에서 작성한 MVConnection을 실제 프로젝트에 응용하여 코드 작성의 편리함 뿐만 아니라 실수로 인해서 발생하는 시스템의 버그 문제를 어느 정도 해결할 수 있었다. 특히, Statement를 생성하거나 ResultSet을 생성할 때 발생하는 커서부족 문제를 획기적으로 줄일 수 있었다. 여러분도 이 클래스를 응용하여 보다 나은 방법으로 코딩의 실수 및 자원의 낭비를 줄일 수 있는 클래스를 작성해보기 바란다.