DBSNMP long running query

I noticed a long running query in one of my databases this morning.

  SELECT TABLESPACE,
           ROUND ( ( (max_size_mb - used_space + free_space_mb) / max_size_mb),
                  :"SYS_B_00")
         * :"SYS_B_01"
             AS pct_free
    FROM (  SELECT ts.tablespace_name                             TABLESPACE,
                     ROUND (SUM (NVL (fs.bytes, :"SYS_B_02")))
                   / :"SYS_B_03"
                   / :"SYS_B_04"
                       free_space_mb,
                   ROUND (SUM (df.bytes)) / :"SYS_B_05" / :"SYS_B_06" used_space,
                     ROUND (
                         SUM (
                             CASE autoextensible
                                 WHEN :"SYS_B_07" THEN df.maxbytes
                                 ELSE df.bytes
                             END))
                   / :"SYS_B_08"
                   / :"SYS_B_09"
                       AS max_size_mb,
                   ts.block_size
                       AS block_size
              FROM dba_data_files df
                   LEFT OUTER JOIN (  SELECT file_id, SUM (bytes) bytes
                                        FROM dba_free_space fs
                                    GROUP BY file_id) fs
                       ON df.file_id = fs.file_id
                   INNER JOIN dba_tablespaces ts
                       ON df.tablespace_name = ts.tablespace_name
             WHERE ts.contents = :"SYS_B_10"
          GROUP BY ts.tablespace_name, ts.block_size)
ORDER BY pct_free DESC

Its computing tablespace size. The average runtime is 272.03 sec, or 4.5 minutes. This isn’t right, its running in a few seconds in other databases.

I started looking at the query in OEM, and noticed almost all the execution time was being spent on a FTS of X$KTFBUE. 

A little googling turned up the following article:

Fixed Table x$ktfbue has not statistics

My issue appears to be the same, there are not statistics on teh X$KTFBUE table.

 SYS pp1oim1> exec print_table('select * from dba_tab_statistics where  table_name=''X$KTFBUE'' ');
OOWNER                        : SYS
TABLE_NAME                    : X$KTFBUE
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : FIXED TABLE
NUM_ROWS                      :
BLOCKS                        :
EMPTY_BLOCKS                  :
AVG_SPACE                     :
CHAIN_CNT                     :
AVG_ROW_LEN                   :
AVG_SPACE_FREELIST_BLOCKS     :
NUM_FREELIST_BLOCKS           :
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
IM_IMCU_COUNT                 :
IM_BLOCK_COUNT                :
IM_STAT_UPDATE_TIME           :
SCAN_RATE                     :
SAMPLE_SIZE                   :
LAST_ANALYZED                 :
GLOBAL_STATS                  :
USER_STATS                    :
STATTYPE_LOCKED               :
STALE_STATS                   :
SCOPE                         : SHARED
??????????????????

So gather stats on the table

 
 SYS pp1oim1> exec DBMS_STATS.GATHER_TABLE_STATS('SYS','X$KTFBUE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.24
 SYS pp1oim1> exec print_table('select * from dba_tab_statistics where  table_name=''X$KTFBUE'' ');
OOWNER                        : SYS
TABLE_NAME                    : X$KTFBUE
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : FIXED TABLE
NUM_ROWS                      : 158077
BLOCKS                        :
EMPTY_BLOCKS                  :
AVG_SPACE                     :
CHAIN_CNT                     :
AVG_ROW_LEN                   : 66
AVG_SPACE_FREELIST_BLOCKS     :
NUM_FREELIST_BLOCKS           :
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
IM_IMCU_COUNT                 :
IM_BLOCK_COUNT                :
IM_STAT_UPDATE_TIME           :
SCAN_RATE                     :
SAMPLE_SIZE                   : 158077
LAST_ANALYZED                 : 09-sep-2019 10:32:52
GLOBAL_STATS                  : YES
USER_STATS                    : NO
STATTYPE_LOCKED               :
STALE_STATS                   :
SCOPE                         : SHARED
??????????????????

With the new statistics the query finishes in a few seconds.