Oracle "Parallel Execution" four--monitoring view

Excerpt from the VLDB and partitioning guide

1. Introduction to the View

V$px_buffer_advice

Provides historical usage of buffer for all parallel queries, as well as related proposal planning. For problems such as insufficient memory during parallel execution, you can query this view to be able to reconfigure the SGA.

V$px_session

Provides information about parallel process sessions, server groups, server collections, number of servers, and real-time parallel server process information. This view can also be used to view information such as DOP and actual DOP for parallel statement requests.

V$px_sesstat

V$px_session and V$sesstat join operations, so this view can provide statistics for all parallel sessions.

V$px_process

Provides information about all parallel processes, including status, session ID, process ID, and other information.

V$px_process_sysstat

Provides status information for the parallel server and buffer allocation information.

V$pq_slave

Lists statistics for all parallel servers.

V$pq_sysstat

Lists system statistics for parallel queries.

V$pq_sesstat

Lists session statistics for parallel queries. Session statistics for this view can be viewed only after the parallel statement has completed execution.

V$pq_tqstat

Provides statistics for parallel operations that show the number of rows and bytes processed by each parallel server at each stage.

Session statistics for this view can only be viewed after the parallel statement has been executed, and only the validity period of the sessions will be preserved. For parallel DML, statistics about this view can be displayed only after committing or rolling back.

2. Common scripts

1) View the parallel statistics in the system, whether the requested DOP is actually used, and whether these operations are degraded:

SELECT name,value from V$sysstat t WHERE t.name like '%parallel% ';

NAME VALUE

Parallel Operations not downgraded 4

Parallel operations downgraded to serial 0

Parallel operations downgraded to the PCT 0

Parallel Operations downgraded 0 pct

Parallel operations downgraded PCT 0

Parallel operations downgraded 1 to PCT 0

2) View parallel subordinate server statistics in the V$pq_sysstat view. By looking at this information, you can see whether the parallel settings in the database are correct. If you see a server shutdown (Servers Shutdown) and a high server startup value, it may indicate that the setting value for the Parallel_min_servers parameter is too low, because a parallel process that continues to start and shut down requires a corresponding cost expense.

SELECT * from V$pq_sysstat;

Statistic VALUE

Servers Busy 8

Servers Idle 8

Servers Highwater 16

Server Sessions 59

Servers Started 35

Servers Shutdown 19

Servers cleaned up 0

Queries Queued 0

Queries initiated 4

Queries initiated (IPQ) 0

DML initiated 0

DML initiated (IPQ) 0

DDL initiated 0

DDL initiated (IPQ) 0

DFO Trees 4

Sessions Active 1

Local Msgs Sent 41706

Distr Msgs Sent 0

Local Msgs Recv ' d 78048

Distr Msgs Recv ' d 0

3) Querying the V$pq_tqstat view, you can determine how each parallel server splits the work, or display the time used DOP. However, when querying this view, you need to display information in parallel operations in the same session.

SELECT * from V$pq_tqstat;

Dfo_number tq_id server_type num_rows BYTES open_time avg_latency WAITS Timeouts PROCESS INS Tance

1                           0           producer           1                        36           0                     0                             13             0                 p001               1

1                           0           producer           1                        36           0                     0                             12             0                 p004               1

1                           0           producer           1                        36           0                     0                             13             0                 p003               1

1                           0           producer           1                       36            0                     0                             13             0                 p000               1

1                           0           producer           1                       36            0                     0                             13             0                 p002               1

1                           0           producer           1                       36            0                     0                             13             1                 p006               1

1                           0          Producer             1                       36            0                     0                             13             0                 p007               1

1                           0          Producer             1                       36            0                     0                             14             1                 p005               1

1                           0         Consumer            8                       288          0                     0                             154             42             qc                 1

4) Query the v$system_event or v$session_event view to know the parallelism-related waits in the database.

Sql> SELECT event,wait_class,total_waits from v$system_event WHERE event like ' px% ';

EVENT Wait_class Total_waits

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

PX Deque Wait Idle 3

PX Idle Wait Idle 94

PX Deq:join ACK Idle 59

PX Deq credit:need Buffer Idle 1009

PX Deq credit:send blkd Idle 4609

PX deq:parse Reply Idle 56

PX Deq:execute Reply Idle 323

PX deq:execution MSG Idle 376

PX deq:table Q Normal Idle 77772

PX Qref Latch Other 14

PX deq:signal ACK RSG Other 8

EVENT Wait_class Total_waits

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

PX deq:signal ACK EXT Other 11

PX Deq:slave Session Stats Other 22

Rows selected.

Oracle "Parallel Execution" four--monitoring view