Oracle Scratchpad

November 19, 2014


Filed under: Histograms,humour,Oracle,Statistics — Jonathan Lewis @ 12:47 pm GMT Nov 19,2014

“You can’t compare apples with oranges.”

Oh, yes you can! The answer is 72,731,533,037,581,000,000,000,000,000,000,000.

SQL> create table fruit(v1 varchar2(30));
SQL> insert into fruit values('apples');
SQL> insert into fruit values('oranges');
SQL> commit;
SQL> begin
  2  	     dbms_stats.gather_table_stats(
  3  		     ownname	      => user,
  4  		     tabname	      =>'FRUIT',
  5  		     method_opt       => 'for all columns size 2'
  6  	     );
  7  end;
  8  /
SQL> select
  2  	     endpoint_number,
  3  	     endpoint_value,
  4  	     to_char(endpoint_value,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') hex_value
  5  from
  6  	     user_tab_histograms
  7  where
  8  	     table_name = 'FRUIT'
  9  order by
 10  	     endpoint_number
 11  ;

ENDPOINT_NUMBER                                   ENDPOINT_VALUE HEX_VALUE
--------------- ------------------------------------------------ -------------------------------
              1  505,933,332,254,715,000,000,000,000,000,000,000  6170706c65731ad171a7dca6e00000
              2  578,664,865,292,296,000,000,000,000,000,000,000  6f72616e67658acc6c9dcaf5000000
SQL> select
  2  	     max(endpoint_value) - min(endpoint_value) diff
  3  from
  4  	     user_tab_histograms
  5  where
  6  	     table_name = 'FRUIT'
  7  ;

SQL> spool off

September 7, 2012


Filed under: humour — Jonathan Lewis @ 12:32 pm GMT Sep 7,2012

Sometimes I wish …

July 10, 2012

Random Plans

Filed under: CBO,humour,Oracle — Jonathan Lewis @ 5:32 pm GMT Jul 10,2012

Have you ever felt that the optimizer was persecuting you by picking plans at random ? Perhaps you’re not paranoid, perhaps it’s part of Oracle Corp’s. master plan to take over the world. If you look closely at the list of hidden parameters you’ll find that some details of this cunning plan have leaked. In Oracle created a new parameter _optimizer_random_plan with the description “optimizer seed value for random plans”. Who knows what terrible effects we may see when the default value of this parameter changes.

August 5, 2011


Filed under: humour — Jonathan Lewis @ 5:55 pm GMT Aug 5,2011

Statistics don’t lie: a survey of browser users (original report no longer available, but see CNN’s report)

But maybe:  the people reporting them do

On 2nd August the BBC were reporting the survey described at the top as news – and I had a URL for their report, and their follow-up report on 3rd of how it was a hoax. Both URLs now point to the same thing – rather reducing the impact of the posting.

June 28, 2011


Filed under: humour — Jonathan Lewis @ 5:50 pm GMT Jun 28,2011

There are those who understand the importance of evidence, and those who rely on other kinds of proof.

(You might want to turn your speakers down, or off, before following the link.)

June 9, 2011

Queueing Theory.

Filed under: humour — Jonathan Lewis @ 5:26 pm GMT Jun 9,2011

So simple that even an eight year old can understand it.

Here’s a link I was sent byDominic Delmolino (another Oak Table member) a few days ago: it’s not a bad model of how things can go in an Oracle database.

For further reading, try Cary Millsap’s book – especially chapter 9.

March 4, 2011

Annie Hall

Filed under: humour — Jonathan Lewis @ 6:48 pm GMT Mar 4,2011

From time to time I am reminded by postings on the OTN database forum of a sequence  from the move Annie Hall which shows Diane Keaton and Woody Allen being interviewed (separately) by their therapists:

    DK’s Therapist: “Do you have sex often? “
    Diane Keaton:  “Constantly, I’d say three times a week”

    WA’s Therapist: “How often do you sleep together?”
    Woody Allen:   “Hardly ever, maybe three times a week”

This dislocation appears in a similar fashion with Oracle – though the dialogue goes something like this:

    User: “This query takes forever to run.”
    DBA: “Can you give me a rough idea of how long ?”
    User: “Nearly 10 seconds”.

Advice to DBAs – remember to ask for facts, not opinions.

February 22, 2011


Filed under: humour — Jonathan Lewis @ 1:09 pm GMT Feb 22,2011

I’ve just had an email ending with the first “smiley” that made me smile. How often have you felt that you’re flogging a dead horse ?

For a derivation of the expression see Wikipedia.

February 14, 2011

Burleson buys BMC ?

Filed under: humour — Jonathan Lewis @ 8:40 am GMT Feb 14,2011

There have been rumours running through the Oracle community over the last couple of days following publication of a note containing a clue that Burleson Consulting may have acquired the rights to BMC’s performance monitoring tool “Patrol” – and may even have bought out BMC itself. These rumours started shortly after the disappearance of a blog item by Charles Hooper discussing an SQL statement executed by the product formerly (perhaps still) known as BMC Patrol.

When questioned about the disappearance of the blog item Mr. Hooper explained that it had been taken down by his service provider in accordance with a DMCA takedown notice issued by Burleson Consulting and signed by Don Burleson who had quoted the SQL statement in question and stated that: “Under penalty of perjury, I swear that … I am the copyright owner of this material”.


The purchase of BMC Patrol by Burleson Consulting has not yet been officially announced by either party but the copyright claim contained in the DMCA notice would appear to indicate that Burleson Consulting has acquired exclusive copyright by purchasing (at a minimum) the rights to the product.

In other news

Latest gossip suggests that database giant Oracle Corporation has not completely discounted their option to sue Burleson Consulting regarding the latter’s frequent publication of the SQL statement: "select sysdate from dual;" despite having  prior publication dates for  the phrase as a whole and  the words “sysdate” and “dual” independently.

Any such gossip is, as yet, completely unsubstantiated but attorneys representing the descendants of Rene Descartes are said to be keeping their fingers crossed that Oracle Corporation will make something of this issue.

It is possible, however, that these rumours were never intended as serious comment and are the result of the inevitable mockery that ought to follow any ridiculous abuse of the DMCA mechanism.

Update 28th Feb:

The original article is back – with a footnote about the false DMCA claim.

I can’t help noticing that the article was unavailable for 17 days in total, though – which is longer than the eleven days it took for my articles to reaappear. There is an important performance guideline here – when Mr. Burleson is behaving badly, publish the fact and drop a note to his wife.

February 4, 2011

Dynamic Views

Filed under: humour — Jonathan Lewis @ 7:42 pm GMT Feb 4,2011

People forget from time to time that when you query the dynamic performance views (v$ views) you shouldn’t expect to get a read-consistent result set – and this can lead to some very strange results, especially when you start writing joins between views (for example you may be able to find a session is both “not waiting” and “waiting” if you still join v$session and v$session_wait).

Here’s a visual analogy to help you remember what’s going on: “Australian Propellors ?”

And a graphic to help explain the phenomenon: “Rolling Shutter Effect.”

And here, for stunning effect only, is another clip that appeared in the “related topics”.

August 2, 2010


Filed under: humour — Jonathan Lewis @ 6:13 pm GMT Aug 2,2010

There are only three join mechanisms used by Oracle: merge join, hash join and nested loop join.

Here’s an important thought: all three join methods are nested loop joins with different startup costs.



July 21, 2010

Design …

Filed under: humour — Jonathan Lewis @ 8:38 pm GMT Jul 21,2010

… and how not to do it.

In the last couple of weeks I’ve visited two offices which have some really high-tech coffee machines, both from the same company. When you use these machines you have two options, you can punch out the menu options for the drink you want, or you can punch out a “quick code” for the drink you want. In my case (coffee, milk, no sugar) the code is 018, so …

Key punches for option 1

  • coffee
  • milk
  • start

Key punches for option 2

  • Enable quick code
  • 0
  • 1
  • 8
  • start

Yes, it really does take more key strokes to use the quick code than it does to choose what you want.

In fact, checking the dozens of possible drinks combinations on the machine I decided there were probably about six options where the quick code was quicker than just punching in what you wanted, for example:

  • coffee
  • extra strength
  • milk
  • more milk
  • sugar
  • more sugar
  • start

Just keep this in mind next time you think about supplying your end users with a cute feature that’s supposed to make them happier people.

June 28, 2010

Stress Test

Filed under: humour — Jonathan Lewis @ 5:45 pm GMT Jun 28,2010

Here’s something you probably don’t want to say at the start of a public presentation: “Does anyone have a laptop I could borrow for the next 90 minutes.”

I’ve just done my first ODTUG presentation – and everything I do runs under VMWare, which simply refused to start. I’m in my room now trying to fix it before tomorrow’s session. (Many thanks to James Haslam from UKOUG who happened to be in the room at the time and offered up his laptop.)

Update: Inevitably it’s now running perfectly with no intervention at all – so I have to go back downstairs and see if it breaks again.

June 25, 2010


Filed under: humour — Jonathan Lewis @ 6:57 pm GMT Jun 25,2010

I wish more people were aware of problems like this:  Error found on Internet!

May 20, 2010

Philosophy – 11

Filed under: humour,Philosophy — Jonathan Lewis @ 7:02 pm GMT May 20,2010

The English language is full of irregular verbs, for example:

I am hypothesising about possible explanations
You are guessing
He’s talking rubbish

Addendum: The point, of course, is that your interpretation of an individual’s words may be critically affected by who the individual is. The use of the expresssion “English irregular verb” to describe this phenomenon was current around the time that I was at University.

[The Philosophy Series]

Next Page »

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 4,520 other followers