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

Last updated on FEBRUARY 02, 2017

Applies to:

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.

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