Wolfgang Breitling has written a snapper.pl script – Perl version of my original snapper.sql one. Check it out!

As his script doesn’t rely on DBMS_OUTPUT for generating output, you can easily get real time continuous output with his script, without needing to wait until the script finishes or by using somewhat complicated DBMS_SYSTEM.KSDWRT tracefile output.

Also, as his script doesn’t rely on DBMS_LOCK package for sleeping, so you don’t need rights on it.

Obviously you need Perl and Oracle DBI drivers installed in your client machine for using the script. Note that the default Unix/Windows Perl installations do not have Oracle DBI drivers installed, so you might be better off using the perl coming with Oracle server installation itself (or you can download the DBI modules from CPAN if you’re not afraid of little configuration work).

So, I wish you happy snapping for the new year! :)

Tanel Poder Administration, Cool stuff, Performance, Tools

If you need to run and manage loads of sqlplus scripts which call other scripts, which call other scripts etc, then you are probably interested in the sqlplus APPINFO parameter shown below.

When you issue SET APPINFO ON in sqlplus, this makes sqlplus to automatically call DBMS_APPLICATION_INFO.SET_MODULE and set the MODULE value to sql script name which is currently being executed.

This allows you to easily pass the current script name info to Oracle, without the need to have a manual call to SET_MODULE in beginning and end of every script (along with some mechanism for storing the previous module).

A simple example is below. I used two scripts blah.sql and blah2.sql for my test:

C:\tmp>type c:\tmp\blah.sql

select sys_context('USERENV', 'MODULE') from dual;

@@blah2

select sys_context('USERENV', 'MODULE') from dual;

 C:\tmp>type c:\tmp\blah2.sql

select sys_context('USERENV', 'MODULE') from dual;

So, blah.sql reports the current module, then calls blah2.sql which reports current module and then returns back to blah.sql which returns the current module again.

SQL> set appinfo on
SQL>
SQL> @blah

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
01@ blah.sql

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
02@ blah2.sql

SYS_CONTEXT('USERENV','MODULE')
------------------------------------------------
01@ blah.sql

SQL>

Looks cool!

From output above we can see the following things:

  Read more…

Tanel Poder Administration, Cool stuff, Oracle, Tools

Update: Since this article was written I’ve released multiple updates to my Snapper script, so you should also read other Snapper related articles in my blog and my new website:

• http://tech.e2sn.com/oracle-scripts-and-tools/ (usage and download)
• http://blog.tanelpoder.com/?s=snapper (previous snapper articles)

—————————————

I have released the version 1.06 of Oracle Session Snapper.

If you don’t know what Snapper is, check out those blog entries first:

• http://blog.tanelpoder.com/2007/08/20/oracle-session-snapper-real-time-session-level-performance-stats-for-dbas/
• http://blog.tanelpoder.com/2007/08/26/oracle-session-snapper-part-2-getting-most-out-of-snapper/

Improvements:

• Ability to report instancewide Latch and Enqueue get deltas (useful for testing code efficiency in dev environments where there’s no other activity going on)
• Header format and header options have changed slightly – for making it simpler to quickly paste & graph the data in Excel
• Some bugs have been fixed (and probably new ones introduced)

How to read Snapper output?

Here are the column definitions (sorry for crappy formatting, I need to do some CSS tweaking once I get a chance):

Examples:

Read more…

Tanel Poder Administration, Oracle, Performance, Tools, Troubleshooting