Home » RDBMS Server » Server Administration » analyze (Oracle 10g Rel2, Linux AS4)
analyze [message #377626] Wed, 24 December 2008 00:10 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello All,

I want to analyze all the tables in the schema , which one is better . I'm using the SQL as
SQL> select 'analyze table'||owner||'.'||object_name||'estimate_statistics;' from dba_objects where owner='IRIS' and object_type='TABLE'

Is there any other apart from this .Plz help me out .

Regards,
Raj
Re: analyze [message #377635 is a reply to message #377626] Wed, 24 December 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The best way is to use DBMS_STATS and NOT analyze statement.

Regards
Michel
Re: analyze [message #377646 is a reply to message #377635] Wed, 24 December 2008 01:29 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

Hi,

Use the below command to analyse all tables in the schema.

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS( OWNNAME => 'IRIS', METHOD_OPT=> 'FOR ALL INDEXED COLUMNS SIZE 1', GRANULARITY=> 'DEFAULT', DEGREE => (value), CASCADE => TRUE);

before using it in SQL ,refer the link provided by michel for parameters used in analyse command.

Regards,
Balaji

Re: analyze [message #377695 is a reply to message #377646] Wed, 24 December 2008 05:33 Go to previous message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Thanks Michel and Balaj. I will look into dbms_stats
Previous Topic: Bringing database to mount state
Next Topic: undo tablespace
Goto Forum:
  


Current Time: Sat Dec 03 14:21:31 CST 2016

Total time taken to generate the page: 0.31540 seconds