Re: Better cardinality estimate when dialing optimizer_features_enable back

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_at_10046.ch

10046 Consulting GmbH
Schwarzackerstrasse 29
CH-8304 Wallisellen
Switzerland

Phone +41-(0)8400-10046
Cell +41 (0) 79 571 36 27
info_at_10046.ch
http://www.10046.ch

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
> 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
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l