Compared to other performance issues, what is the performance impact of secondary extents, tables indexes? Is the impression correct that Load/Reorg leaves the free space (PCTFREE and FREE PAGES) unused and leave unused space at the end of the Primary Space (PQTY) free?
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
When inserts have filled near free space, they insert to far free space then to the end of PQTY. Then, they have to allocate SQTY and insert to it.
Is the impression correct that inserts to near and far free space are roughly the same for tables and indexes? But inserts to the end of Index PQTY and SQTY degrade performance more than inserts to PQTY and SQTY of tables? (Indexes are on a random dumb key, many millions of rows.)
Well, let me provide some general guidance. Secondary extents are probably at the bottom in terms of performance impact. There are many more issues that have greater importance such as proper indexing, efficient SQL, memory usage (buffer pools), database organization, workload balancing and so on.
That being said, extents will negatively impact performance. The chain from the primary to the extent must be read to get to the extent -- and that is additional work that does not need to be done when extents do not exist. But don't go overboard worrying about extents. The impact is small and I wouldn't worry about them until there were dozens (as opposed to just a few).
In terms of INSERTs, when a secondary extent must be created for the INSERT, performance will suffer.
Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our