dba_extents 查询缓慢

Following are possible workarounds to improve the performance

1. Gathering stats on the X$KTFBUE table using the following command:

   EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'X$KTFBUE');

   
   For more information on gathering fixed table statistics see:

   Document? 798257.1?GATHER_FIXED_OBJECTS_STATS Considerations

   ?
   
   OR

2. alter session set "_optimizer_cartesian_enabled" = false;

   
   OR

3. alter session set "_smm_auto_cost_enabled" = false;

   
   OR
4. Disable fix for unpublished Bug Bug 11814428: POOR CARDINALITY ESTIMATE FROM JOIN WITH A UNION-ALL VIEW:

   alter session set "_FIX_CONTROL" = "11814428:0";

?SELECT ?owner , segment_name , segment_type ,block_id,blocks
? ? FROM ?dba_extents
? ?WHERE ?file_id = 2612

• Parameter changes cause optimization differences
  
  Parameters such as 'optimizer_features_enable' can have a large affect on dictionary queries since they make extensive use of views and union views and the optimization of these structures can be modified. It is recommended to use the default setting for OPTIMIZER_FEATURES_ENABLE.? Some view related parameters covered by OPTIMIZER_FEATURES_ENABLE include:

  alter session set "_COMPLEX_VIEW_MERGING" = false;
  alter session set "_PUSH_JOIN_PREDICATE" = false;
  alter session set "_PUSH_JOIN_UNION_VIEW" = false;

• 红色有效的其他都无效