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@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
