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.

OOW 2011 Day 4 and Blogger's meetup

Day four started well with Oracle Optimizer: Best Practices for Managing Optimizer Statistics presented by Maria Colgan. Then followed SQL Tuning Expert Roundtable with Jagan Athreya, Benoit Dageville, Tom Kyte and Graham Wood. These two presentations had a lot in common, correct statistics should be checked if plan is wrong, note correct, but not necessarily updated statistics. If an automatic routine for stats gathering cannot be found the metadata for the table and columns should be set manually and locked. More people talk about skews this day, it was mentioned here also, but I guess James Morle and Cary has more to say on that. The problem with histograms, bind variables and bind peeking is somewhat easier in 11g with adaptive cursor sharing, but not a 100% solution according to the panel. One 11g feature I'm going to look more into is extended statistics.

The third and last presentation for me was Powerful New Ways to Use Oracle Data Guard for Planned Maintenance with Larry Carpenter. This stuff requires a lot of consideration and planning. Downtime, complexity and risk. Finding a solution that lowers especially the last two is important. Another presentation I have to digest.

At 17:00 I went to Pythian's bloggers meetup. Met a lot of guys IRL that I've been following on Twitter and whose blogs I've been reading for years. Impressive event and very social. Pythian just ranked even higher.  Tim Hall won the prize for having collected most signatures, my collection in the picture. I was too busy talking to people, but got a fair share of signatures. Loved it all.

OOW 2011 Day 3

Went to five presentations, two of them stood out. Doug Burn spoke about SQL plan management in 11g and I learned some new tricks. The material and the content was quite good, also I like these comments that may not be in the slides, e.g. he quoted Jonathan Lewis who said that if you have a narrow problem you should apply a narrow solution, an advice against changing a system wide parameter just because you have one SQL statement gone astray.

The other presentation I liked a lot was about mining the AWR data with SQL, by Yury Velikanov from Pythian. The presentation was packed with useful examples and based on experience he had with customers. When he started almost all seats were taken, and then some people left, maybe it was over their head, but for me, I like these kind of presentations. Yes, it is cool with fancy colorful slides where rule number one is max six words pr slide, but I don't mind seeing code in the slides once in a while. I think that if you strive to understand the AWR repository and analyze it the way Yury did you will have a better understanding on how things are connected, in a way you cannot have when looking on nice graphs in EM. Also you see the limitations in AWR, most data are aggregated which creates other limitations; see Cary's presentation about skew. He made a point in the beginning that there are different concepts to use when attacking optimization and troubleshooting, he gave pointers to tools and methods. All in all a presentation that I have to download and study for a while.

Another great day.