关于SGA分配的一个弱弱的问题 - Oracle专题深入讨论 - ITPUB论坛

catan
为水系风－吉他手孔乙己

精华贴数 0
个人空间 0
技术积分 237 (8102)
社区积分 62 (4365)
注册日期 2003-4-3
论坛徽章:0

#1

使用道具

发表于 2008-5-14 11:35

关于SGA分配的一个弱弱的问题

我用windows xp系统做了个试验环境
默认安装的，通用数据库
版本是9.2.0.1.0

默认安装后，我想修改一下sga的大小
此时查看oracle进程占用内存在120M左右
我通过
SQL> alter system set SGA_MAX_SIZE= 150M scope=spfile;
System altered.

然后我shutdown
再startup

show sga
SQL> show sga

Total System Global Area  160504432 bytes
Fixed Size                453232 bytes
Variable Size          117440512 bytes
Database Buffers          41943040 bytes
Redo Buffers                667648 bytes
但是，我发现windows下oracle的进程占用的内存变小了了，才90M左右
为什么
不应该增加到150M多吗？

__________________

“回”帖的“回”字有四种写法：“迴”“囬”“囘”“廻”

有老程序员，一日得成仙。
学富足五车，概只十字言。
叮嘱众子弟，代代要相传。
“硬件须接地，软件要存盘”。

只看该作者

catan
为水系风－吉他手孔乙己

精华贴数 0
个人空间 0
技术积分 237 (8102)
社区积分 62 (4365)
注册日期 2003-4-3
论坛徽章:0

#2

使用道具

发表于 2008-5-14 13:04

考虑虚拟内存的问题
我执行了：
SQL> alter system set pre_page_sga=true scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

监控oralce 进程
发现仍然是100m左右比原来多点不多

__________________

“回”帖的“回”字有四种写法：“迴”“囬”“囘”“廻”

有老程序员，一日得成仙。
学富足五车，概只十字言。
叮嘱众子弟，代代要相传。
“硬件须接地，软件要存盘”。

只看该作者

zf_wu
老会员

精华贴数 0
个人空间 330
技术积分 1234 (1411)
社区积分 52 (4757)
注册日期 2003-4-4
论坛徽章:1

#3

使用道具

发表于 2008-5-14 15:52

SGA_MAX_SIZE只是限制最大分配量而不是实际的分配量.SGA_MAX_SIZE实质是在进程中预先分配一段虚拟地址,(并不一定分配实际的内存),以免私有地址的冲突

__________________

http://tahiti.oracle.com http://asktom.oracle.com http://metalink.oracle.com http://www.ixora.com.au http://www.orafaq.netMy Blog: http://BreezeWu@itpub.net

只看该作者

catan
为水系风－吉他手孔乙己

精华贴数 0
个人空间 0
技术积分 237 (8102)
社区积分 62 (4365)
注册日期 2003-4-3
论坛徽章:0

#4

使用道具

发表于 2008-5-14 17:05

谢谢楼上的回复

我再看看书学习学习

__________________

“回”帖的“回”字有四种写法：“迴”“囬”“囘”“廻”

有老程序员，一日得成仙。
学富足五车，概只十字言。
叮嘱众子弟，代代要相传。
“硬件须接地，软件要存盘”。

只看该作者

晶晶小妹
月是上弦

精华贴数 3
个人空间 6470
技术积分 1835 (892)
社区积分 9 (11681)
注册日期 2008-2-15
论坛徽章:2

#5

使用道具

发表于 2008-5-15 00:08

你可以了解一下9i的动态SGA部分

__________________

没有必胜的秘籍，没有方程式遵循
要赢~只有全身心的投入！

为了方便大家查阅，所有的文章都已转入空间

http://space.itpub.net/?13095417

请大家多多支持！

只看该作者

catan
为水系风－吉他手孔乙己

精华贴数 0
个人空间 0
技术积分 237 (8102)
社区积分 62 (4365)
注册日期 2003-4-3
论坛徽章:0

#6

使用道具

发表于 2008-5-15 10:21

谢谢晶晶版主

__________________

“回”帖的“回”字有四种写法：“迴”“囬”“囘”“廻”

有老程序员，一日得成仙。
学富足五车，概只十字言。
叮嘱众子弟，代代要相传。
“硬件须接地，软件要存盘”。

只看该作者

kdkd
一般会员

精华贴数 0
个人空间 0
技术积分 1886 (851)
社区积分 28 (6460)
注册日期 2005-4-29
论坛徽章:8

#7

使用道具

发表于 2008-5-18 23:36

QUOTE:

原帖由 zf_wu 于 2008-5-14 15:52 发表

SGA_MAX_SIZE只是限制最大分配量而不是实际的分配量.SGA_MAX_SIZE实质是在进程中预先分配一段虚拟地址,(并不一定分配实际的内存),以免私有地址的冲突

很好的解释

只看该作者

Yong Huang
版主

精华贴数 2
个人空间 0
技术积分 4166 (339)
社区积分 129 (2998)
注册日期 2001-10-9
论坛徽章:6

