Search

Top 60 Oracle Blogs

Recent comments

Internals

cursor: pin S waits, sporadic CPU spikes and systematic troubleshooting

I recently consulted one big telecom and helped to solve their sporadic performance problem which had troubled them for some months. It was an interesting case as it happened in the Oracle / OS touchpoint and it was a product of multiple “root causes”, not just one, an early Oracle mutex design bug and a Unix scheduling issue – that’s why it had been hard to resolve earlier despite multiple SRs opened etc.

Martin Meyer, their lead DBA, posted some info about the problem and technical details, so before going on, you should read his blog entry and read my comments below after this:

Problem:

So, the problem was, that occasionally the critical application transactions which should have taken very short time in the database (<1s), took 10-15 seconds or even longer and timed out.

Symptoms:

KGH: NO ACCESS – Buffer cache inside streams pool too!

Some time ago I wrote that since Oracle 10.2, some of the buffer cache can physically reside within shared pool granules.

I just noticed this in an 11.2 instance:

Calculate SQL_ID and SQL_HASH_VALUE from SQL text

Some time ago I wrote an article about the 10g+ SQL_ID being just a hash value of the SQL statement text. It’s just like the “old” SQL_HASH_VALUE, only twice longer (8 last bytes instead of 4 last bytes of the MD5 hash value of SQL text).

Slavik Markovich has written a nice python script for calculating SQL_IDs and SQL hash values from SQL text using that approach.

Slavik’s article is available here:

http://www.slaviks-blog.com/2010/03/30/oracle-sql_id-and-hash-value/

Share/Bookmark

Oracle Latch Contention Troubleshooting

I wrote a latch contention troubleshooting article for IOUG Select journal last year (it was published earlier this year). I have uploaded this to tech.E2SN too, I recommend you to read it if you want to become systematic about latch contention troubleshooting:

http://tech.e2sn.com/oracle/troubleshooting

I’m working on getting the commenting & feedback work at tech.E2SN site too, but for now you can comment here at this blog entry…

Share/Bookmark

Oracle Troubleshooting: How to read Oracle ERRORSTACK output?!

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:

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

Share/Bookmark

Oracle Wait Event reference

Kyle Hailey has started putting together a much needed Oracle wait event reference.

You can access it here.

By the way, Oracle documentation also has a wait event reference section, it has more events, but it’s less detailed…

I have plans to go deep into some wait events and cover some less common ones in tech.E2SN too… in the future ;-)

Share/Bookmark

ORA-31098: Internal event to turn on XDB tracing

“Do not document”…

…if not only due to that comment, it is a very interesting event, if not only that it seems that it is used for multiple items and not only tracing. I tried to figure out what I could do with this event regarding the XDB Protocol Server trying to figure out how it works and to trace a partially documented/undocumented feature in the manuals that makes use of the XDB Protocol Server.

The XDB Procotol Server architecture is used for more than the (APEX) PL/SQL Gateway. It also supports HTTP, FTP and WebDAV (so called) “servlets” and a hook-in into C kernel library that enables the XMLDB Native Database Web Service (NDWS).


The following should only be done asked by Oracle Support and/or are at your own risk. Always test on a test environment (so if when the database is corrupt is not a big deal)

Anyway, “events” can be set on session and system level and/or via the oradebug facility. Julian Dyke has a good post on the basics. As Julian describes, there are four types of numeric events: Immediate dumps, Conditional dumps, Trace dumps, Events that change database behavior. The “fun” with the ORA-31098 seems that is used for multiple things. It at least creates DDL scripts in trace file during dbms_xmlschema registration and it also traces XDB Protocol Server issues. During X-Mas and new years eve I had some time, so I tried to figure out some of them…

Described in a small overview XDB event post, some of these settings can be found somewhere in the Oracle manuals…

Debugging XML Schema Registration

ORA-31098: Internal event to turn on XDB tracing