Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Concepts - Guidelines for Tuning the Oracle Shared Pool

Oracle Tips by Burleson Consulting

In Summary

I hope you now understand that the old "just increase the shared pool" answer isn't good enough anymore when it comes to tuning problems. You must take an in depth look at your shared pool and tune what needs to be tuned, not just throw memory at a problem until it submerges. Indeed, I have shown that in some cases increasing the size of the shared pool may harm performance and decreasing the size may be advisable.  The shared pool is vital to the proper performance of your Oracle database, you must have it properly tuned or drown in bad performance. Next we will cover what to pin, the shared pool and multi-threaded server, hashing and generalized library and dictionary cache tuning. We have also discussed ways to monitor for what objects should be pinned, discussed multi-threaded server, looked at hashing problems and their resolution as well as examined classic library and data dictionary cache tuning. We have established 8 guidelines for tuning the Oracle shared pool:

Guideline 1: If gross usage of the shared pool in a non-ad-hoc environment exceeds 95% (rises to 95% or greater and stays there) establish a shared pool size large enough to hold the fixed size portions, pin reusable packages and procedures. Gradually increase shared pool by 20% increments until usage drops below 90% on the average.

Guideline 2: If the shared pool shows a mixed ad-hoc and reuse environment, establish a shared pool size large enough to hold the fixed size portions, pin reusable packages and establish a comfort level above this required level of pool fill. Establish a routine flush cycle to filter non-reusable code from the pool.

Guideline 3: If the shared pool shows that no reusable SQL is being used establish a shared pool large enough to hold the fixed size portions plus a few megabytes (usually not more than 40) and allow the shared pool modified least recently used (LRU) algorithm to manage the pool. (also see guideline 8)

Guideline 4: Determine usage patterns of packages, procedures, functions and cursors and pin those that are frequently used.

Guideline 5: In Oracle7when using MTS increase the shared pool size to accommodate MTS messaging and queuing as well as UGA requirements. In Oracle8 use the Large Pool to prevent MTS from effecting the shared pool areas.

Guideline 6: Use bind variables, PL/SQL (procedures or functions) and views to reduce the size of large SQL statements to prevent hashing problems.

Guideline 7: In a system where there is no flushing increase the shared pool size in 20% increments to reduce reloads and invalidations and increase object cache hit ratios.

Guideline 8: In any shared pool, if the overall data dictionary cache miss ratio exceeds 1 percent, increase the size of the shared pool.

Using these guidelines and the scripts and techniques covered in this lesson, your should be well on the way towards a well tuned and well performing shared pool.

Table 18: Initialization Parameters That Effect The Shared Pool

NAME

DESCRIPTION

shared_pool_size

size in bytes of shared pool (7 and 8)

shared_pool_reserved_size

size in bytes of reserved area of shared pool (7 and 8)

shared_pool_reserved_min_alloc

minimum allocation size in bytes for reserved area of shared pool (7 and 8)

large_pool_size

size in bytes of the large allocation pool (8 only)

parallel_max_servers

Maximum number of parallel query slaves, if set forces calculation and setting of large pool size parameter (8i)

parallel_adaptive_multi_user

If set forces calculation of large pool size is over-ridden if size manually set(8i).  Oracle DOES NOT recommend setting parallel_adaptive_multi_user.

parallel_automatic_tuning

If set forces calculation of large pool size is over-ridden if size is manually set (8i)

large_pool_min_alloc

minimum allocation size in bytes for the large allocation pool (8 only, obsolete in 8i)

parallel_min_message_pool

minimum size of shared pool memory to reserve for pq servers (8 only, obsolete in 8i)

backup_io_slaves

Number of backup IO slaves to configure (8 only)

temporary_table_locks

Number of temporary table locks to configure (7 and 8)

dml_locks

Number of DML locks to configure (7 and 8)

sequence_cache_entries

Number of sequence numbers to cache (7 and 8)

row_cache_cursors

Number of row caches to set up (7 and 8)

max_enabled_roles

Number of role caches to set up (7 and 8)

mts_dispatchers

Number of MTS dispatcher processes to start with (7 and 8)

mts_max_dispatchers

Maximum number of dispatcher processes to allow (7 and 8)

mts_servers

Number of MTS servers to start with (7 and 8)

mts_max_servers

Maximum number of MTS servers to allow (7 and 8)

open_cursors

Maximum number of open cursors per session (7 and 8)

Cursor_space_for_time

Hold open cursors until process exits (7 and 8)

Table 19: Initialization Parameters Used In Tuning Shared Pool

VIEW NAME

PURPOSE

V$PARAMETER

Contains current settings for all documented initialization parameters

V$SGASTAT

Contains sizing information for all SGA areas

V$SQLAREA

Contains information and statistics on the SQL area of the shared pool

V$DB_OBJECT_CACHE

Contains information on all cached objects in the database shared pool area

V$LIBRARYCACHE

 Contains statistics on the library caches

V$ROWCACHE

Contains statistics on the data dictionary caches

DBA_USERS

Contains database user information

V$BUFFER_POOL

Oracle8 view showing pool areas

V$BUFFER_POOL_STATISTICS

Oracle8 buffer pool statistics

V$BH

View that monitors every buffer in buffer pool

Table 20: Views Dealing With Shared Pool and Buffer Tuning

Software

Manufacturer

Purpose

Oracle Administrator

RevealNet, Inc.

Administration Knowledge base

Q Diagnostic

Savant, Corp.

Provide Oracle DB diagnostics

Table 21: Software Mentioned in Lessons

 


This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.

 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.