Re: Stats gather taking longer -19c

Pap,

*Its having histograms on ~36 columns but all of them are still showing height-balanced and frequency. I was expecting those to be hybrid , but its didnt happen, may be because of the way the stats getting gathered on this table i.e. with hard coded 50% sample i.e as below. *

If you are in 12c and above and you still find histograms of the legacy HEIGHT BALANCED type then it is either that

• the statistics for the concerned tables have not yet been calculated in the newer Oracle version
• or that you are calculating the statistics with a non-default value of the estimate_percent ( sampling percentage): default value being *AUTO_SAMPLE_SIZE *

> Hi, We moved from 11.2.0.4 to version 19.11.0.0.0 of oracle for this
> database. And we are seeing for one of the table the stats gather has been
> taking longer. It was used to take ~5-6minutes vs now its taking
> ~30-40minutes. Dont have enough AWR history to verify the exact run time
> from ASH/AWR. But we can see it from the application log which notes the
> start and end time of the stats gather.
>
>
> There does exists 5 indexes in this table but when i am seeing ,
> DBA_OPTSTAT_OPERATION_TASKS its showing ~1 minutes for all the index stats
> gather combinely. Its the table/column gather which takes longer.We are not
> able to test it as we dont have a 11.2 lower environment with us now though.
>
>
> The table size is 25Gb and is non partitioned one holding ~13million rows.
> Its having 111 columns. Its having histograms on ~36 columns but all of
> them are still showing height-balanced and frequency. I was expecting those
> to be hybrid , but its didnt happen, may be because of the way the stats
> getting gathered on this table i.e. with hard coded 50% sample i.e as
> below.
>
>
> exec DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => NULL, TABNAME =>
> table_name, ESTIMATE_PERCENT => 50, CASCADE => TRUE, method_opt => 'FOR ALL
> COLUMNS SIZE AUTO', DEGREE => 4);
>
>
> So my question is what must be the reason that same stats gather with ~50%
> sample size now taking ~30minutes more on 19C as compared to 11.2 where it
> was finishing in ~5minutes?
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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