Index Dynamics – Part 3

Ver este articulo en Español

See previous post Index Dynamics – Part II or first part Index Dynamics – Part I

On this part, I’m going to share with you the final results for my 2 week experiment with indexes on their habitat, a production environment.

I’ve to forewarn that some data was not available, as the ANALYZE method fails for objects currently locked, given that our environment is a “live” production database and for consistency we scheduled all tests for a given time, which some days collided with business process. The Used Space graph shows those NA data points; however for INDEX1 and INDEX4 the Nov 7th data for Deltas was interpolated, the only index for which we have all samples is INDEX2, coincidentally our research subject.

First let’s see the Used Space graph, here you may see how the Used Space percentage continues the decay trend, which means that free space within the index nodes is increasing.

Compare the current Used Space figures to those sampled before the rebuild, observe that after 15 days some indexes are midway to have the same amount of free space. That recalls the frequent doubts regarding index maintenance: “Must rebuild indexes? How long will last the structure ‘optimus’ state?”

Used Space    First    Now
INDEX1(10c)    69%     99%
INDEX4(6c)     63%     74%
INDEX2(4c)     53%     77%
INDEX3(2c)     45%     92%

Given our partial-conclusion stated last post (Index Dynamics – Part II)
”The branch blocks are the stressed part of the B-Tree right after the rebuild”
We now might see less activity for Branch Blocks during the following days, which actually occurred.

One remark: observe the Nov 6th high for INDEX1 and the previous behavior, was on that day the transaction rate motivated a higher reorganization within the index, showing us the impact business patterns have on data structures.

I have to mention that my production environment went through a period close week, therefore increased activity and closing processes made their mark on observations. An example of this is the resulting graphs for Leaf Blocks, with noticeable inter-day spikes.

Let’s pay a visit to our research subject, INDEX2. This chart is online with everything previously stated, no surprises here.

This study motivated the following questions (that’s the problem with research, you answer one question and many new take the place):
1) How PCT_FREE (or PCT_USED) impacts the decay curve?
2) Are the splitting constants embedded in the code, optimal figures?
3) Do we need a feature for PCT_FREE change after rebuild has been done?

Conclusions

* High stress within the branch blocks may be observed as soon as transactions start to modify the index structure; the stress period or intensity, will depend on transaction rate and index attributes.

Recommendation

* Do not rebuild indexes right before processes or high load days, if heavy writing is expected: the index leaf block split overhead may impact performance.

I’ve the pending assignment of proposing a mathematical model for the Index Decay Rate… I’m working on that, with the help of Calculus. My next delivery will talk about that, I’m pretty close…

Thank you for reading, don’t forget to leave your comments.

See previous post Index Dynamics – Part II or first part Index Dynamics – Part I

Bookmark this on Delicious

Index Dynamics – Part 2 (Halloween on the Block)

Ver este artículo en Español

View starting post: Index Dynamics – Part 1

As I’ve promised, today will share with you mid-term results for my index observations.

First we may see a graph of Used Space, as reported by column PCT_USED of table INDEX_STATS (right after an ANALYZE over each index). This percentage accounts the space allocated to the B-Tree that is used.

There is one line for every index we are considering, and note the legend on the graph showing the index name and the number of columns inside parenthesis.

What can be observed in this chart?
1) After rebuild (done on saturday), every index starts with 95% used space.
2) First day is sunday, the system had almost 0 activity, therefore our indexes’ space usage show slight changes.
3) Starting on monday, INDEX1 reported wrong data for PCT_USED and the other indexes began their “decay” trend.
4) After a whole week of activity, indexes gained free space, some of them faster than others (for instance INDEX4 went from 95% to 84% used space, that is 11% on 5 days).

Point 3 raised a service request with Oracle.
Point 4 may be explained in terms of:
a) Table transactionality (how many insertions/deletes/updates it had)
b) Index type, if unique or non-unique.
c) Number of columns conforming the index.
d) Type of every indexed column.

I may propose the following hypothesis: the index used space decay rate is directly proportional to the table’s transactionality, to the number of columns and types of them, and inversely proportional to the type of the index (Unique or non-Unique) and block size.

As you already know, B-Tree indexes have two types of nodes: called Branches and Leafs.

Now, let’s see where is that space allocated, look this chart that shows increment or delta on daily samples taken from column LF_BLKS.

You may see a great saving due to rebuild, however that saving fades slowly during the following days. Next chart makes a zoom, in order to watch closely the variation rate experimented during those days.

Keep in mind the rate at which leaf blocks are incremented, later you’ll see how it’s related to new key insertions (transactionality).

What about the branches? … that’s what we’re going to see on next chart: the behavior of those indexes for branch blocks. I’ve taken the sampled value for BR_BLKS and got the variation rate versus the previous day.

