Re: execution plan and poor statistics
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 12 Apr 2011 16:33:46 +0100
Message-ID: <WaGdnZ5gualI7TnQnZ2dnUVZ8oadnZ2d_at_bt.com>
"Ceeper" <nosp_at_m.e> wrote in message news:invpnv$nea$1_at_online.de...
>
> questions:
> 1. The above example preferes indexed access. How can oracle 11g be
> advised to always use the index? The used sql is fix, the existing
> application should not be changed.
>
> 2. Assuming the above example uses different times for operations. We do
> not know, when table is full or empty. How can oracle be advised to use
> the index.
>
Date: Tue, 12 Apr 2011 16:33:46 +0100
Message-ID: <WaGdnZ5gualI7TnQnZ2dnUVZ8oadnZ2d_at_bt.com>
"Ceeper" <nosp_at_m.e> wrote in message news:invpnv$nea$1_at_online.de...
>
> questions:
> 1. The above example preferes indexed access. How can oracle 11g be
> advised to always use the index? The used sql is fix, the existing
> application should not be changed.
>
> 2. Assuming the above example uses different times for operations. We do
> not know, when table is full or empty. How can oracle be advised to use
> the index.
>
Since you've mentioned 11g and fixed SQL - use SQL baselines.
Use dbms_spm.load_plans_from_cursor_cache() to capture an SQL Baseline for each of the critical SQL statements when good plans appear, enable them, and that's the job done. There's an example of a method here ( http://jonathanlewis.wordpress.com/2011/01/12/fake-baselines/ ) that doesn't quite match your position - but I think a couple of the comments should be relevant.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.comReceived on Tue Apr 12 2011 - 10:33:46 CDT