My Oracle Support Banner

Query Against DBA_EXTENTS Performs Slowly After Upgrade to 11.2.0.3 (Doc ID 1453425.1)

Last updated on NOVEMBER 07, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

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:

<Document 556610.1> Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) .

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.