Usn's IT Blog

In early April, I had the honour to attend Craig Shallahamer’s Oracle Performance Firefighting and Advanced Performance Analysis courses. It was a great opportunity, since he came to Frankfurt which is just a three hours drive away. It was the second time meeting him – and it was great, again. The last two days have been a bit dry by their theoretical nature, and the last exercises on friday are only of limited use for me. But especially the first three days of firefighting have been really useful and most valuable due to their basic technology insights and the teached strategies “how to be sure”.

Craig Shallahamer in Frankfurt 2011

Read more…

The newest piece of my bloomer collection is a query with an execution plan that has 18E of cost. I am no believer in absolute cost numbers, but this one is just nice.

This exa-executionplan is the new definition of Exadata….

Keep your fingers off ;)
Usn

I have not been able to blog for a while. My family has grown, and so there have been different priorities.

In the meantime, I was told to become responsible for a few MS SQL Server databases as well. I took it for a challenge, and started digging into a different world. Well, sometimes different, sometimes similar.

One of the first questions I will have to answer for myself is the penalty for using snapshot read consistency – you know, avoiding read locks in the Microsoft way: Copy the block into tempdb, maybe anybody will need it. As an IO- and performance junkie, my backside notices pain in advance. We will see.

Take care
Martin Klier

For the last three days, I have been in Nuremberg, attending the DOAG Konferenz 2010 (annual conference of the german Oracle user group). This year, my list of “Look at’s” is way shorter than last years list was. It’s not the fault of the event, I just did not write down that many facts.

My general impression was, that the number of talks intersting for DBAs was limited, but all talks I attended have been of high technical quality. Speakers may be more or less gifted, but I am enough of a technican to read behind the presentation to appreciate it.

Here’s my list. Not comprehensive, but this is my basic “lessons to take”:
Day 1:

• Carefully decide how far to rely on RAT (Real Application Testing) feature, it’s not the Golden Bullet.
• Once again, Oracle changed an established name: FRA is now “Fast Recovery Area”
• Use RMAN’s “configure for” command in a dataguard environment
• Use auto-delete for archived redo logs
• Calculate your next migration with Oracle GoldenGate licensing
• Son Of A Preacher Man: stay an IOPS evangelist, if you happen to be a DBA

Day 2:

• Dive deep into SQL*Net / TNS / tcp optimization. Thanks to Dr. Mensah for the enlightenment!
• Oracle now SUPPORTS the RAC on VMware. No certification, but at least something to take home.
• Dear Larry, please make Oracle-at-VMware licensing accept VMware-side CPU assignment, FINALLY!
• Most constraints can have an optimizer upside! Thanks, Mr. Senegacnik!
• 11.2.0.2 has lots of new unexpected features. Unusually. Both in RAC and single instance. Nice!

Day 3:

• Immediately look at the 11.2.0.2 “hang manager”. Thanks, Markus Michalewicz!
• Try a $ORACLE_HOME in ACFS on your next RAC test, looks cute.
• Oracle Clusterware process architecture is confusing, but changes worse in 11.2.0.2, for the first glance. Docs are still a mess, but Dr. Grebe unwired lots of them. Maybe the highlight of my conference 2010!

to be continued…

Best regards
Martin

For all who may concern ;)

Oracle 11.2.0.2 is out for Linux x86 and Linux x86_64

It’s patch set 10098816!

Packaging: Starting with the first patch set for Oracle Database 11g Release 2 (11.2.0.2), Oracle Database patch sets are full installations of the Oracle Database software. In past releases, Oracle Database patch sets consisted of a set of files that replaced files in an existing Oracle home. Beginning with Oracle Database 11g release 2, patch sets are full installations that replace existing installations.

Have a look at Document ID 1189783.1 please.

EDIT: Being withdrawn within hours after publishing, the patch is back available now at support.oracle.com.

Be careful!
Martin Klier

Please, do yourself a favour and NEVER EVER use ASM on Windows!

“Dear Sir,

obviously I forgot to put a file system on drives E:, F: and G: of the
DB machine. I did this now, and hope you excuse the delay.

Best regards
John Doe
Windows Server Administrator”

/* no comment */
Martin

This is my example schema, basis for the DB tuning lectures. Please use your “save as” function or copy the URI to use wget.

expdp_klm1.dmp.bz2
expdp_klm2.dmp.bz2

Regards
Martin Klier

As a follow-up of my last post, I learned that creating traces is much simpler in 11g than I expected it to be. Dion Cho and Tanel Poder pointed me to some interesting links

http://oraclue.com/2009/03/24/oracle-event-sql_trace-in-11g/

http://tech.e2sn.com/oracle/troubleshooting/oradebug-doc

describing how to set up a trace event on one SQL_ID without need to do it on session level (“alter system” possible). A quick copy&paste of an example to demonstrate the value:
alter system set events ‘sql_trace [sql:b6z8h59a39gv9]‘;

Great? Great. All connection-pool plagued DBAs will like it I guess.
Have a good trace
Martin Klier

Once again, there was a lesson to be learned about the Oracle RDBMS. The occaison was a direct upgrade from Enterprise Edition 10gR2 to EE 11gR2, the application was kept untouched for good reason.

Problem
After running the new version for two hours, everything became incredibly slow, and the CPU load on the DB server was hitting 100%. Research in the Oracle Wait Interface quickly showed me the wait event: “Cursor: mutex S”. The mutex S is a serialization mutex for the cursor cache. It is involved, as soon as two sessions try to (hard/soft) parse the same statement in sense of SQL_IDs.

A quick look into the cursor cache with v$sql immediately showed an UPDATE statement that has more than 1000 child cursors for the same SQL_ID, it was very likely to have found the culprit, because creating new child cursors means expensive hard parsing, and searching the list of existing children means CPU load as well.

During the next hours, it turned out that on 11gR2 for this machine and constellation off peak hours (thus with low DB load), the critical mass was around 1500 cursors. Beyond this point, the server became slow as described above: The mutex S ate up all CPU power available. In peak time, with lots of DB activity and high basic load, the critical number of child cursors for this UPDATE was around 300, so about 1/5 of “peacetime”. With a growth of 200 new child cursors per minute, expect no fun!

So what can we do about it?
Read more…

The oracle-l mailing list made me try a new tool, created by David Litchfield. It’s called DDLDUMP V. 0.1 and extracts DDL from redo logs or archived redo logs, the output format is XML. Since it’s a windows .exe file for now, I had to use wine to test it, but it works flawlessly, and David promised to provide Linux binaries as well.

Look here:
Read more…