Srikanth Addagiri

• Apr 18th, 2005

 

srikanthaddagiri@yahoo.com 
When the data in the data warehouse changes frequently we need to analyze the tables. Analyze tables will compute/update the table statistics, that will help to boost the performance of your SQL.

ramana_pasula

 Profile Answers by ramana_pasula 

• Sep 11th, 2007

 

Analyze your_table_name compute statistics;

jryan999

 Profile Answers by jryan999 Questions by jryan999

• May 1st, 2008

 

In a warehouse I would recommend analyzing the data either after each load has completed, or periodically (week-end or month-end) depending upon the data volumes and therefore the effort required.

You can analyze a table in Oracle at the Database, Schema or Table level.  I would recommend Schema or Table.

You can "estimate statistics" or "compute statistics".  Personally I'd almost always use the ESTIMATE option.  I've found Oracle to be around 90% accurate from an estimate but using about 20% of the effort - useful on very large tables.

Here's the scripts I typically use:-

Analyse Table:
accept table_name prompt "Enter the table to analyze: "
execute dbms_stats.gather_table_stats(user, '&table_name', null, dbms_stats.auto_sample_size, null, 'FOR ALL INDEXED COLUMNS SIZE 254');


Analyze Schema:-
-- Note: To remove all existing statistics
-- EXEC DBMS_Stats.Delete_Schema_Stats(user);
exec dbms_stats.gather_schema_stats( -
ownname          => user, -
options          => 'GATHER AUTO', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt       => 'for all columns size repeat', -
degree           => 15 -
);