OERI 6051

How smashed (in the non-alcoholic sense) can an index be?

One of the components in the cost calculation for an indexed access path is the “blevel” (branch-level) or, indirectly, the “height” of the index. Both count the steps from the root block down to a leaf block (and all leaf blocks are at the same distance from the root – that’s the meaning of “balanced” in the expression “balanced B-tree”) but the height includes the leaf level in the count while the blevel excludes it and counts down only to the lowest level of branch blocks (so height = blevel + 1).

In many cases you will find that even with a few million entries in a single index segment the height may still be only 3 (blevel = 2), and it may take a few tens of millions of rows before an index needs to grow to height = 4 (blevel = 3).

It’s often the case that the number of index entries per leaf block and block pointers per branch block is around 200 to 400, so the rate at which the height/blevel grows is tiny compared to the rate at which the number of rows in the index increases. But algorithms often have weak points, and some time around the year 2000 I started demonstrating an edge case where I could crash a session in less than 3 seconds (and most of that time was spent on Oracle creating the crash dump) by inserting just 25 (carefully designed) rows into a table.

I published an article about this in 2005 (see footnote), but since then the algorithm has changed. My demo worked in versions up to 9.2.0.4; but in later versions Oracle Corp. modified the way that index blocks (possibly just the branch blocks) split at the low end of the index making the harder to achieve a crash. If you have a MOS account you can check Doc ID 1748260.8: OERI:6051 possible during index manipulation.

The change wasn’t a response to my demo, of course; it was in response to a problem that could occur in production systems running some of the big “database agnostic” accounting or HR or CRM systems that created huges indexes on multiple columns. Even when the crash never occured the nature of the application and its indexing strategy could result in some indexes growing to a ridiculous height that made a dramatic difference to the cost calculations (hence the desirability of the “best” index).

It’s harder, and less likely to happen in the wild, but it’s still possible to make the same crash occur even in the newest versions of Oracle. It will (probably) take roughly 8 million (power(2,23) + 1) rows and 32GB of space to crash (or 128GB if you want to play nicely and use an 8KB block size – and tweak my code a little further).

Richard Foote spotted a slide with a surprising blevel in a short presentation about CBO arithmetic by Maria Colgan a couple of days ago, so I thought it would be entertaining to tweak the old code to see if it could still cause the crash. So here it is:

rem
rem     Script:         silly_index_3a.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2004 / Jan 2022
rem     Purpose:        Build an index with a large blevel
rem
rem     Notes: 
rem     Uses 2K block size for tablespace holding the index
rem     Estimated run-time (for me) with m_blevel = 23 - ca. 1 hour
rem

define m_blevel = 5
define m_rows = power(2,&m_blevel)

drop table t1 purge;
create table t1 (v1 varchar2(8));

create index t1_i1 on t1(substrb(lpad(v1,1469,'0'),1,1469))
tablespace test_2k
;

-- execute snap_my_stats.start_snap

prompt  ===================================================
prompt  Inserting &m_rows (short) rows in reverse order
prompt  ===================================================

begin
        for i in reverse 1..&m_rows loop
                insert into t1 values (i);
--              commit;
        end loop;
end;
/

-- execute snap_my_stats.end_snap

prompt  ================
prompt  Validating index
prompt  ================

validate index t1_i1;

select 
        lf_rows, height, height-1 blevel, lf_blks, br_blks
from
        index_stats
;

column  object_id new_value m_object_id

select  object_id
from    user_objects
where   object_name = 'T1_I1'
/

alter session set events 'immediate trace name treedump level &m_object_id';

insert into t1(v1) values('0');

I’ve precreated a tablespace called test_2k with a block size of 2KB for this demo; you’ll need a couple of percent over 32GB for this tablespace.

This script then creates a table in my default tablespace to hold a small character column, and a function-based index on that column that produces a character result of 1469 bytes (which gives me the largest possible index entry that’s allowed in a 2KB block size). The older version of the code used a simple lpad() to do this, but the newer versions decided that that would produce up to 2*1,469 bytes thanks to my default character set – hence the substrb(), note, especially the b for byte.

