December 2014

UKOUG TECH14 SQL Quiz

If you are an attendee of UKOUG Tech14 you have a chance to win a free copy of Expert Oracle SQL by Tony Hasler AND a free copy of Troubleshooting Oracle Performance (2nd edition) by myself.

If you want to know more, click here.

Expert Oracle SQL

Troubleshooting Oracle Performance, 2nd Edition

UKOUG Tech14 Suggestions for Intro Talks and My Picks of the Rest

As I mentioned in my last post, we tried to organise a thread of intro talks into day one and two of this year’s UKOUG Tech14 conference (you can see the agenda grid here). It was not complete but I thought I should pull it into it’s own post and add in what I would recommend from the overall agenda for people who are relatively new to Oracle RDBMS.

Monday 8th

Updated (XML) Content Section

“Once per year I try to update the “XML Content” page that, in principle, should…

STATISTICS COLLECTOR Row Source Operation and LOBs

The aim of the STATISTICS COLLECTOR row source operation, which is used in adaptive plans, is to buffer all data produced by its child operation until it is known whether the inflection point is crossed. It goes without saying that buffering requires memory and, therefore, Oracle Database has to limit the amount of memory that can be allocated for that purpose. As a result, in some situations no adaptive plans can be used because according to the query optimizer estimations too much memory is required.

A special case of the previous limitation is when the row source operation under the STATISTICS COLLECTOR operation produces a LOB. In fact, when a LOB is detected, independently of the amount of data that is expected to be produced, the adaptive plan is bypassed by the query optimizer.

Let’s have a look to an example:

Upgrades

I have a simple script that creates two identical tables , collects stats (with no histograms) on the pair of them, then executes a join. Here’s the SQL to create the first table:

UKOUG 2014

So it’s that time of year when I have to decide on my timetable for the UKOUG annual conference. Of course, I never manage to stick to it, but in principle here are the sessions I’ve highlighted:

How do you Explain Oracle in 50 Minutes?

I’ve done a very “brave”* thing. I’ve put forward a talk to this year’s UKOUG Tech14 conference titled “How Oracle Works – in under 50 minutes”. Yes, I really was suggesting I could explain to people how the core of Oracle functions in that time. Not only that, but the talk is aimed at those new to Oracle technology. And it got accepted, so I have to present it. I can’t complain about that too much, I was on the paper selection committee…

* – “brave”, of course, means “stupid” in this context.

As a result I am now strapped to the chair in front of my desk, preparing an attempt to explain the overall structure of an Oracle instance, how data moves in out of storage, how ACID works and a few other things. Writing this blog is just avoidance behaviour on my part as I delay going back to it.

Little things worth knowing: creating the Swingbench order entry schema “lights out”

This is a little note, primarily to myself I guess, about the creation of the order entry schema (part of Swingbench, written by Dominic Giles) when no VNC sessions are available (although you can almost always use port-forwarding :). Instead, you can create the schema on the command line. I always execute commands on remote systems in screen for increased peace of mind. Should the network drop, the order entry generation will continue as if nothing ever happened.

Like many others I use Swingbench during trainings and presentations to have some activity on a system. Very useful for demonstrating ASH and OEM, and many other things too!

Syntax Highlighting is broken in all my SCN blogs

Hey SCN guys,

the SQL syntax highlighting is broken in all my SCN blogs. Here are just some example: