_enable_shared_pool_durations – Init.ora parm

Recently we had multiple ora-445 and ora-7445 database crashes.  After many hours with Oracle support they suggested many things (Oracle 11gR2 – Linux)

Use Sqlnet.ora – set sqlnet.expire_time=5 = to activate DCD (Dead Connection Detection)

Enable “_enable_shared_pool_durations”=false (Default is True)

and smaller shared_pool_size, session_cached_cursors and shared_pool_reserved_size (10% of shared_pool_size)

What is _enable_shared_pool_durations:

What is the meaning of “_enable_shared_pool_durations” parameter?

ANSWER:
==============
With sga_target set, the shared pool and the streams pool have of subpools subpools for 4 durations.
The durations are “instance”, “session”, “cursor”, and “execution”.
The main advantage of “_enable_shared_pool_durations = false” is that all the durations are combined into one pool and so a duration will not run out while another duration has free memory. This is true for both the shared pool and the streams pool.

The disadvantage of the setting is that neither the streams pool nor the shared pool can shrink. (Without the setting, only the execution duration of the shared pool is eligible to shrink.) The setting does not affect the large pool because the large pool is not divided into durations.

With sga_target set, all the pools grow by transferring granules from the buffer cache. Shrinks if enabled go back to the buffer cache. There is no direct transfer from one pool to another pool or from one duration to another duration. All transfers involve the buffer cache as source or target.

Note that “_kghdsidx_count=4” creates four “list-set” pools (standard subpools) for the each of the shared pool, large pool, and streams pool. Each list-set has its own kgh latch. The list-set pools are orthogonal to the duration subpools. So if sga_target is set and durations are not disabled, the shared pool and the streams pool have 16 subpool/duration combinations, i.e. 4 subpools * 4 durations.

Languagesfr>en YahooCEerror
Recently we had multiple ora-445 and ora-7445 database crashes. After many hours with Oracle support they suggested many things (Oracle 11gR2 – Linux)Use Sqlnet.ora – set sqlnet.expire time=5 = to activate DCD (Dead Connection Detection)Enable enable shared pool durations =false (Default is True)and smaller shared pool size, session cached cursors and shared pool reserved size (10% of shared pool size)What is enable shared pool durations:
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s