With the structure in place I’ve then inserted numeric values in descending order into the table so that the index is constantly doing leaf block splits at the left hand (low) end.

Once I’ve populated the table I use a call to validate index so that I can report the number of rows, leaf blocks, branch blocks and the height (and blevel) of the index; then I find it’s object_id so that I can do a treedump of it.

For m_blevel = 5, here are the results of the query against index_stats after the call to validate the index:

   LF_ROWS     HEIGHT     BLEVEL    LF_BLKS    BR_BLKS
---------- ---------- ---------- ---------- ----------
        32          6          5         32         31

As you can see, setting m_blevel = 5 I get an index with blevel = 5, and 2^5 leaf blocks each holding one row. If you set m_blevel to 23 you’ll end up (after about 1 hour, probably) with a blevel of 23 and 8,388,608 rows and leaf blocks (and branch blocks = leaf blocks – 1: hence the 32GB+ requirement for the tablespace … 16M blocks at 2KB per block, plus ASSM overheads).

To show you what’s happening inside the index here’s the treedump (from 19.11.0.0) with m_blevel = 5

branch: 0x4c00204 79692292 (0: nrow: 2, level: 5)
   branch: 0x4c0022a 79692330 (-1: nrow: 2, level: 4)
      branch: 0x4c00212 79692306 (-1: nrow: 2, level: 3)
         branch: 0x4c00206 79692294 (-1: nrow: 2, level: 2)
            branch: 0x4c0020c 79692300 (-1: nrow: 2, level: 1)
               leaf: 0x4c00209 79692297 (-1: row:1.1 avs:370)
               leaf: 0x4c00249 79692361 (0: row:1.1 avs:370)
            branch: 0x4c00248 79692360 (0: nrow: 2, level: 1)
               leaf: 0x4c00247 79692359 (-1: row:1.1 avs:370)
               leaf: 0x4c00246 79692358 (0: row:1.1 avs:370)
         branch: 0x4c00244 79692356 (0: nrow: 2, level: 2)
            branch: 0x4c00243 79692355 (-1: nrow: 2, level: 1)
               leaf: 0x4c00242 79692354 (-1: row:1.1 avs:370)
               leaf: 0x4c0024f 79692367 (0: row:1.1 avs:370)
            branch: 0x4c0024e 79692366 (0: nrow: 2, level: 1)
               leaf: 0x4c00235 79692341 (-1: row:1.1 avs:370)
               leaf: 0x4c00239 79692345 (0: row:1.1 avs:370)
      branch: 0x4c00238 79692344 (0: nrow: 2, level: 3)
         branch: 0x4c00237 79692343 (-1: nrow: 2, level: 2)
            branch: 0x4c00236 79692342 (-1: nrow: 2, level: 1)
               leaf: 0x4c00234 79692340 (-1: row:1.1 avs:370)
               leaf: 0x4c00233 79692339 (0: row:1.1 avs:370)
            branch: 0x4c00232 79692338 (0: nrow: 2, level: 1)
               leaf: 0x4c00231 79692337 (-1: row:1.1 avs:370)
               leaf: 0x4c00230 79692336 (0: row:1.1 avs:370)
         branch: 0x4c0023f 79692351 (0: nrow: 2, level: 2)
            branch: 0x4c0023e 79692350 (-1: nrow: 2, level: 1)
               leaf: 0x4c0023d 79692349 (-1: row:1.1 avs:370)
               leaf: 0x4c0023c 79692348 (0: row:1.1 avs:370)
            branch: 0x4c00225 79692325 (0: nrow: 2, level: 1)
               leaf: 0x4c0022d 79692333 (-1: row:1.1 avs:370)
               leaf: 0x4c0022c 79692332 (0: row:1.1 avs:370)
   branch: 0x4c0022b 79692331 (0: nrow: 2, level: 4)
      branch: 0x4c00229 79692329 (-1: nrow: 2, level: 3)
         branch: 0x4c00228 79692328 (-1: nrow: 2, level: 2)
            branch: 0x4c00227 79692327 (-1: nrow: 2, level: 1)
               leaf: 0x4c00226 79692326 (-1: row:1.1 avs:370)
               leaf: 0x4c00224 79692324 (0: row:1.1 avs:370)
            branch: 0x4c00223 79692323 (0: nrow: 2, level: 1)
               leaf: 0x4c00222 79692322 (-1: row:1.1 avs:370)
               leaf: 0x4c0022f 79692335 (0: row:1.1 avs:370)
         branch: 0x4c0022e 79692334 (0: nrow: 2, level: 2)
            branch: 0x4c00215 79692309 (-1: nrow: 2, level: 1)
               leaf: 0x4c00219 79692313 (-1: row:1.1 avs:370)
               leaf: 0x4c00218 79692312 (0: row:1.1 avs:370)
            branch: 0x4c00217 79692311 (0: nrow: 2, level: 1)
               leaf: 0x4c00216 79692310 (-1: row:1.1 avs:370)
               leaf: 0x4c00214 79692308 (0: row:1.1 avs:370)
      branch: 0x4c00213 79692307 (0: nrow: 2, level: 3)
         branch: 0x4c00211 79692305 (-1: nrow: 2, level: 2)
            branch: 0x4c00210 79692304 (-1: nrow: 2, level: 1)
               leaf: 0x4c0021f 79692319 (-1: row:1.1 avs:370)
               leaf: 0x4c0021e 79692318 (0: row:1.1 avs:370)
            branch: 0x4c0021d 79692317 (0: nrow: 2, level: 1)
               leaf: 0x4c0021c 79692316 (-1: row:1.1 avs:370)
               leaf: 0x4c00208 79692296 (0: row:1.1 avs:370)
         branch: 0x4c00207 79692295 (0: nrow: 2, level: 2)
            branch: 0x4c00205 79692293 (-1: nrow: 2, level: 1)
               leaf: 0x4c0020f 79692303 (-1: row:1.1 avs:370)
               leaf: 0x4c0020e 79692302 (0: row:1.1 avs:370)
            branch: 0x4c0020d 79692301 (0: nrow: 2, level: 1)
               leaf: 0x4c0020b 79692299 (-1: row:1.1 avs:370)
               leaf: 0x4c0020a 79692298 (0: row:1.1 avs:370)
