Home » RDBMS Server » Performance Tuning » shutting down database
shutting down database [message #127028] Fri, 08 July 2005 00:36 Go to next message
kinjal
Messages: 124
Registered: June 2001
Senior Member
Hello,

Is there any advantage of shutting down and restarting the database on the performance of the database?

Thanking you,
Kinjal
Re: shutting down database [message #127054 is a reply to message #127028] Fri, 08 July 2005 04:50 Go to previous messageGo to next message
Frank Naude
Messages: 4566
Registered: April 1998
Senior Member
Hi,

As far as I understand, there are only disadvantages. If you restart the database, all caches will be cold and the database will need some time (and additional resources) to ramp-up to its optimum performance level.

Best regards.

Frank
Re: shutting down database [message #127137 is a reply to message #127054] Fri, 08 July 2005 23:52 Go to previous messageGo to next message
kinjal
Messages: 124
Registered: June 2001
Senior Member
Hi Frank,

Thanks for this information..

So is it better to user 'alter system flush shared_pool' command when the speed of database becomes slow?
I mean sometimes it happens that the database is running very slow. So what could be major issues related to this?

Thanking you,
Kinjal
Re: shutting down database [message #127141 is a reply to message #127137] Sat, 09 July 2005 00:44 Go to previous messageGo to next message
Frank Naude
Messages: 4566
Registered: April 1998
Senior Member
Hi,

Flushing the shared pool will almost always make the database run slower until all frequently used SQL is re-cached.

Do you have proof that it runs faster after you've flushed the shared pool?

PS: I know that applications that don't use bind variables are messing up the shared pool, and flushing might occasionally be required to prevent nasty errors on some old DB releases.

Best regards.

Frank
Re: shutting down database [message #127143 is a reply to message #127141] Sat, 09 July 2005 00:49 Go to previous messageGo to next message
kinjal
Messages: 124
Registered: June 2001
Senior Member
Hello Frank,

Thanks again.

You are right. The application which don't use bind variables and all that is creating problem.
And for that I need to instruct all programmers to come out of this problem. It may help prevent some of the problem with performance.

Anything else which needs to be taken care of?

Thanks for your support.

Regards,
Kinjal
Re: shutting down database [message #127213 is a reply to message #127028] Sun, 10 July 2005 01:04 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Check AskTom for a script to identify queries that don't use bind-variables.

hth
Re: shutting down database [message #127286 is a reply to message #127213] Mon, 11 July 2005 01:39 Go to previous messageGo to next message
kinjal
Messages: 124
Registered: June 2001
Senior Member
Hi Frank,

Thank you very much..
It really help me find out statements which do not use bind variables..

Thanks again,
Kinjal
Re: shutting down database [message #127412 is a reply to message #127028] Mon, 11 July 2005 10:12 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Also, as a temporary measure only, to experiment in development first while people are rewriting code to use bind variables, you may consider testing different values for the cursor_sharing parameter after reading about what it does.

Previous Topic: composite index or single column index?
Next Topic: how to find out where oracle is using paging and swapping (merged)
Goto Forum:
  


Current Time: Sun Jan 17 13:26:38 CST 2021