Database Reference
In-Depth Information
The following example (based on the
px_min_percent.sql
script), executed while no other parallel execution
was running, illustrates this (note that 40 is 80% of 50):
SQL> ALTER SYSTEM SET parallel_max_servers =
40
;
SQL> ALTER TABLE t PARALLEL
50
;
SQL> ALTER SESSION SET parallel_min_percent =
80
;
SQL> SELECT count(pad) FROM t;
COUNT(PAD)
----------
100000
SQL> SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'basic +parallel'));
------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T | Q1,00 | PCWP | |
------------------------------------------------------------------------
SQL> ALTER SESSION SET parallel_min_percent =
81
;
SQL> SELECT count(pad) FROM t;
SELECT count(pad) FROM t
*
ERROR at line 1:
ORA-12827: insufficient parallel query slaves (requested 50, available 40, parallel_min_percent 81)
If you want to know how many operations were downgraded on a running database instance and by how
much, you can execute the following query. Obviously, when you see too many downgrades, especially when many
operations are serialized, you should question the configuration:
SQL> SELECT name, value
2 FROM v$sysstat
3 WHERE name like 'Parallel operations%';
NAME VALUE
------------------------------------------- -----
Parallel operations not downgraded 14
Parallel operations downgraded to serial 10
Parallel operations downgraded 75 to 99 pct 14
Parallel operations downgraded 50 to 75 pct 2
Parallel operations downgraded 25 to 50 pct 0
