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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 14 Aug 2019 07:38:18 -0400
Message-ID: <014e01d55294$c4de0620$4e9a1260$_at_rsiz.com>



And if you are spilling to temp, you probably should check your setting of TEMP_UNDO_ENABLED. (since you are 12c).  

In addition to JL’s mention of storage indexes, if you have plans that consistently seem to want to scan this 2GB table, especially with similar predicates, you may want to implement a zonemap on that table. IF you do that, then it is likely a one-time reload of this table after adding linear attribute clustering to match the leading edge (if not all of) the zonemap would be useful.  

Zonemaps do not require the “warm-up” and competition between storage indexes active, but similarly prevent the transmission of data from the storage layer (though possibly not as efficiently IN the storage layer itself as storage indexes, that is very unlikely to be the problem.)  

I may have missed it, but I saw only 12c, not whether 12.1 or 12.2. My opinion is that if you are on 12.1, run, do not walk, to 12.2.  

And that reminds me to have you check whether you are in local or shared undo mode. If you are NOT in local mode and you are at least 12.2, you probably should be using local undo mode. An excellent and short note on that is on Tim Hall’s excellent site:  

https://oracle-base.com/articles/12c/multitenant-local-undo-mode-12cr2    

Good luck.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of kunwar singh Sent: Tuesday, August 13, 2019 7:15 AM
To: dimensional.dba_at_comcast.net
Cc: ORACLE-L
Subject: 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
Received on Wed Aug 14 2019 - 13:38:18 CEST

Original text of this message