Parallel Processing Data Dictionary views Lesson 1



Last updated on August 15th, 2015 at 05:50 pm

In this section we are trying to give a glimpse of Parallel Processing Data Dictionary views
Oracle keep changing the view name,but below query should be able to give all the views
SELECT table_name
FROM dict
WHERE table_name LIKE ‘V%PQ%’
OR table_name like ‘V%PX%‘;
Examples
V$PX_SESSION
V$PX_SESSTAT
V$PX_PROCESS
V$PX_PROCESS_SYSSTATSome important views
a)V$PQ_SESSTAT
V$PQ_SESSTAT shows you PX statistics for your current session.
Following query could be used
SQL> SELECT * FROM v$pq_sesstat;
Queries Parallelized                         1               2
DML Parallelized                             0               0
DDL Parallelized                             0               0
DFO Trees                                    1               2
Server Threads                               2               0
Allocation Height                            1               0
Allocation Width                             2               0
Local Msgs Sent                              3               6
Distr Msgs Sent                              3               6
Local Msgs Recv’d                            3               6
Distr Msgs Recv’d                            3               6

This gives us a nice ways to track the parallism in current session

b)V$PQ_SYSSTAT
This view is useful for getting an instance-wide overview of how PX slaves are being used and is particularly helpful in
determining possible changes to parallel_max_servers and parallel_min_server
SQL> column STATISTIC format a40
SQL> SELECT * FROM v$pq_sysstat;
STATISTIC                                     VALUE
—————————————- ———-
Servers Busy                                      0
Servers Idle                                      3
Servers Highwater                                17
Server Sessions                              672683
Servers Started                               11740
Servers Shutdown                              11737
Servers Cleaned Up                                0
Queries Queued                                    0
Queries Initiated                            191358
Queries Initiated (IPQ)                           0
DML Initiated                                   100
DML Initiated (IPQ)                               0
DDL Initiated                                     7
DDL Initiated (IPQ)                               0
DFO Trees                                    193693
Sessions Active                                   0
Local Msgs Sent                            94708255
Distr Msgs Sent                              323561
Local Msgs Recv’d                          95268766
Distr Msgs Recv’d                            360143

 


Leave a Reply