?
Solved

for dba's -ORA 4032- Unable to allocate <NUM> bytes of shared memory

Posted on 1999-12-07
19
Medium Priority
?
713 Views
Last Modified: 2008-02-26
I am getting the above mentioned error when i run Oracle reportr 2.5 on my clients. Server is Sun Solaris 2.5 Oracle 7.2. Clients are Windows 95.

I have set the shared_pool_size in my init<instance>.ora to the maximum possible value. (If I increse increse this value Oralce instance is not starting).

Any solutions/suggestions to avoid this error are most welcome.

I will increse points to 100, if good suggestions are there.
0
Comment
Question by:sunilkk
  • 5
  • 4
  • 3
  • +4
19 Comments
 
LVL 1

Expert Comment

by:fveillet
ID: 2261853
Does it happen for all the reports you're trying to run?

Does it happen just after you start the Oracle instance or after a few days or weeks?

Does your report call a particularly huge package or store procedure?

Thanx
0
 
LVL 3

Expert Comment

by:bkowalski
ID: 2261989
What is your setting for shared_pool_size?
-Brad
0
 
LVL 2

Expert Comment

by:mkooloos
ID: 2262036
Try to increase the value of the "shared_pool_reserved_size" and/or the "large_pool_size" next to the "shared_pool_size" that you already have increased.

Michael.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 2262328
Try running the report right after restarting Oracle, before other processes run.  Is this a custom report that you (or a developer) may be able to change, or is this part of a canned application that you cannot change?
0
 
LVL 4

Accepted Solution

by:
tbcox earned 100 total points
ID: 2262879
First, you're almost undoubtedly getting ORA-4031, not 4032.  If I'm wrong, please tell me.

Second, if you have not configured your shared memory correctly on Solaris, you can get this error even when your INIT.ORA settings for Shared_Pool are fine.  Or you could have Solaris set up right but Shared_Pool set too small.

Try this:

ALTER SYSTEM FLUSH SHARED_POOL
then
Run the report

See if it works.  If so, go back to your documentation and verify your Solaris shared memory settings.

 -Tom
0
 
LVL 6

Expert Comment

by:mshaikh
ID: 2264160
What is your sharepool size setting?
What is the size of your db_buffer cache?
What is the total memory do you have?
0
 

Author Comment

by:sunilkk
ID: 2264214
fveillet
This does not happen with all the reports. It happens when I have to join a lot of tables (6-7). This happens most of the times with some reports (immediately after instance startup,or any time later.) This does not happen all the times. Very rarely I am able to get these reports without any problems.
I am not calling any procedure or packages.

 bkowalski
shared_pool_size =  7650000

 markgeer
This is a custom report designed by a college of mine using 6-7 tables.
 
tbcox
You are right ERROR IS ORA-4031. It was a typing mistake.
I will try your suggestion today and let you know.

mshaikh
My init<instance>.ora file's settings are as below.

db_files = 20
open_cursors = 255                  
db_file_multiblock_read_count = 16                                    
processes = 50                                                  
dml_locks = 300                        
audit_trail = OS                        max_enabled_roles = 100
# Buffer Cache (includes 6 MB shared sql area)
db_block_buffers = 650                     
shared_pool_size =  7650000

sequence_cache_entries = 30
sequence_cache_hash_buckets = 23                                      

# LGWR
log_buffer = 32768                                                  
log_checkpoint_interval = 10000
# Statistics
#timed_statistics = true                  
 
max_dump_file_size = 10240      # limit trace file size to 5 Meg each
#use_ism = false   # this is the default for Solaris 2.3
 
mts_dispatchers="tcp,3"
mts_max_dispatchers=30
mts_servers=3
mts_max_servers=30
mts_service=<instancename>
mts_listener_address="(ADDRESS=(PROTOCOL=tcp)(host=<instancename>)(port=1521))"

Thanks
George
0
 

Author Comment

by:sunilkk
ID: 2265005
tbcox
My shared pool size is the maximum I can set. When I tried incresing this value, the instance is not starting. I would like to know if any other parameter can be set to increse the SGA size.
0
 
LVL 6

Expert Comment

by:mshaikh
ID: 2265127
Your shared_pool_size is not large. Another thing, shared pool does not come out of you Buffer Cache as the comment says. It is seperate from the Buffer cache.

What is you db_block_size. This is critical to know to find out how much memory you are using.

You can probably reduce the db_block_buffers  from 650 to 500
and try increasing the shared_pool_size.

Other option for you would be to pin the PL/SQL procedure that is causing this error.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 2265158
Your shared_pool_size looks small to me.  On WindowsNT I  usually set the shared_pool_size to 15 - 20MB (about 2 - 3 times bigger than yours).  I wouldn't expect Solaris to limit you to 7.65MB.  Mshaikh knows Solaris better than I do, maybe he will have another suggestion.

A couple other things you could try:
1.) remove tables from the join that are only included to get a descriptive column, and replace them with either stored functions in the database or function columns in the report;
2.) create a view that does the join, then change the report to use the view;

Also test the select statement from the report in SQL*Plus.  Does it work without error there?  If so, the report needs some adjustment.
0
 
LVL 3

Expert Comment

by:bkowalski
ID: 2265420
Again, your shared pool size is too small - typical Oracle installations on Unix are 10 times bigger or more.  We should try to figure out why the instance won't start when you increase it.  What error msgs or symptoms do you see when trying to increase shared pool size?  How much physical RAM on your server? - you may need to install more.  Is the server running anything besides Oracle?
-Brad
0
 
LVL 4

Expert Comment

by:tbcox
ID: 2266136
Ahah!  If your database won't start with a larger setting, and the setting is this small, then you definitely have problems with your shared memory setup.
0
 

Author Comment

by:sunilkk
ID: 2267611
bkowalski
I don't have any other applications on the server.

mshaikh
I will reduce the db_block_buffer and let you know after a couple of days as today is weekend here in middle east.
0
 

Author Comment

by:sunilkk
ID: 2267619
markgeer
My SQL statement at SQL Prompt is running wihtout any problems and it runs in Oracle reports most of the time wihtout any problems. At times I get the above mentioned ORA-4031 error. When error occurs, if I select a smaller range of data (by using from and to date parameters) the error does not occur.
0
 
LVL 3

Expert Comment

by:bkowalski
ID: 2268662
We should still try to figure out why the instance won't start when you increase shared_pool_size.  What error messages or symptoms do you see when trying to increase it?  How much physical RAM on your server?
0
 
LVL 6

Expert Comment

by:mshaikh
ID: 2269561
You instance does not start most likely because you don't have enough share memory and semophore defined for your Solaris.

Refer to you Sparc Oracle Installation booklet to set these up at the OS level. You will have to reboot the machine after you change the shared memory and semphore parameters.
0
 

Author Comment

by:sunilkk
ID: 2275993
Now I found that this problem happens one or two days after the instance is started. If I restart the oracle, the error is not there for 1-2 days.

mshaikh
I reduced the db_block_buffers  from 650 to 500 and its the same result.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 2276839
Your shared_pool_size of only 7.65MB and your cache of only 500 db_block_buffers are both VERY small for typical Oracle databases.  Unless you only have 128MB of RAM you should be able to use much higher values for both.

The fact that the report runs fine right after you restart Oracle, but not after a couple of days, clearly shows that you do not enough room in Oracle's SGA to do the kind of processing that is normal for Oracle.
0
 
LVL 6

Expert Comment

by:mshaikh
ID: 2284354
Please, look at my comment about shared memory parameter:

"You instance does not start most likely because you don't have enough share memory and semophore defined for your Solaris.

Refer to you Sparc Oracle Installation booklet to set these up at the OS level. You will have to reboot the machine after you change the shared memory and semphore parameters."

You won't be able to give Oracle much memory (DB_BLOCK_BUFFERS, SHARED_POOL_SIZE,..) if these paramters
are not set high enough. The required shared memory parameters for Sparc Solaris are describe in your Oracle Installation manual. You should first set these parameter to what Oracle recommends. Then you will be able to increase your Buffers and your pool size.

Also, the fact that the report runs good after you restart is because, when you restart Oracle database, you are essentially starting with an empty Shared pool. So there is all kinds of space to load and run your procedure.  You Shared pool then get fragmented over time as a lot of small procedure and SQL get loaded and the Oracle can't find enough contigous space in the Shared pool to load your procedure. This does not mean the space isn't there. It just means that it is fragmented. You can also sovle this problem by pinning this procedure in to the shared pool immediately after restart of the database.

But, you really need to have more share pool size for the over all performance of your shared pool.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question