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

Hi Dimensional DBA,
Thanks for suggestions, reviewing from your points too . Yes outline data via DBMS_XPLAN.DISPLAY(format=>'ADVANCED') was used here. i am comparing the parameters and the patch level now.

On Tue, Aug 13, 2019 at 3:00 AM <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
>

-- 
Cheers,
Kunwar

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