Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
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. –  ik_zelf Jun 17 '13 at 4:46
    
Bullet two addressed -- I cleared my recyclebin and modified the instance to have recyclebin turned off. –  eebbesen Jun 17 '13 at 19:04
    
Here's how I did it, by the way: ALTER SYSTEM SET recyclebin = OFF DEFERRED; –  eebbesen Aug 6 '13 at 18:01
add comment

1 Answer

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.

share|improve this answer
    
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 Jun 18 '13 at 13:22
    
Sorry -- should be this link instead of the text 'Memory Configuration and Use' –  eebbesen Jun 18 '13 at 13:33
1  
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. –  David Aldridge Jun 18 '13 at 14:23
1  
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. –  eebbesen Jun 18 '13 at 15:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.