BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










Monitoring Oracle Parallel Query with STATSPACK

Oracle Tips by Burleson Consulting

In addition to monitoring parallel execution for individual queries, you can also monitor parallel query activity for your whole database. Using STATSPACK, you can query the stats$sysstat table to extract the number of parallelized queries that have been run during each time period between your STATSPACK snapshots.


set pages 9999;

column nbr_pq format 999,999,999
column mydate heading 'yr.  mo dy Hr.'

   to_char(snap_time,'yyyy-mm-dd HH24')      mydate,
   new.value  nbr_pq
   perfstat.stats$sysstat   old,
   perfstat.stats$sysstat   new,
   perfstat.stats$snapshot  sn
   new.name = old.name
   new.name = 'queries parallelized'
   new.snap_id = sn.snap_id
   old.snap_id = sn.snap_id-1
   new.value > 1
order by
   to_char(snap_time,'yyyy-mm-dd HH24')

Here is a sample of the output. This will quickly show the Remote DBA the time periods when parallel full-table scans are being invoked:

SQL> @rpt_parallel

yr.  mo dy      hr. nbr_pq
------------- -------------
2001-03-12 20         3,521
2001-03-12 21         2,082
2001-03-12 22         2,832
2001-03-13 20         5,152
2001-03-13 21         1,835
2001-03-13 22         2,623
2001-03-14 20         4,274
2001-03-14 21         1,429
2001-03-14 22         2,313

In this example, we see that there appears to be a period each day between 8:00 p.m. and 10:00 p.m. when tasks are executing parallel queries against tables.

In practice, you may want to run the STATSPACK report to identify periods of high parallel query activity and then go to the stats$sql_summary table to examine and tune the individual parallel queries. Of course, you can also examine parallel query summaries since database start-up time by using the v$pq_sysstat view.

Monitoring Oracle Parallel Query with V$ Views

To see how many parallel query servers are busy at any given time, the following query can be issued against the v$pq_sysstat view:

   statistic = 'Servers Busy';

---------         -----
Servers Busy      30

In this case, we see that 30 parallel servers are busy at this moment. Do not be misled by this number. Parallel query servers are constantly accepting work or returning to idle status, so it is a good idea to issue the query many times over a one-hour period to get an accurate reading of parallel query activity. Only then will you receive a realistic measure of how many parallel query servers are being used.

There is one other method for observing parallel query from inside Oracle. If you are running Oracle on UNIX, you can use the ps command to see the parallel query background processes in action:

ps –ef|grep “ora_p”

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter