Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips

 

Monitoring Oracle Parallel Query with STATSPACK

 

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. Here is the script that I use to monitor parallel queries:

 

rpt_parallel.sql

set pages 9999;

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

select
   to_char(snap_time,'yyyy-mm-dd HH24')      mydate,
   new.value  nbr_pq
from
   perfstat.stats$sysstat   old,
   perfstat.stats$sysstat   new,
   perfstat.stats$snapshot  sn
where
   new.name = old.name
and
   new.name = 'queries parallelized'
and
   new.snap_id = sn.snap_id
and
   old.snap_id = sn.snap_id-1
and
   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 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.

 

For additional details see the Oracle Press Book “Oracle High Performance Tuning with STATSPACK“.

 

 

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.