Good Stuff

Oracle Open World! I’ll be there, and so will lots of other cool people. Don’t miss the blogger meetup where we’ll all hang out 🙂 Don’t miss the unconference. The line-up is better than what I see at most events – Greg Rahn, Cary Millsap, Kelvin Closson, Rob van Wijk, Alex Gorbatchev, Richard Foote and I will all be there.

Dr. Neil Gunther! One of the top most performance specialists. His blog is not easy to read, and is not strictly Oracle related, but I’m always glad I take the time to read it because I learn so much. Its also quite entertaining (for load testing nerds). For example: “Without knowing any details, I can see is that the test rig was driven into saturation, starting with the first concurrent request! Therefore, the first data points provide all the comparison information. The other measurements are redundant (log axis or no). So, what’s the point of the plot?”. Oh, and he also has good twittings!

Exadata v2! A DB server so fast the only way to describe it is ridiculous! There’s still not a lot of technical information out there about it, but the FAQ is a good start.

Advanced Oracle Troubleshooting Seminar at NoCoug Unbelievable, but two month before the event 50% of the sits are already taken. If you are interested, you should probably hurry up. Early bird registration ends in a week. Don’t say I didn’t warn you.

Shell tricks! Don’t know about you, but I still do my scripting with BASH. Jared Still posted some useful tricks.

Please post more cool stuff in the comments. Also suggestions for books I should read on my daily 3 hour train commute to OpenWorld will be nice.


Few Links of the Week and a Biased Recommendation

Because Pythian’s Log Buffer is missing due to unexpected appendix, and I can’t leave my readers with nothing to do all weekend.

Jonathan Lewis suggests browsing through V$SQL and provides a nice script.

OracleNerd explains how to shop for cars using SQL. I thought I’m nerdish for planning my purchases in Excel, but SQL is by far a nerdier method.

Marco Gralike gives a very cute HTTPURI example.

Tanel Poder shows the memory overhead of generating rows with “connect by” and shows a short and sweet solution.

Laurent Schneider’s Advanced Oracle SQL Programming book is now available for sale on Amazon. I’ve been lucky to get the chance to review the book. It covers some of the most advanced and exciting aspects of SQL programming, and it is full of useful, practical examples. Almost everyone who uses Oracle SQL regularly in his work can benefit from the information and ideas in this book. I know my programming skills improved significantly from reviewing it (and even my co-workers noticed!).

Not many posts this week. Maybe it is related to Euro2008?

Notes about OCP

Hey, I passed my OCP exam today. Now I just need to take care of the hands-on course requirement and you’ll be reading the blog of an Oracle Certified Professional. It is more interesting already, right?

I already explained my study method when I took the OCA. This time I followed a similar plan, with three main changes:

  1. I did not hike up Mount Shasta the weekend before the exam.
  2. When I studied for the OCA I already knew all the material before I began studying. Simply because I spent years installing and maintaining Oracle. For the OCP, most of the material was new. I was completely unfamiliar with RMAN, MMON alerts, flashback database, recyclebin, IOT, clusters and resource manager. This meant that I had to do a lot more practice in order to feel comfortable with all those tools. I spent an entire weekend backing up and restoring with RMAN, and it was time well spent.
  3. I followed good advice from my favorite OCM and studied quite a bit from Oracle’s official documentation. Mostly the backup and recovery guides. I found them way more accurate and more readable than the exam guides, and as an added bonus – you can send to Oracle any problems you find in the doc. I enjoy complaining about mistakes so much that the study process was actually fun.

Which brings me to a very important point – when you take mock exams from any of the study guides, verify each and every one of their answers with the oracle docs or with a live system. I found about 15% mistake rate in their answers, and you really don’t want to memorize the wrong things.

Its unbelievable, but I can honestly say that I’m a much better DBA now that I’ve studied for the OCP. I’m much more comfortable with most backup and recovery scenarios (Few years ago I caused unnecessary data loss while recovering a corrupt data file, simply because I did not know not to restore redo logs!) and I know how to use new important features that I didn’t know about before (or at least didn’t look into seriously).

So, my two cents for value of certification for employers debate: DBA with four years and experience and an OCP is probably better than four years of experience and no certification. I know I am.

What’s next for me? My team lead wants me to go for OCM. I’m not sure I’m quite there yet. I’m thinking of 11g upgrade and RAC Expert certificate. But before all that, I promised myself a white-paper and a presentation this year.


Lots of good posts this week:

Tanel Poder wrote an incredible V$ sampling query. Its simply brilliant. He also showed how to use his Snapper tool to find who is generating excessive redo and saved me a long session with logminer last weekend.

Steven Feuerstein shows how to run OS commands from PL/SQL.

Happy birthday to Syed Jaffar Hussain!

Alejandro Vargas posted a good RMAN practice.

Rob Van-Wijk wrote nice string aggregator using model. (Because Oracle SQL programmers do it with models!)

Cumulative Distribution

Laurent Schneider sent me a link to an interesting discussion in AskTom.

How do we calculate Cumulative Normal Distribution? This means calculating the probability that a random variable from a normal distribution, will end up equal to or less than X.

I’d love to know why the OP needed that. While Normal Distribution is very popular, most realistic statistical applications would use Student’s T distribution. This is so common that Oracle contains a built in T Test function. Maybe this would be useful to the OP?

Regardless of Tom’s flippant reply, it is actually a difficult question. Knowing the cumulative normal distribution means integrating the normal (Gaussian) function. Which is impossible. So in a way, the correct answer to the OP’s question is “you can’t”.

But thats not correct either, while there is no “general” function for the integral of the normal distribution, you can manually calculate the integral for any specific value on the function. Lucky for us, Gauss had grad students who did this hard work, and until very recently statisticians referred to books with tables containing these results whenever they needed to know the probabilities.

Which brings us to another good answer – find a website with this table, load the table into Oracle, and use it as much as you want. No PL/SQL needed.

Another interesting way to calculate the cumulative normal distribution in Oracle would be to use Oracle’s dbms_random.normal to generate an approximation of the normal distribution and calculate the cumulative distribution on the result. Of course it is not very accurate, but it is a lot of fun:

select n,cume_dist ( ) over (order by n) cume_dist from
(select dbms_random.normal n from dual connect by level<=100);

In the AskTom thread, Georg from Germany linked to a function that approximates the cumulative normal distribution, and that site contained link to a paper with even better approximation. This is probably how statistics software (SPSS and its friends) calculates probabilities.

Interesting question, many good answers.


Frits Hoogland also managed to write a long post about a short question with a short answer:
How an ASM diskgroup is found by the database.

Links of the Week – #4

Friday morning is not complete without going over the latest Log Buffer and choosing few good articles to read over the weekend. Problem is that the last two Log Buffers are too MySQL oriented for me, and missing terrific posts from Oracle blogs.

As a service to my readers, here are the missing posts:

Rob Van-Wijk demonstrates the non-intuitive (but well documented) behavior of date format element ‘ww’.

Don Seiler found a bug with v$sql_bind_capture.

Tom Kyte doesn’t like a common exception handling technique.

Michael Dinh shows how to drop database (don’t try this on production!)

Christian Bilien has an excellent post about throughput.

Jared Still detects numbers with Translate().

Not exactly Oracle, but Steven Feuerstein wrote a fascinating post about Singapore.

Enjoy the weekend!

Log Buffer #80 – A Carnival of the Vanities for DBAs

Couple of weeks ago, when I asked Dave Edwards of the Pythian Group to let me edit Log Buffer edition #80, I just wanted to reserve an edition with a nice round number. As it turned out, I got to write Log Buffer for an incredibly exciting week.

Breaking news: Sun bought MySQL (and everyone is blogging about it).
Groklaw site posts The Details on the Deal, including the announcement, responses of the primary players and big media coverage. Brian “Krow” Aker posts very quick thoughts, Kaj Arno talked to MySQL founders, and Matt Asay on CNet blogs posted a good analysis on what it all means for the software industry. Reading the posts you can see that everyone is celebrating this incredible change for the open source database and the largest ever open source deal. Well, maybe except for Sheeri Cabral who is a bit worried. The nice thing about blogs is that you can read many opinions and pick your side – Paul Vallee is not worried and thinks it is great. Between Sheeri and Paul, Pythian Blogs provided lots of coverage for this event and if you want to read (and hear!) more about this topic, head over to the MySQL category of Pythian blogs.

The other big news of the week is that Oracle bought BEA, a bit less exciting because BEA is neither a database nor open source. Eye on Oracle provided good coverage and asks for your opinions, while Lucas Jellema of Amis Technology Blog posted several different opinions about the deal.

Oracle released another Critical Patch Update. Matt Asay of Cnet Blogs is shocked to hear that 66% of Oracle users will not install it. Pete Finnigan is not shocked at all but thinks it is time to get over our collective fear of patches. Jaykumar Vijayan on Computer World blog posts reader opinions who collectively say that when CPUs are not applied it is not the DBA fault. For those who are wondering, I will be installing Jan08 CPU on all our production servers within this quarter.

On other Oracle news: Laurent Schneider made a typo and discovered that in SQL white space can be optional, Tanel Poder thinks that this flexibility can be viewed as a feature, but Red Database Security thinks this can be security threat.

Jonathan Lewis posted great tips for troubleshooting and Richard Foote explains reverse indexes and then explains them some more.

In SQL Server blogs Decipher Information Systems posted a simple script for capturing DB size and showed how data can be moved in and out of partitions. In SQL Team blogs, Joe Webb posts instructions on how to reclaim unused space in a table, and Dan Guzman explains the significance of database owner.

More news about Open Source Databses: MySQL DBA posts about problems with version 5.0.51, and warns not to use O_DIRECT with EXT3, Xaprb explains how to speed up MySQL replication slaves and Mark Schoonover posts about a linked-in group for MySQL certified professionals.
In Database Soup, Josh Berckus tells PostgreSQL DBAs to stay updated with the current security patches. Postgres fans may also want to read David Fetter‘s blog, he posts a weekly summary of Postgres News.

Thats it for Log Buffer #80. Plenty of links this week. Remember that you don’t have to read them all at once – you have the entire weekend for catching up on the news.

Back to work links

Today was a very busy day at Oracle blogs – everyone is back from vacation with new ideas for posts and I found myself bookmarking six excellent posts today. I’ve never read so much good stuff in a single day before. Of course I have to share them:

Tanel Poder explains why parameter count can change in mid session.

Crazy DBA wants his users to check the facts.

Steven Feuerstein uses few colorful characters to demonstrate a PL/SQL best practice.

Frits Hoogland found out that you can’t mix VMWare and OProfile. Maybe he should use Xen, instead?

Babette Turner-Underwood found a great 11g Data Pump feature.

Structured Data post about a terrific SQL tuning monitor in 11g.

Richard Foote tells about an important change in the way statistics are collected by default in 10g. I now have to review statistics for some key tables and make sure they make sense.

All this in a single day! Thats too good to be true!

Links of the Week – #3

Once again, here’s what I enjoyed reading this week. Not all of it is database related:

Jeremy Schneider diagnosed busy waits in a RAC environment. This article and its follow up are probably the most educational articles I’ve read this week.

On oracle-l mailing list, Tanel Poder explains how to set the window title for SQL Plus client running in windows.

Don Seiler had lots of fun with CBO, and lived to tell the tale.

Jeff Hunter was very unhappy with Oracle support. Getting Oracle to backport fixes is indeed difficult.

Dominic Delmolino introduced me to V$OSSTAT and explained how he finds out about new features.

Marc Andersen found a good quote in a book about the panic of 1907.

Gretchen Rubin wants everyone to start a happiness project. Maybe I’ll give her ideas a try.

It was fashion week in NY this week, get updated on what to wear this season. I can’t wait to see how many readers will click on this link.

Enjoy the weekend.

Links of the week – #2

Here’s what I enjoyed reading this week. Warning – not everything here is related to databases.

Kevin Closson wrote an article that really left me with my jaw dropped. He wrote about good IO throughput on NFS systems. The article is excellent, as usual, but the part that really knocked me over was the fact that he wrote his own dd, because he was not satisfied with the throughput he measured with oracle’s dd. Isn’t this guy unbelievable?
That’s why I was happy to read that he sometimes botches RAC installations too.

Tanel Poder wrote about running remote scripts from sqlplus through http or ftp. Definitely something that can be helpful in production, especially someone else’s production.

H.Tonguç Yılmaz wrote best practices for upgrades. He has very good advice there, so it is definitely recommended reading.

On oracle-l list, Anurag Verma asked a good question about block recovery and got some good answers.

My favorite SQL Server blogger wrote a good parable about data types. I’m still troubled by the fact that he ran into so many DBAs that misuse data types so badly.

Paul Graham wrote more advice for startups, and Marc Andersen has a tip too. The Corporate Cynic describes one of the most annoying things you can hear from a manager or a co-worker.

In BMJ, a medical ethics journal, I’ve read an article about Hyperactivity in children: the Gillberg affair. The article describes an academic disagreement between Swedish psychiatric researchers that quickly escalated into law suits. If you think that some Oracle consultants are fighting dirty, you should read what those Swedish scientists do.

I think I’ve got addicted to writing blog summaries, and I have to admit that its more enjoyable writing them when I don’t have to look for Postgres articles in the name of database objectivity.

Botched installations and funny new features
Tips for upgrading and NFS measures
Blocks that recover and startups that jinx
These are a few of my favorite links

Log Buffer #59: a Carnival of the Vanities for DBAs

As I’ve hinted at the beginning of the week, this week I’m writing up edition #59 of Log Buffer. Dave Edwards of Pythian Group contacted me couple of weeks ago and offered this opportunity. I hesitated quite a bit, this is a big responsibility, and I was not sure my rather new blog is ready for such exposure. I’m glad I agreed. Dave Edwards sent me a bunch of helpful links to other interesting blogs, so I got to discover many valuable blogs I’ve managed to miss so far, and to read about databases that I usually don’t see much. Despite the fact that the links Dave sent included many databases besides Oracle, this Log Buffer will probably be a bit biased toward the worlds most popular DB.

Oracle 11g is still the hottest topic in Oracle blogsphere. Tanel Poder started an 11g internals series with an article about automatic memory management, getting into the implementation in Linux. Kevin Closson continued looking into this topic with a typically well researched article.

Blogging About Oracle also has a series about Oracle 11g, written by different authors. I particularly liked Part 7 – Function Result Cache, written by Roel. He gives a good example that demonstrates the value of this feature.
Virag Sharma, in Database, RAC, Dataguard and Apps writes about SQL Performance Analyzer, one of the hottest features in 11g, and Oracle Brains explain how to tune using 11g’s invisible indexes.

If you are still not tired of 11g, Eddie Awad posted 40+ links to 11g articles and blog posts. This should keep everyone busy during the weekend.

Oracle Open World is three month away, but Dan Norris and Shay Shmeltzer are already getting ready for the event. I also need to start looking for interesting sessions. there are always so many to choose from.

And in other news: Tanel Poder published a seriously cool script for tracking session level performance stats that work even when you are not allowed to do anything on the database.
Alex Niujten, on the highly useful Amis Technology blog explained how to create table as select when you have nested tables.
In Eye on Oracle, Mike Brunelli is collecting information about the quality of Oracle support. I hope his project will generate many responses and maybe it will even cause Oracle to rethink their support organization.

Daniel Fink, the Optimal DBA has fun with SQL and SQL*Plus while dynamically assigning column names. Don Seiler at Die Seilerwerks writes how he used trace 10053 to determine how CBO does its job, and Jakub Pawlowski points to training material about PL/SQL that Steven Feuerstein published in his site.

SQL Server fans continue to blog about SQLServer 2008, Bob Beauchemin writes about SQL Server’s support for extended events. Jeff Smith writes about composite primary keys. I truly hope that everyone already knows about them, but if you don’t – its a must read, and Mladen Prajdić , at I want some Moore, explains how to modify data in a result set that uses joins. I wish he had written this article few month back when I needed it.

Meanwhile, on the OpenSource web:
Ronald, at Technical Notes blog posts a bunch of links for Oracle DBAs learning MySQL and also advice regarding backup and recovery. Last week I received a task to take ownership of MySQL server and write a recovery procedure for it, I can testify that both articles are very useful.
Charlie Cahoon ends in his Summer of Code blog with a release of his MySQL proxy. Corra is already using MySQL Proxy on Ubuntu.
Morgan Tocker and Brian “Krow” Aker try to decide how big transactions should be, and last but not least </depesz> explains how to secure PostgreSQL.

Whew, that has been one long post. I didn’t realize how much interesting things are being written by DBAs every day. There were so many great articles that choosing and picking them for this post was much more difficult than I expected. I highly recommend every database blogger to try writing Log Buffer once or twice, its a unique experience.