Re: Statistics Problem on partitioned table

  • From: David Fitzjarrell <oratune@xxxxxxxxx>
  • To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, Mohamed Houri <mohamed.houri@xxxxxxxxx>
  • Date: Wed, 2 Oct 2013 10:32:14 -0700 (PDT)

I'll agree with Jonathan as I've seen Oracle generate 'garbage' histograms.  
Basically I did what he suggests, create your own histogram to replace the 
errant one (or add one if you need to).

David Fitzjarrell

 

________________________________
 From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
To: Mohamed Houri <mohamed.houri@xxxxxxxxx> 
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx> 
Sent: Wednesday, October 2, 2013 5:55 AM
Subject: Re: Statistics Problem on partitioned table
  


Okay -  got it.

The simple answer is that you're missing a histogram (or the histogram that 
Oracle acquired id garbage.
Most efficient trick - you've run the query about which values appear how 
often - create a frequency histogram for the 250 most popular (or less if 
appropriate), include the low and high values put in one extra value with 
the number of rows x 2 that you want the optimizer to consider for all 
other values then call set_table_stats.

See my latest article for allthingsoracle - published about 24 hours ago, 
by coincidence.


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

----- Original Message ----- 
From: "Mohamed Houri" <mohamed.houri@xxxxxxxxx>
To: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, October 02, 2013 12:39 PM
Subject: Re: Statistics Problem on partitioned table


Jonathan,

I am sorry I may have not been clear but there are in fact 721,699 and that
is what I showed above

*select per_ind, count(1) cnt from XXX_PER_YYY group by per_ind;*

721,699 rows …



   PER_IND        CNT

---------- ----------

14820567             2

14820568             2

14823592             2

14888565             2

14332136             2

13565375             2

13617240            2

…

13546549         92

  13546573         92

  13546630         92

  13546881         92

  13546890         92

  13546911         92

…

  13546914         92…

….

And so on until I arrived at the end (721,699)

13831389             130

13831395             130

13831404             130

13831451             130

0                            6119655   ----> this is my predicate



It is when I count how many distinct CNT I have that I found 59 rows (2,
92, 130, .......6119655) . That’s what I meant by 59 rows


Best regards


2013/10/2 Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>

>
> Mohamed,
>
>
>
> There's still the puzzle that you now show 56 distinct values, but the
> stats show 721,599 distinct keys.
>
> We need to work out how this discrepancy could have appeared (it would
> explain your plan, of course).
>
>
>
> Regards
>
> Jonathan Lewis
>
>
>
>
>
> ________________________________
> From: Mohamed Houri [mohamed.houri@xxxxxxxxx]
> Sent: 02 October 2013 09:58
> To: Jonathan Lewis
> Cc: ORACLE-L
> Subject: Re: Statistics Problem on partitioned table
>
>
> Jonathan
>
> >  How come there is only one distinct value of per_ind (first post), but
> the number of distinct keys in the index on per_ind is over 700,000 ?
>
> Again nicely spotted. Here below is the correct figure
>
> select per_ind, count(1) cnt from XXX_PER_YYY group by per_ind;
>
> 721,699 rows …
>
> I managed to put the result in a test table so that I can check how much
> count I have for each per_ind and so on
>
> select distinct cnt from mho_test order by cnt asc;
>
> 2
>
> 4
>
> 6
>
> 8
>
> 10
>
> 12
>
> 14
>
> …
>
> 6119655  ---> this the count for per_ind = 0 (at the moment the query was
> issued)
>
> 56 rows
>
>
> The old figure (that have prompted your question) was against a view
> XXX_PER_YYY_VW (which is select * from XXX_PER_YYY where per_ind = 0).
>
>
> There 4 columns on the XXX_PER_YYY table and they are all not null;
>
> SQL> select count(1)
>
>     from
>
>     (
>
>     select  table_name, partition_name, global_stats, last_analyzed,
> num_rows
>
>         from all_tab_partitions
>
>         where table_name='XXX_PAR_YYY’
>
>     )
>
>     where num_rows = 0;
>
>
>
>   COUNT(1)
>
> ----------
>
>        758
>
>
>
> 785 empty partitions over 1493 partitions.
>
>

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 10-2013