Oracle Partition Table Shrink 및 Lock 확인 테스트

Oracle Partition TABLE 기본 구성

Oracle Partition TABLE 기본 구성 #테이블 스페이스 생성 CREATE TABLESPACE TEST DATAFILE '+ASM' SIZE 1G AUTOEXTEND ON; #유저생성 CREATE USER PCW IDENTIFIED BY 'oracle1' DEFAULT TABLESPACE TABLESPA..

datapyo.tistory.com

시나리오

SESSION 1 DML 발생1
SESSION 2 SHRINK 작업 수행
SESSION 3 DML 발생2
SESSION 4 LOCK SESSION 확인

SESSION 1번 수행 중 SESSION 2번에서 SHRINK 수행 하며 SHRINK 작업중 SESSION 2번에서 DML 발생에 대한 경과를
SESSION 4번을 통해 확인합니다.

#SESSION 1

DML 발생1

begin /*+ 단편화 발생을 위한 테이블 DELETE UPDATE */
for i in 1 .. 100000
loop

UPDATE PCW.ORDERS_PT
SET
ORDER_MODE = ORDER_MODE || dbms_random.string('W', 1),
CUSTOMER_ID = CUSTOMER_ID ||dbms_random.string('C', 1),
EMPLOYEE_ID = EMPLOYEE_ID ||dbms_random.string('P', 1)
WHERE ORDER_DATE < TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN');
COMMIT;
DELETE from PCW.ORDERS_PT WHERE ORDER_ID = (SELECT TRUNC(DBMS_RANDOM.VALUE(1,1200000)) FROM DUAL);
COMMIT;
end loop;
end;
/

#SESSION 2

Shrink

ALTER TABLE PCW.ORDERS_PT ENABLE ROW MOVEMENT;
ALTER TABLE PCW.ORDERS_PT MODIFY PARTITION PCW_202101 SHRINK SPACE CASCADE;
ALTER TABLE PCW.ORDERS_PT MODIFY PARTITION PCW_202102 SHRINK SPACE CASCADE;
ALTER TABLE PCW.ORDERS_PT MODIFY PARTITION PCW_202103 SHRINK SPACE CASCADE;
ALTER TABLE PCW.ORDERS_PT MODIFY PARTITION PCW_202104 SHRINK SPACE CASCADE;
ALTER TABLE PCW.ORDERS_PT MODIFY PARTITION PCW_202105 SHRINK SPACE CASCADE;
ALTER TABLE PCW.ORDERS_PT MODIFY PARTITION PCW_202106 SHRINK SPACE CASCADE;
ALTER TABLE PCW.ORDERS_PT MODIFY PARTITION PCW_202107 SHRINK SPACE CASCADE;
ALTER TABLE PCW.ORDERS_PT MODIFY PARTITION PCW_202108 SHRINK SPACE CASCADE;
ALTER TABLE PCW.ORDERS_PT MODIFY PARTITION PCW_202109 SHRINK SPACE CASCADE;
ALTER TABLE PCW.ORDERS_PT MODIFY PARTITION PCW_202110 SHRINK SPACE CASCADE;
ALTER TABLE PCW.ORDERS_PT MODIFY PARTITION PCW_202111 SHRINK SPACE CASCADE;
ALTER TABLE PCW.ORDERS_PT DISABLE ROW MOVEMENT;

#SESSION 3

DML 발생2

#SESSION 4

LOCK Session 확인

