久久国产成人av_抖音国产毛片_a片网站免费观看_A片无码播放手机在线观看,色五月在线观看,亚洲精品m在线观看,女人自慰的免费网址,悠悠在线观看精品视频,一级日本片免费的,亚洲精品久,国产精品成人久久久久久久

搜索
分享

ORA-04031一例 ("shared pool","…","SQLA","tmp")

 xfxyxh 2022-09-18 發(fā)布于湖南

今天突然收到告警，一臺11.2.0.4的數(shù)據(jù)庫報錯ORA-04031,。

1
2
SYS@> show sga        
ORA-04031: 無法分配 32 字節(jié)的共享內(nèi)存 ("shared pool","SELECT DECODE(null,'','Total...","SQLA","tmp")

根據(jù)錯誤代碼可以判斷是共享池使用出了問題

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[oracle@xxx trace]$ oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
//          DBMS_SHARED_POOL package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          initialization parameters SHARED_POOL_RESERVED_SIZE and 
//          SHARED_POOL_SIZE.
//          If the large pool is out of memory, increase the initialization
//          parameter LARGE_POOL_SIZE.  
//          If the error is issued from an Oracle Streams or XStream process,
//          increase the initialization parameter STREAMS_POOL_SIZE or increase
//          the capture or apply parameter MAX_SGA_SIZE.

從smon日志中可以看到大量的sga組件等待擴展的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Session Wait History:
    elapsed time of 0.261076 sec since last wait
 0: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=2337078 seq_num=43657 snap_id=101
    wait times: snap=0.000000 sec, exc=5.069536 sec, total=13.674583 sec
    wait times: max=infinite
    wait counts: calls=100 os=100
    occurred after 0.000000 sec of elapsed time
 1: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=2337178 seq_num=43656 snap_id=1
    wait times: snap=0.003531 sec, exc=0.003531 sec, total=0.003531 sec
    wait times: max=infinite
    wait counts: calls=2 os=2
    occurred after 0.000000 sec of elapsed time
 2: waited for 'SGA: allocation forcing component growth'
    =0x0, =0x0, =0x0
    wait_id=2337078 seq_num=43655 snap_id=100
    wait times: snap=0.050112 sec, exc=5.069536 sec, total=13.671052 sec
    wait times: max=infinite
    wait counts: calls=100 os=100
    occurred after 0.000000 sec of elapsed time
...省略

表示sga所分配的內(nèi)存已經(jīng)使用完，不足以支撐組件的擴展,。這個庫使用的sga_target,所以sga的各個組件都能自適應(yīng)分配內(nèi)存大小

1
2
3
PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ------------------------------------------------------
sga_target                                                   big integer 12G

在sga中有6個子池,，每個子池中大部分內(nèi)存都分配給了”KGH: NO ACCESS”,都在1G左右,。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 1
----------------------------------------------
"KGH: NO ACCESS            "       1118 MB 67%
"free memory               "        205 MB 12%
"KGLH0                     "         86 MB  5%
"SQLA                      "         70 MB  4%
"KGLS                      "         21 MB  1%
"KGLHD                     "         15 MB  1%
"VIRTUAL CIRCUITS          "         15 MB  1%
"db_block_hash_buckets     "         13 MB  1%
"kglsim object batch       "         10 MB  1%
"private strands           "       9443 KB  1%
     -----------------------------------------
free memory                         205 MB
memory alloc.                      1459 MB
Sub total                          1664 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 2
----------------------------------------------
"KGH: NO ACCESS            "       1277 MB 77%
"free memory               "        260 MB 16%
"db_block_hash_buckets     "         13 MB  1%
"kglsim object batch       "         11 MB  1%
"ASH buffers               "         10 MB  1%
"private strands           "       9443 KB  1%
"event statistics per sess "       8335 KB  0%
"ksunfy : SSO free list    "       7929 KB  0%
"dbktb: trace buffer       "       6848 KB  0%
"kglsim heap               "       6444 KB  0%
     -----------------------------------------
