Parallel Query Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 261

Data Skew : Parallel queries divide data between reader slaves on a ROWID range basis.
Although the same number of blocks is given to each slave, it does not mean that these
blocks contain identical numbers of rows. In fact, some of the blocks could be completely
empty. This can be especially problematic where large quantities of data are archived and
deleted, as this results in many empty, or sparsely filled, blocks. The effect of this non-
uniform data distribution is to cause queries to run slower than they might otherwise.
This is because one slave does all the work. Under such situations, the only option that is
available is to reorganize the data.
Data Dictionary Views to Monitor Parallel Operations
Oracle dynamic performance views provide a great insight into the performance statistics. These views provide great
instrumentation into the functioning and behavior of parallel queries in a RAC or non-RAC environment.
GV$PX_BUFFER_ADVICE
Similar to most advice views available, the (G)V$PX_BUFFER_ADVICE view provides statistics on historical and projected
maximum buffer usage by all parallel queries. This view is useful to monitor memory utilization and to size the system
global area (SGA). The following output provides the current utilization across 8 instances of a RAC cluster (formatted
for clarity). The following output indicates that there are sufficient buffers available and only a small percentage of this
is used. The output also indicates that all instances in the cluster are balanced from a memory utilization perspective:
SELECT inst_id INT,
statistic,
value
FROM gv$px_buffer_advice
ORDER BY inst_id;
INT STATISTIC VALUE
---- ------------------------------ ----------
1 Servers Highwater 264
Buffers HWM 5349
Estimated Buffers HWM 53064
Servers Max 256
Estimated Buffers Max 49920
Buffers Current Free 205
Buffers Current Total 1125
. . . . . . . . .. . . . . . .
. . . . . . . . . . . . . . . . .
. . . . . . .. . . . . . . . . .
8 Servers Highwater 249
Buffers HWM 3030
Estimated Buffers HWM 46872
Servers Max 256
Estimated Buffers Max 49920
Buffers Current Free 439
Buffers Current Total 500