Parallel operations (parallel Query and DML) and monitoring

1. Scan the partition table in PARALLEL by ROWID: select/* + PARALLEL (SALES, 9) */* FROMSALES; V $ PQ_TQSTAT: allows you to view statistics on PARALLEL operations. helps you determine imbalance in a query. it is best to work with each parallel servers... syntaxHighl 1: scan the partition table in PARALLEL by ROWID: select/* + PARALLEL (SALES, 9) */* from sales; V $ PQ_TQSTAT: you can view statistics on parallel execution operations. helps you determine imbalance in a query. it is best to have a similar workload for each parallel servers. We can see the number of processes processed by each subordinate process. Only sessions that issue SQL statements in parallel can be viewed. Other sessions cannot. SQL> select * from v $ pq_tqstat; DFO_NUMBER TQ_ID SERVER_TYPE BYTES OPEN_TIME wait waits timeouts process instance ------------ hour ------------ ---------- hour ------------ hour ---------- 1 0 Producer 103114 3851028 0 13 0 P008 1 0 Producer 104775 3892852 0 0 13 0 P007 1 1 0 Producer 98771 3672836 0 0 12 0 P006 1 1 0 Producer 102038 3797736 0 0 13 0 P005 1 1 0 Producer 98982 3694298 0 0 14 0 P003 1 1 0 Producer 104311 3877078 0 0 13 0 p004 1 1 0 Producer 100393 3715209 0 0 12 0 P002 1 1 0 Producer 105659 3927959 0 0 13 0 P001 1 1 0 Producer 100800 3742268 0 0 14 0 P000 1 1 0 Consumer 918843 34171264 0 0 18 11 QC 110 rows selected. 2. Scanning index partitions in parallel can be performed on partitioned tables and indexes. The table name, index name, and degree of parallelism (DOP) must exist in nt ). for example: select/* + parallel_index (s, SALES_PROD_BIX, 3) */* from sales s; 3. partitionwise join, partial partitionwise join a table T1 has three partitions, one table T2 has no partitions, But they connect T1.id = T2.id through ID, assuming DOP = 3, in table T1, the three SERVERS scan in parallel at the same time, and then scan table T2 through broadcast. In this case, table T2 is dynamically divided into three parts. B, Full partitionwise join (equi-partitioned) 4, parallel DMLa, enable alter session enable parallel dml; B, first QUERY, then UPDATEc, even if DML disable, QUERY parallelism is acceptable. D. By default, session DML is Disable partitioned tables, which are particularly suitable for parallel operations, for example: alter session enable parallel DML; drop table t1 purge; create table t1 as select * from sales where rownum <5000; SQL> set autotrace traceonly expSQL> update/* + parallel (t1, 4) */t1 set amount_sold = amount_sold * 1.1; 4999 rows updated. execution Plan ---------------------------------------------------------- Plan hash value: 121765358 --------------------------------- ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib | distribution | 0 | update statement | 4999 | 64987 | 2 (0) | 00:00:01 | 1 | UPDATE | T1 | 2 | px coordinator | | 3 | px send qc (RANDOM) |: TQ10000 | 4999 | 64987 | 2 (0) | 00:00:01 | Q1, 00 | P-> S | QC (RAND) | 4 | px block iterator | 4999 | 64987 | 2 (0) | 00:00:01 | Q1, 00 | PCWC | 5 | table access full | T1 | 4999 | 64987 | 2 (0) | 00:00:01 | Q1, 00 | PCWP | notice Note ------dynamic sampli Ng used for this statement # From the execution plan here, we can see that the update statement is on top of px coordinator, which is obviously a serial update. In this case, the alter session enable parallel dml switch is enabled. An error is reported as follows. Because the previous transaction is not over yet. SQL> alter session enable parallel DML; ERROR: ORA-12841: Cannot alter the session parallel DML state within a transactionSQL> rollback; Rollback complete. SQL> alter session enable parallel dml; Session altered. SQL> update/* + parallel (t1, 4) */t1 set amount_sold = amount_sold * 1.1; 27 rows updated. execution Plan ---------------------------------------------------------- ERROR: ORA-12838: cannot read/modify An object after modifying it in parallel SP2-0612: Error generating autotrace explain report # Because parallel dml is implemented here, insert for plan_table also becomes parallel, for the same transaction, the table that has been concurrently dml cannot be queried, so an error is reported here. SQL> rollback; Rollback complete. SQL> set autotrace off; SQL> explain plan for update/* + parallel (t1, 4) */t1 set amount_sold = amount_sold * 1.1; explain. SQL> select * from table (dbms_xplan.display (); PLAN_TABLE_OUTPUT tables ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Explain Plan hash value: 3991856572 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib | Bytes | 0 | UPDATE STATEMENT | 4999 | 64987 | 2 (0) | 00:00:01 | 1 | px coordinator | 2 | px send qc (RANDOM) |: TQ10000 | 4999 | 64987 | 2 (0) | 00:00:01 | Q1, 00 | P-> S | QC (RAND) | 3 | UPDATE | T1 | Q1, 00 | PCWP | 4 | px block iterator | 4999 | 64987 | 2 (0) | 00:00:01 | Q1, 00 | PCWC | 5 | table access full | T1 | 4999 | 64987 | 2 (0) | 00:00:01 | Q1, 00 | PCWP | PLAN_TABLE _ OUTPUT sampled random Note ------dynamic sampling used for this Statement16 rows selected. check the real parallel dml execution plan. We can see that the update statement exists as the son of px coordinator, and the update statement corresponds to the PCPW operation rather than the P-> S operation. Obviously, records are retrieved in parallel, and updates are also in parallel. SQL> alter session enable parallel dml; Session altered. SQL> update/* + parallel (t1, 4) */t1 set amount_sold = amount_sold * 1.1; 4999 rows updated. SQL> update t1 set amount_sold = amount_sold * 1.1 where rownum = 1; update t1 set amount_sold = amount_sold * 1.1 where rownum = 1 * ERROR at line 1: ORA-12838: cannot read/modify an object after modifying it in parallel Note: ORA-12838 errors indicate that an object is concurrently updated as long as it is enable parallel dml After (without commit or rollback), you cannot perform read or update. Of course, other sessions are affected by the operation of this table. It is only affected by its own session. Of course, if you alter session disable parallel dml; and then perform parallel updates, you can perform read or update. See the following example: SQL> alter session disable parallel dml; Session altered. SQL> update/* + parallel (t1, 4) */t1 set amount_sold = amount_sold * 1.1; 4999 rows updated. SQL> update t1 set amount_sold = amount_sold * 1.1 where rownum = 1; 1 row updated.www.2cto.com after the first SQL statement is executed in parallel, I have no commit or rollback, the following SQL statement can be updated. NOTE: If parallel_adaptive_multi_user is set to true, the system automatically adjusts your parallel value and does not generate expected values. Set it to false. Parallel DML explanation: the parallel update and delete statements are divided into two parts, one is to retrieve data, and the other is to update data. If the parallel prompt is added to the update and delete statements, but the alter session enable parallel dml is not set. In this case, only the part of the data retrieved in the update and delete statements enables parallel operations, and the actual update is still serialized. However, if alter session enable parallel dml is set at the same time, both data retrieval and Data Update use parallel processing. View information about Parallelism: V $ PX_SESSIONV $ PX_PROCESSV $ PX_SESSTATV $ PQ_SESSTAT