My Oracle Support Banner

Queries from inlist partitioned tables works very slow with statistics collected (Doc ID 1269010.1)

Last updated on JUNE 17, 2019

Applies to:

Oracle Database - Standard Edition - Version 10.2.0.3 to 11.2.0.1 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms


On production database 10.2.0.3 version, when statistics are collected the execution plan of some specific queries changes and the performance having the statistics collected is worst.

It is about queries using list partitioned tables works very slow when statistics are collected.
Same queries works ok when tables are regular tables with and without statistics.

The same code runs in serial in 2 minutes when statistics are deleted on 2 of 5 tables.
When statistics are gathered on these 2 tables even with estimate_pct=100 the query runs for 45 minutes.

STEPS
-----------------------
The issue can be reproduced at will when statistics are collected.

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