On the Eighth Day of 12.2, my DBA gave to me…

Index New Features

[hr width=”2px” color=”#2980b9″ style=”solid”][/hr]

December 19, 2016

On the Eighth day of 12.2, my DBA gave to me…

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
Null?
——–
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]

[link_button link=”https://viscosityna.com/resources/dba-resources/twelve-days-12-2/day-8-index/” size=”small” color=”#1CC6DA” align=”left”]Get Full Version[/link_button]