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 Parker
Chief Technologist
Dimensional DBA
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba@xxxxxxxxxxx
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew
Parker's profile on LinkedIn
www.dimensionaldba.com <http://www.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 -0700
Had 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 Parker
Chief Technologist
Dimensional DBA
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7
Dimensional.dba@xxxxxxxxxxx
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew
Parker's profile on LinkedIn
www.dimensionaldba.com <http://www.dimensionaldba.com/>
