jump to navigation

IOT Secondary Indexes – The Logical ROWID Guess Component Part II (Move On) May 8, 2012

Posted by Richard Foote in Index Block Size, Index Organized Tables, IOT, ROWID, Secondary Indexes.
7 comments

Having mentioned a couple of dangers associated with IOT Secondary Indexes, thought I might discuss a couple of their nicer attributes.

In the previous post, we saw how 50-50 index block splits on the ALBUM_SALES_IOT IOT table caused rows to move to new leaf blocks, resulting in a degradation in the PCT_DIRECT_ACCESS value of the associated ALBUM_SALES_IOT_TOTAL_SALES_I secondary index, which in turn resulted in poorer performance when using this index. We had to rebuild the secondary index (or update block references) to make all the “guess” components accurate and the index efficient again and so point to the correct locations within the parent IOT.

So, if you have 50-50 block splits occurring in your IOT, this will degrade the efficiency of the associated IOT Secondary indexes over time.

However, if you don’t have 50-50 block splits and the entries in the IOT don’t move from leaf block to leaf block, then this will not be an issue. Remembering of course that many Primary Key values are based on a sequence which monotonically increases and results in 90-10 block splits rather than 50-50 block splits.  90-10 block splits don’t move data around, Oracle leaves the full blocks alone and simply adds a new block in the IOT Btree structure into which new values are added. Therefore, with IOT data not moving around, the “guess” component of the logical ROWIDS remain valid and don’t go stale over time and so the associated secondary indexes remain nice and efficient.

If we look at the current state of the ALBUM_SALES_IOT_TOTAL_SALES_I secondary index:

SQL> SELECT index_name, pct_direct_access, iot_redundant_pkey_elim  FROM dba_indexes WHERE index_name = 'ALBUM_SALES_IOT_TOTAL_SALES_I';

INDEX_NAME                     PCT_DIRECT_ACCESS IOT
------------------------------ ----------------- ---
ALBUM_SALES_IOT_TOTAL_SALES_I                100 NO

We notice the PCT_DIRECT_ACCESS is currently nice and efficient at 100%.

If we now add a bunch of new rows into the IOT, but this time with PK values that monotonically increase:

SQL> BEGIN
  2    FOR i IN 5001..10000 LOOP
  3      FOR c IN 201..300 LOOP
  4        INSERT INTO album_sales_iot VALUES(i,c,ceil(dbms_random.value(1,5000000)), 'Yet more new rows');
  5      END LOOP;
  6    END LOOP;
  7    COMMIT;
  8  END;
  9  /

PL/SQL procedure successfully completed.

And collect fresh statistics:

SQL> exec dbms_stats.gather_table_stats(ownname=>'BOWIE', tabname=>'ALBUM_SALES_IOT', estimate_percent=>null, cascade=>true, method_opt=> 'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

SQL> SELECT index_name, pct_direct_access, iot_redundant_pkey_elim  FROM dba_indexes WHERE index_name = 'ALBUM_SALES_IOT_TOTAL_SALES_I';

INDEX_NAME                     PCT_DIRECT_ACCESS IOT
------------------------------ ----------------- ---
ALBUM_SALES_IOT_TOTAL_SALES_I                100 NO

We notice that the PCT_DIRECT_ACCESS value remains unchanged. So, no 50-50 block split, no PCT_DIRECT_ACCESS degradation with regard the secondary indexes.

OK, another nice feature with IOT Secondary Indexes.

With a “normal” Heap table, if we were to MOVE and reorganise the table, all associated indexes become invalid as the Move results in all the rows being relocated and the indexes are not maintained during this process (as this would add considerably to the overhead in the Move process). All associated indexes have to be rebuilt after the Move operation completes, which is both expensive and adds considerably to the availability issues associated with the whole table reorg process as the table is locked during the Move operation. In short, moving a heap table is an expensive and an availability unfriendly process.

As this little demo illustrates, moving a heap table results in all indexes becoming unusable:

SQL> create table radiohead (id number constraint radiohead_pk primary key, code number, name varchar2(30));

Table created.

SQL> create index code_i on radiohead(code);

Index created.

SQL> insert into radiohead select rownum, rownum, 'OK COMPUTER' from dual connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> select index_name, status from dba_indexes where table_name = 'RADIOHEAD';

INDEX_NAME                     STATUS
------------------------------ --------
RADIOHEAD_PK                   VALID
CODE_I                         VALID

SQL> alter table radiohead move;

Table altered.

SQL> select index_name, status from dba_indexes where table_name = 'RADIOHEAD';

INDEX_NAME                     STATUS
------------------------------ --------
RADIOHEAD_PK                   UNUSABLE
CODE_I                         UNUSABLE

However, moving an IOT has a number of advantages over a heap table.

Firstly, as it’s an index structure, it can be reorganised and rebuilt in much the same way as we can rebuild any btree index. Remembering, an index can be rebuilt “online” (on Enterprise Edition), overcoming many of the locking issues associated with moving heap tables.

Additionally, although the physical locations of all the rows in the IOT change following a Move operation, the PK values  themselves don’t change. Therefore, although the PCT_DIRECT_ACCESS value becomes 0, the indexes themselves are still Valid and usable as the PK component can still be used to access the relevant data.

So the syntax to move an IOT table can be expanded to be performed “Online” and all the secondary indexes will remain “Valid”:

SQL> select index_name, status, PCT_DIRECT_ACCESS from dba_indexes where table_name = 'ALBUM_SALES_IOT';

INDEX_NAME                     STATUS   PCT_DIRECT_ACCESS
------------------------------ -------- -----------------
ALBUM_SALES_IOT_PK             VALID                    0
ALBUM_SALES_IOT_TOTAL_SALES_I  VALID                  100
ALBUM_SALES_IOT_COUNTRY_ID_I   VALID                  100

SQL> alter table album_sales_iot move online;

Table altered.

SQL> select index_name, status, PCT_DIRECT_ACCESS from dba_indexes where table_name = 'ALBUM_SALES_IOT';

INDEX_NAME                     STATUS   PCT_DIRECT_ACCESS
------------------------------ -------- -----------------
ALBUM_SALES_IOT_PK             VALID                    0
ALBUM_SALES_IOT_TOTAL_SALES_I  VALID                    0
ALBUM_SALES_IOT_COUNTRY_ID_I   VALID                    0

So although the PCT_DIRECT_ACCESS values for the secondary indexes has gone down to 0, making them less efficient as a result, they do at least remain valid and usable by the CBO:

SQL> select * from album_sales_iot where total_sales between 424242 and 424343;

26 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1433198708

---------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                               |    33 |   858 |    68   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| ALBUM_SALES_IOT_PK            |    33 |   858 |    68   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| ALBUM_SALES_IOT_TOTAL_SALES_I |    33 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TOTAL_SALES">=424242 AND "TOTAL_SALES"<=424343)
   2 - access("TOTAL_SALES">=424242 AND "TOTAL_SALES"<=424343)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         83  consistent gets
         53  physical reads
          0  redo size
       1655  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         26  rows processed

The secondary index is still used by the CBO, although at 83 consistent gets in this example, it’s not as efficient as it could be.

The rebuild of the secondary index can be performed subsequently to repair the stale guesses and improve the efficiency of the index as desired:

SQL> alter index album_sales_iot_total_sales_i rebuild online;

Index altered.

SQL> select index_name, status, PCT_DIRECT_ACCESS from dba_indexes where table_name = 'ALBUM_SALES_IOT';

INDEX_NAME                     STATUS   PCT_DIRECT_ACCESS
------------------------------ -------- -----------------
ALBUM_SALES_IOT_PK             VALID                    0
ALBUM_SALES_IOT_TOTAL_SALES_I  VALID                  100
ALBUM_SALES_IOT_COUNTRY_ID_I   VALID                    0
SQL> select * from album_sales_iot where total_sales between 424242 and 424343;

26 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1433198708

---------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                               |    33 |   858 |    36   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| ALBUM_SALES_IOT_PK            |    33 |   858 |    36   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| ALBUM_SALES_IOT_TOTAL_SALES_I |    33 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TOTAL_SALES">=424242 AND "TOTAL_SALES"<=424343)
   2 - access("TOTAL_SALES">=424242 AND "TOTAL_SALES"<=424343)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
       1655  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         26  rows processed

Following the rebuild of the secondary index and getting the PCT_DIRECT_ACCESS back to 100%, the example query is now more efficient, with a reduction of consistent gets down from 83 to just 31.

So IOTs can be less problematic to reorganise and if 90-10 block splits are performed, the impact on the secondary indexes is minimised.

Larger Block Tablespace For Indexes Revisited Part III (Prove Yourself) March 2, 2009

Posted by Richard Foote in Index Block Size, Index Height, Index Internals, Index Rebuild, Oracle Indexes, Oracle Myths.
10 comments

Time to look a little at an Index Fast Full Scan (IFFS) with respect to moving indexes into a larger block tablespace.

To start, a few points about an IFFS. When performing an IFFS, Oracle will read each and every block in the index structure, including all branch and leaf blocks (up to the HWM), using multi-block read operations. So although an IFFS has its place, it’s a relatively expensive operation, especially if the index is large, as it needs to read all blocks in the current index structure. Therefore, it’s not generally considered a “common” operation, as in traditional index range scans, especially in OLTP environments. Effectively, an IFFS is the index equivalent of a Full Table Scan (FTS) and is performed in a similar fashion. During an IFFS, Oracle basically treats the index as if it were an smaller version of the table from which it can extract the necessary data.

So if an IFFS were indeed to be very common and could be performed so much more effectively in a larger block tablespace, this begs the question why not move tables in a larger block tablespace as well, so FTS can have the same benefits as the index. And if you move both tables and indexes into a larger block tablespace, why not create the database in the larger block and have done with it? Which brings us around to the question of selecting an appropriate block size for the database, which is a different discussion to the so-called benefits of just moving indexes into a larger block tablespace. For another day perhaps …

Now, although Robin’s demo clearly shows consistent gets are basically halved when an index is rebuilt with double the block size (which all sounds very impressive), what it doesn’t highlight however is that during an IFFS, approximately the same amount of data is still actually being read. Although there are certainly some efficiencies in having fewer logical reads, if each consistent gets is more expensive while the overall data being read is similar, you’re simply not going to get an enormous performance boost that simply halving consistent gets might suggest. Claims that things will suddenly run twice as fast (or 120 times as fast) purely by moving indexes into a larger block tablespace are shall we say “exaggerated” to say the least. However, as all index blocks within an index are being read with no subsequent table accesses, an IFFS is going to be as good as it gets when we talk about any possible performance improvements of moving indexes into a larger block tablespace.

Again, it’s all very simple to “give it a go” and see for yourself exactly what improvements one might expect. Move an index into a larger block tablespace and see those IFFS fly. Using exactly the same setup from Robin’s demo as in Parts I and II, let’s see the actual differences …

First, get some current session stats:

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);

NAME                             VALUE
---------------------------- ---------
CPU used by this session         35856
consistent gets               10685006
physical reads                    2582
index fast full scans (full)     33003

Now execute 1000 IFFS using the index in an 8K block tablespace …
SQL> declare
  2  v_count number;
  3  begin
  4    for i in 1..1000 loop
  5       select /*+ index_ffs(bowie) */ count(*) into v_count from bowie;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.23

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);

NAME                             VALUE
---------------------------- ---------
CPU used by this session         36952
consistent gets               11106006
physical reads                    2582
index fast full scans (full)     34003

 
Note that response times are roughly 11.23 secs, mostly consisting of CPU at 10.96 secs. Note also consistent gets are 1000 x 421 as one would expect from Robin’s demo. Now lets see how things differ when we move the index into a 16K block tablespace.

 
SQL> alter index bowie_idx rebuild tablespace ts_16k;

Index altered.

 
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);

NAME                             VALUE
---------------------------- ---------
CPU used by this session         43192
consistent gets               12372513
physical reads                    2789
index fast full scans (full)     40003

SQL> declare
  2  v_count number;
  3  begin
  4    for i in 1..1000 loop
  5       select /*+ index_ffs(bowie) */ count(*) into v_count from bowie;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.14

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);

NAME                               VALUE
----------------------------- ----------
CPU used by this session           44260
consistent gets                 12583513
physical reads                      2789
index fast full scans (full)       41003

 
Note there’s only a slight improvement in response times and CPU usage of a couple of percentage points, even though the number of consistent gets has halved. Now your milage may vary a tad but what you will not see is things suddenly using 1/2 the resources or running twice as fast (or running 120 times as fast). Because at the end of the day, both sets of IFFS are reading and processing approximately the same amount of data.

Note also that any such possible minor improvments in IFFS performance is likely going to be cancelled out or more likely overtaken by the increased costs associated with more typical index range scans due to the index not actually reducing in height as discussed in Part II. Again, what’s more common, smaller index range scans or IFFS ?

Robin’s demo and the demo above however assumes the index is fully cached, with no physical I/O (PIO) operations being performed. Now as an IFFS needs to read the entire index, it’s quite likely that an IFFS would require some physical I/O, especially if the index is large. Surely then, if an index is in a larger block tablespace, such physical I/Os would be more efficient because Oracle would be able to read more data with a multi-block read based on a larger block  size?

The answer is unfortunately no.  Another (rather poor) analogy to get the point across.

You go to the bank to take some money out. You’re only allowed to take out a maximum of $1000 per day from your account. You ask for this in $50 notes and you get 20 x $50 = $1000 in total. Your mate wants to take out more money and asks for the money in $100 notes. However, as the limit is set to $1000 a day, regardless of the denomination of the bank notes, he simply gets 10 x $100 = $1000. You both end up with exactly the same amount of money, it’s just that you have more bank notes.

An Oracle multi-block read works in exactly the same manner. There’s a maximum amount of data you can read which is calculated by the default block size x db_file_multiblock_read_count. If the default block size is 8K and the db_file_multiblock_read_count = 8, then the maximum size of a multiblock read is 8K x 8 = 64K.

If you attempt to perform a multi-block read using a non-default block size, Oracle simply divides the 64K by the default block size to determine how many blocks to read. So if you now attempt to perform a multi-block read from a 16K tablespace, Oracle will only read 16K x 4 blocks = 64K. The I/O size is the same regardless of the blocksize.

And this makes perfect sense. If you’ve tuned the size of a multi-block read perfectly with the default block size in mind, why would you want to (say) suddenly double this perfect size when you double the block size in another tablespace. If doubling a multi-block read size were to suddenly improved things, why not also double the multi-block read size for the default block size as well …

Again, it’s very easy and simple to test how changing the block size in a specific tablespace makes no difference to the behaviour of an associated multi-block read.

First flush the buffer cache and trace a session, while performing a multi-block read via an IFFS , first with a default 8K block size index:

