 |
|
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.
rpt_parallel.sql
L 12-13
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
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 full table scans are being
invoked.
L 12-14
SQL> @rpt_parallel
TO_CHAR(SNAP_ 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.
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 table:
L 12-15
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.
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:
L 12-16
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:
L 12-17
create view all_employee as
select * from new_york_employee@manhattan
UNION ALL
select * from california_employee@los_angeles
UNION ALL
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.
L 12-18
select
employee_name
from
all_employee
where
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:
L 12-19
Mon Jan
29
page 1
full table scans and counts
Note that "C" indicates in the table is cached.
OWNER NAME NUM_ROWS C K BLOCKS
NBR_FTS
-------------- ------------------------ ------------ - - --------
--------
SYS DUAL N 2
97,237
EMPDB1 PAGE 3,450,209 N 932,120
9,999
EMPDB1 RWU_PAGE 434 N 8
7,355
EMPDB1 PAGE_IMAGE 18,067 N 1,104
5,368
EMPDB1 SUBSCRIPTION 476 N K 192
2,087
EMPDB1 PRINT_PAGE_RANGE 10 N K
32 874
ARSD JANET_BOOKS 20 N
8 64
PERFSTAT STATS$TAB_STATS N
65 10
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
command.
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
Chapter 11.
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
degree 1.
access_parallel_syntax.sql
L 12-20
select
'alter table '||p.owner||'.'||p.name||' parallel degree 11;'
from
dba_tables t,
dba_segments s,
sqltemp s,
(select distinct
statement_id stid,
object_owner owner,
object_name name
from
plan_table
where
operation = 'TABLE ACCESS'
and
options = 'FULL') p
where
s.addr||':'||TO_CHAR(s.hashval) = p.stid
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.degree = 1
having
s.blocks > 1000
group by
p.owner, p.name, t.num_rows, s.blocks
order by
sum(s.executions) desc;
Here is the output from this script. Note that we should
manually set the degree of parallelism before running this output in
SQL*Plus.
L 12-21
SQL> @access_parallel_syntax
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. |
|
