SGA space usage watching from UNIX
about ipcs command for Oracle SGA, there was a problem reported on Oracle 11.2.0.3.0 caused by Stale Native Code Files.
Searching in Oracle Documentation, we found one hit:
Oracle® Database Readme 11g Release 2 (11.2) E41331-02
4.29.2 Stale Native Code Files Are Being Cached
Natively compiled PL/SQL and native code generated by the JIT compiler for Oracle JVM, may be cached in operating system files. The SHUTDOWN ABORT and SHUTDOWN IMMEDIATE commands do not clean these cached native code files (reference Bug 8527383).
The name patterns are as follows where sid_name is the system identifier name:
JOXSHM_EXT_*_sid_name_*
PESHM_EXT_*_sid_name_*
PESLD_sid_name_*
However as observed with Oracle 11.2.0.3.0 on AIX 7.1.0.0, the name patterns looks like:
/JOXSHM_EXT_*_instname_sidname
/PESLD_instname_*_*
(sidname is the ID of shared memory segment)
(Update-2022-Dec-21: In Oracle 19c, the name pattern is like JOEZSHM_* instead of JOXSHM_EXT_*)
In fact, when summing up shared memory segments plus stale objects, it is more than Oracle reported SGA size since such stale objects are no more visible to Oracle. As a consequence, if there exist a large number of such Stale objects, Database can be made outages due to space starvation.
To this problem, Oracle provides certain workaround described in MOS: (Doc ID 1120143.1)
Stale Native Code Files Are Being Cached with File Names Such as: JOXSHM_EXT*, PESHM_EXT*, PESLD* or SHMDJOXSHM_EXT*
but it is only applied to platforms except AIX since on AIX Stale Native Code Files such as (JOXSHM_EXT*) are kept as shared memory segments not as physical files, so there is no way to delete them by rm command.
Tests on AIX and Linux show the different shutdown behaviors:
shutdown immediate: clean current stale files (created by this startup), but not previous ones.
Probably because sidname does not belong to current running instance.
shutdown normal: same as shutdown immediate
shutdown abort: no clean
This Blog will try to provide a workaround for AIX by a small demo with detail steps.
(1). On AIX DB, run the appended TestCase.
(2). View all native and JIT compiled code by:
select * from dba_plsql_object_settings where plsql_code_type = 'NATIVE';
select * from dba_java_methods where is_native = 'YES';
Here also two non-documented methods:
select * from sys.ncomp_dll$ n, dba_objects o where n.obj# = o.object_id;
call sys.dbms_feature_plsql_native to generate a plsqlNativeReport.
declare
l_is_used number;
l_aux_count number;
l_report clob;
begin
sys.dbms_feature_plsql_native(o_is_used=>l_is_used, o_aux_count=>l_aux_count, o_report=>l_report);
dbms_output.put_line('o_is_used='||l_is_used);
dbms_output.put_line('o_aux_count='||l_aux_count);
dbms_output.put_line('o_report='||l_report);
end;
(3). Shutdown database by:
shutdown abort
(4). On AIX, monitor memory usage:
ipcs -ar |grep -e JOXSHM_EXT -e PESHM -e PESLD | awk '{cnt+=1; sum+=$10} END {print "Count=",cnt,"Sum=",sum,"Average=",sum/cnt}'
(5). remove them by:
ipcs -ar |grep -e JOXSHM_EXT -e PESHM -e PESLD | awk ' {name = $17; cmd = "ipcrm -r -m " name; print "Run ", cmd; system( cmd )}'
Addendum (2016.01.21):
The above workaround seems also adopted by Oracle MOS: (Doc ID 1120143.1) in which there is one original text:There is no fix to remove the stale files.
that was seen in 06-Nov-2013.
Now one controversial text was added:
On AIX these files can then be removed using the 'ipcrm' command.
There is one MOS Note on Solaris and Linux: Ora-7445 [Ioc_pin_shared_executable_object()] (Doc ID 1316906.1)
The error is most often related to an inconsistency that has been detected between the java shared object loaded in memory and the backing store image stored on disk as a result of calling java code and having the JIT compiler enabled.
On Solaris these files are written to /tmp and have names like .SHMDJOXSHM_EXT_...
On Linux these files are written to /dev/shm and have names like JOXSHM_EXT_...
Where IOC could be an abbreviation of Inversion of Control, something like callbacks.
Addendum (2015.12.28):
The Blog: What the heck are the /dev/shm/JOXSHM_EXT_x files on Linux? talked about such files on Linux.Test Case
alter system set plsql_code_type=native;
create or replace package bla as
procedure foo;
end;
/
create or replace package body bla as
procedure foo is begin null; end;
end;
/
exec bla.foo;
alter system set java_jit_enabled=true;
create or replace and compile java source named "Hello" as
public class Hello {
public static String world ()
{
return "hello world";
}
}
/
create or replace function helloworld
return varchar2 as language java
name 'Hello.world () return java.lang.string';
/
select helloworld from dual;
