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


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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

ps –ef|grep "ora_p"

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter