 |
|
Finding Candidate Tables for Oracle Parallel
Query
Oracle Tips by Burleson Consulting |
To enable parallel query, the Remote DBA will locate
those SQL statements that participate in full-table scans and then
ensure that these queries utilize parallel query. This is generally
done by adding the full and parallel hints to the SQL and making the
change persistent with optimizer plan stability or by changing the
SQL source code to include the hints.
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 6, 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:
Mon Jan
29 page 1
full table scans and counts
Note that "C" indicates that the table is cached.
“K" indicates that the table is in the KEEP Pool.
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_RANG 10 N K 32
874
ARSP JANET_BOOKS 20 N 8
64
PERFSTAT STATS$TAB_STATS N 65
10
In the preceding 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 tables in the KEEP pool. The large-table full-table
scans should also be investigated, and the legitimate large-table
full-table scans should be parallelized by adding a parallel
hint to the SQL statement.
Caution: The Remote DBA should always investigate
large-table full-table scans to ensure that they requires more than
40 percent of sequenced table blocks or 7 percent of unsequenced
table blocks before implementing parallel query on the tables.
Using the KEEP Pool
For all tables that are small (i.e., those
where you have enough db_block_buffers to hold all of the
blocks in the table), you should always use the KEEP pool to cache
the table rows. The threshold for the number of blocks in the table
depends upon the size of your db_block_buffers, since you
must increase the size of buffer_pool_keep every time you add
a table to the KEEP pool. For example, if you add a table with 400
blocks to the KEEP pool, you must increase buffer_pool_keep
by 400. Of course, this increase will decrease the number of
available blocks in the DEFAULT pool by 400 blocks, so you may also
want to increase the db_block_buffers.
Placing small tables in the KEEP pool is analogous to the table
caching option in Oracle7, and it can dramatically improve the speed
of small table full-table scans because there will be no physical
disk I/O. Of course, Oracle parallel query will not improve the
speed of small table full-table scans, especially if all of the
table blocks reside in the KEEP pool.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
