gv$px_session tips

Question:  How does the gv$px_session view work?

Answer:  The gv$px_session view can provide this information as can be seen below.

 select

     ps.qcsid,

     ps.sid,

     p.spid,

     ps.inst_id,

     ps.degree,

     ps.req_degree

  from
For full scripts, download the Oracle script collection.

     gv$px_session ps

     join

     gv$session s

        on ps.sid=s.sid

           and

           ps.inst_id=s.inst_id

     join

     gv$process p

        on p.addr=s.paddr

        and

        p.inst_id=s.inst_id

  order by

     qcsid,

     server_group desc,

     inst_id,

     sid;

     QCSID        SID    SPID    INST_ID     DEGREE REQ_DEGREE

---------- ---------- ------- ---------- ---------- ----------

        84         84   15343          1

        84         39    5511          1          8          8

        84         63    5552          1          8          8

        84         66    5495          1          8          8

        84         74    5469          1          8          8

        84         80   18554          1          8          8

        84         81   18560          1          8          8

        84         82   18556          1          8          8

        84         83   18558          1          8          8

        84         11    6123          2          8          8

        84         34    5015          2          8          8

        84         40    5004          2          8          8

        84         42    5013          2          8          8

        84         57    5010          2          8          8

        84         62    6121          2          8          8

        84         68    6109          2          8          8

        84         75    6115          2          8          8

The qcsid column shows the session identifier of the Query Coordinator. The first line of output shows the sid column has the same value as the qcsid column, which means the first line is the process that identifies the QC. Also note that for the QC process, the last two columns are null. The OS process identifier, shown in the spid column, should correspond to the user's shadow process on the host as can be seen below.

$ ps -ef|grep 15343

oracle   15343 15266  0 03:28 ?        00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

oracle   18799 12748  0 03:37 pts/2    00:00:00 grep 15343

The remaining rows of output are the parallel slave processes. The degree column provides the DEGREE OF PARALLELISM for this session while the req_degree column denotes the requested DEGREE OF PARALLELISM. The actual DEGREE OF PARALLELISM may be lower than the requested DEGREE OF PARALLELISM if the Resource Master has constrained the session. In the output above, the DEGREE OF PARALLELISM is 8 however there are 16 slave processes. When the number of parallel slaves equals twice the DEGREE OF PARALLELISM, then half the slave processes are producers and half are consumers. Two of the slave OS process identifiers are verified below.

$ ps -ef|grep 5511

oracle    5511     1  1 03:16 ?        00:00:22 ora_p002_orcl1

oracle   18830 12748  0 03:37 pts/2    00:00:00 grep 5511

$ ps -ef|grep 5552

oracle    5552     1  1 03:16 ?        00:00:22 ora_p003_orcl1

oracle   18848 12748  0 03:37 pts/2    00:00:00 grep 5552

Also note by inspecting the inst_id column that half of the slave processes are executing on the first instance and half are on the second instance. Nothing special was done to force the parallel processes to spread over the instances. Oracle RAC automatically distributes the parallel slaves over the instances that run the QC session's service.