Gathering Dictionary Statistics Post TTS Import to Improve Statistics Table Import Performance: What Specific Tables to Gather Statistics On, Rather Than Gathering Statistics on All Tables (Doc ID 2452739.1)

Last updated on JULY 20, 2024

Applies to:

Goal

A Transportable TableSpace (TTS) Data Pump import can be made faster by excluding object statistics and later importing a statistics table. This statistics table import can take a long time itself, but importing the statistics table can be made faster by gathering generic dictionary statistics via DBMS_STATS.GATHER_DICTIONARY_STATS. However, even the process of generically gathering dictionary statistics can still consume valuable time. This document presents three key tables that can be specifically targeted to gather statistics on in lieu of running DBMS_STATS.GATHER_DICTIONARY_STATS, such that stats table import performance is vastly improved without having to wait for DBMS_STATS.GATHER_DICTIONARY_STATS to complete.

Also, this document shows that there are several key clustered indexes on dictionary tables that do not have their statistics updated when statistics are collected on the individual tables discussed above, nor are the statistics updated when DBMS_STATS.GATHER_DICTIONARY_STATS is run or statistics gathered for the SYS schema. In all three cases, the statistics remain stale.

This information is being shared in direct collaboration with Oracle development.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.