Solved

how to collect dbms stats

Posted on 2010-11-23
11
355 Views
Last Modified: 2012-10-06
hi how can i take out a dbms_stats reports on a particular table
0
Comment
Question by:iabhinay_dba
11 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Mainly with the dbms_stats package.  There are a few different procedures in there.  Check the online docs for descriptions.
0
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 100 total points
Comment Utility
You can check if the table is provided with statistics using the column LAST_ANALYZED in the views dba_tables or user_tables.
About histograms read the Oracle documnet "performance tunning" for the partuicular version of Oracle.

pay attention also on collecting the technical statistics over the installations.
0
 
LVL 14

Expert Comment

by:ajexpert
Comment Utility
Check these views
INDEX_STATS

DBA_PART_COL_STATISTICS  

ALL_PART_COL_STATISTICS 

USER_PART_COL_STATISTICS 

DBA_SUBPART_COL_STATISTICS  

ALL_SUBPART_COL_STATISTICS  

USER_SUBPART_COL_STATISTICS 

DBA_TAB_COL_STATISTICS   

ALL_TAB_COL_STATISTICS  

USER_TAB_COL_STATISTICS 

DBA_USTATS               

ALL_USTATS              

USER_USTATS

Open in new window

0
 
LVL 73

Accepted Solution

by:
sdstuber earned 300 total points
Comment Utility

simplest form

dbms_stats.gather_table_stats('YOUR_SCHEMA','YOUR_TABLE_NAME');

but as above, read the docs for more information about all of the other options
0
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

by:iabhinay_dba
Comment Utility
it says insufficient privilages when i am querieng the statment
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
Comment Utility
you need to have EXECUTE privilege granted on DBMS_STATS to your user
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
Comment Utility
or you don't have privileges on the table itself
0
 
LVL 8

Assisted Solution

by:ReliableDBA
ReliableDBA earned 100 total points
Comment Utility

exec dbms_stats.gather_table_stats(ownname =>'<table owner name>',tabname =>'<table name>',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>DBMS_STATS.AUTO_DEGREE);

Note: One should be careful about gathering histograms too.  Hence, specifying method_opt=>'FOR ALL COLUMNS SIZE AUTO' is very very important.
0
 
LVL 5

Expert Comment

by:anand_20703
Comment Utility
Be careful with gathering statistics. Be alert about how the query/process is performing related to the table you intend to gather stats,before you gather stats. If the query/process performance goes down after you gather stats, you have options to restore the previous statistics. check the documentation for the same. In reality, this happens most of the time , particularly from 10g onwards.
0

Featured Post

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.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now