SQL> show parameter db_file_m

NAME                             TYPE VALUE
----------------------------- ------- -----
db_file_multiblock_read_count integer     8

SQL> alter system flush buffer_cache;

System altered.

SQL> alter session set events ’10046 trace name context forever, level 12′;

Session altered.

SQL> select /*+ index_ffs(bowie) */ count(*) from bowie;
 
The trace file will show that blocks=8 are being read during the IFFS multiblock read operation as the following extract from the trace file highlights:

 
WAIT #2: nam=’db file scattered read’ ela= 22897 file#=7 block#=38929 blocks=8 obj#=95742 tim=1217521768059
WAIT #2: nam=’db file scattered read’ ela= 47158 file#=7 block#=38937 blocks=8 obj#=95742 tim=1217521816003
WAIT #2: nam=’db file scattered read’ ela= 37776 file#=7 block#=38945 blocks=8 obj#=95742 tim=1217521854530

However, when you trace a session as it performs the IFFS with a 16K block

WAIT #1: nam=’db file scattered read’ ela= 55181 file#=6 block#=1030 blocks=4obj#=95742 tim=867071895617
WAIT #1: nam=’db file scattered read’ ela= 68932 file#=6 block#=1034 blocks=4obj#=95742 tim=867071965238
WAIT #1: nam=’db file scattered read’ ela= 67136 file#=6 block#=1038 blocks=4obj#=95742 tim=867072142114
 

We notice that Oracle is now only reading 4 blocks at a time, ensuring that the same 64K is read each time. So increasing the blocksize of an index doesn’t suddenly impact the manner or efficiency in which multi-block PIOs are performed.

Next, we’ll perform a similar test as before, comparing the difference between the 8K and 16K block index during an IFFS but this time with PIOs introduced. There are various ways one could do this. Make the buffer cache too small to fit an index or make the index too big for the buffer cache. Keeping with Robin’s demo, I’ll use exactly the same index definitions but this time run the following procedure in another session that constantly flushes the buffer caches. So in one session:

SQL> begin
  2  for i in 1..10000 loop
  3    execute immediate (‘alter system flush buffer_cache’);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

 
Meanwhile in another session, run a number of IFFS (200 this time) with an 8K block size index:

 
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);

NAME                             VALUE
---------------------------- ---------
CPU used by this session          4851
consistent gets                1536024
physical reads                  423992
index fast full scans (full)      3205

 
SQL> declare
  2  v_count number;
  3  begin
  4    for i in 1..200 loop
  5     select /*+ index_ffs(bowie) */ count(*) into v_count from bowie;
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:35.92

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);

NAME                             VALUE
---------------------------- ---------
CPU used by this session          5248
consistent gets                1620224
physical reads                  507651
index fast full scans (full)      3405

 

Note the response time is 35.92 secs and that we used approximately 3.97 secs. This time we have performed a how bunch of PIO operations.

Same thing, this time with the 16K index:

 
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);

NAME                             VALUE
---------------------------- ---------
CPU used by this session          6695
consistent gets                1789531
physical reads                  633216
index fast full scans (full)      4205

 
SQL> declare
  2  v_count number;
  3  begin
  4    for i in 1..200 loop
  5     select /*+ index_ffs(bowie) */ count(*) into v_count from bowie;
  6   end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:36.59

 
SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 137 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’ or n.name = ‘index fast full scans (full)’);

NAME                             VALUE
---------------------------- ---------
CPU used by this session          7118
consistent gets                1831731
physical reads                  674861
index fast full scans (full)      4405

 
We now note that the 16K block index has the slightly slower response time at 36.59 secs and uses a tad more CPU at 4.23 secs. As soon as we introduce PIOs, the PIOs contribute significantly to the overall costs and may well result in the larger block index being more costly depending on how the PIO is performed. So even in Robin’s example, depending on the nature of PIOs, even an IFFS can potentially run slower with an index in a larger block tablespace. Remembering of course that larger indexes are less likely to be fully cached and are more likely to incur PIOs during an IFFS.

Here’s a demo I’ve used before that uses a different approach to highlight the same issue (which doesn’t rely on a different session “interfering” with things). Another excellent example is this one by Greg Rahn on this OTN thread where he shows an IFFS performing a tad slower in a larger block tablespace. As I’ve been saying, just give it a go and see for yourself.

Perhaps, simply halving the consistent gets when doubling the index block size doesn’t tell the whole story …

The next time you see someone reference Robin’s demo as some sort of “proof” that by moving indexes into a larger block tablespace and (perhaps) reducing the number of consistent reads, the index will somehow be flatter and/or more efficient, just remember that the index may not necessarily be flatter, it might actually have the same height and that subsequent index operations, including index range scans and IFFS could very well be more expensive, not less expensive, to perform.

The next time you see someone claim that by moving indexes into a larger block tablespace, performance has suddenly improved by 100% or that things suddenly run 120 times faster, just ask one simple question. Why ?Ask what else has changed, what else might be contributing to the incredible performance improvements, because when you actually test things out for yourself, you’ll noticed results are not anywhere near as “impressive”.

Larger Block Tablespace For Indexes Revisted: Part II (Money) February 23, 2009

Posted by Richard Foote in Index Block Size, Index Height, Index Rebuild, Oracle Indexes, Oracle Myths.
7 comments

In Part I I looked at Robin Schumacher’s “classic” example of the “so-called” benefits of rebuilding an index into a larger block tablespace. I started by highlighting that by simply rebuilding an index in a larger block tablespace and (say) halving the number of associated index blocks, it doesn’t necessarily mean the index will result in a “flatter structure” , that the index height will reduce. Robin’s demo is in fact a perfect example of this.

Let me start Part II by following this up with a little story …

“I went with a friend of mine to get some cash from the bank the other day in order to buy the latest David Bowie Box-Set. I got my cash in $50 notes but being an efficient, cost saving sort of bloke, my mate got out the same amount of cash in $100 notes. Although we both had the same amount of cash, his wallet was that little bit more compact and “efficient” than mine as he had less actual bank notes to carry.

However, when we got to the record store, we were surprised to discover that the actual “cost” of the David Bowie Box-Set was 2 bank notes of any domination, but with no change being given. Therefore, it cost me 2 x $50 notes to make my purchase. Unfortunately for my mate, it cost him 2 x $100 for the same thing as $100 notes were the smallest denomination he could use. Yes, I was a little bit mean not lending him some of my $50 notes but I was a little disappointed myself for not having any $5 notes on me at the time ;)

OK, it’s not a perfect analogy but you perhaps get the point …

If you have to pay with a quantity of bank notes and you only have larger bank notes, you end up paying more than you would if you could only have paid with the same number of smaller denomination bank notes.

If you have to pay for an index scan in database blocks and you’re forced to use larger index blocks, you actually end up paying more if you have to read the same number of index blocks anyways.

This is one of the potential dangers with rebuilding indexes in a larger block tablespace. And like I said, Robin’s little demo is a perfect example of this. You might indeed reduce and halve the number of index blocks but this might not be sufficient to actually flatten the index structure and reduce the actual height of the index. The height of the index after rebuilding the index can remain the same, so the minimum cost of performing a small range scan increases. Even if you reduce the index height, you can still end up paying more if the savings do not compensate you enough for the additional overhead associated with now having to read and process larger index blocks. 

Therefore, you potentially start paying more for smaller index range scans because you might not actually reduce the number of index blocks you visit for these types of index scans.

