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

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