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 ;) )
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 .
- 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.