Oracle Scratchpad

November 25, 2011

Quiz Night

Filed under: Indexing,Infrastructure,Oracle — Jonathan Lewis @ 5:05 pm BST Nov 25,2011

Inspired by Martin Widlake’s series on IOTs, I thought I’d throw out this little item. In the following, run against 10.2.0.3, tables t3 and t4 are index organized tables, in the same tablespace, with a primary key of (id1, id2) in that order.


SQL> desc t3
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID1                           NOT NULL NUMBER
 ID2                           NOT NULL NUMBER
 V1                                     VARCHAR2(40)
 V2                                     VARCHAR2(40)
 PADDING                                VARCHAR2(500)

SQL> desc t4
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID1                           NOT NULL NUMBER
 ID2                           NOT NULL NUMBER
 V1                                     VARCHAR2(40)
 V2                                     VARCHAR2(40)
 PADDING                                VARCHAR2(500)

SQL> select index_name, column_name
  2  from user_ind_columns
  3  where table_name in ('T3','T4')
  4  order by table_name, index_name, column_position;

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
T3_PK                ID1
                     ID2

T4_PK                ID1
                     ID2

4 rows selected.

SQL> truncate table t3;

Table truncated.

SQL> truncate table t4;

Table truncated.

SQL> insert into t3 select * from t1;

2000 rows created.

SQL> insert into t4 select * from t1;

2000 rows created.

SQL> commit;

Commit complete.

SQL> set autotrace traceonly
SQL> select max(v1) from t3;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2110918630

---------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |     3 |  2003 |
|   1 |  SORT AGGREGATE       |       |     1 |     3 |       |
|   2 |   INDEX FAST FULL SCAN| T3_PK |  2000 |  6000 |  2003 |
---------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

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

SQL> select max(v1) from t4;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4293386624

---------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |     3 |  2003 |
|   1 |  SORT AGGREGATE       |       |     1 |     3 |       |
|   2 |   INDEX FAST FULL SCAN| T4_PK |  2000 |  6000 |  2003 |
---------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

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

The question – how come, following a truncate and with an index fast full scan in both cases, the number of consistent gets varies so much between the two tables ? You may also wonder why the cost is so high for a fast full scan on such a small amount of data.

In case it helps, here’s another little bit of information:


  1  select
  2     index_name, blevel, leaf_blocks,
  3     avg_leaf_blocks_per_key         avg_lpk,
  4     avg_data_blocks_per_key         avg_dpk,
  5     clustering_factor               cluf
  6  from
  7     user_indexes
  8  where
  9     table_name in ('T3','T4')
 10  order by
 11*    index_name
SQL> /

INDEX_NAME  BLEVEL LEAF_BLOCKS    AVG_LPK    AVG_DPK    CLUF
---------- ------- ----------- ---------- ---------- -------
T3_PK            1           8          1          1    2000
T4_PK            1          12          1          1    2000

