• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

how to collect dbms stats

hi how can i take out a dbms_stats reports on a particular table
0
iabhinay_dba
Asked:
iabhinay_dba
5 Solutions
 
slightwv (䄆 Netminder) Commented:
Mainly with the dbms_stats package.  There are a few different procedures in there.  Check the online docs for descriptions.
0
 
schwertnerCommented:
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
 
ajexpertCommented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
sdstuberCommented:

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
 
iabhinay_dbaAuthor Commented:
it says insufficient privilages when i am querieng the statment
0
 
sdstuberCommented:
you need to have EXECUTE privilege granted on DBMS_STATS to your user
0
 
sdstuberCommented:
or you don't have privileges on the table itself
0
 
Chakravarthi AyyalaDatabase AdministratorCommented:

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
 
AnandCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now