No question at this time
DBA Top 10
1 B. Vroman 14600
2 M. Cadot 11000
3 J. Schnackenberg 8200
4 T. Boles 7950
5 A. Kavsek 6200
6 M. Hidayathullah ... 2200
7 G. Lambregts 1100
8 T. P 1000
9 P. Wisse 900
10 B. Derous 500
10 . Lauri 500
10 R. Pattyn 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48286
Total active users1591
Act. users last 24h3
Act. users last hour0
Registered user hits last week242
Registered user hits last month1121
Go up

Oracle is using more physical memory than available on server.
Next thread: LOST OCR ON RAC ENV
Prev thread: Relation between RESOURCE_NAME and HV_ID

Message Score Author Date
I have 3 instances running on one server. <b>Ins...... abhishek jindal Oct 25, 2018, 20:37
On modern OS, Oracle does not allocate the whole...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Michel Cadot Oct 25, 2018, 21:39
Hello Michel, <pre> SQL> select * from v$sga; ...... abhishek jindal Oct 25, 2018, 21:54
>>> <i> Also is there any way to check what was ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Oct 26, 2018, 08:36

Follow up by mail Click here


Subject: Oracle is using more physical memory than available on server.
Author: abhishek jindal, India
Date: Oct 25, 2018, 20:37, 24 days ago
Os info: AIX 7
Oracle info: 11.2.0.4
Message: I have 3 instances running on one server.
Instance1
sga_max_size=5 GB
sga_target =0
memory_max_target = 5 GB
memory_target=5 GB
pga_aggregate_target =2 GB

Instance2
sga_max_size=110 GB
sga_target =0
memory_max_target = 110 GB
memory_target=100 GB
pga_aggregate_target =2 GB

Instance3
sga_max_size=24 GB
sga_target =0
memory_max_target = 24 GB
memory_target=24 GB
pga_aggregate_target =0

Server Physical Ram = 130GB
Server Paging Space = 20GB

Below is the output of command nmon
Memory ──────────────────────────────────────────────────────────────────────────────────────────────────────────────

│ Physical PageSpace | pages/sec In Out |FileSystemCache
│% Used 95.6% 1.0% | to Paging Space 0.0 0.0 | (numperm) 21.4%
│% Free 4.4% 99.0% | to File System 0.0 5.0 | Process 65.7%
│GB Used 124.2GB 0.2GB | Page Scans 0.0 | System 8.5%
│GB Free 5.8GB 19.8GB | Page Cycles 0.0 | Free 4.4%
│Total(GB) 130.0GB 20.0GB | Page Steals 0.0 | ------
│ | Page Faults 31.0 | Total 100.0%
│------------------------------------------------------------ | numclient 21.4%
│Min/Maxperm 6410MB( 5%) 115379MB( 90%) <--% of RAM | maxclient 90.0%
│Min/Maxfree 960 1088 Total Virtual 150.0GB | User 83.6%
│Min/Maxpgahead 2 8 Accessed Virtual 95.3GB 63.5%| Pinned 11.2%


We confirmed with Unix Admin that The Free paging space (free swap in AIX jargon) is near 100% all year, which means that the swap usage is almost zero.

So my question is can some one please explain to me how Oracle instances are configured for more memory than available are working perfectly fine without using any swap space.?
Please let me know if any more detail is required.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Oracle is using more physical memory than available on server.
Author: Michel Cadot, France
Date: Oct 25, 2018, 21:39, 24 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message:
On modern OS, Oracle does not allocate the whole SGA at startup but when needed.
You can check this using:
ipcs -m (at OS level)
select * from v$sga; (within SQL*Plus)

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Oracle is using more physical memory than available on server.
Author: abhishek jindal, India
Date: Oct 25, 2018, 21:54, 24 days ago
Message: Hello Michel,

SQL> select * from v$sga;

NAME VALUE
-------------------- ----------------
Fixed Size 2,258,352
Variable Size 17,783,851,600
Database Buffers 7,851,737,088
Redo Buffers 16,904,192


Variable size can decrease or increase depending on database load.I can assume that on peak load, instance will use all the physical memory as defined by sga_max_size or memory_target parameter.
Am I right.?

Also is there any way to check what was actual memory used (allocated to) by instance in past 2-3 months if we have awr data retention period of only 14 days.?
In OEM I see only full memory usage as defined by parameters not actual allocation.

Thanks
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Oracle is using more physical memory than available on server.
Author: Michel Cadot, France
Date: Oct 26, 2018, 08:36, 23 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message:
>>> Also is there any way to check what was actual memory used (allocated to) by instance in past 2-3 months if we have awr data retention period of only 14 days.?

No, unless you store it somewhere yourself.

>>> Variable size can decrease or increase depending on database load.

Database buffers as well but the whole SGA allocated from the OS can only increase.

>>> In OEM I see only full memory usage as defined by parameters not actual allocation.

v$sgainfo and v$sga_dynamic_components give you the current size of the different components.
v$sga_resize_ops gives you the resize operations during the instance life.

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here