| analyze schema hangs [message #349252] |
Fri, 19 September 2008 09:14  |
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 #349256 is a reply to message #349252] |
Fri, 19 September 2008 09:19  |
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 |
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 |
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 |
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 #349266 is a reply to message #349264] |
Fri, 19 September 2008 10:02 |
ThomasG
Messages: 3212
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 |
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 #349301 is a reply to message #349279] |
Fri, 19 September 2008 15:06 |
ankush_chawla
Messages: 136
Registered: November 2006
|
Senior Member |
|
|
i apologize for incomplete info
Thanks for your help 
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.
|
|
|
|
) 1 Vote
] 
Blog
Search
Help
Members
Register
Login
Home
