BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter