"트랜젝션 중인 데이터 조회 V$transaction"의 두 판 사이의 차이
DB CAFE
(→V$TRANSACTION) |
|||
1번째 줄: | 1번째 줄: | ||
=== V$TRANSACTION === | === V$TRANSACTION === | ||
− | + | === 트랜잭션 목록 정보 === | |
− | V$TRANSACTION lists the active transactions in the system. | + | * V$TRANSACTION lists the active transactions in the system. |
<source lang=sql> | <source lang=sql> | ||
Column Datatype Description | Column Datatype Description | ||
51번째 줄: | 51번째 줄: | ||
PTX_XID RAW(8) Parent transaction XID | PTX_XID RAW(8) Parent transaction XID | ||
</source> | </source> | ||
+ | |||
+ | |||
+ | v$transaction - used_urec , used_ublk | ||
+ | lovewj8318 2014. 1. 30. 19:27 | ||
+ | 5 - 6 minutes | ||
+ | |||
+ | The v$transaction view lists the active transactions in the system. | ||
+ | |||
+ | Oracle will insert an entry in v$transaction for each active transaction in the database. When the transaction is complete the v$transaction row will disappear. | ||
+ | |||
+ | The v$transaction columns used_urec and v$transaction used_ublk show the number of undo records held by an active transaction. This is useful for monitoring long-running DML. | ||
+ | |||
+ | The start_time column in the view v$transaction, the column shows only the starting time of the whole transaction. | ||
+ | |||
+ | ==== 트랜젝션 데이터 건수/블럭수 조회 ==== | ||
+ | From this v$transaction information we can compute the time required to roll-back a transaction with a query such as this, which joins v$session into v$transaction: | ||
+ | <source lang=sql> | ||
+ | select | ||
+ | t1.sid, | ||
+ | a.username, | ||
+ | t2.xidusn, | ||
+ | t2.used_urec, | ||
+ | t2.used_ublk | ||
+ | from | ||
+ | v$session t1, | ||
+ | v$transaction t2 | ||
+ | where | ||
+ | t1.saddr = t2.ses_addr; | ||
+ | </source> | ||
+ | |||
+ | ==== 트랜젝션 처리중인 명령어 조회 ==== | ||
+ | Here is another script example using v$transaction which is extremely valuable for Oracle administrators who need to very quickly find out what is going on within their Oracle system. | ||
+ | <source lang=sql> | ||
+ | select | ||
+ | substr(s.username,1,18) username, | ||
+ | substr(s.program,1,15) program, | ||
+ | decode(s.command, | ||
+ | 0,'No Command', | ||
+ | 1,'Create Table', | ||
+ | 2,'Insert', | ||
+ | 3,'Select', | ||
+ | 6,'Update', | ||
+ | 7,'Delete', | ||
+ | 9,'Create Index', | ||
+ | 15,'Alter Table', | ||
+ | 21,'Create View', | ||
+ | 23,'Validate Index', | ||
+ | 35,'Alter Database', | ||
+ | 39,'Create Tablespace', | ||
+ | 41,'Drop Tablespace', | ||
+ | 40,'Alter Tablespace', | ||
+ | 53,'Drop User', | ||
+ | 62,'Analyze Table', | ||
+ | 63,'Analyze Index', | ||
+ | s.command||': Other') command | ||
+ | from | ||
+ | v$session s, | ||
+ | v$process p, | ||
+ | v$transaction t, | ||
+ | v$rollstat r, | ||
+ | v$rollname n | ||
+ | where s.paddr = p.addr | ||
+ | and s.taddr = t.addr (+) | ||
+ | and t.xidusn = r.usn (+) | ||
+ | and r.usn = n.usn (+) | ||
+ | order by 1; | ||
+ | </source> | ||
+ | <source lang=sql> | ||
+ | USERNAME PROGRAM COMMAND | ||
+ | ------------------ --------------- ---------------- | ||
+ | APPS f45runm@corp-hp Select | ||
+ | APPS S:\ORANT\BIN\F5 Insert | ||
+ | APPS S:\ORANT\BIN\R3 No Command | ||
+ | </source> | ||
+ | |||
+ | =================================================== | ||
+ | |||
+ | ==== 사용자별 Rollback Segment 사용현황 ==== | ||
+ | <source lang=sql> | ||
+ | select s.username, rn.name, rs.curext | ||
+ | ,rs.curblk, t.used_ublk, t.used_urec | ||
+ | from v$transaction t | ||
+ | ,v$session s | ||
+ | ,v$rollname rn | ||
+ | ,v$rollstat rs | ||
+ | where t.addr = s.taddr | ||
+ | and t.xidusn = rn.usn | ||
+ | and rn.usn = rs.usn | ||
+ | </source> | ||
+ | |||
+ | * APPS 유저가 ROLL03 롤백 세그먼트에서 3개의 블럭(USED_UBLK)에 94개 레코드(USED_UREC)를 저장하고 있음. | ||
+ | |||
+ | <source lang=sql> | ||
+ | USERNAME NAME CUREXT CURBLK USED_UBLK USED_UREC | ||
+ | --------------- --------------- ---------- ---------- ---------- ---------- | ||
+ | APPS ROLL03 1 139 3 94 | ||
+ | </source> | ||
+ | |||
+ | |||
+ | ================================================== | ||
+ | |||
+ | Q : how much undo do you generate when you insert or update a row? | ||
+ | |||
+ | A : it depends on what you’re actually doing, the amount of change you introduce, and the number of indexes affected. The key is to keep an eye on the used_urec and used_ublk colummns from v$transactions. | ||
+ | |||
+ | Consider the example of inserting data: | ||
+ | |||
+ | when you insert a row, you typically generate one undo record (used_urec) for the row, but when you insert many rows using an array insert Oracle has an optimisation mechanism on undo record creation that allows it to create one used_urec to cover all the changes you have made simultaneously to an individual block - so used_urec could be much smaller than the number of rows processed. | ||
+ | |||
+ | However, if you have indexes in place and are doing normal index maintenance on import, then each table row would require each index to be updated, so you would go back to one used_urec per table row plus one used_urec per index maintained per table row. | ||
+ | |||
+ | So, when you look at the “big picture” there’s no obvious correlation between rows inserted and undo generated — until you look at the fine detail of exactly what you’re doing, and whether any optimisations apply. (The details of the optimisation strategies available vary with the chosen insert mechanisms and with version of Oracle) |
2023년 11월 14일 (화) 18:51 판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 V$TRANSACTION[편집]
2 트랜잭션 목록 정보[편집]
- V$TRANSACTION lists the active transactions in the system.
Column Datatype Description
ADDR RAW(4 | 8) Address of the transaction state object
XIDUSN NUMBER Undo segment number
XIDSLOT NUMBER Slot number
XIDSQN NUMBER Sequence number
UBAFIL NUMBER Undo block address (UBA) filenum
UBABLK NUMBER UBA block number
UBASQN NUMBER UBA sequence number
UBAREC NUMBER UBA record number
STATUS VARCHAR2(16) Status
START_TIME VARCHAR2(20) Start time (wall clock)
START_SCNB NUMBER Start system change number (SCN) base
START_SCNW NUMBER Start SCN wrap
START_UEXT NUMBER Start extent number
START_UBAFIL NUMBER Start UBA file number
START_UBABLK NUMBER Start UBA block number
START_UBASQN NUMBER Start UBA sequence number
START_UBAREC NUMBER Start UBA record number
SES_ADDR RAW(4 | 8) User session object address
FLAG NUMBER Flag
SPACE VARCHAR2(3) YES if a space transaction
RECURSIVE VARCHAR2(3) YES if a recursive transaction
NOUNDO VARCHAR2(3) YES if a no undo transaction
PTX VARCHAR 2(3) YES if parallel transaction
NAME VARCHAR2(256) Name of a named transaction
PRV_XIDUSN NUMBER Previous transaction undo segment number
PRV_XIDSLT NUMBER Previous transaction slot number
PRV_XIDSQN NUMBER Previous transaction sequence number
PTX_XIDUSN NUMBER Rollback segment number of the parent XID
PTX_XIDSLT NUMBER Slot number of the parent XID
PTX_XIDSQN NUMBER Sequence number of the parent XID
DSCN-B NUMBER This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in DSCN_BASE.
DSCN-W NUMBER This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in DSCN_WRAP.
USED_UBLK NUMBER Number of undo blocks used
USED_UREC NUMBER Number of undo records used
LOG_IO NUMBER Logical I/O
PHY_IO NUMBER Physical I/O
CR_GET NUMBER Consistent gets
CR_CHANGE NUMBER Consistent changes
START_DATE DATE Start time (wall clock)
DSCN_BASE NUMBER Dependent SCN base
DSCN_WRAP NUMBER Dependent SCN wrap
START_SCN NUMBER Start SCN
DEPENDENT_SCN NUMBER Dependent SCN
XID RAW(8) Transaction XID
PRV_XID RAW(8) Previous transaction XID
PTX_XID RAW(8) Parent transaction XID
v$transaction - used_urec , used_ublk
lovewj8318 2014. 1. 30. 19:27
5 - 6 minutes
The v$transaction view lists the active transactions in the system.
Oracle will insert an entry in v$transaction for each active transaction in the database. When the transaction is complete the v$transaction row will disappear.
The v$transaction columns used_urec and v$transaction used_ublk show the number of undo records held by an active transaction. This is useful for monitoring long-running DML.
The start_time column in the view v$transaction, the column shows only the starting time of the whole transaction.
2.1 트랜젝션 데이터 건수/블럭수 조회[편집]
From this v$transaction information we can compute the time required to roll-back a transaction with a query such as this, which joins v$session into v$transaction:
select
t1.sid,
a.username,
t2.xidusn,
t2.used_urec,
t2.used_ublk
from
v$session t1,
v$transaction t2
where
t1.saddr = t2.ses_addr;
2.2 트랜젝션 처리중인 명령어 조회[편집]
Here is another script example using v$transaction which is extremely valuable for Oracle administrators who need to very quickly find out what is going on within their Oracle system.
select
substr(s.username,1,18) username,
substr(s.program,1,15) program,
decode(s.command,
0,'No Command',
1,'Create Table',
2,'Insert',
3,'Select',
6,'Update',
7,'Delete',
9,'Create Index',
15,'Alter Table',
21,'Create View',
23,'Validate Index',
35,'Alter Database',
39,'Create Tablespace',
41,'Drop Tablespace',
40,'Alter Tablespace',
53,'Drop User',
62,'Analyze Table',
63,'Analyze Index',
s.command||': Other') command
from
v$session s,
v$process p,
v$transaction t,
v$rollstat r,
v$rollname n
where s.paddr = p.addr
and s.taddr = t.addr (+)
and t.xidusn = r.usn (+)
and r.usn = n.usn (+)
order by 1;
USERNAME PROGRAM COMMAND
------------------ --------------- ----------------
APPS f45runm@corp-hp Select
APPS S:\ORANT\BIN\F5 Insert
APPS S:\ORANT\BIN\R3 No Command
===================================================
2.3 사용자별 Rollback Segment 사용현황[편집]
select s.username, rn.name, rs.curext
,rs.curblk, t.used_ublk, t.used_urec
from v$transaction t
,v$session s
,v$rollname rn
,v$rollstat rs
where t.addr = s.taddr
and t.xidusn = rn.usn
and rn.usn = rs.usn
- APPS 유저가 ROLL03 롤백 세그먼트에서 3개의 블럭(USED_UBLK)에 94개 레코드(USED_UREC)를 저장하고 있음.
USERNAME NAME CUREXT CURBLK USED_UBLK USED_UREC
--------------- --------------- ---------- ---------- ---------- ----------
APPS ROLL03 1 139 3 94
2.3.1 ======================================[편집]
Q : how much undo do you generate when you insert or update a row?
A : it depends on what you’re actually doing, the amount of change you introduce, and the number of indexes affected. The key is to keep an eye on the used_urec and used_ublk colummns from v$transactions.
Consider the example of inserting data:
when you insert a row, you typically generate one undo record (used_urec) for the row, but when you insert many rows using an array insert Oracle has an optimisation mechanism on undo record creation that allows it to create one used_urec to cover all the changes you have made simultaneously to an individual block - so used_urec could be much smaller than the number of rows processed.
However, if you have indexes in place and are doing normal index maintenance on import, then each table row would require each index to be updated, so you would go back to one used_urec per table row plus one used_urec per index maintained per table row.
So, when you look at the “big picture” there’s no obvious correlation between rows inserted and undo generated — until you look at the fine detail of exactly what you’re doing, and whether any optimisations apply. (The details of the optimisation strategies available vary with the chosen insert mechanisms and with version of Oracle)