Administration

Gwen Shapira has written an article about a good example of a non-trivial performance problem.

I’m not talking about anything advanced here (such as bugs or problems arising at OS/Oracle touchpoint) but that sometimes the root cause of a problem (or at least the reason why you notice this problem now) is not something deeply technical or related to some specific SQL optimizer feature or a configuration issue. Instead of focusing on the first symptom you see immediately, it pays off to take a step back and see how the problem task/application/SQL is actually used by the users or client applications.

In other words, talk to the users, ask how exactly they experience the problem and then drill down from there.

The LatchProf and LatchProfX scripts allow you to be more systematic with latch contention troubleshooting and tuning. No more guesswork is needed as these scripts give you exact session IDs and in this version also SQLIDs of the troublemaking applications.

You can download the new versions here:

• LatchProf (reads V$ views)
• LatchProfX (reads X$ tables, but gives better info, run as SYS)

Example output (with SQLID info) is below:

I have written the first article to the troubleshooting section of my new website tech.E2SN.com:

It’s about a very valuable Oracle troubleshooting tool -> ERRORSTACK trace.

I cover 4 frequently asked questions there:

1. Reading the current executing SQL statement text from errorstack trace
2. Reading the current executing PL/SQL package and PL/SQL source code line number from errorstack trace
3. Reading the current bind variable values from errostack trace
4. Identifying how much private (UGA) memory a cursor is using

You can read it here:

• http://tech.e2sn.com/oracle/troubleshooting/how-to-read-errorstack-output

By the way, if you like my new website, feel free to link to it !!! ;-)

• http://tech.e2sn.com