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. Cheers Stefan ========================= Stefan P Knecht CEO & Founder s@xxxxxxxx 10046 Consulting GmbH Schwarzackerstrasse 29 CH-8304 Wallisellen Switzerland Phone +41-(0)8400-10046 Cell +41 (0) 79 571 36 27 info@xxxxxxxx http://www.10046.ch ========================= On Mon, Nov 23, 2009 at 11:39 PM, neil kodner <nkodner@xxxxxxxxx> wrote: > OLTP system. Optimizer statistics are all set to defaults. System > statistics are present. > > table letter_bin has about 4.5 M rows. About 3M rows have a value for > party_id. There are about 250,000 party_ids present in letter_bin and > they're not evenly distributed. > > Lately, when queries access letter_bin by way of the index on party_id, we > get degraded performance. > > I thought I was doing an OK job managing optimizer statistics on this table > up to this point. I've tried with dbms_stats.auto_sample_size as well as > estimate_percent=>100 and I'm still not getting the cardinality estimate > that I hoped to receive. > > Only today did I try setting optimizer_features_enable='10.1.0' and then I > got a good cardinality estimate. No other changes were made. The query is > back to performing at lightning-fast speed after setting the > optimizer_features_enable. > > A transcript for my session can be found at > http://www.neilkodner.com/cardinality.txt > > I've been reading article after article, whitepaper after whitepaper on > cardinality-based-tuning, but this has me stumped. > > Here are some details on my environment > > optimizer_dynamic_sampling integer 2 > optimizer_features_enable string 10.2.0.4 > optimizer_index_caching integer 0 > optimizer_index_cost_adj integer 100 > optimizer_mode string ALL_ROWS > optimizer_secure_view_merging boolean TRUE > db_file_multiblock_read_count integer 16 > > > > SQL> select version from v$instance; > select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN'; > VERSION > ----------------- > 10.2.0.4.0 > > SQL> > > PNAME PVAL1 > ------------------------------ ---------- > CPUSPEEDNW 774.281 > IOSEEKTIM 8.049 > IOTFRSPEED 15136.032 > SREADTIM 4.004 > MREADTIM 1.547 > CPUSPEED 777 > MBRC 5 > MAXTHR 5261312 > SLAVETHR > > > > -- > //www.freelists.org/webpage/oracle-l > > >