Search

Top 60 Oracle Blogs

Recent comments

humour

Annie Hall

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.

Burleson buys BMC ?

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”.

Disclaimer:

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.

Scientific Method

Dynamic Views

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”.

Best Practice

This came up in one of today’s presentations at the UKOUG annual conference: http://dilbert.com/strips/comic/2008-09-03/

It’s like the line from the movie The Incredibles: “when everyone’s super, no-one’s super.”

You can explain an invalid SQL statement

I’m in “nightmare weekend before presenting” mode. I’m up to my eyes at work (and have been for ages, thus the quiet blog) and my recent weekends have been full of normal {and abnormal} life.

As is the way, when up against it and putting together my proofs for wild claims, everything breaks subtly and makes my wild claims look a little, well, wild – even though they are real issues I’ve seen, worked through and fixed in the day job. *sigh*. It does not help when you come across little oddities you have never seen before and end up spending valuable time looking into them.

So here is one. I’m just putting together a very, very simple demo of how the number of rows the CBO expects to see drops off as you move outside the known range. In the below you can see the statement I am using (I keep passing in different days of the month and watching the expected number of rows drop until I hit 1 expected row), but look at how it progress to the last entry…

mdw11> select count(*) from date_test_flat where date_1=to_date('&day-02-2011','DD-MM-YYYY')
  2  /
Enter value for day: 01

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |    16 |   128 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE(' 2011-02-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

mdw11> /
Enter value for day: 15

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |     2 |    16 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE(' 2011-02-15 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

mdw11> /
Enter value for day: 21

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |     1 |     8 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE(' 2011-02-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

mdw11> /
Enter value for day: 30

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |    99 |   792 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE('30-02-2011','DD-MM-YYYY'))

mdw11>

The expected number of rows drops, becomes and – and has shot up to 99 again (which is the expected number in the known range, as I have 10,000 rows spread over 100 days). My immediate thought is “Wow! Maybe Oracle have put some odd fix in where when you go well out of range it reverts to expecting an average number of rows”. Nope. It is because I asked for the data for 30th February. And I did not get an error.

I think it is because I have set autotrace traceonly explain. This causes the SQL statement not to be executed {if it is just a select, not an insert, update or delete}. It seems the costing section of the CBO is not so good at spotting duff dates, but it then gets the costing wrong.

I’ve spotted that the format of the filter also changes when the date is invalid, I really want to check that out – but I better continue failing to write the presentation!

I know, pretty pointless knowing this but it just amused me. Below is just a quick continuation to show that if the statment is to be executed you get an error and no plan and that utterly duff dates can be passed in.

mdw11> /
Enter value for day: 28

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |     1 |     8 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE(' 2011-02-28 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

mdw11> SET AUTOTRACE ON
mdw11> /
Enter value for day: 20
any key>

  COUNT(*)
----------
         0

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |     1 |     8 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE(' 2011-02-20 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        821  consistent gets
          0  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

mdw11> /
Enter value for day: 30
select count(*) from date_test_flat where date_1=to_date('30-02-2011','DD-MM-YYYY')
                                                         *
ERROR at line 1:
ORA-01839: date not valid for month specified

mdw11> set autotrace traceonly explain
mdw11> /
Enter value for day: 30

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |    99 |   792 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE('30-02-2011','DD-MM-YYYY'))

mdw11> /
Enter value for day: 45

Execution Plan
----------------------------------------------------------
Plan hash value: 247163334

-------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |     1 |     8 |   215   (0)| 00:00:04 |
|   1 |  SORT AGGREGATE    |                |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| DATE_TEST_FLAT |    99 |   792 |   215   (0)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DATE_1"=TO_DATE('45-02-2011','DD-MM-YYYY'))

Design …

… 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 [...]

Stress Test

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 [...]

Errors

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

Philosophy – 11

The English language is full of irregular verbs, for example: I am hypothesising about possible explanations You are guessing He’s talking rubbish [Back to Philosophy 10] Filed under: humour, Philosophy