March 2013 – Mike Gangler's Musings

In our system we usually get 40 – 50 stateless incidents due to ora-00060 errors (Poorly written software) type issues. In the past it has been very difficult to remove them via the GUI console. I found an article in http://www.freelists.org that helped me fix them – “www.freelists.org/post/oracle-l/Clearing-stateless-events-in-OEM-12c,2“. There is an automated job (Out of the box) to clean them up, and i will be setting this up in my environment. Also, the event for alert logs can be modified, but Im still looking at ways to do that in an easy manner.

Here is a snippet of the code to clear the events:

[oracle@machinename bin]$ ./emcli login -username=sysman
Enter password

Login successful
[oracle@machinename bin]$ ./emcli clear_stateless_alerts -older_than=0 -target_type=oracle_database -target_name=<databasename>

46 alerts were cleared successfully.

[oracle@machine bin]$ ./emcli logout
Logout successful

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: