OUGN 2012 Day 1 - At sea

This is my first conference as a board member, and going to a presentation for every slot is not possible. I did go to Doug Burns presentation on SQL Plan Management in 11g though I had actually seen it at OOW 2011, probably because I like Doug's presentation style. This preso does not go into very much detail on SPM, but takes us on a travel through the subject and leave it to us to actually get some experience on it. It does look interesting, and the only thing that keeps me from getting much experience on it is that the current customer is lagging behind on the upgrades to 11g; the few we have on 11g are small and have not presented any problems with plan instability.

Maria Colgan's presentation on how to collect statistics was a big hit. A colleague stated this was perhaps the best presentation he had been to ever. Very entertaining presentation and packed with useful take aways. Yes, I did go to the same presentation at OOW 2011 as if I'm unwilling to expose my brains to something new. But in fact I learned something new, and I still have something to try out, for example next time I encounter a database that uses partitioning, sigh.

The RAC Attack team set up in the public area and a couple of enthusiasts insisted to complete the lab from Day 0. The intention was not only to have a lab on board, but also a place where people could come and go, and ask questions on RAC in particular and about life in general. Think it worked well, but obviously the hands-on lab was competing with the sessions, and most concluded that lab was something they could try at home later.

The feedback we got after Day 1 was great, but I'll save that to the wrap-up.

OUGN 2012 Day 0 - The Martins

23:48, so tired that a Twitter message would be more appropriate. But, just wanted to make it for the record that today turned out to be a very pleasant day. I had the responsibility to host track #5, which started with Martin Büchi on Information Lifecycle Management in OLTP DBs with Partitioning. ILM is a subject that many postpone because it is too complicated. In Martin's presentation he showed a reference model, illustrated various scenarios with alternative solutions. He then showed how partitioning can be used to archive data that has been aged out. I cannot possible make justice to his presentation, but since I write about it here I'm sure I'll remember to come back to it, download it and digest it. If Mr Büchi reads this, he has it on record that he did well. Hope he'll be back again.

Two hours later, two other Martins took the stage. Martin Paul Nash and Martin Bach introduced RAC Attack for the first time in Norway. I've been excited about RAC Attack since I took the courage to recommend it for the OUGN Board, and after getting the approval, inviting them over. They did not disappoint at all! The turnout was quite good; 20 people filled the room and started to hack on VirtualBox, VMware and Oracle. After lunch a few wearied off but the rest continued to the end. We where the last to leave the conference area and we got plenty of positive feedback on this. I think I've been tweeting enough on #RACATTACK lately, it was a great experience.

Actually OUGN was blessed by a fourth Martin, Martin Widlake. I couldn't go to his presentation, but head counts and feedback showed his presentation went very well. I did have the pleasure to talk to him during the dinner later. The picture of the Martins where taken at Holmenkollen just before the dinner. Martin's smile on his face pretty much sums up the mood after Day 0. Loved it.

OUGN 2012 Day -1

Tomorrow is the big day. For the last 4 years our annual conference has been on a cruise ship from Oslo to Kiel and back. That is two days with presentations, fun and stuff. A big success we are not going to change at least this year. But, for this conference, we sent out too many invitations to Very Important Presenters and got an impressive positive response; we run out of slots pretty fast. (Let's say we are not really #1 when it comes to logistics in this country.) So we decided to extend the conference with one day in Oslo before we board the ship on Thursday. Someone called it a pre-conference, which is unfair to the presenters since they are giving real meat tomorrow. Someone called it a whole-day conference, eh, as opposed to the other days? Anyway. tomorrow we'll have lots of hands-on labs, master classes and what I'm really excited about, RAC Attack.

Tonight we met at the top floor of the conference hotel as some kind of pre-conference beer meetup. I suspect the guys that showed up didn't really know they took part in a meetup, they just went to the nearest bar with the coolest view in the city. As always, it was nice to meet some of the invited guests: Maria Colgan, Christian Shay, Kuassi Mensah, Bryn Llewelyn, and Holger Friedrich, together with the rest and beers.

Some came with a late flight from UK, like the two Martins responsible for RAC Attack. Not sure where Doug is, he is not on tomorrow AFAIK, but he will be very welcome when he shows up. Btw, Maria told me they have the same humour in the presentations. I agree, those two can fool anyone into studying statistics.

More details on the conference here: http://ougn.no/program-2012. Let the party begin.

EXP-00003

There is a bug in the export utility on version 11.2.0.1 when running against a 11.2.0.2 (and possiby 11.2.0.1) database that has tables without segments. Deferred segment creation was introduced as a smart feature in 11gR2, but this is causing error EXP-00003 when running the old export utility from lower versions. This is reported on MOS for pre-11g clients, but right now I could not find any reports that this is also a problem with the 11.2.0.1 version when this feature was introduced.

This is a simple test to reproduce the error. Create a couple of tables in an 11gR2 database:

create table empty (id number, foo varchar2(2000) );
create table aaa (id number, foo varchar2(10));
insert into aaa values (42,'asdfas');
commit;
select table_name,segment_created 
from user_tables ;

TABLE_NAME SEG
------------------------------ ---
EMPTY NO
AAA YES

Then perform a simple export from a 11.2.0.1 client:

exp oyise/oracle@orcl file=oyise.dmp log=oyise.log

Excerpt from the output / logfile:

. about to export OYISE's tables via Conventional Path ...
. . exporting table AAA 1 rows exported
. . exporting table EMPTY
EXP-00003: no storage definition found for segment(0, 0)

(For export to succeed with one table, create it with a name alphabetically before the EMPTY table).

Note, if the X,Y in "no storage definition found for segment(X,Y)" is non-zero, you have another issue that you probably find a solution for on Oracle Support, e.g. when segment owner <> table owner.

Solution: Upgrade client or allocate one extent for the table that fails:

alter table empty allocate extent;

You may consider set the parameter DEFERRED_SEGMENT_CREATION to FALSE in case more empty tables will be created.

Also this error does not happen on Data Pump export 11.2.0.1.

Wordfeud and regular expression in SQL

Three family members invited me to play Wordfeud with them this Christmas. At the end of such a game losing badly to my sister-in-law and with only a few letters left I was thinking about how I could find candidate words using regular expression in SQL.

First I found a list of Norwegian words on the net. I should have taken the effort to get hold of the same word list as Wordfeud is using. The list was loaded into a table using SQL Developer.

Say you want to see if there is any word that start with N and uses only the letters C, F, S, and A. The following SQL may find it:

select word
from norwegians
where regexp_substr(word,'^n[cfsa]+$') is not null ;

For those not familiar with regular expressions, well, just Google or check out any good book on Perl. In short, the ^ and $ means the beginning and end of line (word in this case) respectively, [] is used to create a group of letters; [cfsa] means any letter of c, f, s, and a; + means one or more occurrences.

Now I only need to make a mobile interface to this, and create a function that calculate the score for each word based on Wordfeud's rules. Next Christmas, maybe. I do know there are apps ready to suggest this, but that is not half as fun :-)

Two free profilers for 10046 trace files

I'm a big fan of Method R, both the company and the way to optimize SQL (or any other process that can possibly be measured). The method is explained in details in their classic book Optimizing Oracle Performance. But it is hard to practice method R without a good profiler. In every installation of Oracle database the profiler tkprof is included and it does serve for some problems, but hides a lot of information from you.

When I have a tasks that runs for a few hours I enable extended Oracle trace from start to end, avoiding as best as possible anything outside the time interval for the task itself, meaning I coordinate the tracing with the end user. When I have the trace file ready I check it briefly to make sure I don't have some rubbish before or after the time window. Especially on Windows it is important to check that the tracefile has not already been written to by a previous session, may be it is the same on Unix, I haven't checked since I'm in Windows land for the moment. I use two free tools that create a profile in html-format:

Oracle Session Resource Profiler (OraSRP) by Egor Starostin and TVD$XTAT by Christian Antognini, the author of the book Troubleshooting Oracle Performance. In that book he describes his profiler that you can download from his site. Excellent book by the way.

I often use both profilers because I like to compare the results. They usually agree surprisingly well, the only difference is what is considered unaccounted for time. Whenever unaccounted for time is large I check the tracefile again to see if there is a long period of time spent waiting for the client at the end. Sometimes the missing time is located inside the session, and at one occasion when I was stuck I bought a license of mrls that parse the tracefile and makes it easy to track down where this happens.

In addition to create an overall profile for your session they have profiles for individual events, OraSRP is especially good at this. This is interesting because it adds another level. One of the tricks you encounter on the web is to increase the parameter DB_FILE_MULTIBLOCK_READ_COUNT. This parameter decides how much Oracle can try to fetch in each call when reading many blocks at a time, typically db file scattered read or direct path read usually when doing table scans as opposed to single key search in an index. Reading 128 at once must be better than 16 that is often the default, but this profile will in some cases show you that Oracle cannot read a maximum number of blocks for each read call. In one query I was working on the event db file scattered read contributed to 51% of the response time, but a profile on this event showed that the number of blocks pr read call was almost flat with a variation from 1 block pr call (7.1%), 128 blocks pr call (1.0%) to 73 blocks pr call (0.1%). The benefit of increasing the parameter was not as much as I hoped for, and the solution was found elsewhere.

One of the important points with profiling is to discover where not to waste time. You might suspect a full table scan to be the problem (because some people believe FTS is always bad), but large tables can be scanned really fast these days. With tracing and profiling you don't have to guess, you measure and draw a conclusion. There are so many things that can go wrong in a complex system, if you can measure and do simple math you are much more likely to reach your goal early and know that you actually have reached it than when you apply all those tricks and guesswork.

"Filter early" is important when optimizing, with profiling you filter out early where you don't want to apply the focus of your brain.

OOW 2011 Day 5

OOW has been great and I'm reaching the limit of how much information I can consume for a while. I'll probably spend some weeks or months reading and understanding presentations. Time to go home and practice what I've learned. I'm not even going to try to report dutifully on every session today. Just say that I've been to Real-World Performance Questions and Answers, Oracle Database Optimizer: Tips for Preventing Suboptimal Execution Plans, Looking Under the Hood at Oracle Clusterware 11g Release 2, and Under the Hood of Oracle Automatic Storage Management: Fault Tolerance. The first one was great fun, 3 of 4 had more than 20 years experience in this field. The youngest "only" 8. Greg Rahn revealed they have a "secret": they try to solve complex problems with simple solutions. Graham Woods advised people to ask Why? and understand what you are doing before you actually change anything in production. Simple guidelines, but since many are not following them it needs to be repeated.

I'm not going to say much about the two following, again useful content and I have to look again at the mysteries of Clusterware. Also Maria Colgan showed as she did yesterday she is an excellent speaker. She is invited to next year's OUGN conference, which according to Bryn is the world's best user group conference. Looking forward to it.

Back at home I've had many discussions regarding ASM, it is surprising what people can get religious about. One former coworker stated that Oracle has no right to implement a volume manager... I've never had any good arguments for migrating to ASM. If the DBAs at the site like it, I chime in with things I like, simplicity to name one. If not, because "any datafile should be verifiable with ls or dir", then I usually back off from any discussion. In the last presentation of the conference by Alex Gorbatchev from Pythian I learned quite a few reasons to go for ASM, like you have more integrity checks and efficiency if you use ASM. I'll probably stay away from heated discussion until I've read everyting from James Morle, baarf.org, ASM-gurus like Alex; SAN- and Unix-admins don't give up easily.

This was first time at OOW. Was it worth it ? Absolutely. I got served more than I could eat, met a lot of smart people whose blog posts and tweets I've been reading for years; i.e. great networking opportunity, and had great fun. Learned a lot, now I know what other SSF-travelers are talking about.

Thanks to everybody who stopped for a talk or sent a smile in my general direction. Going home looking forward to see wife & jr.