[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2010-11-07
10
Medium Priority
?
638 Views
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.

0
Comment
Question by:GP7
[X]
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
10 Comments
 
LVL 7

Accepted Solution

by:
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.
0
 
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.

Thanks,
0
 
LVL 7

Assisted Solution

by:jocave
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.
0
Independent Software Vendors: 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

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

0
 
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.
0
 
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.
0
 

Expert Comment

by:South Mod
ID: 34440911
All,
 
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,
 
SouthMod
Community Support Moderator
0

Featured Post

Technology Partners: 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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Simple Linear Regression

650 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