generate dumps – overview

Some nice – important dump options 🙂

and still under constuction

find sga ID

in Unix environments with mutiple instances running within the same os-use (e.g. oracle) you cannot see which memory segment is allocated to which instance.

sqlplus  / as sysdba
oradebug setmypid
oradebug ipc
oradebug tracefile_name

the column Shmid shows the dependend memory structures

AWR – automatic workload repository

to change the collection interval and retention policy

exec dbms_workload_repository.modify_snapshot_settings ( retention => 20160, -
interval => 30);

example above will change to 14 days (value in minutes) with 30mins snapshots

retention (default  8 days) and interval (default 1 hour)  in minutes

be aware – increasing the retention time or increase the frequence of snapshots the sysaux tablespace will increase also !

to show the current settings or the latest snapshot

col systimestamp format a35
col most_recent_snap_time format a25
col snap_interval format a17
col retention format a17

select systimestamp, most_recent_snap_time, snap_interval, retention
 from wrm$_wr_control 
  where dbid = (select dbid from v$database);

create manually a snapshot

exec dbms_workload_repository.create_snapshot

enable/disable Diagnostic&Tuning pack

event 10046 – tracing sessions

how to trace session by
– alter session
– oradebug
– logon trigger

tracefiles will be placed in user_dump_dest (10g) or in diag_dest/db/inst/trace (11g)

Session Tracing

alter session set max_dump_file_size = unlimited;
alter session set tracefile_identifier='tr10046';
alter session set events '10046 trace name context forever, level 12';

tkprof – analyze tracefiles

After creating some traces (e.g. via event 10046)  you can analyze those traces with tkprof

tkprof <input tracefile> <output file>  aggregate=no sys=no explain=username/password

sys=no                 hiddes the recursive calls
aggregate=no    doesn’t aggregate reoccured statements to one summary

with sort=xxxx,yyyy,zzzz you can sort the output

     prscnt  number of times parse was called
     prscpu  cpu time parsing
     prsela  elapsed time parsing
     prsdsk  number of disk reads during parse
     prsqry  number of buffers for consistent read during parse
     prscu   number of buffers for current read during parse
     prsmis  number of misses in library cache during parse

     execnt  number of execute was called
     execpu  cpu time spent executing
     exeela  elapsed time executing
     exedsk  number of disk reads during execute
     exeqry  number of buffers for consistent read during execute
     execu   number of buffers for current read during execute
     exerow  number of rows processed during execute
     exemis  number of library cache misses during execute

     fchcnt  number of times fetch was called
     fchcpu  cpu time spent fetching
     fchela  elapsed time fetching
     fchdsk  number of disk reads during fetch
     fchqry  number of buffers for consistent read during fetch
     fchcu   number of buffers for current read during fetch
     fchrow  number of rows fetched

event 10053 – obtaining optimizer computations

to see how the oracle optimizer generates the execution plan  use the event 10053

alter session set max_dump_file_size = unlimited;
alter session set tracefile_identifier='tr10053';
alter session set events='10053 trace name context forever, level 1';

the tracefile will be written to user_dump_dest (10.2) or in <diag_dest>/rdbms/<database>/<instance>/trace

additional events

Cursor tracing

to find the hash_value of the parent cursor for adress
select sql_text, hash_value
from v$sqlarea where sql_id='<sql_id>’;

turn on tracing:
alter session set events ‘immediate trace name cursortrace level 99172,  address <hash_value>’;

turn off tracing
alter system set events ‘immediate trace name cursortrace level 2147483648, address 1’;