_ Kgl_large_heap_warning_threshold Parameter

Today, the alert. log file contains a lot of such information:

Memory notification: Library cache object loaded into SGA
Heap size 2295 K exceeds notification threshold (2048 K)
KGL Object Name: XDB. xdbd/plz01tchgnagaiiegtw =

Feel like Oracle bug

Today, the alert. log file contains a lot of such information:

Memory notification: Library cache object loaded into SGA
Heap size 2295 K exceeds notification threshold (2048 K)
KGL Object Name: XDB. xdbd/plz01tchgnagaiiegtw =

Feel like Oracle bug

C:> sqlplus/nolog

SQL * Plus: Release 10.2.0.1.0-production on Thursday November 22 16:11:50 2007

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Idle> ConnSys/sys @ doscdbAs sysdba
Connected.

Doscdb (sys)> select * from V $ version;

Banner
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
PL/SQL release 10.2.0.1.0-Production
Core 10.2.0.1.0 Production
TNS for 32-bit windows: Version 10.2.0.1.0-Production
Nlsrtl version 10.2.0.1.0-Production

Check the implicit parameters.

doscdb (sys)> select ksppinm name,
2 ksppstvl value,
3 ksppstdf isdefault,
4 decode (bitand (ksppstvf, 7), 1, 'modified', 4, 'System _ mod', 'false') ismod,
5 decode (bitand (ksppstvf, 2), 2, 'true', 'false') isadj
6 from x $ ksppi x, x $ ksppcv Y
7 where (X. indx = y. indx) and
8 X. ksppinm like '%' | '& Parameter' | '%'
9/
enter the parameter value: KGL
original value: 8: X. ksppinm like '%' | '& Parameter' | '%'
New Value: 8: x. ksppinm like '%' | 'kgl '|' % '

name value isdefault ismod isadj
------------------------------ ---------------------------------------------------- ----------
_ kgl_multi_instance_lock true True False false
_ kgl_multi_instance_pin true True False false
_ kgl_multi_instance_invalidati true True False false
On

_ kgl_latch_count 0 True False false
_ kgl_heap_size 1024 True False false
_ kgl_fixed_extents true True False false
_ kgl_session_cached_objects 10 True False false
_ kgl_keep_cache_pct 30 True False false
_ kgl_keep_cache_retain_pct 20 True False false
_ kgl_bucket_count 9 True False false
_ kglsim_maxmem_percent 5 True False false
_ kgl_hash_collision false True False false
_ kgl_time_to_wait_for_locks 15 True False false
_ kgl_large_heap_warning_thresh 2097152 True False false
old

14 rows have been selected.

_ Kgl_large_heap_warning_threshold 2097152

This line of data is questionable and should be a problem.

Analyze the problem:

Doscdb (sys)> select ksppdesc from x $ ksppi where ksppinm like '_ kgl_large_heap_warning % ';

Ksppdesc
Bytes -----------------------------------------------------------------------------------------

Maximum heap size before KGL writes warnings to the Alert Log

Literally, this is why. After querying some data, we found that in Oracle10g, when a large object is loaded into the library cache,
The above warnings are recorded. In version 10.2.0.1, the threshold value of this defined large object is 2 m, which is specified by the implicit parameter _ kgl_large_heap_warning_threshold,
If you need to load many large objects in the system and do not want to see too many such warnings in alert, you can modify this parameter.

Solution:

Change it to 10 MB

Doscdb (sys)> alter system set "_ kgl_large_heap_warning_threshold" = 10485760 scope = spfile;

The system has been changed.

Doscdb (sys)> shutdown immediate;
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.
Doscdb (sys)> startup

The Oracle routine has been started.

Total system global area 419430400 bytes
Fixed size 1249368 bytes
Variable Size 125833128 bytes
Database buffers 285212672 bytes
Redo buffers 7135232 bytes
The database has been loaded.
The database has been opened.

