June 2016

Never …

From time to time a question comes up on OTN that results in someone responding with the mantra: “Never do in PL/SQL that which can be done in plain  SQL”. It’s a theme I’ve mentioned a couple of times before on this blog, most recently with regard to Bryn Llewellyn’s presentation on transforming one table into another and Stew Ashton’s use of Analytic functions to solve a problem that I got stuck with.

Here’s a different question that challenges that mantra. What’s the obvious reason why someone might decide to produce the following code rather than writing a simple “insert into t1 select * from t2;”:

Granular detail from a summary

We had an interesting question on AskTom a few days ago.  Given a set of 12 values (forecasts in this case), one for each month of the year , can we manufacture a set of weekly forecasts for the same period.  Now it is perhaps a little dubious to “invent” detailed data out of summarised data, but we can come up with a reasonable algorithm for doing so.

We could simply divide each month by 4 because there are ~4 weeks per month, but we still face the challenge of mapping 48 values into 52/53 weeks of the year.

We could also sum all the months, and carve it into 52 weekly chunks, but in doing so, we lose the peaks and troughs that a monthly forecast is meant to represent.

So can we do better ?

“ASH math” of time_waited explained with pictures and simulation

As explained by John Beresniewicz, Graham Wood and Uri Shaft in their excellent overview ASH architecture and advanced usage, avg( v\$active_session_history.time_waited ) is not a correct estimate of the average latency (the "true average") esperienced by a wait event, the reason being that short events are less likely to be sampled. In order to correct this, the authors propose a formula that gives an unbiased estimate of the "true average".

Cross Joins

Cross joins give all combinations of rows from two tables. One use for them
lies in generating large amounts of data for performance testing or similar
purposes.

Read the full post at www.gennick.com/database.

Cross Joins

Sixth in a series of posts in response to Tim Ford's #EntryLevel Challenge.

Cross joins give all combinations of rows from two tables. They aren't normally useful, but they can be so in the right circumstances. One use for cross joins lies in generating large amounts of data for performance testing or similar purposes.

Cross Joins

Sixth in a series of posts in response to Tim Ford's #EntryLevel Challenge.

Cross joins give all combinations of rows from two tables. They aren't normally useful, but they can be so in the right circumstances. One use for cross joins lies in generating large amounts of data for performance testing or similar purposes.

Conditional SQL- 6

An odd little anomaly showed up on the OTN database forum a few days ago where a query involving a table covered by Oracle Label Security (OLS) seemed to wrap itself into a non-mergeable view when written using traditional Oracle SQL, but allowed for view-merging when accessed through ANSI standard SQL. I don’t know why there’s a difference but it did prompt a thought about non-mergeable views and what I’ve previously called “conditional SQL” – namely SQL which holds a predicate that should have been tested in the client code and not passed to the database engine.

The thought was this – could the database engine decide to do a lot of redundant work if you stuck a silly predicate inside a non-mergeable view: the answer turns out to be yes. Here’s a demonstration I’ve run on 11g and 12c:

My New Book!

After what seems like eons since we first started on it, I’m excited to announce a new book I co-authored is finally out. The book is called “Building Database Clouds in Oracle 12c” and is available on Amazon. Of course, it really isn’t that long ago that we started writing the book, but there’s been a lot happening between then and now!

The book was co-authored with Tariq Farooq and Sridhar Avantsa. Tariq asked me to write the material on Enterprise Manager (chapters 4 – 8 in the book), which was done over a number of releases of EM12c. We deliberately left the material on the versions it was written on, because people are still on a variety of different releases, so you can see how it applies in the version you’re using.

From a content perspective, this is what the book covers:

When CURSOR_SHARING=FORCE, Does Literal Replacement Always Take Place?

The concept of cursor sharing is simple. If an application executes SQL statements containing literals and if cursor sharing is enabled (i.e. CURSOR_SHARING=FORCE), the database engine automatically replaces the literals with bind variables. Thanks to these replacements, hard parses might be turned into soft parses for the SQL statements that differ only in the literals.

The question raised by the title of this post is: in case cursor sharing is enabled, does literal replacement always take place?