Slow Query Performance Due to High Execution Counts of tbl$or$idx$part$num for Composite Partitioned Table (Doc ID 1950658.1)

Last updated on OCTOBER 18, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

When selecting from a composite partitioned table, the following recursive query is taking too long to execute.

SELECT distinct TBL$OR$IDX$PART$NUM("PMT_HIST"."ENTRY_RDFI", 0, 2, 2971,
 "RDFI_RTN")
FROM
(SELECT "OUTPUTTRAN1_"."RDFI_RTN" "RDFI_RTN" FROM
 "PMT_HIST"."OUTPUT_TRANSACTION_GROUP_RDFI" "OUTPUTTRAN1_" WHERE
 "OUTPUTTRAN1_"."RDFI_RTN"=:B1) ORDER BY 1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      163      0.10       0.10          0          0          0           0
Execute    163      0.13       0.13          0          0          0           0
Fetch      326    898.37     898.37          0     330727          0         163
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      652    898.61     898.61          0     330727          0         163

This issue is similar to bug 17572606. The bug does not cover the case of a
composite partitioned table (which is the customer's use case).

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms