Introduction

Architecture

Instance Configuration

Instance Configuration II

Dictionary Views

Dictionary Views II

Monitoring Queries

Session Tracing and Wait Events

Conclusion

Other Resources

Please note this page has moved to http://oracledoug.com/px7.html. You will be redirected in 5 seconds but it would be better to update your bookmarks ;-)

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.

Previous