My team came across the following earlier this month. There’s a shark in the water, waiting to byte users running 22.214.171.124 through 10.2.0.3.
One evening, a table with 10 million rows got several thousand rows updated and inserted via a nightly batch job. But the table grew from 500 MB to over 4 GB!
Well, how could that be…The tablespace where the table resides had recently been re-organized so that settings were now locally managed, uniform extent sizes and automatic segment space management (ASSM) turned on. All best practices. Oracle version was 126.96.36.199 on Solaris.
We looked at the possibility of chained rows. There were none. We checked how the batch job worked. It was a Peoplesoft SQR job. It did not use parallelism. It only inserted and updated rows. It did not do deletes or truncate the table before inserts and updates.
I remembered about how the high water mark can be driven up high under certain circumstances, so I checked that using dbms_space.used_space. High water mark seemed fine. But that led me to dbms_space.space_usage. This procedure indicated that there thousands of “unformatted blocks”. This was something new for me. I forwarded this off to Oracle support, and sure enough, there is a bug affecting 188.8.131.52, and also 10g versions through 10.2.0.3. See Metalink Note:469985.1: Sudden Increase in Unformatted Blocks.
The write up on this one is a little scary, just like a shark in the water. So be careful out there.
“Unexplained increases in unformatted blocks in a tablespace. Very few of the unformatted blocks ever get used. More space is added whenever a new block is requested resulting in even more unformatted blocks. The number of unformatted blocks varies from one database to another. Some databases only add a few thousand unformatted blocks at one time. Others add millions of unformatted blocks all at once. Once the problem starts the only way to prevent new unformatted blocks from being added is to rebuild the tablespace.”