Share pool 10: Simulate ORA-4031 errors

ORA-4031 error.

ORA-04031 error is because the continuous memory cannot be allocated, it may be that the shared pool memory fragments are serious, or it may be that the memory is indeed insufficient.
The cause of this situation is: frequent and more resolution-requiring more memory space and more serious memory fragments.
Session_cached_cursors is too high, too many cached cursors -- occupying a large amount of library cache memory, may cause ORA-4031.

Example of sharding in a shared pool:

When the ORA-4031 error is reported, it is possible to use the following SQL statement to view, found that the available memory is large enough,
SQL> select POOL, NAME, BYTES from v $ sgastat whereNAME = 'free memory ';
In fact, many chunks of the recreatable type have been released before Oracle releases the 4031 error. Therefore, a lot of available memory will be generated. However, none of these available chunks can provide the required memory space with continuous physical memory.
There is a reserved area in the shared pool, which is set by the shared_pool_reserved_size parameter. The default value is 5% of the shared pool space.
SQL> show parameter shared_pool_reserved_size
SQL> select request_misses from v $ shared_pool_reserved;
This parameter indicates the total number of times that the chunk cannot be obtained from the reserved area. Try to set this parameter to 0.
For very large objects, space is usually allocated from this region.
This is an independently managed region. The chunk in this region will not be attached to the linked list of the normal region, and the chunk of the normal region will not be attached to the linked list of this region.

The SQL statement must be cached in the CHUNK of the Shared Pool. Assume that the text and execution plan of the SQL statement need to be 4 kb in total.
When searching for available chunks, first enter the corresponding bucket to search for them,
If 4 k chunk is not found, it is switched to the next non-empty bucket. Assume that a 5 k chunk is found;
At this time, 4 K will be used to store the text and execution plan of the SQL statement. The remaining 1 K will become a new CHUNK and enter the corresponding bucket and FREE LIST.
In the long run, a large number of small chunks such as 1 K/2 K may be generated, with a large total space. However, if an SQL statement requires a CHUNK such as 4 K, it cannot request the required CHUNK, a Shared Pool fragment is generated, causing a ORA-04031 error.
View the chunk size in the Shared Pool. If <1 K is large, the chunk size may be severe. --- Generally, when there are more than 2000 chunks in each bucket, it is considered that there are too many fragments in the shared pool.
Col sga_heap format a15
Col size format a10
Select KSMCHIDX "SubPool", 'sgaheap ('| KSMCHIDX |', 0) 'sga _ heap, ksmchcom ChunkComment,
Decode (round (ksmchsiz/1000), 0, '0-1k', 1, '1-2k', 2, '2-3k', 3, '3-4k ',
4, '4-5k', 5, '5-6k', 6, '6-7k', 7, '7-8k', 8,
'8-9k', 9, '9-10k', '> 10k') "size ",
Count (*), ksmchcls Status, sum (ksmchsiz) Bytes
From x $ ksmsp
Where KSMCHCOM = 'free memory'
Group by ksmchidx, ksmchcls,
'Sga heap ('| KSMCHIDX |', 0) ', ksmchcom, ksmchcls, decode (round (ksmchsiz/1000), 0, '0-1k ',
1, '1-2K ', 2, '2-3K', 3, '3-4K ', 4, '4-5k', 5, '5-6k ', 6,
'6-7k', 7, '7-8k', 8, '8-9k', 9, '9-10k', '> 10k ');
###################

To search for a CHUNK in a shared pool, follow these steps:

1. First find the subheap free list
2. Find the sub-heap lru
3. Allocate chunk from the parent heap as the new extent of the child heap.
4. Search for free list in the parent Stack
5. Search for LRU in the parent Stack
6. Use hide free space
7. If the above steps fail, the report is 0RA-04031
The parent heap must hold the shared pool latch.
Sub-heap operations with mutex/library cache pin protection

Simulate ORA-04031 error:

First, modify the open_cursors parameter.
BYS @ bys3> show parameter cursors
NAME TYPE VALUE
-----------------------------------------------------------------------------
Open_cursors integer 50
Session_cached_cursors integer 2
BYS @ bys3> alter system set open_cursors = 50000;
System altered.
BYS @ bys3> show parameter shared_pool
NAME TYPE VALUE
-----------------------------------------------------------------------------
Shared_pool_reserved_size big integer 7 M
Shared_pool_size big integer 176 M
Re-login session:
Only open the cursor, not close: select deptno from dept where deptno = the object field of this statement must be executed
Declare
Msql varchar2 (100 );
Mcur number;
Mstat number;
Jg varchar2 (2000 );
Cg number;
Begin for I in 1 .. 10000 loop
Mcur: = dbms_ SQL .open_cursor;
Msql: = 'select deptno from dept where deptno = '| I;
Dbms_ SQL .parse (mcur, msql, dbms_ SQL .native );
Mstat: mongodbms_ SQL .exe cute (mcur );
End loop;
End;
/
Error:
Declare
*
ERROR at line 1:
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool", "unknownobject", "SQLA", "tmp ")
ORA-06512: at "SYS. DBMS_ SQL", line 1199
ORA-06512: at line 10
An error may also be reported: the main reason is unable to allocate1040 bytes, which is sometimes different. The last 16-bytes statement may require 16 bytes, but from the parent heap distribution area, it needs to be allocated by a CHUNK. The CHUNK size is not displayed.
Declare
*
ERROR at line 1:
ORA-04031: unable to allocate 1040 bytes of shared memory ("sharedpool", "select
O "," PLDIA ^ 191e0a8d "," PAR. C: parapt: Page ")
ORA-06508: PL/SQL: cocould not find program unit being called: "SYS. DBMS_ SQL"
ORA-06512: at line 8
An error is returned when other queries are executed:
BYS @ bys3> select * from dept where deptno = 44;
Select * from dept where deptno = 44
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 136 bytes of shared memory ("sharedpool", "select/* + rule */
Bucket_cn... "," SQLA ^ 337fc737 "," kccdef: qkxrMemAlloc ")


Elapsed: 00:00:10. 99
BYS @ bys3> show parameter shared_pool
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool", "unknown
Object "," KGLH0 ^ 92c529c4 "," kglHeapInitialize: temp ")

You can also see a large number of error logs in the warning log:

Mon Feb 03 20:20:44 2014
Errors in file/u01/diag/rdbms/bys3/bys3/trace/bys3_mmon_10113.trc (incident = 138413 ):
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool", "unknownobject", "KGLH0 ^ ca490471", "kglHeapInitialize: temp ")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file/u01/diag/rdbms/bys3/bys3/trace/bys3_mmon_10113.trc (incident = 138414 ):
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool", "unknownobject", "KGLH0 ^ ca490471", "kglHeapInitialize: temp ")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
[Oracle @ bys3 ~] $ Tail-n 50 alert_bys3.log
Mon Feb 03 20:20:14 2014
Errors in file/u01/diag/rdbms/bys3/bys3/trace/bys3_m000_10679.trc (incident = 134643 ):
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool", "unknownobject", "KKSSP ^ 37", "kglseshtSegs ")
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file/u01/diag/rdbms/bys3/bys3/trace/bys3_m000_10679.trc:
ORA-04031: unable to allocate 16 bytes of shared memory ("sharedpool", "unknownobject", "KKSSP ^ 37", "kglseshtSegs ")
..............................
Errors in file/u01/diag/rdbms/bys3/bys3/trace/bys3_cjq0_10175.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 528 bytes of shared memory ("sharedpool", "PROPS $", "PLDIA ^ 191e0a8d", "PAR. C: parapt: Page ")

4031 temporary solution to the error is: Kill some sessions or flush shared_pool;

SYS @ bys3> select sid, count (*) from v $ open_cursor group by sid;
Sid count (*)
--------------------
30 7
1 2
28 1
13 3
31 4
14 2
35 3014
33 3
15 7
16 5
Select sid, machine from v $ session;
KILL unimportant sessions based on the machines found

Log on to the system as the flushshared_pool;

--- Not recommended. After the Shared Pool is refreshed, all SQL statements need to be re-parsed to compete for LATCH in the shared pool, and a large amount of parsing will consume a lot of CPU resources.
SYS @ bys3> alter system flush shared_pool;
System altered.
However, a large number of normal SQL statements will be hard parsed for the first time ..
After that, you still need to find SQL statements that generate a large number of hard-parsed statements for optimization.