Example: Using oradebug to trace Oracle Process Unix/Linux

- Need to easy to use. so create shell script (oradebug_from_process.sh)file

oradebug_from_process.sh file script.

>>>>>begin oradebug_from_process.sh file<<<<<<

#!/bin/sh

# Script to help oradebug

# by Surachart

# http://surachartopun.com

# 2008/11/05

. ~/.bash_profile

if [ $# -eq 2 ]

then

EVENT=10046

elif [ $# -eq 3 ]

then

EVENT=$3

else

  echo "Please Check arguments."

  echo

  echo "./oradebug_from_process.sh [on|off] [process id] [10046|10053]"

  echo

  echo "        [process id] process id from session"

  echo "        [10046|10053] event"

  echo

  echo "10046 / TKProf Analysis"

  echo "- Elapsed time to verify customer s assertion"

  echo "- Wait events and average wait time"

  echo "- Runtime execution plans for the Vendors and Customer s tests"

  echo "- Final rows returned"

  echo "- Does it match the statspack report?"

  echo

  echo "10053"

  echo "- Object sizes"

  echo "- Statistics gathered"

  echo "- Cost Based Optimizer (CBO) costs"

  echo

  echo "        ./oradebug_from_process.sh on 100 10046"

  exit 0

fi 

if [ $1 = "on" ]

then

if [ ${EVENT} -eq 10046 ]

then

##################################

sqlplus / as sysdba << EOF

oradebug setospid $2

oradebug TRACEFILE_NAME

oradebug unlimit

oradebug event ${EVENT} trace name context forever, level 12

quit;

EOF

##################################

elif [ ${EVENT} -eq 10053 ]

        then

##################################

sqlplus / as sysdba << EOF

oradebug setospid $2

oradebug TRACEFILE_NAME

oradebug unlimit

oradebug event ${EVENT} trace name context forever, level 10

quit;

EOF

##################################

else

  echo "Please Check arguments."

  echo

  echo "./oradebug_from_process.sh [on|off] [process id] [10046|10053]"

  echo

  echo "        [process id] process id from session"

  echo "        [10046|10053] event"

  echo

  echo "10046 / TKProf Analysis"

  echo "- Elapsed time to verify customer s assertion"

  echo "- Wait events and average wait time"

  echo "- Runtime execution plans for the Vendors and Customer s tests"

  echo "- Final rows returned"

  echo "- Does it match the statspack report?"

  echo

  echo "10053"

  echo "- Object sizes"

  echo "- Statistics gathered"

  echo "- Cost Based Optimizer (CBO) costs"

  echo

  echo "        ./oradebug_from_process.sh on 100 10046"

  exit 0

fi

elif [ $1 = "off" ]

then

##################################

sqlplus / as sysdba << EOF

oradebug setospid $2

oradebug unlimit

oradebug event ${EVENT} trace name context off

quit;

EOF

##################################

else

  echo "Please Check arguments."

  echo

  echo "./oradebug_from_process.sh [on|off] [process id] [10046|10053]"

  echo

  echo "        [process id] process id from session"

  echo "        [10046|10053] event"

  echo

  echo "10046 / TKProf Analysis"

  echo "- Elapsed time to verify customer s assertion"

  echo "- Wait events and average wait time"

  echo "- Runtime execution plans for the Vendors and Customer s tests"

  echo "- Final rows returned"

  echo "- Does it match the statspack report?"

  echo

  echo "10053"

  echo "- Object sizes"

  echo "- Statistics gathered"

  echo "- Cost Based Optimizer (CBO) costs"

  echo

  echo "        ./oradebug_from_process.sh on 100 10046"

  exit 0

fi

>>>>>end oradebug_from_process.sh file<<<<<<

- Change mode

$ chmod  750 oradebug_from_process.sh

- Check Env

$ env | grep ORACLE

ORACLE_HOME=/oracle/11g

ORACLE_SID=testdb

- Find OS PID, that need to oradebug (assume want to trace 23983 os pid )

$ ps -e -o pcpu,user,pid,args |sort -k 1 | grep oracle  | grep LOCAL

9.0 oracle   23983 oracletestdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

- Use oradebug to trace 23983 process (use script)

$ ./oradebug_from_process.sh   on 23983 10046

Connected to:

SQL> Oracle pid: 26, Unix process pid: 23983, image: oracle@host(TNS V1-V3)

SQL> /oracle/11g/diag/rdbms/testdb/testdb/trace/testdb_ora_23983.trc

SQL> Statement processed.

SQL> Statement processed.

SQL> Disconnected from Oracle Database

$  tail -f /oracle/11g/diag/rdbms/testdb/testdb/trace/testdb_ora_23983.trc

*** 2009-02-27 13:06:47.348

Finished processing ORADEBUG command (#6) 'event 10046 trace name context forever, level 12'

*** 2009-02-27 13:06:58.991

WAIT #5: nam='SQL*Net message from client' ela= 23503311 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1235714818991706

CLOSE #3:c=0,e=0,dep=0,type=0,tim=1235714818974433

CLOSE #5:c=0,e=0,dep=0,type=3,tim=1235714818974433

=====================

PARSING IN CURSOR #4 len=16 dep=0 uid=100 oct=3 lid=100 tim=1235714818974433 hv=4067108735 ad='1354f5a10' sqlid='53xavd3t6q9vz'

select * from T1

END OF STMT

PARSE #4:c=1999,e=0,p=0,cr=1,cu=0,mis=1,r=0,dep=0,og=1,plh=3617692013,tim=1235714818974433

EXEC #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3617692013,tim=1235714818974433

WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1235714818993388

FETCH #4:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=3617692013,tim=1235714818974433

STAT #4 id=1 cnt=0 pid=0 pos=1 obj=76141 op='TABLE ACCESS FULL T1 (cr=3 pr=0 pw=0 time=0 us cost=2 size=13 card=1)'

WAIT #4: nam='SQL*Net message from client' ela= 379 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1235714818993931

=====================

PARSING IN CURSOR #3 len=52 dep=0 uid=100 oct=47 lid=100 tim=1235714818974433 hv=1029988163 ad='1356200c0' sqlid='9babjv8yq8ru3'

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

END OF STMT

PARSE #3:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1235714818974433

BINDS #3:

Bind#0

  oacdty=123 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00

  oacflg=00 fl2=1000000 frm=00 csi=00 siz=4000 off=0

  kxsbbbfp=2a96abd280  bln=4000  avl=00  flg=15

Bind#1

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=00 csi=00 siz=24 off=0

  kxsbbbfp=2a96abf220  bln=22  avl=22  flg=05

  value=###

  An invalid number has been seen.Memory contents are :

Dump of memory from 0x0000002A96ABF220 to 0x0000002A96ABF236

2A96ABF220 000010C1 00000000 00000000 00000000  [................]

2A96ABF230 00000000 00000000                    [........]

WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1235714818994527

EXEC #3:c=1000,e=0,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1235714818974433

.

.

.

- Disable trace...

$ ./oradebug_from_process.sh   off  23983 10046

Connected to:

SQL> Oracle pid: 26, Unix process pid: 23983, image: oracle@host (TNS V1-V3)

SQL> Statement processed.

SQL> Statement processed.

SQL> Disconnected from Oracle Database

Write By: http://surachartopun.com