Re: Bad execution plan after migrating to exadata ( 12c) from non-exadata (11g)

A cost of 90K taking 15ms sounds like a situation where multiple wrongs made a right. If you want to get that same plan then sql plan management (dbms_spm) is the way to do it.

Gathering system statistics is a sure fire way to get new plans when you move on to Exa. You probably do want new plans as Exa is so much different to non-Exa. But this extra variable (well, many variables) can easily disrupt the balance of your many wrongs.

I would focus on the part of your old plan that made it so good - it’s usually going to be the driving table and filters. What statistics are necessary for your new system to see that starting from there is a good idea, or what can you do to make it see that starting from where it wants to is a bad idea?

We’re also dealing with a version change, 12c would include many “fixes” that completely removed some query transformations, is that the case here? If you hinted for the old plan (using the full outline), would you even be able to get it? It’s a good idea to make sure you’re running the latest patches where these sorts of things are fixed at a more appropriate granularity.

Hope this gets you started,
Andy

On Tue, 13 Aug 2019 at 08:04, <dimensional.dba_at_comcast.net> wrote:

> What specific version of 11g down to patches applied? (opatch lsinventory)
>
> What specific version of 12c on Exadata down to patches applied? (opatch
> lsinventory)
>
> Did you port the plan baselines/outln data to 12c?
>
> We could simply start with the query and execution plan on 12c side to see
> what it may be doing. We would also need a pfile created from the spfile to
> see what parameters you have set on the database.
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> *On
> Behalf Of *kunwar singh
> *Sent:* Monday, August 12, 2019 11:13 PM
> *To:* ORACLE-L <oracle-l_at_freelists.org>
> *Subject:* Bad execution plan after migrating to exadata ( 12c) from
> non-exadata (11g)
>
>
>
> Hi Listers,
>
>
>
> How to approach this? Looking for a approach in general when it comes to
> check plan issues when migrating to exadata and not something to this query
> ( but wont mind any insights into it either ;) )
>
>
>
> *Issue:*
>
> with outline data from 11g(in 12c exa DB)
> - cost ~90k, fast, elapsed time about 15 ms.
> - doing index range scan on a index on a 2GB table .
>
> 12c exadata
> - cost ~6k , slower , elapsed time about 4 seconds.
> - FTS on the 2GB table and from sql monitor report time is spent on
> reading it only/processing the hash join on it.
> - execution plan is having a view VW_NSO_1
>
>
>
> *Few details:*1. I have already gathered stats on all tables/indexes
> 2. Have gathered system statistics with 'EXADATA'
> 3. Don't have the access to source 11g DB . getting it will take some
> time.
>
> Will post redacted version of the SQL & the execution plan ( if you prefer
> to look at it ) as account is very strict about security.
>
>
>
>
>
>
>
> --
>
> Cheers,
> Kunwar
>

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