On the Eighth Day of 12.2, my DBA gave to me… Index New Features
The following list illustrates the new Index features for Oracle Database 12c Release 2. There are several new index features that offer improved performance and capability.
Tracking Index Usage
New index tracking has been added that provides information, about the use of indexes in the database. The V$INDEX_USAGE_INFO tracks index usage since last flush. SQL> desc V$INDEX_USAGE_INFO [page_container]
Name —————————– INDEX_STATS_ENABLED INDEX_STATS_COLLECTION_TYPE ACTIVE_ELEM_COUNT ALLOC_ELEM_COUNT MAX_ELEM_COUNT FLUSH_COUNT TOTAL_FLUSH_DURATION LAST_FLUSH_TIME STATUS_MSG CON_ID
Type ——————– NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER NUMBER TIMESTAMP(3) VARCHAR2(256) NUMBER
[/page_container] A flush occurs every 15 minutes and is recorded to the V$INDEX_USAGE_INFO view. The ACTIVE_ELEM_COUNT column is reset to 0, while the LAST_FLUSH_TIME is updated with the system time. The INDEX_STATS_ENABLED column indicates if the index usage statistics are enabled or disabled. A value of 0, indicates that the index statistics are disabled; whereas a value of 1 indicates that the index statistics are enabled. The ACTIVE_ELEM_COUNT, represents the number of active indexes since the last flush. The FLUSH_COUNT, represents the total number of flushes since the database was brought online. The INDEX_STATS_COLLECTION_TYPE column, represents whether the type of collection was sampled or comprehensive (ALL) statistics; these are collected for each access of the index. The DBA_INDEX_USAGE view, holds cumulative statistics for each index in the database. Usage data includes total access count, executions involving the index, and when the index was last used.
Partitioning Support for XMLIndex
Oracle 12.2 now provides partitioning support for XMLIndex, allowing for more scalability during parallel operations. Paralyzing XML operations, allows for increased performance by taking advantage of parallel…. [hr width=”1px” color=”#16a085″ style=”dashed”][/hr] To receive a full version emailed copy of this document, including more on Application Security in Database, Enhancements to Security Manageability, Administration, and Integration, and Other Improvements, please complete the download form provided.