Re: Better cardinality estimate when dialing optimizer_features_enable back
Date: Tue, 24 Nov 2009 00:55:02 +0100
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.
Stefan P Knecht
CEO & Founder
10046 Consulting GmbH
Cell +41 (0) 79 571 36 27
On Mon, Nov 23, 2009 at 11:39 PM, neil kodner <nkodner_at_gmail.com> 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
> A transcript for my session can be found at
> 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';
> PNAME PVAL1
> ------------------------------ ----------
> CPUSPEEDNW 774.281
> IOSEEKTIM 8.049
> IOTFRSPEED 15136.032
> SREADTIM 4.004
> MREADTIM 1.547
> CPUSPEED 777
> MBRC 5
> MAXTHR 5261312