Home » RDBMS Server » Server Administration » Flushing Shared Pool
Flushing Shared Pool [message #294827] Sat, 19 January 2008 14:36 Go to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

hi experts,

can anybody tell me why do we flush the shared pool??

Regards,
Balaji

Re: Flushing Shared Pool [message #294828 is a reply to message #294827] Sat, 19 January 2008 15:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You NEVER do it.

Regards
Michel
Re: Flushing Shared Pool [message #294838 is a reply to message #294827] Sat, 19 January 2008 23:53 Go to previous messageGo to next message
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
Member

Often in oracle world we face certain kind of situation where flushing the shared pool remains only solution. Three times I faced such situations. The application was executing a very large number of dynamically generated SQL statements. the statements were almost identical & differing with only
where userid='xxx' or some thing little different 
. When system was started in the morning the performance was nice but with the passage of every hour the performance degraded and by afternoon performance was almost unacceptable with users started complaining. The application developers were adamant to change code. The last resort I restored to was to flush shared pool every two hours with a scheduled job.


Regards

Manjit Kumar [mkbhati]
Re: Flushing Shared Pool [message #294844 is a reply to message #294838] Sun, 20 January 2008 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When have I to push my car?
Each time I stop because otherwise it doesn't start.
In this case, you don't have a car you have a soapbox.

Regards
Michel
Re: Flushing Shared Pool [message #294887 is a reply to message #294838] Sun, 20 January 2008 07:56 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
mkbhati wrote on Sun, 20 January 2008 05:53

Often in oracle world we face certain kind of situation where flushing the shared pool remains only solution. Three times I faced such situations. The application was executing a very large number of dynamically generated SQL statements. the statements were almost identical & differing with only
where userid='xxx' or some thing little different 
. When system was started in the morning the performance was nice but with the passage of every hour the performance degraded and by afternoon performance was almost unacceptable with users started complaining. The application developers were adamant to change code. The last resort I restored to was to flush shared pool every two hours with a scheduled job.


Regards

Manjit Kumar [mkbhati]


Your issue is that you are not using bind variables. Flushing the shared pool is not a solution, re-writing your code to use bind variables is.

[Updated on: Sun, 20 January 2008 07:56]

Report message to a moderator

Re: Flushing Shared Pool [message #294924 is a reply to message #294844] Sun, 20 January 2008 21:59 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

what do u mean ??
Re: Flushing Shared Pool [message #294927 is a reply to message #294827] Sun, 20 January 2008 22:21 Go to previous messageGo to next message
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
Member

Yes pablolee you are right, the correct solution is to use bind variables. But very often dba's are faced with poorly written application code hijacking Oracle resources & developers claiming that their code is absolutely legal & oracle supports it. Often developers are adamant enough to change their code and blame it on DBA for not handling the server properly. There is a difference between a DBA's perspective & a Developers perspective. Both see oracle from different perspective. This is root of many problems in oracle world. There are many things is oracle world which are legal and permitted but not advisable from performance point of view. The developers which are entry level guys in most cases fails to understand this point and continue to code with bad coding practices. There oracle comes to rescue by providing dba with unconventional facilities of flushing shared pool / buffer cache e.t.c. .

Regards

Manjit Kumar [mkbhati]
Re: Flushing Shared Pool [message #294935 is a reply to message #294924] Sun, 20 January 2008 23:20 Go to previous message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what do u mean ??

pablolee explained what I meant.

If you are forced to often flush the shared pool then you don't have an Oracle application.

@mkbhati, it is not because you are a feature that you have to use it. You are wrong, Oracle did not provide flushing the shared pool facility for this, he did because in a very ancient version it was useful, it is not now.
If a DBA accepts to do this, he does not make his job. The only answer to this kind of application is: "he does not work". Full stop. He should never mention flushing something.

Regards
Michel
Previous Topic: virtual memory problem in window oracle database
Next Topic: Oracle Server date
Goto Forum:
  


Current Time: Thu Dec 08 06:04:55 CST 2016

Total time taken to generate the page: 0.07321 seconds