Taking exactly the same table/index definitions and data used to replicate Robin’s example in Part I, let’s see if there’s any difference in the costs associated with performing a number of small index range scans after rebuilding the index in a 16K block tablespace.

Again, it’s a simple case of just giving it a go and see for yourself. What resources are used if you perform a series of small index scans ? Do things really run faster ? Do we really use less resources ?  Having rebuilt such an index in a larger block tablespace and halved the number of associated leaf blocks, are we really better off ?

Let’s begin by setting up the same example as Robin’s demo as before …

SQL> create table bowie (id number not null, value varchar2(10));

Table created.

SQL> insert into bowie select rownum, ‘BOWIE’ from dual connect by level <=187200;

187200 rows created.

SQL> commit;

Commit complete.

SQL> create index bowie_idx on bowie(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’BOWIE’, cascade=>true, estimate_percent=>null, method_opt=> ‘FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> select index_name, blevel from user_indexes where index_name = ‘BOWIE_IDX’;

INDEX_NAME BLEVEL
---------- ------
BOWIE_IDX       1

SQL> analyze index bowie_idx validate structure;

Index analyzed.

SQL> select height, btree_space, used_space from index_stats;

HEIGHT BTREE_SPACE USED_SPACE
------ ----------- ----------
     2     3336032    2988168

 

Note the index height when built in an 8K block tablespace is 2 …

Let’s now capture the current amount of CPU used by the session:

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 134 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’);

NAME                          VALUE
------------------------ ----------
CPU used by this session      36555
consistent gets            22520369
physical reads                 3750

 

Now we run a series of small index range scans …

SQL> set timing on

SQL> declare
  2  v_id    number;
  3  v_value varchar2(10);
  4  begin
  5   for o in 1..10 loop
  6    for i in 1..187200 loop
  7    select id, value into v_id, v_value from bowie where id = i;
  8    end loop;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:28.42

 

Let’s see how our resource stats have changed …

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 134 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’);

NAME                          VALUE
------------------------ ----------
CPU used by this session      45346
consistent gets            28140519
physical reads                 3750

 

We note we have used approximately 87.91 CPU seconds. (Note: You can run this a number of times and determine an average figure).

Let’s now rebuild the index again in a 16K block tablespace:

SQL> alter index bowie_idx rebuild tablespace ts_16k;

Index altered.

SQL> select index_name, blevel from user_indexes where index_name = ‘BOWIE_IDX’;

INDEX_NAME BLEVEL
---------- ------
BOWIE_IDX       1

SQL> analyze index bowie_idx validate structure;

Index analyzed.

SQL> select height, btree_space, used_space from index_stats;

    HEIGHT BTREE_SPACE USED_SPACE
---------- ----------- ----------
         2     3351776    2985662 

Note the index height remains at 2 …

If we run the same series of small index scans:

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 134 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’);

NAME                          VALUE
------------------------ ----------
CPU used by this session      45381
consistent gets            28142640
physical reads                 3957

SQL> declare
  2  v_id    number;
  3  v_value varchar2(10);
  4  begin
  5   for o in 1..10 loop
  6    for i in 1..187200 loop
  7    select id, value into v_id, v_value from bowie where id = i;
  8    end loop;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:42.44

SQL> select n.name, s.value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and s.sid = 134 and (n.name = ‘CPU used by this session’ or n.name = ‘consistent gets’ or n.name = ‘physical reads’);

NAME                          VALUE
------------------------ ----------
CPU used by this session      54484
consistent gets            33760690
physical reads                 3957

We note that elapsed times have increased and we have now increased our overall CPU consumption to 91.03 CPU seconds as well.

As we can see, there has been no advantage with rebuilding the in index in the 16K block tablespace for these smaller index scans. In fact, there’s actually been an increase in the overall elapsed times and an increase in the overall CPU. Performance has not improved but has in fact worsened overall for these queries after rebuilding the index in a larger block tablespace.

You begin to get the point …

And of course, indexes are generally far more typically to be used in small index range scan operations than they are in performing Index Fast Full Index Scans. Just compare the numbers of index fast full scans vs. index fetch by key operations in your databases if you want some indication. 

However, Robin’s specific example used a SQL statement that performed an Index Fast Full Scan. Surely, such operations would improve dramatically if indexes were only rebuilt in a larger block tablespace ? Surely such operations would be able to read data more quickly, especially if we have to go to disk, as we would be able to read more data with each associated multiblock read if the index were built in a larger block tablespace ? Surely it would be worth all the extra effort and management considerations ?

Things will run at least 2 times faster, maybe even 150 times faster, right  ;)

We’ll see how the results can be a tad “disappointing” in the next post …

Larger Block Tablespace For Indexes Revisited: Part I (The Tourist) February 18, 2009

Posted by Richard Foote in Index Block Size, Index Height, Oracle Indexes, Oracle Myths.
15 comments

I’ve previously discussed the various issues and myths relating to the so-called benefits of creating a separate, larger block tablespace for indexes and why it’s not recommended and generally a bad idea:

Store Indexes In A Larger Block Tablespace:  Some Thoughts (Big Brother)

Store Indexes In A Larger Block Tablespace: The Multiblock Read Myth (Karma Police)

Store Indexes In A Larger Block Tablespace: The Multiblock Read Myth Part II (The Fly)

Store Indexes In A Larger Block Tablespace: Height Reduction 1/2 Myth (Five Foot One)

Larger Block Tablespace and Small Index Scans – Performance Improvement ? (Let Down)

However, some myths have a habit of lingering ;)

A recent question on reverse indexes (which could so easily have been answered by the person asking the question if they had only just “given it a go”) had me thinking that so many of these myths and misconceptions can be easily challenged and unproven.

Perhaps the most repeated example I’ve seen where the “so-called” benefits of moving indexes into larger block size tablespace is misunderstood is this one by Robin Schumacher. He demonstrates how by moving an index from an 8K block size to a 16K block size, “the amount of logical reads has been reduced in half”, with consistent gets reducing from 421 to 211, during an Index Fast Full Scan operation.

Sounds impressive, but only if one doesn’t understand what the numbers actually represent and if one doesn’t understand what Oracle actually does under the covers. In actual fact, the reduction in consistent gets in this specific example is somewhat meaningless …

Some folks even go on to say that “When can we “prove” a benefit from an index rebuild?  Here, Robin Schumacher proves that an index that is rebuilt in a larger tablespace will contain more index entries be block, and have a flatter structure”.

So I thought I might demonstrate just how easy it is to “give it a go” and see for yourself whether or not these sorts of claims are actually true.

In Part I, I’m just going to focus on the specific claim that this example somehow proves indexes have a “flatter structure” when rebuilt in a larger block tablepsace. That by recreating an index in a larger block tablespace and halving the consistent gets from 421 to 211, the index will somehow have a “flatter structure” as result.

The key message I want to convey however is how easy it is to actually determine the accuracy of these sorts of claims yourself, simply by “giving it a go”. Trust but verify (or in some cases just verify). I’ll revisit some of the other misconceptions with these claims, such as why the reduction in consistent gets is not as impressive as it sounds, in later posts.

The first thing we need to do is reproduce the example and test the results for ourselves. Unfortunately we don’t have a script to reproduce the data used by Robin but we have enough clues at hand to reproduce the same demonstration. We need to basically create an index in an 8K block that performs 421 consistent gets during an Index Fast Full Scan when performing a count(*)SQL operation. So with a little bit of experimenting with different volumes of data, inserting 187,200 numbers into an index produced the necessary volume of data to replicate the scenario. Note: your mileage may vary slightly depending on database version, tablespace options, the specific query you use to test the results, etc.

