Ferhat's Blog

There will be only one database

Do not have to wake up in order to get a trace

Posted by fsengonul on September 2, 2010

I’m sure there is a better way to get processstate dumps in a RAC environment but I needed something fast and not very dirty.
The following solution wakes up at 10 minute  periods and finds the sessions who causes ST contention and gets the processstate dump as long as locks continue.
crontab:
*/10 * * * * home/oracle/dba/debug.sh >> /home/oracle/dba/debug.log 2>> /tmp/oradebug.err
debug.sh:
. /home/oracle/.bash_profile
date
[ -e /home/oracle/dba/debug_ST.sql ] && exit
cd /home/oracle/dba
sqlplus / as sysdba @debug.sql
#if debug_ST is empty then leave else rerun the job so keep getting traces.
if [ -e /home/oracle/dba/debug_ST.sql ] && [ ! -s /home/oracle/dba/debug_ST.sql ]
then
rm debug_ST.sql
else
rm debug_ST.sql
nohup /home/oracle/dba/debug.sh >> /home/oracle/dba/debug.log 2>> /tmp/oradebug.err &
fi
exit
debug.sql:
set heading off;
set feedback off;
select sql_id,count(*) from gv$session where s.program like ‘sqlldr%’ group by rollup(sql_id);
spool debug_ST.sql
select /*+ RULE */ ‘conn sys/***@DBNAME’||p.inst_id||’ as sysdba ‘|| chr(10)
||’set feedback off’|| chr(10)
||’oradebug setorapid ‘||pid|| chr(10)
|| ‘oradebug unlimit’|| chr(10)
||’oradebug tracefile_name’|| chr(10)
||’oradebug dump processstate 10′ || chr(10)
|| ‘oradebug dump errorstack 3’ ||chr(10)
||’ho sleep 10′ ||chr(10)
||’oradebug dump processstate 10′ || chr(10)
|| ‘oradebug dump errorstack 3’ ||chr(10)
||’ho sleep 10′ ||chr(10)
||’oradebug dump processstate 10′ || chr(10)
|| ‘oradebug dump errorstack 3’ ||chr(10)
from  gv$session s,gv$process p where
s.inst_id=p.inst_id and s.paddr=p.addr and
(s.inst_id,s.sid) IN
—                (select 7,249 from dual)
(SELECT distinct
final_blocking_instance,
final_blocking_Session
from gv$session s
WHERE s.program like ‘sqlldr%’
and event like ‘enq: ST%’)   ;
spool off;
@debug_ST.sql
exit;

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
%d bloggers like this: