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!


Get every new post delivered to your Inbox.

Join 3,110 other followers