Database Reference
In-Depth Information
select /*+ use_hash (h l ) parallel (h 8) parallel (l 8) full(h) full(l) */
count(*) from
oe_order_headers_all h, oe_order_lines_all l
where h.header_id = l.header_id and
h.open_flag='N'
/
Trace file due to 10053 event:
Best:: AccessPath: TableScan
Cost: 197226.11 Degree: 1 Resp: 197226.11 Card: 10992402.90 Bytes: 0
After altering parallel_instance_group to the correct value, reparsing the same statement shows that the
optimizer used a parallel degree of 8. Also, notice that the parallel cost is lower than the earlier serial cost of
197226.11 .
Best:: AccessPath: TableScan
Cost: 61202.86 Degree: 8 Resp: 61202.86 Card: 10992402.90 Bytes: 0
Even though the parameters instance_group and parallel_instance_group can be used to control PX server placement,
use of these parameter combinations is deprecated from Oracle Database version 11.2. Now, it is recommended to use
services to control placement of PX servers. Also, in 11.2, the incorrect value for parallel_instance_group parameter
can lead to an excessive number of child cursor problems due to bugs such as 7352775. There are also a few
unpublished bugs where the use of these two parameters leads to unshareable child cursors. So, it is preferable to
use services to control PX server placement.
Services
Services are the preferred approach to control PX server placement. By default, PX servers are allocated in instances
where the current service is active (“current service” refers to the service that the session initiating PX execution
connected). For example, if you connect to po service and if po service is available in ORCL1 and ORCL2 instances,
then PX servers can be allocated from both ORCL1 and ORCL2 instances.
The following output shows the service configuration in a database named ORCL. In this configuration, service
po is active in ORCL1, ORCL2 instances, service scm is active in all three instances, and finance is active only in the
ORCL3 instance. If you connect to the po service and initiate a parallel statement execution, then PX servers can be
allocated from the ORCL1 and ORCL2 instances. Similarly, if you connect to finance service and initiate a parallel
statement execution, then PX servers can be allocated from the ORCL3 instance only.
$ srvctl status service -d ORCL
Service po is running on instance(s) ORCL1,ORCL2
Service scm is running on instance(s) ORCL1,ORCL2,ORCL3
Service finance is running on instance(s) ORCL3
You can also use a combination of both services and the Parallel_instance_group parameter to control PX server
placement. If you set the parallel_instance_group parameter to a value of service, then PX servers can be allocated
from the instances that service is running. For example, after connecting to the finance service (the connection will
go to the ORCL3 instance), you can alter the parallel_instance_group parameter at the session level to po service. Now,
PX executions from the current session can allocate PX servers in the ORCL1 and ORCL2 instances, since po service is
active in the ORCL1 and ORCL2 instances, even though PX is triggered from the ORCL3 instance. While this strategy
provides the ability to control your PX server allocation, it is not a recommended approach.
 
Search WWH ::




Custom Search