OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » analyze (Oracle 10g Rel2, Linux AS4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

analyze [message #377626]

Wed, 24 December 2008 00:10

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

Report message to a moderator

Re: analyze [message #377635 is a reply to message #377626]

Wed, 24 December 2008 00:47

Michel Cadot
Messages: 68666
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

Report message to a moderator

Re: analyze [message #377646 is a reply to message #377635]

Wed, 24 December 2008 01:29

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

Report message to a moderator

Re: analyze [message #377695 is a reply to message #377646]

Wed, 24 December 2008 05:33

rsreddy28
Messages: 295
Registered: May 2007

Senior Member

Thanks Michel and Balaj. I will look into dbms_stats

Report message to a moderator

Previous Topic:	Bringing database to mount state
Next Topic:	undo tablespace

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 05 03:42:48 CDT 2024