| appropriate memory [message #566521] |
Sun, 16 September 2012 22:34  |
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 #566908 is a reply to message #566720] |
Thu, 20 September 2012 18:15  |
 |
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.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
