Monitoring Oracle Parallel Query
Oracle Tips by Burleson
There are several STATSPACK tables and v$ views
that can be used to monitor the activity of the Parallel Query
background processes. Unfortunately, Parallel Query activity is only
measured at the database level, and you cannot find the specific
tables that are the target of the Parallel Query. Let's begin by
looking at STATSPACK methods for measuring Parallel Query activity.
Monitoring Oracle Parallel Query with 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.name = old.name
new.name = 'queries parallelized'
new.snap_id = sn.snap_id
old.snap_id = sn.snap_id-1
new.value > 1
Here is a sample of the output. This will
quickly show the DBA the time periods when full table scans are being
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.
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
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.
This 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:
Parallel Queries and Distributed Objects
Oracle Parallel Query can be simulated when
using Net8 to perform remote queries. These types of parallel queries
are most useful in distributed databases where a single logical table
has been partitioned into smaller tables at each remote node. For
example, a customer table that is ordered by customer name may be
partitioned into a customer table at each remote database, such that
we have a new_york_employee table, a california_employee table, and so
on. This vertical table partitioning approach is very common with
distributed databases where local autonomy of processing is important.
With the tables partitioned onto different
databases at different geographical locations, how can we meet the
needs of the corporate headquarters where a complete view is required?
How can they query all of these remote tables as a single unit and
treat the logical customer table as a single entity? For large queries
that may span many logical tables, the isolated tables can then easily
be reassembled to use Oracle's Parallel Query facility:
create view all_employee as
select * from new_york_employee@manhattan
select * from california_employee@los_angeles
select * from japan_employee@tokyo
We can now query the all_employee view as if it
were a single database table, and Oracle will automatically recognize
the union all SQL command syntax and fire off simultaneous
queries against each of the three base tables. It is important to note
that the distributed database manager will direct that each query is
processed at the remote location, while the Query Manager waits until
each remote node has returned its result set. For example, the
following query will assemble the requested data from the three tables
in parallel, with each query being separately optimized. The result
set from each subquery is then merged by the Query Manager.
salary > 500000;
Next, let's look at a method for identifying candidate
tables for Oracle Parallel Query.
Finding Candidate Tables for Oracle Parallel Query
The first step in implementing parallelism for
your database is to locate those large tables that experience frequent
full table scans. Using the access.sql script from Chapter 11,
we can begin by observing the full table scan report that was produced
by analyzing all of the SQL that was in the library cache:
full table scans and counts
Note that "C" indicates in the table is cached.
OWNER NAME NUM_ROWS C K BLOCKS
-------------- ------------------------ ------------ - - --------
SYS DUAL N 2
EMPDB1 PAGE 3,450,209 N 932,120
EMPDB1 RWU_PAGE 434 N 8
EMPDB1 PAGE_IMAGE 18,067 N 1,104
EMPDB1 SUBSCRIPTION 476 N K 192
EMPDB1 PRINT_PAGE_RANGE 10 N K
ARSD JANET_BOOKS 20 N
PERFSTAT STATS$TAB_STATS N
In this report we see several huge tables that are
performing full table scans. For tables that have less than 200 blocks
and are doing legitimate full table scans, we will want to place these
in the KEEP pool. The larger-table full table scans should also be
investigated, and the legitimate large-table full table scans should
be parallelized with the alter table parallel degree nn
CAUTION: The DBA should always investigate
large-table full table scans to ensure that they require more than 40
percent of the table rows before implementing Parallel Query on the
tables. For details on investigating large-table full table scans, see
After we have ensured that the tables are
legitimate large-table full table scans, we can run a script to
generate the parallelization syntax. After running
access_parallel_syntax.sql, we can extract and execute the syntax.
Note that the script here references the sqltemp table that was
created by running access.sql as the schema owner. From this
table, we choose to generate parallelization syntax for all tables
that have more than 1,000 blocks and are currently set to parallel
'alter table '||p.owner||'.'||p.name||' parallel degree 11;'
operation = 'TABLE ACCESS'
options = 'FULL') p
s.addr||':'||TO_CHAR(s.hashval) = p.stid
t.table_name = s.segment_name
t.table_name = p.name
t.owner = p.owner
t.degree = 1
s.blocks > 1000
p.owner, p.name, t.num_rows, s.blocks
Here is the output from this script. Note that we should
manually set the degree of parallelism before running this output in
alter table EMPDB1.PAGE parallel degree 11;
alter table EMPDB1.PAGE_IMAGE parallel degree 11;
Remember, it is not a great problem if the
wrong table is marked for Parallel Query. Oracle Parallel Query only
works when a full table scan is invoked, so a table that never has
full table scans would remain unaffected by Oracle Parallel Query.
Next, let's move on and look at Oracle Parallel
DML commands and see how they can help the Oracle DBA improve the
speed of database maintenance activities.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts.