Ask the Expert

Minimizing generation of archive logs

We are using Oracle 9.2.0.1.0 Enterprise Edition database under 32-bit Windows 2003 Enterprise Server operating system. The server is dedicated only to database. At this moment we have 12GB of data. The Dell server has 4 CPUs and 16 GB RAM with PAE (Physical Address Extension).

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?

    Requires Free Membership to View

The issue here is not just the size of your SGA, but how you're managing its various components. What is your shared_pool_size? You probably need to increase it. You should also take advantage of Enterprise Manager's advisories (Instance->Configuration->Memory), or use the underlying v$shared_pool_advice view to determine an appropriate size for your shared pool.

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: