Re: Stats gather taking longer -19c

Pap,

*However, we are trying to understand, why it's taking 30minutes more now in 19c even with same 50% sample , which was used in 11.2? Is it because any bug here in 19c?*

I don't know. And I don't think really that this could be linked to a bug. And even if this could be due to any bug, Oracle support will suggest using the default value of the sampling percentage

As Nenad has already suggested you can trace your dbms_stats call to this table and check where time is spent.

Or you can check dba_hist_sqltext ( or v$sql_monitor (or dba_hist_reports) ) for SQL statement where sql_text like '%Analyse%' and sql_text like '%YOUR_TABLE_NAME%' and investigate where time is spent for this SQL statements

Best regards
Mohamed

Le jeu. 23 juin 2022 à 17:26, Nenad Noveljic <nenad.noveljic_at_gmail.com> a écrit :

> Why don‘t you trace the process?
>
> Von meinem iPhone gesendet
>
> Am 23.06.2022 um 15:40 schrieb Pap <oracle.developer35_at_gmail.com>:
>
> 
> Thank you Lothar. Actually all the indexes on this mon partitioned table
> are btree indexes. And also I checked DBA_OPTSTAT_OPERATION_TASKS in which
> there is breakup of howmuch time each index stats gather took. And it says
> those all indexes are combined finishing in minutes.
>
> On Thu, 23 Jun 2022, 6:40 pm l.flatz_at_bluewin.ch, <l.flatz_at_bluewin.ch>
> wrote:
>
>> Hi,
>>
>> there was a change in the way bitmap index stats are calculated.
>> Check out Doc ID 2611336.1
>>
>> Thanks
>>
>> Lothar
>>
>> ----Ursprüngliche Nachricht----
>> Von : oracle.developer35_at_gmail.com
>> Datum : 23/06/2022 - 14:10 (MS)
>> An : mohamed.houri_at_gmail.com
>> Cc : oracle-l_at_freelists.org
>> Betreff : Re: Stats gather taking longer -19c
>>
>> Thank you Mohamed.
>>
>> So basically we must have to remove the hard coded sample and use to auto
>> sample size in all the places. Without this the histogram would be old
>> types and also distinct value calculation would be old types.
>>
>> However we are trying to understand, why it's taking 30minutes more now
>> in 19c even with same 50% sample , which was used in 11.2? Is it because
>> any bug here in 19c?
>>
>> On Thu, 23 Jun 2022, 5:13 pm Mohamed Houri, < mohamed.houri_at_gmail.com>
>> wrote:
>>
>>> 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 *
>>>
>>> It looks like you are in the second situation since you are using 50% a
>>> sample percentage that you should never have done; since the
>>> *AUTO_SAMPLE_SIZE *default value governs,among others, the
>>> approximate_ndv algorithm and the new histogram types
>>>
>>> Best regards
>>> Mohamed Houri
>>>
>>> Le jeu. 23 juin 2022 à 12:23, Pap < oracle.developer35_at_gmail.com> a
>>> écrit :
>>>
>>>> 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>
>>>
>>>
>>
>>

-- 

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