Dedicated Database Server - SGA,PGA Parameter Values (ORACLE)

[Former Member]

We have done OSS Note Research and done quite a good study on SDN aswell.

Can anyone suggest a rule for OLAP systems for Dedicated Database server values for

SGA_MAX_SIZE

DB_CACHE_SIZE

SHARED_POOL_SIZE

PGA_AGGREGATE_TARGET

According to OSS Note u2013 619876

PGA_AGGREGATE_TARGET = Total physical memory * 40%,

pga_aggregate_target >= (sort_area_size + hash_area_size+...) * (#shadow processes / 10)

No. of oracle Process = #ABAP work processes * 2 + #J2EE server processes * <max-connections> + PARALLEL_MAX_SERVERS + 40.

Above is the only information I found and nothing on DB_CACHE_SIZE and SGA_MAX_SIZE.

Here is what we have currently.

SGA_MAX_SIZE =2.16GB (DB_CACHE_SIZE = 1.08, SHARED_POOL_SIZE =1.08 which are part of SGA)

PGA_AGGREGATE_TARGET = 1.08GB

No. of oracle Process = 1000.

We have 1 CI with 4 apps server.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<HERE IS MY ESTIMATION>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SGA_MAX_SIZE + PGA_AGGREGATE_TARGET < = Physical Memory (80% of RAM)

Physical Memory Available = 10GB (RAM)

PGA_AGGREGATE_TARGET = 40% of 8GB RAM = 3.2GB

SGA_MAX_SIZE = 3.2GB

DB_CACHE_SIZE = 1.6 GB

SHARED_POOL_SIZE = 1.6 GB

No. of oracle Process = 1000.

Can BI/Oracle gurus help us with the above...

THanks

Tots

[Former Member · Accepted solution]

Thank you for your reply.

As a BI System Health check I have to suggest parameters.Hence I sent DBA team my suggestions for OLAP.I have definately considered bunch of OSS Notes including Note 830576.

On my suggestion here is what I have received from DBA team...

**********************************************************************************************************

Memory ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────│

│ Physical PageSpace | pages/sec In Out | FileSystemCache │

│% Used 86.7% 0.4% | to Paging Space 0.0 0.0 | (numperm) 7.2% │

│% Free 13.3% 99.6% | to File System 0.0 0.0 | Process 59.2% │

│MB Used 8879.9MB 93.1MB | Page Scans 0.0 | System 20.3% │

│MB Free 1360.1MB 23970.9MB | Page Cycles 0.0 | Free 13.3% │

│Total(MB) 10240.0MB 24064.0MB | Page Steals 0.0 | -

---

│

│ | Page Faults 2375.4 | Total 100.0% │

│----

---

| numclient 7.2% │

│Min/Maxperm 483MB( 5%) 773MB( 8%) <--% of RAM | maxclient 7.5% │

│Min/Maxfree 960 1088 Total Virtual 33.5GB | User 61.3% │

│Min/Maxpgahead 2 8 Accessed Virtual 7.8GB 23.2%| Pinned 24.6% │

│───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────│

Indicates that available memory is allocated appropriately and no significant surplus exists for further database allocations.

As per PBW cache hit statistics cumulative since last startup (6/9/2009 1:13:59.000 PM):

Consistent Gets DB Block Gets Physical Reads Hit Ratio

67,715,496,894 27,224,561,257 2,918,336,085 96.93

Gets Get Misses Hit Ratio

1,726,552,775 30,588,783 98.23

Memory Sorts Disk Sorts Memory Ratio

64,936,008 1,429 100

EXECUTIONS Cache Misses Miss Ratio Hit Ratio

3,664,195,096 2,017,904 0.06 99.94

Cache hit ratios are VERY GOOD indicating that additional database cache memory allocations would not yield significant improvements.

Further analysis of V$PGA_TARGET_ADVICE, V$DB_CACHE_ADVICE, V$SHARED_POOL_ADVICE @PBW corroborates that current database cache memory settings are appropriate and that additional database cache memory allocations would not yield significant improvements.

*****************************************************************************************

I do see MB Used : 8879.9MB - where could the memory consumed if SGA=2.16 and PGA=1.08.

I dont have much of System Access,I have to give my opinion on init<SID>.ora parameters which they passed on to me.

So ST04 analysis is out for time being.

Here is what we have currently....

SGA_MAX_SIZE =2.16GB

DB_CACHE_SIZE = 1.08

SHARED_POOL_SIZE =1.08

Processes = 1000

PGA_AGGREGATE_TARGET = 1.08GB

No. of oracle Process = 1000.

We have 1 CI with 4 apps server with Dedicate Database

CI and Apps are - ABAP+JAVA

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<HERE IS MY ESTIMATION>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SGA_MAX_SIZE + PGA_AGGREGATE_TARGET < = Physical Memory (80% of RAM)

Physical Memory Available = 10GB (RAM)

PGA_AGGREGATE_TARGET = 40% of 8GB RAM = 3.2GB

SGA_MAX_SIZE = 3.2GB

DB_CACHE_SIZE = 1.6 GB

SHARED_POOL_SIZE = 1.6 GB

No. of oracle Process = 1000.

So how strongly should I push my suggestions to DBA team....

We can definitely do a detail analysis which I have suggested..but if you were a senior Basis and BI team wants you to revisit to the parameter values with SAP Recommended values....what would you go with.(without much analysis).

Thanks

[Former Member · Accepted solution]

Hello,

The 8879.9 MB of memory in use is tht server-wide total and includes memory used by the O/S, non-Oracle processes, file cache pages etc. The fact that this represents almost 90% of overall memory is not a problem. UNIX will always try to fill the available memory; as long as you do not observe significant page-out rates at O/S level, things are OK.

The Oracle cache hit rate of 96.93% is not phenomenal but it is well inside recommended limits. I would not advise arbitrarily enlarging the cache especially if your DBA teams has looked at V$DB_CACHE_ADVICE and found that no significant improvement can be expected with a bigger cache. It might perhaps be worthwhile to look at the SQL Cache to identify individual statements with a low hit rate (high ratio of disk reads to buffer gets).

The shared pool is perhaps a bit oversized at 1.08 GB. Its size is dynamic (parameter shared_pool_size) but as there does not seem to be an immediate need for more memory you can just leave it as it is.

One thing I always do when configuring Oracle memory parameters is setting SGA_MAX_SIZE to a value greater than the sum of db_cache_size and shared_pool_size. That way you can enlarge the caches without the need to stop/restart the database (sga_max_size is a static parameter, the other two are dynamic). In your current configuration for example, I'd set sga_max_size to 3 GB, which would give me ~1 GB to play with.

Regards,

Mark

[Former Member]

You are absolutely correct with the calculations.

Thing is, you monitor the ST14 data buffer quality and if you find any degrade in that then we can think about some resizing of these parameters.

There are some other crucial parameters which need to be set in an OLAP system for a better performance. Hope you might have done that.

Thank you,

Tilak

[Former Member]

I think Tilak meant ST04 for data buffer quality check. You may want to have a look at Note 830576 - Parameter recommendations for Oracle 10g.