While playing around 12.1.0.2 db, I ran my usual script to check tablespace usage details and it took ~59 secs (Elapsed: 00:00:59.02), which was high.
Ran the sql using “gather_plan_statistics” hint and below lines in the execution plan were of interest –
| 34 | HASH GROUP BY | | 1 | 6 | 3 |00:00:58.68 | 129K| 54801 | 1160K| 1160K| 758K (0)| | 35 | VIEW | DBA_FREE_SPACE | 1 | 21019 | 13 |00:00:00.01 | 129K| 54801 | | | | | 36 | JOIN FILTER USE | :BF0000 | 1 | | 13 |00:00:00.01 | 129K| 54801 | | | | | 37 | UNION-ALL | | 1 | | 19 |00:00:00.01 | 129K| 54801 | | | | | 38 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 7 | 0 | | | | | 39 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 7 | 0 | | | | |* 40 | INDEX FULL SCAN | I_FILE2 | 1 | 4 | 4 |00:00:00.01 | 1 | 0 | | | | |* 41 | TABLE ACCESS CLUSTER | FET$ | 4 | 1 | 0 |00:00:00.01 | 6 | 0 | | | | |* 42 | INDEX UNIQUE SCAN | I_TS# | 4 | 1 | 4 |00:00:00.01 | 2 | 0 | | | | |* 43 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | |* 44 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | | | 45 | NESTED LOOPS | | 1 | 10 | 10 |00:00:00.01 | 25 | 0 | | | | | 46 | NESTED LOOPS | | 1 | 10 | 10 |00:00:00.01 | 21 | 0 | | | | |* 47 | TABLE ACCESS FULL | TS$ | 1 | 4 | 4 |00:00:00.01 | 9 | 0 | | | | |* 48 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 4 | 2 | 10 |00:00:00.01 | 12 | 0 | | | | |* 49 | INDEX UNIQUE SCAN | I_FILE2 | 10 | 1 | 10 |00:00:00.01 | 4 | 0 | | | | | 50 | NESTED LOOPS | | 1 | 21007 | 9 |00:00:57.85 | 129K| 54801 | | | | |* 51 | HASH JOIN | | 1 | 7 | 9 |00:00:00.01 | 13 | 0 | 1115K| 1115K| 471K (0)| | 52 | NESTED LOOPS | | 1 | 7 | 9 |00:00:00.01 | 12 | 0 | | | | | 53 | NESTED LOOPS | | 1 | 32 | 9 |00:00:00.01 | 11 | 0 | | | | |* 54 | TABLE ACCESS FULL | TS$ | 1 | 4 | 4 |00:00:00.01 | 9 | 0 | | | | |* 55 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 4 | 8 | 9 |00:00:00.01 | 2 | 0 | | | | | 56 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 9 | 2 | 9 |00:00:00.01 | 1 | 0 | | | | |* 57 | INDEX FULL SCAN | I_FILE2 | 1 | 4 | 4 |00:00:00.01 | 1 | 0 | | | | |* 58 | FIXED TABLE FULL | X$KTFBUE | 9 | 3125 | 9 |00:00:58.67 | 129K| 54801 | | | | | 59 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 21 | 0 | | | | | 60 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 21 | 0 | | | | Note ----- - dynamic statistics used: dynamic sampling (level=2) - this is an adaptive plan - 3 Sql Plan Directives used for this statement
The E-Rows for X$KTFBUE is 3125 whereas A-Rows 9. Checking the stats on the table
SYS@orcl:1> set serveroutput on
SYS@orcl:1>
SYS@orcl:1>
SYS@orcl:1> exec print_table('select * from dba_tab_statistics where table_name=''X$KTFBUE'' ');
OWNER : 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 :
SAMPLE_SIZE :
LAST_ANALYZED :
GLOBAL_STATS :
USER_STATS :
STATTYPE_LOCKED :
STALE_STATS :
SCOPE : SHARED
-----------------
Looking at the directive details shows missing details on “F(SYS.TS$) – F(SYS.UNDO$) – F(SYS.X$KTFBUE)”
SYS@orcl:1> exec print_table('select * from DBA_SQL_PLAN_DIR_OBJECTS where owner=''SYS'' and object_name=''X$KTFBUE'' ');
DIRECTIVE_ID : 16383234857226438245
OWNER : SYS
OBJECT_NAME : X$KTFBUE
SUBOBJECT_NAME :
OBJECT_TYPE : TABLE
NOTES : NONONOYES
-----------------
PL/SQL procedure successfully completed.
SYS@orcl:1> exec print_table('select * from DBA_SQL_PLAN_DIRECTIVES where DIRECTIVE_ID=''16383234857226438245'' ');
DIRECTIVE_ID : 16383234857226438245
TYPE : DYNAMIC_SAMPLING
ENABLED : NO
STATE : USABLE
AUTO_DROP : YES
REASON : JOIN CARDINALITY MISESTIMATE
CREATED : 28-MAY-16 09.21.20.000000 PM
LAST_MODIFIED : 28-MAY-16 11.12.20.000000 PM
LAST_USED : 28-MAY-16 10.21.29.000000000 PM
NOTES : MISSING_STATSNO{F(SYS.TS$) - F(SYS.UNDO$) - F(SYS.X$KTFBUE)}
-----------------
PL/SQL procedure successfully completed.
Lets gather fixed object stats :-
SYS@orcl:1> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; PL/SQL procedure successfully completed. Elapsed: 00:02:47.92
Ran the script again and it still took the same time, so checked the stats on X$KTFBUE and its still missing
SYS@orcl:1> exec print_table('select * from dba_tab_statistics where table_name=''X$KTFBUE'' ');
OWNER : 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 :
SAMPLE_SIZE :
LAST_ANALYZED :
GLOBAL_STATS :
USER_STATS :
STATTYPE_LOCKED :
STALE_STATS :
SCOPE : SHARED
-----------------
PL/SQL procedure successfully completed.
Reviewed few documents
Query Against DBA_EXTENTS Performs Slowly After Upgrade to 11.2.0.3 (Doc ID 1453425.1)
Bug 5259025 – The fixed table x$ktfbue has no statistics (Doc ID 5259025.8)
Gathered the stats on fixed table x$ktfbue
SYS@orcl:1> exec DBMS_STATS.GATHER_TABLE_STATS('SYS','X$KTFBUE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:34.09
SYS@orcl:1> exec print_table('select * from dba_tab_statistics where table_name=''X$KTFBUE'' ');
OWNER : SYS
TABLE_NAME : X$KTFBUE
PARTITION_NAME :
PARTITION_POSITION :
SUBPARTITION_NAME :
SUBPARTITION_POSITION :
OBJECT_TYPE : FIXED TABLE
NUM_ROWS : 9581
BLOCKS :
EMPTY_BLOCKS :
AVG_SPACE :
CHAIN_CNT :
AVG_ROW_LEN : 47
AVG_SPACE_FREELIST_BLOCKS :
NUM_FREELIST_BLOCKS :
AVG_CACHED_BLOCKS :
AVG_CACHE_HIT_RATIO :
SAMPLE_SIZE : 9581
LAST_ANALYZED : 28-may-2016 22:55:47
GLOBAL_STATS : YES
USER_STATS : NO
STATTYPE_LOCKED :
STALE_STATS :
SCOPE : SHARED
-----------------
Re-ran the sql script and Elapsed: 00:00:00.41
Learn. Share. Repeat. 
can you share the “print_table” procedure code?
can you send me the “print_table” procedure code?
at ata.rehman70@gmail.com
Take a look at https://oraclespin.com/2010/10/31/print_table-utility-procedure/