Oracle DBA ANALYZE COMMAND

sam_2012
sam_2012 used Ask the Experts™
on
Hi All,

I have been tuning the tables using the ANALYZE command , without knowing what happens internally . Iam new to Oracle DBA in tuning. Hence request anyone to provide a detailed explanation as to what happens when we ANALYZE the table or use DBMS_GATHER_STATISTICS package.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
johnsoneSenior Oracle DBA

Commented:
You shouldn't be using ANALYZE.  It doesn't gather all the statistics.  Use the DBMS_STATISTICS package procedures.

These are the statistics that the optimizer uses to determine which plan is the best.  Information gathered includes number of rows, distributions, histograms and other information.
slightwv (䄆 Netminder)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Since you are new to Oracle, I would strongly encourage you to spend some time in the Concepts documentation and the 2-Day books.

The Concepts guide has a fairly decent overview of the statistics:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/toc.htm
Optimizer Statistics

Optimizer statistics are a collection of data that describe details about the database and the objects in the database. The statistics provide a statistically correct picture of data storage and distribution usable by the optimizer when evaluating access paths.

The Documentation home page:
http://docs.oracle.com/cd/E11882_01/nav/portal_4.htm
sdstuber
Most Valuable Expert 2011
Top Expert 2012

Commented:
if you really want to see what happens with either or both of the commands, try tracing your own session, then running the commands.  You'll see every query the database runs internally to determine the output.

Note - ANALYZE can still be used, just don't use it for statistics, instead, use it for structure analysis.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Devinder Singh VirdiLead Oracle DBA Team

Commented:
There are lots of parameters when creating a table, therefore tuning a table is very generic statement. Is there any specific problem you are trying to solve?
sam_2012

Author

Commented:
I need to know what are the types of statistics oracle gathers when we run either ANALYZE or DBMS_GATHER_TABLE_STATS ?
Most Valuable Expert 2011
Top Expert 2012
Commented:
counts of total number of values, total number of nulls, and number of distinct values.
also minimums and maximums

if you have histograms they gather information about the relative distribution of values (like 90 As, 10 Bs ,  30 Cs, etc)


You can look at the various statistic views to see them

Here are some sample queries
SELECT table_name, partition_name, stattype_locked FROM dba_tab_statistics;

SELECT owner,
       table_name,
       num_rows,
       blocks,
       empty_blocks,
       avg_space,
       chain_cnt,
       avg_row_len,
       avg_space_freelist_blocks,
       num_freelist_blocks,
       sample_size,
       last_analyzed,
       global_stats,
       user_stats
  FROM dba_tables;

SELECT owner,
       table_name,
       column_name,
       num_distinct,
       low_value,
       high_value,
       density,
       num_nulls,
       avg_col_len,
       num_buckets,
       sample_size,
       last_analyzed,
       global_stats,
       user_stats
  FROM dba_tab_cols;

SELECT * FROM dba_tab_col_statistics;

SELECT owner,
       index_name,
       table_owner,
       table_name,
       blevel,
       leaf_blocks,
       distinct_keys,
       avg_leaf_blocks_per_key,
       avg_data_blocks_per_key,
       clustering_factor,
       sample_size,
       last_analyzed,
       global_stats,
       user_stats,
       pct_direct_access
  FROM dba_indexes;

SELECT table_owner,
       table_name,
       partition_name,
       num_rows,
       blocks,
       empty_blocks,
       avg_space,
       chain_cnt,
       avg_row_len,
       sample_size,
       last_analyzed,
       global_stats,
       user_stats
  FROM dba_tab_partitions;

SELECT * FROM dba_part_col_statistics;

SELECT index_owner,
       index_name,
       partition_name,
       leaf_blocks,
       distinct_keys,
       avg_leaf_blocks_per_key,
       avg_data_blocks_per_key,
       clustering_factor,
       num_rows,
       sample_size,
       last_analyzed,
       user_stats,
       pct_direct_access,
       global_stats
  FROM dba_ind_partitions;

SELECT table_owner,
       table_name,
       partition_name,
       subpartition_name,
       num_rows,
       blocks,
       empty_blocks,
       avg_space,
       chain_cnt,
       avg_row_len,
       sample_size,
       last_analyzed,
       global_stats,
       user_stats
  FROM dba_tab_subpartitions;

SELECT * FROM dba_subpart_col_statistics;

SELECT index_owner,
       index_name,
       partition_name,
       subpartition_name,
       blevel,
       leaf_blocks,
       distinct_keys,
       avg_leaf_blocks_per_key,
       avg_data_blocks_per_key,
       clustering_factor,
       num_rows,
       sample_size,
       last_analyzed,
       user_stats,
       global_stats
  FROM dba_ind_subpartitions;

SELECT owner,
       table_name,
       num_rows,
       blocks,
       empty_blocks,
       avg_space,
       chain_cnt,
       avg_row_len,
       avg_space_freelist_blocks,
       num_freelist_blocks,
       sample_size,
       last_analyzed,
       global_stats,
       user_stats
  FROM dba_object_table;

SELECT owner,
       table_name,
       num_rows,
       blocks,
       empty_blocks,
       avg_space,
       chain_cnt,
       avg_row_len,
       avg_space_freelist_blocks,
       num_freelist_blocks,
       sample_size,
       last_analyzed,
       global_stats,
       user_stats
  FROM dba_all_tables;

Open in new window




Note dbms_gather_table_stats can also gather system statistics which measure  io and cpu speeds

select * from sys.aux_stats$;
sam_2012

Author

Commented:
Thanks a lot , I ran the queries got some idea about the statistics gathered. Sdstuber u always rock.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial