Database Reference
In-Depth Information
$ sqlplus rs/temp123@//orcl-scan:1521/finance
...
Alter session set parallel_instance_group=po;
PX server placement utilizing the service configuration is elegant, since after a node/instance failure, services
automatically fail over to the surviving nodes. You should consider instance failure scenarios while setting up services
and make active/passive configuration for services too. Further, service configuration can be modified dynamically.
In contrast, if you use instance_group and parallel_instance_group parameters, then during instance failure, you may
have to modify the instance_group parameter, which might require another surviving instance restart.
Parallel_force_local
The parallel_force_local parameter can be used to localize the PX server allocation. If the parallel_force_local
parameter is set to TRUE, then PX servers are allocated in the same instance that the PX query was initiated. For
example, if you connect to the ORCL1 instance and initiate a PX execution, then all PX servers will be allocated from
the ORCL1 instance. This parameter can be modified at the session level or at the global level the using alter session
or alter system commands.
The problem with controlling the PX server placement with this parameter is that if all PX servers in the local
instance are exhausted, then the query may run serially or with reduced parallelism. Thus, it is a preferable approach
to use services to control the allocation. Also, this parameter doesn't work well with the parallel_statement_queuing
parameter, as discussed later in this chapter.
2
Measuring PX Traffic
Since Oracle Database version 11g, private network traffic statistics have been captured at the workload level and
externalized in the x$ksxpclient fixed table.
In Listing 12-3, query shows that the data from the x$ksxpclient fixed table is connected to the PROD1 instance.
From the output, we can calculate bytes sent and received for each workload. The PROD1 instance sent 32TB and
received 49TB for the buffer cache (cache fusion) workload. Similarly, the PROD1 instance sent 8.7TB and received
5.2TB for the DLM (Distributed Lock Manager) workload. Instance PROD1 also sent 1.8TB and received 2.1TB for the
PX workload. In the following output, PX workload is indicated by the line with name as ipq.
Listing 12-3.
x$ksxpclient
set pages 100
SYS@PROD1> SELECT name,
TRUNC (bytes_sent/1024/1024,2) bytes_sent_MB,
TRUNC(bytes_rcv /1024/1024,2) bytes_recv_MB
FROM x$ksxpclient;
NAME BYTES_SENT BYTES_RECV
--------- ---------- ----------
cache 32165225 49870288.1
dlm 8754651.32 5217822.01
ipq 1791954.4 2121334.66
ksxr 5069.85 4572.55
2
Arup Nanda points out that, in the Exadata platform, by default, the parallel_force_local parameter is set to TRUE. It is interesting
to realize that even with the highly efficient Infiniband fabric-based interconnect, this parameter is set to TRUE. However, choosing
a value for this parameter might require knowledge about your application workload and database server architecture.