Please observe, the stepped peak for INDEX2 and INDEX4, during the first day of activity the number of branch blocks almost doubled, that means an intense reorganization within the index. That may be caused due to the nature of these indexes (all are UNIQUE) and a high transactionality rate.

For the INDEX3, we observe that the increase is splited between 2 days, Sunday and Monday. This table presented activity the day after the rebuild was done.

We have to ignore the INDEX4, cause their figures are not reliable.

Why is there a higher increase on the number of branch blocks? We may answer that question recalling the percentage of free space (5%) we had after the rebuild, that is a very small margin for a leaf block, and the chances of split increase if we have a UNIQUE index. We must remember that a leaf block split, may involve a branch block split.

Our partial conclusions may be stated as follow:
1) Depending on the percentage of free space, after rebuilding indexes, their state becomes “less” stable.
2) Indexes tend to take a “stable” form, with the pass of time.
3) The branch blocks are the stressed part of the B-Tree right after the rebuild.

I will finish this experiment next Saturday, and share with you all remaining findings next Tuesday; yes, seven days from now… or eight days? anyway…
I hope to get near a mathematical model for the Index Decay Rate, cross fingers.

Thank you for reading, keep in touch!

See next part on Index Dynamics – Part III or go to first part Index Dynamics – Part I

How to Flush your database caches

Ver este articulo en Español

Flushing the SGA memory areas, Shared Pool and Buffer Cache, it’s an uncommon task, however, it gets useful when you’re doing some tests and want to override the memory and go direct to disk, or when you have shared pool issues (here is a workaround, however I encourage you to find the root cause).

Shared Pool flush
This is the only sentence you have to know for releases 9i and up…

alter system flush shared_pool

Buffer Cache flush
For Oracle 9i I didn’t know how to do this, fortunately found it today on Rahat Agivetova’s blog

alter session set events = 'immediate trace name flush_cache';

I’ve tested it and does the job well…

And for Oracle 10g and up, the syntaxis is as follows:

alter system flush buffer_cache;

But you already knew that, isn’t it? …well, this was a “snack” post until Friday’s follow up to Index Dynamics, which I may anticipate interesting results…

Thanks for reading… and for your comments, too!

Subscribe to Oracle Database Disected by Email

Index dynamics

Ver este articulo en Español

Today I will start with a series of 3 posts depicting the follow up of 4 index behavior on a Production database.

The purpose of this exercise is try to unveil the morphology these four indexes take during a given period of time, under what kind of load, model it in a graphical way. Since indexes are quite a black box, this exercises also proposes a complementary procedure for index quality measure.

Experiment subjects were selected using information on DBA_SEGMENTS+DBA_HIST_SEGMENTS, with focus on size and activity, indexes were choosen within the 400Mb-500Mb range thinking on fast ALTER INDEX REBUILD and fast ANALYZE VALIDATE STRUCTURE.

Our four candidates are (names were replaced in order to protect their identity)

OBJNAME                  BLOCKS      BYTES HEIGHT PCTUSED
-------------------- ---------- ---------- ------ -------
INDEX1                    62464  511705088      4      69
INDEX2                    60416  494927872      4      53
INDEX3                    61056  500170752      4      45
INDEX4                    54272  444596224      4      63

We have our “Before” snapshots, now we need to rebuild all four indexes and take “Initial” snapshots to start the experiment. After this operation our figures are:

OBJNAME                  BLOCKS      BYTES HEIGHT PCT_USED
-------------------- ---------- ---------- ------ --------
INDEX1                    41600  340787200     4       95
INDEX2                    34176  279969792     4       95
INDEX3                    28032  229638144     3       95
INDEX4                    37888  270445017     4       95

Note the amount of space “empty” the indexes had before rebuild, for INDEX2 and INDEX2 we may ask ourselves “How are the tables used in order to get index keys so dispersed?”. INDEX1 and INDEX4 seem within normal parameters. All four indexes now have the same initial condition on 5% free space.

On October 31th, Friday … Hallowen, we are going to see what has been happening with our indexes;and who knows, you may testify witchcraft… or, is it science? What do you think?

Keep in touch…

See next part of this story on Index Dynamics – Part II

Honey, I shrunk the indexes – Part 2

Ver este articulo en Español

How to pick the perfect candidates

Warning: index compression may change execution plans and affect performance, try this on a test database and check if application SQL and PL/SQL code execution improves.

Not every index must be compressed, sometimes compression will give minimal space reductions, that don’t compensate the overhead incurred. But how do we know that?

I may suggest two approaches:
1) Size oriented
2) Access frequency oriented

Size Oriented

Let’s start with size oriented picking, saying: the bigger the elephant is, better results will get when on diet.

I’ve used a script like this to get my list of candidates for shrinking:

SELECTsubstr(segment_name,1,20) as index_name,bytes, blocks, extentsFROM  dba_segmentsWHERE owner = '{write here the owner}'AND   segment_type = 'INDEX'AND   extents > 63            <---this you may change order by bytes desc; 

After running above script, you’ll get a listing like this:

INDEX NAME                BYTES     BLOCKS    EXTENTS                 -------------------- ---------- ---------- ----------                 PROD_NAME_IX             524288         64          8                 PRD_DESC_PK              327680         40          5                 SYS_C009603              131072         16          2                 SYS_C009607               65536          8          1                 SYS_C009606               65536          8          1                 ACTION_TABLE_MEMBERS      65536          8          1                 LINEITEM_TABLE_MEMBE      65536          8          1                 SYS_C009602               65536          8          1                 

Now you have to forecast the best compression ratio for your index, and there is a feature very accurate for doing so: ANALYZE the index.

Despite the fact that analyzing tables or objects have deprecated the statistics purpose (one of them), we may use this sentence to test structure. Following command and a quick query to INDEX_STATS will show us if the selected index is a best fit, which compression order to choose and expected size reduction:

SQL> ANALYZE INDEX owner.index_name VALIDATE STRUCTURE OFFLINE;

Index Analyzed

SQL> SELECT name, height, blocks, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVEFROM index_statsWHERE name = '{index_name}';

The resulting value OPT_CMPR_COUNT is the value you specify for COMPRESS {n} clause, and OPT_CMPR_PCTSAVE is the “expected” compression ratio for that value. All other values from INDEX_STATS are present figures.

Then your sentences may look like this:

SQL> ALTER INDEX owner.index_name REBUILD COMPRESS {value from OPT_CMPR_COUNT}

or

SQL> CREATE INDEX owner.index_name ON {table_index_clause}2:   TABLESPACE {Tablespace Name}3:   COMPRESS {value from OPT_CMPR_COUNT}4:   {other storage clauses};

Second approach: Access Frequency

For this we’re going to need the help of two important views: V$SEGSTAT(9i and up) and ALL_OBJECTS. We need V$SEGSTAT because that dynamic view will show us valuable statistics regarding logical reads/writes or physical reads/writes. Following script is proposed as an aid to find the top used indexes within a schema.

SELECT a.object_name, b.statistic_name, b.valueFROM all_objects a, v$segstat bWHERE  a.object_id = b.obj#AND  a.owner = '{your schema owner here}'AND  a.object_type = 'INDEX'AND  b.statistic_name = 'physical reads'  <-- You may change this for physical reads direct ORDER by b.value desc 

Above query will give you a list of candidates for compression, now you have to apply the ANALYZE and check if there are good space reductions that ‘may’ correspond to less IO.

Jump to Part III Honey, I shrunk the indexes – Part 3: Index Compression is good or evil?

View starting post: Honey, I shrunk the indexes – Part 1

Ver este articulo en Español

Honey, I shrunk the indexes

Ver este articulo en Español

Introduction

There is an Oracle feature that may provide savings in space and IO, have you heard of “index key compression”? well, this feature is with us since 8i, but for many, it’s obscure or unknown.

Despite the 10g storage management optimizations, always there is gain from index maintenance. If you do index checks regularly you’re a good DBA… but if you don’t, better take care from now on.

Adding the gains from index rebuild or shrink, you may consider compressing some well-picked indexes, for which the space savings and IO reductions overcome the slight(?) cpu overhead it causes. I wrote a question mark after ’slight’ because we will try to estimate that cost in the short term.

I’ll propose this starting questions:
* How do you use index key compression?
* What are the first sight results?
* How to pick the best candidates for compression?
* Index compression is good or evil… or both?
* What is the benefit/cost after the shrinking?
* What are the “inside” results or how to analyze the effect on my present queries?

If you have more questions, please feel free to drop a comment and we (all of you and I, because as far as I know I don’t have multiple personality disorder) will try to tackle and provide a satisfactory answer.

How do you use index key compression?

There are two ways to accomplish this:
1) drop the index, create it again with COMPRESS
2) rebuild the index with COMPRESS

I will try the second method, with this huge index I’ve on a test database. These are the starting figures:

TABLE_ROWS   TABLE_BLOCKS   INDEX_BLOCKS   INDEX_BYTES    BLEVEL  LEAF_BLOCKS----------   ------------   ------------  -------------   ------  -----------7,331,706        459,210        155,648  1,275,068,416        3      149,394

Now that we have our baseline, it’s time to issue the DDL sentence that will reorganize the index:

