RUNSTATS and REORG-3: An effective REORG policy

Hi guys... so I am back with my blog sequence on reorgs and runstats... So just a recap my last two posts in this sequence had just explained "what is the importance of using RUNSTATs and REORGs" and then I discussed "How to 'effectively' automate RUNSTATS"...

So I will end the "saga" with REORG policy and it could not have come at a better time, when I had just fixed some major I/O anomalies with REORG (how many of you are thinking I am boasting?)...
Ok lets start with a problem statement...

I was told about about two problems, firstly Sudden Spikes in Reads at DB Server and Sudden Spikes in Writes at DB Server...

I really could not justify the Writes (but it struck me much later) but had an idea about reads... Also, at the design I had done for tablespace included some reserve for prefetching/blocked I/O... They were performing poor... Ooopss!!!

What helped me find it out about the bad performance of blocked I/O was this snapshot:

SELECT BP_NAME,
CAST(VECTORED_IOS as decimal(24,10)) / NULLIF((POOL_DATA_P_READS + POOL_INDEX_P_READS), 0) AS VECTORED_IO_FRACTION,
CAST (UNREAD_PREFETCH_PAGES as decimal(24,10)) / NULLIF(PAGES_FROM_BLOCK_IOS,0) AS WASTEFULL_PREFETCH_FRACTION, CAST((POOL_ASYNC_DATA_READ_REQS + POOL_ASYNC_INDEX_READ_REQS) as decimal(24,10)) / NULLIF((POOL_DATA_P_READS+POOL_INDEX_P_READS) - VECTORED_IOS,0) AS ANSYNC_PREFETCH_EFFICIENCY FROM SNAPBP;

So I had to do some analytic... First thing I did was running the runstats on tables and indexes and then I tried to find the amount of defragmentation:

SELECT I.TABNAME TABLE_NAME, I.INDNAME INDEX_NAME, REPLACE(SUBSTR(I.COLNAMES, 2) ,'+',',') AS MEM_COLS,
T.LASTUSED AS TAB_LASTUSED, I.LASTUSED AS INDX_LASTUSED,
I.NUM_EMPTY_LEAFS/NULLIF(I.NLEAF+NUM_EMPTY_LEAFS,0) INDX_EMPTY_LEAF_FRACTION,
T.FPAGES-T.NPAGES TAB_EMPTY_PAGES_CNT, I.NLEAF+NUM_EMPTY_LEAFS/(num_partitions*extent_size) AS INDX_REORG_NEEDED_IF_GT_1,
T.FPAGES/(num_partitions*extent_size) TAB_REORG_NEEDED_IF_GT_1
FROM SYSCAT.INDEXES I JOIN SYSCAT.TABLES T ON T.TABNAME=I.TABNAME AND T.TABSCHEMA=I.TABSCHEMA AND T.TABSCHEMA IN (schemalist) AND T.TYPE='T';

I guess the column names are pretty self explainatory and I need not explain what I got was huge no of index leaves scattered around and tables which were defragemented... So to explain the problems in terms of observation:

1) Too many disk seeks happening for read (even for index scans)
2) The sequential bloced IO fails miserably as the blocks pertaining to same table/index are scatterd
3) The writes, a record inserted/updated might have to re-balance the indexes, which is scattered so write cost increases
4) Finally the free leaves (for indexes)/blocks (for tables) are not available sequentially...

So I decided to do a REORG.. Most of the times people re-org with "REORG TABLE SCHEMA.TABLENAME" Guys it is hardly gonna help... Actually the one should arrange data in such fashion with will be usefull... So it is always good to reorg based on an index which can help you fetch data faster when you are going by that index (so choose the index you use more often)... Wait that is not enough... Before doing a REORG on your tables, you should have REORGed all your indexes on every table...

So I wrote a stored proc to dynamially select an index for every table and generate re-org statements for "REORG INDEXES ALL FOR TABLE" and "REORG TABLE USING INDEX"... Guess what!!! those momentary spikes were past and we had a good stable bufferpool utilization...

Many people while reading my last post would have thought AUTO_REORG as an equally useful method to do reorgs... i.e. to leave it to the database manager... But you might end up messing your OLTP performance and even your batch jobs which do bulk inserts/deletes... So best way is to keep checking weekly/fortnightly/monthly with the above queries on syscat tables to check if reorg is required or not... I really do not rely much on REORGCHCK utility from DB2... It is also important to run reorg on a regular interval based on about of data inflows and gets modified in your system...

So what next??? Probably some SQL Tuning tips!!! Till then HAPPY PREFETCHING!!!

RUNSTATS and REORG-1: How important they are

Hi guys...

So I am back... little quick this time...
So this is final post in my pseudo participation blog for DB2 Night Show (I had an early exit :-( after qualifying for finals)...
Congrats to Norberto for winning the competitions... Check his blog for tips...
Congrats to JB, Momi and Thiru as well for making to final...
I am looking forward to more such opportunities in future...

So I have come with some tips for people (people as the DBA community) to understand how important is the usage of two DB2 commands RUNSTATS and REORG, more importantly how you can determine when to REORG/RUNSTATS on which tables...

1) RUNSTATS are important for your tables, as they keep the stats updated for your table. This will always help your optimizer generate a better plan, rather the apt plan for fetching the data. I have seen situation when a filtered fetch goes for a table scan for 0.1-1million records even though column in filter is indexed...

2) REORG is equally important as it helps in putting the data in continous chunks... Generally for better insert performance, the data is put where you have space rather than putting it in a place (or near it) where other data of the table... The placement of data is also dependent on availability of space in nearest extent/block... If your data is too scattered across, there might be too much of disk seek involved and might as well degrade the performance of sequential and concurrent fetch...

Well you can AUTOMATE your task to certain extent and use automatic maintainance database configuration for auto runstats and auto reorg...

I shall come up with more on pros and cons of using those parameters and ways to optimize your RUNSTATS and REORG, in my future blogs...

I would like to thank Susan and Sathey for their continuous support, encouragement and tips for improvement on by blogging...

I will keep my blogs little short and will split the massive overdose of info over several blogs (thanks to Sathey)...

Latter I will also try to split my previous long posts into several blogs, which shall help my new subscriber...