Database Reference
In-Depth Information
It's also worth noting in the prior example in this section, that had you
not
enabled parallel DML for the
UPDATE
statement, then the explain plan output would look quite different, for example:
------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | UPDATE | BIG_TABLE | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| BIG_TABLE | Q1,00 | PCWP | |
------------------------------------------------------------------------
To the untrained eye it may look like the
UPDATE
happened in parallel, but in fact it did not. What the prior output
shows is that the
UPDATE
is serial and that the full scan (read) of the table was parallel. So there was parallel query
involved, but not PDML.
VerIFYING paraLLeL OperatIONS
You can quickly verify the parallel operations that have occurred in a session by querying the data dictionary.
For example, here's a parallel DMl operation:
EODA@ORA12CR1> alter session enable parallel dml;
EODA@ORA12CR1> update big_table set status='AGAIN';
next verify the type and number of parallel activities via:
EODA@ORA12CR1> select name, value from v$statname a, v$mystat b
2 where a.statistic# = b.statistic# and name like '%parallel%';
here is some sample output for this session:
NAME VALUE
----------------------------------------------- ----------
DBWR parallel query checkpoint buffers written 0
queries parallelized 0
DML statements parallelized 1
DDL statements parallelized 0
DFO trees parallelized 1
the prior output verifies that one parallel DMl statement has executed in this session.
Parallel DDL
I believe that parallel DDL is the real sweet spot of Oracle's parallel technology. As we've discussed, parallel execution
is generally not appropriate for OLTP systems. In fact, for many data warehouses, parallel query is becoming less and
less of an option. It used to be that a data warehouse was built for a very small, focused user community—sometimes
comprised of just one or two analysts. However, over the last decade or so, I've watched them grow from small user
