Oracle analysis 101 : Begining analysis techniques

Tuesday Dec 09, 2008

Recently, I was asked to present beginning Oracle analysis techniques to an internal audience of Sun engineers. This presentation was a lot of fun to put together and was well received. After cleaning it up a bit and taking out the boring internal Sun stuff, I thought the presentation might be useful to a larger audience. This presentation focuses on problem statement, environmental, and basic AWR/Statspack analysis.

Presentation: Oracle analysis 101

If you find this useful or have suggestions, drop me a note.

Like this post? | | | |

Posted at 02:05PM Dec 09, 2008 by glennf in Oracle
Tags: 10046 analysis awr cmt database databases debug method-r oracle performance solaris statspack

Threshold login triggers for Oracle 10046 event trace collection

Tuesday Jul 11, 2006

There are multiple ways to gather trace data. You can instrument the application, pick an oracle sid from sysdba, turn on tracing for all users (ouch), or use a login trigger to narrow down to a specific user. Each of these methods have merit, but recently I desired to gather traces at various user levels.

The problem with most packaged applications, is that they all use the *same* userid. For this Oracle 10G environment, I used this fact to filter only connections of the type that I wanted to sample. I wanted to gather 10046 event trace data when the number of connections was 10, 20, or 30. To achieve this, I used a logon trigger and sampled the number of sessions from v$session to come up with the connection count. I have found this little trick to be very useful in automating collection without modifying the application. I hope this can be useful to you as well.

create or replace trigger trace_my_user
  after logon on database

DECLARE
  mycnt  int;

BEGIN

SELECT count(*)
 INTO mycnt
 FROM v$session
 WHERE username='GLENNF';

 if (user='GLENNF') and ((mycnt=10) or (mycnt=20) or (mycnt=30)) then
   dbms_monitor.session_trace_enable(null,null,true,true);
 end if;
end;
/

Like this post? | | | |

Posted at 10:51AM Jul 11, 2006 by glennf in Oracle
Tags: 10046 databases dbms_monitor event hotsos oracle orasrp performance session_trace_enable solaris sun trace trigger

"orasrp" - Free Oracle response time based trace profiler.

Tuesday Jun 21, 2005

Ever since I started reading the "Optimizing Oracle Performance" book by Cary Millsap, I have been salivating over getting a copy of the Hotsos profiler - but alas it is too expensive. Recently I tried to find a copy of their free version "Sparky" but it had been pulled due to support issues.

Finally, I stumbled upon orasrp. This analyzer is written in python and can be used standalone to produce html, or you can browse a directory of trace files via your web browser.

Like this post? | | | |

Posted at 10:51AM Jun 21, 2005 by glennf in Oracle
Tags: 10046 databases event hotsos method-r oracle orasrp performance solaris sun tracing