OS & Oracle

After the parallel execution, this is the best chance to query the execution information. For Example, to analyze the execution of following parallel SQL statement.

select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c hash, hash)*/
c.unit_price
from products p, costs c
where c.prod_id = p.prod_id;

After execution,

hlr@ORCL> select * from v$pq_sesstat;

STATISTIC [...]

Two execution plans.
Disable optimizer_dynamic_sampling firstly.

alter session set optimizer_dynamic_sampling = 0;

Then, create two simple tables;

CREATE TABLE t1 ( c1 NUMBER, c2 NUMBER );
CREATE TABLE t2 ( c1 NUMBER, c2 NUMBER );

hr@ORCL> SELECT /*+ parallel(t1,2) */ t1.c2,t2.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;

Execution Plan
———————————————————-
Plan hash value: 869483324

——————————————————————————————————————
| Id | Operation [...]