How to keep an Oracle 11g instance lean?

Question

As a Mac user I run a local Oracle Enterprise Linux (OEL) Developer Day VM that houses an Oracle 11g instance for development/unit testing. I frequently refresh the data in the schemas (sometimes multiple times a day) and do not have a need for snapshots or backups. The number of rows in each schema's tables are orders of magnitude smaller than those of a production instance.

I've had to extend the tablespaces' size more than a few times and I suspect I have improper resource allocation to begin with (SGA, PGA) for the way in which I'm using the instance. I have a similar concern about my sizing/configuration of the VM itself since I'm only using it to house the instance, not for SQL Developer, TimesTen, etc..

I'm looking for recommendations/resources to help me

• Optimize my database instance for a small amount of data in the first place
• 'Clean' my database instance so that I don't need to increase data file size
• Modify my OEL VM to most efficiently run the only thing I need it to run -- the Oracle instance

For example, I have turned off snapshot collection. But I think there are other (more?) impactful changes I can make to help increase the performance and reduce the footprint of VM.

• user953: The instance is memory only; If you drop tables when you refresh them, check docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm check the recycle bin.
• eebbesen: Bullet two addressed -- I cleared my recyclebin and modified the instance to have recyclebin turned off.
• eebbesen: Here's how I did it, by the way: ALTER SYSTEM SET recyclebin = OFF DEFERRED;

Answer 1

The best check for memory allocations is the buffer advisories. These estimate the impact on caching effectiveness of modifying the cache sizes, so you know whether you have the sizes of the PGA and SGA correct, and whether reallocating memory from one to another would be beneficial.

• eebbesen: I'll checkout the [11gR2 memory management docs](Memory Configuration and Use). What I'd love to be able to do is to cache all of the (small) data in the instance)
• eebbesen: Sorry -- should be this link instead of the text 'Memory Configuration and Use'
• David Aldridge: What matters is not whether it's small or not, but how frequently it's accessed. The buffer cache advisor takes that into account and estimates the changes in physical i/o as the cache increases or decreases.
• eebbesen: Before I have a chance to dig into that doc, what is the argument against just keeping all data from all of my tables in the cache (assuming the data size makes this feasible)? My JUnit run should give a consistent picture of physical i/o but the development work I use the instance for (manual testing, etc.) is all over the map depending upon what part of the application my story is on.