Q

Minimizing generation of archive logs

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. How can I minimize the 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?

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

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close