doscdb (sys)> select ksppinm name,
2 ksppstvl value,
3 ksppstdf isdefault,
4 decode (bitand (ksppstvf, 7), 1, 'modified', 4, 'System _ mod', 'false') ismod,
5 decode (bitand (ksppstvf, 2), 2, 'true', 'false') isadj
6 from x $ ksppi x, x $ ksppcv Y
7 where (X. indx = y. indx) and
8 X. ksppinm like '%' | '& Parameter' | '%'
9/
enter the parameter value: KGL
original value: 8: X. ksppinm like '%' | '& Parameter' | '%'
New Value: 8: x. ksppinm like '%' | 'kgl '|' % '

name value isdefault ismod isadj
------------------------------ ---------------------------------------------------- ----------
_ kgl_multi_instance_lock true True False false
_ kgl_multi_instance_pin true True False false
_ kgl_multi_instance_invalidati true True False false
On

_ kgl_latch_count 0 True False false
_ kgl_heap_size 1024 True False false
_ kgl_fixed_extents true True False false
_ kgl_session_cached_objects 10 True False false
_ kgl_keep_cache_pct 30 True False false
_ kgl_keep_cache_retain_pct 20 True False false
_ kgl_bucket_count 9 True False false
_ kglsim_maxmem_percent 5 True False false
_ kgl_hash_collision false True False false
_ kgl_time_to_wait_for_locks 15 True False false
_ kgl_large_heap_warning_thresh 10485760 false False false
old

14 rows have been selected.

Check the alert. log file again, and no similar information is found. The problem can be solved.

From:

Http://luckysea.itpub.net/post/5481/414835

 

SQL Trace startup and shutdown:

 

1, SQL Trace startup and shutdown:
Before using SQL Trace, you must first view Oracle Whether the initial parameters are set. Three parameters need to be correctly set: timed_statistics = true, user_dump_dest = tracking file storage location, max_dump_file_size = unlimited, and can be set by yourself, here, the timed_statistics parameter is used to determine whether a trace can be executed in the system. True indicates that the trail can be executed.
In fact, in oracle10gr2, these parameters are already set and do not need to be set. After confirming these parameters, you can start tracing and execution. You can execute a trail under the Sys user or another user with permissions (however, no trace file is found after I execute the trail under the Scott user ).
(1) enable tracking:
Alter session set SQL _trace true;
(2) perform the operations to be tracked, such
Select ename from e-mapreduce where e-mapreduce = 7788;
(3) Disable tracking
Alter session set SQL _trace false;
2. Convert and query trace files
The value of my initial parameter user_dump_dest is/home/Oracle/orcl/udump. After the trail is complete, I will see the trail file in this directory, however, when I open it, I find that there are many trace files in it, and I don't know which file I used, so next, I will return to SQL * Plus to query the process ID of the session where the trail is executed, because the file name format of the trail file is (instance name_ora _ process ID. TRC), My Instance name is orcl, so I run the query to determine the process ID. Before the query, grant the user the permission to query the V $ process, V $ session, and V $ mystat views:
Grant select on V _ $ process to Scott;
Grant select on V _ $ session to Scott;
Grant select on V _ $ mystat to Scott;
Fortunately, when granting permissions, I always remember that in Oracle, the V $ view I usually access is actually a synonym, and she is not a real view, it is a synonym created by the underlying view, and the underlying view of these views is very similar to these view names. For example, the underlying view of V $ process is V _ $ process, therefore, you should grant the query permission of the V _ $ process view to the user during authorization. After the authorization is completed, you can execute the query:
Select spid
From v $ PROCESS p, V $ session s
Where p. ADDR = S. paddr and S. Sid = (select Sid from V $ mystat where
Rownum = 1 );
In the returned query result, the spid (that is, the session process ID) is 2842, and I confirm that the trace file I just created is orcl_ora_2842.trc.
After you confirm the Tracing file, you can convert its format:
Tkprof orcl_ora_2842.trc orcl_ora_2842_1.prf explain = system/password;
This is not run in SQL * Plus, but in the terminal. In fact, this conversion is not necessary, but reading orcl_ora_2842.trc is really a bit difficult, so I use tkprof for practical purposes.ProgramConverts a trace file. However, this conversion does not contain all the information of the original trace file, which is easy to read after conversion. After the conversion is completed, I will open it first to see the tracing information ···

 

From:

Http://space.itpub.net/25244847/viewspace-693287