----- end tree dump

As you can see, every branch block (which includes the root block) holds exactly 2 entries, and every leaf block holds just one row.

Once you’ve tested the code with a couple of small starting values you might want to skip the validate index and treedump steps – they might take quite a long time (especially since the treedump will write a trace file of 16M+ lines). The other thing to watch out for is that the script will generate something like 200GB of redo and 72GB of undo – so you might want to remove the comment marker from the commit in my PL/SQL loop and check your auto undo settings and auto extend settings on the undo files.

I should also point out that I’ve used 1469 for the substrb(lpad()) because that’s the largest string I can use for the index definition – but the same index pattern of use (i.e. one row per leaf block, two children per branch block) will appear if you reduce this (for a 2KB block size, using ASSM) to 915 bytes. (And this is instructive because if you use the smaller size and then eliminate the “reverse” in the loop the index branch blocks pack more efficiently and the blevel is smaller (even though the index leaf-block count is unchanged.)

In the good old days (9.2.0.4 and earlier) the maximum allowed height for a B-tree index was 24 (blevel = 23). I haven’t got a spare 32GB in any of my virtual machines at present so I haven’t checked to see if this is still true; but if you do run a test with m_blevel – 23, the final line of the script (inserting a zero row) should result in an ORA-00600 error with first parameter 6051 if the limit hasn’t changed.

Footnote

Here’s a link to the original document (how_high.doc) that I wrote and published in dbazine in 2005, now available (very slowly) on the Wayback Machine, and made redundant by a change in the branch block split algorithm in 10g.

Footnote 2

At some point I did discover a bug note on MOS (Metalink, in those days) that reported a performance problem due to an index with a very high blevel (I have a vague memory of it reaching double digits, but not very getting close to the limit – possibly something like 15). So there is a serious point to this post – bad column definitions with bad index definitions (and a silly block size) and a bit of bad luck with the pattern of data insertion can lead to an unexpected problems.

Index Conspiracy

A little light entertainment – but with a tiny bit of information that’s worth knowing – for a Friday evening. This is a demo I used at IOUG 2003 to warm the audience up before the main event.

We start with a table and two indexes – and a clunky little query just to show the index names and object ids.

rem
rem     Script:         index_conspiracy_2.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2003 / Nov 2021
rem     Purpose:        Demonstrate the anti-microsoft conspiracy.
rem

create table t1 
as
select 
        rownum          n1, 
        rownum          n2,
        rpad('x',10)    small_vc,
        rpad('x',100)   padding
from
        all_objects
where
        rownum <= 3000
;

create index first_col_index on t1(n1);
create index microsoft_index on t1(n2);

select object_id, table_name, index_name
from
        (
        select  object_id, object_name
        from    user_objects
        where   object_type = 'INDEX'
        )       v1,
        (
        select table_name, index_name
        from   user_indexes
        where  table_name = 'T1'
        )       v2
where
        object_name = index_name
order by 
        object_id
;

You’ll notice that the n1 and n2 columns are identical and that means the corresponding indexes will have identical content, statistics and costs. So let’s use autotrace to check the plans for a few queries – we won’t be using bind variables so it’s a good bet that the plans from autotrace would be what we’d get if we actually ran the queries and pulled the plans from memory:

set autotrace traceonly explain

prompt  ==========================================
prompt  Initial Behaviour (uses "first_col_index")
prompt  ==========================================

select
        *
from    t1
where   n1 = 44
and     n2 = 44
;

At the time of the IOUG conference there was quite a lot of antipathy between Microsoft and Oracle, so it didn’t surprise anyone that the query ignored the index called “microsoft_index” and used the following plan:

Execution Plan
----------------------------------------------------------
Plan hash value: 4128733246

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     1 |   120 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 |   120 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | FIRST_COL_INDEX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

   1 - filter("N2"=44)
   2 - access("N1"=44)

We can demonstrate that the microsoft_index was an appropriate index for this query by renaming it, of course, and I’ll leave it as an exercise for the user to see what happens as we go through several different names:

prompt  ============================================
prompt  Renaming "microsoft_index" to "better_index"
prompt  ============================================

alter index microsoft_index rename to better_index;

select
        *
from    t1
where   n1 = 44
and     n2 = 44
;

prompt  ============================================
prompt  Renaming "better_index" to "microsoft_index"
prompt  ============================================

alter index better_index rename to microsoft_index ;

select
        *
from    t1
where   n1 = 44
and     n2 = 44
;

prompt  =============================================
prompt  Renaming "microsoft_index" to "ellison_index"
prompt  =============================================

alter index microsoft_index rename to ellison_index ;

select
        *
from    t1
where   n1 = 44
and     n2 = 44
;

prompt  =========================================
prompt  Renaming "ellison_index" to "gates_index"
prompt  =========================================

alter index ellison_index rename to gates_index ;

select
        *
from    t1
where   n1 = 44
and     n2 = 44
;

set autotrace off

I think the version of Oracle that I demonstrated this on was a late version of 9i. The behaviour is the same in 21c.

Bait and Switch

Just what you need after a long hike in the Lake District:

Histograms

I had a sudden urge to go a bit meta – so here’s a relative frequency histogram of my observations of the general use of histograms in Oracle:

 

 

 

Quantum Space

Here’s a not very serious note that makes a serious point.  I’ve got a small tablespace made up of 4 files, and here’s a little report I can run against the data dictionary for that tablespace:

select 'File space' What, nvl(sum(user_bytes)/1048576,0) MB from dba_data_files where tablespace_name = 'LOB_TEST'
union all
select 'Free space',      nvl(sum(bytes/1048576),0)         from dba_free_space where tablespace_name = 'LOB_TEST'
union all
select 'Extents',         nvl(sum(bytes/1048576),0)         from dba_extents    where tablespace_name = 'LOB_TEST'
union all
select 'Segments',        nvl(sum(bytes/1048576),0)         from dba_segments   where tablespace_name = 'LOB_TEST'
;

The name of the tablespace isn’t significant – it happens to be a tablespace I created to do some tests relating to space allocation with securefile LOBs, and it’s been hanging around ever since.

The query first reports the “user” space defined by the files – remember that there will be some space for header information and other metadata, and there may be some space near the end of the file which is smaller than the minimum extent allowed for that tablespace.

After the total user space the query reports :

• the free space
• the space currently allocated to extents
• the space currently allocated to segments

Here’s the result I get currently:

WHAT               MB
---------- ----------
File space        196
Free space        196
Extents             0
Segments          196

The total user space is 196MB, of which 196MB are free and there’s no space allocated to extents — except 196MB has been allocated to segments.

Depending how you look at it the tablespace is either full, or empty.

If you’re in the know the puzzle resolves itself when you query dba_segments for the segment names, which look like: “BIN$TrDKUCvcVQbgUwEAAH9K2Q==$0” – I have a dropped segment in the recycle bin, so that I can recover it from the bin if I need it (with a call to “flashback table XXX to before drop”), but Oracle is allowed to reuse the space if it needs to. Like Schrodinger’s cat the object is both alive and dead until someone decides to peek.

After a call to “purge recyclebin” the result changes to:

WHAT               MB
---------- ----------
File space        196
Free space        196
Extents             0
Segments            0

On a production system you might need to issue “purge dba_recyclebin” (if you have the appropriate privilege) to resolve the apparent contradiction as the call to “purge recyclebin” applies only to objects in your own schema.

This note was prompted by a question on the OTN database forum about contradiction between a traditional SQL statement to report free and used space and a screen dump from Enterprise Manager.  I don’t think the thread reached a firm conclusion – but apart from the potential for the recyclebin to confuse the issue, there are extra possibilities thanks to auto-extensible data files, and the Enterprise Manager’s scope for querying a complete different set of views such as dba_tablespace_usage_metrics and v$filespace_usage. In fact the EM code clearly had at least one error in it (which makes any of its results suspect) because it managed to report the critical tablespace as 390% used!

 

 

Reorg

A current question on the OTN database forum asks: “What’s the difference between object and tablespace reorganization?” Here’s an analogy to address the question.

I have three crates of Guiness in the boot (trunk) of my car, one crate has 4 bottles left, one has 7 bottles left and one has 2 bottles. I also have two cases of Louis Roederer Brut NV champagne, one case has 2 bottles left and one has only one. (I have two objects in my tablespace – one of type Beer, one of type Champagne – and my boot requires manual free space management .)

I move all the Guiness bottles into a single crate and all the champagne bottles into a single case. That’s a couple of “shrink space compact” calls – I’ve re-organised the objects to get all the bottles in each object close to each other, but the crates are still taking up space in the boot.

I take the two empty crates and the empty case out of the boot. That’s a couple of “resize” (or “shrink space” without “compact”) calls that free up space in the boot.

I now want to load a port barrel into car, but it won’t fit until I slide the remaining beer crate and champagne case together at one side of the boot. That’s a couple of “move” commands that have reorganized the boot (tablespace) to make the free space usable.

 

count(*) – again !

Because you can never have enough of a good thing.

Here’s a thought – The optimizer doesn’t treat all constants equally.  No explanations, just read the code – execution plans at the end:

SQL> drop table t1 purge;
SQL> create table t1 nologging as select * from all_objects;
SQL> create bitmap index t1_b1 on t1(owner);

SQL> alter session set statistics_level = all;

SQL> set serveroutput off
SQL> select count(*) from t1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

SQL> select count(1) from t1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

SQL> select count(-1) from t1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

SQL> alter session set cursor_sharing = force;
SQL> alter system flush shared_pool;

SQL> select count(1) from t1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

So, are you expecting to see the same results and performance from every single one of those queries ?

select count(*) from t1
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      1 |00:00:00.01 |       9 |      5 |
|   1 |  SORT AGGREGATE               |       |      1 |      1 |      1 |00:00:00.01 |       9 |      5 |
|   2 |   BITMAP CONVERSION COUNT     |       |      1 |  84499 |     31 |00:00:00.01 |       9 |      5 |
|   3 |    BITMAP INDEX FAST FULL SCAN| T1_B1 |      1 |        |     31 |00:00:00.01 |       9 |      5 |
----------------------------------------------------------------------------------------------------------

select count(1) from t1
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      1 |00:00:00.01 |       9 |
|   1 |  SORT AGGREGATE               |       |      1 |      1 |      1 |00:00:00.01 |       9 |
|   2 |   BITMAP CONVERSION COUNT     |       |      1 |  84499 |     31 |00:00:00.01 |       9 |
|   3 |    BITMAP INDEX FAST FULL SCAN| T1_B1 |      1 |        |     31 |00:00:00.01 |       9 |
-------------------------------------------------------------------------------------------------

select count(-1) from t1
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      1 |00:00:00.43 |       9 |
|   1 |  SORT AGGREGATE               |       |      1 |      1 |      1 |00:00:00.43 |       9 |
|   2 |   BITMAP CONVERSION TO ROWIDS |       |      1 |  84499 |  84499 |00:00:00.22 |       9 |
|   3 |    BITMAP INDEX FAST FULL SCAN| T1_B1 |      1 |        |     31 |00:00:00.01 |       9 |
-------------------------------------------------------------------------------------------------

SQL> alter session set cursor_sharing = force;
SQL> alter system flush shared_pool;

select count(1) from t1
select count(:"SYS_B_0") from t1    -- effect of cursor-sharing
-------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |      1 |        |      1 |00:00:00.46 |       9 |
|   1 |  SORT AGGREGATE               |       |      1 |      1 |      1 |00:00:00.46 |       9 |
|   2 |   BITMAP CONVERSION TO ROWIDS |       |      1 |  84499 |  84499 |00:00:00.23 |       9 |
|   3 |    BITMAP INDEX FAST FULL SCAN| T1_B1 |      1 |        |     31 |00:00:00.01 |       9 |
-------------------------------------------------------------------------------------------------

Check operation 2 in each plan – with the bitmap index in place there are two possible ways to count the rows referenced in the index – and one of them converts to rowids and does a lot more work.

The only “real” threat in this set of examples, of course, is the bind variable one – there are times when count(*) WILL be faster than count(1). Having said that, there is a case where a redundant “conversion to rowids” IS a threat – and I’ll write that up some time in the near future.

Trick question: when is 1+1 != 2 ?
Silly answer: compare the plan for: “select count (2) from t1” with the plan for “select count(1+1) from t1”

Note: All tests above run on 12.1.0.2

Comparisons

“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> 
SQL> create table fruit(v1 varchar2(30));
SQL> 
SQL> insert into fruit values('apples');
SQL> insert into fruit values('oranges');
SQL> commit;
SQL> 
SQL> 
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> 
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> 
SQL> 
SQL> 
SQL> select
  2  	     max(endpoint_value) - min(endpoint_value) diff
  3  from
  4  	     user_tab_histograms
  5  where
  6  	     table_name = 'FRUIT'
  7  ;

                                            DIFF
------------------------------------------------
  72,731,533,037,581,000,000,000,000,000,000,000
SQL> 
SQL> spool off

Science

Sometimes I wish …  http://www.nature.com/nature/journal/v489/n7414/full/489170a.html

Random Plans

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

Proof

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

Queueing Theory.

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.

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.

Smiley

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.

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.