Oracle DBMS_PQDIAG
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose Parallel Query Diagnostic Utilities
AUTHID CURRENT_USER
Data Types TYPE bbox_type IS RECORD (
INST_ID    NUMBER,
SLVPID     NUMBER,
QC_INST_ID NUMBER,
QCSID      NUMBER,
TRACE      VARCHAR2(2048));

TYPE bbox_type_local IS RECORD (
SLVPID     NUMBER,
QC_INST_ID NUMBER,
QCSID      NUMBER,
TRACE      VARCHAR2(2048));

TYPE qerpxCnt_type IS RECORD (
INST_ID   NUMBER,
KXFBBSET  NUMBER,
KXFBBPNAM VARCHAR2(10),
COUNT     NUMBER);

TYPE qcslv_type IS RECORD (
QCINSTNO  NUMBER,
QCSID     NUMBER,
QCPROC    VARCHAR2(24),
SLVSID    NUMBER,
SLVPROC   VARCHAR2(24),
SLVINSTNO NUMBER,
SLVNO     NUMBER,
PNAME     VARCHAR2(4),
SQL_ID    VARCHAR2(13),
COMMAND   NUMBER);

TYPE qcslv_type_local IS RECORD (
QCINSTNO   NUMBER,
QCSID      NUMBER,
QCPROC     VARCHAR2(24),
SLVSID     NUMBER,
SLVPROC    VARCHAR2(24),
SQL_ID     VARCHAR2(13),
COMMAND    NUMBER,
DEGREE     NUMBER,
REQ_DEGREE NUMBER);

TYPE bbox_col IS TABLE OF bbox_type;
TYPE bbox_col_local IS TABLE OF bbox_type_local;
TYPE qerpxCnt_col IS TABLE OF qerpxCnt_type;
TYPE qcslv_col IS TABLE OF qcslv_type;
TYPE qcslv_col_local IS TABLE OF qcslv_type_local;
Dependencies
 DBMS_SYS_ERROR GV$PX_PROCESS V$PX_PROCESS
DUAL GV$SESSION V$PX_SESSION
GV$ENQUEUE_LOCK V$PQ_TQSTAT V$SESSION
Documented No
First Available 20c
Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, READ_ONLY);
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmspqdiag.sql
Subprograms
 
CNT_QERPXTRC (new 20c)
Count qerpx* traces after slave starts in black box trace dbms_pqdiag.cnt_qerPXtrc(last_exe IN NUMBER DEFAULT 1)
RETURN sys.dbms_pqdiag.qerpxcnt_col PIPELINED;

PARAMETERS:
-- last_exe - 1 = only show traces of the last executed query,
--            0 = show all traces in blackbox
SELECT * FROM TABLE(dbms_pqdiag.cnt_qerpxTrc(last_exe=>0));

 INST_ID   KXFBBSET KXFBBPNAM  COUNT
-------- ---------- ---------- -----
       1          1 P000          16
       1          1 P001          15
       1          2 P002          22
       1          2 P003          21
 
DISPLAY_TRACE (new 20c)
Displays the black box trace of a query execution after the Parallel Query slave starts dbms_pqdiag.display_trace(
topnrows IN NUMBER DEFAULT 0,
pxstate  IN NUMBER DEFAULT 0,
last_exe IN NUMBER DEFAULT 1)
RETURN sys.dbms_pqdiag.bbox_col PIPELINED;

PARAMETERS:
-- topNrows - only display the top N rows for each slave, default displays all rows
-- pxState - slave process state
-- - 0 = not specified
-- - 1 = available
-- - 2 = in use
-- last_exe - 1 = only show traces of the last executed query,
--            0 = show all traces in blackbox
col trace format a95

SELECT slvpid, qcsid, trace
FROM TABLE(dbms_pqdiag.display_trace(last_exe=>0, pxState=>0))
WHERE rownum < 11;

 SLVPID QCSID TRACE
------- ----- ----------------------------------------------------------------------------------------
  19446   181 -> fetching pxid:1 rwsrid:1 dfo:1
  19446   181 done pxid:1 rwsrid:1
  19446   181 In signal-handler after catching 10387
  19446   181 kxfx slave finish
  19446   181 Exiting parallel client parallel query execution(6) on error=10387
  19446   181 Sending session slave statistics to QC qref=0x640544e0
  19446   181 q=0x926262f8 qser=3405825 qinc=1 action=0x1 flg=0x0
  19446   181 del qref=0x85bbb580 q=0x926262f8 qflg=0 qrser=3405825 qrseq=1 server=1.3 action=1 sopr=0
  19446   181 del qref=0x9261d828 q=0x926262f8 qflg=0 qrser=3405825 qrseq=1 server=1.2 action=1 sopr=0
  19446   181 del qref=0x640544e0 q=0x926262f8 qflg=0 qrser=3405825 qrseq=3 server=1.-1 action=1 sopr=0
 
DISPLAY_TRACE_LOCAL (new 20c)
Displays the black box trace of a query execution after the Parallel Query slave starts dbms_pqdiag.display_trace_local(
topnrows IN NUMBER DEFAULT 0,
pxstate  IN NUMBER DEFAULT 0,
last_exe IN NUMBER DEFAULT 1)
RETURN sys.dbms_pqdiag.bbox_col_local PIPELINED;

-- parameters are the same as DISPLAY_TRACE
col trace format a95

SELECT slvpid, qcsid, trace
FROM TABLE(dbms_pqdiag.display_trace(last_exe=>0, pxState=>0))
WHERE rownum < 11;

SELECT * FROM TABLE(dbms_pqdiag.display_trace_local(last_exe=>0, pxState=>2));

 SLVPID QCSID TRACE
------- ----- ---------------------------------------------------------------------------------------
  19446   181 -> fetching pxid:1 rwsrid:1 dfo:1
  19446   181 done pxid:1 rwsrid:1
  19446   181 In signal-handler after catching 10387
  19446   181 kxfx slave finish
  19446   181 Exiting parallel client parallel query execution(6) on error=10387
  19446   181 Sending session slave statistics to QC qref=0x640544e0
  19446   181 q=0x926262f8 qser=3405825 qinc=1 action=0x1 flg=0x0
  19446   181 del qref=0x85bbb580 q=0x926262f8 qflg=0 qrser=3405825 qrseq=1 server=1.3 action=1 sopr=0
  19446   181 del qref=0x9261d828 q=0x926262f8 qflg=0 qrser=3405825 qrseq=1 server=1.2 action=1 sopr=0
  19446   181 del qref=0x640544e0 q=0x926262f8 qflg=0 qrser=3405825 qrseq=3 server=1.-1 action=1 sopr=0
 
GETPROCESSNAME (new 20c)
Returns the process name associated with the identified process dbms_pqdiag.getProcessName(id IN NUMBER) RETURN VARCHAR2;
SELECT pid
FROM v$process
WHERE rownum < 11;

 PID
----
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10

SELECT dbms_pqdiag.getProcessName(31)
FROM dual;

DBMS_PQDIAG.GETPROCESSNAME(57)
------------------------------
P01L
 
QCSLV_INFO (new 20c)
Displays the QC-slave relationship of a currently running query dbms_pqdiag.qcslv_info RETURN sys.dbms_pqdiag.qcslv_col PIPELINED;
SELECT * FROM TABLE(dbms_pqdiag.qcslv_info);

no rows selected
 
QCSLV_INFO_LOCAL (new 20c)
Displays the QC-slave relationship of a currently running query dbms_pqdiag.qcslv_info_local RETURN sys.dbms_pqdiag.qcslv_col_local PIPELINED;
SELECT * FROM TABLE(dbms_pqdiag.qcslv_info_local);

no rows selected

Related Topics
Built-in Functions
Built-in Packages
Database Security
DBMS_SQLDIAG
DBMS_SQLTUNE
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx