The Hitchhiker’s Guide to the EXPLAIN PLAN Part 40: Why is it so hard to get SQL performance right the first time?

In the August 2015 issue of the NoCOUG Journal, we asked Stéphane Faroult why it is so hard to get SQL performance right the first time. His answer implies that SQL itself and the way it is taught are the problems.(read more)

SQL and the Art of Problem Solving

What would you do if you were lost in a labyrinth of underground caves? In the dark. With no food. Mark Twain tells the story of how Tom Sawyer and Becky Thatcher got separated from their picnic group and got lost in a labyrinth of underground caves. What’s that got to do with problem solving? I think that the first rule of problem solving is “If at first you don’t succeed, try, try, again.” (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 39: Unfriending the optimizer statistics

We can’t help attaching meaning to optimizer statistics. After all, they represent our data, don’t they? So we refresh them as frequently as possible and use the largest sample sizes that we can use. Recently, for the first time in my life, I encountered a group of DBAs who understood that statistics do not have any intrinsic meaning; that they are nothing more than a collection of numbers that influence the generation of query plans. (more...)

OT: Some advice for beginning Oracle Database professionals

And if you don’t know Which to Do Of all the things in front of you, Then what you’ll have when you are through Is just a mess without a clue … —Winnie the Pooh in The Tao of Pooh, by Benjamin Hoff Excerpts from the first chapter of my just-released book Beginning Oracle Database 12 c Administration : Thank you very much for buying this book (or for getting a legal copy). Database administrators (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 38: Utilizing Shakespearean wisdom in database upgrades

“ Those friends thou hast, and their adoption tried, Grapple them unto thy soul with hoops of steel; But do not dull thy palm with entertainment Of each new-hatch'd, unfledg'd comrade.” —the advice of Polonius, counselor to King Claudius, to his son Laertes who is leaving for France in Act 1, Scene 3 of The Tragedy of Hamlet, Prince of Denmark by William Shakespeare Every new version of Oracle Database comes—this should be no (more...)

OT: How to interview an Oracle DBA candidate (NOT)

Yesterday T.J. wrote on the Oracle-L mailing list : “ For those of you who have conducted job interviews, what sort of questions have you found to be effective in evaluating a candidate’s skill level?” I remember the interview at which I could not answer the question “how do you enable block change tracking in a database?” (The answer is ALTER DATABASE ENABLE BLOCK CHANGE TRACKING.) I no longer want to (more...)

OT: Update on the Fourth International NoCOUG SQL Challenge (Part III)

Update on the Fourth International NoCOUG SQL Challenge in progress at . Chris Goerg submitted the following MODEL-clause solution. The use of two measures is the key. with d as ( select unique extract(month from dateofbirth) m , extract(day from dateofbirth) d from dates ) select m, d from ( select * from d model dimension by (m,d) measures (0 cd, 0 cm) rules ( -- mark the unique...(read more)

OT: Update on the Fourth International NoCOUG SQL Challenge (Part II)

Update on the Fourth International NoCOUG SQL Challenge in progress at . The NoCOUG challenge does not come with a fixed data set such as the 10 dates in the Cheryl’s Birthday problem. Instead contestants are required to solve the problem for all data sets that fit the story. CREATE TABLE DateOfBirth (DateOfBirth DATE NOT NULL); Perhaps because of familiarity with the Cheryl’s...(read more)

OT: Update on the Fourth International NoCOUG SQL Challenge (Part I)

Update on the Fourth International NoCOUG SQL Challenge in progress at

The previous NoCOUG SQL challenges (First, Second, Third) were about devising clever algorithms and then trying to code them in a language (SQL) that was not designed for implementing algorithms. The fourth challenge is more about translating a functional specification into relational algebra or calculus  so it’s closer to what SQL is all about.

The fourth challenge (more...)

OT: Get your next job through Twitter?

From Gwen Shapira’s interview in the May 2015 issue of the NoCOUG Journal : “I got my current job at Cloudera through Twitter. At the time, I was working with a retailer in Japan, as a consultant through Pythian. I was there to help them migrate to Exadata, but their data warehouse system was tightly integrated with Hadoop, and I got to work on this integration too. I got a lot of exposure to Cloudera’s (more...)

Fourth International NoCOUG SQL Challenge (teaser announcement)

The Fourth International NoCOUG SQL Challenge will be published in the May issue of the NoCOUG Journal which will be available at at 9 AM PDT on Monday, May 11 . Here's a sneak preview: How Romeo Won the Heart of Juliet Fourth International NoCOUG SQL Challenge Once upon a time, Romeo, the son of Montague, told his cousin Benvolio that he was in love with Rosaline but she was not returning (more...)

Eventual Consistency is NOT a violation of the relational model (says C. J. Date)

C. J. Date was kind enough to comment on my article in the last issue (February 2015) of the NoCOUG Journal (see The Rise and Fall of the NoSQL Empire ). The full text of his remarks will be published in the next issue (May 2015). Here’s a sneak preview: First, to say that a database (distributed or otherwise) is consistent merely means, formally speaking, that the database conforms to all stated integrity constraints. Now, (more...)

The Rise and Fall of the NoSQL Empire (2007–2013)

The NoSQL camp put performance, scalability, and reliability front and center but lost the opportunity to take the relational model to the next level because—just like the relational camp—it mistakenly believed that normalization dictates physical storage choices, that non-relational APIs are forbidden by the relational model, and that “relational” is synonymous with ACID (Atomicity, Consistency, Isolation, and Durability). The NoSQL camp created a number of innovations: functional segmentation, sharding, replication, eventual consistency, and schemaless design. (more...)

OT: New Oracle Database features spell doom and gloom for NoSQL

Oracle Corporation has released a slew of new features that allow Oracle Database implementations to catch up to, match up to, and leave in the dust NoSQL implementations in the areas of performance, scalability, and reliability (PSR).(read more)

OT: Oracle wants your votes

It used to be that if you had an enhancement suggestion for Oracle Database, you had to file an enhancement request in MOS and pray that the Oracle product management gods would hear your request someday. It is now possible to make your enhancement request in a public forum and let the community vote on your idea.(read more)

OT: Never buy an Oracle book again (if you live in California)

If you are a resident of California, you can become a member of the San Jose Public Library and gain access to Safari Books Online and Books24x7. At last count, there were 445 Oracle titles available through Safari Books Online(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 37: POISED: A problem-solving method

P.O.I.S.E.D. is the acronym I invented for the performance tuning method described by Chris Lawson in The Art and Science of Oracle Performance Tuning; it stands for Problem, Observation, Interpretation, Solution, Execution, and Documentation. The Observation and Interpretation phases feed each other. The Interpretation phase concludes with a determination of root cause. If we are investigating poor performance a single SQL statement, the tool of choice for the observation phase (more...)

OT: A software tool that improves my writing

I would like a little more help with my writing than the Spelling & Grammar check in Microsoft Word can provide so I am checking out a tool called Word Rake.(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 36: The sisters of the mother of all SQL antipatterns

The mother of all SQL “anti-patterns” is the ad-hoc query. Here, the end-user of the application can specify any combination of parameters—for example, the job_id, the dept_id, or a combination of both—and the data is retrieved using a catchall query. This antipattern has two sisters who are almost as evil. These two sisters arise from the requirement to paginate through the records retrieved by a query.(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 35: Robust v/s optimal query execution plans

A subset of all possible execution plans can be described as robust. While such solutions are not always quite optimum, they are almost always close to optimum in real-world queries, and they have desirable characteristics, such as predictability and low likelihood of errors during execution.(read more)