Oracle 23c AI introduces new V$ views to diagnose Data Pump performance issues. These views help monitor and troubleshoot Data Pump activities by providing detailed insights into job execution, resource usage, and potential bottlenecks.
Key V$ Views:
- (G)V$DATAPUMP_PROCESS_INFO: Provides information about active Data Pump processes
- (G)V$DATAPUMP_PROCESSWAIT_INFO: Details the waits encountered by Data Pump processes
- (G)V$DATAPUMP_SESSIONWAIT_INFO: Provides information about session waits related to Data Pump operations.
These views enable administrators to efficiently manage and optimize Data Pump tasks. The outputs will be needed, along with the DataPump job logs, traces and other job specific info for diagnostic purpose.
(G)V$DATAPUMP_PROCESS_INFO
SQL> desc gv$datapump_process_info;
Name Null? Type
---------------------------
INST_ID NUMBER
CUR_DATE VARCHAR2(19)
PROGRAM VARCHAR2(84)
SESSIONID NUMBER
STATUS VARCHAR2(8)
USERNAME VARCHAR2(128)
JOBNAME VARCHAR2(128)
SPID VARCHAR2(24)
SERIALNUMBER NUMBER
PROCESSID NUMBER
CON_ID NUMBER
Sample output:
select * from v$datapump_process_info;
CUR_DATE PROGRAM SESSIONID STATUS USERNAME JOBNAME SPID SERIALNUMBER PROCESSID CON_ID
------------------- -------------------------------------- --------- -------- ---------- ------------------
2023-01-09 13:56:07 ude@orcl (TNS V1-V3) 42 ACTIVE SYSTEM SYS_EXPORT_FULL_01 3891480 11829 70 0
2023-01-09 13:56:07 oracle@orcl (DW00) 48 ACTIVE SYSTEM SYS_EXPORT_FULL_01 3891500 36244 82 0
2023-01-09 13:56:07 oracle@orcl (DM00) 149 ACTIVE SYSTEM SYS_EXPORT_FULL_01 3891494 42966 79 0(G)V$DATAPUMP_PROCESSWAIT_INFO
SQL> desc gv$datapump_processwait_info;
Name Null? Type
-------------------------------------
INST_ID NUMBER
WAITING_SESSION NUMBER
HOLDING_SESSION NUMBER
SERIAL_NUMBER NUMBER
EVENT VARCHAR2(64)
PROGRAM_WAITSESSION VARCHAR2(84)
PROGRAM_HOLDINGDSESSION VARCHAR2(84)
MODULE_WAITSESSION VARCHAR2(64)
MODULE_HOLDINGSESSION VARCHAR2(64)
DATAPUMP_LOCKID NUMBER
CON_ID NUMBER
Sample output:
SQL> select * from gv$datapump_processwait_info;
INST_ID WAITING_SESSION HOLDING_SESSION SERIAL_NUMBER EVENT PROGRAM_WAITSESSION PROGRAM_HOLDINGDSESSION MODULE_WAITSESSION MODULE_HOLDINGSESSION DATAPUMP_LOCKID CON_ID
-------------------------------------------------------------------------------------------------------------------------------------------------------------
1 174 57 40525 enq: TM - contention oracle@orcl (DM00) oracle@orcl (DW00) Data Pump Master Data Pump Worker 79473 1(G)V$DATAPUMP_SESSIONWAIT_INFO
SQL> desc gv$datapump_sessionwait_info;
Name Null? Type
------------------------------------
INST_ID NUMBER
WAITING_SESSION NUMBER
SERIAL_NUMBER NUMBER
SEQ_NUMBER NUMBER
EVENT VARCHAR2(64)
DP_WAITTIME NUMBER
DP_SECONDS_IN_WAIT NUMBER
DP_STATE_IN_WAIT VARCHAR2(19)
DP_P1TEXT VARCHAR2(64)
DP_P1 NUMBER
DP_P2TEXT VARCHAR2(64)
DP_P2 NUMBER
DP_P3TEXT VARCHAR2(64)
DP_P3 NUMBER
CON_ID NUMBER
Sample output:
SQL> select * from gv$datapump_sessionwait_info;
INST_ID WAITING_SESSION SERIAL_NUMBER SEQ_NUMBER EVENT DP_WAITTIME DP_SECONDS_IN_WAIT DP_STATE_IN_WAIT DP_P1TEXT DP_P1 DP_P2TEXT DP_P2 DP_P3TEXT DP_P3 CON_ID
--------------------------------------------------------------------------------------------------------------------------------------------
1 46 65244 1319 enq: TM - contention 0 8086 WAITING name|mode 1414332419 object # 80996 table/partition 0
Leave a Reply