Solved

how to collect dbms stats

Posted on 2010-11-23
11
376 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
[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
11 Comments
 
LVL 77

Expert Comment

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

Assisted Solution

by:schwertner
schwertner earned 100 total points
ID: 34198038
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
ID: 34198270
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 74

Accepted Solution

by:
sdstuber earned 300 total points
ID: 34198281

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
 

Author Comment

by:iabhinay_dba
ID: 34198763
it says insufficient privilages when i am querieng the statment
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 300 total points
ID: 34198831
you need to have EXECUTE privilege granted on DBMS_STATS to your user
0
 
LVL 74

Assisted Solution

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

Assisted Solution

by:Chakravarthi Ayyala
Chakravarthi Ayyala earned 100 total points
ID: 34199186

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
ID: 34273584
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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
pl/sql - query very slow 26 105
how to find out the count of records based on the subfolders paths 11 50
add more rows to hierarchy 3 46
Oracle Nested table uses ? 2 60
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…
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.

734 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