...
Session information using or accessing the undo tablespace. (Autocommit mode, Client IP and Process id, UTRANS_TIMEOUT setting value)
Whether or not there is a change transaction using the undo realm
Whether there is a query transaction accessing the undo area
Undo usage in use by change transactions
Last SQL statement and execution time of transaction
Whether to execute SQL statement
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT RPAD(DECODE(TX.LOG_TYPE, 1, REP.REP_NAME, TX.SESSION_ID), 10) SESSION_ID -- Session ID that performed the transaction.
, RPAD(TX.ID, 20) TX_ID -- Transaction ID
, RPAD(DECODE(ST.ID, NULL, 'REP''S TX', ST.ID), 20) STATEMENT_ID -- STATEMENT ID
, RPAD(DECODE(TX.TSS_RID, 0, 'SELECT', 'UPDATE'), 7) TX_TYPE -- Transaction type. SELECT: A transaction with only query statements. UPDATE: Transaction that changed data such as DELETE, UPDATE, etc.
, RPAD(DECODE(TX.STATUS, 0, 'BEGIN', 1, 'PRECOMMIT', 2, 'COMMIT_IN_MEMORY', 3, 'COMMIT', 4, 'ABORT', 5, 'BLOCKED', 6, 'END'), 16) TX_STATUS -- Transaction status
, RPAD(DECODE(ST.EXECUTE_FLAG, NULL, 'REP''S TX', 1, 'SQL ING', 0, 'SQL END'), 10) SQL_STATUS -- SQL status
, RPAD(DECODE(TX.LOG_TYPE, 1, 'REP '||REP.PEER_IP||':'||REP.PEER_PORT, S.COMM_NAME||' PID:'||S.CLIENT_PID), 40) CLIENT_IP -- Client IP and process ID
, RPAD(DECODE(S.AUTOCOMMIT_FLAG, 1, 'ON', 0, 'OFF', NULL, 'REP''S TX'), 10) AUTOCOMMIT -- AUTOCOMMIT mode
, RPAD(DECODE(TX.LOG_TYPE, 1, 'REP''S TX', S.UTRANS_TIME_LIMIT), 15) UTRANS_TIMEOUT -- Session UTRANS_TIMEOUT setting value
, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') CURRENT_TIME -- Current time
, DECODE(ST.LAST_QUERY_START_TIME, NULL, TO_CHAR(TO_DATE('1970010109','YYYYMMDDHH') + TX.FIRST_UPDATE_TIME / (60*60*24), 'YYYY-MM-DD HH:MI:SS'), ST.LAST_QUERY_START_TIME) LAST_QUERY_START_TIME -- SQL statement start time
, RPAD(LTRIM(TXM.SYS_MIN_DISK_VIEWSCN), 10) SYS_MIN_DISK_VIEWSCN -- Minimum SCN viewing at the undo area
, DECODE(TX.DISK_VIEW_SCN, 'INFINITE( 0)', '-', LTRIM(TX.DISK_VIEW_SCN)) DISK_VIEW_SCN -- Minimum SCN seen in viewing transaction
, DECODE(TX.MIN_DISK_LOB_VIEW_SCN, 'INFINITE( 0)', '-', LTRIM(TX.MIN_DISK_LOB_VIEW_SCN)) MIN_DISK_LOB_VIEW_SCN -- Minimum SCN viewed in a transaction querying LOB data
, TO_CHAR(((UD_S.TOTAL_EXTENT_COUNT*UD_S.PAGE_COUNT_IN_EXTENT*8192)/1024), '999,999,999') 'UNDO_USED(KB)' -- Size of the undo area used in the change transaction.
, DECODE(TX.LOG_TYPE, 1, 'REMOTE_TX_ID : '||REP_TX.REMOTE_TID, ST.QUERY) QUERY -- Last query performed by a transaction accessed or using undo
FROM V$TRANSACTION TX LEFT OUTER JOIN (SELECT SESSION_ID , TX_ID , ID , TO_CHAR(TO_DATE('1970010109','YYYYMMDDHH') + (LAST_QUERY_START_TIME) / (60*60*24), 'YYYY-MM-DD HH:MI:SS') LAST_QUERY_START_TIME, EXECUTE_FLAG, UNDO_READ_PAGE , UNDO_GET_PAGE , SUBSTR(QUERY, 1, 60) QUERY
FROM V$STATEMENT
WHERE (SESSION_ID
, TX_ID
, LAST_QUERY_START_TIME) IN (SELECT SESSION_ID
, TX_ID
, MAX(LAST_QUERY_START_TIME) LAST_QUERY_START_TIME
FROM V$STATEMENT
GROUP BY SESSION_ID
, TX_ID)) ST ON TX.ID = ST.TX_ID LEFT OUTER JOIN V$SESSION S ON TX.SESSION_ID = S.ID LEFT OUTER JOIN V$REPRECEIVER_TRANSTBL REP_TX ON TX.ID = REP_TX.LOCAL_TID LEFT OUTER JOIN V$REPRECEIVER REP ON REP_TX.REP_NAME = REP.REP_NAME
LEFT OUTER JOIN V$TXSEGS TX_S ON TX.ID = TX_S.TRANS_ID LEFT OUTER JOIN V$UDSEGS UD_S ON TX_S.ID = UD_S.TXSEG_ENTRY_ID
, V$TRANSACTION_MGR TXM
WHERE 1=1
AND (TX.TSS_RID <> 0
OR ST.UNDO_READ_PAGE <> 0
OR ST.UNDO_GET_PAGE <> 0)
ORDER BY SESSION_ID
, TX_ID
, LAST_QUERY_START_TIME; |
...
| Code Block | ||
|---|---|---|
| ||
iSQL> /
SESSION_ID TX_ID STATEMENT_ID TX_TYPE TX_STATUS SQL_STATUS CLIENT_IP AUTOCOMMIT UTRANS_TIMEOUT CURRENT_TIME LAST_QUERY_START_TIME SYS_MIN_DISK_VIEWSCN DISK_VIEW_SCN MIN_DISK_LOB_VIEW_SCN UNDO_USED(KB) QUERY
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 7374720 131072 SELECT BEGIN SQL_END TCP 127.0.0.1:45173 PID:1 OFF 0 2015-03-11 12:30:25 2015-03-11 09:31:43 800783 - 800783 SELECT * FROM LOB_T
12123
5 1613185 327681 SELECT BEGIN SQL_ING TCP 127.0.0.1:44251 PID:4 ON 3600 2015-03-11 12:30:25 2015-03-11 09:33:42 800783 800783 800783 SELECT ENO, C_DATE
0252 FROM EMP
2 rows selected.
iSQL> /
SESSION_ID TX_ID STATEMENT_ID TX_TYPE TX_STATUS SQL_STATUS CLIENT_IP AUTOCOMMIT UTRANS_TIMEOUT CURRENT_TIME LAST_QUERY_START_TIME SYS_MIN_DISK_VIEWSCN DISK_VIEW_SCN MIN_DISK_LOB_VIEW_SCN UNDO_USED(KB) QUERY
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 7374720 131072 SELECT BEGIN SQL_END TCP 127.0.0.1:45173 PID:1 OFF 0 2015-03-11 12:35:12 2015-03-11 09:31:43 800783 - 800783 SELECT * FROM LOB_T
12123
1 rows selected.
iSQL> /
SESSION_ID TX_ID STATEMENT_ID TX_TYPE TX_STATUS SQL_STATUS CLIENT_IP AUTOCOMMIT UTRANS_TIMEOUT CURRENT_TIME LAST_QUERY_START_TIME SYS_MIN_DISK_VIEWSCN DISK_VIEW_SCN MIN_DISK_LOB_VIEW_SCN UNDO_USED(KB) QUERY
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9 376196 589824 SELECT BEGIN SQL END TCP 127.0.0.1:21792 PID:2 OFF 3600 2015-03-11 16:00:13 2015-03-11 15:54:19 800951 - - SELECT DEP_LOCATION FROM
20176 DEPT WHERE DNO BETWEEN '1
001' AND '
1 rows selected. |
- If TX_TYPE is SELECT, it means a transaction that has never performed a change statement.
- SELECT statements that do not contain LOB data will no longer look at the undo image after fetching data from the database.
- Transactions in Autocommit-Mode disappear from the result when the fetch is complete. (Refer to TX_ID 1613185 in Session 5. The SCN output in DISK_VIEW_SCN means that LOB data is not included.)
- In non-autocommit-mode transactions, both DISK_VIEW_SCN and MIN_DISK_LOB_VIEW_SCN are out as-after fetch is finished and until commit is executed. (TX_ID 376196 of session 9) - SELECT statements including LOB data look at the undo image until commit or rollback is executed even when fetch is terminated.
If DISK_VIEW_SCN-and SCN is output in MIN_DISK_LOB_VIEW_SCN, it means a transaction that queries LOB data. (TX_ID 7374720 of session 2)
In the case of LOB data, there is information called lob cursor. When this is open, the undo image can be found. The lob cursor is closed only by commit or rollback.
Transactions in this state can cause increased undo usage.
The following is the result showing a replication transaction using the undo area.
| Code Block | ||||
|---|---|---|---|---|
| ||||
SESSION_ID TX_ID STATEMENT_ID TX_TYPE TX_STATUS SQL_STATUS CLIENT_IP AUTOCOMMIT UTRANS_TIMEOUT CURRENT_TIME LAST_QUERY_START_TIME SYS_MIN_DISK_VIEWSCN DISK_VIEW_SCN MIN_DISK_LOB_VIEW_SCN UNDO_USED(KB) QUERY
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
REP1 4417 REP'S TX UPDATE BEGIN REP'S TX REP 192.168.1.146:55007 REP'S TX REP'S TX 2015-03-10 12:35:12 2015-03-10 11:37:40 800791 800791 800791 8,192 REMOTE_TX_ID : 58753
1 row selected. |
The IP of the server that sent the transaction with CLIENT_IP can be checked.
The name of the redundant object with SESSION_ID can be found.
The value of REP'S TX means a value that cannot be checked locally. (Section and SQL information of a redundant transaction cannot be checked locally)
The TX_ID of the remote server can be found with REMOTE_TX_ID output on QUERY.
ALTIBASE HDB 4.3.9, 5.1.5
Query for monitoring undo tablespace usage by transaction
Find and take action on transactions that are using or accessing undo with the monitoring query below.
The following information can be checked in the query result.
Session information using or accessing the undo tablespace. (Autocommit mode, Client IP and Process id, UTRANS_TIMEOUT setting value)
Whether or not there is a change transaction using the undo realm
Whether there is an a query transaction accessing the undo area
Last SQL statement and execution time of transaction
Whether to execute SQL statement
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT RPAD(DECODE(TX.LOG_TYPE, 1, REP.REP_NAME, ST.SESSION_ID), 10) SESSION_ID -- Session ID that performed the transaction.
, RPAD(TX.ID, 20) TX_ID -- Transaction ID
, RPAD(DECODE(ST.ID, NULL, 'REP''S TX', ST.ID), 20) STATEMENT_ID -- STATEMENT ID
, RPAD(DECODE(TX.TSS_RID, 0, 'SELECT', 'UPDATE'), 7) TX_TYPE -- Transaction type. SELECT: A transaction with only query statements. UPDATE: Transaction that changed data such as DELETE, UPDATE, etc.
, RPAD(DECODE(TX.STATUS, 0, 'BEGIN', 1, 'PRECOMMIT', 2, 'COMMIT_IN_MEMORY', 3, 'COMMIT', 4, 'ABORT', 5, 'BLOCKED', 6, 'END'), 16) TX_STATUS -- Transaction status
, RPAD(DECODE(ST.EXECUTE_FLAG, NULL, 'REP''S TX', 0, 'SQL_END', 1, 'SQL_ING'), 16) SQL_STATUS -- SQL statement status
, RPAD(DECODE(TX.LOG_TYPE, 1, 'REP '||REP.PEER_IP||':'||REP.PEER_PORT, S.COMM_NAME||' PID:'||S.CLIENT_PID), 40) CLIENT_IP -- Client IP and process ID
, RPAD(DECODE(S.AUTOCOMMIT_FLAG, 1, 'ON', 0, 'OFF', NULL, 'REP''S TX'), 10) AUTOCOMMIT -- AUTOCOMMIT mode
, RPAD(DECODE(TX.LOG_TYPE, 1, 'REP''S TX', S.UTRANS_TIME_LIMIT), 15) UTRANS_TIMEOUT -- Session UTRANS_TIMEOUT setting value
, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') CURRENT_TIME -- Current time
, DECODE(ST.LAST_QUERY_START_TIME, NULL, TO_CHAR(TO_DATE('1970010109','YYYYMMDDHH') + TX.FIRST_UPDATE_TIME / (60*60*24), 'YYYY-MM-DD HH:MI:SS'), ST.LAST_QUERY_START_TIME) LAST_QUERY_START_TIME -- SQL statement start time
, DECODE(TX.LOG_TYPE, 1, 'REMOTE_TX_ID : '||REP_TX.REMOTE_TID, ST.QUERY) QUERY -- Last query performed by a transaction accessed or using undo
FROM V$TRANSACTION TX LEFT OUTER JOIN (SELECT SESSION_ID , TX_ID , ID , TO_CHAR(TO_DATE('1970010109','YYYYMMDDHH') + (LAST_QUERY_START_TIME) / (60*60*24), 'YYYY-MM-DD HH:MI:SS') LAST_QUERY_START_TIME , UNDO_READ_PAGE , UNDO_GET_PAGE , EXECUTE_FLAG, SUBSTR(QUERY, 1, 60) QUERY
FROM V$STATEMENT
WHERE (SESSION_ID
, TX_ID
, LAST_QUERY_START_TIME) IN (SELECT SESSION_ID
, TX_ID
, MAX(LAST_QUERY_START_TIME) LAST_QUERY_START_TIME
FROM V$STATEMENT
GROUP BY SESSION_ID
, TX_ID)) ST ON TX.ID = ST.TX_ID LEFT OUTER JOIN V$SESSION S ON ST.SESSION_ID = S.ID LEFT OUTER JOIN V$REPRECEIVER_TRANSTBL REP_TX ON TX.ID = REP_TX.LOCAL_TID LEFT OUTER JOIN V$REPRECEIVER REP ON REP_TX.REP_NAME = REP.REP_NAME
WHERE 1=1
AND TX.STATUS <> 6
AND (TX.TSS_RID <> 0
OR ST.UNDO_READ_PAGE <> 0
OR ST.UNDO_GET_PAGE <> 0)
ORDER BY SESSION_ID
, TX_ID
, LAST_QUERY_START_TIME; |
Output result
Below is an example output showing a change transaction using undo.
| Code Block | ||
|---|---|---|
| ||
iSQL> /
SESSION_ID TX_ID STATEMENT_ID TX_TYPE TX_STATUS SQL_STATUS CLIENT_IP AUTOCOMMIT UTRANS_TIMEOUT CURRENT_TIME LAST_QUERY_START_TIME QUERY
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
453698 876975109 0 UPDATE BEGIN SQL_ING SOCKET-INET-SERVER : 127.0.0.1 OFF 0 2015-03-11 14:21:38 2015-03-11 14:07:11 UPDATE EMP SET JOIN_DATE = SYS
PID:12959 DATE
453832 34379778 0 UPDATE BEGIN SQL_END SOCKET-INET-SERVER : 127.0.0.1 OFF 0 2015-03-11 14:21:38 2015-03-11 14:06:32 UPDATE LOB_T SET C1 = 'T'
PID:13015
2 rows selected. |
If TX_TYPE is UPDATE, it means that a data change operation occurred at least once in that transaction.
If SQL_STATUS is SQL_END, it means that the SQL statement executed in the transaction has ended, but the transaction has not. (TX_ID 61397121 of session 4 in the results below)
If SQL_STATUS is SQL_ING, it means that the SQL statement executed in the transaction is being executed. (TX_ID 61397121 of session 6 in the results below)
The start time of the last SQL statement executed in the transaction can be checked with LAST_QUERY_START_TIME. The difference from the current time allows you to determine how long the transaction is running.
UTRANS_TIMEOUT means the maximum amount of time (in seconds) a change transaction can perform.
The following is the output result showing the inquiry transaction viewing the pre-change data created in the undo area in the change transaction.
| Code Block | ||
|---|---|---|
| ||
iSQL> /
SESSION_ID TX_ID STATEMENT_ID TX_TYPE TX_STATUS SQL_STATUS CLIENT_IP AUTOCOMMIT UTRANS_TIMEOUT CURRENT_TIME LAST_QUERY_START_TIME QUERY
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
462187 34470914 0 SELECT BEGIN SQL_ING SOCKET-INET-SERVER : 127.0.0.1 OFF 0 2015-03-11 16:31:28 2015-03-11 16:28:27 SELECT JOIN_DATE FROM EMP
PID:16067
1 row selected. |
If TX_TYPE is SELECT, it means a transaction that has never performed a change statement.
The following is the result showing a replication transaction using the undo area.
| Code Block | ||
|---|---|---|
| ||
iSQL> / SESSION_ID TX_ID STATEMENT_ID TX_TYPE TX_STATUS SQL_STATUS CLIENT_IP AUTOCOMMIT UTRANS_TIMEOUT CURRENT_TIME LAST_QUERY_START_TIME QUERY ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- REP 16617476 REP'S TX UPDATE BLOCKED REP'S TX REP 192.168.1.145:27206 REP'S TX REP'S TX 2015-03-11 14:21:38 2015-03-11 14:21:34 REMOTE_TX_ID : 3875 1 rows selected. |
The IP of the server that sent the transaction can be checked with CLIENT_IP.
The name of the replication object can be checked with SESSION_ID.
The value of REP'S TX means a value that cannot be checked locally. (Section and SQL information of a redundant transaction cannot be checked locally)
The TX_ID of the remote server can be checked with REMOTE_TX_ID printed on QUERY.
Solution
Increasing the size of the undo tablespace
The data file must be added for the undo tablespace or the size of an existing data file must be expanded.
One of the things the user can do when the undo usage is increasing.
Adding data file
After checking the existing data file name, add the data file according to the existing naming rules.
| Code Block | ||||
|---|---|---|---|---|
| ||||
SELECT RPAD(T.NAME, 20) SPACE_NAME
, RPAD(DECODE(D.AUTOEXTEND, 0, 'OFF', 1, 'ON'), 10) AS AUTOEXTEND
, DECODE(D.MAXSIZE, 0, ROUND((D.CURRSIZE*T.PAGE_SIZE)/1024/1024), ROUND((D.MAXSIZE*T.PAGE_SIZE)/1024/1024)) AS 'MAXSIZE(KB)'
, D.NAME DATAFILE
, DECODE(D.AUTOEXTEND, 0, 'OFF', 1, 'ON') 'AUTOEXTEND'
FROM V$TABLESPACES T, V$DATAFILES D
WHERE T.ID = D.SPACEID AND T.TYPE = 7
ORDER BY 1, 2 ;
-- Example of execution result
iSQL> /
SPACE_NAME AUTOEXTEND MAXSIZE(KB) DATAFILE AUTOEXTEND
------------------------------------------------------------------------------------------------------------------------------
SYS_TBS_DISK_UNDO ON 2048 /data/heejung.lee/63138/dbs/undo001.dbf ON
1 row selected. |
| Panel | ||||
|---|---|---|---|---|
| ||||
ALTER TABLESPACE SYS_TBS_DISK_UNDO ADD DATAFILE 'datafile_name' AUTOEXTEND ON NEXT 1M MAXSIZE 2G; |
| Panel | ||||
|---|---|---|---|---|
| ||||
ALTER TABLESPACE SYS_TBS_DISK_UNDO ADD DATAFILE 'datafile_name' SIZE 2G ; iSQL> ALTER TABLESPACE SYS_TBS_DISK_UNDO ADD DATAFILE 'undo002.dbf' SIZE 2G; |
Extending the size of an existing data file
After checking the name and maximum size of the existing data file, set the maximum size larger than the current value.
Check the data file name and maximum size using the query above.
| Panel | ||||
|---|---|---|---|---|
| ||||
ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE 'datafile_name' AUTOEXTEND OFF; ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE 'datafile_name' AUTOEXTEND ON NEXT 1M MAXSIZE 4G; -- Example ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE 'undo001.dbf' AUTOEXTEND OFF; ALTER TABLESPACE SYS_TBS_DISK_UNDO ALTER DATAFILE 'undo001.dbf' AUTOEXTEND ON NEXT 1M MAXSIZE 4G; |
...