May be I should have done it earlier. There is a tablespace in production which is meant for " INSERT " operation, yes ! Only INSERT DML's run on this table. The application performs SELECT on this table, Ok - So what's the big deal ? , The tablespace was designed long back with DSSIZE as 2Gigs - fair ? I felt so, Average LRECL of 1,098 and with 3 million rows. Now, with such a configuration why should this tablespace have 2 extents ?? The size of the tablespace was beyond 3.5 GB ( 3.86 GB to be precise ). What causing this massive space utilization and of course high number of active pages
FREEPAGE and PCTFREE is defined for the table -
FREEPAGE 9
PCTFREE 30
The LDS of this tablspace with the above values -
The application run time on this table -
Is it really required to have PCTFREE as 30 ? Like i mentioned above, this table is a history table where records get inserted on a daily basis.
So here is what I did - I altered the tablespace and set the FREEPAGE value to 0 and PCTFREE to 5 ( I could have made it 0 as well ), I felt what if in case without my notice there is a update query that triggers on the table, what about REORG's ? 5 is a good value for PCTFREE if you know your table very well and administer what is happening on it. I then ran a REORG with share level CHANGE, performed a REBIND on the program
The LDS of this tablespace after alteration & REORG -
The REORG worked like a charm and there was a dip in the space utilization on the underlined dataset for this tablespace. The LDS now accounted for just 1.8 GB as compared to its earlier size of 3.8 GB, we have successfully optimized space - very fruitful indeed !
The application run time on this table post tuning activities -
There was a reduction in GETPAGES value, around 30,000 pages were saved from reading in. Efficiency gain achieved on the application was around 86 %. After reading this you may want to go back to your tablespace and check with your application developers as to what these tables are and follow up on the PCTFREE and FREEPAGE values and save !
Note : You can try such options out with indexes as well. The above savings were influenced due to various other factors such as system availability, number of records, tablespace definitions, job start time, CPU utilization etc. Due to the drop in the application run time there was no wait for I/O on the systems that led to such drastic downsized figures. You may not get the exact numbers, but digging into what the table does and tweaking the parameters - does not stop you, at anytime !
