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 !
you went from 2 rows per page to 3 rows per page and no extra FREEPAGES. However, good test would have been to change only only value at a time. I agree you improved, less pages read = better performance. However, my suspicion is that PCTFREE was largely responsible for the improvement vs FREEPAGE. So 3Million rows went from 1.5 million pages to 1 million pages. and FREEPAGE went from 166,666 pages to 111,111. So what really produced the page savings? the FREEPAGE or PCTFREE? SQL aside and number of rows returned would have been a better test. Good article thou shows that altering these values does definitely have an effect.
ReplyDeleteRock,
ReplyDeleteThank you and I really appreciate your thoughts on this. Yes, I should be done a lot of tests in a step by step manner. I shall follow what you have said in my future research or findings. To answer your question on - What really produced the page savings ? I would say tweaking both the parameters - FREEPAGE & PCTFREE resulted in savings. The onlines would largely perform INSERT's on the table, thus I brought down the FREEPAGE value to 5 from 9. Application does not have to scroll through those 4 extra pages anymore to insert the record. You are right about PCTFREE % being the lead factor for the saving and I totally agree with you on this stage and I am glad that you liked this post