SELECT "TIME",
"BLOCKED SID",
"WAIT CLASS",
"WAIT TIME",
"BLOCK OBJ",
"EVENT" ,
P1,
P2,
P3,
"USER",
"SQL CMD",
SQL_ID,
"TOP SQL_ID",
MODULE,
"BLOCKING SID" ,
MAX("WAIT TIME") KEEP(DENSE_RANK FIRST
ORDER BY "TIME", "BLOCKED SID") AS MAX_WAIT
FROM (SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI:SS') "TIME",
INST_ID || ',' || SESSION_ID || ',' || SESSION_SERIAL# "BLOCKED SID",
V1.WAIT_CLASS "WAIT CLASS",
(SELECT TO_CHAR(ROUND(NVL(LAST_CALL_ET, 0)/60), '99990') || ':' || TRIM(TO_CHAR(MOD(NVL(LAST_CALL_ET, 0), 60), '09'))
FROM GV$SESSION X
WHERE X.INST_ID = V1.INST_ID
AND X.SID = V1.SESSION_ID ) "WAIT TIME",
(SELECT OWNER || '.' || OBJECT_NAME || ' [' || DECODE(OBJECT_TYPE, 'TABLE', 'T', 'TABLE PARTITION', 'T-P', 'TABLE SUBPARTITION', 'T-S', 'INDEX', 'I', 'INDEX PARTITION', 'I-P', OBJECT_TYPE) || ']'
FROM DBA_OBJECTS
WHERE OBJECT_ID = CURRENT_OBJ#) "BLOCK OBJ",
V1.EVENT "EVENT" ,
V1.P1,
V1.P2,
V1.P3,
(SELECT USERNAME
FROM DBA_USERS B
WHERE V1.USER_ID = B.USER_ID
AND ROWNUM <= 1) "USER",
(SELECT NAME
FROM AUDIT_ACTIONS
WHERE ACTION = SQL_OPCODE) "SQL CMD",
SQL_ID ,
TOP_LEVEL_SQL_ID "TOP SQL_ID",
MODULE,
V1.BLOCKING_INST_ID ||',' || V1.BLOCKING_SESSION || ',' || V1.BLOCKING_SESSION_SERIAL# "BLOCKING SID"
FROM (SELECT RANK() OVER (PARTITION BY INST_ID, SESSION_ID
ORDER BY INST_ID, SESSION_ID, SAMPLE_ID DESC) RNK,
SAMPLE_ID,
SAMPLE_TIME,
INST_ID,
SESSION_ID,
SESSION_SERIAL#,
SESSION_TYPE,
USER_ID,
SQL_OPCODE,
SQL_ID,
TOP_LEVEL_SQL_ID,
.

SESSION_STATE,
MODULE,
ACTION,
PROGRAM,
BLOCKING_INST_ID ,
BLOCKING_SESSION ,
BLOCKING_SESSION_SERIAL#,
BLOCKING_SESSION_STATUS,
CURRENT_OBJ#,
EVENT,
P1,
P1TEXT,
P2,
P2TEXT,
P3,
P3TEXT,
WAIT_CLASS
FROM GV$ACTIVE_SESSION_HISTORY A
WHERE 1=1
AND SAMPLE_TIME < SYSDATE -- 시간 조절 가능
AND BLOCKING_INST_ID IS NOT NULL
AND BLOCKING_SESSION IS NOT NULL
AND WAIT_CLASS IN ('Application') ) V1
WHERE RNK <= 1 )
GROUP BY "TIME", "BLOCKED SID", "WAIT CLASS", "WAIT TIME", "BLOCK OBJ", "EVENT" , P1, P2, P3, "USER", "SQL CMD", SQL_ID, "TOP SQL_ID", MODULE, "BLOCKING SID"
ORDER BY 3, 2

#결과

TIME

BLOCKED SID

WAIT CLASS

WAIT TIME

BLOCK OBJ

EVENT

USER

SQL CMD

SQL_ID

TOP SQL_ID

MODULE

BLOCKING SID

MAX_WAIT

14:53:10

1,257,52267

Application

21:05

PCW.ORDERS_PT [T-P]

enq: TX - row lock contention

1415053318

131093

17694

SYSTEM

UPDATE

f44w44yavqskf

dqd20khvrcqnw

Orange for ORACLE DBA

1,505,2489

21:05

14:53:12

1,505,2489

Application

21:04

PCW.ORDERS_PT [T-P]

enq: TX - row lock contention

1415053318

196621

17889

SYSTEM

UPDATE

cphj9ngw7rbkt

8y8hgw0gphf3c

Orange for ORACLE DBA

1,257,52267

21:04

14:53:04

1,629,63874

Application

0:00

PCW.ORDERS_PT [T-P]

enq: TM - contention

1414332422

141081

SYSTEM

ALTER TABLE

56n5264k4zvwy

Orange for ORACLE DBA

1,257,52267

0:00

1,257,52267 영향으로 인해 다른 2개의 세션이 대기한것으로 판단

'Oracle > Oracle Admin' 카테고리의 다른 글

Oracle Partition TABLE 기본 구성 (0)	2022.02.16
Oracle Controlfile 재 생성 오라클 구성 변경 (0)	2021.12.20
Oracle Partition Table SPLIT (파티션 스플릿) (0)	2021.12.17
Oracle Partition Table Fragmentation(단편화 비교) (0)	2021.11.22
Oracle Partition Table Index 기본 구성기준 및 SQL 실행계획 (0)	2021.11.17

기록과 습관 그리고...

Oracle Partition Table Shrink 및 Lock 확인 테스트

'Oracle > Oracle Admin' 카테고리의 다른 글

댓글

티스토리툴바

Oracle Partition Table Shrink 및 Lock 확인 테스트

'Oracle > Oracle Admin' 카테고리의 다른 글

관련글

댓글

티스토리툴바