The column groups will survive the deletion of statistics (since they are
containers for statistics). Statistics for the column groups will be
recalculated when you collect statistics.
Iggy
From: dimensional.dba@xxxxxxxxxxx
To: iggy_fernandez@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: 12c Optimizer and mis-calculations.
Date: Wed, 11 May 2016 09:55:27 -0700
Thanks, but before I collect 100% statistics I always delete statistics for a
table and it’s indexes. Been bitten by too many optimizer bugs that on
statistics collection it doesn’t seem to get rid of all the old statistics for
the object. Matthew ParkerChief TechnologistDimensional DBA425-891-7934
(cell)D&B 047931344CAGE 7J5S7Dimensional.dba@comcast.netView Matthew Parker's
profile on LinkedInwww.dimensionaldba.com From: Iggy Fernandez
[mailto:iggy_fernandez@xxxxxxxxxxx] ;
Sent: Wednesday, May 11, 2016 9:55 AM
To: dimensional.dba@xxxxxxxxxxx; Oracle L
Subject: RE: 12c Optimizer and mis-calculations. re: with or without adaptive
optimization in effect Even if adaptive optimization is turned off, there may
still be "extended column statistics" that were created when adaptive
optimization is in effect. You may want to get rid of them. A script is at
http://www.toadworld.com/platforms/oracle/w/wiki/11586.completely-disabling-adaptive-query-optimization-in-oracle-database-12c.
From: dimensional.dba@xxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Subject: 12c Optimizer and mis-calculations.
Date: Wed, 11 May 2016 09:11:56 -0700Had to share: At one of my clients I was
called in the middle of the night to look at a slow running query (9+ hours) on
12.1.0.2.0 on a X5 Exadata on a EDW. When I pulled the execution plan from the
cursor cache the calculated numbers were little off (look at rows/bytes/Cost)
by about 9 orders of magnitude as the query actually only generates 2.9 million
rows in about 6 minutes.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Id | Operation | Name
| Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ
Distrib
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------|
0 | INSERT STATEMENT |
| | | | 29P(100)| | | |
|| 1 | PX COORDINATOR |
| | | | | | | |
|| 2 | PX SEND QC (RANDOM) | :TQ20001
| 2556T| 15E| | 3089M (5)| 20:26:25 | Q2,01 | P->S | QC
(RAND) | This bad optimizer planning with estimated statistics or even 100%
statistics has been troublesome of late on 12c especially for out of the box
applications.The variations in calculation of overall size normally appears in
tempspc usage with and without adaptive planning in effect.The extreme
miscalculated values normally translate to queries that you kill otherwise they
run for days before they die some unnatural death on their own. The fix is
normally just to reverse the table order of the joins the optimizer chose for
the Fact versus the Dimension tables and then the query runs in minutes instead
of days. Matthew ParkerChief TechnologistDimensional DBA425-891-7934
(cell)D&B 047931344CAGE 7J5S7Dimensional.dba@comcast.netView Matthew Parker's
profile on LinkedInwww.dimensionaldba.com
