technical

On Wednesday last week I led a workshop at Oracle office in Burlington, MA. The goal was to outline how to develop on Oracle technology stack and avoid issues. We started at 8:30am east cost time (5:30am pacific), that was quite an early start of the day for me. Also, I had plenty of the material to present. The agenda was set for eight hours but it so happened that we spent two more doing extra presentation and having discussions. I still have to learn how to manage time doing big presentations. I guess I never had a chance :-) to talk for ten hours having anybody listening to me carefully. But the audience was great and eager to learn from my experience as well as folks wanted to share theirs. Good, down to earth questions and real problems and issues to discuss and think about solutions. Some aspects were covered in details, some we had to skim -- lack of time. I aimed folks to have 2-5% increase in their knowledge base comparing to what they had before the workshop. After we finished some stated that it's more than 5%! At least it was not time spent for nothing then -- good increase in performance! :-) There is a Russian saying: "first pancake comes as a lump" (="you must spoil before you spin") and it looks like even there were lumps here and there the pancakes came out tasty. :-)

Below is a snippet from the series of slides about the index growth and leaf blocks splits. Hope one would be interested to think how to hit the limit. We were using Oracle 11.2.

07/07/2011:
So, here is my very artificial example illustrating how fast an index can grow. The script below does perform a set of aggressive index blocks splits and forces that growth-on-steroids behavior. In a matter of seconds we can observe the ORA-00600 [6051].

Nowadays, it would be a rare case to see 2K block size used as a default block size of the database. So at first, I used a small block size for the index segment and for that a special tablespace is created. If you want to run the script make sure that your instance has a proper value for db_2k_cache_size, e.g.:

SQL> show parameter db_2k_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_2k_cache_size                     big integer 16M

CREATE TABLESPACE ora600_6051
  DATAFILE '/tmp/ora600_6051.dbf'
  SIZE 75M
  BLOCKSIZE 2048
/

Second, each index leaf block has one key value only. I am using a function-based index padding the original numeric value to 1469 bytes. FBI is used for the sake of clarity, since I did not want to insert long strings into the table.

CREATE TABLE ora600_6051 (
  v                                NUMBER
)
TABLESPACE ora600_6051
/
CREATE INDEX ora600_6051$i
  ON ora600_6051(SUBSTRB(LPAD(TO_CHAR(v, 'FM0000009'), 1469, '0'), 1, 1469))
  TABLESPACE ora600_6051
/

Third, and that's very cunning, I'm using a tricky way of populating the table. The order of inserted value is based on power of 2, but each set of values is reversed:

RANGE_BEGIN  RANGE_END
----------- ----------
          0          0
          1          1
          3          2
          7          4
         15          8
         31         16
         63         32
        127         64
        255        128
        511        256
       1023        512
       2047       1024
       4095       2048
       8191       4097

SET LINES 1000 TRIMSPOOL ON TERMOUT ON TIMING ON SERVEROUTPUT ON TIMING ON
BEGIN
  FOR b IN 0 .. 13
  LOOP
    FOR i IN REVERSE TRUNC(POWER(2, b - 1)) .. POWER(2, b) - 1
    LOOP
      INSERT INTO ora600_6051 VALUES(i);
      COMMIT;
    END LOOP;
  END LOOP;
END;
/

BEGIN
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [6051], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at line 6

Elapsed: 00:00:07.77
SQL> SET TIMING OFF
SQL> SELECT COUNT(*) FROM ora600_6051;

  COUNT(*)
----------
      8191

SQL> ANALYZE INDEX ora600_6051$i VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT lf_rows
  2       , lf_blks
  3       , br_rows
  4       , br_blks
  5       , height
  6       , height - 1 blevel
  7    FROM index_stats
  8  /

   LF_ROWS    LF_BLKS    BR_ROWS    BR_BLKS     HEIGHT     BLEVEL
---------- ---------- ---------- ---------- ---------- ----------
      8191       8191       8190      24445         24         23

As you can see an insertion of the very last value — 4096 — fails with ORA-600 i.e. we reached the limit. The primary purpose of this post is to illustrate the concept of index growth in Oracle RDBMS. Avoid using this approach to test how fast an index of your production application can grow... your DBA won't be happy to see ORA-600 here and there.