SPA ( SQL Performance Analyzer )

• 환경의 변환에 따른 SQL의 변경 플랜에 대한 영향도 분석
• 11.2.0.1

환경

• 데이터 베이스 : 버전 업그레이드, 패치, 파라미터
• 인덱스 : 변경, 추가
• 통계 정보 : 최신화
• OS : CPU, Memory

SPA 프로세스

1. SQL SET 생성 및 등록

2. SQL EXECUTE ( BEFORE )

3. 환경 변환

4. SQL EXECUTE ( AFTER )

5. 비교 분석

SPA 테스트 환경

CREATE TABLE

{CODE:SQL}

CREATE TABLE SYS.TEMP_SPA_LOG_JHLEE
(
SPA_GRP NUMBER,
TASK_NAME VARCHAR2(1000 BYTE),
BEFORE_EXEC_NAME VARCHAR2(1000 BYTE),
AFTER_EXEC_NAME VARCHAR2(1000 BYTE),
RN NUMBER,
TABLE_NAME VARCHAR2(1000 BYTE),
BYTES NUMBER,
STATUS VARCHAR2(1000 BYTE),
START_DT DATE,
END_DT DATE,
STAT_GET_DDL VARCHAR2(2000 BYTE),
REPORT CLOB
)
TABLESPACE LCTM_DAT
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
LOB (REPORT) STORE AS
( TABLESPACE SYSTEM
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION
NOCACHE
INDEX (
TABLESPACE SYSTEM
STORAGE (
INITIAL 64K
NEXT 1
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
))
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING;

{CODE}

PACKAGE

{CODE:SQL}
CREATE OR REPLACE PACKAGE SYS.PKG_SPA AS
/******************************************************************************
NAME: PKG_SPA
PURPOSE:

REVISIONS:
Ver Date Author Description

---

---

-

---

---

--

---

---

---

---

---

---

---

---

---

---

---

---

---

1.0 2013-05-24 hyun 1. Created this package.
******************************************************************************/

PROCEDURE CREATE_BEFORE_TASK( IN_TASK_NAME IN VARCHAR2, IN_TABLE_LIKE_NAME IN VARCHAR2);

PROCEDURE CREATE_AFTER_REPORT( IN_TASK_NAME IN VARCHAR2, IN_BEFORE_EXEC_NAME IN VARCHAR2, IN_TABLE_LIKE_NAME IN VARCHAR2 );

-- PROCEDURE CREATE_USER( IN_USERNAME IN VARCHAR2
-- , IN_ROLENAME IN VARCHAR2
-- , IN_USERNO IN NUMBER);
--
-- PROCEDURE DROP_OBJECT( IN_OBJECT IN VARCHAR2, IN_USER IN VARCHAR DEFAULT 'TEMP');

END PKG_SPA;
/

{CODE}

PACKAGE BODY

{CODE:SQL}
CREATE OR REPLACE PACKAGE BODY SYS.PKG_SPA
AS

PROCEDURE CREATE_BEFORE_TASK( IN_TASK_NAME IN VARCHAR2 , IN_TABLE_LIKE_NAME IN VARCHAR2 )
IS
/******************************************************************************
NAME: CREATE_ROLE
PURPOSE:

EXEC PKG_SPA.CREATE_BEFORE_TASK( '2013051706' , 'OP')

sqlplus "/as sysdba"<<EOF
set serveroutput on
EXEC PKG_SPA.CREATE_BEFORE_TASK( '2013061011' , 'OP')
exit;

삭제
EXEC DBMS_SQLPA.DROP_ANALYSIS_TASK( '2013052009' )

EXEC DBMS_SQLTUNE.DROP_SQLSET( '2013052009' )

REVISIONS:
Ver Date Author Description

---

---

-

---

---

--

---

---

---

---

---

---

---

---

---

---

---

---

---

0.0 2013-05-24 LeeJH 1. Start
1.0 2013-05-28 LeeJH 1. Created this procedure.
******************************************************************************/

START_SNAP_ID NUMBER;
END_SNAP_ID NUMBER;

CHECK_SQLSET NUMBER;

V_TASK_NAME VARCHAR2(100);

V_EXEC_NAME VARCHAR2(100);

V_BEGIN_INTERVAL_TIME VARCHAR2(100);

BASELINE_SNAP DBMS_SQLTUNE.SQLSET_CURSOR;

tname varchar2(30);

BEGIN

-- 생성하는 TASK NAME이 없으면 디폴트 설정
IF IN_TASK_NAME IS NULL THEN

SELECT TO_CHAR( TRUNC( SYSDATE, 'IW' ) - 3 + 1 / 1440 * 60 * 9 , 'YYYYMMDDHH24')
INTO V_BEGIN_INTERVAL_TIME
FROM DUAL;

ELSE

-- 존재하는 SQLSET 인지 확인 있으면 에러발생
-- SELECT SQL_ID AS CHECK_SQLSET
-- INTO CHECK_SQLSET
-- FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET ( IN_TASK_NAME ));

V_BEGIN_INTERVAL_TIME := IN_TASK_NAME;

END IF;

V_TASK_NAME := V_BEGIN_INTERVAL_TIME;

V_EXEC_NAME := V_BEGIN_INTERVAL_TIME||'_'||TO_CHAR( LPAD( TRUNC( DBMS_RANDOM.VALUE( 1, 9999999999 ) ),10, '0') );

/*
SELECT COUNT (*)
INTO CHECK_SQLSET
FROM TABLE (DBMS_SQLTUNE.select_sqlset (in_task_name) )
WHERE ROWNUM <= 1;

-- 존재하는 SQL 셋이라면 삭제후 생성
IF CHECK_SQLSET = 1 THEN

/*+
select name,created,last_modified,statement_count,description from DBA_SQLSET;

select description, created, owner from DBA_SQLSET_REFERENCES where sqlset_name = '2013061011';

select owner,description, created,last_modified from DBA_ADVISOR_TASKS where task_name = '2013061011'

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => '2013061011') / /

CHECK_SQLSET := 0;

SELECT COUNT (*)
INTO CHECK_SQLSET
FROM dba_advisor_tasks
WHERE task_name = in_task_name
AND ROWNUM <= 1;

IF CHECK_SQLSET = 1 THEN

DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => in_task_name );

END IF;

DBMS_SQLTUNE.DROP_SQLSET( IN_TASK_NAME );

END IF;

*/

DBMS_APPLICATION_INFO.SET_MODULE('CREATE_BEFORE_TASK', 'CREATE_SQLSET START');

DBMS_SQLTUNE.CREATE_SQLSET( SQLSET_NAME => V_TASK_NAME, DESCRIPTION => 'I/O intensive workload');
-- SELECT * FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( '2013052009_BE' ) )
-- exec DBMS_SQLTUNE.DROP_SQLSET( '2013052009_BE' )

SELECT MIN( SNAP_ID ) AS START_SNAP_ID, MAX( SNAP_ID ) AS END_SNAP_ID
INTO START_SNAP_ID, END_SNAP_ID
FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME BETWEEN TO_DATE( V_BEGIN_INTERVAL_TIME, 'YYYYMMDDHH24' ) AND TO_DATE( V_BEGIN_INTERVAL_TIME, 'YYYYMMDDHH24' ) + 1 / 1440 * 60 * 1 -- 1 시간 +
AND INSTANCE_NUMBER = 1
ORDER BY 1;

DBMS_APPLICATION_INFO.SET_MODULE('CREATE_BEFORE_TASK', 'CREATE_SQLSET END');
OPEN BASELINE_SNAP FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( START_SNAP_ID,END_SNAP_ID,NULL,NULL, NULL,NULL,NULL,1,NULL,'ALL')) p
WHERE PARSING_SCHEMA_NAME in ('EC_APP','EC_ADM')
-- AND COMMAND_TYPE=3 – 20130808 주석처리
AND SQL_TEXT NOT LIKE '%searchEntrCostBondConcommonSignsMgmt%'; -- 넘 오래걸려서 배제함.

DBMS_APPLICATION_INFO.SET_MODULE('CREATE_BEFORE_TASK', 'LOAD_SQLSET START');

DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME=> V_TASK_NAME, POPULATE_CURSOR=>BASELINE_SNAP );
-- SELECT sql_text FROM dba_sqlset_statements WHERE sqlset_name = 'spa_test_sqlset';

DBMS_APPLICATION_INFO.SET_MODULE('CREATE_BEFORE_TASK', 'CREATE_ANALYSIS_TASK START');

-- 20130808 주석 처리
DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER ( DBMS_SQLPA.CREATE_ANALYSIS_TASK(SQLSET_NAME => V_TASK_NAME,TASK_NAME => V_TASK_NAME ) ,'DISABLE_MULTI_EXEC','TRUE'); --, ORDER_BY => 'BUFFER_GETS'

-- tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(SQLSET_NAME => V_TASK_NAME, TASK_NAME => V_TASK_NAME );

DBMS_APPLICATION_INFO.SET_MODULE('CREATE_BEFORE_TASK', 'EXECUTE_ANALYSIS_TASK START');

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( TASK_NAME => V_TASK_NAME, EXECUTION_TYPE => 'TEST EXECUTE', EXECUTION_NAME => V_EXEC_NAME );
--DBMS_SQLPA.DROP_ANALYSIS_TASK

DBMS_APPLICATION_INFO.SET_MODULE('CREATE_AFTER_REPORT', 'START');

CREATE_AFTER_REPORT( IN_TASK_NAME => V_TASK_NAME, IN_BEFORE_EXEC_NAME => V_EXEC_NAME, IN_TABLE_LIKE_NAME => IN_TABLE_LIKE_NAME );

DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);

/*

EXEC DBMS_SQLPA.DROP_ANALYSIS_TASK( '2013052009' )

EXEC DBMS_SQLTUNE.DROP_SQLSET( '2013052009' )

--task select
select TASK_NAME,
ADVISOR_NAME,
created
from DBA_ADVISOR_TASKS
where task_name LIKE '%2013052009%'

SELECT EXECUTION_NAME
, STATUS
, EXECUTION_END
FROM DBA_ADVISOR_EXECUTIONS
WHERE EXECUTION_NAME LIKE 'BEFORE_%' --
ORDER BY EXECUTION_END DESC

*/

END CREATE_BEFORE_TASK;

PROCEDURE CREATE_AFTER_REPORT( IN_TASK_NAME IN VARCHAR2, IN_BEFORE_EXEC_NAME IN VARCHAR2,IN_TABLE_LIKE_NAME IN VARCHAR2 )
IS
/******************************************************************************
NAME: CREATE_ROLE
PURPOSE:

EXEC

EXEC PKG_SPA.CREATE_BEFORE_TASK( '2013051512' )

REVISIONS:
Ver Date Author Description

---

---

-

---

---

--

---

---

---

---

---

---

---

---

---

---

---

---

---

1.0 2013-05-24 LeeJH 1. Created this procedure.
******************************************************************************/

V_AFTER_EXEC_NAME VARCHAR2( 1000 );

INTO_MAX_RN NUMBER;

BEGIN

