Back to Papers
Monitoring the Parallel Adaptive Multi-user Algorithm
If you are using the Parallel Adaptive Multi-User algorithm, itís vital that you are able to check whether any particular operations have been severely downgraded because the server is too busy. There are additional statistics in V$SYSSTAT that show this information.
SELECT name, value FROM v$sysstat WHERE name LIKE 'Parallel%' NAME VALUE -------------------------------------------------------- ---------- Parallel operations not downgraded 546353 Parallel operations downgraded to serial 432 Parallel operations downgraded 75 to 99 pct 790 Parallel operations downgraded 50 to 75 pct 1454 Parallel operations downgraded 25 to 50 pct 7654 Parallel operations downgraded 1 to 25 pct 11873
Clearly, you should be most concerned about any operations that have been downgraded to serial as these may be running many times more slowly than youíd expect. This information is also available in a Statspack report so itís easy to get a view over a period of time. Unfortunately the name column in truncated in the report, which makes it a little difficult to read, but you soon get used to this.
Monitoring the SQL being executed by slaves
As with most dictionary views, we can write queries that combine them to show us interesting or useful information. To offer just one small example, this query will show us the SQL statements that are being executed by active PX slaves. (N.B. The slave must be active, otherwise the SID and SERIAL# it was previously associated with is not contained in the v$px_process view)
set pages 0 column sql_test format a60 select p.server_name, sql.sql_text from v$px_process p, v$sql sql, v$session s WHERE p.sid = s.sid and p.serial# = s.serial# and s.sql_address = sql.address and s.sql_hash_value = sql.hash_value /
Even more interestingly, youíll see completely different results if you run this query on Oracle 10g than on previous versions. First some example results from Oracle 9.2
P001 SELECT A1.C0 C0,A1.C1 C1,A1.C2 C2,A1.C3 C3,A1.C4 C4,A1.C5 C5, A1.C6 C6,A1.C7 C7 FROM :Q3000 A1 ORDER BY A1.C0
Whereas on 10g the results look like this
P000 SELECT /*+ PARALLEL (attendance, 2) */ * FROM attendance ORD ER BY amount_paid P003 SELECT /*+ PARALLEL (attendance, 2) */ * FROM attendance ORD ER BY amount_paid P002 SELECT /*+ PARALLEL (attendance, 2) */ * FROM attendance ORD ER BY amount_paid P001 SELECT /*+ PARALLEL (attendance, 2) */ * FROM attendance ORD ER BY amount_paid
This is an example of a more general change in 10g. When tracing or monitoring the PX slaves, the originating SQL statement is returned, rather than a block range query as shown earlier in this document. I think this makes it much easier to see at a glance what a particular long-running slave is really doing, rather than having to tie it back to the QC as on previous versions. However, it makes things much trickier when trying to identify which slave is doing what.