pat98's always & forever

10g R2 로 upgrade 하거나 새로 설치하면 나타나는 증상인데 (SQL문 실행하면 alertSID.log 화일에 계속 기록됨) heap size 기본셋팅 사이즈때문에 나타나는 warning message 이므로 무시하거나, 신경쓰이시는 분들은 아래와 같이 조치하면 됨
================================================================================================

Applies to:
Oracle Server - Enterprise Edition - Version:
This problem can occur on any platform.

Symptoms
The following messages are reported in alert.log after 10g Release 2 is installed.

       Memory Notification: Library Cache Object loaded into SGA
       Heap size 2294K exceeds notification threshold (2048K)

Changes
Installed / Upgraded to 10g Release 2

Cause
These are warning messages that should not cause the program
responsible for these errors to fail. 
They appear as a result of new event messaging mechanism
and memory manager in 10g Release 2. The meaning is that the
process is just spending a lot of time in finding free memory
extents during an allocate as the memory may be heavily fragmented.
Real memory messages should be ORA-403x when  a real memory allocation problem
occurs.

Solution
In 10g we have a new undocumented parameter that sets the KGL heap size
warning threshold.   This parameter was not present in 10gR1.
Warnings are written if heap size exceeds this threshold.
  
Set  _kgl_large_heap_warning_threshold  to a reasonable high value or zero
to prevent these warning messages. Value needs to be set in bytes.

If you want to set this to 8192 (8192 * 1024) and are using an spfile:

(logged in as "/ as sysdba")

SQL> alter system set "_kgl_large_heap_warning_threshold"=8388608 scope=spfile ;

SQL> shutdown immediate SQL> startup

SQL> show parameter _kgl_large_heap_warning_threshold
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_kgl_large_heap_warning_threshold integer 8388608

If using an "old-style" init parameter,

Edit the init parameter file and add

_kgl_large_heap_warning_threshold=8388608

In 10.2.0.2,  the threshold is increased to 50MB after regression tests, so this should be a reasonable and recommended value.

References

주로 Linux plaform 에서 이런현상이 있는거 같더군요.

Applies to:
------------------
Oracle Server - Enterprise Edition - Version: 10.2.0.1.0
Linux Itanium
SUSE \ UnitedLinux x86-64
Red Hat Enterprise Linux Advanced Server x86-64 (AMD Opetron Architecture)
Linux x86-64
Red Hat Enterprise Linux Workstation
Linux x86

Symptoms
------------------
This is Linux Specific Issue. ( RedHat and Suse Linux Mainly )

In Oracle Database 10G you dont need to relink oracle binary with async_on.It comes default with oracle 10G.This note applies in such case wherein async_on is enable at Oracle Database level though Operating System is not able to get the I/O events.

You have set all required parameters for enabling the asynch I/O and finds that asynch I/O is not available from /proc/slabinfo.

SQL> sho parameter disk_asynch_io

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io boolean TRUE

SQL> sho parameter filesystemio_options

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string SETALL

The Asynchronous I/O does not work.

cat /proc/slabinfo | grep kio ( See the value of kioctx.Its zero )
kioctx 0 0 384 42 1 : tunables 54 27 8 :
slabdata 0 0 0
kiocb 0 0 256 62 1 : tunables 120 60 8 :
slabdata 0 0 0

Cause
---------------
Oracle 10GR2 doesnt requires relinking of oracle binary for async I/O to work but in oracle 10GR2 i found that Oracle is not able to report the I/O activity or event to OS ( in /proc/slabinfo )

As for example.( Please look at the value of kioctx.Its zero )

cat /proc/slabinfo | grep kio
kioctx 0 0 384 42 1 : tunables 54 27 8 : slabdata 0
0 0
kiocb 0 0 256 62 1 : tunables 120 60 8 : slabdata 0
0 0

oracle@tslxprac01:~> cat /proc/sys/fs/aio-nr
0

The reson behind this behavior is that LIBAIO_0.1 Linux OS library is not attached for io_getevents.

So you will have to relink the Oracle 10gr2 with async_on option.Oracle 10gr2 does not come with file called skgaio.o so if you try to relink oracle with async_on , it will fail with following error:

oracle@tslxprac01:/u01/app/oracle/product/10.2.0/db_1/rdbms/lib> make
PL_ORALIBS=-laio -f ins_rdbms.mk async_on ioracle
rm -f /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaioi.o
cp /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaio.o
/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaioi.o
cp: cannot stat `/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaio.o': No
such file or directory
make: Following error says that while trying to relinking oracle 10g with async_on it tried to look for
file called skgaio.o and it got failed because the file skgaio.o is not there.

cp: cannot stat `/u01/app/oracle/product/10.2.0/db_1/rdbms/lib/skgaio.o': No such file or directory

Solution
---------------------
To implement the solution, please execute the following steps:

SQL>shutdown immediate

$cd $ORACLE_HOME/rdbms/lib

$ln -s /usr/lib/libaio.so.1 skgaio.o

$make PL_ORALIBS=-laio -f ins_rdbms.mk async_on

SQL>startup

$grep kio /proc/slabinfo