Queries from inlist partitioned tables works very slow with statistics collected
Last updated on MARCH 27, 2014
Applies to:Oracle Server - Standard Edition - Version: 10.2.0.3 to 188.8.131.52 - Release: 10.2 to 11.2
Information in this document applies to any platform.
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.
The issue can be reproduced at will when statistics are collected.
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