it is about one year that I have changed my server from Oracle 10g to 11.2g (and also changed the hardware and Windows from 2003R2 to 2012).

The problem is that with more than doubled cpu power, about 3 times the ram and faster disks, my new server is slower than before!!! I'm investigating on it but having the standard edition one license I haven't diagnostic pack or something else...

One thing that sounds me bad is that on old server, if I set SGA+PGA memory to 16GB, oracle allocate all 16GB immediatelly and I can check it also in windows task manager. Now, my memory_target = memory_max_target = 20GB, but in windows task manager oracle doesn't use more than 10GB of ram. How is it possible? Can I force it to use more ram?

This is my pfile


This is the querty for DB Cache Advice starting from 1


So it seems that doubling cache will improve drammatiacally the performance..

How can I set it in order to use at least 24-28GB of ram? Thanks

2 Answers 2


Oracle doesn't pre-page the SGA by default, meaning that when you start the DB instance, it will not initially grab all the memory until it needs it.

See http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams201.htm#REFRN10174.

I'd try this:

  1. Set memory_target = memory_max_target = 24GB.
  2. Get rid of sga_max_size, sga_target, pga_aggregate_target in your init parameters, unless specifically needed by some crazy vendor app (in other words, let 11g AMM do its thing instead).
  3. Also clear the db_cache_size from your pfile. You were basically limiting your cache to 5.4 GB.
  4. Bounce the instance.
  5. Beat it up with workload for a few hours/days (SQL queries, gather stats, rebuild indexes, RMAN backups, expdp).
  6. Check in Task Manager for memory utilization.

Unset sga_max_size.

sicor.__db_cache_size is the last value used by Automatic memory management (AMM.) While you can clear it from the pfile, Oracle will put it back. The 9GB memory allocation you are seeing at startup is sicor.__sga_target (9097445376), which should be roughly the sum of the sicor.__*_size values.

Even with the sga_*, pga_*, etc. parameters unset and pre_page_sga set, Oracle will not allocate memory_max_target at startup. Some space is reserved for PGA and not allocated until it is needed.

You should also remove pga_aggregate_target=0 unless you specifically need it set that way:

Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_AREA_SIZE parameters.

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.