Summary information |
|
|---|---|
Default value |
95 |
Range of values |
1–100 |
Status |
Dynamic |
Display level |
Comprehensive |
Required role |
System Administrator |
The page utilization percent parameter is used during page allocations to control whether Adaptive Server scans a table’s OAM (object allocation map) to find unused pages or simply allocates a new extent to the table. (See “number of oam trips” for more information on the OAM.) The page utilization percent parameter is a performance optimization for servers with very large tables; it reduces the time needed to add new space.
If page utilization percent is set to 100, Adaptive Server scans through all OAM pages to find unused pages allocated to the object before allocating a new extent. When this parameter is set lower than 100, Adaptive Server compares the page utilization percent setting to the ratio of used and unused pages allocated to the table, as follows:
100 * used pages/(used pages + unused pages)
If the page utilization percent setting is lower than the ratio, Adaptive Server allocates a new extent instead of searching for the unused pages.
For example, when inserting data into a 10GB table that has 120 OAM pages and only 1 unused data page:
A page utilization percent of 100 tells Adaptive Server to scan through all 120 OAM pages to locate an unused data page.
A page utilization percent of 95 allows Adaptive Server to allocate a new extent to the object, because 95 is lower than the ratio of used pages to used and unused pages.
A low page utilization percent value results in more unused pages. A high page utilization percent value slows page allocations in very large tables, as Adaptive Server performs an OAM scan to locate each unused page before allocating a new extent. This increases logical and physical I/O.
If page allocations (especially in the case of large inserts) seem to be slow, you can lower the value of page utilization percent, but be sure to reset it after inserting the data. A lower setting affects all tables on the server and results in unused pages in all tables.
Fast bulk copy ignores the page utilization percent setting and always allocates new extents until there are no more extents available in the database.
