Currently we are using 1 GB RAM for SGA. But we are freqently facing ORA-04031 (Unable to allocate 4200 bytes of shared pool memory ("shared pool," unknown object, "kgl simulator," kgl heap)).
For our server some users are connecting through a VPN connection.
For our database, front end applications are COGNOS for report generation. Our users usually run batch programs to generate the reports. While running it is generating a lot of archive logs, almost one archive log for every two minutes (log file size 250MB).
How can I minimize the generation of archive logs?
Under this scenario what is the maximum amount of RAM I can assign for SGA?
As for the minimization of archived logs, you can do that only by minimizing the generation of redo, and the only way to do that is to minimize DML. Now, minimizing DML is not usually a performance-tuning recommendation, but avoiding unnecessary work always is, so try to find out why the reports (which, after all, should be mostly doing reads) are writing to the database so much.
This was first published in July 2005