Home » RDBMS Server » Server Administration » Analyze schema (Oracle 10.2.0.3 Linux AS4)
Analyze schema [message #393220] Fri, 20 March 2009 15:02 Go to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Hello All,

I need to analyze the schema .When I gave this

EXEC DBMS_STATS.gather_schema_stats('CRG', estimate_percent => 30);

It's taking more than 30 mins for now. I don't know if it got hanged . Can i cancel the operation and do it all over or is there any other command for analyzing the schema.

Kindly help .
Re: Analyze schema [message #393221 is a reply to message #393220] Fri, 20 March 2009 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Are statistics being updated?

[Updated on: Fri, 20 March 2009 15:04]

Report message to a moderator

Re: Analyze schema [message #393229 is a reply to message #393221] Fri, 20 March 2009 15:24 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
Only very few tables and indexes have got the new analyzed date which I could find from

> select table_name,last_analyzed from user_tables;

And now it's 1 hour. Can I cancel that operation and give the syntax all over .

Is there anything wrong with the syntax.

I heard that dbms_stats is much faster that dbms_utility , but this is taking a long time .

Please help me out .

[Updated on: Fri, 20 March 2009 15:27]

Report message to a moderator

Re: Analyze schema [message #393230 is a reply to message #393229] Fri, 20 March 2009 15:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No this is correct and collecting statistics is long.
Just follow the query executed by the session and you will see.

Regards
Michel
Re: Analyze schema [message #393235 is a reply to message #393229] Fri, 20 March 2009 15:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Is there anything wrong with the syntax.
Once again,
You are getting confused with SYNTAX and Arguments/options.
If there is a wrong with syntax, Oracle will let you know instantly.
>>but this is taking a long time .

It Depends on the number of tables/indexes and volume of data it is holding,plus your load.
Re: Analyze schema [message #393238 is a reply to message #393235] Fri, 20 March 2009 15:46 Go to previous messageGo to next message
rsreddy28
Messages: 295
Registered: May 2007
Senior Member
There are around 500 tables and 1000 Indexes. But as I told you earlier now it's near to 2 hours that I gave that syntax .

When I gave

SQL> select table_name,last_analyzed from user tables;

I could see very few tables that got analyzed and other tables are totally blank.

And as of now nobody is working on this now. I did the same last time , it hardly took 30 mins . Don't where it's going wroing.

Kindly help me out.
Re: Analyze schema [message #393239 is a reply to message #393238] Fri, 20 March 2009 15:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You are analyzing tables and indexes with estimate percent 30.
There is nothing much to do about it.
Re: Analyze schema [message #393243 is a reply to message #393238] Fri, 20 March 2009 15:59 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#sthref6735
Choose your options.
Specifically understand the default value for parameters you did not provide.
May be you can employ some parallelism.
Previous Topic: insufficient privileges error ORA-01031 when trying to connect as sysdba
Next Topic: Wrapped Packages
Goto Forum:
  


Current Time: Wed Dec 07 18:37:01 CST 2016

Total time taken to generate the page: 0.15781 seconds