What's new in performance for Informix, version 12.10
This publication includes information about new features and changes in existing functionality.
The following changes and enhancements are relevant to this publication. For a complete list of what's new in this release, go to What's new in Informix®.
Overview | Reference |
---|---|
Prioritize databases for automatic update statistics You can now assign a priority to each of your databases in the Auto Update Statistics (AUS) maintenance system. By default all databases have a medium priority. You can assign specific databases a high or a low priority to ensure that statistics for your most important databases are updated first. Statistics for low priority databases are updated after high and medium priority databases, if time and resources permit. For example, if you have a system with a production and a test database, you can assign the production database a high priority and the test database a low priority. You can also disable AUS for a database. You can set AUS priorities in the IBM® OpenAdmin Tool (OAT) for Informix or by adding rows to the ph_threshold table in the sysadmin database. |
Prioritizing databases in AUS |
Overview | Reference |
---|---|
Easier removal of outstanding in-place alter operations Removing outstanding in-place alter operations improves performance and is a prerequisite for reverting to an earlier version of Informix. You can easily remove outstanding in-place alter operations for tables or fragments in IBM OpenAdmin Tool (OAT) for Informix or with the new table update_ipa or fragment update_ipa argument of the admin() or task() SQL administration command. Previously, you ran a dummy UPDATE statement to remove outstanding in-place alter operations. You can remove outstanding in-place alter operations faster by including the parallel option with the table update_ipa or fragment update_ipa argument of the admin() or task() SQL administration command. |
Performance of in-place alters for DDL operations |
Faster storage optimization You can now compress, uncompress, and repack data or indexes faster by including the new parallel option with the table, fragment, or index argument of the admin() or task() SQL administration command. |
Reduce disk space by compressing tables and fragments |
Overview | Reference |
---|---|
Automatic resource tuning for performance You can configure the database server to adjust resources to improve performance:
|
The BUFFERPOOL configuration parameter and memory utilization AUTO_LLOG and its effect on logging |
Temporarily prevent constraint validation You can significantly increase the speed of loading or migrating large tables by temporarily preventing the database server from validating foreign-key referential constraints. You can disable the validation of constraints when you create constraints or change the mode of constraints to ENABLED or FILTERING.
The NOVALIDATE keyword prevents the database server from checking every row for referential integrity during ALTER TABLE ADD CONSTRAINT and SET CONSTRAINTS operations on foreign-key constraints. When those statements finish running, the database server automatically resumes referential-integrity enforcement of those constraints in subsequent DML operations. Use this feature only on tables whose enabled foreign-key constraints are free of violations, or when the referential constraints can be validated after the tables are loaded or migrated to the target database. |
Creating and enabling referential constraints efficiently |
Faster creation of foreign-key constraints When you run the ALTER TABLE ADD CONSTRAINT statement, some foreign-key constraints can be created faster if the table has a unique index or a primary-key constraint that is already defined on the columns in the foreign-key constraint. Foreign-key constraints are not created faster, however, if the constraint key or index key includes columns of user-defined or opaque data types, including BOOLEAN and LVARCHAR, or if other restrictions are true for the foreign-key constraint or for the referenced table. |
Creating and enabling referential constraints efficiently |
Overview | Reference |
---|---|
In-place alter operations on serial data types The ALTER TABLE statement converts the following
column data types with in-place alter operations:
Previously such data types were converted with slow alter operations. In-place alter operations require less space than slow alter operations and make the table available to other sessions faster. |
Conditions for in-place alter operations |
Dynamic private memory caches for CPU virtual
processors Private memory caches for CPU virtual processors now change size automatically as needed. You create private memory caches by setting the VP_MEMORY_CACHE_KB configuration parameter to the initial size of the caches. The size of a private memory cache increases and decreases automatically, depending on the needs of the associated CPU virtual processor. Previously, the size of private memory caches was limited to the value of the VP_MEMORY_CACHE_KB configuration parameter. |
Private memory caches |
Monitor resource contention You can view the dependencies between blocking and waiting threads by running the onstat -g bth command. Run the onstat -g BTH command to display session and stack information for the blocking threads. |
Monitor blocking threads with the onstat -g bth and onstat -g BTH commands |
Overview | Reference |
---|---|
Increased scalability with optimized
caching The sizes of memory caches are now twice the values that are set by the DS_POOLSIZE, PC_POOLSIZE, PLCY_POOLSIZE, or USRC_POOLSIZE configuration parameters. For example, if the DS_POOLSIZE configuration parameter is set to 127, 254 entries are allowed in the data distribution cache. If all entries in a cache are full, the cache size automatically grows by 10%. You can also dynamically increase cache sizes in memory. You can view more information about caches and mutexes with onstat commands. You can view detailed information about memory caches by running the onstat –g cac command. The output can help you determine whether the cache is configured for optimal performance. |
Configure and monitor memory caches |
Increased scalability with optimized
caching Cache access and management is optimized to provide faster performance for large systems that have many users. You can dynamically increase cache sizes in memory. You can view more information about caches and mutexes with onstat commands. |
Adjust the UDR cache |