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

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.
sunilkkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fveilletCommented:
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
bkowalskiCommented:
What is your setting for shared_pool_size?
-Brad
0
mkooloosCommented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Mark GeerlingsDatabase AdministratorCommented:
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
tbcoxCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mshaikhCommented:
What is your sharepool size setting?
What is the size of your db_buffer cache?
What is the total memory do you have?
0
sunilkkAuthor Commented:
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
sunilkkAuthor Commented:
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
mshaikhCommented:
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
Mark GeerlingsDatabase AdministratorCommented:
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
bkowalskiCommented:
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
tbcoxCommented:
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
sunilkkAuthor Commented:
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
sunilkkAuthor Commented:
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
bkowalskiCommented:
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
mshaikhCommented:
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
sunilkkAuthor Commented:
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
Mark GeerlingsDatabase AdministratorCommented:
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
mshaikhCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.