Now I'm thoroughly confused. Since last night's analyze, I now get the same (poor) cardinality estimate regardless of optimizer_features_enable. However, in my QA system, which was updated from production, I can replicate the problem. Here are my table/column/index stats in both production and QA. http://spreadsheets.google.com/ccc?key=0ArUTHdqN0J--dGtCLUZ1ZmhXMkpNUUpRVjZmcE9QLXc&hl=en The real issue behind all of this is queries against letter_bin by prty_id. They are taking too long and scanning too many blocks when I have the poor cardinality estimate. With the correct cardinality estimates, I get subsecond response. Otherwise, they take anywhere from 1 to 30 seconds. Here's a sample 10046 http://www.neilkodner.com/10046_trace.txt and a 10053 http://www.neilkodner.com/10053_trace.txt And finally, my analyze command DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>USER,CASCADE=>TRUE,method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',DEGREE=>4); On Mon, Nov 23, 2009 at 7:59 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote: > I would second this. Get a 10053 trace from both versions and look at > the trace file in your favorite graphical diff tool. Look for the > cardinality/density calculation. I would do this for the full query > as well as your one table test case . > > What are the two execution plans of the full query? > > On Mon, Nov 23, 2009 at 3:55 PM, Stefan Knecht <knecht.stefan@xxxxxxxxx> > wrote: > > Just a quick thought... > > > > One thing you can try is to take a 10053 trace, and check for the values > of > > the various underscore (optimizer features), as well as bugfixes listed > in > > the tracefile, for both values for optimizer_features_enable. Any > difference > > you may spot may lead you to some more information. > > -- > Regards, > Greg Rahn > http://structureddata.org >