[제목]
[2025-11-03] SPA 작업 시 소스 DB(AS-IS)에서
하나의 SQL_ID 기준 N개의 PHV(Plan Hash Value)가 있을 경우 타켓 DB(TO-BE)에서는 어떤 동작을 하는지
테스트
[테스트 개요]
<소스 db에서 하나의 SQL_ID 기준 여러 개의 PLAN_HASH_VALUE가 있는 경우>
SQL_ID 단위로 한 번만 실행하지만, 실행계획 레벨에서는 전부 비교된다
--> 타켓 DB에서는 그 하나의 SQL_ID를
실행 시킨 후 PLAN_HASH_VALUE가 도출 되면 소스에 있었던 여러 개의 PLAH_HASH_VALUE들과 비교하여
PLAN_HASH_VALUE가 다르다면 바뀌었다고 보고해줌
--> 소스 DB에서 PHV가 여러개 였던 SQL_ID를 타켓 DB에서 돌린 후 특정 PHV가 나오면
그거랑 소스 DB에 있었던 여러 개의 PHV와 비교해서 다르면 다르다고 말해줌
--> 결국 소스 DB에서 하나의 SQL_ID 기준 여러 개의 PHV가 존재하는 경우, 타켓 DB에서는 해당 SQL_ID를 실행 후 특정 PHV를 얻으면 그 PHV와 소스 DB에 있는 여러 개의 PHV와 비교하는 것임
[테스트 환경]
<소스
DB>
OS : Oracle Linux Server 7.9 (grep ^PRETTY_NAME= /etc/os-release | cut
-d= -f2- | tr -d '"')
OS Kernal : 5.4.17-2102.201.3.el7uek.x86_64 (uname
-r)
Oracle Version : Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production (echo -e "set pages 0 lines 200 feedback off
heading off\nselect banner from v\$version where banner like 'Oracle
Database%';" | sqlplus -s / as sysdba)
Oracle
Configuration
DB명 : ORA12RF (echo -e 'set pages 0
feedback off heading off verify off\nselect '\''DB명 : '\''||name from
v$database;' | sqlplus -s / as sysdba) '
PDB명 : non-cdb (echo -e "set
pages 0 feedback off heading off verify off\nselect 'PDB명 : '||name from v\$pdbs
where name <> 'PDB\$SEED';" | sqlplus -s / as sysdba)
RAC
Node 1
Hostname : ol7ora12rf1
(hostname)
Public IP : 192.168.240.11 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA12RF1 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
RAC Node 2
Hostname : ol7ora12rf2
(hostname)
Public IP : 192.168.240.12 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA12RF2 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
Patch Info
Grid (opatch lspatches) (grid os
user)
33610989;TOMCAT RELEASE UPDATE 12.2.0.1.0(ID:RELEASE)
(33610989)
26839277;DBWLM RELEASE UPDATE 12.2.0.1.0(ID:170913)
(26839277)
33116894;ACFS JUL 2021 RELEASE UPDATE 12.2.0.1.210720
(33116894)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118
(33678030)
33587128;Database Jan 2022 Release Update :
12.2.0.1.220118 (33587128)
Oracle (opatch lspatches) (oracle os
user)
33678030;OCW JAN 2022 RELEASE UPDATE 12.2.0.1.220118
(33678030)
33587128;Database Jan 2022 Release Update :
12.2.0.1.220118 (33587128)
<타켓
DB>
OS : Oracle Linux Server 8.10 (grep ^PRETTY_NAME= /etc/os-release | cut
-d= -f2- | tr -d '"')
OS Kernal : 5.15.0-206.153.7.1.el8uek.x86_64 (uname
-r)
Oracle Version : Oracle Database 19c Enterprise Edition Release
19.0.0.0.0 - Production (echo -e "set pages 0 lines 200 feedback off heading
off\nselect banner from v\$version where banner like 'Oracle Database%';" |
sqlplus -s / as sysdba)
Oracle Configuration
DB명
: ORA19RF (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''DB명 : '\''||name from v$database;' | sqlplus -s / as sysdba) '
PDB명 : non-cdb (echo -e "set pages 0 feedback off heading off verify off\nselect
'PDB명 : '||name from v\$pdbs where name <> 'PDB\$SEED';" | sqlplus -s / as
sysdba)
RAC Node 1
Hostname : ol8ora19rf1
(hostname)
Public IP : 192.168.240.31 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA19RS1 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
RAC Node 2
Hostname :
ol8ora19rf2
Public IP : 192.168.240.32 (getent ahostsv4
`hostname` | awk '{print $1; exit}')
Instance Name :
ORA19RS2 (echo -e 'set pages 0 feedback off heading off verify off\nselect
'\''Instance Name : '\''||instance_name from v$instance;' | sqlplus -s / as
sysdba) '
Patch Info
Grid (opatch lspatches) (grid os
user)
38124772;TOMCAT RELEASE UPDATE 19.0.0.0.0
(38124772)
37962946;OCW RELEASE UPDATE 19.28.0.0.0
(37962946)
37962938;ACFS RELEASE UPDATE 19.28.0.0.0
(37962938)
37960098;Database Release Update : 19.28.0.0.250715
(37960098)
36758186;DBWLM RELEASE UPDATE 19.0.0.0.0
(36758186)
Oracle (opatch lspatches) (oracle os
user)
37962946;OCW RELEASE UPDATE 19.28.0.0.0
(37962946)
37960098;Database Release Update : 19.28.0.0.250715
(37960098)
[내용]
1. 데이터 준비 (소스 DB 및 타켓 DB 모두)
--아래의 sql문을 실행 (소스 DB 및 타켓 DB
각각)
--소스 DB 및 타켓 DB 모두 ASM DISK GROUP 명은 +DATA1
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle/oracle_scripts]$ cat
1.USER_TABLESPACE_SYS_SYSTEM_v1_0.sql
----------------------------------------------------------------------------------------------------------------------------------------
--1.
사용자 계정 생성 및 테이블 스페이스
생성
----------------------------------------------------------------------------------------------------------------------------------------
DROP
USER TUNER CASCADE;
CREATE USER TUNER IDENTIFIED BY "oracle";
GRANT
RESOURCE, DBA, CONNECT TO TUNER;
ALTER USER TUNER ACCOUNT
UNLOCK;
DROP
TABLESPACE TUNER_DATA1 INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE
TUNER_DATA1
DATAFILE '+DATA1' SIZE 12G
AUTOEXTEND ON NEXT 512M MAXSIZE
UNLIMITED
;
DROP
TABLESPACE TUNER_IDX1 INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE
TUNER_IDX1
DATAFILE '+DATA1' SIZE 12G
AUTOEXTEND ON NEXT 512M MAXSIZE
UNLIMITED
;
DROP
TABLESPACE TUNER_TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY
TABLESPACE TUNER_TEMP
TEMPFILE '+DATA1' SIZE 4G
AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED
;
ALTER USER
TUNER DEFAULT TABLESPACE TUNER_DATA1;
ALTER USER TUNER TEMPORARY TABLESPACE
TUNER_TEMP;
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle/oracle_scripts]$ cat
2.TABLE_INDEX_MERGE_STATISTICS_TUNER_v1_1.sql
----------------------------------------------------------------------------------------------------------------------------------------
--2.
바인드 변수 세팅 (tuner 계정으로 실행할 것)
--SYS@PTDB> conn tuner
--Enter
password:
--Connected.
--TUNER@PTDB>
----------------------------------------------------------------------------------------------------------------------------------------
--conn
tuner;
VAR
V_TB_CUST_CNT NUMBER;
EXEC :V_TB_CUST_CNT := 1000000;
VAR
V_TB_ITEM_CNT NUMBER;
EXEC :V_TB_ITEM_CNT := 100000;
--:V_TB_CUST_CNT 1000000
String
--:V_TB_ITEM_CNT
100000 String
--예시
--V_TB_CUST_CNT가 100만인 경우
----TB_CUST :
100만
----TB_CUST_ADDR : 200만
----TB_ORD : 1000만
----TB_ORD_DTL :
5000만
----TB_DLVY: 3000만
--V_TB_ITEM_CNT가 10만인 경우
----TB_ITEM :
10만
----------------------------------------------------------------------------------------------------------------------------------------
--3.
테이블, 인덱스, 데이터입력, 통계정보
생성
----------------------------------------------------------------------------------------------------------------------------------------
ALTER
SESSION SET "_px_hybrid_TSM_HWMB_load"=FALSE;
ALTER SESSION SET
NLS_DATE_FORMAT='YYYY-MM-DD
HH24:MI:SS';
----------------------------------------------------------------------------------------------------------------------------------------
--시작시간
COLUMN
START_TIME NEW_VALUE START_TIME
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD
HH24:MI:SS.FF') AS START_TIME FROM
DUAL;
----------------------------------------------------------------------------------------------------------------------------------------
DROP
TABLE TUNER.TB_CUST PURGE;
DROP TABLE TUNER.TB_CUST_ADDR PURGE;
DROP TABLE
TUNER.TB_DLVY PURGE;
DROP TABLE TUNER.TB_ITEM PURGE;
DROP TABLE
TUNER.TB_ORD PURGE;
DROP TABLE TUNER.TB_ORD_DTL
PURGE;
----------------------------------------------------------------------------------------------------------------------------------------
CREATE
TABLE TUNER.TB_CUST
(
CUST_NO VARCHAR2(10) NOT NULL
, CUST_ID
VARCHAR2(20) NOT NULL
, CUST_NM VARCHAR2(50) NOT NULL
, BRTHDY
VARCHAR2(8)
, SEX_CD VARCHAR2(6) NOT NULL
, JOIN_DT VARCHAR2(14) NOT
NULL
, CUST_STS_CD VARCHAR2(6) NOT NULL
, INPUT_ID VARCHAR2(20) NOT
NULL
, INPUT_DT VARCHAR2(14) NOT NULL
, UPDT_ID VARCHAR2(20)
, UPDT_DT
VARCHAR2(14)
)
TABLESPACE TUNER_DATA1;
COMMENT ON
COLUMN TUNER.TB_CUST.CUST_NO IS '고객번호';
COMMENT ON COLUMN
TUNER.TB_CUST.CUST_ID IS '고객아이디';
COMMENT ON COLUMN TUNER.TB_CUST.CUST_NM IS
'고객명';
COMMENT ON COLUMN TUNER.TB_CUST.BRTHDY IS '생년월일';
COMMENT ON COLUMN
TUNER.TB_CUST.SEX_CD IS '성별코드';
COMMENT ON COLUMN TUNER.TB_CUST.JOIN_DT IS
'가입일시';
COMMENT ON COLUMN TUNER.TB_CUST.CUST_STS_CD IS '고객상태코드';
COMMENT
ON COLUMN TUNER.TB_CUST.INPUT_ID IS '입력아이디';
COMMENT ON COLUMN
TUNER.TB_CUST.INPUT_DT IS '입력일시';
COMMENT ON COLUMN TUNER.TB_CUST.UPDT_ID IS
'수정아이디';
COMMENT ON COLUMN TUNER.TB_CUST.UPDT_DT IS '수정일시';
COMMENT ON
TABLE TUNER.TB_CUST IS '고객';
ALTER TABLE TUNER.TB_CUST NOLOGGING;
INSERT /*+
APPEND */
INTO TUNER.TB_CUST
SELECT 'C' || LPAD(ROWNUM, 9, '0') AS
CUST_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS CUST_ID
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 50))) AS
CUST_NM
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(365*20, 365*80), 'YYYYMMDD') AS
BRTHDY
, 'SC' || LPAD(MOD(ROWNUM, 2), 4, '0') AS
SEX_CD
, TO_CHAR(TO_DATE('2030-12-31', 'YYYY-MM-DD')
- DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
JOIN_DT
, 'CSC' || LPAD(MOD(ROWNUM, 5), 3, '0') AS
CUST_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS UPDT_DT
FROM DUAL CONNECT BY LEVEL <=
:V_TB_CUST_CNT;
COMMIT;
CREATE
UNIQUE INDEX TUNER.PK_TB_CUST ON TUNER.TB_CUST(CUST_NO) TABLESPACE TUNER_IDX1
PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.PK_TB_CUST PARALLEL 1;
ALTER INDEX
TUNER.PK_TB_CUST LOGGING;
ALTER TABLE TUNER.TB_CUST ADD CONSTRAINT PK_TB_CUST
PRIMARY KEY (CUST_NO);
CREATE
INDEX TUNER.IDX_TB_CUST_01 ON TUNER.TB_CUST(CUST_STS_CD) TABLESPACE TUNER_IDX1
PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_01 PARALLEL 1;
ALTER
INDEX TUNER.IDX_TB_CUST_01 LOGGING;
CREATE
INDEX TUNER.IDX_TB_CUST_02 ON TUNER.TB_CUST(BRTHDY) TABLESPACE TUNER_IDX1
PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_02 PARALLEL 1;
ALTER
INDEX TUNER.IDX_TB_CUST_02 LOGGING;
CREATE
INDEX TUNER.IDX_TB_CUST_03 ON TUNER.TB_CUST(CUST_STS_CD, BRTHDY) TABLESPACE
TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_CUST_03 PARALLEL
1;
ALTER INDEX TUNER.IDX_TB_CUST_03 LOGGING;
ALTER TABLE
TUNER.TB_CUST
LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
CREATE
TABLE TUNER.TB_CUST_ADDR
(
CUST_NO VARCHAR2(10) NOT NULL
,
ADDR_SN NUMBER(6) NOT NULL
, POST_NO VARCHAR2(10) NOT NULL
, ADDR
VARCHAR2(500) NOT NULL
, REG_DT VARCHAR2(14) NOT NULL
, INPUT_ID
VARCHAR2(20) NOT NULL
, INPUT_DT VARCHAR2(14) NOT NULL
, UPDT_ID
VARCHAR2(20)
, UPDT_DT VARCHAR2(14)
)
TABLESPACE
TUNER_DATA1;
COMMENT ON
COLUMN TUNER.TB_CUST_ADDR.CUST_NO IS '고객번호';
COMMENT ON COLUMN
TUNER.TB_CUST_ADDR.ADDR_SN IS '주소순번';
COMMENT ON COLUMN
TUNER.TB_CUST_ADDR.POST_NO IS '우편번호';
COMMENT ON COLUMN
TUNER.TB_CUST_ADDR.ADDR IS '주소';
COMMENT ON COLUMN TUNER.TB_CUST_ADDR.REG_DT
IS '등록일시';
COMMENT ON COLUMN TUNER.TB_CUST_ADDR.INPUT_ID IS
'입력아이디';
COMMENT ON COLUMN TUNER.TB_CUST_ADDR.INPUT_DT IS '입력일시';
COMMENT
ON COLUMN TUNER.TB_CUST_ADDR.UPDT_ID IS '수정아이디';
COMMENT ON COLUMN
TUNER.TB_CUST_ADDR.UPDT_DT IS '수정일시';
COMMENT ON TABLE TUNER.TB_CUST_ADDR IS
'고객주소';
ALTER TABLE TUNER.TB_CUST_ADDR NOLOGGING;
INSERT /*+
APPEND */
INTO TUNER.TB_CUST_ADDR
SELECT
A.CUST_NO
, B.LVL AS
ADDR_SN
, TRUNC(DBMS_RANDOM.VALUE(11111, 99999)) AS
POST_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(50, 100))) AS ADDR
,
TO_CHAR(TO_DATE(A.JOIN_DT, 'YYYYMMDDHH24MISS') + TRUNC(DBMS_RANDOM.VALUE(1,
240))/24, 'YYYYMMDDHH24MISS') AS REG_DT
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS
INPUT_ID
, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS
INPUT_DT
, NULL AS
UPDT_ID
, NULL AS UPDT_DT
FROM
TUNER.TB_CUST A
, (SELECT LEVEL AS LVL FROM DUAL
CONNECT BY LEVEL <= 2) B
;
COMMIT;
CREATE
UNIQUE INDEX TUNER.PK_TB_CUST_ADDR ON TUNER.TB_CUST_ADDR (CUST_NO, ADDR_SN)
TABLESPACE TUNER_IDX1 PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.PK_TB_CUST_ADDR
PARALLEL 1;
ALTER INDEX TUNER.PK_TB_CUST_ADDR LOGGING;
ALTER TABLE
TUNER.TB_CUST_ADDR ADD CONSTRAINT PK_TB_CUST_ADDR PRIMARY KEY
(CUST_NO,ADDR_SN);
ALTER TABLE
TUNER.TB_CUST_ADDR
LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
CREATE
TABLE TUNER.TB_ORD
(
CUST_NO VARCHAR2(10) NOT
NULL
, ORD_DT VARCHAR2(14) NOT NULL
, ORD_TOT_AMT NUMBER(15,
2) NOT NULL
, ORD_STS_CD VARCHAR2(6) NOT NULL
, INPUT_ID
VARCHAR2(20) NOT NULL
, INPUT_DT VARCHAR2(14) NOT NULL
,
UPDT_ID VARCHAR2(20)
, UPDT_DT VARCHAR2(14)
)
PARTITION BY
RANGE(ORD_DT)
(
PARTITION P_BEF_2020 VALUES LESS THAN
('20200101')
, PARTITION P2020 VALUES LESS THAN ('20210101')
, PARTITION P2021 VALUES LESS THAN ('20220101')
, PARTITION P2022
VALUES LESS THAN ('20230101')
, PARTITION P2023 VALUES LESS THAN
('20240101')
, PARTITION P2024 VALUES LESS THAN ('20250101')
, PARTITION P2025 VALUES LESS THAN ('20260101')
, PARTITION P2026
VALUES LESS THAN ('20270101')
, PARTITION P2027 VALUES LESS THAN
('20280101')
, PARTITION P2028 VALUES LESS THAN ('20290101')
, PARTITION P2029 VALUES LESS THAN ('20300101')
, PARTITION P2030
VALUES LESS THAN ('20310101')
, PARTITION P_AFT_2030 VALUES LESS THAN
(MAXVALUE)
)
TABLESPACE TUNER_DATA1;
COMMENT ON
COLUMN TUNER.TB_ORD.CUST_NO IS '고객번호';
COMMENT ON COLUMN TUNER.TB_ORD.ORD_DT
IS '주문일시';
COMMENT ON COLUMN TUNER.TB_ORD.ORD_TOT_AMT IS '주문총금액';
COMMENT
ON COLUMN TUNER.TB_ORD.ORD_STS_CD IS '주문상태코드';
COMMENT ON COLUMN
TUNER.TB_ORD.INPUT_ID IS '입력아이디';
COMMENT ON COLUMN TUNER.TB_ORD.INPUT_DT IS
'입력일시';
COMMENT ON COLUMN TUNER.TB_ORD.UPDT_ID IS '수정아이디';
COMMENT ON
COLUMN TUNER.TB_ORD.UPDT_DT IS '수정일시';
COMMENT ON TABLE TUNER.TB_ORD IS
'주문';
ALTER TABLE TUNER.TB_ORD NOLOGGING;
INSERT /*+
APPEND */
INTO TUNER.TB_ORD
SELECT /*+ PARALEL(4)
ENABLE_PARALLEL_DML */
A.CUST_NO
, TO_CHAR(TO_DATE(A.JOIN_DT,
'YYYYMMDDHH24MISS') + B.LVL/24/60, 'YYYYMMDDHH24MISS') AS
ORD_DT
, 0 AS
ORD_TOT_AMT
, CASE MOD(ROWNUM, 4) WHEN 0 THEN
'OSC001' WHEN 1 THEN 'OSC002' WHEN 2 THEN 'OSC003' WHEN 3 THEN 'OSC004'
END AS ORD_STS_CD
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS UPDT_DT
FROM
TUNER.TB_CUST A
, (SELECT LEVEL AS LVL FROM DUAL
CONNECT BY LEVEL <= 10) B
;
COMMIT;
CREATE
UNIQUE INDEX TUNER.PK_TB_ORD ON TUNER.TB_ORD(CUST_NO, ORD_DT) TABLESPACE
TUNER_IDX1 PARALLEL 4 LOCAL NOLOGGING;
ALTER INDEX TUNER.PK_TB_ORD PARALLEL
1;
ALTER INDEX TUNER.PK_TB_ORD LOGGING;
ALTER
TABLE TUNER.TB_ORD ADD CONSTRAINT PK_TB_ORD PRIMARY KEY (CUST_NO,
ORD_DT);
CREATE
INDEX TUNER.IDX_TB_ORD_01 ON TUNER.TB_ORD(ORD_STS_CD) TABLESPACE TUNER_IDX1
LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_ORD_01 PARALLEL
1;
ALTER INDEX TUNER.IDX_TB_ORD_01 LOGGING;
ALTER TABLE
TUNER.TB_ORD
LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
CREATE
TABLE TUNER.TB_DLVY
(
CUST_NO VARCHAR2(10) NOT NULL
, ORD_DT
VARCHAR2(14) NOT NULL
, DLVY_SN NUMBER(6) NOT NULL
, DLVY_DT VARCHAR2(14)
NOT NULL
, DLVY_STS_CD VARCHAR2(6) NOT NULL
, HDRY_CMPNY_CD
VARCHAR2(6)
, WAYBIL_NO VARCHAR2(50)
, ADDR_SN NUMBER(6) NOT NULL
,
INPUT_ID VARCHAR2(20) NOT NULL
, INPUT_DT VARCHAR2(14) NOT NULL
, UPDT_ID
VARCHAR2(20)
, UPDT_DT VARCHAR2(14)
)
PARTITION BY RANGE
(ORD_DT)
SUBPARTITION BY HASH (CUST_NO)
SUBPARTITIONS 8
(
PARTITION P_BEF_2020 VALUES LESS THAN ('20200101')
, PARTITION P2020 VALUES
LESS THAN ('20210101')
, PARTITION P2021 VALUES LESS THAN ('20220101')
,
PARTITION P2022 VALUES LESS THAN ('20230101')
, PARTITION P2023 VALUES LESS
THAN ('20240101')
, PARTITION P2024 VALUES LESS THAN ('20250101')
,
PARTITION P2025 VALUES LESS THAN ('20260101')
, PARTITION P2026 VALUES LESS
THAN ('20270101')
, PARTITION P2027 VALUES LESS THAN ('20280101')
,
PARTITION P2028 VALUES LESS THAN ('20290101')
, PARTITION P2029 VALUES LESS
THAN ('20300101')
, PARTITION P2030 VALUES LESS THAN ('20310101')
,
PARTITION P_AFT_2030 VALUES LESS THAN (MAXVALUE)
)
TABLESPACE
TUNER_DATA1;
COMMENT ON
COLUMN TUNER.TB_DLVY.CUST_NO IS '고객번호';
COMMENT ON COLUMN
TUNER.TB_DLVY.ORD_DT IS '주문일시';
COMMENT ON COLUMN TUNER.TB_DLVY.DLVY_SN IS
'배송순번';
COMMENT ON COLUMN TUNER.TB_DLVY.DLVY_DT IS '배송일시';
COMMENT ON
COLUMN TUNER.TB_DLVY.DLVY_STS_CD IS '배송상태코드';
COMMENT ON COLUMN
TUNER.TB_DLVY.HDRY_CMPNY_CD IS '택배사코드';
COMMENT ON COLUMN
TUNER.TB_DLVY.WAYBIL_NO IS '운송장번호';
COMMENT ON COLUMN TUNER.TB_DLVY.ADDR_SN
IS '주소순번';
COMMENT ON COLUMN TUNER.TB_DLVY.INPUT_ID IS '입력아이디';
COMMENT ON
COLUMN TUNER.TB_DLVY.INPUT_DT IS '입력일시';
COMMENT ON COLUMN
TUNER.TB_DLVY.UPDT_ID IS '수정아이디';
COMMENT ON COLUMN TUNER.TB_DLVY.UPDT_DT IS
'수정일시';
COMMENT ON TABLE TUNER.TB_DLVY IS '배송';
ALTER TABLE TUNER.TB_DLVY NOLOGGING;
INSERT /*+
ENABLE_PARALLEL_DML APPEND */
INTO TUNER.TB_DLVY
SELECT /*+
PARALLEL(4) LEADING(A B L) USE_HASH(L) NO_MERGE(L) NO_SWAP_JOIN_INPUTS(L)
*/
A.CUST_NO
, A.ORD_DT
, B.LVL AS
DLVY_SN
, TO_CHAR(TO_DATE(A.ORD_DT,
'YYYYMMDDHH24MISS') + TRUNC(DBMS_RANDOM.VALUE(1, 240))/24, 'YYYYMMDDHH24MISS')
AS DLVY_DT
, 'DSC' || LPAD(MOD(ROWNUM, 5), 3, '0')
AS DLVY_STS_CD
, 'HCC' || LPAD(MOD(ROWNUM, 20), 3,
'0') AS HDRY_CMPNY_CD
, LPAD(ROWNUM, 20, '0') AS
WAYBIL_NO
, L.ADDR_SN
,
DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS
INPUT_ID
, TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS
INPUT_DT
, NULL AS
UPDT_ID
, NULL AS UPDT_DT
FROM
TUNER.TB_ORD A
INNER JOIN (SELECT LEVEL LVL FROM DUAL CONNECT BY LEVEL
<= 3) B ON (1=1)
LEFT OUTER JOIN
(
SELECT
*
FROM
(
SELECT L.CUST_NO, L.ADDR_SN, ROW_NUMBER() OVER (PARTITION BY L.CUST_NO ORDER BY
DBMS_RANDOM.VALUE) AS
RN
FROM TUNER.TB_CUST_ADDR
L
)
L
WHERE L.RN =
1
)
L
ON (A.CUST_NO = L.CUST_NO)
;
COMMIT;
CREATE
UNIQUE INDEX TUNER.PK_TB_DLVY ON TUNER.TB_DLVY(CUST_NO, ORD_DT, DLVY_SN)
TABLESPACE TUNER_IDX1 LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX
TUNER.PK_TB_DLVY PARALLEL 1;
ALTER INDEX TUNER.PK_TB_DLVY LOGGING;
ALTER
TABLE TUNER.TB_DLVY ADD CONSTRAINT PK_TB_DLVY PRIMARY KEY (CUST_NO, ORD_DT,
DLVY_SN);
CREATE
INDEX TUNER.IDX_TB_DLVY_01 ON TUNER.TB_DLVY(CUST_NO, ADDR_SN) TABLESPACE
TUNER_IDX1 LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_DLVY_01
PARALLEL 1;
ALTER INDEX TUNER.IDX_TB_DLVY_01 LOGGING;
CREATE
INDEX TUNER.IDX_TB_DLVY_02 ON TUNER.TB_DLVY(DLVY_STS_CD, DLVY_DT) TABLESPACE
TUNER_IDX1 LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_DLVY_02
PARALLEL 1;
ALTER INDEX TUNER.IDX_TB_DLVY_02 LOGGING;
CREATE
INDEX TUNER.IDX_TB_DLVY_03 ON TUNER.TB_DLVY(HDRY_CMPNY_CD, WAYBIL_NO) TABLESPACE
TUNER_IDX1 LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_DLVY_03
PARALLEL 1;
ALTER INDEX TUNER.IDX_TB_DLVY_03 LOGGING;
ALTER TABLE
TUNER.TB_DLVY
LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
CREATE
TABLE TUNER.TB_ITEM
(
ITEM_NO VARCHAR2(10) NOT NULL
, ITEM_NM
VARCHAR2(500) NOT NULL
, ITEM_PRC NUMBER(15, 2) NOT NULL
, REG_DT
VARCHAR2(14) NOT NULL
, INPUT_ID VARCHAR2(20) NOT NULL
, INPUT_DT
VARCHAR2(14) NOT NULL
, UPDT_ID VARCHAR2(20)
, UPDT_DT
VARCHAR2(14)
)
TABLESPACE TUNER_DATA1;
COMMENT ON
COLUMN TUNER.TB_ITEM.ITEM_NO IS '상품번호';
COMMENT ON COLUMN
TUNER.TB_ITEM.ITEM_NM IS '상품명';
COMMENT ON COLUMN TUNER.TB_ITEM.ITEM_PRC IS
'상품가격';
COMMENT ON COLUMN TUNER.TB_ITEM.REG_DT IS '등록일시';
COMMENT ON
COLUMN TUNER.TB_ITEM.INPUT_ID IS '입력아이디';
COMMENT ON COLUMN
TUNER.TB_ITEM.INPUT_DT IS '입력일시';
COMMENT ON COLUMN TUNER.TB_ITEM.UPDT_ID IS
'수정아이디';
COMMENT ON COLUMN TUNER.TB_ITEM.UPDT_DT IS '수정일시';
COMMENT ON
TABLE TUNER.TB_ITEM IS '상품';
ALTER TABLE
TUNER.TB_ITEM NOLOGGING;
INSERT /*+ APPEND */
INTO
TUNER.TB_ITEM
SELECT 'I' || LPAD(ROWNUM, 9, '0') AS
ITEM_NO
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(50, 150))) AS ITEM_NM
,
TRUNC(DBMS_RANDOM.VALUE(5000, 100000), -3) AS
ITEM_PRC
, TO_CHAR(TO_DATE('2030-12-31',
'YYYY-MM-DD') - DBMS_RANDOM.VALUE(0, 365*10), 'YYYYMMDDHH24MISS') AS
REG_DT
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(1, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS UPDT_DT
FROM DUAL CONNECT BY LEVEL <=
:V_TB_ITEM_CNT;
COMMIT;
CREATE
UNIQUE INDEX TUNER.PK_TB_ITEM ON TUNER.TB_ITEM ( ITEM_NO ) TABLESPACE TUNER_IDX1
PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.PK_TB_ITEM PARALLEL 1;
ALTER INDEX
TUNER.PK_TB_ITEM LOGGING;
ALTER TABLE TUNER.TB_ITEM ADD CONSTRAINT PK_TB_ITEM
PRIMARY KEY (ITEM_NO);
ALTER TABLE
TUNER.TB_ITEM
LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
CREATE
TABLE TUNER.TB_ORD_DTL
(
CUST_NO VARCHAR2(10) NOT NULL
, ORD_DT
VARCHAR2(14) NOT NULL
, ORD_DTL_SN NUMBER(6) NOT NULL
, ORD_DTL_STS_CD
VARCHAR2(6) NOT NULL
, ORD_AMT NUMBER(15, 2) NOT NULL
, ITEM_NO
VARCHAR2(10) NOT NULL
, DLVY_SN VARCHAR2(14)
, INPUT_ID VARCHAR2(20) NOT
NULL
, INPUT_DT VARCHAR2(14) NOT NULL
, UPDT_ID VARCHAR2(20)
, UPDT_DT
VARCHAR2(14)
)
PARTITION BY RANGE (ORD_DT)
SUBPARTITION BY HASH
(CUST_NO)
SUBPARTITIONS 8
(
PARTITION P_BEF_2020 VALUES LESS
THAN ('20200101')
, PARTITION P2020 VALUES LESS THAN ('20210101')
,
PARTITION P2021 VALUES LESS THAN ('20220101')
, PARTITION P2022 VALUES LESS
THAN ('20230101')
, PARTITION P2023 VALUES LESS THAN ('20240101')
,
PARTITION P2024 VALUES LESS THAN ('20250101')
, PARTITION P2025 VALUES LESS
THAN ('20260101')
, PARTITION P2026 VALUES LESS THAN ('20270101')
,
PARTITION P2027 VALUES LESS THAN ('20280101')
, PARTITION P2028 VALUES LESS
THAN ('20290101')
, PARTITION P2029 VALUES LESS THAN ('20300101')
,
PARTITION P2030 VALUES LESS THAN ('20310101')
, PARTITION P_AFT_2030 VALUES
LESS THAN (MAXVALUE)
)
TABLESPACE TUNER_DATA1;
COMMENT ON
COLUMN TUNER.TB_ORD_DTL.CUST_NO IS '고객번호';
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.ORD_DT IS '주문일시';
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.ORD_DTL_SN IS '주문상세순번';
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.ORD_DTL_STS_CD IS '주문상세상태코드';
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.ORD_AMT IS '주문금액';
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.ITEM_NO IS '상품번호';
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.DLVY_SN IS '배송순번';
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.INPUT_ID IS '입력아이디';
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.INPUT_DT IS '입력일시';
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.UPDT_ID IS '수정아이디';
COMMENT ON COLUMN
TUNER.TB_ORD_DTL.UPDT_DT IS '수정일시';
COMMENT ON TABLE TUNER.TB_ORD_DTL IS
'주문상세';
ALTER TABLE TUNER.TB_ORD_DTL NOLOGGING;
INSERT /*+
ENABLE_PARALLEL_DML APPEND */
INTO TUNER.TB_ORD_DTL
SELECT /*+
PARALLEL(4) NO_MERGE(A) LEADING(A) USE_HASH(B)
*/A.CUST_NO
, A.ORD_DT
,
A.ORD_DTL_SN
, 'ODSC' || LPAD(MOD(ROWNUM, 5), 2,
'0') AS ORD_DTL_STS_CD
, B.ITEM_PRC AS
ORD_AMT
, A.ITEM_NO
-- ,
CASE WHEN A.ORD_DTL_SN > A.MAX_DLVY_SN THEN MOD(A.ORD_DTL_SN,
A.MAX_DLVY_SN)
-- ELSE
A.DLVY_SN
--END AS DLVY_SN
, CASE WHEN
A.ORD_DTL_SN > A.MAX_DLVY_SN THEN
NULL
ELSE A.DLVY_SN
END
AS DLVY_SN
, DBMS_RANDOM.STRING('A',
TRUNC(DBMS_RANDOM.VALUE(10, 20))) AS INPUT_ID
,
TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') AS INPUT_DT
,
NULL AS UPDT_ID
, NULL AS UPDT_DT
FROM
(
SELECT /*+ LEADING(A B L) USE_HASH(L) NO_MERGE(L) NO_SWAP_JOIN_INPUTS(L) */
A.CUST_NO
,
A.ORD_DT
, B.LVL AS
ORD_DTL_SN
, 0 AS
ORD_AMT
, 'I' || LPAD(TRUNC(DBMS_RANDOM.VALUE(1, :V_TB_ITEM_CNT)), 9, '0') AS
ITEM_NO
,
L.DLVY_SN
, MAX(L.DLVY_SN) OVER (PARTITION BY A.CUST_NO, A.ORD_DT ORDER BY
L.DLVY_SN
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS
MAX_DLVY_SN
FROM
TUNER.TB_ORD A
INNER
JOIN (SELECT LEVEL LVL FROM DUAL CONNECT BY LEVEL <= 5) B ON
(1=1)
LEFT OUTER JOIN
(
SELECT L.CUST_NO, L.ORD_DT,
L.DLVY_SN
FROM
(
SELECT L.CUST_NO, L.ORD_DT,
L.DLVY_SN
FROM TUNER.TB_DLVY
L
)
L
) L
ON (L.CUST_NO =
A.CUST_NO AND L.ORD_DT = A.ORD_DT AND L.DLVY_SN =
B.LVL)
) A
INNER JOIN TUNER.TB_ITEM B
ON
(A.ITEM_NO = B.ITEM_NO)
;
COMMIT;
CREATE
UNIQUE INDEX TUNER.PK_TB_ORD_DTL ON TUNER.TB_ORD_DTL( CUST_NO,ORD_DT,ORD_DTL_SN
) TABLESPACE TUNER_IDX1 LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX
TUNER.PK_TB_ORD_DTL PARALLEL 1;
ALTER INDEX TUNER.PK_TB_ORD_DTL
LOGGING;
ALTER TABLE TUNER.TB_ORD_DTL ADD CONSTRAINT PK_TB_ORD_DTL PRIMARY
KEY ( CUST_NO,ORD_DT,ORD_DTL_SN );
CREATE
INDEX TUNER.IDX_TB_ORD_DTL_01 ON TUNER.TB_ORD_DTL(ITEM_NO) TABLESPACE TUNER_IDX1
LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_ORD_DTL_01 PARALLEL
1;
ALTER INDEX TUNER.IDX_TB_ORD_DTL_01 LOGGING;
CREATE
INDEX TUNER.IDX_TB_ORD_DTL_02 ON TUNER.TB_ORD_DTL(ORD_DTL_STS_CD) TABLESPACE
TUNER_IDX1 LOCAL PARALLEL 4 NOLOGGING;
ALTER INDEX TUNER.IDX_TB_ORD_DTL_02
PARALLEL 1;
ALTER INDEX TUNER.IDX_TB_ORD_DTL_02 LOGGING;
ALTER TABLE
TUNER.TB_ORD_DTL
LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE
TUNER.TB_ORD NOLOGGING;
ALTER INDEX TUNER.PK_TB_ORD NOLOGGING;
ALTER INDEX
TUNER.IDX_TB_ORD_01 NOLOGGING;
--MERGE문이라 REDO LOG가 쌓이는건 막을수 없음 ARCHIVE LOG가
과도하게 쌓이는 것을 방지하려면 NO ARCHIVE LOG 모드로 설정 후 돌려야함
MERGE /*+ PARALLEL(4)
ENABLE_PARALLEL_DML LEADING(A Z) USE_NL(Z) */ INTO TUNER.TB_ORD Z
USING
(
SELECT
/*+ LEADING(A B) USE_HASH(B) */ A.CUST_NO, A.ORD_DT, SUM(B.ORD_AMT) AS
ORD_TOT_AMT
FROM TUNER.TB_ORD
A
, TUNER.TB_ORD_DTL
B
WHERE
1=1
AND A.CUST_NO =
B.CUST_NO
AND A.ORD_DT =
B.ORD_DT
GROUP BY A.CUST_NO, A.ORD_DT
)
A
ON (Z.CUST_NO = A.CUST_NO AND Z.ORD_DT = A.ORD_DT)
WHEN MATCHED THEN UPDATE SET Z.ORD_TOT_AMT =
A.ORD_TOT_AMT
;
COMMIT;
ALTER TABLE TUNER.TB_ORD LOGGING;
ALTER INDEX
TUNER.PK_TB_ORD LOGGING;
ALTER INDEX TUNER.IDX_TB_ORD_01
LOGGING;
----------------------------------------------------------------------------------------------------------------------------------------
EXEC
DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_CUST', method_opt => 'for all
indexed columns' , cascade => true, DEGREE=> 4);
EXEC
DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_CUST_ADDR', method_opt => 'for all
indexed columns' , cascade => true, DEGREE=> 4);
EXEC
DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_DLVY', method_opt => 'for all
indexed columns' , cascade => true, DEGREE=> 4);
EXEC
DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_ITEM', method_opt => 'for all
indexed columns' , cascade => true, DEGREE=> 4);
EXEC
DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_ORD', method_opt => 'for all
indexed columns' , cascade => true, DEGREE=> 4);
EXEC
DBMS_STATS.GATHER_TABLE_STATS('TUNER', 'TB_ORD_DTL', method_opt => 'for all
indexed columns' , cascade => true, DEGREE=>
4);
----------------------------------------------------------------------------------------------------------------------------------------
SELECT
/*+ PARALLEL(4) */
A.*
FROM
(
SELECT 'TUNER.TB_CUST' AS TABLE_NAME, COUNT(*) AS CNT FROM TUNER.TB_CUST
A
UNION
ALL
SELECT 'TUNER.TB_CUST_ADDR' AS
TABLE_NAME, COUNT(*) AS CNT FROM TUNER.TB_CUST_ADDR
A
UNION
ALL
SELECT /*+ INDEX_FFS(A) */
'TUNER.TB_DLVY' AS TABLE_NAME, COUNT(*) AS CNT FROM TUNER.TB_DLVY
A
UNION
ALL
SELECT 'TUNER.TB_ITEM' AS
TABLE_NAME, COUNT(*) AS CNT FROM TUNER.TB_ITEM
A
UNION
ALL
SELECT 'TUNER.TB_ORD' AS
TABLE_NAME, COUNT(*) AS CNT FROM TUNER.TB_ORD
A
UNION
ALL
SELECT /*+ INDEX_FFS(A) */
'TUNER.TB_ORD_DTL' AS TABLE_NAME, COUNT(*) AS CNT FROM TUNER.TB_ORD_DTL
A
)
A
;
----------------------------------------------------------------------------------------------------------------------------------------
--종료시간
COLUMN
END_TIME NEW_VALUE END_TIME
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD
HH24:MI:SS.FF') AS END_TIME FROM
DUAL;
----------------------------------------------------------------------------------------------------------------------------------------
--경과시간
SELECT
(TO_TIMESTAMP('&END_TIME', 'YYYY-MM-DD
HH24:MI:SS.FF')
-
TO_TIMESTAMP('&START_TIME', 'YYYY-MM-DD HH24:MI:SS.FF')) AS
ELAPSED_TIME
FROM
DUAL;
----------------------------------------------------------------------------------------------------------------------------------------
2. 소스 DB에서
SPA 전용 Oracle User 생성
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
ss
alias ss='rlwrap sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 18:31:18 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$
DROP USER SPA CASCADE;
CREATE USER
SPA IDENTIFIED BY "oracle";
GRANT RESOURCE, DBA, CONNECT TO SPA;
ALTER
USER SPA ACCOUNT UNLOCK;
DROP
TABLESPACE SPA_DATA1 INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE
SPA_DATA1
DATAFILE '+DATA1' SIZE 1G
AUTOEXTEND ON NEXT 512M MAXSIZE
UNLIMITED
;
DROP
TABLESPACE SPA_IDX1 INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE
SPA_IDX1
DATAFILE '+DATA1' SIZE 1G
AUTOEXTEND ON NEXT 512M MAXSIZE
UNLIMITED
;
DROP
TABLESPACE SPA_TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY
TABLESPACE SPA_TEMP
TEMPFILE '+DATA1' SIZE 128M
AUTOEXTEND ON NEXT 100M
MAXSIZE UNLIMITED
;
ALTER USER
SPA DEFAULT TABLESPACE SPA_DATA1;
ALTER USER SPA TEMPORARY TABLESPACE
SPA_TEMP;
3. 소스 DB에서 spa oracle user에게 dbms_lock 패키지 사용 권한 주기
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
alias ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 18:35:14 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$ GRANT EXECUTE ON DBMS_LOCK TO SPA;
Grant succeeded.
Elapsed:
00:00:00.02
[ol7ora12rf1]<SYS@ORA12RF1>$ GRANT EXECUTE ON DBMS_LOCK TO
TUNER;
Grant succeeded.
Elapsed:
00:00:00.01
[ol7ora12rf1]<SYS@ORA12RF1>$ quit
Disconnected from Oracle Database 12c Enterprise Edition
Release 12.2.0.1.0 - 64bit Production
--만약 권한을 회수해야 한다면 아래와 같이 실행하면
됨
--REVOKE EXECUTE ON DBMS_LOCK FROM SPA;
--REVOKE EXECUTE ON DBMS_LOCK FROM TUNER;
4. 소스 DB의 AWR 스냅샷
drop 및 sharead pool/buffer cache 제거
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 18:38:02 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$
DECLARE
V_LOW_SNAP_ID NUMBER;
V_HIGH_SNAP_ID NUMBER;
BEGIN
SELECT MIN(SNAP_ID),
MAX(SNAP_ID)
INTO V_LOW_SNAP_ID, V_HIGH_SNAP_ID
FROM DBA_HIST_SNAPSHOT;
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID => V_LOW_SNAP_ID,
HIGH_SNAP_ID => V_HIGH_SNAP_ID
);
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:02:03.23
--모든 노드에서 각각 실행
--1번 노드
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 18:40:29 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$ ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.17
[ol7ora12rf1]<SYS@ORA12RF1>$ ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed: 00:00:00.39
--2번 노드
[ORA12RF2:oracle@ol7ora12rf2][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12RF2:oracle@ol7ora12rf2][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 18:38:34 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12rf2]<SYS@ORA12RF2>$ ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
Elapsed: 00:00:00.16
[ol7ora12rf2]<SYS@ORA12RF2>$ ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
Elapsed:
00:00:00.33
5. SQL문 실행
--1번 노드
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
st
alias st='rlwrap
sqlplus tuner/oracle'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
st
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 19:16:28 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sat Nov 08 2025 19:13:08 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
[ol7ora12rf1]<TUNER@ORA12RF1>$
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 100;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = FALSE;
VARIABLE V_BRTHDY VARCHAR2(32);
EXEC
:V_BRTHDY := '19910528';
SELECT /*
SPA_TEST_ORA12RF1 SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
--SQL_ID 조회
SELECT sql_id,
child_number, plan_hash_value, last_active_time
FROM v$sql
WHERE sql_text LIKE '%SPA_TEST_ORA12RF1
SELECT_FROM_TB_CUST_WHERE_BRTHDY%'
and
sql_text not LIKE '%v$sql%'
;
SQL_ID
CHILD_NUMBER PLAN_HASH_VALUE LAST_ACTIVE_TIME
--------------------------------------- ------------
--------------- -------------------
93uyt37jadcu3
0 738787558
2025-11-08:21:18:17
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('93uyt37jadcu3', 0, 'ALLSTATS LAST -ROWS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 93uyt37jadcu3, child number 0
-------------------------------------
SELECT /* SPA_TEST_ORA12RF1 SELECT_FROM_TB_CUST_WHERE_BRTHDY
*/
* FROM TB_CUST A WHERE A.BRTHDY LIKE
:V_BRTHDY || '%'
Plan hash value: 738787558
--------------------------------------------------------------------------------------------------------
| Id |
Operation
| Name | Starts |
A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
|
| 1 | 31 |00:00:00.01
| 37 | 34 |
| 1 |
TABLE ACCESS BY INDEX ROWID| TB_CUST
| 1 | 31 |00:00:00.01
| 37 | 34 |
|* 2 | INDEX RANGE
SCAN | IDX_TB_CUST_02
| 1 | 31 |00:00:00.01
| 6 | 3 |
--------------------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 -
access("A"."BRTHDY" LIKE :V_BRTHDY||'%')
filter("A"."BRTHDY"
LIKE :V_BRTHDY||'%')
21 rows selected.
Elapsed:
00:00:00.17
--> INDEX RANGE SCAN + NO BATCHED IO
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 100;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = TRUE; --> BATCHED I/O 켜기
VARIABLE V_BRTHDY VARCHAR2(32);
EXEC
:V_BRTHDY := '19910528';
SELECT /*
SPA_TEST_ORA12RF1 SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
SELECT sql_id,
child_number, plan_hash_value, last_active_time
FROM v$sql
WHERE sql_text LIKE '%SPA_TEST_ORA12RF1
SELECT_FROM_TB_CUST_WHERE_BRTHDY%'
and
sql_text not LIKE '%v$sql%'
order by last_active_time
desc
;
SQL_ID
CHILD_NUMBER PLAN_HASH_VALUE LAST_ACTIVE_TIME
--------------------------------------- ------------
--------------- -------------------
93uyt37jadcu3
1 1470838782 2025-11-08:21:21:07
93uyt37jadcu3
0 738787558
2025-11-08:21:18:17
2 rows selected.
Elapsed: 00:00:00.01
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('93uyt37jadcu3', 1, 'ALLSTATS LAST -ROWS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 93uyt37jadcu3, child number 1
-------------------------------------
SELECT /* SPA_TEST_ORA12RF1 SELECT_FROM_TB_CUST_WHERE_BRTHDY
*/
* FROM TB_CUST A WHERE A.BRTHDY LIKE
:V_BRTHDY || '%'
Plan hash value: 1470838782
-------------------------------------------------------------------------------------------------------
| Id |
Operation
| Name | Starts |
A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
|
| 1 | 31 |00:00:00.01
| 37 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED|
TB_CUST
| 1 | 31 |00:00:00.01
| 37 |
|* 2
| INDEX RANGE
SCAN
| IDX_TB_CUST_02 | 1 | 31
|00:00:00.01 | 6 |
-------------------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 -
access("A"."BRTHDY" LIKE :V_BRTHDY||'%')
filter("A"."BRTHDY"
LIKE :V_BRTHDY||'%')
21 rows selected.
Elapsed: 00:00:00.03
--> INDEX RANGE SCAN + BATCHED
I/O
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 10000; --> 1만으로 변경하여 TABLE FULL SCAN이 유리하도록
판단하게함
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = TRUE;
VARIABLE V_BRTHDY VARCHAR2(32);
EXEC
:V_BRTHDY := '19910528';
SELECT /*
SPA_TEST_ORA12RF1 SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
SELECT sql_id,
child_number, plan_hash_value, last_active_time
FROM v$sql
WHERE sql_text LIKE '%SPA_TEST_ORA12RF1
SELECT_FROM_TB_CUST_WHERE_BRTHDY%'
and
sql_text not LIKE '%v$sql%'
order by last_active_time
desc
;
SQL_ID
CHILD_NUMBER PLAN_HASH_VALUE LAST_ACTIVE_TIME
--------------------------------------- ------------
--------------- -------------------
93uyt37jadcu3
2 482704130 2025-11-08:21:22:09
93uyt37jadcu3
1 1470838782 2025-11-08:21:21:07
93uyt37jadcu3
0 738787558
2025-11-08:21:18:17
3 rows selected.
Elapsed: 00:00:00.00
SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR('93uyt37jadcu3', 2, 'ALLSTATS LAST -ROWS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 93uyt37jadcu3, child number 2
-------------------------------------
SELECT /* SPA_TEST_ORA12RF1 SELECT_FROM_TB_CUST_WHERE_BRTHDY
*/
* FROM TB_CUST A WHERE A.BRTHDY LIKE
:V_BRTHDY || '%'
Plan hash value: 482704130
---------------------------------------------------------------------------------------
| Id |
Operation |
Name | Starts | A-Rows | A-Time |
Buffers | Reads |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 | 31 |00:00:00.17
| 18220 | 18177 |
|* 1 | TABLE
ACCESS FULL| TB_CUST | 1 |
31 |00:00:00.17 | 18220 | 18177 |
---------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."BRTHDY" LIKE :V_BRTHDY||'%')
19 rows selected.
Elapsed: 00:00:00.04
--> TABLE FULL SCAN
--2번 노드 (1번노드와 동일하게 작업함)
[ORA12RF2:oracle@ol7ora12rf2][/home/oracle]$ alias st
alias st='rlwrap sqlplus
tuner/oracle'
[ORA12RF2:oracle@ol7ora12rf2][/home/oracle]$
st
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 19:16:44 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sat Nov 08 2025 19:16:28 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
[ol7ora12rf2]<TUNER@ORA12RF2>$
ALTER SESSION SET STATISTICS_LEVEL = ALL;
ALTER SESSION SET optimizer_index_cost_adj = 100;
ALTER SESSION SET "_optimizer_batch_table_access_by_rowid" =
FALSE;
VARIABLE V_BRTHDY VARCHAR2(32);
EXEC
:V_BRTHDY := '19910528';
SELECT /*
SPA_TEST_ORA12RF2 SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
SELECT sql_id,
child_number, plan_hash_value, last_active_time
FROM v$sql
WHERE sql_text LIKE '%SPA_TEST_ORA12RF2
SELECT_FROM_TB_CUST_WHERE_BRTHDY%'
and
sql_text not LIKE '%v$sql%'
order by last_active_time
desc
;
SQL_ID
CHILD_NUMBER PLAN_HASH_VALUE LAST_ACTIVE_TIME
--------------------------------------- ------------
--------------- -------------------
828m0ghwzfryd
0 738787558
2025-11-08:21:23:50
1 rows selected.
Elapsed: 00:00:00.01
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('828m0ghwzfryd', 0, 'ALLSTATS LAST -ROWS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 828m0ghwzfryd, child number 0
-------------------------------------
SELECT /* SPA_TEST_ORA12RF2 SELECT_FROM_TB_CUST_WHERE_BRTHDY
*/
* FROM TB_CUST A WHERE A.BRTHDY LIKE
:V_BRTHDY || '%'
Plan hash value: 738787558
--------------------------------------------------------------------------------------------------------
| Id |
Operation
| Name | Starts |
A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
|
| 1 | 31 |00:00:00.06
| 38 | 4 |
| 1 |
TABLE ACCESS BY INDEX ROWID| TB_CUST
| 1 | 31 |00:00:00.06
| 38 | 4 |
|* 2 | INDEX RANGE
SCAN | IDX_TB_CUST_02
| 1 | 31 |00:00:00.01
| 6 | 4 |
--------------------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 -
access("A"."BRTHDY" LIKE :V_BRTHDY||'%')
filter("A"."BRTHDY"
LIKE :V_BRTHDY||'%')
21 rows selected.
Elapsed:
00:00:00.49
ALTER SESSION SET STATISTICS_LEVEL = ALL;
ALTER SESSION SET optimizer_index_cost_adj = 100;
ALTER SESSION SET "_optimizer_batch_table_access_by_rowid" =
TRUE;
VARIABLE V_BRTHDY VARCHAR2(32);
EXEC
:V_BRTHDY := '19910528';
SELECT /*
SPA_TEST_ORA12RF2 SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
SELECT sql_id,
child_number, plan_hash_value, last_active_time
FROM v$sql
WHERE sql_text LIKE '%SPA_TEST_ORA12RF2
SELECT_FROM_TB_CUST_WHERE_BRTHDY%'
and
sql_text not LIKE '%v$sql%'
order by last_active_time
desc
;
SQL_ID
CHILD_NUMBER PLAN_HASH_VALUE LAST_ACTIVE_TIME
--------------------------------------- ------------
--------------- -------------------
828m0ghwzfryd
1 1470838782 2025-11-08:21:24:33
828m0ghwzfryd
0 738787558
2025-11-08:21:23:50
2 rows selected.
Elapsed: 00:00:00.01
SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR('828m0ghwzfryd', 1, 'ALLSTATS LAST -ROWS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 828m0ghwzfryd, child number 1
-------------------------------------
SELECT /* SPA_TEST_ORA12RF2 SELECT_FROM_TB_CUST_WHERE_BRTHDY
*/
* FROM TB_CUST A WHERE A.BRTHDY LIKE
:V_BRTHDY || '%'
Plan hash value: 1470838782
-------------------------------------------------------------------------------------------------------
| Id |
Operation
| Name | Starts |
A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
|
| 1 | 31 |00:00:00.01
| 37 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED|
TB_CUST
| 1 | 31 |00:00:00.01
| 37 |
|* 2
| INDEX RANGE
SCAN
| IDX_TB_CUST_02 | 1 | 31
|00:00:00.01 | 6 |
-------------------------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
2 -
access("A"."BRTHDY" LIKE :V_BRTHDY||'%')
filter("A"."BRTHDY"
LIKE :V_BRTHDY||'%')
21 rows selected.
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 10000;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = TRUE;
VARIABLE V_BRTHDY VARCHAR2(32);
EXEC
:V_BRTHDY := '19910528';
SELECT /*
SPA_TEST_ORA12RF2 SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
SELECT sql_id,
child_number, plan_hash_value, last_active_time
FROM v$sql
WHERE sql_text LIKE '%SPA_TEST_ORA12RF2
SELECT_FROM_TB_CUST_WHERE_BRTHDY%'
and
sql_text not LIKE '%v$sql%'
order by last_active_time
desc
;
SQL_ID
CHILD_NUMBER PLAN_HASH_VALUE LAST_ACTIVE_TIME
--------------------------------------- ------------
--------------- -------------------
828m0ghwzfryd
2 482704130 2025-11-08:21:25:06
828m0ghwzfryd
1 1470838782 2025-11-08:21:24:33
828m0ghwzfryd
0 738787558
2025-11-08:21:23:50
3 rows selected.
Elapsed: 00:00:00.01
SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR('828m0ghwzfryd', 2, 'ALLSTATS LAST -ROWS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 828m0ghwzfryd, child number 2
-------------------------------------
SELECT /* SPA_TEST_ORA12RF2 SELECT_FROM_TB_CUST_WHERE_BRTHDY
*/
* FROM TB_CUST A WHERE A.BRTHDY LIKE
:V_BRTHDY || '%'
Plan hash value: 482704130
---------------------------------------------------------------------------------------
| Id |
Operation |
Name | Starts | A-Rows | A-Time |
Buffers | Reads |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| 1 | 31 |00:00:01.45
| 18220 | 17995 |
|* 1 | TABLE
ACCESS FULL| TB_CUST | 1 |
31 |00:00:01.45 | 18220 | 17995 |
---------------------------------------------------------------------------------------
Predicate
Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."BRTHDY" LIKE :V_BRTHDY||'%')
19 rows selected.
Elapsed: 00:00:00.03
6. AWR 스냅샷 생성
--1번 노드
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 19:17:35 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$
DECLARE
V_LOW_SNAP_ID NUMBER;
V_HIGH_SNAP_ID NUMBER;
BEGIN
SELECT MIN(SNAP_ID),
MAX(SNAP_ID)
INTO V_LOW_SNAP_ID, V_HIGH_SNAP_ID
FROM DBA_HIST_SNAPSHOT;
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID => V_LOW_SNAP_ID,
HIGH_SNAP_ID => V_HIGH_SNAP_ID
);
END;
/
--1번 노드 (tuner oracle
user)
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 100;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = FALSE;
VARIABLE V_BRTHDY VARCHAR2(32);
EXEC
:V_BRTHDY := '19910528';
SELECT /*
SPA_TEST_ORA12RF1_AWR SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 100;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = TRUE;
VARIABLE
V_BRTHDY VARCHAR2(32);
EXEC :V_BRTHDY :=
'19910528';
SELECT /*
SPA_TEST_ORA12RF1_AWR SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 10000;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = TRUE;
VARIABLE
V_BRTHDY VARCHAR2(32);
EXEC :V_BRTHDY :=
'19910528';
SELECT /*
SPA_TEST_ORA12RF1_AWR SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
--2번 노드 (tuner oracle
user)
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 100;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = FALSE;
VARIABLE V_BRTHDY VARCHAR2(32);
EXEC
:V_BRTHDY := '19910528';
SELECT /*
SPA_TEST_ORA12RF2_AWR SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 100;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = TRUE;
VARIABLE
V_BRTHDY VARCHAR2(32);
EXEC :V_BRTHDY :=
'19910528';
SELECT /*
SPA_TEST_ORA12RF2_AWR SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 10000;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = TRUE;
VARIABLE
V_BRTHDY VARCHAR2(32);
EXEC :V_BRTHDY :=
'19910528';
SELECT /*
SPA_TEST_ORA12RF2_AWR SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
--AWR 스냅샷 생성 시 테스트 SQL문이 등록되도록 강제할 것임
COL SQL_ FOR A100
SELECT 'EXEC DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('''||
SQL_ID||''');' AS SQL_
FROM
(
SELECT SQL_ID
FROM
GV$SQL
WHERE SQL_FULLTEXT LIKE '%SELECT_FROM_TB_CUST_WHERE_BRTHDY%'
AND SQL_FULLTEXT NOT LIKE '%EXPLAIN PLAN SET%'
AND UPPER(SQL_FULLTEXT) NOT LIKE '%' || UPPER('GV$SQL') || '%'
GROUP BY SQL_ID
)
;
SQL_
----------------------------------------------------------------------------------------------------
EXEC
DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('93uyt37jadcu3');
EXEC
DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('828m0ghwzfryd');
EXEC
DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('9zr30g89mhym9');
EXEC
DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('3ks0ccp8rmh7b');
EXEC
DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('4yss4a0kvmyfj');
EXEC
DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('cmg9g4u3hp7qy');
EXEC
DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('3gswmyntc4vb3');
EXEC
DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('6uw8tuht71zxk');
EXEC
DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('gza20dm7tmxr4');
--> 위의 SQL 실행
8 rows selected.
Elapsed: 00:00:00.03
SET LINESIZE 200
SET PAGESIZE 100
SET COLSEP ' | '
COLUMN
DBID FORMAT
9999999999 HEADING 'DBID'
COLUMN SQL_ID FORMAT
A13
HEADING 'SQL_ID'
COLUMN CREATE_TIME FORMAT
A20
HEADING 'CREATE_TIME'
COLUMN
CON_ID FORMAT
999
HEADING 'CON_ID'
SELECT
DBID
, SQL_ID
, TO_CHAR(CREATE_TIME, 'YYYY-MM-DD
HH24:MI:SS') AS CREATE_TIME
,
CON_ID
FROM DBA_HIST_COLORED_SQL
ORDER BY
CREATE_TIME
DESC;
DBID |
SQL_ID |
CREATE_TIME | CON_ID
----------- | ------------- | -------------------- |
------
4288610047 | 93uyt37jadcu3 | 2025-11-08
21:28:21 | 0
4288610047 | 828m0ghwzfryd | 2025-11-08 21:28:21
| 0
4288610047 |
9zr30g89mhym9 | 2025-11-08 21:28:21 | 0
4288610047 | 3ks0ccp8rmh7b | 2025-11-08 21:28:21
| 0
4288610047 |
gza20dm7tmxr4 | 2025-11-08 21:28:21 | 0
4288610047 | cmg9g4u3hp7qy | 2025-11-08 21:28:21
| 0
4288610047 |
3gswmyntc4vb3 | 2025-11-08 21:28:21 | 0
4288610047 | 6uw8tuht71zxk | 2025-11-08 21:28:21
| 0
4288610047 |
4yss4a0kvmyfj | 2025-11-08 21:28:21 |
0
9 rows selected.
Elapsed: 00:00:00.04
--2번 실행
EXEC
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
EXEC
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
SET LINESIZE 200
SET PAGESIZE 100
SET COLSEP ' |
'
COLUMN
DBID
FORMAT 9999999999 HEADING 'DBID'
COLUMN
SNAP_ID FORMAT
999999 HEADING
'SNAP_ID'
COLUMN INSTANCE_NUMBER FORMAT
99
HEADING 'INST#'
COLUMN BEGIN_INTERVAL_TIME FORMAT
A25 HEADING
'BEGIN_INTERVAL_TIME'
SELECT
DBID
, SNAP_ID
, INSTANCE_NUMBER
, TO_CHAR(BEGIN_INTERVAL_TIME,
'YYYY-MM-DD HH24:MI:SS') AS BEGIN_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
ORDER BY
SNAP_ID,
INSTANCE_NUMBER
;
DBID | SNAP_ID | INST#
| BEGIN_INTERVAL_TIME
----------- | ------- | ----- |
-------------------------
4288610047
| 978 | 1 | 2025-11-08
21:20:02
4288610047 | 978
| 2 | 2025-11-08 21:20:02
4288610047 | 979
| 1 | 2025-11-08 21:28:42
4288610047 | 979
| 2 | 2025-11-08 21:28:42
4 rows selected.
Elapsed: 00:00:00.01
SELECT DBID
, SNAP_ID
, SQL_ID
FROM DBA_HIST_SQLSTAT
WHERE SQL_ID IN (SELECT SQL_ID
FROM
GV$SQL
WHERE SQL_FULLTEXT LIKE '%SELECT_FROM_TB_CUST_WHERE_BRTHDY%'
AND SQL_FULLTEXT NOT LIKE '%EXPLAIN PLAN SET%'
AND UPPER(SQL_FULLTEXT) NOT LIKE '%' || UPPER('GV$SQL') || '%'
GROUP BY
SQL_ID);
DBID | SNAP_ID |
SQL_ID
----------- | ------- | -------------
4288610047 | 978 |
6uw8tuht71zxk
4288610047 |
978 | 93uyt37jadcu3
4288610047
| 978 | 9zr30g89mhym9
4288610047 | 978 |
cmg9g4u3hp7qy
4288610047 |
978 | cmg9g4u3hp7qy
4288610047
| 978 | 93uyt37jadcu3
4288610047 | 978 |
9zr30g89mhym9
4288610047 |
978 | cmg9g4u3hp7qy
4288610047
| 978 | 6uw8tuht71zxk
4288610047 | 978 |
828m0ghwzfryd
4288610047 |
978 | 828m0ghwzfryd
4288610047
| 978 | 93uyt37jadcu3
4288610047 | 978 |
3gswmyntc4vb3
4288610047 |
978 | 828m0ghwzfryd
4288610047
| 978 | 4yss4a0kvmyfj
4288610047 | 978 |
9zr30g89mhym9
--AWR 스냅샷에 테스트 SQL문이 등록되었으니 제거함
COL SQL_ FOR
A100
SELECT 'EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('''|| SQL_ID||''');' AS SQL_
FROM
(
SELECT
SQL_ID
FROM
GV$SQL
WHERE SQL_FULLTEXT LIKE '%SELECT_FROM_TB_CUST_WHERE_BRTHDY%'
AND SQL_FULLTEXT NOT LIKE '%EXPLAIN PLAN SET%'
AND UPPER(SQL_FULLTEXT) NOT LIKE '%' || UPPER('GV$SQL') || '%'
GROUP BY SQL_ID
)
;
SQL_
----------------------------------------------------------------------------------------------------
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('93uyt37jadcu3');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('828m0ghwzfryd');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('9zr30g89mhym9');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('3ks0ccp8rmh7b');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('4yss4a0kvmyfj');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('cmg9g4u3hp7qy');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('3gswmyntc4vb3');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('6uw8tuht71zxk');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('gza20dm7tmxr4');
9 rows selected.
Elapsed:
00:00:00.04
7. SQLSET 생성
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
sp
alias sp='rlwrap
sqlplus spa/oracle'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
sp
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 21:42:28 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sat Nov 08 2025 21:41:59 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
[ol7ora12rf1]<SPA@ORA12RF1>$
--EXEC DBMS_SQLTUNE.DROP_SQLSET('SS_ORA12RF1_SP1');
--EXEC DBMS_SQLTUNE.DROP_SQLSET('SS_ORA12RF2_SP1');
--EXEC
DBMS_SQLTUNE.DROP_SQLSET('SS_ORA12RF_AWR1');
EXEC
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => 'SS_ORA12RF1_SP1', DESCRIPTION
=> 'SS_ORA12RF1_SP1', SQLSET_OWNER => 'SPA'); --1번노드
SHARED POOL 전용
EXEC
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => 'SS_ORA12RF2_SP1', DESCRIPTION
=> 'SS_ORA12RF2_SP1', SQLSET_OWNER => 'SPA'); --2번노드 SHARED POOL 전용
EXEC
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => 'SS_ORA12RF_AWR1', DESCRIPTION
=> 'SS_ORA12RF_AWR1', SQLSET_OWNER => 'SPA'); --모든노드 AWR 전용
SET LINESIZE 200
SET PAGESIZE 100
SET NUMWIDTH 12
SET COLSEP ' | '
COLUMN
ID
FORMAT
9999
HEADING 'ID'
COLUMN
CON_DBID FORMAT
9999999999 HEADING 'CON_DBID'
COLUMN
NAME
FORMAT
A20
HEADING 'NAME'
COLUMN
OWNER FORMAT
A10
HEADING 'OWNER'
COLUMN
DESCRIPTION FORMAT
A25
HEADING 'DESCRIPTION'
COLUMN
CREATED FORMAT
A20
HEADING 'CREATED'
COLUMN LAST_MODIFIED
FORMAT
A20
HEADING 'LAST_MODIFIED'
COLUMN STATEMENT_COUNT
FORMAT 999999
HEADING 'STMT_CNT'
SELECT
ID
, CON_DBID
, NAME
, OWNER
, DESCRIPTION
,
TO_CHAR(CREATED, 'YYYY/MM/DD HH24:MI:SS') AS
CREATED
, TO_CHAR(LAST_MODIFIED,
'YYYY/MM/DD HH24:MI:SS') AS LAST_MODIFIED
, STATEMENT_COUNT
FROM
DBA_SQLSET
ORDER BY
ID;
ID
| CON_DBID |
NAME
| OWNER |
DESCRIPTION
|
CREATED
| LAST_MODIFIED | STMT_CNT
----- | ----------- | -------------------- | ---------- |
------------------------- | -------------------- | -------------------- |
--------
13 | 4288610047 |
SS_ORA12RF1_SP1 |
SPA |
SS_ORA12RF1_SP1 |
2025/11/08 21:40:18 | 2025/11/08 21:40:18
| 0
14 | 4288610047 |
SS_ORA12RF2_SP1 |
SPA |
SS_ORA12RF2_SP1 |
2025/11/08 21:40:18 | 2025/11/08 21:40:18
| 0
15 | 4288610047 |
SS_ORA12RF_AWR1 |
SPA |
SS_ORA12RF_AWR1 |
2025/11/08 21:40:19 | 2025/11/08 21:40:19
| 0
3 rows selected.
Elapsed:
00:00:00.00
8. 소스 DB의 성능 데이터 캡처
--1번 노드의 V$ 캡처
--1번 노드 (tuner oracle user) (테스트 시점에 따라 SHARED POOL에서 밀려났을
수도 있으므로 한번더 TEST SQL을 실행 시켜줌)
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
st
alias st='rlwrap sqlplus tuner/oracle'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
st
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 21:41:16 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sat Nov 08 2025 21:35:26 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
[ol7ora12rf1]<TUNER@ORA12RF1>$
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 100;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = FALSE;
VARIABLE V_BRTHDY VARCHAR2(32);
EXEC
:V_BRTHDY := '19910528';
SELECT /*
SPA_TEST_ORA12RF1 SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 100;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = TRUE;
VARIABLE
V_BRTHDY VARCHAR2(32);
EXEC :V_BRTHDY :=
'19910528';
SELECT /*
SPA_TEST_ORA12RF1 SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 10000;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = TRUE;
VARIABLE
V_BRTHDY VARCHAR2(32);
EXEC :V_BRTHDY :=
'19910528';
SELECT /*
SPA_TEST_ORA12RF1 SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
--1번 노드에서 (spa oracle
user)
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
sp
alias sp='rlwrap
sqlplus spa/oracle'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
sp
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 21:42:28 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sat Nov 08 2025 21:41:59 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
[ol7ora12rf1]<SPA@ORA12RF1>$
DECLARE
STS_CURSOR DBMS_SQLTUNE.SQLSET_CURSOR;
V_SQLSET_NAME VARCHAR(30);
BEGIN
V_SQLSET_NAME := 'SS_ORA12RF1_SP1';
OPEN STS_CURSOR
FOR
SELECT
VALUE(P)
FROM
TABLE(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'PARSING_SCHEMA_NAME IN (''TUNER'')',
NULL,
NULL,
NULL,
NULL,
1,
NULL,
'ALL'
)
)
P;
DBMS_OUTPUT.PUT_LINE('P_SQLSET_NAME =
' || V_SQLSET_NAME);
DBMS_SQLTUNE.LOAD_SQLSET(
SQLSET_NAME => V_SQLSET_NAME,
POPULATE_CURSOR => STS_CURSOR,
SQLSET_OWNER => 'SPA',
LOAD_OPTION => 'MERGE',
UPDATE_OPTION => 'REPLACE',
UPDATE_ATTRIBUTES => 'ALL',
IGNORE_NULL => TRUE,
COMMIT_ROWS => 100
);
CLOSE
STS_CURSOR;
DBMS_OUTPUT.PUT_LINE('WORKLOAD HAS BEEN
SUCCESSFULLY LOADED INTO SQL TUNING SET: ' || V_SQLSET_NAME);
END;
/
--> 수집된
STS확인
SET LINESIZE 180
SET PAGESIZE 100
SET COLSEP ' |
'
COLUMN
OWNER FORMAT
A10 HEADING
'OWNER'
COLUMN
NAME FORMAT
A20 HEADING 'SQLSET
NAME'
COLUMN
CREATED FORMAT
A20 HEADING
'CREATED'
COLUMN LAST_MODIFIED FORMAT
A20 HEADING 'LAST
MODIFIED'
COLUMN STATEMENT_COUNT FORMAT
9999999 HEADING 'STMT
COUNT'
SELECT A.OWNER
, A.NAME
, TO_CHAR(A.CREATED, 'YYYY-MM-DD
HH24:MI:SS') AS CREATED
,
TO_CHAR(A.LAST_MODIFIED, 'YYYY-MM-DD HH24:MI:SS') AS LAST_MODIFIED
, A.STATEMENT_COUNT
FROM DBA_SQLSET A
WHERE
1=1
AND A.NAME = 'SS_ORA12RF1_SP1'
AND A.OWNER = 'SPA'
ORDER BY 1,2
;
OWNER | SQLSET
NAME |
CREATED
| LAST MODIFIED | STMT COUNT
---------- | -------------------- | -------------------- |
-------------------- | ----------
SPA |
SS_ORA12RF1_SP1 | 2025-11-08 21:40:18 |
2025-11-08 21:42:00 |
47
1 row selected.
Elapsed: 00:00:00.01
SET LINESIZE 180
SET PAGESIZE 100
SET COLSEP ' | '
COLUMN
SQLSET_NAME FORMAT
A20 HEADING 'SQLSET
NAME'
COLUMN
EXEC_DATE FORMAT
A15 HEADING 'EXEC DATE'
COLUMN
TOTAL_SQL FORMAT
999,999 HEADING 'TOTAL SQL'
COLUMN
UNIQ_SQL FORMAT
999,999 HEADING 'UNIQ SQL'
COLUMN UNIQ_SQL_PLAN FORMAT
999,999 HEADING 'UNIQ
SQL+PLAN'
SELECT
A.SQLSET_NAME, TO_CHAR(SYSDATE, 'MM-DD HH24:MI:SS') EXEC_DATE
--
,SYS_CONTEXT('USERENV','INSTANCE_NAME') AS INST_NAME
, COUNT(*) TOTAL_SQL
, COUNT(DISTINCT A.SQL_ID) AS
UNIQ_SQL
, COUNT(DISTINCT
A.SQL_ID||A.PLAN_HASH_VALUE) AS UNIQ_SQL_PLAN
FROM DBA_SQLSET_STATEMENTS A
WHERE 1=1
AND
A.SQLSET_OWNER = 'SPA'
AND A.SQLSET_NAME =
'SS_ORA12RF1_SP1'
GROUP BY SQLSET_NAME
;
SQLSET
NAME | EXEC
DATE | TOTAL SQL | UNIQ SQL | UNIQ
SQL+PLAN
-------------------- | --------------- |
--------- | -------- | -------------
SS_ORA12RF1_SP1 | 11-08
21:43:09 | 53
| 47
|
53
1 row selected.
Elapsed: 00:00:00.02
--2번 노드의 V$ 캡처
--2번 노드 (tuner oracle user) (테스트 시점에 따라 SHARED POOL에서 밀려났을
수도 있으므로 한번더 TEST SQL을 실행 시켜줌)
[ORA12RF2:oracle@ol7ora12rf2][/home/oracle]$ alias
st
alias st='rlwrap
sqlplus tuner/oracle'
[ORA12RF2:oracle@ol7ora12rf2][/home/oracle]$
st
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 21:44:06 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sat Nov 08 2025 21:41:16 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 100;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = FALSE;
VARIABLE V_BRTHDY VARCHAR2(32);
EXEC
:V_BRTHDY := '19910528';
SELECT /*
SPA_TEST_ORA12RF2 SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 100;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = TRUE;
VARIABLE
V_BRTHDY VARCHAR2(32);
EXEC :V_BRTHDY :=
'19910528';
SELECT /*
SPA_TEST_ORA12RF2 SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
ALTER SESSION SET
STATISTICS_LEVEL = ALL;
ALTER SESSION SET
optimizer_index_cost_adj = 10000;
ALTER SESSION SET
"_optimizer_batch_table_access_by_rowid" = TRUE;
VARIABLE
V_BRTHDY VARCHAR2(32);
EXEC :V_BRTHDY :=
'19910528';
SELECT /*
SPA_TEST_ORA12RF2 SELECT_FROM_TB_CUST_WHERE_BRTHDY */
*
FROM TB_CUST A
WHERE
A.BRTHDY LIKE :V_BRTHDY || '%'
;
[ORA12RF2:oracle@ol7ora12rf2][/home/oracle]$ alias
sp
alias sp='rlwrap
sqlplus spa/oracle'
[ORA12RF2:oracle@ol7ora12rf2][/home/oracle]$
sp
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 21:44:38 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sat Nov 08 2025 21:42:28 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
[ol7ora12rf2]<SPA@ORA12RF2>$
DECLARE
STS_CURSOR DBMS_SQLTUNE.SQLSET_CURSOR;
V_SQLSET_NAME VARCHAR(30);
BEGIN
V_SQLSET_NAME := 'SS_ORA12RF2_SP1';
OPEN STS_CURSOR
FOR
SELECT
VALUE(P)
FROM
TABLE(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'PARSING_SCHEMA_NAME IN (''TUNER'')',
NULL,
NULL,
NULL,
NULL,
1,
NULL,
'ALL'
)
)
P;
DBMS_OUTPUT.PUT_LINE('P_SQLSET_NAME =
' || V_SQLSET_NAME);
DBMS_SQLTUNE.LOAD_SQLSET(
SQLSET_NAME => V_SQLSET_NAME,
POPULATE_CURSOR => STS_CURSOR,
SQLSET_OWNER => 'SPA',
LOAD_OPTION => 'MERGE',
UPDATE_OPTION => 'REPLACE',
UPDATE_ATTRIBUTES => 'ALL',
IGNORE_NULL => TRUE,
COMMIT_ROWS => 100
);
CLOSE STS_CURSOR;
DBMS_OUTPUT.PUT_LINE('WORKLOAD HAS BEEN
SUCCESSFULLY LOADED INTO SQL TUNING SET: ' || V_SQLSET_NAME);
END;
/
--> 수집된
STS확인
SET LINESIZE 180
SET PAGESIZE 100
SET COLSEP ' |
'
COLUMN
OWNER FORMAT
A10 HEADING
'OWNER'
COLUMN
NAME FORMAT
A20 HEADING 'SQLSET
NAME'
COLUMN
CREATED FORMAT
A20 HEADING
'CREATED'
COLUMN LAST_MODIFIED FORMAT
A20 HEADING 'LAST
MODIFIED'
COLUMN STATEMENT_COUNT FORMAT
9999999 HEADING 'STMT
COUNT'
SELECT A.OWNER
, A.NAME
, TO_CHAR(A.CREATED, 'YYYY-MM-DD
HH24:MI:SS') AS CREATED
,
TO_CHAR(A.LAST_MODIFIED, 'YYYY-MM-DD HH24:MI:SS') AS LAST_MODIFIED
, A.STATEMENT_COUNT
FROM DBA_SQLSET A
WHERE
1=1
AND A.NAME = 'SS_ORA12RF2_SP1'
AND A.OWNER = 'SPA'
ORDER BY 1,2
;
OWNER | SQLSET
NAME |
CREATED
| LAST MODIFIED | STMT COUNT
---------- | -------------------- | -------------------- |
-------------------- | ----------
SPA |
SS_ORA12RF2_SP1 | 2025-11-08 21:40:18 |
2025-11-08 21:44:58 |
27
1 row selected.
Elapsed:
00:00:00.01
SET LINESIZE 180
SET PAGESIZE 100
SET COLSEP ' |
'
COLUMN
SQLSET_NAME FORMAT
A20 HEADING 'SQLSET
NAME'
COLUMN
EXEC_DATE FORMAT
A15 HEADING 'EXEC DATE'
COLUMN
TOTAL_SQL FORMAT
999,999 HEADING 'TOTAL SQL'
COLUMN
UNIQ_SQL FORMAT
999,999 HEADING 'UNIQ SQL'
COLUMN UNIQ_SQL_PLAN FORMAT
999,999 HEADING 'UNIQ
SQL+PLAN'
SELECT
A.SQLSET_NAME, TO_CHAR(SYSDATE, 'MM-DD HH24:MI:SS') EXEC_DATE
--
,SYS_CONTEXT('USERENV','INSTANCE_NAME') AS INST_NAME
, COUNT(*) TOTAL_SQL
, COUNT(DISTINCT A.SQL_ID) AS
UNIQ_SQL
, COUNT(DISTINCT
A.SQL_ID||A.PLAN_HASH_VALUE) AS UNIQ_SQL_PLAN
FROM DBA_SQLSET_STATEMENTS A
WHERE 1=1
AND
A.SQLSET_OWNER = 'SPA'
AND A.SQLSET_NAME =
'SS_ORA12RF2_SP1'
GROUP BY SQLSET_NAME
;
SQLSET
NAME | EXEC
DATE | TOTAL SQL | UNIQ SQL | UNIQ
SQL+PLAN
-------------------- | --------------- |
--------- | -------- | -------------
SS_ORA12RF2_SP1 | 11-08
21:45:18 | 29
| 27
|
29
1 row selected.
Elapsed: 00:00:00.02
--AWR
캡처
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
sp
alias sp='rlwrap
sqlplus spa/oracle'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
sp
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 21:45:36 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sat Nov 08 2025 21:44:38 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
[ol7ora12rf1]<SPA@ORA12RF1>$
SET LINESIZE 200
SET PAGESIZE 100
SET COLSEP ' |
'
COLUMN
DBID
FORMAT 9999999999 HEADING 'DBID'
COLUMN
SNAP_ID FORMAT
999999 HEADING
'SNAP_ID'
COLUMN INSTANCE_NUMBER FORMAT
99
HEADING 'INST#'
COLUMN BEGIN_INTERVAL_TIME FORMAT
A25 HEADING
'BEGIN_INTERVAL_TIME'
SELECT
DBID
, SNAP_ID
, INSTANCE_NUMBER
, TO_CHAR(BEGIN_INTERVAL_TIME,
'YYYY-MM-DD HH24:MI:SS') AS BEGIN_INTERVAL_TIME
FROM DBA_HIST_SNAPSHOT
ORDER BY
SNAP_ID,
INSTANCE_NUMBER
;
DBID | SNAP_ID | INST#
| BEGIN_INTERVAL_TIME
----------- | ------- | ----- |
-------------------------
4288610047
| 978 | 1 | 2025-11-08
21:20:02
4288610047 | 978
| 2 | 2025-11-08 21:20:02
4288610047 | 979
| 1 | 2025-11-08 21:28:42
4288610047 | 979
| 2 | 2025-11-08 21:28:42
4288610047 | 980
| 1 | 2025-11-08 21:28:43
4288610047 | 980
| 2 | 2025-11-08 21:28:43
6 rows selected.
Elapsed: 00:00:00.00
DECLARE
STS_CURSOR DBMS_SQLTUNE.SQLSET_CURSOR;
V_SQLSET_NAME VARCHAR(30);
V_BEGIN_SNAP NUMBER;
V_END_SNAP NUMBER;
BEGIN
V_SQLSET_NAME :=
'SS_ORA12RF_AWR1';
V_BEGIN_SNAP := 978;
V_END_SNAP := 980;
OPEN STS_CURSOR
FOR
SELECT
VALUE(P)
FROM
TABLE(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(V_BEGIN_SNAP, V_END_SNAP,
'PARSING_SCHEMA_NAME IN (''TUNER'')')
)
P;
DBMS_OUTPUT.PUT_LINE('P_SQLSET_NAME = ' ||
V_SQLSET_NAME);
DBMS_SQLTUNE.LOAD_SQLSET(
SQLSET_NAME => V_SQLSET_NAME,
POPULATE_CURSOR => STS_CURSOR,
SQLSET_OWNER => 'SPA',
LOAD_OPTION => 'MERGE',
UPDATE_OPTION => 'REPLACE',
UPDATE_ATTRIBUTES => 'ALL',
IGNORE_NULL => TRUE,
COMMIT_ROWS => 100
);
CLOSE STS_CURSOR;
DBMS_OUTPUT.PUT_LINE('WORKLOAD HAS BEEN
SUCCESSFULLY LOADED INTO SQL TUNING SET: ' || V_SQLSET_NAME);
END;
/
--> 수집된
STS확인
SET LINESIZE 180
SET PAGESIZE 100
SET COLSEP ' |
'
COLUMN
OWNER FORMAT
A10 HEADING
'OWNER'
COLUMN
NAME FORMAT
A20 HEADING 'SQLSET
NAME'
COLUMN
CREATED FORMAT
A20 HEADING
'CREATED'
COLUMN LAST_MODIFIED FORMAT
A20 HEADING 'LAST
MODIFIED'
COLUMN STATEMENT_COUNT FORMAT
9999999 HEADING 'STMT COUNT'
;
SELECT A.OWNER
, A.NAME
, TO_CHAR(A.CREATED, 'YYYY-MM-DD
HH24:MI:SS') AS CREATED
,
TO_CHAR(A.LAST_MODIFIED, 'YYYY-MM-DD HH24:MI:SS') AS LAST_MODIFIED
, A.STATEMENT_COUNT
FROM DBA_SQLSET A
WHERE
1=1
AND A.NAME = 'SS_ORA12RF_AWR1'
AND A.OWNER = 'SPA'
ORDER BY 1,2
;
OWNER | SQLSET
NAME |
CREATED
| LAST MODIFIED | STMT COUNT
---------- | -------------------- | -------------------- |
-------------------- | ----------
SPA |
SS_ORA12RF_AWR1 | 2025-11-08 21:40:19 |
2025-11-08 21:46:10
| 9
1 row selected.
Elapsed: 00:00:00.00
SET LINESIZE 180
SET PAGESIZE 100
SET COLSEP ' |
'
COLUMN
SQLSET_NAME FORMAT
A20 HEADING 'SQLSET
NAME'
COLUMN
EXEC_DATE FORMAT
A15 HEADING 'EXEC DATE'
COLUMN
TOTAL_SQL FORMAT
999,999 HEADING 'TOTAL SQL'
COLUMN
UNIQ_SQL FORMAT
999,999 HEADING 'UNIQ SQL'
COLUMN UNIQ_SQL_PLAN FORMAT
999,999 HEADING 'UNIQ SQL+PLAN'
;
SELECT A.SQLSET_NAME,
TO_CHAR(SYSDATE, 'MM-DD HH24:MI:SS') EXEC_DATE
--
,SYS_CONTEXT('USERENV','INSTANCE_NAME') AS INST_NAME
, COUNT(*) TOTAL_SQL
, COUNT(DISTINCT A.SQL_ID) AS
UNIQ_SQL
, COUNT(DISTINCT
A.SQL_ID||A.PLAN_HASH_VALUE) AS UNIQ_SQL_PLAN
FROM DBA_SQLSET_STATEMENTS A
WHERE 1=1
AND
A.SQLSET_OWNER = 'SPA'
AND A.SQLSET_NAME =
'SS_ORA12RF_AWR1'
GROUP BY SQLSET_NAME
;
SQLSET
NAME | EXEC
DATE | TOTAL SQL | UNIQ SQL | UNIQ
SQL+PLAN
-------------------- | --------------- |
--------- | -------- | -------------
SS_ORA12RF_AWR1 | 11-08
21:46:30 | 11
| 9
|
11
1 row selected.
Elapsed: 00:00:00.01
9. 소스 DB에서 캡처한 성능 데이터를 하나로 병합시킴
--SPA ORACLE USER로 접속
SET LINESIZE 200
SET PAGESIZE 100
SET NUMWIDTH 12
SET COLSEP ' | '
COLUMN
ID
FORMAT
9999
HEADING 'ID'
COLUMN
CON_DBID FORMAT
9999999999 HEADING 'CON_DBID'
COLUMN
NAME
FORMAT
A20
HEADING 'NAME'
COLUMN
OWNER FORMAT
A10
HEADING 'OWNER'
COLUMN
DESCRIPTION FORMAT
A25
HEADING 'DESCRIPTION'
COLUMN
CREATED FORMAT
A20
HEADING 'CREATED'
COLUMN LAST_MODIFIED
FORMAT
A20
HEADING 'LAST_MODIFIED'
COLUMN STATEMENT_COUNT
FORMAT 999999
HEADING 'STMT_CNT'
SELECT
ID
, CON_DBID
, NAME
, OWNER
, DESCRIPTION
,
TO_CHAR(CREATED, 'YYYY/MM/DD HH24:MI:SS') AS
CREATED
, TO_CHAR(LAST_MODIFIED,
'YYYY/MM/DD HH24:MI:SS') AS LAST_MODIFIED
, STATEMENT_COUNT
FROM
DBA_SQLSET
ORDER BY
ID;
ID | CON_DBID |
NAME
| OWNER |
DESCRIPTION
|
CREATED
| LAST_MODIFIED | STMT_CNT
----- | ----------- | -------------------- | ---------- |
------------------------- | -------------------- | -------------------- |
--------
13 | 4288610047 |
SS_ORA12RF1_SP1 |
SPA |
SS_ORA12RF1_SP1 |
2025/11/08 21:40:18 | 2025/11/08 21:42:00
| 47
14
| 4288610047 | SS_ORA12RF2_SP1 |
SPA |
SS_ORA12RF2_SP1 |
2025/11/08 21:40:18 | 2025/11/08 21:44:58
| 27
15
| 4288610047 | SS_ORA12RF_AWR1 |
SPA |
SS_ORA12RF_AWR1 |
2025/11/08 21:40:19 | 2025/11/08 21:46:10
| 9
3 rows selected.
Elapsed: 00:00:00.00
[ol7ora12rf1]<SPA@ORA12RF1>$ exec DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME =>
'SS_ORA12RF',DESCRIPTION =>'SS_ORA12RF',SQLSET_OWNER =>
'SPA');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
SELECT * FROM DBA_SQLSET ORDER BY
LAST_MODIFIED;
ID
| CON_DBID |
NAME
| OWNER |
DESCRIPTION
|
CREATED
| LAST_MODIFIED | STMT_CNT
----- | ----------- | -------------------- | ---------- |
------------------------- | -------------------- | -------------------- |
--------
13 | 4288610047 |
SS_ORA12RF1_SP1 |
SPA |
SS_ORA12RF1_SP1 |
2025-11-08:21:40:18 | 2025-11-08:21:42:00
| 47
14
| 4288610047 | SS_ORA12RF2_SP1 |
SPA |
SS_ORA12RF2_SP1 |
2025-11-08:21:40:18 | 2025-11-08:21:44:58
| 27
15
| 4288610047 | SS_ORA12RF_AWR1 |
SPA |
SS_ORA12RF_AWR1 |
2025-11-08:21:40:19 | 2025-11-08:21:46:10
| 9
16 | 4288610047 |
SS_ORA12RF |
SPA |
SS_ORA12RF
| 2025-11-08:21:47:57 | 2025-11-08:21:47:57
| 0
4 rows selected.
Elapsed: 00:00:00.00
--sys user
--rac1과 rac2의 SHAPRED POOL과 AWR을 합치는 작업임
DECLARE
CUR1 SYS_REFCURSOR;
CUR2 SYS_REFCURSOR;
CUR3 SYS_REFCURSOR;
V_SQLSET VARCHAR2(100) := 'SS_ORA12RF';
BEGIN
OPEN CUR1 FOR SELECT
VALUE(P) FROM
TABLE(DBMS_SQLTUNE.SELECT_SQLSET(SQLSET_NAME=>'SS_ORA12RF1_SP1',SQLSET_OWNER
=> 'SPA')) P;
OPEN CUR2 FOR SELECT VALUE(P)
FROM
TABLE(DBMS_SQLTUNE.SELECT_SQLSET(SQLSET_NAME=>'SS_ORA12RF2_SP1',SQLSET_OWNER
=> 'SPA')) P;
OPEN CUR3 FOR SELECT VALUE(P)
FROM
TABLE(DBMS_SQLTUNE.SELECT_SQLSET(SQLSET_NAME=>'SS_ORA12RF_AWR1',SQLSET_OWNER
=> 'SPA')) P;
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME => V_SQLSET, POPULATE_CURSOR => CUR1,
SQLSET_OWNER => 'SPA', LOAD_OPTION => 'MERGE', UPDATE_OPTION =>
'REPLACE', UPDATE_ATTRIBUTES => 'ALL',IGNORE_NULL => TRUE, COMMIT_ROWS
=> 100);
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME
=> V_SQLSET, POPULATE_CURSOR => CUR2, SQLSET_OWNER => 'SPA',
LOAD_OPTION => 'MERGE', UPDATE_OPTION => 'REPLACE', UPDATE_ATTRIBUTES
=> 'ALL',IGNORE_NULL => TRUE, COMMIT_ROWS => 100);
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME => V_SQLSET,
POPULATE_CURSOR => CUR3, SQLSET_OWNER => 'SPA', LOAD_OPTION => 'MERGE',
UPDATE_OPTION => 'REPLACE', UPDATE_ATTRIBUTES => 'ALL',IGNORE_NULL =>
TRUE, COMMIT_ROWS => 100);
CLOSE CUR1;
CLOSE CUR2;
CLOSE
CUR3;
END;
/
--> 합치고 나면
SS_ORA12RF 에 저장됨
SELECT A.OWNER
, A.NAME
, TO_CHAR(A.CREATED, 'YYYY-MM-DD
HH24:MI:SS') AS CREATED
,
TO_CHAR(A.LAST_MODIFIED, 'YYYY-MM-DD HH24:MI:SS') AS LAST_MODIFIED
, A.STATEMENT_COUNT
FROM DBA_SQLSET A
WHERE
1=1
AND A.NAME = 'SS_ORA12RF'
AND A.OWNER = 'SPA'
ORDER BY 1,2
;
OWNER |
NAME
|
CREATED
| LAST_MODIFIED | STMT_CNT
---------- | -------------------- | -------------------- |
-------------------- | --------
SPA |
SS_ORA12RF |
2025-11-08 21:47:57 | 2025-11-08 21:48:27
| 62
1 row selected.
Elapsed: 00:00:00.01
SELECT A.SQLSET_NAME, TO_CHAR(SYSDATE, 'MM-DD HH24:MI:SS')
EXEC_DATE
--
,SYS_CONTEXT('USERENV','INSTANCE_NAME') AS INST_NAME
, COUNT(*) TOTAL_SQL
, COUNT(DISTINCT A.SQL_ID) AS
UNIQ_SQL
, COUNT(DISTINCT
A.SQL_ID||A.PLAN_HASH_VALUE) AS UNIQ_SQL_PLAN
FROM DBA_SQLSET_STATEMENTS A
WHERE 1=1
AND
A.SQLSET_OWNER = 'SPA'
AND A.SQLSET_NAME =
'SS_ORA12RF'
GROUP BY SQLSET_NAME
;
SQLSET
NAME | EXEC
DATE | TOTAL SQL | UNIQ SQL | UNIQ
SQL+PLAN
-------------------- | --------------- |
--------- | -------- | -------------
SS_ORA12RF
| 11-08 21:48:53 | 70
| 62
|
70
1 row selected.
Elapsed: 00:00:00.15
SELECT * FROM DBA_SQLSET_STATEMENTS WHERE SQLSET_NAME =
'SS_ORA12RF';
--테이블을 생성하고
EXEC
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(TABLE_NAME => 'TB_SS_ORA12RF', SCHEMA_NAME
=> 'SPA', TABLESPACE_NAME =>'SPA_DATA1');
--합병시켠 성능 데이터를 테이블에 저장함
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(SQLSET_NAME => 'SS_ORA12RF',SQLSET_OWNER=>'SPA',STAGING_TABLE_NAME=>'TB_SS_ORA12RF',STAGING_SCHEMA_OWNER=>'SPA');
SELECT OWNER,
SEGMENT_NAME, SEGMENT_TYPE, ROUND(SUM(BYTES)/1024/1024,2) SIZE_MB
FROM DBA_SEGMENTS
WHERE
OWNER = 'SPA'
AND SEGMENT_NAME =
'TB_SS_ORA12RF' --> 이 테이블에 합병된 성능 데이터가 저장된 것임
GROUP BY OWNER, SEGMENT_NAME, SEGMENT_TYPE
ORDER BY OWNER, SEGMENT_NAME
;
OWNER |
SEGMENT_NAME
|
SEGMENT_TYPE
| SIZE_MB
---------- |
--------------------------------------- |
------------------------------------------------------ | ------------
SPA |
TB_SS_ORA12RF
|
TABLE
| .38
1 row selected.
Elapsed: 00:00:00.18
10. 소스 DB에서 캡처한 성능 데이터(하나로 합병시킨 성능 데이터)를 export
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ss
SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 8 21:52:05 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$ CREATE OR REPLACE DIRECTORY SPA_DIR AS '/home/oracle/spa';
Directory created.
Elapsed: 00:00:00.03
[ol7ora12rf1]<SYS@ORA12RF1>$ GRANT READ, WRITE ON DIRECTORY SPA_DIR TO SPA;
Grant succeeded.
Elapsed: 00:00:00.02
[ol7ora12rf1]<SYS@ORA12RF1>$ quit
Disconnected from Oracle Database 12c Enterprise Edition
Release 12.2.0.1.0 - 64bit Production
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ mkdir -p
/home/oracle/spa
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ expdp
system/oracle dumpfile=SPA_DIR:TB_SS_ORA12RF.dmp
logfile=SPA_DIR:TB_SS_ORA12RF.log tables=SPA.TB_SS_ORA12RF
Export: Release 12.2.0.1.0 - Production on Sat Nov 8 21:52:45 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":
system/********/ dumpfile=SPA_DIR:TB_SS_ORA12RF.dmp
logfile=SPA_DIR:TB_SS_ORA12RF.log tables=SPA.TB_SS_ORA12RF
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type
TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type
TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing
object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported
"SPA"."TB_SS_ORA12RF"
328.1 KB 200 rows
Master table
"SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/spa/TB_SS_ORA12RF.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at
Sat Nov 8 21:53:09 2025 elapsed 0 00:00:20
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ ls -l
/home/oracle/spa
total 700
-rw-r-----. 1 oracle
asmadmin 712704 Nov 8 21:53 TB_SS_ORA12RF.dmp
-rw-r--r--. 1 oracle asmadmin 1128 Nov 8
21:53 TB_SS_ORA12RF.log
11. 타켓 DB에서 캡처한 spa user 및 테이블 스페이스 생성
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Nov 8 21:54:38 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rf1]<SYS@ORA19RF1>$
DROP USER SPA
CASCADE;
CREATE USER SPA IDENTIFIED BY "oracle";
GRANT RESOURCE, DBA, CONNECT TO SPA;
ALTER USER SPA ACCOUNT UNLOCK;
DROP TABLESPACE
SPA_DATA1 INCLUDING CONTENTS AND DATAFILES;
CREATE
TABLESPACE SPA_DATA1
DATAFILE '+DATA1' SIZE 1G
AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
;
DROP TABLESPACE
SPA_IDX1 INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE
SPA_IDX1
DATAFILE '+DATA1' SIZE 1G
AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED
;
DROP TABLESPACE
SPA_TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE
TEMPORARY TABLESPACE SPA_TEMP
TEMPFILE '+DATA1' SIZE
128M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
;
ALTER USER SPA
DEFAULT TABLESPACE SPA_DATA1;
ALTER USER SPA TEMPORARY
TABLESPACE SPA_TEMP;
12. 소스 DB에서 캡처한 성능 데이터를 타켓 DB에 import
--소스 DB에
있는 /home/oracle/spa/TB_SS_ORA12RF.dmp 파일을 타켓 DB 서버로 가져옴
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ mkdir -p
/home/oracle/spa
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ scp
192.168.240.11:/home/oracle/spa/TB_SS_ORA12RF.dmp
/home/oracle/spa
oracle@192.168.240.11''s
password:
TB_SS_ORA12RF.dmp 100% 696KB
48.5MB/s 00:00
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ ls -l
/home/oracle/spa
total 696
-rw-r-----. 1 oracle
oinstall 712704 Nov 8 21:56 TB_SS_ORA12RF.dmp
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Nov 8 21:58:26 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rf1]<SYS@ORA19RF1>$ CREATE OR REPLACE DIRECTORY SPA_DIR AS '/home/oracle/spa';
Directory created.
Elapsed: 00:00:00.02
[ol8ora19rf1]<SYS@ORA19RF1>$ GRANT READ,WRITE ON DIRECTORY SPA_DIR TO
SPA;
Grant succeeded.
Elapsed: 00:00:00.02
[ol8ora19rf1]<SYS@ORA19RF1>$ quit
Disconnected from Oracle
Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ impdp
spa/oracle directory=SPA_DIR dumpfile=TB_SS_ORA12RF.dmp log=TB_SS_ORA12RF.log
tables=spa.TB_SS_ORA12RF
Import: Release
19.0.0.0.0 - Production on Sat Nov 8 21:58:48 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=TB_SS_ORA12RF.log" Location:
Command Line, Replaced with: "logfile=TB_SS_ORA12RF.log"
Master table "SPA"."SYS_IMPORT_TABLE_01" successfully
loaded/unloaded
Starting
"SPA"."SYS_IMPORT_TABLE_01": spa/********/ directory=SPA_DIR
dumpfile=TB_SS_ORA12RF.dmp logfile=TB_SS_ORA12RF.log tables=spa.TB_SS_ORA12RF
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported
"SPA"."TB_SS_ORA12RF"
328.1 KB 200 rows
Processing
object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type
TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing
object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job
"SPA"."SYS_IMPORT_TABLE_01" successfully completed at Sat Nov 8 21:59:09 2025
elapsed 0 00:00:17
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias
sp
alias sp='rlwrap
sqlplus spa/oracle'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ sp
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Nov 8 22:00:27 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Sat Nov 08 2025 22:00:21 +09:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.28.0.0.0
[ol8ora19rf1]<SPA@ORA19RF1>$ SELECT count(*) FROM TB_SS_ORA12RF;
COUNT(*)
----------
200
1 row selected.
Elapsed: 00:00:00.00
SET LINESIZE 250
SET PAGESIZE 200
SET NUMWIDTH 12
SET TRIMSPOOL ON
COLUMN
NAME
FORMAT A25 HEADING 'SQLSET_NAME'
COLUMN
OWNER
FORMAT A10 HEADING 'OWNER'
COLUMN
DESCRIPTION FORMAT
A25 HEADING 'DESCRIPTION'
COLUMN PARSING_SCHEMA_NAME FORMAT
A15 HEADING 'PARSING_SCHEMA'
COLUMN
SQL_ID
FORMAT A13 HEADING 'SQL_ID'
COLUMN PLAN_HASH_VALUE FORMAT
9999999999 HEADING 'PLAN_HASH'
COLUMN
EXECUTIONS FORMAT
999,999,999 HEADING 'EXECS'
COLUMN
ELAPSED_TIME FORMAT 999,999,999,999
HEADING 'ELAPSED_TIME(us)'
COLUMN
CPU_TIME
FORMAT 999,999,999,999 HEADING 'CPU_TIME(us)'
COLUMN
BUFFER_GETS FORMAT
999,999,999,999 HEADING 'BUFFER_GETS'
COLUMN
DISK_READS FORMAT
999,999,999,999 HEADING 'DISK_READS'
SELECT
NAME
, OWNER
, DESCRIPTION
, PARSING_SCHEMA_NAME
, SQL_ID
, PLAN_HASH_VALUE
, EXECUTIONS
, ELAPSED_TIME
, CPU_TIME
, BUFFER_GETS
, DISK_READS
FROM TB_SS_ORA12RF
WHERE
SQL_TEXT LIKE '%SPA_TEST%'
AND
UPPER(SQL_TEXT) NOT LIKE '%' || UPPER('V$SQL') || '%'
;
SQLSET_NAME
OWNER
DESCRIPTION
PARSING_SCHEMA
SQL_ID
PLAN_HASH EXECS
ELAPSED_TIME(us)
CPU_TIME(us)
BUFFER_GETS DISK_READS
------------------------- ----------
------------------------- --------------- ------------- ----------- ------------
---------------- ---------------- ---------------- ----------------
SS_ORA12RF
SPA
SS_ORA12RF
TUNER
93uyt37jadcu3
482704130
2
140,959
136,478
36,440
0
SS_ORA12RF
SPA
SS_ORA12RF
TUNER
93uyt37jadcu3
738787558
2
1,406
1,267
76
0
SS_ORA12RF
SPA
SS_ORA12RF
TUNER
93uyt37jadcu3
1470838782
2
385
161
74
0
SS_ORA12RF
SPA
SS_ORA12RF
TUNER
9zr30g89mhym9
482704130
1
68,917
66,333
18,220
0
SS_ORA12RF
SPA
SS_ORA12RF
TUNER
9zr30g89mhym9
738787558
2
2,015
1,483
75
0
SS_ORA12RF
SPA
SS_ORA12RF
TUNER
9zr30g89mhym9
1470838782
2
2,204
2,067
74
0
SS_ORA12RF
SPA
SS_ORA12RF
TUNER
cmg9g4u3hp7qy
482704130
1
63,699
61,569
18,220
0
SS_ORA12RF
SPA
SS_ORA12RF
TUNER
cmg9g4u3hp7qy
738787558
1
1,097
1,002
37
0
SS_ORA12RF
SPA
SS_ORA12RF
TUNER
cmg9g4u3hp7qy
1470838782
1
1,175
1,133
37
0
SS_ORA12RF
SPA
SS_ORA12RF
TUNER
828m0ghwzfryd
482704130
2
1,522,308
336,392
36,470 17,995
SS_ORA12RF
SPA
SS_ORA12RF
TUNER
828m0ghwzfryd
738787558
2
69,253
14,224
286
4
SS_ORA12RF
SPA
SS_ORA12RF
TUNER
828m0ghwzfryd
1470838782
2
3,032
2,140
73
0
-->
동일한 SQL_ID인데 PLAN_HASH_VALUE가 여러 개인 경우가 모두 저장되어 있음
12 rows selected.
Elapsed: 00:00:00.00
--EXEC
DBMS_SQLTUNE.DROP_SQLSET('SS_ORA12RF');
EXEC
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => 'SS_ORA12RF',DESCRIPTION
=>'SS_ORA12RF',SQLSET_OWNER => 'SPA');
SET LINESIZE 180
SET PAGESIZE 100
SET COLSEP ' |
'
COLUMN
OWNER FORMAT
A10 HEADING
'OWNER'
COLUMN
NAME FORMAT
A20 HEADING 'SQLSET
NAME'
COLUMN
CREATED FORMAT
A20 HEADING
'CREATED'
COLUMN LAST_MODIFIED FORMAT
A20 HEADING 'LAST
MODIFIED'
COLUMN STATEMENT_COUNT FORMAT
9999999 HEADING 'STMT COUNT'
SELECT A.OWNER
, A.NAME
, TO_CHAR(A.CREATED, 'YYYY-MM-DD
HH24:MI:SS') AS CREATED
,
TO_CHAR(A.LAST_MODIFIED, 'YYYY-MM-DD HH24:MI:SS') AS LAST_MODIFIED
, A.STATEMENT_COUNT
FROM DBA_SQLSET A
WHERE
1=1
AND A.NAME = 'SS_ORA12RF'
AND A.OWNER = 'SPA'
ORDER BY 1,2
;
OWNER | SQLSET
NAME |
CREATED
| LAST MODIFIED | STMT COUNT
---------- | -------------------- | -------------------- |
-------------------- | ----------
SPA |
SS_ORA12RF |
2025-11-08 22:01:23 | 2025-11-08 22:01:23
| 0
--> 타켓 DB에 새로운 SQLSET을 생성한 것임
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
SQLSET_NAME => 'SS_ORA12RF'
,
SQLSET_OWNER => 'SPA'
, STAGING_TABLE_NAME =>
'TB_SS_ORA12RF'
, STAGING_SCHEMA_OWNER
=> 'SPA'
,
REPLACE =>
TRUE
);
END;
/
--> TB_SS_ORA12RF 데이터를 SS_ORA12RF SQLSET으로 UNPACK함
SET LINESIZE 180
SET PAGESIZE 100
SET COLSEP ' |
'
COLUMN
SQLSET_NAME FORMAT
A20 HEADING 'SQLSET
NAME'
COLUMN
EXEC_DATE FORMAT
A15 HEADING 'EXEC DATE'
COLUMN
TOTAL_SQL FORMAT
999,999 HEADING 'TOTAL SQL'
COLUMN
UNIQ_SQL FORMAT
999,999 HEADING 'UNIQ SQL'
COLUMN UNIQ_SQL_PLAN FORMAT
999,999 HEADING 'UNIQ SQL+PLAN'
SELECT A.SQLSET_NAME, TO_CHAR(SYSDATE, 'MM-DD HH24:MI:SS')
EXEC_DATE
--
,SYS_CONTEXT('USERENV','INSTANCE_NAME') AS INST_NAME
, COUNT(*) TOTAL_SQL
, COUNT(DISTINCT A.SQL_ID) AS
UNIQ_SQL
, COUNT(DISTINCT
A.SQL_ID||A.PLAN_HASH_VALUE) AS UNIQ_SQL_PLAN
FROM DBA_SQLSET_STATEMENTS A
WHERE 1=1
AND
A.SQLSET_OWNER = 'SPA'
AND A.SQLSET_NAME =
'SS_ORA12RF'
GROUP BY SQLSET_NAME
;
OWNER | SQLSET
NAME |
CREATED
| LAST MODIFIED | STMT COUNT
---------- | -------------------- | -------------------- |
-------------------- | ----------
SPA |
SS_ORA12RF |
2025-11-08 22:07:04 | 2025-11-08 22:07:04
| 62
1 row selected.
Elapsed: 00:00:00.00
SET LINESIZE 200
SET PAGESIZE 200
SET NUMWIDTH 12
SET TRIMSPOOL ON
COLUMN
PARSING_SCHEMA_NAME FORMAT
A15
HEADING 'PARSING_SCHEMA'
COLUMN
SQL_ID
FORMAT
A13
HEADING 'SQL_ID'
COLUMN
PLAN_HASH_VALUE FORMAT
9999999999 HEADING 'PLAN_HASH'
COLUMN
EXECUTIONS FORMAT
999,999,999 HEADING 'EXECS'
COLUMN
ELAPSED_TIME FORMAT
999,999,999,999 HEADING 'ELAPSED_TIME(us)'
COLUMN
CPU_TIME
FORMAT 999,999,999,999 HEADING 'CPU_TIME(us)'
COLUMN
BUFFER_GETS FORMAT
999,999,999,999 HEADING 'BUFFER_GETS'
COLUMN
DISK_READS FORMAT
999,999,999,999 HEADING 'DISK_READS'
SELECT
PARSING_SCHEMA_NAME
, SQL_ID
, PLAN_HASH_VALUE
, EXECUTIONS
, ELAPSED_TIME
, CPU_TIME
, BUFFER_GETS
,
DISK_READS
FROM DBA_SQLSET_STATEMENTS A
WHERE SQL_TEXT LIKE '%SPA_TEST%'
AND UPPER(SQL_TEXT) NOT LIKE '%' ||
UPPER('V$SQL') || '%'
;
PARSING_SCHEMA
SQL_ID
PLAN_HASH EXECS
ELAPSED_TIME(us)
CPU_TIME(us)
BUFFER_GETS DISK_READS
--------------- ------------- ----------- ------------
---------------- ---------------- ---------------- ----------------
TUNER
93uyt37jadcu3
482704130
2
140,959
136,478
36,440
0
TUNER
93uyt37jadcu3
738787558
2
1,406
1,267
76
0
TUNER
93uyt37jadcu3
1470838782
2
385
161
74
0
TUNER
9zr30g89mhym9
482704130
1
68,917
66,333
18,220
0
TUNER
9zr30g89mhym9
738787558
2
2,015
1,483
75
0
TUNER
9zr30g89mhym9
1470838782
2
2,204
2,067
74
0
TUNER
cmg9g4u3hp7qy
482704130
1
63,699
61,569
18,220
0
TUNER
cmg9g4u3hp7qy
738787558
1
1,097
1,002
37
0
TUNER
cmg9g4u3hp7qy
1470838782
1
1,175
1,133
37
0
TUNER
828m0ghwzfryd
482704130
2
1,522,308
336,392
36,470 17,995
TUNER
828m0ghwzfryd
738787558
2
69,253
14,224
286
4
TUNER
828m0ghwzfryd
1470838782
2
3,032
2,140
73
0
12 rows selected.
Elapsed: 00:00:00.01
--> 동일한 SQL_ID 기준 여러개의 PLAN HASH VALUE가 있는 목록이 그대로 뜨고 있음
13. 소스DB에서 캡쳐해온 성능 데이터를 타켓 DB에서 TEST
(실행시킴)
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias
sp
alias sp='rlwrap
sqlplus spa/oracle'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$
sp
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Nov 8 22:09:34 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Sat Nov 08 2025 22:00:27 +09:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.28.0.0.0
--BEGIN
--
DBMS_ADVISOR.DELETE_TASK('TASK_SS_ORA12RF_1');
--END;
--/
DECLARE
V_CODE NUMBER;
V_ERRM
VARCHAR2(2048);
STS_TASK VARCHAR2(1000);
BEGIN
STS_TASK :=
DBMS_SQLPA.CREATE_ANALYSIS_TASK(
SQLSET_OWNER=>'SPA'
, SQLSET_NAME=>'SS_ORA12RF'
,
TASK_NAME=>'TASK_SS_ORA12RF_1'
,
BASIC_FILTER=>NULL
,
ORDER_BY=>NULL
,
TOP_SQL=>NULL
,
DESCRIPTION=>NULL
);
EXCEPTION WHEN OTHERS THEN
V_CODE := SQLCODE;
V_ERRM := SQLERRM;
DBMS_OUTPUT.PUT_LINE('CREATE_TUNING_TASK ERROR: '||V_CODE||' '||V_ERRM);
END;
/
--> 새로운 태스크를 생성함
SET LINESIZE 180
SET PAGESIZE 200
SET NUMWIDTH 10
COLUMN
TASK_NAME FORMAT A30 HEADING 'TASK_NAME'
COLUMN OWNER
FORMAT A10 HEADING 'OWNER'
COLUMN
TASK_ID FORMAT 9999999 HEADING
'TASK_ID'
COLUMN
CREATED FORMAT A19 HEADING
'CREATED'
COLUMN LAST_MODIFIED FORMAT A19 HEADING
'LAST_MODIFIED'
COLUMN
STATUS FORMAT A10 HEADING
'STATUS'
SELECT
TASK_NAME
, OWNER
, TASK_ID
, TO_CHAR(CREATED,'YYYY-MM-DD
HH24:MI:SS') AS CREATED
,
TO_CHAR(LAST_MODIFIED,'YYYY-MM-DD HH24:MI:SS') AS LAST_MODIFIED
, STATUS
FROM DBA_ADVISOR_TASKS
WHERE ADVISOR_NAME = 'SQL Performance Analyzer'
AND TASK_NAME LIKE 'TASK_SS_ORA12RF_1'
AND STATUS = 'INITIAL'
;
TASK_NAME
OWNER TASK_ID
CREATED
LAST_MODIFIED STATUS
------------------------------ ---------- --------
------------------- ------------------- ----------
TASK_SS_ORA12RF_1
SPA 3187
2025-11-08 22:09:14 2025-11-08 22:09:14 INITIAL
--> 새로운 태스크가 정상적으로 등록임
1 row selected.
Elapsed: 00:00:00.01
DECLARE
V_CODE NUMBER;
V_ERRM
VARCHAR2(2048);
V_RST VARCHAR2(1000);
BEGIN
V_RST :=
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
TASK_NAME => 'TASK_SS_ORA12RF_1',
EXECUTION_TYPE
=> 'CONVERT SQLSET',
EXECUTION_NAME =>
'CONV1',
EXECUTION_DESC => 'CONV1'
);
EXCEPTION WHEN OTHERS
THEN
V_CODE := SQLCODE;
V_ERRM := SQLERRM;
DBMS_OUTPUT.PUT_LINE('COVNERTING_TUNING_TASK ERROR: '||V_CODE||'
'||V_ERRM);
END;
/
--> 소스DB에서 가져온 성능 데이터인 컨버트 시킨 것임
--> 이렇게 해야 소스 DB의 성능과 타켓 DB의 성능을 비교할 수 있음
SELECT TASK_NAME
, OWNER
, TASK_ID
, TO_CHAR(CREATED,'YYYY-MM-DD
HH24:MI:SS') AS CREATED
,
TO_CHAR(LAST_MODIFIED,'YYYY-MM-DD HH24:MI:SS') AS LAST_MODIFIED
, STATUS
FROM DBA_ADVISOR_TASKS
WHERE 1=1
AND
TASK_NAME LIKE 'TASK_SS_ORA12RF_1'
AND
ADVISOR_NAME = 'SQL Performance Analyzer'
;
TASK_NAME
OWNER TASK_ID
CREATED
LAST_MODIFIED STATUS
------------------------------ ---------- --------
------------------- ------------------- ----------
TASK_SS_ORA12RF_1
SPA 3187
2025-11-08 22:09:14 2025-11-08 22:12:03 COMPLETED
1 row selected.
Elapsed: 00:00:00.00
SET LINESIZE 220
SET PAGESIZE 200
SET NUMWIDTH 10
SET TRIMSPOOL
ON
COLUMN
OWNER
FORMAT A10 HEADING 'OWNER'
COLUMN
TASK_ID
FORMAT 999999 HEADING 'TASK_ID'
COLUMN
TASK_NAME
FORMAT A25 HEADING 'TASK_NAME'
COLUMN
EXECUTION_NAME FORMAT A20 HEADING
'EXEC_NAME'
COLUMN
EXECUTION_ID FORMAT 999999
HEADING 'EXEC_ID'
COLUMN
EXECUTION_TYPE FORMAT A15 HEADING
'TYPE'
COLUMN
EXECUTION_START FORMAT A19 HEADING
'START_TIME'
COLUMN EXECUTION_LAST_MODIFIED FORMAT
A19 HEADING 'LAST_MODIFIED'
COLUMN
EXECUTION_END FORMAT A19 HEADING
'END_TIME'
COLUMN
REQUESTED_DOP FORMAT 9999
HEADING 'REQ|DOP'
COLUMN
ACTUAL_DOP FORMAT
9999 HEADING 'ACT|DOP'
COLUMN
STATUS
FORMAT A10 HEADING 'STATUS'
SELECT
OWNER
, TASK_ID
, TASK_NAME
, EXECUTION_NAME
, EXECUTION_ID
, EXECUTION_TYPE
, TO_CHAR(EXECUTION_START,
'YYYY-MM-DD HH24:MI:SS') AS
EXECUTION_START
,
TO_CHAR(EXECUTION_LAST_MODIFIED, 'YYYY-MM-DD HH24:MI:SS') AS
EXECUTION_LAST_MODIFIED
,
TO_CHAR(EXECUTION_END, 'YYYY-MM-DD
HH24:MI:SS') AS
EXECUTION_END
, REQUESTED_DOP
, ACTUAL_DOP
, STATUS
FROM DBA_ADVISOR_EXECUTIONS
WHERE TASK_NAME = 'TASK_SS_ORA12RF_1'
AND EXECUTION_NAME = 'CONV1'
;
OWNER TASK_ID
TASK_NAME
EXEC_NAME
EXEC_ID TYPE
START_TIME
LAST_MODIFIED
END_TIME
DOP DOP STATUS
---------- -------
------------------------- -------------------- -------
------------ ------------------- -------------------
------------------- ----- ----- ----------
SPA
3187 TASK_SS_ORA12RF_1
CONV1
3239 CONVERT SQLSSET 2025-11-08 22:12:03 2025-11-08 22:12:03 2025-11-08
22:12:03 0 1 COMPLETED
--> 컨버트가 완료된
상태임
1 row
selected.
Elapsed: 00:00:00.00
--> 태스트를 직접 실행함 (결국 소스 DB에서
돌던 SQL을 타켓 DB에서 실행시켜 보는 것임)
EXEC
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(TASK_NAME=>'TASK_SS_ORA12RF_1',
PARAMETER=>'TIME_LIMIT', VALUE=>'UNLIMITED');
EXEC
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(TASK_NAME=>'TASK_SS_ORA12RF_1',
PARAMETER=>'LOCAL_TIME_LIMIT', VALUE=>'UNLIMITED');
EXEC
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(TASK_NAME=>'TASK_SS_ORA12RF_1',
PARAMETER=>'REPLACE_SYSDATE_WITH', VALUE=>'SQLSET_SYSDATE');
EXEC
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(TASK_NAME=>'TASK_SS_ORA12RF_1',
PARAMETER=>'DISABLE_MULTI_EXEC', VALUE=>'FALSE');
ALTER SESSION SET
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
EXEC DBMS_APPLICATION_INFO.SET_MODULE('SPA TASK RUNNING','TASK_SS_ORA12RF_1');
DECLARE
V_CODE NUMBER;
V_ERRM
VARCHAR2(2048);
V_RST VARCHAR2(1000);
BEGIN
V_RST :=
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
TASK_NAME => 'TASK_SS_ORA12RF_1',
EXECUTION_TYPE
=> 'TEST EXECUTE',
EXECUTION_NAME =>
'EXEC1',
EXECUTION_PARAMS=>
DBMS_ADVISOR.ARGLIST(
'BASIC_FILTER', ' PARSING_SCHEMA_NAME IN (''TUNER'') '
, 'TEST_EXECUTE_DOP', 1
, 'LOCAL_TIME_LIMIT','UNLIMITED'
, 'TIME_LIMIT', 'UNLIMITED'
, 'EXECUTE_FULLDML','TRUE'
),
EXECUTION_DESC => 'EXEC1'
);
EXCEPTION WHEN OTHERS
THEN
V_CODE := SQLCODE;
V_ERRM := SQLERRM;
DBMS_OUTPUT.PUT_LINE('EXECUTING_TUNING_TASK ERROR: '||V_CODE||'
'||V_ERRM);
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.08
--아래의 SQL문으로 잘 실행됐는지 확인
SET LINESIZE 220
SET PAGESIZE 200
SET NUMWIDTH 10
SET TRIMSPOOL ON
COLUMN
OWNER
FORMAT A10 HEADING 'OWNER'
COLUMN
TASK_ID
FORMAT 999999 HEADING 'TASK_ID'
COLUMN
TASK_NAME
FORMAT A25 HEADING 'TASK_NAME'
COLUMN
EXECUTION_NAME FORMAT A20 HEADING
'EXEC_NAME'
COLUMN
EXECUTION_ID FORMAT 999999
HEADING 'EXEC_ID'
COLUMN
EXECUTION_TYPE FORMAT A12 HEADING
'TYPE'
COLUMN
EXECUTION_START FORMAT A19 HEADING
'START_TIME'
COLUMN EXECUTION_LAST_MODIFIED FORMAT
A19 HEADING 'LAST_MODIFIED'
COLUMN
EXECUTION_END FORMAT A19 HEADING
'END_TIME'
COLUMN
REQUESTED_DOP FORMAT 9999
HEADING 'REQ|DOP'
COLUMN
ACTUAL_DOP FORMAT
9999 HEADING 'ACT|DOP'
COLUMN
STATUS
FORMAT A10 HEADING 'STATUS'
SELECT
OWNER
, TASK_ID
, TASK_NAME
, EXECUTION_NAME
, EXECUTION_ID
, EXECUTION_TYPE
, TO_CHAR(EXECUTION_START,
'YYYY-MM-DD HH24:MI:SS') AS
EXECUTION_START
,
TO_CHAR(EXECUTION_LAST_MODIFIED, 'YYYY-MM-DD HH24:MI:SS') AS
EXECUTION_LAST_MODIFIED
,
TO_CHAR(EXECUTION_END, 'YYYY-MM-DD
HH24:MI:SS') AS
EXECUTION_END
, REQUESTED_DOP
, ACTUAL_DOP
, STATUS
FROM DBA_ADVISOR_EXECUTIONS
WHERE TASK_NAME = 'TASK_SS_ORA12RF_1'
AND EXECUTION_NAME = 'EXEC1'
;
SET LINESIZE 200
SET PAGESIZE 200
SET NUMWIDTH 10
SET TRIMSPOOL ON
COLUMN
OWNER
FORMAT A10 HEADING 'OWNER'
COLUMN
TASK_ID
FORMAT 999999 HEADING 'TASK_ID'
COLUMN
TASK_NAME
FORMAT A25 HEADING 'TASK_NAME'
COLUMN
EXECUTION_START FORMAT A19 HEADING
'START_TIME'
COLUMN
EXECUTION_END FORMAT A19 HEADING
'END_TIME'
COLUMN
STATUS
FORMAT A12 HEADING 'STATUS'
COLUMN
PCT_COMPLETION_TIME FORMAT 999.9 HEADING 'COMP(%)'
COLUMN PROGRESS_METRIC FORMAT
999999.9 HEADING 'PROGRESS'
COLUMN
METRIC_UNITS FORMAT A10
HEADING 'UNIT'
COLUMN
ACTIVITY_COUNTER FORMAT 999999 HEADING
'ACT_CNT'
COLUMN RECOMMENDATION_COUNT FORMAT 999999
HEADING 'RECO_CNT'
SELECT
OWNER
, TASK_ID
, TASK_NAME
, TO_CHAR(EXECUTION_START,
'YYYY-MM-DD HH24:MI:SS') AS EXECUTION_START
, TO_CHAR(EXECUTION_END,
'YYYY-MM-DD HH24:MI:SS') AS EXECUTION_END
, STATUS
-- , STATUS_MESSAGE
, PCT_COMPLETION_TIME
, PROGRESS_METRIC
, METRIC_UNITS
, ACTIVITY_COUNTER
, RECOMMENDATION_COUNT
-- , ERROR_MESSAGE
FROM DBA_ADVISOR_LOG
WHERE TASK_NAME =
'TASK_SS_ORA12RF_1'
;
--실행 리포트 출력
--HTML 모드
SET LONG 100000000
LONGCHUNKSIZE 1000000 PAGESIZE 0 LINESIZE 32767 TRIMSPOOL ON
SPOOL spa_exec1_report.html
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK(
task_name => 'TASK_SS_ORA12RF_1'
,
type =>
'HTML' -- TEXT 도 가능
,
level =>
'TYPICAL' -- or 'ALL' (자세히)
,
section =>
'ALL' -- SUMMARY만
보려면 'SUMMARY'
,
execution_name => 'EXEC1'
)
FROM dual;
SPOOL OFF
--TEXT 모드
SET LONG 100000000
LONGCHUNKSIZE 1000000 PAGESIZE 0 LINESIZE 32767 TRIMSPOOL ON
SPOOL spa_exec1_report.txt
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK(
task_name => 'TASK_SS_ORA12RF_1'
,
type =>
'TEXT' -- TEXT 도 가능
,
level =>
'ALL' -- or 'ALL' (자세히)
,
section =>
'ALL' -- SUMMARY만
보려면 'SUMMARY'
,
execution_name => 'EXEC1'
)
FROM dual;
SPOOL OFF
14. 타켓 DB에서 소스DB에서 캡쳐해온 성능 데이터와 타켓 DB에서 돌린 성능 데이터를
비교
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias
sp
alias sp='rlwrap
sqlplus spa/oracle'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$
sp
SQL*Plus: Release
19.0.0.0.0 - Production on Sat Nov 8 23:39:40 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Sat Nov 08 2025 23:23:28 +09:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 -
Production
Version 19.28.0.0.0
[ol8ora19rf1]<SPA@ORA19RF1>$
EXEC
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(TASK_NAME=>'TASK_SS_ORA12RF_1',PARAMETER=>'WORKLOAD_IMPACT_THRESHOLD',VALUE=>0.0001);
EXEC
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(TASK_NAME=>'TASK_SS_ORA12RF_1',PARAMETER=>'SQL_IMPACT_THRESHOLD',VALUE=>0.0001);
--BUFFER_GETS 로 비교
DECLARE
V_CODE NUMBER;
V_ERRM
VARCHAR2(2048);
V_RST VARCHAR2(1000);
BEGIN
V_RST :=
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
TASK_NAME => 'TASK_SS_ORA12RF_1',
EXECUTION_TYPE
=> 'COMPARE PERFORMANCE',
EXECUTION_NAME =>
'COMP_BUFFER_GETS',
EXECUTION_PARAMS=>
DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC','BUFFER_GETS','EXECUTION_NAME1','CONV1','EXECUTION_NAME2','EXEC1'),
EXECUTION_DESC => 'COMP_BUFFER_GETS'
);
EXCEPTION WHEN OTHERS
THEN
V_CODE := SQLCODE;
V_ERRM := SQLERRM;
DBMS_OUTPUT.PUT_LINE('COMPARE_TUNING_TASK ERROR: '||V_CODE||' '||V_ERRM);
END;
/
--ELAPSED_TIME 으로
비교
DECLARE
V_CODE NUMBER;
V_ERRM
VARCHAR2(2048);
V_RST VARCHAR2(1000);
BEGIN
V_RST :=
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
TASK_NAME => 'TASK_SS_ORA12RF_1',
EXECUTION_TYPE
=> 'COMPARE PERFORMANCE',
EXECUTION_NAME =>
'COMP_ELAPSED_TIME',
EXECUTION_PARAMS=>
DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC','ELAPSED_TIME','EXECUTION_NAME1','CONV1','EXECUTION_NAME2','EXEC1'),
EXECUTION_DESC => 'COMP_ELAPSED_TIME'
);
EXCEPTION WHEN OTHERS THEN
V_CODE := SQLCODE;
V_ERRM := SQLERRM;
DBMS_OUTPUT.PUT_LINE('COMPARE_TUNING_TASK ERROR:
'||V_CODE||' '||V_ERRM);
END;
/
--DISK_READS 로
비교
DECLARE
V_CODE NUMBER;
V_ERRM
VARCHAR2(2048);
V_RST VARCHAR2(1000);
BEGIN
V_RST :=
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
TASK_NAME => 'TASK_SS_ORA12RF_1',
EXECUTION_TYPE
=> 'COMPARE PERFORMANCE',
EXECUTION_NAME =>
'COMP_DISK_READS',
EXECUTION_PARAMS=>
DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC','DISK_READS','EXECUTION_NAME1','CONV1','EXECUTION_NAME2','EXEC1'),
EXECUTION_DESC => 'COMP_DISK_READS'
);
EXCEPTION WHEN OTHERS THEN
V_CODE := SQLCODE;
V_ERRM := SQLERRM;
DBMS_OUTPUT.PUT_LINE('COMPARE_TUNING_TASK ERROR:
'||V_CODE||' '||V_ERRM);
END;
/
--CPU_TIME 으로
비교
DECLARE
V_CODE NUMBER;
V_ERRM
VARCHAR2(2048);
V_RST VARCHAR2(1000);
BEGIN
V_RST :=
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
TASK_NAME => 'TASK_SS_ORA12RF_1',
EXECUTION_TYPE
=> 'COMPARE PERFORMANCE',
EXECUTION_NAME =>
'COMP_DISK_CPU_TIME',
EXECUTION_PARAMS=>
DBMS_ADVISOR.ARGLIST('COMPARISON_METRIC','CPU_TIME','EXECUTION_NAME1','CONV1','EXECUTION_NAME2','EXEC1'),
EXECUTION_DESC => 'COMP_DISK_CPU_TIME'
);
EXCEPTION WHEN OTHERS THEN
V_CODE := SQLCODE;
V_ERRM := SQLERRM;
DBMS_OUTPUT.PUT_LINE('COMPARE_TUNING_TASK ERROR:
'||V_CODE||' '||V_ERRM);
END;
/
SET LINESIZE 220
SET PAGESIZE 200
SET NUMWIDTH 10
SET TRIMSPOOL ON
COLUMN
OWNER
FORMAT A10 HEADING 'OWNER'
COLUMN
TASK_ID
FORMAT 999999 HEADING 'TASK_ID'
COLUMN
TASK_NAME
FORMAT A25 HEADING 'TASK_NAME'
COLUMN
EXECUTION_NAME FORMAT A18 HEADING
'EXEC_NAME'
COLUMN
EXECUTION_ID FORMAT 999999
HEADING 'EXEC_ID'
COLUMN
EXECUTION_TYPE FORMAT A20 HEADING
'EXEC_TYPE'
COLUMN
EXECUTION_START FORMAT A19 HEADING
'START_TIME'
COLUMN EXECUTION_LAST_MODIFIED FORMAT A19
HEADING 'LAST_MODIFIED'
COLUMN
EXECUTION_END FORMAT A19 HEADING
'END_TIME'
COLUMN
REQUESTED_DOP FORMAT 9999
HEADING 'REQ|DOP'
COLUMN
ACTUAL_DOP FORMAT
9999 HEADING 'ACT|DOP'
COLUMN
STATUS
FORMAT A10 HEADING 'STATUS'
SELECT OWNER, TASK_ID,
TASK_NAME, EXECUTION_NAME, EXECUTION_ID, EXECUTION_TYPE, EXECUTION_START,
EXECUTION_LAST_MODIFIED, EXECUTION_END, REQUESTED_DOP, ACTUAL_DOP, STATUS
FROM DBA_ADVISOR_EXECUTIONS
WHERE
TASK_NAME = 'TASK_SS_ORA12RF_1'
AND EXECUTION_NAME LIKE
'COMP%';
OWNER TASK_ID
TASK_NAME
EXEC_NAME EXEC_ID
EXEC_TYPE
START_TIME
LAST_MODIFIED
END_TIME
DOP DOP STATUS
---------- -------
------------------------- ------------------ ------- --------------------
------------------- ------------------- ------------------- ----- -----
----------
SPA
3187 TASK_SS_ORA12RF_1
COMP_BUFFER_GETS 3262 COMPARE PERFORMANCE
2025-11-08:23:40:18 2025-11-08:23:40:18
2025-11-08:23:40:18 0 1
COMPLETED
SPA
3187 TASK_SS_ORA12RF_1
COMP_DISK_CPU_TIME 3265 COMPARE PERFORMANCE
2025-11-08:23:41:24 2025-11-08:23:41:24
2025-11-08:23:41:24 0 1
COMPLETED
SPA
3187 TASK_SS_ORA12RF_1
COMP_DISK_READS 3264 COMPARE
PERFORMANCE 2025-11-08:23:41:15 2025-11-08:23:41:15
2025-11-08:23:41:15 0 1
COMPLETED
SPA
3187 TASK_SS_ORA12RF_1
COMP_ELAPSED_TIME 3263 COMPARE PERFORMANCE
2025-11-08:23:41:05 2025-11-08:23:41:05
2025-11-08:23:41:05 0 1
COMPLETED
4 rows selected.
Elapsed: 00:00:00.00
SELECT OWNER, TASK_ID, TASK_NAME, EXECUTION_NAME,
EXECUTION_ID, EXECUTION_TYPE, EXECUTION_START, EXECUTION_LAST_MODIFIED,
EXECUTION_END, REQUESTED_DOP, ACTUAL_DOP, STATUS
FROM DBA_ADVISOR_EXECUTIONS
WHERE TASK_NAME =
'TASK_SS_ORA12RF_1'
AND EXECUTION_NAME LIKE
'EXEC%';
OWNER TASK_ID
TASK_NAME
EXEC_NAME EXEC_ID
EXEC_TYPE
START_TIME
LAST_MODIFIED
END_TIME
DOP DOP STATUS
---------- -------
------------------------- ------------------ ------- --------------------
------------------- ------------------- ------------------- ----- -----
----------
SPA
3187 TASK_SS_ORA12RF_1
EXEC1
3240 TEST EXECUTE
2025-11-08:22:17:00 2025-11-08:22:17:04
2025-11-08:22:17:04 1 1
COMPLETED
1 row selected.
Elapsed: 00:00:00.01
SELECT OWNER, TASK_ID, TASK_NAME, EXECUTION_NAME,
EXECUTION_ID, EXECUTION_TYPE, EXECUTION_START, EXECUTION_LAST_MODIFIED,
EXECUTION_END, REQUESTED_DOP, ACTUAL_DOP, STATUS
FROM DBA_ADVISOR_EXECUTIONS
WHERE TASK_NAME =
'TASK_SS_ORA12RF_1'
AND EXECUTION_NAME LIKE
'CONV%';
OWNER TASK_ID
TASK_NAME
EXEC_NAME EXEC_ID
EXEC_TYPE
START_TIME
LAST_MODIFIED
END_TIME
DOP DOP STATUS
---------- -------
------------------------- ------------------ ------- --------------------
------------------- ------------------- ------------------- ----- -----
----------
SPA
3187 TASK_SS_ORA12RF_1
CONV1
3239 CONVERT SQLSET 2025-11-08:22:12:03
2025-11-08:22:12:03 2025-11-08:22:12:03
0 1 COMPLETED
1 row selected.
Elapsed: 00:00:00.00
--비교 리포트 출력 (TEXT
방식)
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1',
'TEXT', 'ERRORS', 'SUMMARY', EXECUTION_NAME=>'COMP_BUFFER_GETS') REPORT FROM
DUAL;
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'TIMEOUT',
'SUMMARY', EXECUTION_NAME=>'COMP_BUFFER_GETS') FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1',
'TEXT', 'UNSUPPORTED', 'SUMMARY', EXECUTION_NAME=>'COMP_BUFFER_GETS')
REPORT FROM DUAL;
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'CHANGED_PLANS',
'SUMMARY', EXECUTION_NAME=>'COMP_BUFFER_GETS') REPORT FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1',
'TEXT', 'REGRESSED', 'SUMMARY', EXECUTION_NAME=>'COMP_BUFFER_GETS') FROM
DUAL;
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'ALL', 'ALL',
EXECUTION_NAME=>'COMP_BUFFER_GETS') FROM DUAL;
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'CHANGED_PLANS',
'ALL', EXECUTION_NAME=>'COMP_BUFFER_GETS') REPORT FROM DUAL;
...생략
op 7 SQL with Changed Plans Sorted by Absolute Value of
Change Impact on the Workload
---------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
|
|
| Impact on | Execution |
Metric | Metric |
Impact | Plan |
| object_id |
sql_id | Workload | Frequency |
Before | After
| on SQL | Change |
----------------------------------------------------------------------------------------------------
| 93 |
828m0ghwzfryd | 3.43%
| 2
| 18235
| 46 | 99.75% | y
|
| 98 |
93uyt37jadcu3 | 3.42%
| 2
| 18220
| 46 | 99.75% | y
|
| 105 |
9zr30g89mhym9 | 1.71%
| 1
| 18220
| 46 | 99.75% | y
|
| 116 |
cmg9g4u3hp7qy | 1.71%
| 1
| 18220
| 46 | 99.75% | y
|
| 111 |
brqtr9hqc7g3u | -1.32%
| 1
| 18432
| 32480 | -76.22% | y |
| 112 | c563f1v0qayn4
| .72%
| 1
| 7687
| 1 | 99.99% |
y |
| 107 | ax51s81797wdk
| 0%
| 7 | 5.14285714285714
| 3 | 41.67% |
y |
----------------------------------------------------------------------------------------------------
...생략
SQL Details:
-----------------------------
Object
ID :
93
Schema
Name :
TUNER
Container Name
: Unknown (con_dbid:
4288610047)
SQL
ID
:
828m0ghwzfryd
Execution Frequency :
2
SQL
Text :
SELECT /*
SPA_TEST_ORA12RF2
SELECT_FROM_TB_CUST_WHERE_BRTHDY */ * FROM TB_CUST
A
WHERE A.BRTHDY LIKE :V_BRTHDY ||
'%'
Execution
Statistics:
-----------------------------
------------------------------------------------------------------
|
| Impact on | Value | Value | Impact |
| Stat
Name |
Workload | Before | After | on SQL |
------------------------------------------------------------------
|
elapsed_time
| 11.04% | .761154 | .000021 | 100% |
|
parse_time
|
| | .003881
| |
|
cpu_time
| 5.76% | .168196 | .000021 | 99.99% |
|
user_io_time
|
|
| 0
| |
|
buffer_gets
| 3.43% | 18235
| 46 | 99.75% |
|
cost
| 48.62% | 4994
| 50 | 99% |
|
reads
| 18.57% | 8997
| 0 | 100% |
|
writes
| 0%
| 0 | 0
| 0% |
| io_interconnect_bytes
|
|
| 0
| |
|
rows
|
| 31 | 42
| |
------------------------------------------------------------------
Note: time statistics are displayed in
seconds
Notes:
-----------------------------
After Change:
1. The statement was first executed to warm the buffer
cache.
2. Statistics shown were averaged over next 9
executions.
Findings (3):
-----------------------------
1. The performance of this SQL has
improved.
2. The structure of the SQL execution plan has
changed.
3. The number of returned rows in execution
'CONV1' is different than in
execution
'EXEC1'.
Execution Plan Before Change:
-----------------------------
Plan Hash Value : 482704130
-----------------------------------------------------------------------
| Id |
Operation |
Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT
|
| | | 4994
| |
| 1 | TABLE ACCESS FULL | TB_CUST
| 46 | 5750 | 4994 | 00:00:01 |
-----------------------------------------------------------------------
Execution Plan
After Change:
-----------------------------
Plan
Id :
229
Plan
Hash Value : 1470838782
-------------------------------------------------------------------------------------------------
| Id |
Operation
| Name | Rows |
Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
|
| 46 | 5750 | 50 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID
BATCHED | TB_CUST | 46
| 5750 | 50 | 00:00:01 |
| * 2
| INDEX RANGE
SCAN
| IDX_TB_CUST_02 | 46 |
| 3 | 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("A"."BRTHDY" LIKE :V_BRTHDY||'%')
* 2 - filter("A"."BRTHDY" LIKE :V_BRTHDY||'%')
--> 기존에 FULL
SCAN에서 INDEX RANGE SCAN으로 바뀌었다고 나옴
--> 소스 db에서 하나의 SQL_ID 기준 여러 개의 PLAN_HASH_VALUE가 있는 경우
--> 타켓 DB에서는 그 하나의 SQL_ID를 실행
시킨 후 PLAN_HASH_VALUE가 도출 되면 소스에 있었던 여러 개의 PLAH_HASH_VALUE들과 비교하여
PLAN_HASH_VALUE가 다르다면 바뀌었다고 보고해줌
--> 소스 DB에서 PHV가 여러개 였던 SQL_ID를 타켓 DB에서 돌린 후 특정 PHV가
나오면 그거랑 소스 DB에 있었던 여러 개의 PHV와 비교해서 다르면 다르다고
말해줌
--> 결국 소스 DB에서 하나의 SQL_ID 기준 여러 개의 PHV가 존재하는 경우, 타켓 DB에서는 해당 SQL_ID를 실행 후 특정 PHV를 얻으면 그 PHV와 소스 DB에 있는 여러 개의 PHV와 비교하는 것임
--> CONV1에는 SQL_ID 기준 모든 PHV가 저장되고 EXEC1에는 SQL_ID 기준 하나의 PHV가 존재해서 서로를 비교하게 되는 것임 (합리적인 동작이라고 판단됨)
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'ERRORS',
'SUMMARY', EXECUTION_NAME=>'COMP_ELAPSED_TIME') REPORT FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1',
'TEXT', 'TIMEOUT', 'SUMMARY', EXECUTION_NAME=>'COMP_ELAPSED_TIME') FROM
DUAL;
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'UNSUPPORTED',
'SUMMARY', EXECUTION_NAME=>'COMP_ELAPSED_TIME') REPORT FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1',
'TEXT', 'CHANGED_PLANS', 'SUMMARY', EXECUTION_NAME=>'COMP_ELAPSED_TIME')
REPORT FROM DUAL;
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'REGRESSED',
'SUMMARY', EXECUTION_NAME=>'COMP_ELAPSED_TIME') FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1',
'TEXT', 'ALL', 'ALL', EXECUTION_NAME=>'COMP_ELAPSED_TIME') FROM
DUAL;
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'ERRORS',
'SUMMARY', EXECUTION_NAME=>'COMP_DISK_READS') REPORT FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1',
'TEXT', 'TIMEOUT', 'SUMMARY', EXECUTION_NAME=>'COMP_DISK_READS') FROM
DUAL;
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'UNSUPPORTED',
'SUMMARY', EXECUTION_NAME=>'COMP_DISK_READS') REPORT FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1',
'TEXT', 'CHANGED_PLANS', 'SUMMARY', EXECUTION_NAME=>'COMP_DISK_READS') REPORT
FROM DUAL;
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'REGRESSED',
'SUMMARY', EXECUTION_NAME=>'COMP_DISK_READS') FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1',
'TEXT', 'ALL', 'ALL', EXECUTION_NAME=>'COMP_DISK_READS') FROM
DUAL;
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'ERRORS',
'SUMMARY', EXECUTION_NAME=>'COMP_DISK_CPU_TIME') REPORT FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1',
'TEXT', 'TIMEOUT', 'SUMMARY', EXECUTION_NAME=>'COMP_DISK_CPU_TIME')
FROM DUAL;
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'UNSUPPORTED',
'SUMMARY', EXECUTION_NAME=>'COMP_DISK_CPU_TIME') REPORT FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1',
'TEXT', 'CHANGED_PLANS', 'SUMMARY', EXECUTION_NAME=>'COMP_DISK_CPU_TIME')
REPORT FROM DUAL;
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1', 'TEXT', 'REGRESSED',
'SUMMARY', EXECUTION_NAME=>'COMP_DISK_CPU_TIME') FROM DUAL;
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('TASK_SS_ORA12RF_1',
'TEXT', 'ALL', 'ALL', EXECUTION_NAME=>'COMP_DISK_CPU_TIME') FROM
DUAL;
--특정 OBJECT_ID 기준으로 리포트 출력할 수
있음
SELECT OBJECT_ID
, ATTR1
, B.*
FROM DBA_ADVISOR_OBJECTS B
WHERE TASK_NAME LIKE
'TASK_SS_ORA12RF_1%'
AND EXECUTION_NAME LIKE
'%COMP_BUFFER_GETS%'
AND ATTR10 > 0
AND ATTR5 <> 0
ORDER BY ATTR10 DESC
;
--> 여기서 얻어진 OBJECT_ID를 아래의 인자로 집어 넣음
SELECT
DBMS_SQLPA.REPORT_ANALYSIS_TASK
(
'TASK_SS_ORA12RF_1'
,
'TEXT'
, 'ALL'
, 'ALL'
, 93 --> OBJECT_ID
, NULL
, 'COMP_BUFFER_GETS'
)
FROM DUAL;
--SQL_TEXT 기준으로 조회할 수
있음
SELECT A.SQL_TEXT,
A.* FROM DBA_SQLSET_STATEMENTS A WHERE A.SQLSET_NAME =
'SELECT_FROM_TB_CUST_WHERE_BRTHDY';
--바인드 변수를 조회할 수 있음
SELECT SQL_ID
, POSITION
, ANYDATA.GETTYPENAME(VALUE)
TYPE_NAME
, CASE
ANYDATA.GETTYPENAME(VALUE)
WHEN
'SYS.NUMBER' THEN TO_CHAR(ANYDATA.ACCESSNUMBER(VALUE))
WHEN
'SYS.DATE' THEN TO_CHAR(ANYDATA.ACCESSDATE(VALUE))
WHEN
'SYS.VARCHAR2' THEN ANYDATA.ACCESSVARCHAR2(VALUE)
WHEN
'SYS.CHAR' THEN ANYDATA.ACCESSCHAR(VALUE)
ELSE
NULL
END AS DATA
FROM DBA_SQLSET_BINDS
WHERE SQLSET_NAME='SS_ORA12RF'
AND SQL_ID =
'828m0ghwzfryd';
--SQL PLAN 뷰 확인
--조건
# operation = view
/ window
# options = outer
#
operation = view && filter_predicates is not null
SELECT A.*,
( SELECT OBJECT_ID FROM
DBA_ADVISOR_OBJECTS B WHERE TASK_NAME LIKE 'TASK_SS_ORA12RF_1%' AND
EXECUTION_NAME LIKE '%COMPARE_BUFFER%' AND ATTR1 = A.SQL_ID ) OBJECT_ID,
( SELECT EXECUTIONS FROM
DBA_SQLSET_STATEMENTS B WHERE SQLSET_NAME = 'SS_ORA12RF' AND B.SQL_ID = A.SQL_ID
AND ROWNUM = 1 ) EXECUTIONS,
(
SELECT SQL_TEXT FROM DBA_SQLSET_STATEMENTS B WHERE SQLSET_NAME = 'SS_ORA12RF'
AND B.SQL_ID = A.SQL_ID AND ROWNUM = 1 ) SQL_TEXT
FROM
(
SELECT /*+
FULL(A) USE_HASH(A B) */UNIQUE A.SQL_ID , A.PLAN_HASH_VALUE
FROM DBA_ADVISOR_SQLPLANS A,
(SELECT /*+ FULL(A)
USE_HASH(A B) NO_MERGE */UNIQUE A.SQL_ID
FROM
DBA_ADVISOR_SQLPLANS A,
(SELECT /*+ NO_MERGE */UNIQUE SQL_ID
FROM DBA_ADVISOR_SQLPLANS
WHERE TASK_NAME LIKE 'TASK_SS_ORA12RF_1%'
AND OPTIONS LIKE '%OUTER%' ) B
WHERE
A.SQL_ID = B.SQL_ID
AND TASK_NAME LIKE 'TASK_SS_ORA12RF_1%'
AND A.OPERATION LIKE '%WINDOW%' ) B
WHERE A.SQL_ID
= B.SQL_ID
AND TASK_NAME LIKE
'TASK_SS_ORA12RF_1%'
AND A.OPERATION LIKE
'%VIEW%'
AND A.FILTER_PREDICATES IS NOT
NULL
) A ORDER BY 4 DESC ;
--> 위의 SQL문으로 특정 동작을 하는 SQL문 조회할 수 있음
--비교 리포트 출력 (HTML 방식)
--summary_buffer_gets.html
set time on timing
on echo on
set heading off long 10000000 longchunksize
10000000 echo off
set linesize 1000 trimspool on
alter session set events='31156 trace name context forever,
level 0x400';
spool summary_buffer_gets.html
select xmltype(
dbms_sqlpa.report_analysis_task(
'TASK_SS_ORA12RF_1'
,'html'
,'typical'
,'summary'
,null
,50000
,'COMP_BUFFER_GETS'
)
).getclobval(0,0)
from dual;
spool off
--regressed_buffer_gets.html
set time on timing on echo on
set
heading off long 10000000 longchunksize 10000000 echo off
set linesize 1000 trimspool on
alter
session set events='31156 trace name context forever, level 0x400';
spool regressed_buffer_gets.html
select xmltype(
dbms_sqlpa.report_analysis_task(
'TASK_SS_ORA12RF_1'
,'html'
,'regressed'
,'all'
,null
,10000
,'COMP_BUFFER_GETS'
)
).getclobval(0,0)
from
dual;
spool off
--plan_change_buffer_gets.html
set time on timing on echo on
set
heading off long 10000000 longchunksize 10000000 echo off
set linesize 1000 trimspool on
alter
session set events='31156 trace name context forever, level 0x400';
spool plan_change_buffer_gets.html
select xmltype(
dbms_sqlpa.report_analysis_task(
'TASK_SS_ORA12RF_1'
,'html'
,'changed_plans'
,'all'
,null
,10000
,'COMP_BUFFER_GETS'
)
).getclobval(0,0)
from
dual;
spool off
--unsupported_total.html
set
time on timing on echo on
set heading off long 10000000
longchunksize 10000000 echo off
set linesize 1000
trimspool on
alter session set events='31156 trace name
context forever, level 0x400';
spool
unsupported_total.html
select xmltype(
dbms_sqlpa.report_analysis_task(
'TASK_SS_ORA12RF_1'
,'html'
,'unsupported'
,'all'
,null
,10000
,'COMP_BUFFER_GETS'
)
).getclobval(0,0)
from
dual;
spool off
--error_total.html
set time on
timing on echo on
set heading off long 10000000
longchunksize 10000000 echo off
set linesize 1000
trimspool on
alter session set events='31156 trace name
context forever, level 0x400';
spool
error_total.html
select xmltype(
dbms_sqlpa.report_analysis_task(
'TASK_SS_ORA12RF_1'
,'html'
,'errors'
,'all'
,null
,10000
,'COMP_BUFFER_GETS'
)
).getclobval(0,0)
from
dual;
spool off
--timeout_total.html
set time
on timing on echo on
set heading off long 10000000
longchunksize 10000000 echo off
set linesize 1000
trimspool on
alter session set events='31156 trace name
context forever, level 0x400';
spool
timeout_total.html
select xmltype(
dbms_sqlpa.report_analysis_task(
'TASK_SS_ORA12RF_1'
,'html'
,'timeout'
,'all'
,null
,10000
,'COMP_BUFFER_GETS'
)
).getclobval(0,0)
from
dual;
spool off
--detail_buffer_gets.html
set
time on timing on echo on
set heading off long 10000000
longchunksize 10000000 echo off
set linesize 1000
trimspool on
alter session set events='31156 trace name
context forever, level 0x400';
spool
detail_buffer_gets.html
select xmltype(
dbms_sqlpa.report_analysis_task(
'TASK_SS_ORA12RF_1'
,'html'
,'all'
,'all'
,null
,10000
,'COMP_BUFFER_GETS'
)
).getclobval(0,0)
from
dual;
spool off
set time off timing
off echo off veri off feed off
set heading off long
10000000 longchunksize 10000000 echo off
set
linesize 1000 trimspool on
alter session set
events='31156 trace name context forever, level 0x400';
accept sql_id char
prompt 'Enter the sql id >'
select object_id from
dba_advisor_objects b where task_name like 'TASK_%INST_POKOR' and
EXECUTION_NAME like '%COMPARE_BUFFER%' and attr1 =
&sql_id;
accept object_id
number prompt 'Enter the object id >'
select
dbms_sqlpa.report_analysis_task(
'TASK_SS_ORA12RF_1'
,'TEXT'
,'all'
,'all'
,&object_id
,null
,'COMPARE_BUFFER_GETS_INST_POKOR'
)
from dual;
98. 기타
(1) SQL TUNING
SET이 제거 되지 않을 때 제거 방법
SET LINESIZE
200
SET PAGESIZE 200
SET
NUMWIDTH 10
SET TRIMSPOOL ON
COLUMN
SQLSET_NAME FORMAT A25 HEADING 'SQLSET_NAME'
COLUMN SQLSET_OWNER FORMAT A10 HEADING
'OWNER'
COLUMN SQLSET_ID FORMAT
999999 HEADING 'SQLSET_ID'
COLUMN
ID FORMAT
999999 HEADING 'REF_ID'
COLUMN
OWNER FORMAT A10 HEADING
'REF_OWNER'
COLUMN
CREATED FORMAT A19 HEADING
'CREATED'
COLUMN DESCRIPTION FORMAT
A50 HEADING 'DESCRIPTION'
SELECT
SQLSET_NAME
, SQLSET_OWNER
, SQLSET_ID
, ID
, OWNER
, TO_CHAR(CREATED, 'YYYY-MM-DD
HH24:MI:SS') AS CREATED
,
DESCRIPTION
FROM DBA_SQLSET_REFERENCES
ORDER BY CREATED;
SQLSET_NAME
OWNER SQLSET_ID REF_ID REF_OWNER
CREATED
DESCRIPTION
------------------------- ----------
--------- ------- ---------- -------------------
--------------------------------------------------
SS_ORA12RF
SPA
3 1
SPA 2025-11-08 22:09:14 created by:
SQL Performance Analyzer - task: TASK_SS_ORA12RF_1
EXEC
DBMS_SQLTUNE.DROP_TUNING_TASK('TASK_SS_ORA12RF_1');
EXEC
DBMS_SQLTUNE.DROP_SQLSET('SS_ORA12RF','SPA');
EXEC
DBMS_SQLTUNE.REMOVE_SQLSET_REFERENCE (SQLSET_NAME =>
'SS_ORA12RF',REFERENCE_ID => 1);
99. 원상 복구
99-1. 소스 DB
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
sp
alias sp='rlwrap
sqlplus spa/oracle'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
sp
SQL*Plus: Release 12.2.0.1.0 Production on Sun Nov 9 01:19:40 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Sat Nov 08 2025 21:45:36 +09:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 -
64bit Production
SELECT *
FROM DBA_SQLSET;
EXEC
DBMS_SQLTUNE.DROP_SQLSET('SS_ORA12RF1_SP1','SPA');
EXEC
DBMS_SQLTUNE.DROP_SQLSET('SS_ORA12RF2_SP1','SPA');
EXEC
DBMS_SQLTUNE.DROP_SQLSET('SS_ORA12RF_AWR1','SPA');
EXEC
DBMS_SQLTUNE.DROP_SQLSET('SS_ORA12RF','SPA');
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$
ss
SQL*Plus: Release 12.2.0.1.0 Production on Sun Nov 9 01:20:23 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[ol7ora12rf1]<SYS@ORA12RF1>$ DROP USER SPA CASCADE;
User dropped.
Elapsed: 00:00:00.57
[ol7ora12rf1]<SYS@ORA12RF1>$ DROP TABLESPACE SPA_DATA1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
Elapsed: 00:00:07.06
[ol7ora12rf1]<SYS@ORA12RF1>$ DROP TABLESPACE SPA_IDX1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
Elapsed: 00:00:06.43
[ol7ora12rf1]<SYS@ORA12RF1>$ DROP TABLESPACE SPA_TEMP INCLUDING CONTENTS AND
DATAFILES;
Tablespace dropped.
Elapsed: 00:00:00.06
select * from
DBA_SQLSET_REFERENCES;
select * from
DBA_SQLSET_BINDS;
select * from
DBA_SQLSET_PLANS;
select * from
DBA_SQLSET_STATEMENTS;
COL SQL_ FOR A100
SELECT 'EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('''|| SQL_ID||''');' AS SQL_
FROM
(
SELECT SQL_ID
FROM
DBA_HIST_COLORED_SQL
)
;
SQL_
----------------------------------------------------------------------------------------------------
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('3gswmyntc4vb3');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('3ks0ccp8rmh7b');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('4yss4a0kvmyfj');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('6uw8tuht71zxk');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('828m0ghwzfryd');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('93uyt37jadcu3');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('9zr30g89mhym9');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('cmg9g4u3hp7qy');
EXEC
DBMS_WORKLOAD_REPOSITORY.REMOVE_COLORED_SQL('gza20dm7tmxr4');
9 rows selected.
Elapsed: 00:00:00.00
[ORA12RF1:oracle@ol7ora12rf1][/home/oracle]$ rm -rf /home/oracle/spa
99-2. 타켓 DB
SELECT *
FROM DBA_SQLSET;
EXEC DBMS_SQLTUNE.DROP_SQLSET('SS_ORA12RF','SPA');
SELECT
SQLSET_NAME
, SQLSET_OWNER
, SQLSET_ID
, ID
, OWNER
, TO_CHAR(CREATED, 'YYYY-MM-DD
HH24:MI:SS') AS CREATED
,
DESCRIPTION
FROM DBA_SQLSET_REFERENCES
ORDER BY CREATED;
--created
by: SQL Performance Analyzer - task: TASK_SS_ORA12RF_1
EXEC
DBMS_SQLTUNE.DROP_TUNING_TASK('TASK_SS_ORA12RF_1');
EXEC
DBMS_SQLTUNE.DROP_SQLSET('SS_ORA12RF','SPA');
EXEC
DBMS_SQLTUNE.REMOVE_SQLSET_REFERENCE (SQLSET_NAME =>
'SS_ORA12RF',REFERENCE_ID => 1);
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ alias
ss
alias ss='rlwrap
sqlplus "/as sysdba"'
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$
ss
SQL*Plus: Release
19.0.0.0.0 - Production on Sun Nov 9 01:26:57 2025
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c
Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
[ol8ora19rf1]<SYS@ORA19RF1>$ DROP USER SPA CASCADE;
User dropped.
Elapsed: 00:00:00.93
[ol8ora19rf1]<SYS@ORA19RF1>$ DROP TABLESPACE SPA_DATA1 INCLUDING CONTENTS AND
DATAFILES;
Tablespace dropped.
Elapsed: 00:00:06.81
[ol8ora19rf1]<SYS@ORA19RF1>$ DROP TABLESPACE SPA_IDX1 INCLUDING CONTENTS AND
DATAFILES;
Tablespace dropped.
Elapsed: 00:00:06.58
[ol8ora19rf1]<SYS@ORA19RF1>$ DROP TABLESPACE SPA_TEMP INCLUDING CONTENTS AND
DATAFILES;
Tablespace dropped.
Elapsed: 00:00:00.20
[ORA19RF1:oracle@ol8ora19rf1][/home/oracle]$ rm -rf
/home/oracle/spa