21 Comments »

  1. Very interesting. Both IOTs are simple tables without any partitions. You obviously didn’t fiddle with PCTFREE nor INITRANS much, too. I don’t have access to any of my databases, right now, but in my mind it has something to do with IOT OVERFLOW segments. I’l have to check it later.

    Regards
    Pavol Babel

    Comment by Pavol Babel — November 25, 2011 @ 5:40 pm BST Nov 25,2011 | Reply

    • Well, now I have time to add some points.

      1. CLUSTERING FACTOR is very important information. This is the reason why are started thinking of OVERFLOW SEGMENT. When overflow segment takes no place, the clustering factor for primary key of IOT is always 0, so both tables have some overflow segment.
      2. LEAF BLOCKS statistic is very important in this example. 8 and 12 are small numbers, so high cost and high consistent gets definitely caused by overflow segments
      3. explain plan has bug in 10g and does not contain any information about scanning overflow segs.
      4. The overflow segment has at least 2000 blocks in Jonathan’s case (CF is 2000 for both tables)
      5. v1 is the first column after PK in both IOT tables. Reading from the execution plans, optimizer knows v1 could occur in overflow segment for some rows in both tables! That means none of tables is created with “INCLUDING id2 OVERFLOW” syntax (but it was good guess in Vladimir’s post without any access to database), but rather with changed PCTTHRESHOLD clause.
      6. numbers from the following example are close to Jonathan’s, however they do not match exactly. I suggest the main idea has been revealed.

      -- create tablespace t_test 8kB , manual segment management, uniform size 1m
      
      DROP TABLE t3;
      CREATE TABLE t3 (
       id1 NUMBER,
       id2 NUMBER,
       v1 VARCHAR2(40),
       v2 VARCHAR2(40),
       padding VARCHAR2(500),
       CONSTRAINT t3_pk PRIMARY KEY(id1,id2)
      )
      ORGANIZATION INDEX 
      INCLUDING id2 OVERFLOW PCTFREE 90 PCTUSED 10
      TABLESPACE t_test;
      
      DROP TABLE t4;
      CREATE TABLE t4 (
       id1 NUMBER,
       id2 NUMBER,
       v1 VARCHAR2(40),
       v2 VARCHAR2(40),
       padding VARCHAR2(500),
       CONSTRAINT t4_pk PRIMARY KEY(id1,id2)
      )
      ORGANIZATION INDEX 
      PCTTHRESHOLD 1
      OVERFLOW PCTFREE 90 PCTUSED 10
      TABLESPACE t_test;
      
      INSERT INTO t3 
      SELECT rownum, mod(rownum,100), to_char(trunc(dbms_random.value(1,100))), lpad('A', 40, 'A') , lpad('A', 500, 'A')
      FROM dba_objects WHERE rownum <= 2000;
      
      INSERT INTO t4 
      SELECT rownum, mod(rownum,100), to_char(trunc(dbms_random.value(1,100))), lpad('A', 40, 'A') , lpad('A', 500, 'A')
      FROM dba_objects WHERE rownum <= 2000;
      
      COMMMIT;
      
      SQL> SELECT  max(v1) FROM t3
        2  /
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3180047932
      
      ---------------------------------------------------------------
      | Id  | Operation             | Name  | Rows  | Bytes | Cost  |
      ---------------------------------------------------------------
      |   0 | SELECT STATEMENT      |       |     1 |     3 |  2002 |
      |   1 |  SORT AGGREGATE       |       |     1 |     3 |       |
      |   2 |   INDEX FAST FULL SCAN| T3_PK |  2000 |  6000 |  2002 |
      ---------------------------------------------------------------
      
      Note
      -----
         - cpu costing is off (consider enabling it)
      
      
      Statistics
      ----------------------------------------------------------
                1  recursive calls
                0  db block gets
             2015  consistent gets
                0  physical reads
                0  redo size
              225  bytes sent via SQL*Net to client
              247  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed
      
      SQL> SELECT  max(v1) FROM t4
        2  /
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2975522427
      
      ---------------------------------------------------------------
      | Id  | Operation             | Name  | Rows  | Bytes | Cost  |
      ---------------------------------------------------------------
      |   0 | SELECT STATEMENT      |       |     1 |     3 |  2002 |
      |   1 |  SORT AGGREGATE       |       |     1 |     3 |       |
      |   2 |   INDEX FAST FULL SCAN| T4_PK |  2000 |  6000 |  2002 |
      ---------------------------------------------------------------
      
      Note
      -----
         - cpu costing is off (consider enabling it)
      
      
      Statistics
      ----------------------------------------------------------
                1  recursive calls
                0  db block gets
               32  consistent gets
                0  physical reads
                0  redo size
              225  bytes sent via SQL*Net to client
              247  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed
      
      SQL> 
      -- gather stats
      
      
      

      Comment by Pavol Babel — November 26, 2011 @ 1:13 am BST Nov 26,2011 | Reply

      • Well, 4. is not correct. It is was just easier fo me to simulate CF 2000 for T3_PK and T4_PK with overflow semgents at size of 2000 blocks.

        One more remark. It is not important whether jonathan’s T1 is heap table, IOT, hash cluster, or whatever. So I dind’t use SELECT insetad of t1 im my examples

        Regards
        Pavol Babel

        Comment by Pavol Babel — November 26, 2011 @ 4:53 pm BST Nov 26,2011 | Reply

  2. Hmmm very interesting.. can you show us:

    SELECT table_name, iot_type, iot_name FROM dba_tables where table_name in (‘t3′, ‘t4′);

    Overflow area can only contains non-primary key columns and just maybe has something to do with v1 :)

    Regards,
    Luís Marques

    Comment by Luís Marques — November 25, 2011 @ 5:56 pm BST Nov 25,2011 | Reply

    • Luis,

      SELECT table_name, iot_type, iot_name FROM user_tables where table_name in ('T3','T4');
      
      TABLE_NAME           IOT_TYPE     IOT_NAME
      -------------------- ------------ ------------
      T3                   IOT
      T4                   IOT
      
      

      Comment by Jonathan Lewis — November 25, 2011 @ 6:40 pm BST Nov 25,2011 | Reply

    • Luis,

      I was just a little unsporting in my previous reply – I answered the question you asked, rather than answering the question you were probably thinking of, which would have resulted in the following information:

      SQL> select table_name, iot_name, blocks, last_analyzed from user_tables order by table_name;
      
      TABLE_NAME           IOT_NAME       BLOCKS LAST_ANALYZED
      -------------------- ---------- ---------- --------------------
      SYS_IOT_OVER_96468   T3                147 26-Nov-2011 04:13:16
      SYS_IOT_OVER_96471   T4                147 26-Nov-2011 04:13:16
      T1                                     154 26-Nov-2011 04:13:15
      T2                                         26-Nov-2011 04:13:15
      T3                                         26-Nov-2011 04:13:16
      T4                                         26-Nov-2011 04:13:16
      

      This shows that t3 and t4 are index organized tables with overflow segments.

      Although it’s probably not guaranteed, this output also suggests that t1 is a heap organized table, and t2 (which I haven’t mentioned above) is an index organized table without an overflow segment. This is based on the observation that all the tables are showing similar last_analyzed dates, but t1 has a block count and t2 doesn’t.

      Comment by Jonathan Lewis — November 26, 2011 @ 10:01 am BST Nov 26,2011 | Reply

  3. Well, i will leave here my preliminary tests regarding OVERFLOW area :)

    SQL> create table iot_jl (id INTEGER PRIMARY KEY, value INTEGER, value_2 INTEGER) ORGANIZATION INDEX;
    
    Table created.
    
    
    SQL> create table iot_jl2 (id INTEGER PRIMARY KEY, value INTEGER, value_2 INTEGER) ORGANIZATION INDEX INCLUDING value OVERFLOW;
    
    Table created.
    
    
    Table created.
    
    SQL> desc iot_jl;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                        NOT NULL NUMBER(38)
     VALUE                                              NUMBER(38)
     VALUE_2                                            NUMBER(38)
    
    SQL> create table iot_normal as select rownum N1, CEIL(dbms_random.value(0,500)) N2, 
      CEIL(dbms_random.value(0,500)) N3 from dual connect by level  truncate table iot_jl;
    
    Table truncated.
    
    SQL> truncate table iot_jl2;
    
    Table truncated.
    
    
    SQL> insert into iot_jl select  * from iot_normal;
    
    100000 rows created.
    
    
    SQL> insert into iot_jl2 select  * from iot_normal;
    
    100000 rows created.
    
    
    
    SQL? select max(value_2) from iot_jl;
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
            254  consistent gets
              0  physical reads
              0  redo size
            530  bytes sent via SQL*Net to client
            523  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    
    
    SQL> select max(value_2) from iot_jl2;
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
            403  consistent gets
              0  physical reads
              0  redo size
            530  bytes sent via SQL*Net to client
            523  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    

    Here we get 254 consistent gets vs 403 consistent gets on IOT with overflow area. Don’t know if this is the case with JL quiz, but the values for CG are very distinct :)

    Comment by Luís Marques — November 25, 2011 @ 6:25 pm BST Nov 25,2011 | Reply

  4. I don’t have DB now to confirm that, but I think that both tables has overflow segment:
    T3 with INCLUDING id2
    T4 with INCLUDING v1
    That’s why T4 has more leaf blocks than T3.
    Then “select max(v1) from T4″ scans only leaf block but “select max(v1) from T3″ has to scan overflow segment too.

    Comment by Vladimir Jelinek — November 25, 2011 @ 8:32 pm BST Nov 25,2011 | Reply

    • Finaly I have option to test on DB. I stick to my original idea of different including clause.
      It’s pretty close to original numbers. The crucial point is clustering factor. So the testing has to be generated so that Oracle can’t use blocks from overflow area physical order, but has to jump between them for each row from primary key. It leads to 2000 gets.
      My testcase:

      TABLESPACE 8k, UNIFORM , MANUAL
      
      DROP TABLE t1;
      CREATE TABLE t1 (
       id1 NUMBER,
       id2 NUMBER,
       v1 VARCHAR2(40),
       v2 VARCHAR2(40),
       padding VARCHAR2(500)
      )
      TABLESPACE dtest;
      
      INSERT INTO t1
      SELECT mod(rownum,100), rownum, mod(rownum,100), rownum/100, lpad('A', 500, 'A')
      FROM dba_objects WHERE rownum  select max(v1) from t3;
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3180047932
      
      -------------------------------------------------------------------------------
      | Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT      |       |     1 |     3 |  2005   (0)| 00:00:25 |
      |   1 |  SORT AGGREGATE       |       |     1 |     3 |            |          |
      |   2 |   INDEX FAST FULL SCAN| T3_PK |  2000 |  6000 |  2005   (0)| 00:00:25 |
      -------------------------------------------------------------------------------
      
      
      Statistics
      ----------------------------------------------------------
                1  recursive calls
                0  db block gets
               62  consistent gets
                0  physical reads
                0  redo size
              217  bytes sent via SQL*Net to client
              239  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed
      
      SQL&gt; select max(v1) from t4;
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2975522427
      
      -------------------------------------------------------------------------------
      | Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT      |       |     1 |     3 |  2004   (0)| 00:00:25 |
      |   1 |  SORT AGGREGATE       |       |     1 |     3 |            |          |
      |   2 |   INDEX FAST FULL SCAN| T4_PK |  2000 |  6000 |  2004   (0)| 00:00:25 |
      -------------------------------------------------------------------------------
      
      
      Statistics
      ----------------------------------------------------------
                0  recursive calls
                0  db block gets
             2041  consistent gets
                0  physical reads
                0  redo size
              224  bytes sent via SQL*Net to client
              239  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed
      

      Comment by Vladimir Jelinek — November 26, 2011 @ 5:22 pm BST Nov 26,2011 | Reply

      • previous post wan’t complete. Reposting:

        
        DROP TABLE t1;
        CREATE TABLE t1 (
         id1 NUMBER,
         id2 NUMBER,
         v1 VARCHAR2(40),
         v2 VARCHAR2(40),
         padding VARCHAR2(500)
        )
        TABLESPACE dtest;
        
        INSERT INTO t1
        SELECT mod(rownum,100), rownum, mod(rownum,100), rownum/100, lpad('A', 500, 'A')
        FROM dba_objects WHERE rownum <= 2000;
        
        DROP TABLE t3;
        CREATE TABLE t3 (
         id1 NUMBER,
         id2 NUMBER,
         v1 VARCHAR2(40),
         v2 VARCHAR2(40),
         padding VARCHAR2(500),
         CONSTRAINT t3_pk PRIMARY KEY(id1,id2)
        )
        ORGANIZATION INDEX
        INCLUDING v1 OVERFLOW
        TABLESPACE dtest;
        
        
        DROP TABLE t4;
        CREATE TABLE t4 (
         id1 NUMBER,
         id2 NUMBER,
         v1 VARCHAR2(40),
         v2 VARCHAR2(40),
         padding VARCHAR2(500),
         CONSTRAINT t4_pk PRIMARY KEY(id1,id2)
        )
        ORGANIZATION INDEX
        INCLUDING id2 OVERFLOW
        TABLESPACE dtest;
        
        
        INSERT INTO t3 select * from t1;
        COMMIT;
        INSERT INTO t4 select * from t1;
        COMMIT;
        
        -- gather stats
        
        
        TABLE_NAME                     IOT_NAME                           BLOCKS LAST_ANALY
        ------------------------------ ------------------------------ ---------- ----------
        SYS_IOT_OVER_75386             T3                                    143 26.11.2011
        SYS_IOT_OVER_75389             T4                                    143 26.11.2011
        T1                                                                   158 26.11.2011
        T3                                                                       26.11.2011
        T4                                                                       26.11.2011
        
        INDEX_NAME                         BLEVEL LEAF_BLOCKS    AVG_LPK    AVG_DPK       CLUF
        ------------------------------ ---------- ----------- ---------- ---------- ----------
        T3_PK                                   1          13          1          1       2000
        T4_PK                                   1           8          1          1       2000
        
        
        SQL> select max(v1) from t3;
        
        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 3180047932
        
        -------------------------------------------------------------------------------
        | Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
        -------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT      |       |     1 |     3 |  2005   (0)| 00:00:25 |
        |   1 |  SORT AGGREGATE       |       |     1 |     3 |            |          |
        |   2 |   INDEX FAST FULL SCAN| T3_PK |  2000 |  6000 |  2005   (0)| 00:00:25 |
        -------------------------------------------------------------------------------
        
        
        Statistics
        ----------------------------------------------------------
                  1  recursive calls
                  0  db block gets
                 62  consistent gets
                  0  physical reads
                  0  redo size
                217  bytes sent via SQL*Net to client
                239  bytes received via SQL*Net from client
                  2  SQL*Net roundtrips to/from client
                  0  sorts (memory)
                  0  sorts (disk)
                  1  rows processed
        
        SQL> select max(v1) from t4;
        
        
        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 2975522427
        
        -------------------------------------------------------------------------------
        | Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
        -------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT      |       |     1 |     3 |  2004   (0)| 00:00:25 |
        |   1 |  SORT AGGREGATE       |       |     1 |     3 |            |          |
        |   2 |   INDEX FAST FULL SCAN| T4_PK |  2000 |  6000 |  2004   (0)| 00:00:25 |
        -------------------------------------------------------------------------------
        
        
        Statistics
        ----------------------------------------------------------
                  0  recursive calls
                  0  db block gets
               2041  consistent gets
                  0  physical reads
                  0  redo size
                224  bytes sent via SQL*Net to client
                239  bytes received via SQL*Net from client
                  2  SQL*Net roundtrips to/from client
                  0  sorts (memory)
                  0  sorts (disk)
                  1  rows processed
        
        
        
        

        Comment by Vladimir Jelinek — November 26, 2011 @ 5:53 pm BST Nov 26,2011 | Reply

        • Hi Vlado,

          You posted your example later, but I have to admit your’s is more accurate, it better describes the CBO’s decision. I though CBO knew it didn’t have to visit OVERFLOW SEGMENT when “INCLUDING v1 OVERFLOW” was used (and the default PCTTHRESHOLD 50). CBO has enough information to consider that non-key column v1 will be always stored in PRIMARY INDEX part of IOT, because id1, id2 and v1 together will never reach 50% of 8192 (or event 4096 o 2048) byte block.
          However optimizer obviously predicts the OVERFLOW SEGMENT inspection for every row when any non-key column is specified in SQL statement and it’s quite significant different from the runtime execution.

          Regards
          Pavol Babel

          Comment by Pavol Babel — November 26, 2011 @ 9:56 pm BST Nov 26,2011

        • Hi Palo,
          your are right. Optimizer could recognize that for table T3 he always hit v1 in PK. But apparently he doesn’t do such deep analysis and create the execution plan with same cost. In reality both execution plans has very different cost.

          I posted my example later but, in fact I was motivated by Jonathan’s post from November 26, 2011 @ 10:01 am UTC. It’s apparent from it, that overflow segment is small just few hundreds block, so the crucial thing is clustering factor and additional logical read which results from it.

          IMO the worst thing is how the execution plan is misleading, because when I see INDEX FAST FULL SCAN, I expecting SEQUENTIAL READS from index segment which is true for T3 table but in case of T4 I have the same execution plan but in reality I have few sequential reads from index segment and LOT of RANDOM READS from overflow segment. On the overflow segment bigger than buffer cache with bad clustering factor I will get many random physical reads and execution times will be very different.

          Comment by Vladimir Jelinek — November 27, 2011 @ 10:33 am BST Nov 27,2011

        • exactly. The execution plan is very misleading in this situation. It was quite clear CF is the most importan statistic in this case, but the situation is even worst thah I expected.
          Great example by Jonathan and great observations by you :)

          Regarda
          Pavol Babel

          Comment by Pavol Babel — November 27, 2011 @ 3:50 pm BST Nov 27,2011

  5. I have no idea what overflow segments are (the data in IOTs?) so another area of reading for me.

    Since we don’t know what the state of play was before the truncate my guess is:
    - Histogram stats on t4 and not t3 ?

    As a side question, if Oracle has been asked to do a MAX on a PK column, why doesn’t it just read the index FFS in reverse?

    Comment by Darryl Griffiths — November 26, 2011 @ 10:21 am BST Nov 26,2011 | Reply

    • “As a side question, if Oracle has been asked to do a MAX on a PK column, why doesn’t it just read the index FFS in reverse?”

      OK, just read that back, and realised that it’s not the PK you’re using.

      Comment by Darryl Griffiths — November 26, 2011 @ 10:26 am BST Nov 26,2011 | Reply

  6. Hi Jonathan,

    Very much interesting test case. As per my analysis

    1. we need to check the type of “segment space management” (MSSM) and physical attributes of the segment you are delaing ? (Reason for that is since if I look at the CF of the index segment its high – that make me thinking that, you index segment (used space) is almost equal to the primary key which makes the data to overflow for next blocks.

    2. Coming to difference in “consistent gets”, I doubt it Oracle would might have known with the index information on next fetch of segment (t4). But how I still in trying to understand.

    Comment by Pavan Kumar N — November 26, 2011 @ 2:52 pm BST Nov 26,2011 | Reply

  7. Are there any ongoing transcations on t3 so oracle has to apply undo to get consisten blocks ?:)
    Regards
    GregG

    Comment by goryszewskig — November 26, 2011 @ 3:07 pm BST Nov 26,2011 | Reply

  8. Plenty of good comments here – and the answers are related to overflow.
    I’ll be writing a follow-up posting this evening, or possibly tomorrow evening.

    Comment by Jonathan Lewis — November 27, 2011 @ 10:48 am BST Nov 27,2011 | Reply

  9. [...] was good to see the answers to the last Quiz Night accumulating. The problem posed was simply this: I have two IOTs and I’ve inserted the same [...]

    Pingback by IOT Answer « Oracle Scratchpad — November 27, 2011 @ 10:04 pm BST Nov 27,2011 | Reply

  10. [...] Here’s an odd little detail about the to_char() function that happened to show up in a little demonstration code I used to create some data for last Friday’s quiz night. [...]

    Pingback by to_char() « Oracle Scratchpad — December 2, 2011 @ 7:49 am BST Dec 2,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers