Parallel Query Plan

Had a case of developing ETL in a 10g environment but the production environment being a 9i. When we released the ETL into production, there was a performance issue with one of the reports. Comparing the execution plan in 9i and 10g for the query looked like the problem was the parallel query not running in 9i. The init.ora parameters were also different, so it looked very likely that not running in parallel query was the problem.

The init.ora parameters in 9i were changed to mirror the ones in 10g, yet after restarting with the new parameters, the query path still did not indicate a parallel run. So what was the problem?

The problem is that the plan in 9i does not indicate parallel query in the same informative way as in 10g. In fact both databases were running in parallel, and the problem with the query was somewhere else.

To make sure the query runs in parallel rather than relying on the plan, examine the following:

BEFORE RUNNING:
SQL> select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 0 0
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 0 0
Server Threads 0 0
Allocation Height 0 0
Allocation Width 0 0
Local Msgs Sent 0 0
Distr Msgs Sent 0 0
Local Msgs Recv'd 0 0
Distr Msgs Recv'd 0 0

AFTER RUNNING THE SQL WITH PARALLLEL HINT:
SQL> select * from V$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 1 1
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 1 1
Server Threads 0 0
Allocation Height 7 0
Allocation Width 2 0
Local Msgs Sent 28 0
Distr Msgs Sent 28 0
Local Msgs Recv'd 63 0
Distr Msgs Recv'd 78 0

11 rows selected.