SQL> create table bowie (id number not null, value varchar2(10));

Table created.

SQL> insert into bowie select rownum, ‘BOWIE’ from dual connect by level <=187200;

187200 rows created.

SQL> commit;

Commit complete.

SQL> create index bowie_idx on bowie(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’BOWIE’, cascade=>true, estimate_percent=>null, method_opt=> ‘FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

As in Robin’s example, running the following simple count(*) SQL statement a couple of times to cache the data produced the following results:

SQL> select /*+ index_ffs(bowie) */ count(*) from bowie;

Execution Plan
-------------------------------------------
Plan hash value: 1410776261
-------------------------------------------
| Id  | Operation             | Name      |
-------------------------------------------
|   0 | SELECT STATEMENT      |           |
|   1 |  SORT AGGREGATE       |           |
|   2 |   INDEX FAST FULL SCAN| BOWIE_IDX |
-------------------------------------------
Statistics
----------------------------------------------------------
  0  recursive calls
  0  db block gets
421  consistent gets
  0  physical reads
  0  redo size
412  bytes sent via SQL*Net to client
396  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

 

OK, so now we have an index that produces 421 consistent gets when performing an Index Fast Full Scan when performing a count(*) SQL operation.

Let’s now see look at the height and size of such an index …

SQL> select index_name, blevel from user_indexes where index_name = ‘BOWIE_IDX’;

INDEX_NAME BLEVEL
---------- ------
BOWIE_IDX       1

SQL> analyze index bowie_idx validate structure;

Index analyzed.

SQL> select height, btree_space, used_space from index_stats;

HEIGHT BTREE_SPACE USED_SPACE
------ ----------- ----------
     2     3336032    2988168

OK, so the index has a height of 2 (or a blevel of 1).

So would rebuilding such an index in a 16K block tablespace really give the index a “flatter structure” ? Will it really reduce the height of the index ? Well, let’s give it a go and see …

SQL> alter index bowie_idx rebuild tablespace ts_16k;

Index altered.

Let’s ensure our test case matches the one used by Robin and see if the number of consistent gets drops as expected by running the same select count(*) statement a number of times:

SQL> select /*+ index_ffs(bowie) */ count(*) from bowie;

Execution Plan
----------------------------------------------------------
Plan hash value: 1410776261
-------------------------------
| Id  | Operation             |
-------------------------------
|   0 | SELECT STATEMENT      |
|   1 |  SORT AGGREGATE       |
|   2 |   INDEX FAST FULL SCAN|
-------------------------------
Statistics
----------------------------------------------------------
  0  recursive calls
  0  db block gets
211  consistent gets
  0  physical reads
  0  redo size
412  bytes sent via SQL*Net to client
396  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

Indeed it does, consistent gets have indeed reduced from 421 down to 211, exactly as in Robin’s example. Exciting stuff  !! Well, not really, I’ll demonstrate later why these numbers don’t actually mean as much as they appear…

Let’s see if indeed the index does have a “flatter structure” …

SQL> select index_name, blevel from user_indexes where index_name = ‘BOWIE_IDX’;

INDEX_NAME BLEVEL
---------- ------
BOWIE_IDX       1

SQL> select height, btree_space, used_space from index_stats;

     HEIGHT BTREE_SPACE USED_SPACE
---------- ----------- ----------
         2     3351776    2985662

No !!

The index in the 16K block tablespace does not have a flatter structure. In fact the index has exactly the same height of 2 as it did previously and practically the same amount of index space.

As you can see, it’s very easy to give it a go, to test and validate these types of claims yourself. So no, even Robin’s “infamous” test case does not in fact “prove” indexes will have a “flatter structure” if rebuilt in a larger block tablespace. This is the first of the key points I want to get across. Just because you rebuild an index in a larger block tablespace, it doesn’t necessarily mean the index height will reduce or that the resultant index will have a flatter structure. In many many cases, depending on the size of the index and the increase in block size, the height of an index will not reduce at all. I’ve explained why this is the case in some detail in this previous post.

In fact, in some ways, the index height has now INCREASED, not decreased as a result of moving the index to a larger block size. The index had a “flatter structure” when it was in the 8K block tablespace than it does after it was rebuilt in the 16K block tablespace. Robin’s demo is actually a perfect example of this !!

Why ?

Well previously, we had an index structure that had a height of 2 with each “level” being 8K. Now we have an index structure that also has a height of 2 but each level now consists of 16K index blocks. Previously to perform an Index range scan we had to read at least 2 x 8K index blocks or 16K in total. Now we have to read at least 2 x 16K or 32K in total when performing an index range scan.

We’ve just rebuilt the index with larger sized blocks. Imagine a new building that has the same number of floors as the older building but each floor is now double the “size” (height) than it was previously. Although the new building is still a 2 storey building, the actual physical height of the building has just doubled … 

And guess what ? For many many common queries and processes, there’s now potentially an additional overhead associated with having to always read in an index block that is double the size.

To be discussed next …

Larger Block Index Tablespace and Small Index Scans – Performance Improvement ? (Let Down) March 31, 2008

Posted by Richard Foote in Index Block Size, Oracle General, Oracle Indexes, Oracle Myths, Performance Tuning, Tablespace Management.
10 comments

Thought it might be worth looking at the impact on the performance of Unique and small index range scans as typical in OLTP environments, when an index is rebuilt in a larger block tablespace.

Warning, this discussion will again primarily be an exercise in simple mathematics. However, a few little details to start the ball rolling.

I’ve already discussed how in many scenarios, by increasing the index block size, the height of the index can remain unchanged. One can’t simply assume a larger block index results in an index with a lesser height. However, as we shall see, even when indexes do reduce in height, the so-called performance benefits can be somewhat “exaggerated”.

Note also in OLTP environments, the vast majority of index related access paths are either unique scans or small index scans. We’re only interested in a specific customer, in a specific order, in the bank balances of specific accounts, with specific customers buying specific products with specific credit cards, etc. Remember also that say a block size of 8K can generally store many hundreds of index entries per index leaf block and that even a humble little 2K leaf block can often store a three figure number of index entries …

So let’s say in our first example we currently have an 8K index with a height of 3 which we rebuild in a 16K tablespace and the index height remains the same. What are the comparative costs of performing a simple index look-up.

In the 8K index, we need to read 3 x 8K (root, branch and leaf block) to read the index entry of interest for a total of 24K. However, in the new 16K index, we now need to read 3 x 16K (again, a root, branch and index leaf block) to read the index entry of interest, for a total of 48K.

That’s double the potential physical I/O if the blocks are not currently cached, that’s double the memory that needs to be used in the buffer cache to store the index blocks and that’s potentially more CPU we need to use in order to pin and process the larger index blocks.

Hummm, these larger blocks aren’t too impressive so far …

But what if we actually reduce the height after moving an index to a larger block tablespace ?

In the 8K index, we still have our 3 x 8K = 24K. If we rebuild in an 16K block tablespace and reduce it’s height by 1, we now only need to read 2 x 16K = 32K. That’s 3 consistent reads at 24K vs. 2 consistent reads at 32K. Yes, we reduce the number of consistent reads which is a good thing but we still have a larger index footprint which again means possibly more data off disk, more memory and more CPU resources to process the index blocks.

Let’s hope our 2K block index with a height of 2 reduces its height somewhat if we decide to rebuild it in 32K blocks because that’s 2 x 2K = 4K vs 2 x 32K = 64K otherwise. But even if we did, although we reduce the consistent reads, which is a good thing, that’s still 2 x 2K = 4K vs 1 x 32K = 32K.

But we can potentially reduce the height of a 2K index if rebuild in a much larger block size (in specific cases) by more than 1 level, right ?

Yes, a 4 level 2K index, 4 x 2K = 8K, might very well now only require 2 levels in a 32K index, 2 x 32K = 64K. But that’s still 64K of index data we need to access vs. 8K in the smaller block. Consistent reads reduce but our footprint for small index range scans is still larger, sometimes by a considerably amount depending on the change of block size.

You begin to see the issue …

It’s a bit like someone saying they’re going to improve the skyline somewhat and only build appartment buildings that have fewer numbers of floors than previously. But if the floors they build are 2 times or 4 times or even 16 times higher than the previous floors and they only reduce the number of floors by a moderate amount, is the building really lower ?

Have we really reduced the height of the building ?

Yes, the lift only has to stop at fewer floors which can be more “efficient” but it takes longer each time as it goes from one floor to the next. So is it really that much quicker to get to the top, or more specifically in our discussion, from the top to the bottom of the building ?

Surely though, things must run faster, performance must actually improve, things must be more efficient by having larger sized index blocks else why bother ? Why indeed …

This demo on the Performance Impact Of Small Index Scans In A Larger Index Block Size shows how performance may actually worsen, not improve if we move indexes into a larger block size. It creates an index on a well clustered column (so the index is at it’s most efficient and potentially impacts performance the most), first with an 8K block size and then with a 16K block size. The size of the index was carefully chosen so that the larger index block size did indeed reduce the height of the index (although the range of values when this was possible was actually quite limited with these block sizes). A simple PL/SQL procedure then performs a massive number of single row look-ups and the CPU and elapsed times are monitored. The results show when the associated blocks are either cached or not cached, the larger block index incurs larger CPU related costs and results in overall slower response times, despite the fact it actually has a lower height than the smaller block index.

It’s not precisely the same as a large scale environment as PL/SQL has subtle little differences and efficiencies when compared to multiple, separate transactions. Also, it’s not the specific results that are important here but the overall general approach. You can pick any index you want to  investigate, you can pick whatever block sizes you may be interested in, you can monitor and benchmark by checking out specific session details or by tracing the specific sessions, you can determine what the comparative costs and response times may be and you can determine what may cause any performance differences by digging deeper into the session statistics or trace files.

No matter what index or block size you select, you will likely come to the same conclusion. The overall performance benefits of your OLTP transactions when you move indexes to a larger block tablespace will likely be “disappointing”.

If a real estate developer comes knocking on your door with a promise that the empty block around the corner will have an apartment building with only a few floors and it shouldn’t ruin the view too much, you may just want to ask them quietly exactly how many floors they’re planning to build and exactly how high each floor will be.

Else the resulting view may turn out more disappointing than you’re led to believe ;)

