Linux database 2.6.9-89.0.0.0.1.ELsmp #1 SMP Tue May 19 05:31:20 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
alterlog 內容:
Sun Oct 30 18:22:28 2011
Memory Notification: Library Cache Object loaded into SGA
Heap size 2227K exceeds notification threshold (2048K)
KGL object name :SELECT * FROM "SYS"."ALL_OBJECTS"
Sun Oct 30 18:22:29 2011
Memory Notification: Library Cache Object loaded into SGA
Heap size 2198K exceeds notification threshold (2048K)
Details in trace file /opt/app/oracle/admin/nvi/udump/nvi_ora_12231.trc
KGL object name :SELECT * FROM "SYS"."ALL_TRIGGERS"
數據庫為單機 生產庫網上找到了解決此辦法的相關信息:
Oracle10g中，在load较大的对象进library cache中时，会记录以上警告。在版本10.2.0.1中，这个定义大对象的阈值是2M，这是有隐含参数_kgl_large_heap_warning_threshold指定的，如果系统中需要load很多大对象，又不想在alert中看到太多这类的警告信息，可以修改该参数：
SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile；
参数值以字节为单位，设置完后需要重启实例才能生效。
从10.2.0.2起，这个参数的默认值被修改为50M.
我的問題是 更改"_kgl_large_heap_warning_threshold" 隱含參數需要重啟,因為生產庫的原因不能做重啟動作. 有沒有其它方法解決此問題.如果沒有其它方法可不可以不進得更改,對數據庫會不會產生影響??? 求指教!!!
trc文件內容:
/opt/app/oracle/admin/nvi/udump/nvi_ora_12231.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/app/oracle/product/10.2.0.1.0/nvi
System name: Linux
Node name: database
Release: 2.6.9-89.0.0.0.1.ELsmp
Version: #1 SMP Tue May 19 05:31:20 EDT 2009
Machine: x86_64
Instance name: nvi
Redo thread mounted by this instance: 1
Oracle process number: 88
Unix process pid: 12231, image: [url=mailto
raclenvi@database]oraclenvi@database[/url]
*** 2011-10-30 18:22:28.508
*** SERVICE NAME
SYS$USERS) 2011-10-30 18:22:28.449
*** SESSION ID
460.17688) 2011-10-30 18:22:28.449
Memory Notification: Library Cache Object loaded into SGA
Heap size 2227K exceeds notification threshold (2048K)
LIBRARY OBJECT HANDLE: handle=90390318 mutex=0x90390448(1)
name=SELECT * FROM "SYS"."ALL_OBJECTS"
hash=e56c2c728b855e35e705d88f81e8ebec timestamp=10-30-2011 18:22:27
namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[120100d4]
kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=5 hpc=0002 hlc=0002
lwt=0x903903c0[0x903903c0,0x903903c0] ltm=0x903903d0[0x903903d0,0x903903d0]
pwt=0x90390388[0x90390388,0x90390388] ptm=0x90390398[0x90390398,0x90390398]
ref=0x903903f0[0x903903f0,0x903903f0] lnd=0x90390408[0xa6d1d390,0xa6745a20]
LIBRARY OBJECT: object=9e690b78
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16
child#table reference handle
------ -------- --------- --------
0 9e6af8189e6af488 a69356b0
DATA BLOCKS:
data# heappointerstatus pins change whr alloc(K)size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a6bfbac8 9e690c90 I/P/A/-/-0 NONE 001.19 2.17
LIBRARY OBJECT HANDLE: handle=a69356b0 mutex=0xa69357e0(0)
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0001-0000 lock=N pin=X latch#=5 hpc=0002 hlc=0002
lwt=0xa6935758[0xa6935758,0xa6935758] ltm=0xa6935768[0xa6935768,0xa6935768]
pwt=0xa6935720[0xa6935720,0xa6935720] ptm=0xa6935730[0xa6935730,0xa6935730]
ref=0xa6935788[0x9e6af488,0x9e6af488] lnd=0xa69357a0[0xa69357a0,0xa69357a0]
LIBRARY OBJECT: object=9ee16b78
type=CRSR flags=EXS[0001] pflags=NST[0001] status=VALD load=0
DEPENDENCIES: count=13 size=16
dependency#table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 9e4c5aa89e27f0d0 a7ab1e78 20 DEP[01]
1 9e4c5aa89e27f120 a6f688e0 2005 DEP[01]
2 9e4c5aa89e27f170 a6f6b8f8 1993 DEP[01]
3 9e4c5aa89e27f1c0 a6d1d2a0897 DEP[01]
4 9e4c5aa89e27f210 a6f6a8c8 2249 DEP[01]
5 9e4c5aa89e27f260 a6f46698 2685 DEP[01]
6 9e4c5aa89e27f2b0 a6b5bcd0 2751 DEP[01]
7 9e4c5aa89e27f300 a783d7e0 3249 DEP[01]
8 9e4c5aa89ee16dd8 a783cd78 25 DEP[01]
9 9e4c5aa89ee16e28 a6ce3888 30 DEP[01]
10 9e4c5aa89ee16e78 a6efa5a8 8759 DEP[01]
11 9e4c5aa89e98f950 90346968 8639 DEP[01]
12 9e4c5aa89e98fa18 a7ad6b400 DEP[01]
READ ONLY DEPENDENCIES: count=1 size=16
dependency#table reference handle flags
----------- -------- --------- -------- -------------------
0 9ee16ef09ee16d98 90390318 /ROD/KPP[60]
ACCESSES: count=1 size=16
dependency# types
----------- -----
0 0009
DATA BLOCKS:
data# heappointerstatus pins change whr alloc(K)size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a63df928 9e4c5718 I/P/A/-/-0 NONE 004.44 5.43
6 9e27ef50 8f943990 I/P/A/-/-1 NONE 00 2227.952250.60
Memory Notification: Library Cache Object loaded into SGA
Heap size 2198K exceeds notification threshold (2048K)
LIBRARY OBJECT HANDLE: handle=984ca878 mutex=0x984ca9a8(1)
name=SELECT * FROM "SYS"."ALL_TRIGGERS"
hash=8315a9862d4a298c33804275bbae4fdf timestamp=10-30-2011 18:22:28
namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[120100d4]
kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=2 hpc=0002 hlc=0002
lwt=0x984ca920[0x984ca920,0x984ca920] ltm=0x984ca930[0x984ca930,0x984ca930]
pwt=0x984ca8e8[0x984ca8e8,0x984ca8e8] ptm=0x984ca8f8[0x984ca8f8,0x984ca8f8]
ref=0x984ca950[0x984ca950,0x984ca950] lnd=0x984ca968[0xa4d94ed0,0xa73d98a0]
LIBRARY OBJECT: object=a0830d68
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
CHILDREN: size=16
child#table reference handle
------ -------- --------- --------
0 a0617e68a0617ad8 a7838428
DATA BLOCKS:
data# heappointerstatus pins change whr alloc(K)size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 a7851d18 a0830e80 I/P/A/-/-0 NONE 001.19 2.17
LIBRARY OBJECT HANDLE: handle=a7838428 mutex=0xa7838558(0)
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0001-0000 lock=N pin=X latch#=2 hpc=0002 hlc=0002
lwt=0xa78384d0[0xa78384d0,0xa78384d0] ltm=0xa78384e0[0xa78384e0,0xa78384e0]
pwt=0xa7838498[0xa7838498,0xa7838498] ptm=0xa78384a8[0xa78384a8,0xa78384a8]
ref=0xa7838500[0xa0617ad8,0xa0617ad8] lnd=0xa7838518[0xa7838518,0xa7838518]
LIBRARY OBJECT: object=a017f350
type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
DEPENDENCIES: count=10 size=16
dependency#table reference handle position flags
----------- -------- --------- -------- -------- -------------------
0 a02fd7b0a0315398 98f7afb0 20 DEP[01]
1 a02fd7b0a03153e8 a6f688e0 1057 DEP[01]
2 a02fd7b0a0315438 a7a76c70 1041 DEP[01]
3 a02fd7b0a0315488 a6f6b8f8 1006 DEP[01]
4 a02fd7b0a03154d8 a6f46698 1462 DEP[01]
5 a02fd7b0a0315528 a6b5bcd0 1528 DEP[01]
6 a02fd7b0a0315578 a783d7e0 1574 DEP[01]
7 a02fd7b0a03155c8 a783cd78 25 DEP[01]
8 a02fd7b0a017f5b0 a6ce3888 30 DEP[01]
9 a02fd7b0a017f600 903e8eb011193 DEP[01]
READ ONLY DEPENDENCIES: count=1 size=16
dependency#table reference handle flags
----------- -------- --------- -------- -------------------
0 a017f6c8a017f570 984ca878 /ROD/KPP[60]
ACCESSES: count=1 size=16
dependency# types
----------- -----
0 0009
DATA BLOCKS:
data# heappointerstatus pins change whr alloc(K)size(K)
----- -------- -------- --------- ---- ------ --- -------- --------
0 98a496b8 a02fd420 I/P/A/-/-0 NONE 004.19 5.43
6 a0315218 91e98930 I/P/A/-/-1 NONE 00 2198.892219.04
|
