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;

• 红色有效的其他都无效