| flush shared pool [message #424551] |
Sat, 03 October 2009 07:34  |
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 #424590 is a reply to message #424578] |
Sun, 04 October 2009 01:32  |
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 |
 |
Michel Cadot
Messages: 68653
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 |
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 #424609 is a reply to message #424595] |
Sun, 04 October 2009 10:20 |
 |
BlackSwan
Messages: 26766
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 |
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 #424636 is a reply to message #424622] |
Mon, 05 October 2009 00:54 |
|
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
rajinranjit wrote on Mon, 05 October 2009 06:29Thanks 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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
