My Oracle Support Banner

Troubleshooting: Understanding and Tuning the Shared Pool (Doc ID 62143.1)

Last updated on JULY 18, 2019

Applies to:

Oracle Database - Personal Edition - Version 7.1.4.0 and later
Oracle Database - Enterprise Edition - Version 7.0.16.0 and later
Oracle Database - Standard Edition - Version 7.0.16.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Purpose

Introduction

The aim of this article is to introduce the key issues involved in tuning the shared pool in Oracle 7 through 12c. The notes here are particularly important if your system shows any of the following:

Ask Questions, Get Help, And Share Your Experiences With This Article

Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?

Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article.
Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Tuning.

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
 Introduction
 Ask Questions, Get Help, And Share Your Experiences With This Article
Troubleshooting Steps
 What is the shared pool ?
 Terminology
 Literal SQL
 Hard Parse
 Soft Parse
 Identical Statements ?
 Sharable SQL
 Versions of a statement
 Library Cache and Shared Pool latches
 Literal SQL versus Shared SQL
 Literal SQL
 Sharable SQL
 Reducing the load on the Shared Pool
 Parse Once / Execute Many
 Eliminating Literal SQL
 Avoid Invalidations
 CURSOR_SHARING parameter (8.1.6 onwards)
 SESSION_CACHED_CURSORS parameter
 CURSOR_SPACE_FOR_TIME parameter
 CLOSE_CACHED_OPEN_CURSORS parameter
 SHARED_POOL_RESERVED_SIZE parameter
 SHARED_POOL_RESERVED_MIN_ALLOC parameter
 SHARED_POOL_SIZE parameter
 _SQLEXEC_PROGRESSION_COST parameter (8.1.5 onwards)
 Precompiler HOLD_CURSOR and RELEASE_CURSOR Options
 Pinning Cursors in the Shared Pool
 DBMS_SHARED_POOL.KEEP
 Flushing the SHARED POOL
 DBMS_SHARED_POOL.PURGE
 Using V$ Views (V$SQL and V$SQLAREA)
 MTS, Shared Server and XA
 Useful SQL for looking at Shared Pool problems
 Issues in various Oracle Releases
 Bug fixes and Enhancements
 Discuss Shared Pool and Library Cache Contention
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.