Home » RDBMS Server » Performance Tuning » slow and freezed
slow and freezed [message #230046] Tue, 10 April 2007 13:30 Go to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
hello sir

i am in very critical situation plz help me

ur OS LINUX : Red Hat 3.4.5-2
oracle 9i
Oracle9i Release 9.2.0.4.0 - Production
JServer Release 9.2.0.4.0 - Production

Acually we have set of report queries ,the same query have been seems good and run the results for another 3 servers but it is freezed in one server all inti parameters r unique for 4 servers
i have checked hit ratio,session waits,I/O's,top sql,long query everything all r idle but i am unable to find out the problem

optimizer mode in CBO (no stat is gathered)
for eg: select count(*) from <object_name>

it would freezed for only one server but the same one would works very fine in other 3 servers .


what can i do plz help me.
thanx.
Re: slow and freezed [message #230047 is a reply to message #230046] Tue, 10 April 2007 13:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>optimizer mode in CBO (no stat is gathered)
Collect the statistics first!.
Read the sticky
Re: slow and freezed [message #230049 is a reply to message #230046] Tue, 10 April 2007 13:49 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member

thank u sir

Actually we dont use stat because our database is not big ,plz gimme the suggesition apart from stats gathering

thanx.
Re: slow and freezed [message #230050 is a reply to message #230049] Tue, 10 April 2007 13:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Being big or small does not matter Smile
use dbms_stats and try again.
Re: slow and freezed [message #230052 is a reply to message #230050] Tue, 10 April 2007 13:56 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
ok thank u


if we use RBO then what is the suggesition in this same situation
Re: slow and freezed [message #230053 is a reply to message #230052] Tue, 10 April 2007 14:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Could try ****something**** and post the results?
RBO is a different case.
Re: slow and freezed [message #230055 is a reply to message #230046] Tue, 10 April 2007 14:07 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
on which/what resource is the "frozen" session waiting?
enable SQL_TRACE to see where it is spending its time.

What does OS "strace" show the process is doing?
Re: slow and freezed [message #230057 is a reply to message #230046] Tue, 10 April 2007 14:46 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
tahnk u sir

plz gimme the steps to enable sql_trace in session vise and how can i identify the pit fall if occurs
plz help me..

thanx
Re: slow and freezed [message #230284 is a reply to message #230057] Wed, 11 April 2007 07:37 Go to previous messageGo to next message
wahid_khan
Messages: 1
Registered: April 2007
Location: Saudi Arabia
Junior Member

Hi,

Got the solutions or still looking for ?

Regards
Wahid
Re: slow and freezed [message #231324 is a reply to message #230284] Mon, 16 April 2007 09:39 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
i am still looking for the solution in RBO mode
icon12.gif  Re: slow and freezed [message #231515 is a reply to message #231324] Tue, 17 April 2007 06:46 Go to previous messageGo to next message
kamkan
Messages: 27
Registered: April 2007
Location: Chennai, INDIA
Junior Member
Gentleman,
Forget RBO. You are using 9i. Oracle have optimized CBO from 9i onwards. Please generate statistics using DBMS_STATS. Do u have any ora-600 error in your alert file? All the db's are 9.2.0.4?
Re: slow and freezed [message #232202 is a reply to message #231515] Thu, 19 April 2007 15:30 Go to previous messageGo to next message
vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY
Senior Member
thanx a lot i got it.
Re: slow and freezed [message #232430 is a reply to message #232202] Fri, 20 April 2007 08:33 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
And could you please share that solution with the nice folks who helped you?
Previous Topic: Regarding Index
Next Topic: faq's
Goto Forum:
  


Current Time: Sat Dec 03 09:35:29 CST 2016

Total time taken to generate the page: 0.11262 seconds