Oracle internals, debugging and undocumented features

Memory Annotations and Oradebug

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

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


-sh-3.1$ sqlplus / as sysdba

SQL*Plus: Release – 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;
Oracle Database 11g Enterprise Edition Release – 64bit Production
PL/SQL Release – Production
CORE      Production
TNS for Linux: Version – Production
NLSRTL Version – 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


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
Region: private
Level: 1
Level collected: 1
—– —————- ———— —————————————-


2 responses to “Memory Annotations and Oradebug

  1. Tanel Poder June 18, 2009 at 3:01 pm

    On which db version did you run this? When testing this on on linux/32b I got bunch of ORA-600’s during startup, even after manually setting the other parameters needed like the annotation level…

  2. oraclue June 18, 2009 at 4:36 pm

    Hi Tanel, on Linux x86-64.
    I have posted working example with all parameters.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: