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.