Tools

I have some broken links in my old blog entries right now, so if you’re looking for something, then download the whole zip file from here:

• http://tech.e2sn.com/oracle-scripts-and-tools

I have uploaded a .zip file (for Windows) and a .tar.gz file (for Unix/Mac). The scripts are all the same with differences in the CR/LF bytes in the files and the init.sql and i.sql which have some OS specific commands in them.

I also uploaded the latest PerfSheet there where I fixed an annoying bug which complained about some missing reference files when opening the file.

I plan to fix the broken links some time between now and my retirement.

People talk about the Oracle SQL Developer 3 being out, which is cool, but I have something even cooler for you today ;-)

I finally figured out how to convert my screen-recordings to uploadable videos, so that the text wouldn’t get unreadable and blurry.

So, here’s the first video, about a tool called MOATS, which we have built together with fellow OakTable Network member and a PL/SQL wizard Adrian Billington (of oracle-developer.net).

Here’s the video, it’s under 3 minutes long. Play the video in full screen for best results (and if it’s too slow loading, change it to lower resolution from HD mode):

Check it out and if you like MOATS, you can download it from Adrian’s website site (current version 1.05) and make sure you read the README.txt file in the zip!

• http://www.oracle-developer.net/utilities.php

Also thanks to Randolf Geist for finding and fixing some bugs in our alpha code… Note that MOATS is still kind of beta right now…

P.S. I will post my ORA-4031 and shared pool hacking video real soon now, too! :-)

P.P.S. Have you already figured out how it works?! ;-)

Update: Now you can suggest new features and improvement requests here:

• http://goo.gl/mod/0ANl

A lot of people have asked me whether there’s some sort of index or “table of contents” of my TPT scripts (there’s over 500 scripts in the tpt_public.zip file – http://tech.e2sn.com/oracle-scripts-and-tools )

I have planned to create such index for years, but never got to it. I probably never will :) So a good way to extract the descriptions of some scripts is this (run the command in the directory where you extracted my scripts to):

Free stuff! Free stuff! Free stuff! :-)

The awesome dudes at E2SN have done it again! (and yes, Tom, this time the “we at E2SN Ltd” doesn’t mean only me alone ;-)

On Tuesday 22nd March I’ll hold two (yes two) Secret Oracle Hacking Sessions – about ORA-04031: unable to allocate x bytes of shared memory errors, cursor management issues and other shared pool related problems (like fragmentation). This event is free for all! You’ll just need to be fast enough to register, both events have 100 attendee limit (due to my GotoWebinar accont limitations).

I am going to run this online event twice, so total 200 people can attend (don’t register for both events, please). One event is in the morning (my time) to cater for APAC/EMEA region and the other session is for EMEA/US/Americas audience.

The content will be the same in both sessions. There will be no slides (you cant fix your shared pool problems with slides!) but there will be demos, scripts, live examples and fun (for the geeks among us anyway – others go and read some slides instead ;-)!

• Note that the live event is over by now, but you can see the recording from my Oracle Troubleshooting TV show :-)

Due to a lot interest I’m going to do another run of my Advanced Oracle Troubleshooting v2.0 Online Deep Dive seminars in April and May (initially I had planned to do it no earlier than Sep/Oct…)

Check the dates & additional info out here:

• http://tech.e2sn.com/oracle-training-seminars

P.S. People who already attended the AOT2 seminars last year – I will schedule the follow-up Q&A sessions in mid-March!

I had a question about how to quickly identify which Oracle process runs out of which ORACLE_HOME on Linux.

I have uploaded a little script for that – it’s basically looking up all PMON process IDs and then using /proc/PID/exe link to find out where is the oracle binary of a running process located.

