Usn’s IT Blog

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…

srvctl in Oracle Grid Infrastructure 11g Release 2 has at least one discrepancy between docs and functionality.

I saw EONS consuming lots of memory in a non-database grid installation, and wanted to get rid of it. (The eONS is used by Oracle Enterprise Manager to receive notification of change in status of components managed by Oracle Restart.) Documentation tells us, it would work:
See the docs for removing eons with “srvctl remove eons”.

But if you try in reality:

[oracle@ASM01 ~]$ LANG=C srvctl remove eons -f -v
Usage: srvctl <command> <object> [<options>]
commands: enable|disable|start|stop| <...>
objects: database|instance|service| <...>
For detailed help on each command and object and its options use:
srvctl <command> -h or
srvctl <command> <object> -h
PRKO-2013 : eons object is not supported in Oracle Clusterware
[oracle@ASM01 ~]$

Repeat: PRKO-2013 : eons object is not supported in Oracle Clusterware
Nice.

Read more…

Many topics are to be found on the web about IMPDP and UNDO_RETENTION. This one will become a more theoretical one, around the central question: Why, please why does a huge IMPDP fail with ORA-1555 if there is too less UNDO_RETENTION configured?

Like so often, these questions come from a problem. I had to impdp about 30GB from one machine to another via database link. (For newcomers to this technology, this is done via impdp on the target machine, no expdp needed, no dumpfile is created.) The impdp failed with ugly ORA-1555s after about 2 1/2 hours. It happened after the import itself, but during the index creation. The web said in several places, that increasing the UNDO_RETENTION period will help, but I have not been able to explain it. For my understanding, UNDO_RETENTION was something to enable flashback-table-like operations over a longer period in a busy database.

Oracle Support
So I opened a Service Request in order to get some clarification. The result was:

QUESTION
——–
Why does the undo_retention affect the impdp behaviour?

Read more…

If you plan to install Oracle products, be prepared for surprise of random kind. :) The new Grid Infrastructure is no exception of this rule: I tried to install it on a SuSE Linux Enterprise Server (SLES) 10.

Quick facts about the setup:

• SLES 10 SP 2 64bit
• Oracle Grid Infrastructure 11.2.0.1 x86_64
• all devices are specified via UDEV symlinks like /dev/disk/by-id/scsi-1HITACHI_732342670003

Problem description

Nearly everything went well, but $GRID_HOME/root.sh failed this way:

ORA-27091: unable to queue I/O
ORA-15081: failed to submit an I/O operation to a disk
ORA-06512: at line 4
PROT-1: Failed to initialize ocrconfig

(By the way, see how to repeat root.sh here.)

This just tastes like catastrophe: Why does a full-fledged SAN-provided LUN has I/O errors? The cross-check with “dd” cooled me down immediately, because it did not show any problems, reading and writing provided full power, no stalls and no errors. So calmly looking up My Oracle Support (fka Metalink) brought me to Article ID 955550.1 that’s talking about ASMLIB. I did not use ASMLIB because I don’t like it, but they solved their problem by an owner change of the device used for OCR. The trouble described there is, that the Oracle Clusterware does not accept OS user “oracle” just as a member in unix group “disk” that has rw permissions on the device, no, user “oracle” has to be the owner of the device!

Read more…

To clean up a tilted installation of Oracle Grid Infrastructure isn’t that intuitive. I had to do it today, and this is my collection what to do and what to read. It’s made for repeating root.sh, that’s not allowed to run twice or more times without cleaning up in between.

So first have a look at Coskan’s page, which contains all hints I did need. By the way, he had the same problem that made me trying this:

ORA-27091: unable to queue I/O
ORA-15081: failed to submit an I/O operation to a disk
ORA-06512: at line 4
PROT-1: Failed to initialize ocrconfig

Furthermore, read in the official docs about the correct cleanup procedure. The keyword is: Deconfiguring Oracle Clusterware Without Removing Binaries

Last but not least, metalink notes 942166.1 and 955550.1 (for Linux) might be enlighting.

Summary, for the folks in a hurry:

# $GRID_HOME/root.sh
... fails ...
# $GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force [-lastnode]
... fix your problem ...
# $GRID_HOME/root.sh
... hopefully - works!

Be careful
Martin Klier

This year, I will talk again about basics of DB technology for 9th grade of AGW (Augustinus Gymnasium Weiden, a secondary school) again. The same event has been a success in the last years, and it’s simply nice there.

Nice? Please allow me a word about this school. They have an engaged teacher, Klaus Märker, who does a lot more of IT teaching than he would have to. His voluntary IT classes are simply looking great and promising. It’s a big opportunity for the kids, to learn some IT specials during their school time. I hope, you know and value what he does for you.

It was self-evident for me to support his approach on showing the young ones more than ministry officials creating an official curriculum can imagine. Database technology is some kind of “poor cousin” in IT schooling. I see that every day. I am not under the imagination that the girls and boys there will become database pros from one lecture, may it be good or bad. But they have a chance to see more than their history-, latin- or maths books. And be honest, at least, if you don’t use a database by yourself, your name is already inside a pretty good one.

Ok, enough of this monologue, here comes the paper.

Hopefully, it will be fun again,
Martin

Sometimes you simple need to know whether a statement is fast or not, or why it is slow. (Sadly, it seems like virtually nobody wants to know why a statement is fast, but that’s another story…)

Do not use EXPLAIN PLAN in such cases, it does not (can not) deal bind variables right. I’ve got  a simple script in my toolbox to create a real execution plan using bind variables, but avoid displaying all the lines the SQL returns. Just have a look, and use it if it’s suitable for your needs. Please keep in mind, that you need to set the binds the way I do, because the cost based optimizer handles bind statements entirely different (bind peeking, (adaptive) cursor sharing …)

This script was designed to run in Oracle SQL Developer very well, but it seems that version 2.1 has trouble with the LAST cursor, due to some changed output.
Read more…