Today I will be taking up one ORA-4031 issue we faced on one of our client database. Please note that SQL statements mentioned below have been changed and does not reveal any confidential information.
Our client was facing ORA-4031 on a 9.2 database.This was a new Database which had gone Live recently. Following error messages were recorded in alert log.
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","select owner#,name,namespace...","sga heap(1,0)","library cache")
Initial shared pool at time of failure was 160 Mb. After the failure the shared_pool_size was increased to 1 Gb. We did not have any other information and were asked to perform RCA for the same. Luckily we had Statspack configured on the server, so we took a report for the duration when the error occurred.
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 1000 15-Apr-08 06:32:06 ####### 4.6
End Snap: 1001 15-Apr-0806:53:41 ####### 4.6
Elapsed: 21.58 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 2,048M Std Block Size: 8K
Shared Pool Size: 160M Log Buffer: 32,768K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 2,459.98 2,911.95
Logical reads: 6,447.76 7,632.40
Block changes: 8.31 9.83
Physical reads: 3,642.28 4,311.47
Physical writes: 0.80 0.95
User calls: 106.51 126.08
<strong>Parses: 151.48 179.31</strong>
<strong>Hard parses: 11.08 13.12</strong>
Sorts: 91.50 108.31
Logons: 1.41 1.67
Executes: 283.09 335.10
Transactions: 0.84
We can clearly see that lot of hard parses were occurring at the time of error.
Library Cache Activity for DB: TESTDB1 Instance: TESTDB1
Snaps: 1000 -1001->"Pct Misses" should be very low
Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 1,564 1.9 2,903 62.9 469 0
CLUSTER 6,827 0.0 9,011 0.1 0 0
INDEX 9,376 62.8 9,375 63.7 5 0
SQL AREA 77,058 11.0 442,341 5.2 <strong>6,723 </strong> 0
TABLE/PROCEDURE 72,495 10.4 135,173 24.2 <strong>8,291</strong> 0
TRIGGER 2,288 14.9 2,288 49.5 218 0
-------------------------------------------------------------
Also looking at information from V$LIBRARYCACHE, we can see that there were lot of reloads happening for SQL AREA and TABLE/PROCEDURE Namespace. This gives indication that Shared Pool is not appropriately sized. So we kind of expected this to be a shared pool sizing issue.
But after increasing the shared_pool_size to 1 Gb, we saw that memory for “sql area” was increasing. So as to avoid further ORA-4031, we started flushing the shared pool when the free memory reached to 200M.
NAME BYTES
-------------------------- ----------------
<strong>free memory 597,154,376</strong> < -large amount of free, was done after flushing shared pool
<strong>sql area 315,365,096</strong> < - High value
library cache 82,229,616
miscellaneous 81,476,336
gcs resources 39,331,928
gcs shadows 26,133,184
Whenever I observe high memory usage for Sqlarea, I run following sql statements.
1) 10g
<strong><span style="color: #3366ff;">SQL> select sa.sql_text,sa.version_count ,ss.*from v$sqlarea sa,v$sql_shared_cursor ss where<span> </span>sa.address=ss.address and sa.version_count > 50 order by sa.version_count ;</span></strong>
8i/9i
<strong><span style="color: #3366ff;">select sa.sql_text,sa.version_count ,ss.*from v$sqlarea sa,v$sql_shared_cursor ss<span> </span>where sa.address=ss.KGLHDPAR<span> </span>and sa.version_count > 50<span> </span>order by sa.version_count ;</span></strong>
– This one is to find if child cursors are being generated. To know about child cursors, refer to following Metalink note
Note 296377.1 – Handling and resolving unshared cursors/large version_counts
We did not get any SQL in this category.
2) Use script from Asktom website to find if application is using Bind variables. You can find ,more information by clicking here
This returned quite a few statements
INSERT INTO EMP (columnname, columnname, columnname, columnname, columnname, columnname, columnname, columnname, columnname, columnname, columnname ,columnname) VALUES ('#', TO_DATE('#','#'), @, '#',
@, @, @, '#', @, @, @, @, @, @, @, @) <strong>1948</strong>
DELETE FROM EMP WHERE EMPID=@ <strong>1976</strong>
INSERT INTO EMP (columnname, columnname, columnname, columnname) VALUES (@, @, '#', @, NULL , @) <strong>1976</strong>
DELETE FROM EMP WHERE TYPE=@ AND EMPID=@ <strong>1976</strong>
INSERT INTO DEPT VALUES (@, '#', '#', TO_DATE('#','#'), SYSDATE)
<strong>2012</strong>
DELETE FROM DEPT WHERE DEPTNO=@ AND DEPT_ID=@ AND MGR=@ AND SAL='#' <strong>2375</strong>
INSERT INTO SALARY (columnname, columnname, columnname, columnname, columnname, columnname, columnname) VA LUES (@, @, @, '#', '#', @, TO_DATE('#','#'), TO_DATE('#','#'), @, @) <strong>2377</strong>
We can clearly see that statements not being shared and thus causing the high memory usage in shared pool.
Each sql statement in shared pool will be occupying some memory. If the sql are similar and differ only in literal, then they should be shared as otherwise they will occupy lot of extra space. By not sharing the statements, we are wasting space in shared pool and causing shared pool fragmentation.
Solution again is to use Bind variables or CURSOR_SHARING.
Recent Comments