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.
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.


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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Starting up a Project

726 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