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

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - 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

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