#8

使用道具

发表于 2008-5-19 07:42

QUOTE:

原帖由 zf_wu 于 2008-5-14 01:52 发表

SGA_MAX_SIZE只是限制最大分配量而不是实际的分配量.SGA_MAX_SIZE实质是在进程中预先分配一段虚拟地址,(并不一定分配实际的内存),以免私有地址的冲突

If I remember right, Oracle allocates all memory specified by sga_max_size. Here's something everybody working on Windows should understand about a Windows process:

Virtual memory is measured by perfmon virtual bytes;
Working set (resident set) part of virtual memory is Mem Usage in Task Manager (TM);
Private part of virtual memory is VM Size in Task Manager.

Sounds confusing? Just read it a few times and memorize it. Process virtual memory can be split into working set and non-working set (the part paged out). TM Mem Usage measures the first part only. Alternatively, virtual memory can also be split into private memory and shared memory. TM VM Size measures the first part only.

catan's problem is due to incomplete measurement. He's probably looking at Mem Usage which hasn't reached its normal workload level yet. The only reliable way is to use perfmon, and look at oracle.exe process's virtual bytes or use Process Explorer to look at virtual size.

I'll be surprised if Oracle decides to allocate less than sga_max_size virtual memory for oracle.exe on instance startup. On UNIX/Linux, that might be true if you turned off _use_ism; even then I doubt it but we can verify. On Windows, _use_ism is unlikely to have any effect at all.

Yong Huang

只看该作者

shiri512003
弦乐之花

精华贴数 0
个人空间 0
技术积分 1794 (920)
社区积分 4 (18457)
注册日期 2006-7-13
论坛徽章:5

#9

使用道具

发表于 2008-5-19 17:12

QUOTE:

原帖由 Yong Huang 于 2008-5-19 07:42 发表

If I remember right, Oracle allocates all memory specified by sga_max_size. Here's something everybody working on Windows should understand about a Windows process:

Virtual memory is measured by perfmon virtual bytes;
Working set (resident set) part of virtual memory is Mem Usage in Task Manager (TM);
Private part of virtual memory is VM Size in Task Manager.

Sounds confusing? Just read it a few times and memorize it. Process virtual memory can be split into working set and non-working set (the part paged out). TM Mem Usage measures the first part only. Alternatively, virtual memory can also be split into private memory and shared memory. TM VM Size measures the first part only.

catan's problem is due to incomplete measurement. He's probably looking at Mem Usage which hasn't reached its normal workload level yet. The only reliable way is to use perfmon, and look at oracle.exe process's virtual bytes or use Process Explorer to look at virtual size.

I'll be surprised if Oracle decides to allocate less than sga_max_size virtual memory for oracle.exe on instance startup. On UNIX/Linux, that might be true if you turned off _use_ism; even then I doubt it but we can verify. On Windows, _use_ism is unlikely to have any effect at all.

Yong Huang

shirir512003mark

__________________

让一切都沉寂的最强小夜曲
绝对的力量，并不是决定命运的全部理由
绝对的意志，并不是决定命运的全部理由

只看该作者

catan
为水系风－吉他手孔乙己

精华贴数 0
个人空间 0
技术积分 237 (8102)
社区积分 62 (4365)
注册日期 2003-4-3
论坛徽章:0

#10

使用道具

发表于 2008-5-27 12:36

QUOTE:

原帖由 Yong Huang 于 2008-5-19 07:42 发表

If I remember right, Oracle allocates all memory specified by sga_max_size. Here's something everybody working on Windows should understand about a Windows process:

Virtual memory is measured by perfmon virtual bytes;
Working set (resident set) part of virtual memory is Mem Usage in Task Manager (TM);
Private part of virtual memory is VM Size in Task Manager.

Sounds confusing? Just read it a few times and memorize it. Process virtual memory can be split into working set and non-working set (the part paged out). TM Mem Usage measures the first part only. Alternatively, virtual memory can also be split into private memory and shared memory. TM VM Size measures the first part only.

catan's problem is due to incomplete measurement. He's probably looking at Mem Usage which hasn't reached its normal workload level yet. The only reliable way is to use perfmon, and look at oracle.exe process's virtual bytes or use Process Explorer to look at virtual size.

I'll be surprised if Oracle decides to allocate less than sga_max_size virtual memory for oracle.exe on instance startup. On UNIX/Linux, that might be true if you turned off _use_ism; even then I doubt it but we can verify. On Windows, _use_ism is unlikely to have any effect at all.

Yong Huang

谢谢yong huang
感谢你的回复
oracle博大精深
学习oralce会让你去学西相应的很多东西
不仅仅是一个dbms了
在此感谢
我加油！

__________________

“回”帖的“回”字有四种写法：“迴”“囬”“囘”“廻”

有老程序员，一日得成仙。
学富足五车，概只十字言。
叮嘱众子弟，代代要相传。
“硬件须接地，软件要存盘”。

只看该作者