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

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

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

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

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

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.

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  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  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 …

## Updates and Indexes Part II (Down Is The New Up)February 9, 2009

Posted by Richard Foote in Index Delete Operations, Oracle Myths, Update Indexes.

In Updates and Indexes Part I, I described how there’s no such things as an “update” operation as such on a index and that an update is effectively a delete followed by an insert operation.

I also showed how Oracle only marks index entries as deleted and doesn’t physically delete the index entry at the time of the transaction.

This leads some folk into (incorrectly) thinking indexes that experience lots of update (or delete) operations need to be frequently rebuilt as the delete space might accumulate and waste space within the index structure over time .

However I also showed how deleted space is actually generally automatically reused by Oracle. For example, all it takes is one subsequent tiny little insert operation into a leaf block for all deleted entries within the leaf block to be automatically cleaned out by Oracle.

Therefore just because an index experiences lots of update activity doesn’t necessarily mean the index needs to be rebuilt. Generally, all deleted space is reused and is effectively nothing but free and available space within the index.

To emphasise this key point and attempt to really get the message across, I thought it might be worth going through a little demo of an index that does indeed experience lots of update operations and see what impact it actually has on the index.

Let’s first just create a simple little table that will have two numeric columns:

SQL> create table bowie (id number, value number);

Table created.

Now let’s populate the first column with a monotonically increasing unique identifier and the second column with a random number:

SQL> insert into bowie select rownum, ceil(dbms_random.value(0,50000))
from dual connect by level <=50000;

50000 rows created.

SQL> commit;

Commit complete.

Now we’ll create an index on the value column that is populated with a bunch of random numbers:

SQL> create index bowie_idx on bowie(value);

Index created.

Let’s collect some statistics and see how much space is being used by this “freshly” created index:

SQL> analyze index bowie_idx validate structure;

Index analyzed.

SQL> select blocks, lf_blks, btree_space, pct_used, del_lf_rows from index_stats;

```BLOCKS LF_BLKS BTREE_S PCT_USED DEL_LF_ROWS
------ ------- ------- -------- -----------
128     110  888032       90           0```

Specifically note the number of leaf blocks and the overall btree space usage of the index. Also note that as the index has only just been created, we currently have no deleted index entries.

OK, next we’re going to update the index column with a new random number, one row at a time, for effectively 50% of all rows in the entire table. The table currently has 50,000 rows and we will update every other row (that’s 25,000 rows for those mathematically challenged) with a new random value. Now that will be effectively 25,000 separate delete operations and 25,000 separate insert operations.

There are some folk who would think such an exercise would result in 25,000 deleted index entries which need to be cleaned up at some point via an index rebuild.

There are some folk who would think updating 50% of all index column values would result in so much wasted space that the index would grow in an inefficient manner in order to store all these deleted index entries, likely causing performance issues.

There are some folk who think that such a regular and high proportion of updates on an index column in a table is a clear indication that such an index should be rebuilt on a regular basis.

Well, let’s see what happens. First, let’s update every other row in the entire table with a new random number for the value column:

SQL> begin
2  for i in 1..25000 loop
3    update bowie set value = ceil(dbms_random.value(0,50000)) where id = i*2;
4    commit;
5  end loop;
6  end;
7  /

PL/SQL procedure successfully completed.

Let’s see how badly the index has been impacted:

SQL> analyze index bowie_idx validate structure;

Index analyzed.

SQL> select blocks, lf_blks, btree_space, pct_used, del_lf_rows from index_stats;

```BLOCKS LF_BLKS BTREE_S PCT_USED DEL_LF_ROWS
------ ------- ------- -------- -----------
128     110  888032       90          60```

How interest !!

The first thing to note is that there aren’t actually 25,000 deleted index entries at all, even though we’ve just updated 25,000 index entries. There are just 60 deleted entries currently in the index. Just 60, that’s it !!

Why ?

Because the index is effectively just a random based index and while we may update (and hence effectively delete) an index entry from a specific leaf block, at some later time we’re likely to insert another index entry into this same leaf block, thereby cleaning out any deleted entries it may contain. Effectively, almost all the deleted index entries are being automatically cleaned out by subsequent random inserts throughout the index structure.

The relative handful of currently marked deleted index entries (60) happen to exist in leaf blocks that have not had a subsequent insert since the last delete operation in the specific leaf block. But even these deleted entries will eventually be cleaned out and the space reused by any other subsequent inserts in the specific leaf blocks.

The deleted space is simply not an issue, the vast majority of it has been cleaned out and reused and those entries that haven’t yet been cleaned out will likely be reused by subsequent insert operations anyways.

If we look at the actual space used by the index, after 50% of all index entries have been updated and we note that the index has not changed at all. It has exactly the same number of leaf blocks and is using exactly the same amount of btree space. (Note: because the index values are random, it’s likely that the new values will not be exactly distributed as it was previously and there might be the possibility that the odd index leaf block could fill and split as it contains more associated values than previously. Regardless, as the index grows with more index entries, this is not going to be an issue anyways).

There is nothing “wrong” or inefficient or fragmented with this index, even though we’ve just updated (and hence deleted) 50% of all its index entries.

With deleted index entries being trivial and likely to be reused at some later time anyways and with the index having the same leaf blocks and btree space as it did when the index was newly created, rebuilding such an index would be a total and utter waste of time and resources.

Again, just because an index column is frequently updated, it doesn’t necessarily mean the index is a candidate for a periodic index rebuilds. Any such suggestions are simply misguided …

## Sunshine Coast Queensland (Good Day Sunshine)February 5, 2009

Posted by Richard Foote in Travel.

Just recently returned from two glorious, relaxing, sun soaked weeks in the paradise that is Queensland’s Sunshine Coast.

We often go to the Gold Coast but decided this year to stay at Mooloolaba instead, about an hour’s drive north of Brisbane on the quieter Sunshine Coast. As you can see from the photo, the views from the hotel were fantastic.

Two weeks with no work, no Oracle, no laptop, no phone, no cooking and no TV (except the Australian Open tennis).

Two weeks of swimming and sun-baking by the pool, body surfing and walks along the beach (where the water temperature as a very nice 26 C), fishing, cocktails, eating out at fantastic restaurants each evening, cold beers on the balcony, reading (Roger Moore’s biography “My Word Is My Bond” and Peter F. Hamilton’s “The Naked God“) exploring Steve Erwin’s Australia Zoo and the famous Glasshouse Mountains, more cold beers on the balcony and of course sleeping in each morning !!

OK, so it may not have been the biggest of fish :)

We were very lucky with the weather, with lots of sun (of course), only the occasional afternoon storm and with mild temperatures in the late 20s, early 30s Celsius while back home and in much of Australia, temperatures were a somewhat uncomfortable 40 degrees plus during much of our stay.

If you’ve never been before and want to visit a truely beautiful part of the world, I would highly recommend checking out the Sunshine Coast.

I was really getting used to it all but now it’s back to work, cooking, 38 C days and what to write next on the blog :(