No question at this time
DBA Top 10
1 A. Kavsek 10900
2 M. Cadot 6200
3 P. Wisse 5000
4 B. Vroman 4500
5 T. Boles 2200
6 . Lauri 2000
7 J. Schnackenberg 1300
8 J. PĂ©ran 1100
9 R. Pattyn 700
10 T. P 500
10 G. Graham 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48425
Total active users1512
Act. users last 24h8
Act. users last hour0
Registered user hits last week172
Registered user hits last month818
Go up

Indexes have stale statistics
Next thread: Merge Join
Prev thread: oracle 11g r2 install ora-19587 error masege withe windows 7 64bit

Message Score Author Date
Hi , I am gathering the statistics of Indexes ,...... saumya das Jul 08, 2019, 19:57
Hi Saumya, Stale means the statistics are candi...... Philip Wisse Jul 09, 2019, 09:42
Hi Phil , Thanks for the Reply.. Yes I checked...... saumya das Jul 09, 2019, 13:50
Hi Sauma, Most likely there's no impact because...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Philip Wisse Jul 09, 2019, 14:24
Hi Sauma, At first glance you do have nothing t...... Lauri Jul 18, 2019, 11:58

Follow up by mail Click here


Subject: Indexes have stale statistics
Author: saumya das, India
Date: Jul 08, 2019, 19:57, 73 days ago
Os info: AIX 7
Oracle info: oracle 12c
Message: Hi ,

I am gathering the statistics of Indexes , But still within 1 Day some indexes statistics become STALE.
Like to know what are the impact if index statistics become STALE. Onething I have observed the Indexes belongs to the Table which have very high DML transactions. will it impact performance.

Please suggest. what action I should Take.

Thanks
Saumya
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Indexes have stale statistics
Author: Philip Wisse, Netherlands
Date: Jul 09, 2019, 09:42, 73 days ago
Message: Hi Saumya,

Stale means the statistics are candidates to be refreshed on your next cycle of stats gathering.

So you don't have to do anything.

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Indexes have stale statistics
Author: saumya das, India
Date: Jul 09, 2019, 13:50, 72 days ago
Message: Hi Phil ,

Thanks for the Reply..
Yes I checked that after gather stats the STALE stats is gone , But onething I want to know , if I have STALE stats in the database for the particular index , how much will it impact for the query that is using that particular Index.

Thanks
Saumya
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Indexes have stale statistics
Author: Philip Wisse, Netherlands
Date: Jul 09, 2019, 14:24, 72 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi Sauma,

Most likely there's no impact because data always comes with a pattern.

But you can construct data sets which are difficult for the optimizer. For instance:
https://oracle-base.com/articles/12c/sql-plan-directives-12cr1

Oracle tries to make the optimizer smarter, check:
https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf

Bottom line, it depends on the data.

HTH, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-Indexes have stale statistics
Author: Lauri, Netherlands
Date: Jul 18, 2019, 11:58, 64 days ago
Message: Hi Sauma,

At first glance you do have nothing to do.
Just let the Oracle engine do the work for you.
If you notice any performance decrease and if you can identify the involved query, then used the recommendations links posted by Philip.
But do implement it then with care.

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here