Store Indexes In A Larger Block Tablespace: Height Reduction 1/2 Myth (Five Foot One) March 26, 2008

Posted by Richard Foote in Index Block Size, Index Height, Index Internals, Oracle General, Oracle Indexes, Oracle Myths, Tablespace Management.
14 comments

A common misconception with using a larger block tablespace specifically for indexes is that this will result in a reduction in the height of indexes and hence “flatten” index structures.

However, this is only partly true.

A few little generalisations to begin with.

First, most databases out there have a default block size of 8K. I won’t go into a big discussion on what the database block size should be set to (maybe another time), however I will say most databases these days have a default block size of 8K and that it’s certainly questionable to have the database block size set to 2K.

Note also in many common platforms (e.g. Windows, Linux) the maximum block size limit is 16K. So in many environments, when we talk about moving indexes into a bigger sized block, it specifically involves moving from an 8K to a 16K block size.

Yes, in theory you could move an index from (say) a 2K block size up as high as a 32K block size but you would need to question why the default is so low to begin with and whether the upper value is actually supported in your environment.

I make this point because the difference between block sizes makes a huge difference in the probability of the index height actually being reduced.

So let’s start with an example of moving indexes between an 8K default block size to a 16K block tablespace, not least because the arithmetic is easier and extrapolate out as we go along.

So in our example, the new block size is double or 2 times that of the default one. By doubling the block size, we effectively 1/2 the number of necessary leaf blocks in the index structure. By having fewer leaf blocks we also therefore reduce the overall associated block level overheads so the actual reduction in leaf blocks could be a tad more, but we’ll say a 1/2 reduction to keep the numbers nice and simple.

Note the reduction in leaf blocks in therefore simply 1 / the ratio of block increase (1/2). Moving from a 2K block to a 32K block is 16 times larger so we’ll have approximately 1/16 the number of leaf blocks.

So how does (say) halving the number of leaf blocks impact the overall height of the index ?

We obviously can’t reduce the height of an index with a height of just 1. The index consists of just the one block so a larger block would simply mean the block having more free space.

To reduce the height of an index with a height of 2 (back to 1), we therefore must be able to store all index entries within a single block. Therefore, in the 8K to 16K example, the index can only have 2 full leaf blocks for this to be possible. If an index has 3 or more “filled” leaf blocks, the index must remain at a height of 2 as we can’t fit all the index entries into the single larger index block.

Importantly therefore, all indexes with a height of 2 with more than 2 full leaf blocks would not reduce in height by simply doubling the block size. This could very well be the vast majority of indexes at this level.

For an index with a height of 2, the index must have less full leaf blocks than the ratio of block increase for a height reduction to be possible. In our best case scenario, 2K block to 32K block, any index with more than 16 full leaf blocks would not reduce in height.

To reduce the height of an index with a height of 3 (back to 2), we must therefore be able to store all intermediate branch blocks into the one branch (root) block. When we double the block size, we therefore 1/2 the leaf blocks and 1/2 again the necessary branch blocks. Therefore the necessary branch blocks is 1/(2×2) = 1/4 that of the default block size. Therefore any index with a height of 3 that has more than 4 full intermediate branch blocks will again not reduce in height as again all the necessary branch information would not fit in one root block.

Importantly therefore, all indexes with a height of 3 with more than 4 full intermediate branch blocks would not reduce in height by simply doubling the block size. Again, this could very well be a significant proportion of all indexes at this level. Note also in many databases, the vast majority of indexes have a height of 3 or less so by simply doubling the index block size, most indexes would not reduce in height …

For an index with a height of 3, the index must have less full intermediate branch blocks than the ratio of block increase to the power of 2 for a height reduction to be possible. In our best case scenario, the 2K block to 32K block, only those indexes with more than 16×16=256 full intermediate branch blocks will reduce in height. This is therefore likely to be a far higher proportion of all such indexes.

You see the pattern …

To reduce the height of an index with a height of 4 (back to 3), we must therefore store all first level intermediate branch blocks into the one branch (root) block. When we double the block size, we therefore 1/2 the leaf blocks, 1/2 again the second level intermediate branch blocks and 1/2 again the first level intermediate branch blocks. Therefore the necessary first level intermediate branch blocks is 1/(2x2x2) = 1/8 that of the default block size. Therefore any index with a height of 4 that has more than 8 full intermediate first level branch blocks would again not reduce in height as again all the necessary first level branch information would not fit in the one root block.