free memory                         260 MB
memory alloc.                      1404 MB
Sub total                          1664 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 3
----------------------------------------------
"KGH: NO ACCESS            "       1086 MB 68%
"free memory               "        192 MB 12%
"KGLH0                     "         94 MB  6%
"SQLA                      "         39 MB  2%
"FileOpenBlock             "         30 MB  2%
"KGLHD                     "         16 MB  1%
"db_block_hash_buckets     "         13 MB  1%
"enqueue                   "         12 MB  1%
"kglsim object batch       "         11 MB  1%
"KGLS                      "         10 MB  1%
     -----------------------------------------
free memory                         192 MB
memory alloc.                      1408 MB
Sub total                          1600 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 4
----------------------------------------------
"KGH: NO ACCESS            "       1182 MB 67%
"free memory               "        243 MB 14%
"KGLH0                     "         81 MB  5%
"SQLA                      "         48 MB  3%
"KQR M PO                  "         38 MB  2%
"KGLS                      "         17 MB  1%
"db_block_hash_buckets     "         13 MB  1%
"KGLHD                     "         12 MB  1%
"kglsim object batch       "         10 MB  1%
"ASH buffers               "         10 MB  1%
     -----------------------------------------
free memory                         243 MB
memory alloc.                      1517 MB
Sub total                          1760 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 5
----------------------------------------------
"KGH: NO ACCESS            "       1271 MB 74%
"free memory               "        221 MB 13%
"SQLA                      "         51 MB  3%
"KGLH0                     "         45 MB  3%
"db_block_hash_buckets     "         13 MB  1%
"ASH buffers               "         10 MB  1%
"kglsim object batch       "       9622 KB  1%
"private strands           "       9443 KB  1%
"event statistics per sess "       8335 KB  0%
"ksunfy : SSO free list    "       7942 KB  0%
     -----------------------------------------
free memory                         221 MB
memory alloc.                      1507 MB
Sub total                          1728 MB
==============================================
TOP 10 MEMORY USES FOR SGA HEAP SUB POOL 6
----------------------------------------------
"KGH: NO ACCESS            "       1182 MB 70%
"free memory               "        232 MB 14%
"KGLH0                     "         83 MB  5%
"SQLA                      "         48 MB  3%
"KGLHD                     "         13 MB  1%
"db_block_hash_buckets     "         13 MB  1%
"KGLS                      "         13 MB  1%
"kglsim object batch       "         12 MB  1%
"private strands           "       9576 KB  1%
"event statistics per sess "       8335 KB  0%
     -----------------------------------------
free memory                         232 MB
memory alloc.                      1464 MB
Sub total                          1696 MB
TOTALS ---------------------------------------
Total free memory                  1571 MB
Total memory alloc.                  10 GB
Grand total                          11 GB
==============================================

在每個子池中，會有4個持續(xù)時間部分（durations）,，分別是”instance”, “session”, “cursor”, 和 “execution”,。 主要目的是為了根據(jù)不同的事務(wù)種類對共享池申請的內(nèi)存大小和方式的不同，而將會造成大量碎片,、或者能重用的任務(wù)區(qū)分開來,，它們之間互相獨立、互不干擾,。比如cursor所需內(nèi)存來源于duration2,，execution所需內(nèi)存來源于duration3等等。

12c以前的版本

12c以后的版本

為了能使shared pool里的內(nèi)存得到充分利用,，我們通過隱含參數(shù)來去掉durations的限制,。

1
2
3
4
       NUM N_HEX NAME                                                   VALUE                          DESCRIPTION
---------- ----- ------------------------------------------------------ ------------------------------ ---------------------------------------------
       111    6F _enable_shared_pool_durations                          TRUE                           temporary to disable/enable kgh policy

最后解決辦法

1
2
SQL> alter system set "_enable_shared_pool_durations"=false scope=spfile;
- restart the database

設(shè)置"_enable_shared_pool_durations = false"的主要好處在于所有的durations會合并成一個池，所以就不會出現(xiàn)其中一個duration內(nèi)存不足而其他duration還有空余的情況,。

這個問題將在12c版本以后解決,，由于體系的改變導(dǎo)致可以允許子池減少對duration的需求。

    本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間,，所有內(nèi)容均由用戶發(fā)布,，不代表本站觀點。請注意甄別內(nèi)容中的聯(lián)系方式,、誘導(dǎo)購買等信息,，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容,，請點擊一鍵舉報,。
    轉(zhuǎn)藏 分享 獻花（0）

    0條評論

    發(fā)表

    請遵守用戶 評論公約

    類似文章 更多