pat98's always & forever

Oracle Standard Edition 환경에서는 RAT (Real Application Testing)를 정상적으로 진행할수 없는데 Trace 파일을 이용하여
편법으로 RAT 를 이용하는 방법이 있어 기록해 놓는다.
-> 원래 Oracle Enterprise Edition 의 라이센스가 있어야 가능하다.


설정하는 방법은 sqlplus 에서 직접 셋팅하거나 Tool 등에서 Trigger 를 사용하는 방법으로 할수 있고 본인의 테스트 환경에 맞춰어 
셋팅하면 될 듯 하다.

- 전반적인 SPA 작업순서는 아래와 같다.

운영환경 : SE
- Trace 화일-> Workload 진행 -> Trace 중지 -> 매핑 Table 생성 (Trace를 STS변환) -> Trace 화일 복사

테스트 환경 : EE
- 운영 환경 이관 (datapump) -> 매핑 table 생성 -> 매핑 table 마이그레이션 -> Trace 화일에서 STS 생성 -> STS 에서 1번째 SPA 실행  -> 테스트환경에서 2번쨰 SPA 실행 -> 2번 실행한 결과애서 리포트 생성

================================================================
작업시작~~

1) SQL*Plus에서 테스트 대상 워크로드를 실행할 수 있는 경우

- 해당 유저로 접속
SQL > conn scott/tiger

SQL Trace 설정
SQL> ALTER SESSION SET TIMED_STATISTICS=TRUE;
SQL> ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='SQLTRACE';
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4';

2)  애플리케이션에서 테스트 대상 워크로드를 실행하는 경우

- 해당 유저로 접속
SQL > conn scott/tiger

- 로그온시 SQL Trace를 얻는 Trigger 생성

SQL > CREATE OR REPLACE TRIGGER logon_sqltrace AFTER LOGON ON SCHEMA
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET TIMED_STATISTICS=TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4''; 
EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER=''SQLTRACE'' ';
EXECUTE IMMEDIATE 'ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED'; 
END;
/

- 로그오프시 SQL Trace 중지하는 Trigger 생성

SQL> CREATE OR REPLACE TRIGGER logoff_sqltrace BEFORE LOGOFF ON SCHEMA
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET EVENTS' '10046 TRACE NAME CONTEXT OFF'' '; 
END;
/

3) Trace 화일의 기본 위치 확인 (해당 경우에는 trc 화일명에 ~~SQLTRACE.trc 로 파일이 생성된다.

SQL > SHOW PARAMETER USER_DUMP_DEST

보통의 경우 $ORACLE_BASE/diag/rdbms/[DB명]/[SID]/Trace

4) Trace 화일을 STS (SQL Tuning Set)으로 변환하기 위한 매핑 Table 생성

create table mapping_table as select object_id id, owner, substr(object_name, 1,
30) name from dba_objects where object_type NOT IN ('CONSUMER
GROUP','EVALUATION CONTEXT','JAVA DATA','JAVA
RESOURCE','LIBRARY','LOB','OPERATOR','PACKAGE','PACKAGE
BODY','PROCEDURE','QUEUE','RESOURCE PLAN','TRIGGER','TYPE','TYPE BODY‘)
union all select user_id id, username owner, null name from dba_users;

5) 테스트 환경 생성
- datapump 의 기능을 사용하여 테스트 환경 만들기
• Test 환경에도 매핑 Table 을 만들고 Table의 데이터 마이그레이션
• 운영 환경에서 생성된 Trace 파일을  복사 (/home/oracle/STS_WORK)

6) SQL Trace  파일을 저장할 위치 지정

create or replace directory MYDIR as '/home/oracle/STS_WORK';

CREATE_SQLSET API를 사용하여 STS 를 만들고, SELECT_SQL_TRACE에서 반환되는 커서 LOAD_SQLSET에 전달

DECLARE
cur sys_refcursor;
BEGIN
dbms_sqltune.create_sqlset('trace_toSTS');
open cur for
select value(p)
from table(dbms_sqltune.select_sql_trace( directory
=> 'MYDIR',
file_name => '%DB0908_ora',    <<<<<<--------------- 이 부분을 출력된 실제 Trace 화일명으로 편집                   
mapping_table_name => 'MAPPING_TABLE')) p;
dbms_sqltune.load_sqlset(
sqlset_name => 'trace_toSTS',
populate_cursor => cur,
commit_rows => 1);
close cur;
END;
/

7) STS 에서 SPA 실행

SQL> begin
dbms_sqlpa.execute_analysis_task(
 task_name => 'SPA_TASK',
 execution_name => 'NewTrial',
 execution_type => 'CONVERT SQLSET',
 execution_desc => 'fromSTS');
end;
/

7) 테스트 환경에서 SPA 실행

begin
dbms_sqlpa.execute_analysis_task(
 task_name => 'SPA_TASK',
 execution_name => 'New19Trial',
 execution_type => 'TEST EXECUTE',
 execution_desc => '19Execute');
end;
/

================================================================
여기까지 끝..

100% 이해 못한 상태에서 테스트해 봤는데 잘 안되서 다시 해 봐야겠다.-_-;

* 환경에 따라 다르지만 완벽하게 작동하지 않으므로 SE에서 SPA를 실행하면 아래 오류가 있을 수 있다. 

1) 생성된 리포트에 오류가 있을 수 있다. (오류확인은 가능하나 성능정보가 출력되지 않거나 하는 경우)
2) 바인드 변수가 설정된 SQL 을 추출할 수 없음.