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 spaceI checked the system and found the following:
- The UNIX parameter SHMMAX set to 2147483647 as the maximum value for this parameter.
- The allocated space for the SWAP is 8 GB.
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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.