1

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.

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

1 Answer 1

Reset to default
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.

Improve this answer
4
  • 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, 2013 at 13:22
  • Sorry -- should be this link instead of the text 'Memory Configuration and Use'
    – eebbesen
    Jun 18, 2013 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, 2013 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, 2013 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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