Which PQ Server

This post shows how to determine which parallel query server’s (PQS) were used to execute a parallel table function.

This is done using the v$px_process view which shows the relationship between Oracle sessions and PQ servers.

First create a table with some demo data.

CREATE TABLE demo
( col1 NUMBER );

BEGIN
  FOR counter IN 1..10 LOOP
    INSERT INTO demo VALUES(counter);
  END LOOP;
END;
/

Then create two object types as the return values from the table function.

CREATE TYPE v_o AS OBJECT ( col1 NUMBER,
                            pq   VARCHAR2(100) )
/
CREATE TYPE v_t AS TABLE OF v_o;
/

Then a package for the cursor definition.

CREATE OR REPLACE PACKAGE demo_curs AS
 TYPE v_demo_curs IS REF CURSOR RETURN demo%ROWTYPE;
END;

Then the function itself.

CREATE OR REPLACE FUNCTION which_pq ( p_curs demo_curs.v_demo_curs )
                  RETURN v_t
                  PARALLEL_ENABLE ( PARTITION p_curs BY RANGE(col1) )
                  ORDER p_curs BY ( col1 )
                  PIPELINED AS
  v_col1 NUMBER;
  v_pq VARCHAR2(100);
BEGIN
  LOOP
    -- find the name of the PQS for the current session
    SELECT server_name
      INTO v_pq
      FROM v$px_process
     WHERE sid = ( SELECT sid
                     FROM v$mystat
                    WHERE rownum = 1 );
    -- get a row from cursor
    FETCH p_curs INTO v_col1;
    EXIT WHEN p_curs%NOTFOUND;
    -- send a row back as soon as its found!
    PIPE ROW(v_o(v_col1,v_pq));
  END LOOP;
  RETURN;
END;

Then execute the function in parallel.

SQL> ALTER SESSION FORCE PARALLEL QUERY;
Session altered.

SQL> SELECT *
  2    FROM TABLE(which_pq(CURSOR(SELECT *
  3                                 FROM demo)))
  4   ORDER BY col1
  5 /

COL1       PQ
---------- ----------
         1 P004
         2 P004
         3 P004
         4 P005
         5 P005
         6 P005
         7 P005
         8 P006
         9 P006
        10 P007
10 rows selected.

SQL> ALTER SESSION DISABLE PARALLEL QUERY;

The function was defined to partition records across PQS based on the values in COL1. In this case it partitioned as:

  • Values 1 through 3 to P004
  • Values 4 through 7 to P005
  • Values 8 through 9 to P006
  • Value 10 to P007

The distribution and PQ processes used will vary based on system load and the amount of data.

Thanks for reading!