trace_column.sql 1.1

For a long time, as a DBA, I mostly didn’t have access to tuning tools, like the Diagnostics Pack. Oracle’s Diagnostics Pack is very handy and I’d recommend it to to anyone who has to deal with performance problems in Oracle databases. But you can do performance tuning without it. One, very exact, way of gathering performance data, it tracing a session – with wait events, also known as event 10046 SQL tracing.

The biggest challenge in SQL tracing, is starting the trace in time. If you are too late, you might have missed a slow SQL statement at the beginning and you are looking at data for Cursor #0. And you have no idea what Cursor #0 is.

So quickly starting a trace is the key. If you start a trace by hand, you still have to find the sid, and the serial# for the right session. First: what is the right session? What do you base that on? username? osuser? program? machine? And when you have found the right session, then you slowly type in the dbms_monitor command. What was that syntax again?

To speed things up, I use my trace_column.sql script. To use it you need to know two things beforehand: based on what column in v$session do you want to find your session. And two: for what values do you want to start a trace? (And you can enter a trace identifier, although I haven’t tested that very thoroughly yet). trace_column.sql automatically starts the trace and, also generates a script to stop the trace. That is important too, because you don’t want to fill up your file system with traces.

You can create a neat readable HTML report of the trace file with OraSRP.


About Marcel-Jan Krijgsman

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
This entry was posted in Oracle performance tuning and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s