Database Reference
In-Depth Information
INSTANCE_NUMBER NAME DIFF_SENT DIFF_RECV
----------------- --------- ---------- ----------
1 ipq 1440328342 1297116631
2 ipq 1299190976 1438331216
3 ipq 733181 575309
4 ipq 482387 350462
The same index creation with PX servers allocated in all four nodes generated 4.1GB of interconnect traffic for PQ
workload.
11
So, index creation from many nodes can increase interconnect traffic.
INSTANCE_NUMBER NAME DIFF_SENT DIFF_RECV
--------------- --------- ---------- ----------
1 ipq 1101141155 989508581
2 ipq 1015271038 992655090
3 ipq 1150033881 981687750
4 ipq 906133897 1204963877
If your database server has sufficient resources, you should probably set parallel_force_local=TRUE in your
session and create the index in that session. This method would allocate all PX servers in the same node, reducing
interconnect traffic.
Also, you should know that index creation on partitioned tables can be slower if not executed properly.
By default, a partition is the granularity of PX allocation while creating a partitioned index. So,
one
PX server is
assigned to process
one
table partition while creating a partitioned index. This strategy can cause performance
issues, especially if the partitioned table is huge, which is typically the case. Worse, if the table has non-uniform size
partition, then a few PX servers can be stuck processing huge partitions while other PX servers are idle. Ideally, at least
half of allocated PX servers should be active during index creation. Hence, it is an optimal approach to create index
structures with
unusable
state and then rebuild index partitions individually. Further, you can design your strategy
in such a way that each index partition is rebuilt by an instance.
■
The dbms_pclxutil package is immensely useful to rebuild partitioned indexes with ease. The procedure call
dbms_pclxutil.build_part_indexes uses a parallel-enabled rebuild strategy to rebuild index partitions effectively, but the
package doesn't allow you to distribute jobs among instances. if you are planning to use an instance to rebuild indexes,
then consider using the dbms_plcxutil package.
Note
Parallel DML in RAC
DML statements can be executed in parallel, utilizing PX servers from multiple instances. There are similarities in
processing between parallel DML and index creation. Please refer to the section “Index Creation in RAC” earlier in
this chapter for discussions about choosing intra-instance or inter-instance parallelism.
Parallel DML must be enabled in the session using the
alter session enable parallel dml
command. Two
types of workload are possible with parallel DML, and the type chosen depends upon the execution plan: (1) parallel
scan only, or (2) parallel scan and change. In the first case, PX servers will scan the objects and the DML operation
will be performed by QC. In the second case, PX servers will scan the objects and perform DML operations too.
In the following execution plan output, in step 3, the table is updated and then a message is sent to QC. Essentially,
11
Calculated by summing up either the DIFF_SENT column or the DIFF_RECV column, but not both.
