Ask the Expert

Any restrictions on SHARED_POOL_SIZE or SGA?

As you suggested, I monitored the free memory form v$sgastat and I found it almost 235 K, therefore I decided to increase the SHARE_POOL_SIZE to 500000000 instead of 450000000, but I got the following error while staring the database instance: 
 SVRMGR> Connected to an idle instance.
 SVRMGR> ORA-07307: sms1sg: shmat error, unable to
 attach sga.
 SCO System V/386 Error: 12: Not enough space
I checked the system and found the following:
  1. The UNIX parameter SHMMAX set to 2147483647 as the maximum value for this parameter.
  2. The allocated space for the SWAP is 8 GB.
Please be informed that the physical memory for the server is 4GB. Is there any restriction that prevents me from increasing the SHARED_POOL_SIZE or the SGA? Thanks in advance for your cooperation.

    Requires Free Membership to View

    Login

Your own query found that you have 235K of free memory in the Shared Pool. That is more than enough! For most of my installations, I don't even have a Shared Pool anywhere near 235K, let alone have that much free memory. If anything, I suggest that you decrease your Shared Pool. Such a large Shared Pool has historically led to poor performance. It is a myth that throwing more memory will always lead to better performance.

I couldn't find any documentation that limits the VALUE of SHARED_POOL_SIZE since your database version is older (Oracle 7). This parameter's range of values is OS dependent. So you'll have to check the documentation for your specific version to see it's allowable range of values. In addition, the total SGA may be limited for this platform and version. And you may be nearing this limitation. Again, check your documentation.

One other thing to check is whether or not your application is using bind variables. Lack of bind variables can lead to too much dependence on the Shared Pool and causes many DBAs to inflate the size of this SGA structure. Using bind variables will make a huge difference in application and database performance.

For More Information


This was first published in November 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top