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

Posted on 2010-11-07
Medium Priority
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 1332 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 668 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 1332 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.
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!


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 http://www.experts-exchange.com/Q_26700482.html) 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

777 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