How to trace queries


How to get current session id, process id, client process id?
Posted by Amin Jaffer on May 15, 2011
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv(‘sessionid’);
SID SERIAL# PROCESSID CLIENTPID
———- ———- ——— ———
43 52612 420734 5852:5460
V$SESSION.SID and V$SESSION.SERIAL# are database process id
V$PROCESS.SPID – Shadow process id on the database server
V$SESSION.PROCESS – Client process id, on windows it is “:” separated the first # is the process id on the client and 2nd one is the thread id.

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551378329289980701

How to enable auditing?
Posted by Amin Jaffer on March 19, 2011
By setting the parameter “AUDIT_TRAIL” to “DB” or “OS”, one can enable auditing in the database. To change the value of the parameter you would need to bounce the database for the new value to take into effect. The default value of the parameter is “NONE” in which database auditing is disabled. Even if “AUDIT_TRAIL” is enabled/disabled, Oracle will write OS audit trail at OS audit trail at startup and shutdown of instance, and connections made by SYSOPER and SYSDBA.
If “AUDIT_TRAIL” is set to “OS”, the parameter “AUDIT_FILE_DEST” is the location where audit files are created. The default value of this parameter is “$ORACLE_HOME/rdbms/audit”.
If “AUDIT_TRAIL” is set to “DB”, oracle writes the auditing information in “SYS.AUD$” table. By default the table is created in SYSTEM tablespace.
If one tries to open a database in read only mode and AUDIT_TRAIL is set to DB, Oracle will generate an error stating the audit_trail is incompatible with database open mode.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16006: audit_trail destination incompatible with database open mode
How to generate systemdump when the instance is hung?
Posted by Amin Jaffer on December 26, 2010
Using oradebug one can analyze if an instance is hung
$ sqlplus “/as sysdba”
SQL> oradebug mysetpid
Statement processed.
– no limit on trace file
SQL> oradebug unlimit
Statement processed.
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/oradata/admin/TEST/udump/test_ora_11852.trc
– Wait for couple of minutes
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/oradata/admin/TEST/udump/test_ora_11852.trc
– Wait for couple of minutes and get systemstate dump
SQL> oradebug dump systemstate 10
Statement processed.
– Wait for couple of minutes and run systemstate dump
SQL> oradebug dump systemstate 10
Statement processed.
If one cannot connect using SQL*Plus then using PRELIM option one can connect without allocation of a session. Information on PRELIM
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Sun Dec 26 11:47:18 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
$ sqlplus /nolog
SQL> set _prelim on
SQL> connect /as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/oradata/admin/TEST/udump/test_ora_11852.trc
– Wait for couple of minutes
SQL> oradebug dump systemstate 10
Statement processed.
Attach using debugger and dump systemstate
$ gdb /u01/oracle/product/10.2.0/db_1/bin/oracle 22878
GNU gdb Red Hat Linux (6.3.0.0-1.153.el4_6.2rh)
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type “show copying” to see the conditions.
There is absolutely no warranty for GDB. Type “show warranty” for details.
This GDB was configured as “x86_64-redhat-linux-gnu”…(no debugging symbols found)
Using host libthread_db library “/lib64/tls/libthread_db.so.1″.
Attaching to program: /u01/oracle/product/10.2.0/db_1/bin/oracle, process 22878
…
(gdb) print ksudss(10)
$1 = -1073762080
(gdb) print ksdhng(3)
$2 = 1
(gdb) detach
Detaching from program: /u01/oracle/product/10.2.0/db_1/bin/oracle, process 22878
(gdb) quit
Posted in General DBA, oradebug, trace | Tagged: attach, dump, gdb, linux, systemstate, trace | Leave a Comment »
How to find the last time a session performed any activity?
Posted by Amin Jaffer on August 27, 2010
In v$session the column last_call_et has value which tells us the last time (seconds) ago when the session performed any activity within the database.
select username, floor(last_call_et / 60) "Minutes", status
from v$session
where username is not null –– to ignore background process
order by last_call_et;
USERNAME Minutes STATUS
—————————— ———- ——–
SYS 0 ACTIVE
SCOTT 0 INACTIVE
SYSTEM 34 INACTIVE
..
Posted in General DBA, trace | Tagged: activity, call, last, last_call_et, session, time | Leave a Comment »
Using oradebug to dump call stack?
Posted by Amin Jaffer on July 22, 2010
Using “oradebug dump errorstack <dump level>” one can dump the call stack of the process after attaching to a running session. There are 3 values that can be passed as dump level
0 – dump error buffer
1 – level 0 with call stack
2 – level 1 with process state
3 – level 2 with context area
Example:
– Attach to a process
SQL> oradebug setospid 21906;
Oracle pid: 17, Unix process pid: 21906, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug dump errorstack 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oradata/admin/TEST/udump/test_ora_21906.trc
Information in the trace file:
…
*** 2010-07-22 21:35:56.554
*** SERVICE NAME:(SYS$USERS) 2010-07-22 21:35:56.554
*** SESSION ID:(27.1875) 2010-07-22 21:35:56.554
Received ORADEBUG command ‘dump errorstack 3′ from process Unix process pid: 22990, image:
*** 2010-07-22 21:35:56.554
ksedmp: internal or fatal error
—– Call Stack Trace —–
calling call entry argument values in hex
location type point (? means dubious value)
——————– ——– ——————– —————————-
Cannot find symbol
Cannot find symbol
Cannot find symbol
ksedst()+31 call ksedst1() 000000001 ? 000000001 ?
000000000 ? 000000000 ?
000000000 ? 000000001 ?
ksedmp()+610 call ksedst() 000000001 ? 000000001 ?
..
How to find/list the events set in a session?
Posted by Amin Jaffer on July 15, 2010
Using “oradebug dump events 1″ one can find/list events set in a session.
For example:
In a session events 10046 and 1410 events are set
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';
Session altered.
SQL> ALTER SESSION SET EVENTS='1410 trace name context forever, level 12';
Session altered.
SQL> SELECT distinct sid FROM v$mystat
SID
———-
131
In another session login as an account as sysdba
SQL> SELECT pid, spid FROM v$process where addr IN (SELECT paddr FROM V$session where sid = 131);
PID SPID
———- ——-
12 3420
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug dump events 1
Statement processed.
SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_10932.trc
Or in 11g:
SQL> oradebug eventdump session
1410 trace name context forever, level 12
sql_trace level=12
After running the above oradebug will dump the events in the trace file in the above case /u01/oradata/admin/TEST/udump/test_ora_10932.trc shows event 1410 and 10046 events set.
…
Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
974FED50 1410 1 974fede0 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
974FEBF8 10046 1 974fec88 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
*** 2010-07-15 23:40:05.671
..
Posted in General DBA, oradebug, trace | Tagged: dump, eventdump, events, list, oradebug, trace | 1 Comment »
How to suspend/resume a process using oradebug?
Posted by Amin Jaffer on July 15, 2010
Suspend a running process
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug suspend
Statement processed.
Resume a process
SQL> oradebug resume
Statement processed.
If you have trace turned on a suspended and resume session you will see the following messages in the trace file.
FETCH #5:c=54992,e=55962,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098751175228
EXEC #5:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098751175408
Received ORADEBUG command ‘suspend’ from process Unix process pid: 22990, image:
*** 2010-07-13 21:12:34.268
Received ORADEBUG command ‘resume’ from process Unix process pid: 22990, image:
FETCH #5:c=58991,e=32316703,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098783492125
EXEC #5:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098783492321
Posted in oradebug, trace | Tagged: continue, halt, oradebug, resume, session, suspend | Leave a Comment »
Using oradebug to set event
Posted by Amin Jaffer on July 14, 2010
The following example shows how to set event on a oracle session
To turn on event, needs to be run as sysdba
SQL> connect /as sysdba
– attach to session by OS process id
SQL> oradebug setospid 5243378;
Oracle pid: 68, Unix process pid: 5243378, image: oracle@hostname (TNS V1-V3)
– sets maximum dump file size to be unlimited
SQL> oradebug unlimit
Statement processed.
– turn trace on
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
To turn off event
– after gathering information from the same session which is already attached to ospid 5243378 turn off the trace
SQL> oradebug event 10046 trace name context off
Statement processed.
List of events can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg
Posted in oradebug, trace | Tagged: 10046, event, off, on, oradebug, oraus.msg, trace | Leave a Comment »
How to use dbms_monitor and dbms_session to turn on trace?
Posted by Amin Jaffer on June 20, 2010
Using dbms_monitor.client_id_trace_enable to turn trace on for all sessions that have client identifier set to 'debug' and using dbms_session.set_identifier which will turn on trace for session.
Example:
To setup trace for client identifier
SQL> exec dbms_monitor.client_id_trace_enable('debug', true, true);
PL/SQL procedure successfully completed.
– displays list of client identifers for which trace can be turned on
SQL> select trace_type, primary_id, waits, binds from dba_enabled_traces;
TRACE_TYPE
———————
PRIMARY_ID WAITS BINDS
—————————————————————- —– —–
CLIENT_ID
debug TRUE TRUE
PL/SQL procedure successfully completed.
In another session in which you want to turn on trace
– set client identifier on a session which will turn on trace
SQL> exec dbms_session.set_identifier('debug');
PL/SQL procedure successfully completed.
SQL> select sysdate from dual;
SYSDATE
———
20-JUN-10
To turn off trace
– to disable trace
SQL> exec dbms_monitor.client_id_trace_disable('debug');

Posted by MADHU APPS at
See more on»

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)