Oracle 11.2 using too few memory

Question

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

sicor.__db_cache_size=5796528128
sicor.__java_pool_size=58720256
sicor.__large_pool_size=12582912
sicor.__pga_aggregate_target=12377391104
sicor.__sga_target=9097445376
sicor.__shared_io_pool_size=0
sicor.__shared_pool_size=3154116608
sicor.__streams_pool_size=25165824
*.db_block_size=8192
*.distributed_lock_timeout=300
*.job_queue_processes=1000
*.memory_max_target=21474836480
*.memory_target=21474836480
*.open_cursors=1500
*.parallel_automatic_tuning=FALSE
*.parallel_max_servers=80
*.parallel_servers_target=32
*.parallel_threads_per_cpu=2
*.pga_aggregate_target=0
*.processes=1200
*.sessions=1325
*.sga_max_size=12884901888
*.sga_target=0

This is the querty for DB Cache Advice starting from 1

5536____682312__1_______39052864
5984____737528__0,9056__35366617
6528____804576__0,8461__33043015
7072____871624__0,8149__31825392
7616____938672__0,789___30813474
8160____1005720_0,766___29912665
8704____1072768_0,7442__29064260
9248____1139816_0,7219__28193363
9792____1206864_0,6997__27324768
10336___1273912_0,6778__26468501
10880___1340960_0,6558__25610722

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

Answer 1

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.

Answer 2

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.