SQL> ALTER INDEX idx_big_comp_test REBUILD COMPRESS 2;Index Rebuild

After that statement our figures are now the following:

TABLE_ROWS   TABLE_BLOCKS   INDEX_BLOCKS   INDEX_BYTES    BLEVEL  LEAF_BLOCKS----------   ------------   ------------  -------------   ------  -----------7,331,706        459,210        139,904   1,146,093,568        3     133,682

A quick comparison yields less index blocks and leaf blocks (which is logical and obvious), accounting for 10.5% of space savings.

Let our imagination fly, we’re showing our boss the way to extend the out-of-disk due date or justifying a well earned salary rise derived from storage savings. Back to reality… in this life everything has a price, don’t rush and compress every index in your databases until we talk about pros and cons, and learn how to choose good candidates for compression.

Jump to Part II Honey, I shrunk the indexes – Part 2: How to pick the perfect candidates

Ver este articulo en Español/Look for this content in spanish

Recover that lost space

Do you know what happens when you issue a DELETE? Have you wondered what happen after COMMIT? … well, I might say that there is life after delete for all those blocks that belonged to the data recently deleted.

I’ll present the following test case to show you how this works:

First we are going to create our test environment, borrowing a table from the Examples OE schema, creating it in the SCOTT schema.

SQL> alter session set current_schema=SCOTT;

SQL> create table pd as select * from oe.PRODUCT_DESCRIPTIONS;

Let’s see how many blocks our new table PD is using

SQL> column segment_name format a202: select segment_name, sum(blocks)3: from dba_Segments where owner = 'SCOTT' and segment_name = 'PD'4: group by segment_name5: order by sum(blocks) desc;

SEGMENT_NAME         SUM(BLOCKS)-------------------- -----------PD                           384

Now we’re going to see how it’s structured the table:

SQL> select column_name,2: substr(data_type,1,20) as dt,3: data_length4: from all_tab_columns5: where table_name = 'PD';

COLUMN_NAME                    DT                   DATA_LENGTH------------------------------ -------------------- -----------PRODUCT_ID                     NUMBER                        22LANGUAGE_ID                    VARCHAR2                       3TRANSLATED_NAME                NVARCHAR2                    100TRANSLATED_DESCRIPTION         NVARCHAR2                   4000

That structure yield us a theoretical row length of (22+3+100+4000) = 4125, which if used fully will give us just 1 row per block (see below for DB blocksize). But , rarelly a NVARCHAR2 is fully used.

SQL> show parameters db_block_size

NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_block_size                        integer     8192

We know there is data and how it’s structured, but we need something to erase… the following may be politically incorrect but is only for instructional purposes:

SQL> select language_id, count(*) from pd group by language_id;

LAN   COUNT(*)--- ----------US         288IW         288TR         288...S          288SK         288ZHS        288

30 rows selected.

We’re going to delete all the ‘S’ language registers, I don’t know what means ‘S’ and I’m sorry if I hurt nationalistic feelings, remember this is hypotetical.

SQL> delete from pd where LANGUAGE_ID = 'S';

288 rows deleted.

SQL> commit;

Commit complete.

That was the death sentence for those rows, now will see what happened with the blocks assigned to PD.

SQL> column segment_name format a202: select segment_name, sum(blocks)3: from dba_Segments where owner = 'SCOTT' and segment_name = 'PD'4: group by segment_name5: order by sum(blocks) desc;

SEGMENT_NAME         SUM(BLOCKS)-------------------- -----------PD                           384

Wow! the table still has the blocks assigned, and will not return them until a TRUNCATE, DROP … or ALTER TABLE is issued. Yes! an alter table will return the extents to the tablespace free pool.

SQL> alter table scott.pd deallocate unused;

Table altered.

SQL> column segment_name format a202: select segment_name, sum(blocks)3: from dba_Segments where owner = 'SCOTT' and segment_name = 'PD'4: group by segment_name5: order by sum(blocks) desc;

SEGMENT_NAME         SUM(BLOCKS)-------------------- -----------PD                           376

There it is!!! the blocks that contained all the ‘S’ language registers, now don’t belong to the table PD.

At first you may be tempted to include a periodic ALTER TABLE…DEALLOCATE UNUSED in order to recover all that ‘wasted’ space, you must be carefull when evaluating this, because there are tables that get data erased and never or rarelly get new data, them are perfect candidates for this.

On the other hand, tables with heavy delete activity won’t benefit too much because of the overhead related to update the internal tables that keep track of blocks and extents. What is ‘heavy’ activity, depends on the deleted rows/total rows rate, and you must develop a maintenance criteria to exclude tables.

You’re welcome to send your comments… I hope this help you

Please, before you go, don’t forget to vote the poll regarding the content of this blog, thank you!