[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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