Home » RDBMS Server » Performance Tuning » flush shared pool (oracle 8.1.7.0.0, windows 2003 server)
flush shared pool [message #424551] Sat, 03 October 2009 07:34 Go to next message
ranjit@buddhaair.com
Messages: 10
Registered: October 2009
Location: Kathmandu
Junior Member
I have been using oracle 8.1.7.0.0 in windows 2003 server environment. We used to shutdown the database daily with no problem at all. Now we have set it live. My shared pool size is 500MB, block size 8092 and db_block_buffer 76880. After setting it live for three days we got ora-04031, i have to bounce the database. When i checked free memory in V$sgastat it was 110MB but still i got the error. I know most of my developers do not use bind variables which causes fragmentation in shared pool. I am bouncing the database every alternate days and recently i observed that Miscellaneous field in v$sgastat keeps on growing to 180MB where as total shared pool size is 500MB. Tried to flush the shared pool but miscellaneous never flushed, only sql area and library cache are flushed. I have been getting ora-04031 after three days operation. So is it happening due to this miscellaneous field growing bigger? I also read that this is a bug in 8i. So pls suggest.

Rajin Ranjit
Buddha Air, Nepal
Re: flush shared pool [message #424554 is a reply to message #424551] Sat, 03 October 2009 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Upgrade or change the application.

Regards
Michel

[Updated on: Sat, 03 October 2009 09:10]

Report message to a moderator

Re: flush shared pool [message #424577 is a reply to message #424554] Sat, 03 October 2009 22:51 Go to previous messageGo to next message
ranjit@buddhaair.com
Messages: 10
Registered: October 2009
Location: Kathmandu
Junior Member
I am looking for the solution where i don't want to upgrade and change the application.

Rajin
Re: flush shared pool [message #424578 is a reply to message #424577] Sat, 03 October 2009 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
rajinranjit wrote on Sat, 03 October 2009 20:51
I am looking for the solution where i don't want to upgrade and change the application.


Then continue to restart the DB every 24 hours.
Re: flush shared pool [message #424590 is a reply to message #424578] Sun, 04 October 2009 01:32 Go to previous messageGo to next message
ranjit@buddhaair.com
Messages: 10
Registered: October 2009
Location: Kathmandu
Junior Member
If i know i shall get this kind of response from this forum then i would have never join this forum. This is not any expert's answer. Upgarding and changing is the last thing i want to do, but in the mean time if there is any solution then why should i do that? You have not looked at my question so far, my question is why the miscellaneous field in v$sgastat keeps on increasing and why it is not flushed when i issue the command alter system flush shared_pool. So my question to Black Swan and other expert's is if you have any problem in your system, then your suggestion will be to change or upgrade the system, is that correct? Do you think by giving this kind of response you will be rated as top expert, then you are wrong. If you can't suggest, then do not reply.

Rajin
Re: flush shared pool [message #424592 is a reply to message #424590] Sun, 04 October 2009 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If you can't suggest, then do not reply.

If you don't want some answers why do you post in forum?
Flush shared pool and shared pool itself work how they work in your version. If you don't want how they work in your version, change version, if you don't want to change version then you stay with how they work in your version.
What do you expect us to tell you? Above all in a version that was released in the previous century and you never patched to fix some bugs.

I forgot to mention also in a configuration that was never supported by Oracle (8i/W2003).

Regards
Michel

[Updated on: Sun, 04 October 2009 01:55]

Report message to a moderator

Re: flush shared pool [message #424595 is a reply to message #424592] Sun, 04 October 2009 07:30 Go to previous messageGo to next message
ranjit@buddhaair.com
Messages: 10
Registered: October 2009
Location: Kathmandu
Junior Member
If you know that this is a bug then you should suggest me about the patch. There are problems in every version, so would you suggest not to use oracle. You are just talking like you are in 25th century. Do you think you are the only expert in this forum, my questions shall be answered by other experts. If you can't go to the root of the problem, you never suggest anybody like this, this is the worst advise i have ever get.

Rajin
Re: flush shared pool [message #424604 is a reply to message #424595] Sun, 04 October 2009 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I doubt your behaviour leads to any other answer.
You don't want to upgrade, you don't want to change the application, so what do you expect?
Yes upgrade to at least 8.1.7.4 but you still stay in a not supported configuration.
If you don't like the answer you get for free, pay Oracle to answer you (and I'm laughing thinking what will be its answer).

Regards
Michel
Re: flush shared pool [message #424609 is a reply to message #424595] Sun, 04 October 2009 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool.
// *Action: If the shared pool is out of memory, either use the
//          dbms_shared_pool package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          INIT.ORA parameters "shared_pool_reserved_size" and 
//          "shared_pool_size".
//          If the large pool is out of memory, increase the INIT.ORA
//          parameter "large_pool_size".  
Re: flush shared pool [message #424621 is a reply to message #424604] Sun, 04 October 2009 23:25 Go to previous messageGo to next message
ranjit@buddhaair.com
Messages: 10
Registered: October 2009
Location: Kathmandu
Junior Member
My ultimate target is to change the version, by the time i thought if i get some good suggestions which can be applied instantly, then i will have some time to think it over. I have been searching thousands of websites which have suggested some new things and i have been learning very new things. My suggestion to you, if you don't know the answer do not reply to anybody else in a way you have answered my question. This is very frustrating. Are you sure that there is no way out to this problem? I can suggest you few options which shall be applicable and you will face no problem, i can challenge you. If somebody ask me about this i can provide solutions to this. I am just testing your capability.

Rajin
Re: flush shared pool [message #424622 is a reply to message #424609] Sun, 04 October 2009 23:29 Go to previous messageGo to next message
ranjit@buddhaair.com
Messages: 10
Registered: October 2009
Location: Kathmandu
Junior Member
Thanks Black Swan, this is what i called is a good approach to the problem.

Rajin
Re: flush shared pool [message #424636 is a reply to message #424622] Mon, 05 October 2009 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rajinranjit wrote on Mon, 05 October 2009 06:29
Thanks Black Swan, this is what i called is a good approach to the problem.

Rajin

It is just an extract of error message you can read in Error Messages book or get using oerr tool.
You should use them before posting (and it is assumed you did it when you post).

By the way, if your application does not use bind variables, you can set SGA as big as you can you will sooner or later fall in the same error.
More, bigger you will set the SGA worse will be the performances.

Regards
Michel

[Updated on: Mon, 05 October 2009 00:57]

Report message to a moderator

Re: flush shared pool [message #424699 is a reply to message #424621] Mon, 05 October 2009 07:17 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Metalink have the definitive document on ORA-04031 errors - Document id 396940.1 - Troubleshooting and Diagnosing ORA-4031 Error

Other than that, the easiest two steps are
1) To use DBMS_SHARED_POOL to pin all the packages in memory
2) Increase the size of the SGA - I think there are a range of parameters that needed altering, butI can't remember, as I've not done DBA work on an 8i database this century.

Metalink Document 316138.1 describes the V$sgastat problem - whether this is the cause, only they can tell you, and as you're running an obsolete version of Oracle in an unsuported configuration, I'd be suprised if they were much help.

You could try setting STATISTICS_LEVEL=BASIC in your init ora file.

Previous Topic: Does "alter system flush shared_pool" statement impact to performance?
Next Topic: Too many columns in one index
Goto Forum:
  


Current Time: Sun Dec 04 12:40:00 CST 2016

Total time taken to generate the page: 0.05578 seconds