Re: Spm used but plan is different

From: Andy Sayer <>
Date: Wed, 22 May 2019 19:17:56 +0100
Message-ID: <>

Hi Kunwar,

When you say you’re copying the plan from 11g, does this mean you’re creating a baseline table and exporting/importing it to the 12.2 DB? Or are you gathering the baseline before you upgrade?

Does the SQL still have the same exact_matching_signature? If so, the optimizer trace will help you out.

Typically the plan in the baseline is no longer legal - perhaps it was relying on a transformation that has been removed as part of a bug fix, perhaps the indexes have changed.

Hope that helps,

On Wed, 22 May 2019 at 18:23, kunwar singh <> wrote:

> hi listers
> We are migrating from 11g to 12.2
> And as part of performance testing we found that few plans regressed and I
> tried to copy the plan from 11g using spm .
> Spm is getting used but plans are different and run times are bad still.
> Same is the case by using ofe of .
> Don’t see any helpful notes on mos for the steps I should take to resolve
> why plans are not same . And there is no single common difference in the
> plans which is causing this issue .
> So wanted to know what your methodology in finding why spm is not using
> the same plan . Same is the case with sql profile and it getting used
> my question is more in general and not query specific .
> --
> Cheers,
> Kunwar

Received on Wed May 22 2019 - 20:17:56 CEST

Original text of this message