From oracle-l-bounce@freelists.org Thu Jul 28 12:04:06 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j6SH42Lc023365 for ; Thu, 28 Jul 2005 12:04:02 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j6SH3uIP023339 for ; Thu, 28 Jul 2005 12:03:56 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C39A41D8C18; Thu, 28 Jul 2005 12:03:50 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32146-05; Thu, 28 Jul 2005 12:03:50 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4168D1D7C8D; Thu, 28 Jul 2005 12:03:50 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:from:to:subject:date:mime-version:content-type:x-mailer:in-reply-to:thread-index:x-mimeole:message-id; b=ckzQvjHqG7eXzVKNU2Xig00EwqxSEHjXCaMo8FxktkB6A2lTrJ44mCoCiASbDIubGcsvHJb4MAkRRssaV8WFiRgBgY2VY7IHuoSmhT+wo4iqyz2ITCSgo8hUFFTDiqt8xb09WgGHZy76cWNl5s/454ddeaVgHS3VHc85NQNrD8I= From: "Ken Naim" To: , , Subject: RE: Large PGA for a data warehouse on Windows 2003 advancedserver(32 bit) Date: Thu, 28 Jul 2005 12:01:59 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0111_01C5936C.2976F4E0" In-Reply-To: Thread-Index: AcWTk43lXYxzGD7bSJ62ViUSOFxPfQAANZtw X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Message-ID: <42e90f87.697f2337.148d.7479@mx.gmail.com> X-archive-position: 23003 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: kennaim@gmail.com Precedence: normal Reply-To: kennaim@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.4 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE autolearn=ham version=2.63 ------=_NextPart_000_0111_01C5936C.2976F4E0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Yes, that is the exact problem. 4096mb usable ram -2048mb windows -1024mb default windows setting for indirect_db_block_buffer - 512mb sga ------ 512mb/ 20 (to 30) concurrent users = 15-25mb/user. 28gb of db block buffers just sitting there waiting to be processed. Doesn't leave a lot of space for sorting and hash joins of gig's worth of data. Even reducing the windows setting to 512mb doesn't add much (30-50mb/user) especially considering how ram is essentially idle. Ken. _____ From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Jeffrey Beckstrom Sent: Thursday, July 28, 2005 11:42 AM To: oracle-l@freelists.org; kennaim@gmail.com; Brandon.Allen@OneNeck.com Subject: RE: Large PGA for a data warehouse on Windows 2003 advancedserver(32 bit) Don't forget to account for at least 500M for the buffer window used to map the indirect buffers. >>> "Allen, Brandon" 7/28/05 12:27:11 PM >>> Ken, See Metalink Note 1036312.6 for info on how to use up to 3GB (set /3GB switch in your boot.ini) or see note 46001.1 for info on how to use more than 3GB (for buffer cache only - you have to set USE_INDIRECT_DATA_BUFFERS=true, and then use the old, static db_block_buffers instead of new, dynamic db_cache_size - and can't use db_cache_advice either). So, theoretically, I believe you could use 1GB for Windows, 3GB for your PGA/SGA and then use the remaining 28GB for your buffer cache. I don't think there is anyway to allocate 4-6gb for PGA on Windows, but maybe someone else will have an idea. Have you opened a TAR? I've used the 3GB switch a few times with no problems, but have never used the second method for accessing > 3GB. If you try it, please let me know how it goes. Thanks, Brandon -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Ken Naim Sent: Thursday, July 28, 2005 9:15 AM To: oracle-l@freelists.org Subject: Large PGA for a data warehouse on Windows 2003 advanced server(32 bit) Dear List, I am developing a data warehouse using Oracle 10g R1 on Windows 2003 advanced server(32 bit). The server has 32gb of ram and ideally I'd like to allocate a good portion of it to the pga and not just db block buffers. As I understand it windows "sees" 4gb of ram, 2gb of which is for windows, 1gb which is used for db block buffers which can map to higher regions of memory for larger amounts of db block buffers, leaving only 1gb of memory for the sga and pga. I'd like to know if there is a way to use more of the ram for the pga, in the neighborhood of 4-6gb. My background is on the unix side, so please keep the switch to linux comments to a minimum. I do not have a choice in this matter. Thanks, Ken Naim Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it. -- http://www.freelists.org/webpage/oracle-l ------=_NextPart_000_0111_01C5936C.2976F4E0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Yes, that is the=20 exact problem.
 
 4096mb usable=20 ram
