Oracle DBMS

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	DISPLAY_TRACE_LOCAL	QCSLV_INFO
DISPLAY_TRACE	GETPROCESSNAME	QCSLV_INFO_LOCAL

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