Shulou(Shulou.com)06/01 Report--

Customer report library, HP-ux B11.31 IA64 FOR Oracle 10.2.0.5.0 RAC system SMS filtering alert log alarm

ORA-00604: error occurred at recursive SQL level 1

ORA-04031:unable to allocate 4120 bytes of shared memory ("shared pool", "select f. Fileboxes, f. Blockblocks, f....", "Typecheck", "kgghteInit")

Ask the customer about SGA, share pool, host resources, etc.

SQL > show parameter sga

NAME TYPE VALUE

-

Lock_sga boolean TRUE

Pre_page_sga boolean FALSE

Sga_max_size big integer 60G

Sga_target big integer 0

SQL > show parameter pool

NAME TYPE VALUE

-

Buffer_pool_keep string

Buffer_pool_recycle string

Global_context_pool_size string

Java_pool_size big integer 512M

Large_pool_size big integer 512M

Olap_page_pool_size big integer 0

Shared_pool_reserved_size big integer 644245094

Shared_pool_size big integer 12G

Streams_pool_size big integer 416M

The total size of SGA is 60G. The total size of pool is 12G. With this intuitive feeling, the 4031 script 4031_OK-ForAll.sql that collects relevant information is immediately sent to the customer to collect the current memory usage. The script content is as follows:

/ *

* File: 4031.sql

* Date: 2012-01-1

*

* Modifications:

* 2012-02-12 Changed v1

* * /

Spool spinfo.txt

SET PAGESIZE 1024

SET LINESIZE 2000

Set echo off

Set feedback off

Set heading on

Set trimout on

Set trimspool on

COL BYTES FORMAT 999999999999999

COL CURRENT_SIZE FORMAT 999999999999999

/ * Script Run TimeStamp * /

Set serveroutput on

Exec dbms_output.put_line ('Script Run TimeStamp')

Select to_char (sysdate, 'dd-MON-yyyy hh34:mi:ss') "Script Run TimeStamp" from dual

Set serveroutput on

Exec dbms_output.put_line ('Instance Startup Time')

/ * Instance Startup time * /

Select to_char (startup_time, 'dd-MON-yyyy hh34:mi:ss') "Instance Startup Time" from v$instance

/ * shared pool related hidden parameter * /

Set serveroutput on

Exec dbms_output.put_line ('shared pool related hidden parameter')

Col name format a40

Col value format a80

Select nam.ksppinm NAME,val.KSPPSTVL VALUE from x$ksppi nam,x$ksppsv val where nam.indx = val.indx and nam.ksppinm like'% shared%' order by 1

/ * SUB Pool Number * /

Set serveroutput on

Exec dbms_output.put_line ('SUB Pool Number')

Col 'Parameter' format A40

Col 'Session Value' format A40

Col 'Instance Value' format A40

Select a.ksppinm "Parameter"

B.ksppstvl "Session Value"

C.ksppstvl "Instance Value"

From sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c

Where a.indx = b.indx and a.indx = c.indx

And a.ksppinm like'% _ kghdsidx_count%'

/ * Each Subpool Size * /

Set serveroutput on

Exec dbms_output.put_line ('Each Subpool Size')

Select ksmchidx poolnumer, sum (ksmchsiz) poolsize

From x$ksmsp

Group by ksmchidx

/ * Researved Shared Pool 4031 information * /

Set serveroutput on

Exec dbms_output.put_line ('Researved Shared Pool 4031 information')

Select REQUEST_FAILURES, LAST_FAILURE_SIZE from V$SHARED_POOL_RESERVED

/ * Reaserved Shared Pool Reserved 4031 information * /

Set serveroutput on

Exec dbms_output.put_line ('Reaserved Shared Pool 4031 information')

Select REQUESTS, REQUEST_MISSES, free_space, avg_free_size, free_count, max_free_size from V$SHARED_POOL_RESERVED

/ * Current SGA Buffer & Pool sizes * /

Set serveroutput on

Exec dbms_output.put_line ('Current SGA Buffer Pool sizes')

