Home » RDBMS Server » Performance Tuning » appropriate memory (Oracle 11g/ 10g/ 9i - Solaris 10)
appropriate memory [message #566521] Sun, 16 September 2012 22:34 Go to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member

Sir/Ma'am,

Any recommendations from you ...

We had this new production database server (re: Oracle Sun M9000 with 32Gb RAM
and 2 x Quad-core CPUs @ 2.8Ghz) for our eServices applications.

Currently, it had an 11 instances installed with 3 diff. Oracle homes
where our newly created database, re: ePortal running in Oracle 11gR2
had 2 instances and have a current memory size (SGA) of 1.5Gb (instance1=idmp) &
3.2Gb (instance2=portal) respectively.

The rest of the 9 instances (8 running in Oracle 10gR2, 1 running in Oracle 9iR1)
have different memory sizes ranging from 500Mb to 1Gb each.

When I sum up all the SGAs, it was almost at 10Gb RAM only.

Although performance-wise, we have no reports that the Apps where too slow,
except that we've always encountered a lost connection (dblink 9i) between the
database and the Apps.

My question is.. is this enough that each of the instances (except for 11gR2)
have a range mostly of less than 1Gb of RAM?

What would be Oracle's recommended size of RAM for an instance?


Re: appropriate memory [message #566522 is a reply to message #566521] Sun, 16 September 2012 22:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what does SGA Advisor suggest for each instance?
Re: appropriate memory [message #566720 is a reply to message #566522] Tue, 18 September 2012 16:37 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Here is a link to the tutorial for running Memory Advisors

Ross Leishman
Re: appropriate memory [message #566908 is a reply to message #566720] Thu, 20 September 2012 18:15 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Reym21,

I personally dont't like many databases nor automatic memory management because I can improve performance by sharing the memory across applications if they are in the same database and find and reduce bottlenecks easier.

If you combine databases you can 1) increase the size of the sga for tables and indexes and reduce physical I/O, and 2) Increase the pga_aggregate_target and more sorts will be done in memory.

I dont like automatic memory management because Oracle can overallocate the buffer pool or shared pool and latching can go up. I also dont like automatic memory management because Oracle spends cycles resizing the PGA, buffer pool and shared pool.

I have three databases running on the same machine and I use the following to monitor Logical reads that runs up cpu and physical reads that causes slowness.

ENWEBP1P > @dba_hist_seg_stat_PHYSICAL_to_LOGICAL_HITR_LAST_7_DAYS_BY_DAILY_TOTALS.sql

DATABASE  Day                         LOGICAL   PHYSICAL HIT_RATIO
--------- -------------------- -------------- ---------- ---------
NWEBP     2012-09-12 Wednesday     6399165072   93365115      98.6
NWEBP     2012-09-13 Thursday      7133812848     773040     100.0
NWEBP     2012-09-14 Friday        8711593904    1508979     100.0
NWEBP     2012-09-15 Saturday      5200628208     152044     100.0
NWEBP     2012-09-16 Sunday        9607073328     778977     100.0
NWEBP     2012-09-17 Monday        7809302128    1956592     100.0
NWEBP     2012-09-18 Tuesday       7398238160    1024693     100.0
NWEBP     2012-09-19 Wednesday     9725321856    3593515     100.0
NWEBP     2012-09-20 Thursday      5679752272     154694     100.0
                               -------------- ----------
avg                                7518320864   11478628

ENWEBP1P > @e ndocp1p
Connected.
ENDOCP1P > @dba_hist_seg_stat_PHYSICAL_to_LOGICAL_HITR_LAST_7_DAYS_BY_DAILY_TOTALS.sql

DATABASE  Day                         LOGICAL   PHYSICAL HIT_RATIO
--------- -------------------- -------------- ---------- ---------
NDOCP     2012-09-12 Wednesday     1418163600    8924001      99.4
NDOCP     2012-09-13 Thursday      1561051520    6706619      99.6
NDOCP     2012-09-14 Friday        1243182192    8826861      99.3
NDOCP     2012-09-15 Saturday      1259762208    6691785      99.5
NDOCP     2012-09-16 Sunday        1699675664    6828664      99.6
NDOCP     2012-09-17 Monday        1553924752    9022716      99.4
NDOCP     2012-09-18 Tuesday       1981066688  657183835      75.1
NDOCP     2012-09-19 Wednesday     1282902528    8980870      99.3
NDOCP     2012-09-20 Thursday       883992016    2192704      99.8
                               -------------- ----------
avg                                1431524574   79484228

ENDOCP1P > @e nalfp1p
Connected.
ENALFP1P > @dba_hist_seg_stat_PHYSICAL_to_LOGICAL_HITR_LAST_7_DAYS_BY_DAILY_TOTALS.sql

DATABASE  Day                         LOGICAL   PHYSICAL HIT_RATIO
--------- -------------------- -------------- ---------- ---------
NALFP     2012-09-12 Wednesday      151637120     346194      99.8
NALFP     2012-09-13 Thursday        97155808      44254     100.0
NALFP     2012-09-14 Friday         103980864     330490      99.7
NALFP     2012-09-15 Saturday        51360032      28642      99.9
NALFP     2012-09-16 Sunday         120618448     117334      99.9
NALFP     2012-09-17 Monday         152161056     379407      99.8
NALFP     2012-09-18 Tuesday        185530064      90666     100.0
NALFP     2012-09-19 Wednesday      140045968     400532      99.7
NALFP     2012-09-20 Thursday        62547760      39870      99.9
                               -------------- ----------
avg                                 118337458     197488

dba_hist_seg_stat_PHYSICAL_to_LOGICAL_HITR_LAST_7_DAYS_BY_DAILY_TOTALS.sql looks like the following:
SET LINES 200
SET WRAP OFF
col "DAY" for a20
col hit_ratio for 999.9
col logical for 9999999999999
col physical for 999999999
set termout off
drop table alan_logical purge;
drop table alan_physical purge;
compute avg of physical on report;
compute avg of logical on report;
break on report
compute sum of total_daily_logical_reads on report
create table alan_logical as 
select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DAT",
sum(b.logical_READS_DELTA) total_logical
from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
where a.object_id=b.OBJ#
and b.logical_READS_DELTA>0
and c.instance_number=(select instance_number from v$instance)
and c.snap_id=b.snap_id
group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
order by 1,2
/
create table alan_physical as 
select to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day') "DAT",
sum(b.physical_READS_DELTA) total_physical
from dba_objects a,dba_hist_seg_stat b,sys.wRM$_SNAPSHOT c
where a.object_id=b.OBJ#
and b.physical_READS_DELTA>0
and c.instance_number=(select instance_number from v$instance)
and c.snap_id=b.snap_id
group by to_char(c.BEGIN_INTERVAL_TIME,'YYYY-MM-DD Day')
order by 1,2
/
set termout on
select d.name database,l.dat "Day",total_logical logical,total_physical physical, 
total_logical/(total_physical+total_logical)*100 Hit_ratio
from alan_physical p, alan_logical l,v$database d
where p.dat=l.dat;

I also use this code to identify excessive physical reads. In the NWEBP database the first day shows excessive physical reads and I created an index and the problem went away. In the NDOCP database, the excessive physical reads is a batch job that runs.
Re: appropriate memory [message #567170 is a reply to message #566522] Mon, 24 September 2012 19:33 Go to previous messageGo to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member

Sir,

Thanks for this.

I have already set the appropriate values using the SGA Advisor.
But what puzzles me is why is it that all-in-all it sums up to
only 10Gb, where is the difference of 20Gb since the dB server
have a 32Gb RAM?

Regards,
Re: appropriate memory [message #567171 is a reply to message #566720] Mon, 24 September 2012 19:35 Go to previous messageGo to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member

Sir,

Thanks for the link.
This is very helpful.

Regards,
Re: appropriate memory [message #567172 is a reply to message #566908] Mon, 24 September 2012 19:39 Go to previous message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member

Sir,

I can only say is "WOW"! This is a very good info.

Thanks for this.

Regards,
Previous Topic: Analytics and Predicate Pushing
Next Topic: What does analyzing a table do for the indexes ?
Goto Forum:
  


Current Time: Thu Mar 28 17:11:26 CDT 2024