How to see if there are statistics available on an object in oracle database?

Posted on 2010-11-07
Last Modified: 2012-05-10
Hi All,

How to see if there are statistics available on an object in oracle database? If they are available, how do  know if they are stale or not. Are there any views to get this information?

Could anybody please also let me know if there is a view to see if an index is unselective or not. I mean I would like to see how often the index is being used.

Your input is greatly appreciated.

Thank you.

Question by:GP7
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

jocave earned 333 total points
ID: 34081906
The data dictionary views DBA_TABLES (or ALL_TABLES or USER_TABLES) will have information about the statistics related to tables (NUM_ROWS, BLOCKS, and AVG_ROW_LEN are commonly used).  DBA_INDEXES (or ALL_INDEXES or USER_INDEXES) will have the same sorts of information about indexes (NUM_ROWS, LEAF_BLOCKS, and DISTINCT_KEYS being probably the most commonly used).  All those data dictionary tables also have a LAST_ANALYZED column that indicates when statistics were last gathered.

If you want to drill deeper, you can see the column-level statistics information in DBA_TAB_COLS (LOW_VALUE, HIGH_VALUE, NUM_DISTINCT, and DENSITY being useful attributes) along with LAST_ANALYZED.  And DBA_HISTOGRAMS has information about the histograms that have been gathered, though delving too deeply into that information can get a bit challenging.

You can generally determine roughly how selective the optimizer believes an index to be by dividing the DISTINCT_KEYS from DBA_INDEXES by NUM_ROWS from DBA_TABLES.  The smaller the value, the more selective (a value near 1 indicates that you have a unique or nearly unique index).  A value of 10,000, on the other hand, indicates that a single key in the index would be expected to map to 10,000 rows in the table-- not very selective at all.  Of course, if you have skewed data with histograms, Oracle can take that into account and may come up with substantially different selectivity estimates.
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 167 total points
ID: 34081963
you can use the below :

select * from dba_tab_statistics
where stale_stats = 'YES'  --> the value can be YES or NO
and owner ='SCOTT' --> change the owner here accordingly.


Assisted Solution

jocave earned 333 total points
ID: 34082031
Be aware that the STALE_STATS column is populated only if table monitoring is enabled (which is the default in 10g but not in earlier versions).  But if all the 10g defaults are in place, the nightly statics gathering job will automatically be gathering statistics on all objects where Oracle considers that statistics stale.  So to the extent that Oracle's algorithm for gathering staleness is appropriate in your environment, when the default configuration is in place, a STALE_STATS value of 'YES' is a transient condition that is going to be remedied overnight.
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


Author Comment

ID: 34104194
Thank you all for your input. I shall implement your recommendations and shall get back to you soon.

LVL 28

Expert Comment

by:Naveen Kumar
ID: 34421271
I believe we ( jocave and myself ) have given the information which should have detinitely helped GP7.
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34421275
I believe we ( jocave and myself ) have given the information which should have definitely helped GP7.

Expert Comment

by:South Mod
ID: 34440911
Following an 'Objection' by nav_kum_v (at to the intended closure of this question, it has been reviewed by at least one Moderator and is being closed as recommended by the Expert.
At this point I am going to re-start the auto-close procedure.
Thank you,
Community Support Moderator

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question