Database Reference
In-Depth Information
Interconnect Throughput by Client DB/Inst: PROD/PROD1 Snaps: 52265-52313
-> Throughput of interconnect usage by major consumers
-> All throughput numbers are megabytes per second
Send Receive
Used By Mbytes/sec Mbytes/sec
---------------- ----------- -----------
Global Cache 15.19 22.93
Parallel Query .05 .07
DB Locks 4.11 2.46
DB Streams .00 .00
Other .00 .00
PX and Cache Fusion
PX execution generally reads the block directly into the Program Global Area (PGA) of the PX server process,
bypassing buffer cache. Since the buffer cache is not used, cache fusion locks are not required for the blocks read
using the direct path read method.
■
not all PX executions will bypass buffer cache. For example, a PX plan can use nested loops join (each PX
server performing its own nested loops join for a subset of rows), and so PX execution using nested loops join might use
the buffer cache. Also, the new feature
in memory parallelism
in Oracle Database release 11.2 also enables the use of
the buffer cache for PX executions.
Note
The following lines are printed from a SQL trace file after initiating a parallel query execution. You can see that
there are no global cache event waits for the direct reads. The direct path method reads the blocks directly into the
PGA of PX server processes. Notice that 128 blocks are read from the disk to PGA directly without incurring a single
global cache-related wait event. For massive segment scans, direct path reads are very efficient and reduce the global
cache overhead associated with a traditional buffered read. If 128 blocks had to be buffered in the buffer cache, then
there would have been numerous waits for global cache events.
select /*+ full(a) parallel (4) */ count(*) from apps.mtl_material_transactions a
...
nam='direct path read' ela= 1676 file number=845 first dba=401920 block cnt=128 obj#=37871
nam='direct path read' ela= 37423 file number=845 first dba=402048 block cnt=128 obj#=37871
nam='direct path read' ela= 26573 file number=845 first dba=402304 block cnt=128 obj#=37871
However, PX execution using the direct path reads method triggers an object-level checkpoint in all instances at
the start of PX execution. Excess PX execution can trigger numerous object-level checkpoints, causing elevated write
activity. Further, excessive object-level checkpoints with a combination of huge buffer cache and numerous instances
(eight-plus instances) can lead to continuous checkpoints in all instances, leading in turn to slowdown for the query
itself. So, you should consider the effect of the checkpoint while designing SQL statements to scan smaller tables using
parallel execution.
nam='KJC: Wait for msg sends to complete' ela= 1166 msg=61225879752 ...
nam='enq: KO - fast object checkpoint' ela= 1330 name|mode=1263468550 ...
nam='enq: KO - fast object checkpoint' ela= 722 name|mode=1263468545 ...
