DATABASE CRASH WITH SGA_TARGET 문서 ID: 747812.1

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.5 to 10.2.0.4
This problem can occur on any platform.

Symptoms

Database can crash many times if Automatic Shared Memory Management (ASMM) is used.

Database crashes many times with the following messages from alert_log:-

Tue Oct 28 13:20:35 2008
Errors in file /ora/product/10.2.0prd/admin/oradwprd/bdump/oradwprd_cjq0_1990.trc:
Tue Oct 28 13:22:03 2008
System State dumped to trace file
Tue Oct 28 13:41:45 2008
MMNL absent for 1310 secs; Foregrounds taking over
Tue Oct 28 13:41:52 2008
MMNL absent for 1256 secs; Foregrounds taking over
MMNL absent for 1256 secs; Foregrounds taking over
MMNL absent for 1256 secs; Foregrounds taking over
Tue Oct 28 14:25:28 2008
...
Tue Oct 28 20:16:50 2008
ksvcreate: Process(m000) creation failed
Tue Oct 28 20:21:32 2008
MMNL absent for 1311 secs; Foregrounds taking over
Tue Oct 28 20:45:05 2008

The AWR report during the time the crash happens shows the following:-

Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 1,280M 1,568M Std Block Size: 8K
Shared Pool Size: 720M 432M Log Buffer: 15,148K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,031.15 6,487.12
Logical reads: 15,843.66 99,674.56
Block changes: 3.61 22.70
Physical reads: 1,975.69 12,429.32
Physical writes: 454.90 2,861.83
User calls: 53.04 333.65
Parses: 2.14 13.45
Hard parses: 0.44 2.79
Sorts: 1.91 12.00
Logons: 0.03 0.18
Executes: 3.74 23.55
Transactions: 0.16

% Blocks changed per Read: 0.02 Recursive Call %: 43.94
Rollback per transaction %: 7.99 Rows per Sort: ########

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 97.12 Redo NoWait %: 100.00
Buffer Hit %: 94.80 In-memory Sort %: 99.10
Library Hit %: 84.79 Soft Parse %: 79.23
Execute to Parse %: 42.89 Latch Hit %: 95.30
Parse CPU to Parse Elapsd %: 10.35 % Non-Parse CPU: 62.73

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 14.69 25.80
% SQL with executions>1: 83.47 43.38
% Memory for SQL w/exec>1: 69.04 43.09

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
latch: library cache 125 14,869 ###### 33.3 Concurrenc
latch: shared pool 553 13,177 23828 29.5 Concurrenc
read by other session 1,653,348 8,455 5 18.9 User I/O
CPU time 4,353 9.7
db file sequential read 841,956 3,000 4 6.7 User I/O

Cause

Huge contention for shared pool and library cache latches are seen from AWR report during the problem period.

Also the shared pool size is getting shrinked during the problem as evident from AWR.

This is because of dynamic memory allocation with in SGA.

As Automatic SGA is used, the repeated shrink / growth in the shared pool and buffer cache would happen and that could cause lot of waits in sessions.

Because of Auto SGA, the shared pool is shrinked during the problem and thats why the contention is seen across the shared pool which caused the database crash.

From the systemstate dumps it is shown Location from where latch is held:  ksucrp:
The function is used to Create and initialise a process.
The process cannot be initialized due to lack of memory.

All the messages seen in alert log are the indication of lack of memory available in shared pool.

Bug 6528336 seems to have caused the problem.

Solution

To disable Auto SGA.

sga_target=0

Configure memory components separately for shared pool and buffer cache.

shared_pool_size=<value>M

db_cache_size=<value>M