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

From: kunwar singh <krishsingh.111_at_gmail.com>
Date: Tue, 13 Aug 2019 02:13:14 -0400
Message-ID: <CAJSrDUrs2KMKs-pNNGFdCVp0Pa+P_dLHZL3t_2KegnpkUwRQ-w_at_mail.gmail.com>



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
Received on Tue Aug 13 2019 - 08:13:14 CEST

Original text of this message