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

How good is the cardinality estimate?

Regards

Lothar
Am 26.08.2019 um 05:20 schrieb Jack van Zanen:
> 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_at_jlcomp.demon.co.uk <mailto:jonathan_at_jlcomp.demon.co.uk>> 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_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>
> <oracle-l-bounce_at_freelists.org
> <mailto:oracle-l-bounce_at_freelists.org>> on behalf of kunwar singh
> <krishsingh.111_at_gmail.com <mailto:krishsingh.111_at_gmail.com>>
> 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 





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