Hi Andy,
1. I would focus on the part of your old plan that made it so good - it’s
usually going to be the driving table and filters. What statistics are
necessary for your new system to see that starting from there is a good
idea, or what can you do to make it see that starting from where it wants
to is a bad idea?
[Kunwar] Thanks Andy. Currently i am pursuing this route only. Is 10053 the
only best to get exacts of what statistics are necessary for new system to
see from where to start. Or will I have to go the crude way of checking the
stats through the dictionary ways ( which is a time taking option) .
Actually we are suspecting that there could be more such cases, so pursuing
why optimizer isnt able to figure that out itself.
2. We’re also dealing with a version change, 12c would include many “fixes”
that completely removed some query transformations, is that the case here?
If you hinted for the old plan (using the full outline), would you even be
able to get it? It’s a good idea to make sure you’re running the latest
patches where these sorts of things are fixed at a more appropriate
granularity.
[Kunwar] I will check on the query transformations point.
On Tue, Aug 13, 2019 at 4:00 AM Andy Sayer <andysayer@xxxxxxxxx> wrote:
A cost of 90K taking 15ms sounds like a situation where multiple wrongs
made a right. If you want to get that same plan then sql plan management
(dbms_spm) is the way to do it.
Gathering system statistics is a sure fire way to get new plans when you
move on to Exa. You probably do want new plans as Exa is so much different
to non-Exa. But this extra variable (well, many variables) can easily
disrupt the balance of your many wrongs.
I would focus on the part of your old plan that made it so good - it’s
usually going to be the driving table and filters. What statistics are
necessary for your new system to see that starting from there is a good
idea, or what can you do to make it see that starting from where it wants
to is a bad idea?
We’re also dealing with a version change, 12c would include many “fixes”
that completely removed some query transformations, is that the case here?
If you hinted for the old plan (using the full outline), would you even be
able to get it? It’s a good idea to make sure you’re running the latest
patches where these sorts of things are fixed at a more appropriate
granularity.
Hope this gets you started,
Andy
On Tue, 13 Aug 2019 at 08:04, <dimensional.dba@xxxxxxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On
Behalf Of *kunwar singh
*Sent:* Monday, August 12, 2019 11:13 PM
*To:* ORACLE-L <oracle-l@xxxxxxxxxxxxx>
*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
