Re: memory usage, 2 javas, 2 apaches?

"Amy Hughes" <amylugnews_at_hotmail.com> wrote in message news:e6e16b3b.0305170810.7ba847df_at_posting.google.com...
> I'm looking to learn Oracle, so I purchased a CD pack, a one-year
> Personal license, a Wrox "Beginning Oracle Programming" book

An *excellent* choice, Amy !! ;-)

>and Alice
> Rischert's "Oracle SQL" book. I installed Oracle and the documentation
> last night, then uninstalled the extra-cost options (data mining,
> spatial, etc).
>

Shame about the Personal Oracle decision, though. You'll find that whilst much is common between the various versions of Oracle, some key bits and pieces are missing from Personal which would be in Enterprise Edition. I always advise learning with Enterprise Edition... largely because it's as complete a version as you can possibly get, and also because it's entirely free to do so (it can be downloaded for free at http://technet.oracle.com).

> I'll probably run into answers as I do my reading but right now my
> system has become bloated and I'd really like to use it for other
> things, so I thought I might pose these questions...
>
> According to task manager I have two copies of java.exe running using
> ~18M and ~10M, and I have two copies of Apache.exe running using ~10M
> and ~31M. Oracle.exe is using ~103M. Is this normal? I've included the
> output of SQL*Plus "select * from v$sgastat;" below.
>
> I'm wondering if Oracle decided it could use that much memory just
> because it was available (I have 512M in a 1.7G Celeron system running
> Win2K Pro), and if I add more if it'll decide it can use it, too?

No. Oracle uses the memory specified in the init.ora, and that's it. However, if you used the database configuration assistant to create your database, then that certainly has a page where you can make the thing use a fixed percentage of the available memory on the box... but again, that means the settings for the init.ora are then made on that basis, and adding more memory won't cause those settings to be dynamically adjusted. We don't have a self-tuning database in Oracle. Yet (but brace yourself for 10i, 11i or 22-and-a-half i).

> Right now task manager shows I'm using about 410M total with Oracle,
> Apache and SQL*Plus running (and other stuff). About 200M of this is
> freed when I shutdown Oracle.

Two things off the top of my head: you shared_pool_size appears to be 48M, and you db_cache_size is 24M. You can trim shared_pool down to 32M relatively comfortably, and for testing/learning, a db_cache_size of 8M is OK.

Other things ...

Your pga_aggregate_target is 24M... it can be taken down to a minimum of 10M without too much drama.

You have a java pool... are you going to be doing java work? If not, set it to zero.

You have a large pool... are you going to be doing RMAN backups, running Shared Server configuration, or doing heaps of Parallel queries? If not, set that to zero too.

>
> Also, my hard disk is accessed about once per second. This is a new
> behavior since installing Oracle. What's it doing?

Probably incremental checkpointing. Controllable with a heap of parameters, such as log_checkpoint_interval, fast_start_io_target and (because this is a 9i) fast_start_mttr_target (which gets converted into settings for the other two). Right now you are asking Oracle to ensure you never take longer than 300 seconds to recover your instance, so it checkpoints as often as necessary to achieve that (instance recoveries requiring all redo generated after the last checkpoint to be read and/or re-played, so the more checkpoints you have, the less work you have to do when recovering).

Watch out when re-setting all these parameters: being 9i, there could well be an spfile lurking in the undergrowth. If there is, you can edit the init.ora all you like, but it will make not the slightest bit of difference. You therefore either need to get rid of the spfile and make your init.ora the working document. Or you need to use 'alter system set....' commands to edit the spfile directly.

Regards
HJR Received on Sat May 17 2003 - 17:58:53 CDT