Importantly therefore, all indexes with a height of 4 with more than 8 full intermediate branch blocks will not reduce in height by simply doubling the block size. However, as the index height increases, the ratio of indexes where this is likely to be the case decreases.

For our best case scenario, 2K to 32K, we now start hitting very large numbers 16x16x16=4096 so the likelihood of a index height reduction is very very high.

And so on …

The important point being that by simply doubling the index block size, in most databases, the vast majority of indexes are actually quite unlikely to reduce in height as the index needs to be within very limited size boundaries for the index height to reduce. The greater the index height however, the greater the index size boundaries whereby an index height reduction is possible.

Also, the greater the index block increase, proportionally the fewer the index blocks and so greater the likelihood of an index height reduction.

This demo on the Impact Of Block Size On Index Height illustrates that by simply doubling the index block size, the height of an index (in various sizes) rarely decreases.

One final point. With our height 4 index example, note the index can only have a maximum of 8 first level branch blocks for the height to reduce. Therefore, in effect, we’re replacing a maximum of 9 x 8K branch blocks with 1 x 16K block. If this index is frequently accessed, these 9 branch blocks are likely cached and we only need to read two of these blocks anyways for an index range scan (for a total of 16K). After the rebuild, we still need to read this block (16K again) anyways so from a purely performance perspective with regard to just simply reducing the index height, the so-called performance benefits are often very much exaggerated.

As we’ll see in the next epic episode of this series, performance can actually decrease :(

Next time someone claims moving indexes into a larger block size will decrease the height and flatten an index, remember it really does depend. In many databases, especially when the index block size is just doubled, it’s actually quite surprising just how unlikely it is for an index to actually decrease in height.

Store Indexes In A Larger Block Tablespace: The Multiblock Read Myth Part II (The Fly) March 20, 2008

Posted by Richard Foote in Index Block Size, Index statistics, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Myths, Performance Tuning, Tablespace Management.
67 comments

Thought I might begin by mentioning a lovely little story by Billy Verreynne in this OTN Forum Thread.

Basically a scientist is doing research on the behaviour of flies. He notes when he opens the jar lid of a trapped fly and claps his hands, the fly takes off and flies away. One day, he decides to pull the wings off the fly. When he claps his hands, the fly just sits there. No matter how loud he claps, other than a slight rocking motion, the fly just doesn’t budge.

Excitedly, he writes in his journal his latest discovery. When you pull the wings off a fly, it goes stone deaf !!

This is soooo funny because this sort of thing happens all the time in the Oracle world (and elsewhere). I see it time after time after time, we’ve all done it.

Hey, I just compressed a segment into one extent and performance improved. Conclusion, storing a segment in one extent improves performance.

Hey, I just separated my indexes from my tables and performance improved. Conclusion, separating indexes from tables improves performance.

Hey, I just moved my indexes into a larger block sized tablespace and my Index Fast Full Scan performance improved. Conclusion, a larger index block size improves Index Fast Full Scan performance.

Lots of flies with no wings. Lots of people thinking flies go deaf when they don’t have wings. An action appears to cause an effect, but does it really …

As Billy himself suggests, the fundamental reason why so many people think “flies with no wings go deaf” is that many don’t understand what’s actually going on. Many simply don’t understand the basic workings, the fundamental processes and mechanisms involved in how Oracle functions or how Oracle performs a specific operation. Tuning by observation, tuning by making change “A” without understanding all the implications of such a change and subsequently making suppositions on the results of such a change, ultimately means we have lots of people thinking flies without wings are deaf.

As I discussed in Part 1, Oracle performs exactly the same sized I/O during a multiblock read, regardless of the block size of the segment. Exactly the same. Without understanding this simple fact, one could very easily come to a wrong conclusion regarding the ramification of block sizes on multiblock reads. Without understanding flies don’t have ears or sound sensors in their wings, one could very well come to a wrong conclusion regarding the ramifications of removing the wings from a fly.

If we perform an Index Fast Full Scan and performance improves, it can’t be because associated multiblock I/Os are more efficient. A fly doesn’t have sound sensors in its wings. There must be another explanation. Conversely, in my example in Part I of this discussion, performance went worse with a larger index block size (as it did in Greg Rahn’s example on this OTN Forum Thread), but again not as a result of multiblock read performance.

So how could the performance of an Index Fast Full Scan change (for better or worse), if one simply rebuilds the index within a larger block size tablespace ? Well there are of course many possible reasons, with the two more obvious explanations being the following:

1) Most randomly inserted indexes have a PCT_USED value ranging between 70-75% as these indexes perform random 50-50 block splits that are subsequently in differing stages of being filled. By rebuilding an index (say back to a default PCTFREE of 10%) one might increase index compactness by say 15% and hence decrease the overall index size (note reduced block overheads may also reduce the index a little as well, depending on index size and differences in block sizes). The Fast Full Index Scan is the access path that potentially benefits most by defragmenting an index as the associated costs are proportional to the overall size of the index. Reducing the size of an index could therefore impact subsequent performance. However, rebuilding the index in the current block size would likely achieve a similar result (plus block overheads), compacting the index and resulting in potentially better performance (although once the index blocks begin to split again, the index would eventually return back to its previous state). Therefore, it’s not the bigger block size but the resultant defragmentation of the index that’s improved matters. The fly isn’t deaf, it just needs its wings to fly …

2) By storing an index in a larger block tablespace, the index must physically be stored on a different database file. This file could be on a faster disk, improving performance, this file could be on a faster part of the disk, improving performance, this file could be on a disk with far less disk contention, improving performance, etc. etc. It’s not the larger block size that’s improved (or worsened) performance, it’s the new physical characteristics of where the index is now stored. If one were to rebuild the index with the current block size and use the same physical characteristics of the larger block index, subsequent performance would likewise increase (or decrease). The fly isn’t deaf, it just needs its wings to fly …

There are many other possible reasons, the system was less busy when using the larger block index, more of the index was physically cached when using the larger block index, etc. etc.

Of course, an Index Fast Full Scan is rarely a scalability issue anyways. Do we really want our applications to perform hundreds of large, concurrent Index Fast Full Scans ? Tuning the application to avoid these overheads should be the focus rather than moving indexes into a larger block tablespace in the vain hope it will improve things dramatically. But that’s the topic of another discussion …

Can the performance of an Index Fast Full scan change after moving the index to a larger block size tablespace. Absolutely. However, it doesn’t necessarily mean such a change in performance is a direct result of the index having a larger block size and that multiblock read performance has improved.

It doesn’t mean moving indexes to larger block size tablespaces suddenly makes Oracle go deaf …

Bzzzzzzzzzzzzzzzzzz

UPDATE: I’ve added this simple little demo that illustrates how performance improves when an index is rebuilt in a larger block tablespace. This will of course suggest to some folk that the larger block tablespace improved the performance but what actually improved things was rebuilding the fragmented index to be a more efficient structure. The larger block tablespace was not the fix, rebuilding the index was the important factor. In fact, by rebuilding the index in the original smaller block tablespace, not only do we also improve performance, but things are further improved as we reduce CPU overheads incurred by the larger block tablespace and as a result elapsed times are further improved.

Store Indexes In A Larger Block Tablespace: The Multiblock Read Myth (Karma Police) March 18, 2008

Posted by Richard Foote in Index Block Size, Index statistics, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Myths, Performance Tuning, Tablespace Management.
8 comments

One of the great myths surrounding the use of differing block sizes is that storing indexes in larger block sizes somehow dramatically improves the performance of index related multiblock reads.

