Here’s a code snippet for identifying current tracefile name using SQL. Yep I know there are many such examples online, but I haven’t found any so far which also account for TRACEFILE_IDENTIFIER variable.

Luckily the value of this variable is accessible from V$PROCESS, so we can write a query which constructs us the full tracefile name, including TRACEFILE_IDENTIFIER part, if any:

SQL> alter session set tracefile_identifier = blah;

Session altered.

SQL> select value ||'/'||(select instance_name from v$instance) ||'_ora_'||
  2         (select spid||case when traceid is not null then '_'||traceid else null end
  3               from v$process where addr = (select paddr from v$session
  4                                           where sid = (select sid from v$mystat
  5                                                      where rownum = 1
  6                                                 )
  7                                      )
  8         ) || '.trc' tracefile
  9  from v$parameter where name = 'user_dump_dest'
 10  /

TRACEFILE
---------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/lin11g/LIN11G1/trace/LIN11G1_ora_14639_BLAH.trc

1 row selected.

Let’s see if this actually worked:

LIN11G1$ head /u01/app/oracle/diag/rdbms/lin11g/LIN11G1/trace/LIN11G1_ora_14639_BLAH.trc
Trace file /u01/app/oracle/diag/rdbms/lin11g/LIN11G1/trace/LIN11G1_ora_14639_BLAH.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1
System name:    Linux
Node name:      linux01
Release:        2.6.18-53.el5
Version:        #1 SMP Sat Nov 10 18:24:52 EST 2007
Machine:        i686
LIN11G1$

Yep! :)

Tanel Poder Administration, Oracle, Tools, Troubleshooting

My friend asked today a question that how to identify why his Oracle 9.2 database has suddenly started generating loads more redo than usual.

So obviously I recommended him Snapper as first thing, it’s perfect for ad-hoc analysis like that! ( I know I sound biased but if you haven’t used Snapper yet, then now is the time! :)

So, I asked him to run Snapper on all sessions of the instance with 10 second interval and find the session with highest “redo size” delta figure.
However as a normal production database has loads of sessions in it and as each session can return tens of statistic rows, then you might need to do some post-processing of output data to get only the “redo size” numbers.

So I figured that why not do the filtering in Snapper itself, so I made the small additions and released Snapper v1.07.

Usage is simple:

SQL> @snapper out,gather=s,sinclude=%redo_size% 10 1 "select sid from v$session"

-- Session Snapper v1.07 by Tanel Poder ( http://www.tanelpoder.com )

---------------------------------------------------------------------------------------------------------------------------------------------
HEAD,     SID, SNAPSHOT START   ,  SECONDS, TYPE, STATISTIC                               ,         DELTA,  DELTA/SEC,     HDELTA, HDELTA/SEC
---------------------------------------------------------------------------------------------------------------------------------------------
DATA,     129, 20080530 09:07:20,       10, STAT, redo size                               ,     180595100,   18059510,     180.6M,     18.06M
DATA,     166, 20080530 09:07:20,       10, STAT, redo size                               ,        790912,      79091,    790.91k,     79.09k
DATA,     167, 20080530 09:07:20,       10, STAT, redo size                               ,        889796,      88980,     889.8k,     88.98k
--  End of snap 1

PL/SQL procedure successfully completed.

And in the result you already see that there is a single session which generates significantly more redo than others (btw this is an artificially generated example). In my friend’s case there also happened to be a single session , scheduled through DBMS_JOB, contributing to most of the redo generation. Once the troublemaker was identified, the problem could be fixed.

There are few things to note:

Read more…

Tanel Poder Oracle, Performance, Troubleshooting

I have uploaded the slides of my “Performance and Scalalability Improvements in Oracle 10g and 11g” presentation here.

Tanel Poder Internals, Oracle, Oracle 11g, Performance

There was a question in a recent Oracle-L thread about various uses of autonomous transactions.

Autonomous transactions can be very useful for a PL/SQL application logging, but sometimes they are also abused to cope with bad application design (like avoiding mutating table errors in triggers etc).

I’m not going to start on that topic here though, but instead presenting another case where autonomous transactions have helped me to work around a problem. It’s more a hack than a real solution though, but may be useful for someone else too.

My Snapper tool requires execute rights on DBMS_LOCK in order to sleep between snapshots. Sometimes when troubleshooting an urgent performance issue, I have had access only to some kind of application support account, without permissions to execute DBMS_LOCK.SLEEP. And sometimes it takes too long to get those rights granted by corporate DBAs.

So one workaround I’ve used so far is creating a fake DBMS_LOCK.SLEEP proc in the local support schema along with one dummy table and use a combination of SELECT FOR UPDATE and autonomous transactions to sleep for short time.

The code is very simple:

Read more…

Tanel Poder Cool stuff, Internals, Oracle