Re: Block size : what is the gain ?
Hi Stephane, If you're using a conventional file system on AIX, you can expect a reduction in performance by moving to 16K from your already imperfect 8K. See Steve Adam's notes why the DB block size should = the file system buffer size (www.ixora.com). And that's *4K* on AIX. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 22, 2003 4:59 AM Hi, All our DB have an 8k block size (8172/aix). Even the reporting/dss database where data is accessed mainly by full scan. Can we quantify the gain in % of switching from an 8k to 16k block size from a performance point of view ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Block size : what is the gain ?
Hi, All our DB have an 8k block size (8172/aix). Even the reporting/dss database where data is accessed mainly by full scan. Can we quantify the gain in % of switching from an 8k to 16k block size from a performance point of view ? Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Size, what is it?
One of the guys that used to work for me in Premium Services, Martin Berg, once had this idea that you could measure the number of logical IO's (LIO's) per CPU per hour on a system. Scanning V$SQL and measuring the % of cpu time being used at the same time could give you an idea. At least this will provide a unit of work that can be related to an Oracle database, although the idea has more buts and ifs than Denmark has rainy days. Whether you're doing OLTP or batch jobs, your unit would still be LIO's... Steve Sapovits wrote: Sort of related, you might be interested in http://www.tpc.org Steve Sapovits Global Sports Interactive Work Email: [EMAIL PROTECTED] Home Email: [EMAIL PROTECTED] Work Phone: 610-491-7087 Cell: 610-574-7706 Pager: 877-239-4003 -Original Message- From: Dave Morgan [SMTP:[EMAIL PROTECTED]] Sent: Monday, June 04, 2001 1:41 PM To: Multiple recipients of list ORACLE-L Subject: Re: Size, what is it? Hi Mogens I agree with all your statements. What I am trying to figure out is what is it that streches the machine. I was quite surprised to see an E450 doing 10GB of transaction logs per day. Pure OLTP using stored procs. I was hoping to get descriptions of the types and amounts of work a large or busy database does along with the description of the hardware that is being used. This would allow a baseline to be developed for estimating. For example how much OLTP work can a Linux 2 CPU machine with lots of memory do? How many DSS users can a similar machine support? I would also like to ask similar questions about other UNIX configurations? VAX/VMS would also be interesting. NT, someone else can do the work if they want :) SUN is also now offering hardware RAID 3 in their RSM2000 array. As I mentioned the Baydel array beats RAID 5 easily, and is substantially cheaper than an equivalent RAID 10 (1+0) array which is my preference. (and everyone elses :) Thanks for your input. Dave Mogens wrote ... My dear friend Cary Millsap once came up with a definition for a VLDB: It's any database that stretches its hardware. I cannot see any relationship between SGA and database sizes. None. RAID-3: Bit-level striping. Incredible it still exists (in my opinion) :). -- Dave Morgan DBA, Cybersurf Office: 403 777 2000 ext 284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Sapovits INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Venlig hilsen Mogens Nørgaard Technical Director Miracle A/S, Denmark Web: http://MiracleAS.dk Mobile: +45 2527 7100 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?iso-8859-1?Q?N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Size, what is it?
Hi Mogens I agree with all your statements. What I am trying to figure out is what is it that streches the machine. I was quite surprised to see an E450 doing 10GB of transaction logs per day. Pure OLTP using stored procs. I was hoping to get descriptions of the types and amounts of work a large or busy database does along with the description of the hardware that is being used. This would allow a baseline to be developed for estimating. For example how much OLTP work can a Linux 2 CPU machine with lots of memory do? How many DSS users can a similar machine support? I would also like to ask similar questions about other UNIX configurations? VAX/VMS would also be interesting. NT, someone else can do the work if they want :) SUN is also now offering hardware RAID 3 in their RSM2000 array. As I mentioned the Baydel array beats RAID 5 easily, and is substantially cheaper than an equivalent RAID 10 (1+0) array which is my preference. (and everyone elses :) Thanks for your input. Dave Mogens wrote ... My dear friend Cary Millsap once came up with a definition for a VLDB: It's any database that stretches its hardware. I cannot see any relationship between SGA and database sizes. None. RAID-3: Bit-level striping. Incredible it still exists (in my opinion) :). -- Dave Morgan DBA, Cybersurf Office: 403 777 2000 ext 284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Size, what is it?
I have 160Gb database size doing great with 150Mb of SGA, You are saying 700Mb SGA to support 150Gb database size?? Is there any white paper available for any criteria??? Let me know If you got any stuff. Thanks Raghu. From: Dave Morgan [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Size, what is it? Date: Fri, 01 Jun 2001 09:41:11 -0800 Hi All, Being able to lift my head from the problems at my new job finally. I would like to get some definitions of what is a large database and the hardware that it is running on. There are multiple different types of large, transactions, physical size, number of objects, number of connections, SGA size, etc. My current database is a heavy transaction site but small in other respects. Oracle 8.1.7, 150GB physical size, 700MB SGA, 10 GB per day redo, 300-700 OLTP connections at any time all on a SUN E450, 2 CPU's, 4 GB RAM, Baydel disk array (RAID 3). The machine is relativiely idle, or I/O bound, memory starts becomming a limit above 700 connections. A previous large database was Oracle 8.0.5, 50 GB physical, associated with 4 TB of image files on the filesystem, 350 GB SGA, 2 GB per day redo, 100 DSS connections, all on a SUN E5000, 6 CPU's 6 GB RAM, many hardware RAID arrays, machine was CPU bound almost all the time. Most of the database I have seen have been on machines that are grossly over powered for the work that they do so I am hoping I can determine some figures for hardware vs workload based on real data. Your assistance would be appreciated. Please include as many details as possible including why you think it is a large database and machine utilization. Also, does anyone have experience with Baydel disk arrays. They seem to be a happening setup even if they are hardwired RAID 3 :( TIA Dave -- Dave Morgan DBA, Cybersurf Office: 403 777 2000 ext 284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Morgan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).