-2048mb=20 windows
-1024mb = default windows=20 setting for indirect_db_block_buffer
- 512mb = sga
------
512mb/ 20 = (to 30)=20 concurrent users =3D 15-25mb/user.
 
28gb of db = block buffers=20 just sitting there waiting to be processed.
 
Doesn't = leave a lot of=20 space for sorting and hash joins of gig's worth of data. Even = reducing the=20 windows setting to 512mb doesn't add much (30-50mb/user) especially = considering=20 how ram is essentially idle.
 
Ken.
 


From: oracle-l-bounce@freelists.org=20 [mailto:oracle-l-bounce@freelists.org] On Behalf Of Jeffrey=20 Beckstrom
Sent: Thursday, July 28, 2005 11:42 AM
To: = oracle-l@freelists.org; kennaim@gmail.com;=20 Brandon.Allen@OneNeck.com
Subject: RE: Large PGA for a data = warehouse=20 on Windows 2003 advancedserver(32 bit)

Don't forget to account for at least 500M for the buffer = window used=20 to map the indirect buffers.

>>> "Allen, Brandon"=20 <Brandon.Allen@OneNeck.com> 7/28/05 12:27:11 PM >>>
Ken,

See Metalink Note 1036312.6 for info on how to use up = to 3GB=20 (set /3GB switch in your boot.ini) or see note 46001.1 for info on how = to use=20 more than 3GB  (for buffer cache only - you have to set=20 USE_INDIRECT_DATA_BUFFERS=3Dtrue, and then use the old, static = db_block_buffers=20 instead of new, dynamic db_cache_size - and can't use db_cache_advice=20 either).  So, theoretically, I believe you could use 1GB for = Windows, 3GB=20 for your PGA/SGA and then use the remaining 28GB for your buffer = cache.  I=20 don't think there is anyway to allocate 4-6gb for PGA on Windows, but = maybe=20 someone else will have an idea.  Have you opened a TAR?

I've = used=20 the 3GB switch a few times with no problems, but have never used the = second=20 method for accessing > 3GB. If you try it, please let me know how it=20 goes.

Thanks,
Brandon

-----Original = Message-----
From:=20 oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@f= reelists.org]On=20 Behalf Of Ken Naim
Sent: Thursday, July 28, 2005 9:15 AM
To:=20 oracle-l@freelists.org
Subject: Large PGA for a data warehouse on = Windows=20 2003 advanced server(32 bit)


Dear List,

I am = developing a data=20 warehouse using Oracle 10g R1 on Windows 2003 advanced server(32 bit). = The=20 server has 32gb of ram and ideally I'd like to allocate a good portion = of it to=20 the pga and not just db block buffers. As I understand it windows "sees" = 4gb of=20 ram, 2gb of which is for windows, 1gb which is used for db block buffers = which=20 can map to higher regions of memory for larger amounts of db block = buffers,=20 leaving only 1gb of memory for the sga and pga. I'd like to know if = there is a=20 way to use more of the ram for the pga, in the neighborhood of 4-6gb. =

My=20 background is on the unix side, so please keep the switch to linux = comments to a=20 minimum. I do not have a choice in this = matter.

Thanks,

Ken=20 Naim

Privileged/Confidential Information may be contained in this = message=20 or attachments hereto. Please advise immediately if you or your employer = do not=20 consent to Internet email for messages of this kind. Opinions, = conclusions and=20 other information in this message that do not relate to the official = business of=20 this company shall be understood as neither given nor endorsed by=20 it.

--
http://www.freelists.o= rg/webpage/oracle-l
------=_NextPart_000_0111_01C5936C.2976F4E0-- -- http://www.freelists.org/webpage/oracle-l