Oracle | DBMS

Oracle DBMS_PQDIAG Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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 qcslv_type_postjoin IS RECORD ( INST_ID NUMBER, SID NUMBER, ID1 NUMBER, ID2 NUMBER, SLVSID NUMBER, SLVINST_ID NUMBER, TOPO_SLVNO NUMBER, SERVER_NAME VARCHAR2(4), SPID VARCHAR2(24), SERIAL# NUMBER, SQL_ID VARCHAR2(13), SQL_CHILD_ NUMBER NUMBER, COMMAND 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; TYPE qcslv_col_postjoin IS TABLE OF qcslv_type_postjoin;

Dependencies

DBMS_SYS_ERROR	GV_$PX_PROCESS	V_$PX_PROCESS
DUAL	GV_$SESSION	V_$PX_SESSION
GV_$ENQUEUE_LOCK	V_$PQ_TQSTAT	V_$SESSION
GV_$PROCESS

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

CNT_QERPXTRC

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

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

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

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

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

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

QCSLV_INFO_POSTJOIN (new 23c)

Undocumented dbms_pqdiag.qcslv_info_postjoin RETURN qcslv_col_postjoin pipelined PIPELINED;

SELECT * FROM TABLE(dbms_pqdiag.qcslv_info_postjoin);

      

      no rows selected

QCSLV_INFO_PREJOIN (new 23c)

Undocumented dbms_pqdiag.qcslv_info_prejoin RETURN qcslv_col PIPELINED;

SELECT * FROM TABLE(dbms_pqdiag.qcslv_info_prejoin);

      

      no rows selected