-- 진행 로그
INSERT INTO TEMP_SPA_LOG_JHLEE ( BYTES, SPA_GRP, TASK_NAME, BEFORE_EXEC_NAME, START_DT, RN, TABLE_NAME, STATUS, STAT_GET_DDL )
SELECT BYTES, NVL( (SELECT MAX( SPA_GRP ) + 1 FROM TEMP_SPA_LOG_JHLEE ), 1 ) AS SPA_GRP, IN_TASK_NAME AS TASK_NAME, IN_BEFORE_EXEC_NAME AS BEFORE_EXEC_NAME, SYSDATE START_DT, ROWNUM RN, SEGMENT_NAME,'EXEC' AS STATUS, STAT_GET_DDL
FROM (SELECT SEGMENT_NAME,SUM(C.BYTES) BYTES, 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || C.OWNER || ''', TABNAME => ''' || C.SEGMENT_NAME || ''', CASCADE => TRUE, ESTIMATE_PERCENT =>''' ||
CASE
-- WHEN SUM(C.BYTES)/(1024*1024) <= 10 THEN 100
-- WHEN SUM(C.BYTES)/(1024*1024) < 50 THEN 80
-- WHEN SUM(C.BYTES)/(1024*1024) < 100 THEN 40
WHEN SUM(C.BYTES)/(1024*1024) < 500 THEN 100
WHEN SUM(C.BYTES)/(1024*1024) < 1000 THEN 50
WHEN SUM(C.BYTES)/(1024*1024) < 10000 THEN 25
WHEN SUM(C.BYTES)/(1024*1024) < 20000 THEN 5
ELSE 1
END || ''', DEGREE => ''' ||
CASE
WHEN SUM(C.BYTES)/(1024*1024) <= 10 THEN 1
WHEN SUM(C.BYTES)/(1024*1024) < 50 THEN 2
WHEN SUM(C.BYTES)/(1024*1024) < 100 THEN 4
WHEN SUM(C.BYTES)/(1024*1024) < 500 THEN 4
WHEN SUM(C.BYTES)/(1024*1024) < 1000 THEN 6
WHEN SUM(C.BYTES)/(1024*1024) < 10000 THEN 8
ELSE 12
END || ''', METHOD_OPT=>''FOR ALL COLUMNS SIZE 1'',NO_INVALIDATE=>false); END;' STAT_GET_DDL
FROM DBA_TABLES A,
DBA_OBJECTS B,
DBA_SEGMENTS C
WHERE A.TABLE_NAME=B.OBJECT_NAME
AND B.OBJECT_NAME=C.SEGMENT_NAME
AND B.OBJECT_TYPE='TABLE'
AND A.OWNER IN ('USER')
AND NOT EXISTS (SELECT 1 FROM DBA_PART_TABLES PT WHERE PT.TABLE_NAME=A.TABLE_NAME)
AND TABLE_NAME NOT LIKE 'LN%'
AND TABLE_NAME NOT LIKE '%TMP%'
AND TABLE_NAME NOT LIKE '%_BACKUP'
AND TABLE_NAME NOT LIKE '%_OLD%'
AND TABLE_NAME NOT LIKE '%TEMP%'
AND TABLE_NAME NOT LIKE 'STATS%'
AND TABLE_NAME NOT LIKE 'INO%'
AND TABLE_NAME NOT LIKE '%CLOB%'
AND TABLE_NAME NOT LIKE '%KHM%'
AND TABLE_NAME NOT LIKE '%TABLE%'
AND TABLE_NAME NOT LIKE '%BAK'
AND TABLE_NAME <>'LTCUST_LOG'
AND TABLE_NAME LIKE UPPER( IN_TABLE_LIKE_NAME )||'%'
AND LAST_ANALYZED < TRUNC( SYSDATE, 'YYYY' )
-- AND B.CREATED >SYSDATE -90
-- AND C.BYTES > 10737418240
GROUP BY C.OWNER, C.SEGMENT_NAME
ORDER BY C.OWNER, BYTES, C.SEGMENT_NAME
);
-- WHERE ROWNUM <= 2;

COMMIT;

--
--나중에 간단 셀렉트로 변경.
FOR RLIST in ( SELECT *
FROM TEMP_SPA_LOG_JHLEE
WHERE TASK_NAME = IN_TASK_NAME
AND SPA_GRP = (SELECT MAX( SPA_GRP ) FROM TEMP_SPA_LOG_JHLEE )
ORDER BY BYTES, TABLE_NAME
-- WHERE ROWNUM <= 2
) LOOP

UPDATE TEMP_SPA_LOG_JHLEE
SET STATUS = 'EXECUTE_TABLE_STATS'
, START_DT = SYSDATE
WHERE SPA_GRP = RLIST.SPA_GRP
AND TASK_NAME = RLIST.TASK_NAME
AND TABLE_NAME = RLIST.TABLE_NAME;

COMMIT;

EXECUTE IMMEDIATE RLIST.STAT_GET_DDL;

UPDATE TEMP_SPA_LOG_JHLEE
SET STATUS = 'COMPLETE_TABLE_STATS'
, END_DT = SYSDATE
WHERE SPA_GRP = RLIST.SPA_GRP
AND TASK_NAME = RLIST.TASK_NAME
AND TABLE_NAME = RLIST.TABLE_NAME;

COMMIT;

IF RLIST.BYTES >= 10737418240 THEN

V_AFTER_EXEC_NAME := IN_TASK_NAME||'_'||TO_CHAR( LPAD( TRUNC( DBMS_RANDOM.VALUE( 1, 9999999999 ) ),10, '0') );

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => IN_TASK_NAME, execution_type => 'TEST EXECUTE', EXECUTION_NAME => V_AFTER_EXEC_NAME );

UPDATE TEMP_SPA_LOG_JHLEE
SET STATUS = 'COMPLETE_AFTER_TASK'
, AFTER_EXEC_NAME = V_AFTER_EXEC_NAME
, END_DT = SYSDATE
WHERE SPA_GRP = RLIST.SPA_GRP
AND TASK_NAME = RLIST.TASK_NAME
AND TABLE_NAME = RLIST.TABLE_NAME;

COMMIT;

-- SPA 비교 분석
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => IN_TASK_NAME
, execution_type => 'compare performance'
, execution_params => dbms_advisor.arglist( 'execution_name1'
, IN_BEFORE_EXEC_NAME
, 'execution_name2'
, V_AFTER_EXEC_NAME
, 'comparison_metric'
, 'buffer_gets'
) );

UPDATE TEMP_SPA_LOG_JHLEE
SET STATUS = 'COMPLETE_FINAL_TASK'
, END_DT = SYSDATE
-- , AFTER_EXEC_NAME = V_AFTER_EXEC_NAME
-- , START_DT = SYSDATE
WHERE SPA_GRP = RLIST.SPA_GRP
AND TASK_NAME = RLIST.TASK_NAME
AND TABLE_NAME = RLIST.TABLE_NAME;

COMMIT;

UPDATE TEMP_SPA_LOG_JHLEE
SET REPORT = ( SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK( IN_TASK_NAME, 'HTML', 'ALL', 'ALL') FROM DUAL )
WHERE 1 = 1 --SPA_GRP = RLIST.SPA_GRP
AND TASK_NAME = RLIST.TASK_NAME
AND TABLE_NAME = RLIST.TABLE_NAME;

UPDATE TEMP_SPA_LOG_JHLEE
SET STATUS = 'COMPLETE_REPORT'
, END_DT = SYSDATE
-- , REPORT = ( SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK( IN_TASK_NAME||'FINAL'||RLIST.RN, 'HTML', 'ALL') FROM DUAL )
WHERE SPA_GRP = RLIST.SPA_GRP
AND TASK_NAME = RLIST.TASK_NAME
AND TABLE_NAME = RLIST.TABLE_NAME;

COMMIT;

END IF;

END LOOP;

END CREATE_AFTER_REPORT;

END PKG_SPA;
/
{CODE}