SQL 튜닝의 시작 (2013년)
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
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;
PACKAGE
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;
/
PACKAGE BODY
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}
"데이터베이스 스터디모임" 에서 2013년에 "SQL튜닝의시작
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3838
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.