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

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

Interview with a Pro: Dan Norris

Ver este articulo en Español

Today I will share with you a video interview, this section will be entitled: “Interview with a Pro”, and it’s aimed to all of you, IT professionals or not related to IT at all. We will present remarkable people, with notable activism for Oracle & IT, and high expertise level on their field.

This time, I’m glad to feature our interview with Dan Norris, he is a consultant (ESA Practice Manager actually) with Piocon, President of the Oracle RAC SIG, member of the Independent Oracle Users Group (IOUG) and a notable member of our IT community.

If you are unable to see this video on the embeded player (below), it’s available from YouTube at this link Dan Norris Interview at Oracle Open World

I hope his knowledge and experiences would be useful for you.

Who is using your UNDO space?

Ver este articulo en Español

Sure you’ve faced this situation: a growing undo tablespace, that seems it could engulf your entire disk space… until finally stops demanding additional space, and within some minutes (or hours, depends on your UNDO_RETENTION setting) you start to see more and more free space in your tablespace. If you scratched your head wondering ‘what happened?’ or ‘who the User did this?’, this post may be helpful.

There are some views that show information related to undo activity:

* V$UNDOSTAT: histogram-like view that shows statistics for 10-minute intervals.
* V$TRANSACTION: present time view providing information on current transactions.
* V$SESSTAT: individual session statistics, which includes one for undo usage.

V$UNDOSTAT will provide a who did hint, recording the longest running query for that 10-interval, through the MAXQUERYID column which may be linked to V$SQL and use columns PARSING_USER_ID or PARSING_SCHEMA_NAME the get a grip on the suspect.

V$TRANSACTION linked with V$SESSION will show current used undo blocks for ongoing transactions. This query may help:

SELECT  a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC

V$SESSTAT provides another view, a who uses the undo kind of view, but we must avoid to get lost in the maze of Oracle statistics and focus on just one: Undo change vector size, which will accumulate the bytes of undo used during the session lifetime. Following query is designed to pinpoint who is having a high undo activity.

SELECT a.sid, b.name, a.value
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
AND a.statistic# = 176    <-- Which stands for undo change vector size
ORDER BY a.value DESC

Good luck with your UNDO-eating monsters…

FOR UPDATE… or not FOR UPDATE, that is the question

Have you considered the impact of an unnecesary FOR UPDATE on your queries?

I had this database which suddenly rised the redo log generation rate, running on archive log mode (like every production database must be) it become a nightmare, trying to backup logs every hour or planning a huge space increase for the archive log destination filesystem.

Fortunately we keep track of every development that is promoted to production; our records showed that near the date a recent interface was released, the database increased the redo log activity. Then we proceeded to disect the suspicious code, realizing that the programmer coded 4 nested loops (on 4GL, given we have BaaN) and every loop was coded using, a presumably unnecesary SELECT…FOR UPDATE.

Those findings pointed the way of my research, since on a previous review of the process, every intermediate result table was turned to NOLOGGING, without any reduction on redo log creation, the only place where logging was taking place was the UNDO tablespace, a behavior that cannot be turned off. But I’ve to verify this theory… and find a solution to the puzzle.

I knew where to look for the information needed: the Oracle Data Dictionary views, then I opened the Oracle Database Reference for 10gR2, which is the ‘fortunate’ release of our test environment, given that our production is running 9R2, both under HP-Unix and showing exactly the same behavior.

These views helped to find useful information, and finally pinpoint the issue:
V$FILESTAT : if the UNDO tablespace was under heavy IO, it may be reflected here.
DBA_DATA_FILE : I needed to translate the file number to something meaningful.
V$UNDOSTAT : provided a 7 day meassure window that allowed to correlate heavy redo log generation with high UNDO activity.
V$EVENT_HISTOGRAM : histograms for every wait event.
V$SESSTAT : timed statistics by user session.
V$SYSSTAT : timed sytem statistics.
V$STATNAME : name of every statistic.

First I tried the following query and the resulting set confirmed the theory: at the very first place was located the datafiles for the UNDO tablespace.

 SELECT b.file_name, a.phywrts, a.phyblkwrt, a.writetim
FROM v$filestat a, dba_data_files b
WHERE a.file# = b.file_id
ORDER BY a.phyblkwrt DESC

Then I built this test case to confirm if there was a difference between SELECT and SELECT..FOR UPDATE shown at redo log write.

SQL> create table test ( col1 varchar2(10) );

Table created

SQL> select sid from v$mystat where rownum = 1;
1609

1 row returned
SQL> select name,a.value
2    from v$sesstat a, v$sysstat b
3    where b.statistic#=a.statistic#
4    and b.name = 'redo size' and sid = 1609;

redo size 13112
1 row selected

SQL> insert into test values ('World');

SQL> commit

SQL> select name,a.value
2    from v$sesstat a, v$sysstat b
3    where b.statistic#=a.statistic#
4    and b.name = 'redo size' and sid = 1609;

redo size 13504
1 row selected
SQL> select * from test;

SQL> select name,a.value
2    from v$sesstat a, v$sysstat b
3    where b.statistic#=a.statistic#
4    and b.name = 'redo size' and sid = 1609;

redo size 13504
1 row selected

SQL> select * from test for update;

redo size 14248
1 row selected

You may see that after issuing a simple SELECT there wasn’t an effect in redo size, that effect was present modifying the query with the FOR UPDATE clause.

Explanation

Seems that SELECT…FOR UPDATE creates a copy of the information retrieved, which is stored at the UNDO tablespace. The undo tablespace logging behavior cannot be changed, therefore when the interface program was executed, there was m * n * p * q writes to the undo tablespace and therefore to the redo logs.

Results

After sending our recommendation to development to change their code and use simple SELECTs, the redo generation rate droped from 4 Gb to 500 Mb per hour, which is a considerable reduction.

Thanks for reading, hope this note helps you