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 11.2.0.1 - Release: 10.2 to 11.2Information 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