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 laterOracle 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 |