In this Document

Applies to:

Symptoms

Queries on DBA_EXTENTS are much slower due to the presence of a merge join cartesian.
This can also influence the performance of script used to collect upgrade/migrate diagnostics as per instructions in:

Running script  on version 11.2.0.1 prior to upgrade, there was no performance issue.
The following SQL query from dbupgdiag.sql may run slowly  (~2 hours) after applying patchset 11.2.0.3.

Changes

An execution plan regression occurs if there are a large number of records in the X$KTFBUE table.  Gathering of dictionary or fixed object stats does not improve performance as this table is intentionally omitted from those packages.

Cause

The Cost Based Optimizer uses a cardinality estimate of 100,000 rows for this table, and the 11.2.0.3 execution plan is doing a full table scan. If you have a large number of extents, this query can take more than 1 hour to complete.
 There are a number of unpublished bugs open on slow perfomance for queries on DBA_EXTENTS
Specifically:

Solution

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";
   
      

References