Using oradebug – 10053 and 10046 events

Hello There! Hope this article finds you well. Today we will talk about oradebug, this little wonder in oracle, and how to set the 10046 and 10053 events to troubleshoot performance.

First of all, what is oradebug?

Oradebug is a sqlplus tool, intended to oracle support personel only – however, it can be very handy in a bunch of situations. You can use it to set specific troubleshooting events, use to diagnose on which interface your RAC is using for the interconnect messages, use to execute a hanganalyze ( the command, not me, okay? =] ), system state dumps, errorstack dumps and the list goes on.

How do I use it?

First of all, you need to attach oradebug to a specific session/process.

Using the ospid:

Get the os pid for the process, usually with and ps command, or getting the thread in windows environment. With the ospid (in this example 1177)

oradebug setospid 1177;

If you try to attach oradebug to an inactive process, you will receive:

SQL> oradebug setospid 13050;
ORA-00072: process “13050” is not active

If everything went smoothly, you will receive:

SQL> oradebug setospid 16792;
Oracle pid: 150, Unix process pid: 16792, image: oracle@BLASERVER12

Using the SID to attach oradebug:

First of all you need to find out the orapid for the session, running the sql below:

select
p.pid orapid
from v$process p, v$session s
where s.sid = &SID
and p.addr = s.paddr
/

 

Example:

SQL> select
p.pid orapid
from v$process p, v$session s
where s.sid = &SID
and p.addr = s.paddr
/ 2 3 4 5 6
Enter value for sid: 651
old 4: where s.sid = &SID
new 4: where s.sid = 651

ORAPID
———-
152

SQL> oradebug setorapid 152;
Oracle pid: 152, Unix process pid: 5392, image: oracle@BLASERVER12 (TNS V1-V3)

You are ready to go!

Setting the 10046 event

oradebug event 10046 trace name context forever, level 12;

Setting the 10053 event

oradebug event 10053 trace name context forever, level 1;

Why this is important?

You can use the 10046 to get sql specific informations, such as object, parsing, elapsed time, wait events, bind variables. It is an alternativa for dbms_monitor and dbms_system, another way to generate a sessions trace, and it could be “tkprofed” as well.

If you wanna to understand the optimizer’s decicions, there is no better way that using a 10053 trace. You will understand the cost estimates, access paths, query block names, peeked binds, optimizer parameters used during the parse.

In another post I’ll give detail about the levels, and why we type “name context forever”. This is a how to post only =]

See you around!

Advertisements
This entry was posted in Performance, Troubleshooting and tagged , , , , . Bookmark the permalink.

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