OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » slow and freezed

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

slow and freezed [message #230046]

Tue, 10 April 2007 13:30

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.

Report message to a moderator

Re: slow and freezed [message #230047 is a reply to message #230046]

Tue, 10 April 2007 13:40

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>optimizer mode in CBO (no stat is gathered)
Collect the statistics first!.
Read the sticky

Report message to a moderator

Re: slow and freezed [message #230049 is a reply to message #230046]

Tue, 10 April 2007 13:49

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.

Report message to a moderator

Re: slow and freezed [message #230050 is a reply to message #230049]

Tue, 10 April 2007 13:50

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Being big or small does not matter Smile

use dbms_stats and try again.

Report message to a moderator

Re: slow and freezed [message #230052 is a reply to message #230050]

Tue, 10 April 2007 13:56

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

Report message to a moderator

Re: slow and freezed [message #230053 is a reply to message #230052]

Tue, 10 April 2007 14:04

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Could try ****something**** and post the results?
RBO is a different case.

Report message to a moderator

Re: slow and freezed [message #230055 is a reply to message #230046]

Tue, 10 April 2007 14:07

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: slow and freezed [message #230057 is a reply to message #230046]

Tue, 10 April 2007 14:46

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

Report message to a moderator

Re: slow and freezed [message #230284 is a reply to message #230057]

Wed, 11 April 2007 07:37

wahid_khan
Messages: 1
Registered: April 2007
Location: Saudi Arabia

Junior Member

Hi,

Got the solutions or still looking for ?

Regards
Wahid

Report message to a moderator

Re: slow and freezed [message #231324 is a reply to message #230284]

Mon, 16 April 2007 09:39

vasudevan
Messages: 119
Registered: February 2006
Location: TRICHY

Senior Member

i am still looking for the solution in RBO mode

Report message to a moderator

Re: slow and freezed [message #231515 is a reply to message #231324]

Tue, 17 April 2007 06:46

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?

Report message to a moderator