Select component, current_size from v$sga_dynamic_components

/ * Shared Pool Memory Allocations by Size * /

Set serveroutput on

Exec dbms_output.put_line ('Shared Pool Memory Allocations by Size')

Select name, bytes from v$sgastat

Where pool = 'shared pool' and (bytes > 999999 or name =' free memory')

Order by bytes desc

Set serveroutput on

Exec dbms_output.put_line ('show component of shared pool which is bigger than 10MB')

Select name, round ((bytes/1024/1024), 0) "more than 10" from v$sgastat where pool='shared pool' and bytes > 10000000 order by bytes desc

Select sum (bytes) "SHARED POOL TOTAL SIZE" from v$sgastat where pool='shared pool'

/ * Total Free of Shared Pool * /

Set serveroutput on

Exec dbms_output.put_line ('Total Free (not Free) of Shared Pool')

COL 'Total Shared Pool Usage' FORMAT 9999999999999999

Select sum (bytes) / 1024 bind 1024 "Free MB in Shared Pool" from v$sgastat where pool = 'shared pool' and name =' free memory'

Select sum (bytes) "Not Free MB Shared Pool" from v$sgastat where pool = 'shared pool' and name! =' free memory'

/ * current KGLH* usage * /

Set serveroutput on

Exec dbms_output.put_line ('current KGLH* usage')

Select name, bytes from v$sgastat where pool = 'shared pool' and name in (' KGLHD','KGHL0')

/ * Hisotry KGLH* usage * /

Set serveroutput on

Exec dbms_output.put_line ('Hisotry KGLH* usage')

Select bytes/1024/1024, s.snap_id, begin_interval_time START_TIME

From dba_hist_sgastat g, dba_hist_snapshot s

Where name='KGLHD'

And pool='shared pool'

And trunc (begin_interval_time) > = '30murmurDECMur2011'

And s.snap_id = g.snap_id

Order by 2

Set serveroutput on

Exec dbms_output.put_line ('Hisotry KGLH0* usage')

Select bytes/1024/1024, s.snap_id, begin_interval_time START_TIME

From dba_hist_sgastat g, dba_hist_snapshot s

Where name='KGLH0'

And pool='shared pool'

And trunc (begin_interval_time) > = '30murmurDECMur2011'

And s.snap_id = g.snap_id

Order by 2

/ * History of Shared pool allocations in a speciifed Day*/

Set serveroutput on

Exec dbms_output.put_line ('history of Shared pool allocations in a speciifed Day')

Col name format a30

Select n

Max (decode (to_char (begin_interval_time, 'hh34'), 1 bytes, null)) "1"

Max (decode (to_char (begin_interval_time, 'hh34'), 2 bytes, null)) "2"

Max (decode (to_char (begin_interval_time, 'hh34'), 3 bytes, null)) "3"

Max (decode (to_char (begin_interval_time, 'hh34'), 4 bytes, null)) "4"

Max (decode (to_char (begin_interval_time, 'hh34'), 5 bytes, null)) "5"

Max (decode (to_char (begin_interval_time, 'hh34'), 6 bytes, null)) "6"

Max (decode (to_char (begin_interval_time, 'hh34'), 7 bytes, null)) "7"

Max (decode (to_char (begin_interval_time, 'hh34'), 8 bytes, null)) "8"

Max (decode (to_char (begin_interval_time, 'hh34'), 9 bytes, null)) "9"

Max (decode (to_char (begin_interval_time, 'hh34'), 10 bytes, null)) "10"

Max (decode (to_char (begin_interval_time, 'hh34'), 11 bytes, null)) "11"

Max (decode (to_char (begin_interval_time, 'hh34'), 12 bytes, null)) "12"

Max (decode (to_char (begin_interval_time, 'hh34'), 13 bytes, null)) "13"

Max (decode (to_char (begin_interval_time, 'hh34'), 14 bytes, null)) "14"

Max (decode (to_char (begin_interval_time, 'hh34'), 15 bytes, null)) "15"

Max (decode (to_char (begin_interval_time, 'hh34'), 16 bytes, null)) "16"

Max (decode (to_char (begin_interval_time, 'hh34'), 17 bytes, null)) "17"

Max (decode (to_char (begin_interval_time, 'hh34'), 18 bytes, null)) "18"

Max (decode (to_char (begin_interval_time, 'hh34'), 19 bytes, null)) "19"

Max (decode (to_char (begin_interval_time, 'hh34'), 20 bytes, null)) "20"

Max (decode (to_char (begin_interval_time, 'hh34'), 21 bytes, null)) "21"

Max (decode (to_char (begin_interval_time, 'hh34'), 22 bytes, null)) "22"

Max (decode (to_char (begin_interval_time, 'hh34'), 23 bytes, null)) "23"

Max (decode (to_char (begin_interval_time, 'hh34'), 24 bytes, null)) "24"

From (select'"'| | name | |'" n, begin_interval_time, bytes from dba_hist_sgastat a, dba_hist_snapshot b

Where pool='shared pool' and a.snap_id=b.snap_id

And to_char (begin_interval_time,'hh34:mi') between '01purl 00' and' 24ly00'

And to_char (begin_interval_time,'dd-mon') = to_char (sysdate-1, 'dd-mon'))

Group by n

/ * Each Subpool sumary usage for free memory, may slow, it depends on custoemr database workload * /

Set serveroutput on

Exec dbms_output.put_line ('Each Subpool sumary usage for free memory')

Col subpool format a20

Col name format a40

SELECT

Subpool

, name

, SUM (bytes)

, ROUND (SUM (bytes) / 1048576 MB)

FROM (

SELECT

'shared pool (' | | DECODE (TO_CHAR (ksmdsidx), 0-Unused',ksmdsidx) | |'): 'subpool

, ksmssnam name

, ksmsslen bytes

FROM

X$ksmss

WHERE

Ksmsslen > 0

AND LOWER (ksmssnam) LIKE LOWER ('% free memory%')

)

GROUP BY

Subpool

, name

ORDER BY

Subpool ASC

, SUM (bytes) DESC

/ * Memory fragment and chunk allocation like 0-1K, 1-2K, may slow, it depends on custoemr database workload * /

Set serveroutput on

Exec dbms_output.put_line ('Memory fragment and chunk allocation like 0-1K ~ 1-2K')

Col SubPool format 999

Col mb format 999999

Col name heading "Name"

SELECT ksmchidx "SubPool"

'sga heap ('| | ksmchidx | |', 0) 'sga_heap

Ksmchcom chunkcomment

DECODE (ROUND (ksmchsiz / 1000)

0

'0-1K'

one,

'1-2K'

two,

'2-3K'

three,

'3-4K'

four,

'4-5K'

five,

'5-6k'

six,

'6-7k'

seven,

'7-8k'

eight,

'8-9k'

nine,

'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'

one,

'1-2K'

two,

'2-3K'

three,

'3-4K'

four,

'4-5K'

five,

'5-6k'

six,

'6-7k'

seven,

'7-8k'

eight,

'8-9k'

nine,

'9-10k'

'> 10K')

Select to_char (sysdate,'YYYY-MM-DD HH24:MI:SS') "Script END TimeStamp" from dual

Spool off

The result after execution is:

NAME VALUE

-

_ _ shared_pool_size 12884901888

_ all_shared_dblinks

_ dm_max_shared_pool_pct 1

_ enable_shared_pool_durations FALSE

_ io_shared_pool_size 4194304

_ shared_pool_max_size 0

_ shared_pool_minsize_on FALSE

_ shared_pool_reserved_min_alloc 4400

_ shared_pool_reserved_pct 5

_ shared_server_spare_param1

_ shared_server_spare_param2

_ shared_server_spare_param3

_ skgxp_shared_port 0

Hi_shared_memory_address 0

Max_shared_servers

Shared_memory_address 0

Shared_pool_reserved_size 644245094

Shared_pool_size 12884901888

Shared_server_sessions

Shared_servers 0

ERROR:

ORA-04031: unable to allocate 48 bytes of shared memory ("shared pool", "BEGIN DBMS_OUTPUT.ENABLE (NUL...", "parameters", "kglpda")

REQUEST_FAILURES LAST_FAILURE_SIZE

--

5679 4200

Memory greater than _ SHARED_POOL_RESERVED_MIN_ALLOC in shared pool is placed in the shared pool reserved pool, which maintains a separate freelist,lru and does not store recreatable type chunks in the lru list. The release of ordinary shared pool has nothing to do with the shared pool reserved pool.

REQUEST_FAILURES > 0 and LAST_FAILURE_SIZE (last requested memory size) select sql_id,child_number,BIND_MISMATCH from v$sql_shared_cursor where sql_id='2g9nykfyk0a95' and BIND_MISMATCH='Y' and rownum select count (*) from v$sql_shared_cursor where sql_id='2g9nykfyk0a95' and BIND_MISMATCH='Y'

COUNT (*)

-

twenty-three

SQL > select position,LAST_CAPTURED,datatype_string from v$sql_bind_capture where sql_id='2g9nykfyk0a95' and rownum SELECT * FROM

2 (SELECT NAME, BYTES/ (1024024) MB

3 FROM V$SGASTAT

4 WHERE POOL = 'shared pool'

5 ORDER BY BYTES DESC)

6 WHERE ROWNUM

SQL >

SQL >

SQL > select * from v$sgastat where name = 'obj stat memo'

POOL NAME BYTES

Shared pool obj stat memo 6244703856

SQL >

SQL >

SQL > jselect * from v$sgastat where name = 'obj stat memo'

SP2-0734: unknown command beginning "jselect *..."-rest of line ignored.

SQL > select * from v$sgastat where name = 'obj stat memo'

POOL NAME BYTES

Shared pool obj stat memo 6245586216

SQL > /

POOL NAME BYTES

Shared pool obj stat memo 6245930952

And obj stat memo has been growing and cannot be released. Tried to use flush share_pool but obj ignored

SQL > alter system flush shared_pool

SQL > select * from v$sgastat where name = 'obj stat memo'

POOL NAME BYTES

Shared pool obj stat memo 6343766208

I had no choice but to turn to support to search and find an article

ORA-04031 With Leak in "OBJ STAT MEMO" Allocations Seen in V$SGASTAT on 10.2.0.5 (document ID 1350050.1) consistent description

CAUSE

On 10.2.0.5 an architectural change was made to switch off the publishing of "obj stat del channel" messages by default. This can lead to excessive growth of "obj stat memo" memory allocation.

SOLUTION

On 10.2.0.5, and only for 10.2.0.5, we have introduced the hidden parameter:

_ disable_objstat_del_broadcast

If you are seeing ORA-04031 related to the symptoms reported then this parameter can be set to FALSE and by doing so we will no longer see the growth of "obj stat memo" that potentially leads to ORA-04031.

This parameter has been instructed by development to be used as the solution to ORA-04031 with the symptoms reported. There is no patch fix and no patch fix will be made. The hidden parameter will not cause any problems to the database and it must not be accidentally left within the init/spfile when/if the database is upgraded as startup would fail with:

Set the original hidden trouble parameter to false

ALTER SYSTEM SET "_ disable_objstat_del_broadcast" = FALSE SCOPE=BOTH

The false of _ disable_objstat_del_broadcast will not affect the database and can be modified, but please note that if the database is upgraded later, you need to cancel the parameter in the parameter file, otherwise the database will not start normally.

Because it has not been released, after the database instance is restarted, the resource is monitored and released.

SQL > select * from v$sgastat where name = 'obj stat memo'

POOL NAME BYTES

Shared pool obj stat memo 102600

SQL > /

POOL NAME BYTES

Shared pool obj stat memo 143640

Summary: 1, mainly to share the relevant scripts.

2. Share the ideas to solve the problem.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.