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

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: Oracle L <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 26 Aug 2019 08:45:54 +0000


I have a DW where I see queries using index and Nested loops even after I 
gathered system stats using EXADATA  and bumping up the parameter MBR for 
session to 256/512.

When you say the MBR do you mean the MBRC value in the system stats, or the 
db_file_multiblock_read_count ? The former will affect the costing, the latter 
won't.

A big problem that the optimizer has with Exadata and the CBO is that it has no 
idea how effective offloading and Bloom filtering will be, so the effective it 
has no idea about the effective throughput of a smart scan.  All it's got to go 
on is the 3 I/O related stats it sees from called 
gather_system_stats('Exadata'). You can change these stats to reflect the 
reality you want Oracle to believe.

See https://jonathanlewis.wordpress.com/2019/08/14/gather_system_stats/

There's no reason why you can't set the ioseektim to (say) 1 millisecond  from 
10 milliseconds, and the iotfrspeed to 400MB/s from 200MB/s if that's a more 
realistic indication of the rate at which this class of query operates.  (Or 
maybe, to "penalise" single block reads, set the ioseektim to something larger 
than 10, while making the MBRC very large and the ittfrspeed 1GB/s).  Just do 
some arithmetic to start with that tells you the speed of a single block read 
and a multiblock read (of size MBRC - the statistic) and see where they have to 
be to make your query do the tablescans you want.

Of course Lothar's comment about cardinality estimates is the first thing to 
check - are they in the right ball park.
There's also the option for creating a patch or fake profile that tells Oracle 
to use a tablescan for the query.


Regards
Jonathan Lewis






________________________________________
From: Jack van Zanen <jack@xxxxxxxxxxxx>
Sent: 26 August 2019 04:20
To: Jonathan Lewis
Cc: Oracle L
Subject: Re: Bad execution plan after migrating to exadata ( 12c) from 
non-exadata (11g)

Sorry for Hijacking this thread

I have a DW where I see queries using index and Nested loops even after I 
gathered system stats using EXADATA  and bumping up the parameter MBR for 
session to 256/512.
However a fts hint on the biggest table results in a much faster execution plan 
and almost 100% saving on I/O through the exadata smart scan.
This code is created by reporting tool so not sure if we can tune it this way 
going forward. I would really like the Optimizer to become more aware.

Is there anything else that can make the optimizer more aware and make better 
decisions?
I have analyzed the tables with the 12C defaults as well as created histograms 
(for all columns size auto & for all columns size 254)

I can always make the index invisible for this query but that may make  other 
processes slow so requires quite a bit of investigation.Plus the database 
objects are maintained by vendor so we can only suggest and than they will have 
to "hotfix" it


Jack van Zanen


-------------------------
This e-mail and any attachments may contain confidential material for the sole 
use of the intended recipient. If you are not the intended recipient, please be 
aware that any disclosure, copying, distribution or use of this e-mail or any 
attachment is prohibited. If you have received this e-mail in error, please 
contact the sender and delete all copies.
Thank you for your cooperation


On Tue, Aug 13, 2019 at 7:25 PM Jonathan Lewis 
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> wrote:

By gathering stats with the EXADATA option you've told the optimizer that a 
typical single block read will take about 10ms while a 1MB multiblock read will 
take about 15ms, so it's not surprising that you will have execution plans that 
switch from indexed access and nested loop to full tablescan and hash joins.

Unfortunately the cost of I/O to temp becomes highly visible on EXADATA when a 
hash join spills to disc, and the implied speed of tablescan is only helpful if 
you can push predicates down to storage and take advantage of storage indexes.  
(Or if you have purchase the IN-Memory option and have the right sort of 
queries that can do vector transformations).

Generic strategy point 1:
Look at the "table_cached_blocks" preference for gathering table stats. This 
can help to give Oracle a much better idea of the quality of an index by 
allowing it do derive a better value for the clustering_factor.

Generic strategy point 2:
Look for places where the optimizer's estimate of cardinality is HIGHER than it 
ought to be and find out why - as higher cardinalities tend to push the 
optimizer away from indexes/nested loops.

Generic strategy point 3:
If Exadata is wasting a lot of resources on tablescans that clearly be indexed 
access paths you consider deleting system stats, or faking some system stats 
that promise less about tablescan speed.

I note you said you had an 11g outline - presumably this means that left to 
itself on 11g this query picked the "wrong" path - which means nothing has 
really changed.

Regards
Jonathan Lewis

________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf 
of kunwar singh <krishsingh.111@xxxxxxxxx<mailto:krishsingh.111@xxxxxxxxx>>
Sent: 13 August 2019 07:13
To: ORACLE-L
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
--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l


Other related posts: