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

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.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
jboss 7.1 start up error 1 34
maven disable workspace resolution 1 21
UTL_FILE invalid file operation 5 24
Comparison query - 4 columns 9 23
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

856 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