Re: plan stability
From: DEV <sdpant_at_gmail.com>
Date: Mon, 14 Sep 2009 09:59:13 -0700 (PDT)
Message-ID: <5f050e93-9015-48c4-9028-43fcff3f4ae3_at_z3g2000prd.googlegroups.com>
On Sep 14, 5:17 am, vsevolod afanassiev
<vsevolod.afanass..._at_gmail.com> wrote:
> Outlines are tricky, sometimes it is difficult to get them to work.
> Have you tried to export statistics from SchemaA and import into
> SchemaB?
> With identical statistics the plans should be identical.
>
> You are on 10.2.0.3, I assume you use standard Oracle job for
> collecting
> statistics (running as DBMS_SCHEDULER job). This job creates
> histograms.
> I also assume the application uses bind variables. In this case there
> could be instances
> of that 'great' feature known as bind variable peeking.
>
> Do you have schema name in the statements or not? I.e. is it
>
> SELECT * FROM SCHEMAA.TABLEA or
> SELECT * FROM TABLEA
I did a import on schemaB and the CPU load didn't go up. We switch between schemaA and schemaB alternately on a daily basis. I think it bind variable peeking thingy that is what is going on and for that reason I wanted to get the outlines working. I might be able to generate the outlines from this and keep it disabled till we have a issue next time. We don't append schema name in our database calls. Thanks for your input. Received on Mon Sep 14 2009 - 11:59:13 CDT
Date: Mon, 14 Sep 2009 09:59:13 -0700 (PDT)
Message-ID: <5f050e93-9015-48c4-9028-43fcff3f4ae3_at_z3g2000prd.googlegroups.com>
On Sep 14, 5:17 am, vsevolod afanassiev
<vsevolod.afanass..._at_gmail.com> wrote:
> Outlines are tricky, sometimes it is difficult to get them to work.
> Have you tried to export statistics from SchemaA and import into
> SchemaB?
> With identical statistics the plans should be identical.
>
> You are on 10.2.0.3, I assume you use standard Oracle job for
> collecting
> statistics (running as DBMS_SCHEDULER job). This job creates
> histograms.
> I also assume the application uses bind variables. In this case there
> could be instances
> of that 'great' feature known as bind variable peeking.
>
> Do you have schema name in the statements or not? I.e. is it
>
> SELECT * FROM SCHEMAA.TABLEA or
> SELECT * FROM TABLEA
I did a import on schemaB and the CPU load didn't go up. We switch between schemaA and schemaB alternately on a daily basis. I think it bind variable peeking thingy that is what is going on and for that reason I wanted to get the outlines working. I might be able to generate the outlines from this and keep it disabled till we have a issue next time. We don't append schema name in our database calls. Thanks for your input. Received on Mon Sep 14 2009 - 11:59:13 CDT