Home » RDBMS Server » Server Administration » analyze schema hangs (10.1.0.2)  () 1 Vote
analyze schema hangs [message #349252] Fri, 19 September 2008 09:14 Go to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
hello


I have submitted a job which executes everyday the Analyze schema command . However it hangs (for 5-6 hours) after sometime. It analyze most of the tables and then hangs at some particular tables.at the end i have kill the session.
Tables contain BLOB column. However if i manually analyzes these tables i dont find any issue.

PLease help.

regards
ankush
Re: analyze schema hangs [message #349255 is a reply to message #349252] Fri, 19 September 2008 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
ttp://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

You should be using DBMS_GETSTATS instead of ANALYZE; which has bee deprecated in V10.
Re: analyze schema hangs [message #349256 is a reply to message #349252] Fri, 19 September 2008 09:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why do you think it is hung, as opposed to taking a long time to run through?
If you look at v$session_wait, what does that say it's waiting on
Re: analyze schema hangs [message #349259 is a reply to message #349256] Fri, 19 September 2008 09:35 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
Thanks for the reply
i have not checked v$session_wait but v$session doesnot show much wait_time for that session that runs the jons . But it is taking around 5-6 hrs now earlier it was taking approx an hour .
and it analyze all the tables except 2 of them .

if i analyze them individually it takes 5 mins each .


Re: analyze schema hangs [message #349260 is a reply to message #349259] Fri, 19 September 2008 09:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What changed in the databse between 'Earlier' and now?

Have a look at v$session_wait - it will tel you what the session is waiting for.

Additionally, on 10g you'd be better off using DBMS_STATS rather
than ANALYZE

{added additional question}

[Updated on: Fri, 19 September 2008 09:39]

Report message to a moderator

Re: analyze schema hangs [message #349264 is a reply to message #349260] Fri, 19 September 2008 09:56 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
thanks for the help.

In v$session_wait wait_time for all sessions are either 0 or 1. I dont think it is a significant figure.

it is prebuilt job for the application that analyzes the schema everyday . We cannot change the application code.
i have used dbms_Stats while manually analyzing the tables.

is the blob columns in the table creating an issue.

regards

Re: analyze schema hangs [message #349265 is a reply to message #349259] Fri, 19 September 2008 10:02 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
what do you mean by "manually analyzing"?
Post the exact command you are using to analyze.
Also,
>>a job which executes everyday the Analyze schema command .
Are you using dbms_utility.analyze_schema? Post the exact command you are using. It has too much issues to deal with and just not
the right tool to gather stats.
As other have already told, use DBMS_STATS.
Are you sure that your data is so volatile you need to analyze them everyday?
Re: analyze schema hangs [message #349266 is a reply to message #349264] Fri, 19 September 2008 10:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
In v$session_wait wait_time for all sessions are either 0 or 1. I dont think it is a significant figure.


Depends. When wait for reading one block takes one second, and the analyse needs to read 20 million blocks, then you will have to wait for a few months before it's finished.
Re: analyze schema hangs [message #349278 is a reply to message #349265] Fri, 19 September 2008 10:48 Go to previous messageGo to next message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
i m using the below command to analyze the table
dbms_Stats.gather_table_stats('<schema-name>','<table-name>')

the application is running the job to analyze the schema. I am not sure how it is doing it.

Re: analyze schema hangs [message #349279 is a reply to message #349278] Fri, 19 September 2008 10:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I am not trying to pick on you.
>>the application is running the job to analyze the schema. I am not sure how it is doing it.
THen how would you expect us to know what your application is doing?
Re: analyze schema hangs [message #349301 is a reply to message #349279] Fri, 19 September 2008 15:06 Go to previous message
ankush_chawla
Messages: 136
Registered: November 2006
Senior Member
i apologize for incomplete info
Thanks for your help Smile

We have the application that gives the complete analysis report
The application is designed to analyzes the schema everyday it either by dbms_stats or dbms_utility or some other.
It analyzes the schema earlier till 2nd sept it was taking a hour to complete now of sudden it hangs for more than 6 hrs . It analyzes all the tables(approx 50) except 4 of them. It also hangs on the same table . Acc to client nothing changed in the machine and morever i dont find any relevant thing in alert log file.

We have the application that gives the complete analysis report.
if i maually analyzes that table(on which it hangs) by dbms_stats its done in 5-6 mins . now i wonder why it hangs at jobs . THe table contains a blob column.
Internal code of application are not very transparent.


Previous Topic: Missing in oracle10g OEM (merged)
Next Topic: create directory object on a client machine
Goto Forum:
  


Current Time: Sun Dec 04 20:32:21 CST 2016

Total time taken to generate the page: 0.10354 seconds