Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Stefan Knecht <knecht.stefan@xxxxxxxxx>
  • To: nkodner@xxxxxxxxx
  • 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.

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
>
>
>

Other related posts: