My Oracle Support Banner

Performance Issue Of FTS Against Hash Partitioned Table With A High Column Number (Doc ID 1540507.1)

Last updated on NOVEMBER 07, 2023

Applies to:

Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

Symptoms

Working with partitioned table, which is around 1.2GB in size and around 150000 blocks in all partitions.
Following select with simple where condition takes 35 minutes to complete:

 
This FTS scan has 4837392 consistent gets, which is much more that around 150000 blocks which table has.
Also physical reads 3253414 blocks, which means 25 GB of data, is around 21 times higher than whole table size.

This happens using a table with a high column number, for example, here the table has column number 183 of 422 columns.

Changes

 

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.