Impact of shrink operation

I recently have been utilizing "shrink" to return partition free pages to the dbspace. Have done lots of testing on shrink/repack/compress so I felt comfortable with feeling that shrink wouldn't have a performance impact (like repack did) due to the simplicity of the shrink op. Apparently I was wrong. We had significant impact - users not able to access tables, etc. - recently. My feeling is that minimally 1) the partition page will have to be updated with accurate page counts, 2) the bitmap pages will have to be updated to remove flags/pages from this partition. 3) chunk free list(s) will have to be updated for the dbspace. Of course, large tables will have many bitmap pages so perhaps this is something I should have considered early on? What other impacts am I forgetting? Still surprised of a rather large negative performance/table access(es) impact. Thanks - Mark Scranton mark@markscranton.com

Hi Mark, this has to scan (not update) all bitmaps, from the end, until it finds=20 first bitmap bit indicating a used page. On this way it would drop unused = extents entirely and unused portion of last used extent. So even less to do than what you'd have thought, though of course 'all=20 bitmaps' can be quite a few to load/read. Have you ran the 'shrink' as a separate operation, not with e.g.=20 compress/repack? How large was the table / fragment, how much to shrink ? Cheers, Andreas From: "MARK SCRANTON" <mark@markscranton.com> To: ids@iiug.org Date: 24.03.2017 20:43 Subject: Impact of shrink operation [38805] Sent by: ids-bounces@iiug.org I recently have been utilizing "shrink" to return partition free pages to=20 the=20 dbspace. Have done lots of testing on shrink/repack/compress so I felt=20 comfortable with feeling that shrink wouldn't have a performance impact=20 (like=20 repack did) due to the simplicity of the shrink op. Apparently I was=20 wrong. We=20 had significant impact - users not able to access tables, etc. - recently. = My=20 feeling is that minimally 1) the partition page will have to be updated=20 with=20 accurate page counts, 2) the bitmap pages will have to be updated to=20 remove=20 flags/pages from this partition. 3) chunk free list(s) will have to be=20 updated=20 for the dbspace. Of course, large tables will have many bitmap pages so=20 perhaps this is something I should have considered early on?=20 What other impacts am I forgetting? Still surprised of a rather large=20 negative=20 performance/table access(es) impact.=20 Thanks -=20 Mark Scranton=20 mark@markscranton.com=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20

Andreas - I am only using "shrink" - no repack/compress. We're not licensed for compress and "repack" certainly had a big impact, which is understandable. Partition sizes vary but largest around 400GB (all fragments for that table) and many in the 100-200GB range. Thanks - Mark Scranton mark@markscranton.com