Oracle performs index multiblock reads when performing an Index Fast Full Scan, when it basically treats the index structure as a skinny version of the table. It reads the entire index structure, multiple blocks at a time, “throwing away” any non leaf blocks as it stumbles across them.

The theory goes that by storing indexes in larger size blocks, we would obviously have fewer index related blocks. If we need to read the entire index, as we do with an Index Fast Full Scan, surely it must be more efficient if we have fewer, larger index blocks.

The evidence looks convincing. Here’s a link to an extract from a book by Robin Schumacher where he clearly shows a dramatic “improvement” by using an index tablespace with double the block size. In one query using an 8K block index, the consistent reads during an Index Fast Full Scan was 421. However, when the same index was recreated as a 16K block index, the same query only used 211 consistent gets, 1/2 of what it was previously.

Conclusive “proof” that the 16K block index improved performance wouldn’t you say ?

Well actually, it’s only conclusive proof that the number of consistent gets has dropped, whether it actually improves “performance” is another thing entirely.

There are a couple of little “details” that many don’t quite appreciate. The devil is always in the details …

The first point to note is that when Oracle performs a multiblock read, it uses the value in the db_file_multiblock_read_count parameter to determine how many blocks to read per multiblock read (with system statistics, Oracle itself can determine how best to set this value).

So if the db_file_multiblock_read_count value were set to say 16, Oracle will attempt to read as many as 16 blocks at a time during a multiblock read operation.

Note this value is based on the default block size of the database. So if the default block size is 8K and the db_file_multiblock_read_count is 16, Oracle will try and read 16 x 8K blocks at a time during a multiblock read operation.

However, if there’s a non-default block sized segment (say 16K), Oracle will adjust the number of blocks that are actually read during a multiblock read operation so that the maximum size of the overall multiblock read is identical to that of the default block size.

So if the db_file_multiblock_read_count is 16 and the default block size is 8K, a multiblock read of an object in a 16K tablespace will only read 8 blocks at a time (and not 16). A multiblock read of an object in a 2K tablespace will read 64 blocks at a time.

The actual size of a multiblock read therefore is identical regardless of the block size of an object within a database.

An easy way to highlight this is to simply trace a session and see the specific size of corresponding multiblock read operations.

A sample from a trace on an 8K block index (with the db_file_multiblock_read_count set to 16), performing an Index Fast Full Scan looks like this:

WAIT #1: nam=’db file scattered read’ ela= 1487 file#=8 block#=1050 blocks=16 obj#=78294 tim=615409554677
WAIT #1: nam=’db file scattered read’ ela= 1377 file#=8 block#=1066 blocks=16 obj#=78294 tim=615409557777
WAIT #1: nam=’db file scattered read’ ela= 1143 file#=8 block#=1082 blocks=16 obj#=78294 tim=615409561563

Note that Oracle is reading 16 x 8K blocks (128K) per multiblock read operation.

However, when the index is recreated in a 16K block size tablespace, the Fast Full Index Scan looks like this:

WAIT #1: nam=’db file scattered read’ ela= 1413 file#=6 block#=14 blocks=8 obj#=78296 tim=626802128684
WAIT #1: nam=’db file scattered read’ ela= 1447 file#=6 block#=22 blocks=8 obj#=78296 tim=626802131649
WAIT #1: nam=’db file scattered read’ ela= 2014 file#=6 block#=30 blocks=8 obj#=78296 tim=626802135222

Note that Oracle is now only reading 8 x 16K blocks (128K) per multiblock operation.

Both indexes are effectively doing exactly the same work, both are effectively reading up to 128K of data per multiblock read …

It’s like paying someone $50 per 1/2 hour of work and then deciding to make things more “efficient” by paying them $100 per hour of work instead. In the end, you’re still just paying them $800 for an 8 hour day’s work regardless …

Note a larger block size will have less associated block overheads with there being less actual blocks so the overall size of an index may reduce a little, depending on index size and differences in block sizes. Therefore any possible improvements will only be restricted to the potential savings in the overall index size. With many databases having default block sizes of 8k and a maximum block size restricted to 16k, these savings may be minimum or non-existent.

This demo on the impact of different block sizes on multiblock read operations shows how Oracle actually performs the same sized reads when performing multiblock reads from differing block sized tablespaces, with the performance of the index in the larger block size tablespace being somewhat worse in this specific example.

With Oracle effectively performing identical work behind the scenes, the performance between different block size tablespaces is likely to be similar. You’re still paying $800 a day regardless …

Although it’s often claimed that multiblock reads is one of the key areas where larger index block sizes are beneficial, a claim based generally on the simplistic fact the number of consistent reads is reduced, the reality of the situation is somewhat different …

Store Indexes In a Larger Block Tablespace: Some Thoughts (Big Brother) March 16, 2008

Posted by Richard Foote in Index Block Size, Oracle Cost Based Optimizer, Oracle General, Oracle Indexes, Oracle Myths, Performance Tuning, Richard's Musings, Tablespace Management.
17 comments

A suggestion that seems to pop up on a routine basis on various forums and discussion boards is that we should be storing our Indexes in a larger block-size tablespace. For example, if our database block size is set to 8K, we should be creating separate (say) 16K block tablespaces specifically for our indexes. Doing so will improve performance as the index will have a flatter, more efficient structure. Multiblock reads will also be more efficient (or so the theory goes) as we would be reading fewer index blocks during such scans.

Oracle introduced the concept of having different tablespaces in a database with different block sizes back in 9i Release 1 in order to make transportable tablespaces between databases with differing block sizes possible. However, there’s nothing preventing one creating a new tablespace with a non-default block size and assigning objects to these tablespaces.

In principle, storing indexes (in particular) in a larger block size sounds like a really good idea doesn’t it ?

I’ll be discussing the pros and cons of this approach in future postings but just some initial thoughts to get everyone thinking about it:

  • All tablespaces with a non-default block size requires a separate, non-default block size buffer cache to be manually configured
  • Non-Default buffer caches are not automatically sized as part of Oracle’s automatic memory management and must be manually tuned and sized, potentially increasing administrative overheads
  • Non-Default buffer caches do not have an associated KEEP or RECYCLED pool and so all objects with the same non-default block size must reside in the same buffer cache
  • The possibility of unnecessarily caching blocks from an infrequently accessed object and wasting memory is therefore likely to increase
  • The possibility of unnecessarily aging out blocks from a more frequently accessed object is also likely to increase, thus increasing I/O related overheads
  • Although the height of an index may reduce if stored in a larger block size, in many cases it may not actually change at all
  • In those cases when the height of an index is actually reduced, the actual performance benefit of such a height reduction is often overstated
  • The reduction of index leaf blocks (a much more telling possible advantage) is only beneficial to very specific types of queries
  • Larger blocks often have the disadvantage of greater contention, which can lead to performance related issues
  • Indexes with larger block sizes have a significantly greater I/O and memory related footprint in relation to most OLTP related index scans
  • Index related multiblock reads on larger block sized segments actually have no real benefit when compared to multiblock reads on smaller block sized segments 
  • Most databases out there in the “real world” only use default block size tablespaces so the risks associated with finding bugs, CBO anomalies, etc. increase once non-default block sizes are introduced

Although in specific scenarios with specific applications, there may be some potential benefits of using non-default blocks sizes, in general, the disadvantages of using non-default block sizes usually out weigh these potential benefits.

As we shall see …

Follow

Get every new post delivered to your Inbox.

Join 1,703 other followers