溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
客戶報表庫，HP-ux B11.31 IA64 FOR Oracle 10.2.0.5.0 RAC系統短信過濾alert日志告警
ORA-00604: error occurred at recursive SQL level 1
ORA-04031:unable to allocate 4120 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","Typecheck","kgghteInit")
詢問客戶SGA、share pool、主機資源等相關情況
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
SGA總大小60G，share pool總大小12G。有了這個直觀的感受，隨即將4031收集相關信息的腳本4031_OK-ForAll.sql發給客戶，收集現在內存使用情況，腳本內容如下：
/**********************************************************************
* 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 999999999999999
select sum(bytes)/1024/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) >= '30-DEC-2011'
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) >= '30-DEC-2011'
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 '01:00' and '24:00'
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,2) MB
FROM (
SELECT
'shared pool ('||DECODE(TO_CHAR(ksmdsidx),'0','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 999,999
col name heading "Name"
SELECT ksmchidx "SubPool",
'sga heap(' || 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');
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "Script END TimeStamp" from dual;
spool off;
執行后結果為：
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
shared pool中內存大于_SHARED_POOL_RESERVED_MIN_ALLOC 將放入shared pool保留池,保留池維護一個單獨的freelist,lru，并且不會在lru列表存recreatable類型chunks，普通shared pool的釋放與shared pool保留池無關。REQUEST_FAILURES>0且LAST_FAILURE_SIZE(最后請求內存大小)<_SHARED_POOL_RESERVED_MIN_ALLOC,表示在 shared pool中缺少連續內存,或者庫里面有大量的硬解析造成的。一般是綁定變量問題。也就是version count過高。我們順著這個思路往下繼續查看；
也就是說兩個sql（7ng34ruy5awxq、2g9nykfyk0a95）有重大影響；
SQL> select sql_id,child_number,BIND_MISMATCH from v$sql_shared_cursor where sql_id='2g9nykfyk0a95' and BIND_MISMATCH='Y' and rownum<10;
SQL_ID CHILD_NUMBER B
------------- ------------ -
2g9nykfyk0a95 4 Y
2g9nykfyk0a95 5 Y
2g9nykfyk0a95 30 Y
2g9nykfyk0a95 0 Y
2g9nykfyk0a95 54 Y
2g9nykfyk0a95 23 Y
2g9nykfyk0a95 27 Y
2g9nykfyk0a95 35 Y
2g9nykfyk0a95 46 Y
9 rows selected.
SQL> select count(*) from v$sql_shared_cursor where sql_id='2g9nykfyk0a95' and BIND_MISMATCH='Y' ;
COUNT(*)
----------
23
SQL>select position,LAST_CAPTURED,datatype_string from v$sql_bind_capture where sql_id='2g9nykfyk0a95' and rownum<50
POSITION LAST_CAPTURE DATATYPE_STRING
---------- ------------ ------------------------------
1 VARCHAR2(128)
2 VARCHAR2(128)
3 VARCHAR2(128)
4 VARCHAR2(32)
5 VARCHAR2(128)
6 VARCHAR2(32)
7 TIMESTAMP
8 TIMESTAMP
9 VARCHAR2(32)
10 VARCHAR2(32)
11 VARCHAR2(32)
12 VARCHAR2(128)
13 VARCHAR2(128)
14 VARCHAR2(32)
15 VARCHAR2(32)
16 VARCHAR2(32)
17 VARCHAR2(32)
18 VARCHAR2(32)
19 VARCHAR2(32)
20 VARCHAR2(32)
21 VARCHAR2(32)
22 VARCHAR2(32)
23 VARCHAR2(32)
24 VARCHAR2(32)
25 VARCHAR2(32)
26 VARCHAR2(32)
27 VARCHAR2(32)
28 VARCHAR2(32)
29 VARCHAR2(32)
30 VARCHAR2(32)
31 NUMBER
1 VARCHAR2(128)
2 VARCHAR2(128)
3 VARCHAR2(128)
4 VARCHAR2(32)
5 VARCHAR2(128)
6 VARCHAR2(32)
7 TIMESTAMP
8 TIMESTAMP
9 VARCHAR2(32)
10 VARCHAR2(32)
11 VARCHAR2(32)
12 VARCHAR2(128)
13 VARCHAR2(128)
14 VARCHAR2(32)
15 VARCHAR2(32)
16 VARCHAR2(32)
17 VARCHAR2(2000)
18 VARCHAR2(32)
49 rows selected.
禁用相關應用后，接著往下分析，看看還有沒有別的地方引起的該4031問題；
NAME BYTES
---------------------------------------- ----------------
obj stat memo 6235601184
free memory 1919164576
object level 1148667072
gcs resources 980982312
gcs shadows 426063424
sql area 401151376
db_block_hash_buckets 188743680
kglsim object batch 179096400
kglsim heap 173694528
CCursor 139486384
Cursor Stats 131995544
ges resource 110674136
library cache 101986224
PCursor 88613936
ges enqueues 87640800
sql area:PLSQL 78693424
ASH buffers 52428800
trace buffer 40927232
KQR L PO 36581592
Checkpoint queue 32776192
state objects 30602616
event statistics per sess 26095360
FileOpenBlock 15936504
ges big msg buffers 15936168
sessions 15163528
KCL name table 12582912
kgllk hash table 10231808
KGLS heap 10113784
simulator hash buckets 8404992
dbwriter coalesce buffer 8392704
gcs res hash bucket 8388608
ges reserved msg buffers 8240008
Heap0: KGL 7905976
object queue 7894320
row cache 7511248
transaction 6885376
KQR L SO 5958168
enqueue 5886080
parameter table block 5331280
procs: ksunfy 5120000
FileIdentificatonBlock 4571216
call 4535640
KCB Table Scan Buffer 4194816
kglsim hash table bkts 4194304
KSFD SGA I/O b 4190328
buffer handles 3600008
DML lock 3541016
KQR M SO 3300680
gcs affinity 3241728
ges process array 3181272
ges resource hash table 2883584
PL/SQL DIANA 2771128
trace buf hdr xtend 2736864
PL/SQL MPCODE 2626400
ges regular msg buffers 2622008
KTI SGA freea 2498560
KGSK scheduler 2358624
ktlbk state objects 2108880
object queue hash buckets 2101248
enqueue resources 1953128
replication session stats 1939520
SGA - SWRF Metric CHBs 1857960
db_files 1777912
kks stbkt 1572864
KEWS sesstat values 1432600
Wait History 1322400
pso tbs: ksunfy 1300000
Sort Segment 1272848
osp allocation 1195984
mvobj part des 1110240
KSXR receive buffers 1036000
SUBPOOL NAME SUM(BYTES) MB
-------------------- ---------------------------------------- ---------- ----------
shared pool (1): free memory 259671360 247.64
shared pool (2): free memory 252015608 240.34
shared pool (3): free memory 277114712 264.28
shared pool (4): free memory 275504440 262.74
shared pool (5): free memory 281692368 268.64
shared pool (6): free memory 268028160 255.61
shared pool (7): free memory 304926192 290.8
期間我們看見obj stat memo排名第一，消耗內存資源5.9G。七個子池，大概share pool剩余空間1.83GB
SQL> SELECT * FROM
2 (SELECT NAME, BYTES/(1024*1024) MB
3 FROM V$SGASTAT
4 WHERE POOL = 'shared pool'
5 ORDER BY BYTES DESC)
6 WHERE ROWNUM <= 10;
NAME MB
-------------------------- ----------
obj stat memo 5955.40194
free memory 2503.24937
object level 1097.0509
gcs resources 935.537636
gcs shadows 406.325745
db_block_hash_buckets 180
kglsim object batch 170.799637
kglsim heap 165.64801
Cursor Stats 125.88076
ges resource 104.992348
SQL>
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
且obj stat memo一直在增長，無法釋放。嘗試使用flush share_pool但是obj無視
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
無解只好求助support進行搜索，找到一篇
ORA-04031 With Leak in "OBJ STAT MEMO" Allocations Seen in V$SGASTAT on 10.2.0.5 (文檔 ID 1350050.1)描述一致；
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 :
將原隱患參數設置false；
ALTER SYSTEM SET "_disable_objstat_del_broadcast"=FALSE SCOPE=BOTH;
_disable_objstat_del_broadcast為false并不會對數據庫造成影響，可以修改，但是請注意假如之后數據庫進行升級，需要在參數文件中取消該參數，否則數據庫無法正常啟動。
因為一直沒有釋放，將數據庫實例重啟后，監控該資源情況，得到釋放；
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
總結：1，主要是分享一下相關腳本。
2，分享一下解決故障的思路問題。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