You may have to run this as root (as on some Linux versions I get “ls: cannot read symbolic link: Permission denied” error even when running this command as the owner of all Oracle homes (it seems to happen when your users UID and primary GID are different than thet setuid/setgid bits on the oracle binary):

oracle@linux03:~$ sudo ./findhomes.sh
   PID NAME                 ORACLE_HOME
  4421 asm_pmon_+ASM        /u01/app/oracle/product/11.2.0/db_1/
  4545 ora_pmon_demo112     /u01/app/oracle/product/11.2.0/dbhome_1/
  4547 ora_pmon_test112     /u01/app/oracle/product/11.2.0/dbhome_1/

You can use a similar approach on other Unixes too where the executable location or current working directory (CWD) is externalized in the /proc filesystem – or just use pmap to get this info instead.

And this time we have audio !!! (Wow!)

Following the huge success of my last hacking session, planned while drinking beer at Graham Woods OOW pre-party and delivered from Miracle’s massive Oracle Closed World event in Thirsty Bear (between drinking beers), I’m announcing another hacking session:

What: Using Session Snapper for flexible Oracle Performance Troubleshooting

When: Wednesday 27th Oct 9:00-10:00 AM PDT (US West coast / California time). Check what’s this in your time zone here

Where: Internet! -> Sign up here: http://tech.e2sn.com/secret

You’ll need to register fast and be “there” on time as my current GotoWebinar account only allow 100 attendees to log on… last time over 100 people signed up, but “luckily” less actually showed up, so nobody got left outside!

BTW, I have figured out what went wrong with audio last time and caused my voice in the end of presentation disappear). A program, which I accidentally launched via a keyboard shortcut, grabbed my Mic input to itself, so gotowebinar’s app couldn’t access it anymore.

• People who will attend my Advanced Oracle Troubleshooting online class or the Virtual Conference I’m organizing with Jonathan, Cary and Kerry – you can also log on to make sure that the (gotomeeting) technology works out OK for you.

See you soon!

I haven’t written any blog entries for a while, so here’s a very sweet treat for low-level Oracle troubleshooters and internals geeks out there :)

Over a year ago I wrote that Oracle 11g has a completely new low-level kernel diagnostics & tracing infrastructure built in to it. I wanted to write a longer article about it with comprehensive examples and use cases, but by now I realize I won’t ever have time for this, so I’ll just point you to the right direction :)

Basically, since 11g, you can use SQL_Trace, kernel undocumented traces, various dumps and other actions at much better granularity than before.

For example, you can enable SQL_Trace for a specific SQL_ID only:

SQL> alter session set events 'sql_trace[SQL: 32cqz71gd8wy3] 
{pgadep: exactdepth 0} {callstack: fname opiexe}
plan_stat=all_executions,wait=true,bind=true';


Session altered.

Actually I have done more in above example, I have also said that trace only when the PGA depth (the dep= in tracefile) is zero. This means that trace only top-level calls, issued directly by the client application and not recursively by some PL/SQL or by dictionary cache layer. Additionally I have added a check whether we are currently servicing opiexe function (whether the current call stack contains opiexe as a (grand)parent function) – this allows to trace & dump only in specific cases of interest!

At Hotsos Symposium Training Day I used rlwrap with sqlplus – which gives nice command line editing and history capabilities for tools like sqlplus. Additionally I pre-generated commonly used Oracle keywords, data dictionary view and package names into rlwrap wordfile, so I got nice tab-completion too. Sqlplus sucks much less with rlwrap ;-)

It’s relatively easy to install rlwrap on Unix (there are rlwrap RPMs out there, Solaris freeware packages and I installed it on Mac via macports.org). Just google around…

You can have rlwrap on Windows too – As rlwrap has been coded for Unix flavors, then on Windows you need to run it on a Unix library environment emulator – like Cygwin.

Dave Herring and Michael Paddock have both written an article about how to get rlwrap & sqlplus running on Windows, check out the articles here. It’s worth reading both as they have different additions…

So, if you want command line history, search and tab completion for sqlplus on Unix flavors or Windows, check these articles out!

http://daveherringsdbablog.blogspot.com/2010/03/sqlplus-command-history-with-cygwin.html

http://blog.enkitec.com/2010/04/29/using-sqlplus-with-rlwrap-on-ms-windows/