Annotations can help to detect leaks and other problems with allocations of memory.
I will again use “old friend “ ORADEBUG
There are two hidden options related to memory annotations:
MEMANOTEND <shared|private> – Record end-state mem annotations
MEMANOTDUMP <shared|private> <level> - Dump memory annotations
and four undocumented parameters :
_mem_annotation_pr_lev private memory annotation collection level 0
_mem_annotation_scale memory annotation pre-allocation scaling 1
_mem_annotation_sh_lev shared memory annotation collection level 0
_mem_annotation_store memory annotation in-memory store FALSE
So before you can use these options I have to set these parameters.They cannot be changed dynamically
and instance must be bounced.Something like this:
SQL> alter system set “_mem_annotation_store”=true scope=spfile;
System altered. etc for the rest..
and now after restarting instance:
First set the process
SQL> oradebug setmypid
Statement processed.
Start recording :
SQL> oradebug memanotend private
Statement processed.
and at the end dump context into trace file:
SQL> oradebug memanotdump private 1
Statement processed.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/test/test2/trace/test2_ora_26953.trc
host vi ora-main/app/oracle/diag/rdbms/test/test2/trace/test2_ora_26953.trc
In some cases when you set these parameters you will get ORA-600 like
ORA-00600: internal error code, arguments: [kspgip1], [101], [121], [1], [_mem_annotation_sh_lev],
or ORA-00600: internal error code, arguments: [ksmanotsh:size] but here is option that will let you start your database:
more inittest.ora
*._mem_annotation_store=TRUE
*._mem_annotation_sh_lev=0
*._mem_annotation_scale=10
*._mem_annotation_pr_lev=1
-sh-3.1$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Thu Jun 18 12:33:48 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=’/oracle/admin/test/pfile/inittest.ora’;
ORACLE instance started.
Total System Global Area 2622255104 bytes
Fixed Size 2162960 bytes
Variable Size 738201328 bytes
Database Buffers 1862270976 bytes
Redo Buffers 19619840 bytes
Database mounted.
Database opened.
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
PL/SQL Release 11.1.0.7.0 – Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 – Production
NLSRTL Version 11.1.0.7.0 – Production
SQL> set linesize 125 pagesize 50 newpage 0
SQL> col param_dflt form a3 head ‘Dflt?’
SQL> col param_name form a33 head ‘Parameter’
SQL> col param_value form a20 head ‘Value’
SQL> col DESCP format a64
SQL> select decode (ksppstdf, ‘FALSE’, ‘NO’, ‘ ‘) param_dflt,
2 ksppinm param_name,ksppdesc descp, ksppstvl param_value
3 from x$ksppi, x$ksppcv
4 where x$ksppi.indx = x$ksppcv.indx
5 and ksppinm like ‘%&1%’
6 order by ksppinm;
Enter value for 1: annota
old 5: and ksppinm like ‘%&1%’
new 5: and ksppinm like ‘%annota%’
Dfl Parameter DESCP Value
— ——————————— —————————————————————- ——————–
NO _mem_annotation_pr_lev private memory annotation collection level 1
NO _mem_annotation_scale memory annotation pre-allocation scaling 10
NO _mem_annotation_sh_lev shared memory annotation collection level 0
NO _mem_annotation_store memory annotation in-memory store TRUE
_optimizer_reuse_cost_annotations reuse cost annotations during cost-based query transformation TRUE
SQL> oradebug setmypid
Statement processed.
SQL> oradebug memanotend private
Statement processed.
SQL> oradebug memanotdump private 1
Statement processed.
SQL> oradebug tracefile_name
/oracle/admin/test/diag/rdbms/test/test/trace/test_ora_30961.trc
SQL> host vi test_ora_30961.trc
SQL> host vi /oracle/admin/test/diag/rdbms/test/test/trace/test_ora_30961.trc
*** 2009-06-18 12:34:49.059
Oradebug command ’setmypid’ console output: <none>
*** 2009-06-18 12:34:57.811
Processing Oradebug command ‘memanotend private’
*** 2009-06-18 12:34:57.811
Oradebug command ‘memanotend private’ console output: <none>
*** 2009-06-18 12:35:05.971
Processing Oradebug command ‘memanotdump private 1′
*** 2009-06-18 12:35:05.971
MEMORY ANNOTATION DUMP
———————-
Region: private
Level: 1
Level collected: 1
LEVEL ADDRESS SIZE NAME:TYPE
—– —————- ———— —————————————-
END OF MEMORY ANNOTATION DUMP