Oracle DBA ANALYZE COMMAND

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.
sam_2012Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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) 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
sdstuberCommented:
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.
Determine the Perfect Price for Your IT Services

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

Get Your eBook
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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_2012Author Commented:
I need to know what are the types of statistics oracle gathers when we run either ANALYZE or DBMS_GATHER_TABLE_STATS ?
sdstuberCommented:
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$;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam_2012Author Commented:
Thanks a lot , I ran the queries got some idea about the statistics gathered. Sdstuber u always rock.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.