 |
|
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.
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 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:
select
statistic,
value
from
v$pq_sysstat
where
statistic = 'Servers Busy';
STATISTIC VALUE
--------- -----
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.
