Re: Re: question about large pool (now NetApp)
thanks for your excellent responses. I guess striping doesnt help with netapps. I was finding alot of waits for redo logs, full table scans, and index reads. However, I guess I have to live with it with the netapps. I believe we have a 1GB pipe. Does it matter if I put my indexes in seperate datafiles from my tables with a netapp configuration? From: Binley Lim [EMAIL PROTECTED] Date: 2003/06/04 Wed AM 07:49:39 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: question about large pool (now NetApp) For NetApp, the key thing is the number, and speed (100Mbit or 1Gbit?), of network I/O cards connecting to the NFS server. Mount points are irrelevant as they could all be going over the same I/O channel. There is a NetApp performance paper that recommends a number of things to tweak for performance, including using multiple IO slaves/DBWRs, rather than asynch_io. Check with your vendor. However, it comes with a disclaimer - do your own tests as it may not apply. I ended up using asynch_io as there was a 10-15% improvement over multiple slaves. In the scheme of things, this is a minor issue. Things that you normally spend a lot of time on like striping and distributing IO are no longer meaningful. After all, you bought a storage server to take care of such things for you. What will kill you are the things you never have to worry about in a DAS configuration. Like: - CPU utilisation will increase significantly, used for shuffling blocks over the network. - test your NFS mount options, especially rsize and wsize. - adjust your OS kernel parameters, including NFS parameters - make sure your OS and especially NFS, patches are up to date - tweak the network interface (ndd command) Some other things that cannot be changed in a hurry: - mount as UDP rather than TCP if you have a dedicated segment for NFS traffic. Trying to share the company-wide network for this is a particularly bad idea. Chances are you will back it out in a hurry. - a later version of OS is much better than an earlier version, eg Solaris 2.8/9 over 2.6. Apparently significant improvements have been made in the TCP/NFS components of the OS. - if you have later version of OS, look at configuring for Ethernet Jumbo Frames. - if the hardware/software is capable, and you have more than 1 network card, look at IP trunking. - if you have older hardware in the DB server, you will run into limits like Sun's SBUS max IO of ~40MB/s. If your requirements are below this, great. Sounds like sysadm type of issues? Yes, it does. If your sysadm (or boss) is good enough to take care of all of these for you, great. In practise, I find that seldom happens. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, June 04, 2003 1:01 AM you only want DBWR_IO_SLAVES or multiple DBWRn if you have datafiles spread over multiple I/O points correct? We are using 'Network Appliance' hard disk array that Im not all that familiar with. It looks like we have 3 I/O points and 5 mount points. my boss told me that striping data files and redo log files across the I/O points wotn help because there is only 1-2 I/O cards(forget the exact, I hope it isnt hard for anyone to figure out what Im referring to) on the server itself. This does not sound accurate. Since Ive read several books and all say to stripe the files? btw, thanks for the info on the large pool. I can free up about 300MB of memory we aer wasting on that and the java pool for other areas. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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: [EMAIL PROTECTED] 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: Re: question about large pool
you only want DBWR_IO_SLAVES or multiple DBWRn if you have datafiles spread over multiple I/O points correct? We are using 'Network Appliance' hard disk array that Im not all that familiar with. It looks like we have 3 I/O points and 5 mount points. my boss told me that striping data files and redo log files across the I/O points wotn help because there is only 1-2 I/O cards(forget the exact, I hope it isnt hard for anyone to figure out what Im referring to) on the server itself. This does not sound accurate. Since Ive read several books and all say to stripe the files? btw, thanks for the info on the large pool. I can free up about 300MB of memory we aer wasting on that and the java pool for other areas. From: Mladen Gogala [EMAIL PROTECTED] Date: 2003/06/03 Tue AM 07:39:41 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: question about large pool Well, I was talking about cursors, sort areas and hash areas. I probably did confuse GA stuff. On 2003.06.02 22:27 Tim Gorman wrote: Almost. It is the UGA areas (not the PGA) for Shared Server (a.k.a. multi-threaded server) that are re-located to the Large Pool, if it exists. Otherwise, they reside in the Shared Pool and all hell breaks loose, performance wise... Also, if DBWR_IO_SLAVES 0, then those IPC queues will reside in the Large Pool, if it is configured. Otherwise, these reside in the Shared Pool, and if you think having UGAs from Shared Server in the Shared Pool play hell with performance, then wait until you are pushing all of your I/O to the datafiles through the Shared Pool... :-) on 6/2/03 2:24 PM, Gogala, Mladen at [EMAIL PROTECTED] wrote: Nope, it's not accurate. PGA areas for shared server sessions are also allocated from the large pool. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 4:35 PM To: Multiple recipients of list ORACLE-L for some reason we have 100MB large pool. I dont think we need it at all. I read that its only used by RMAN or Parallel server. Is that accurate? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/06/02 Mon PM 03:39:42 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: question about large pool Use the large pool to store what? I can think of 3 aspects of a transaction: - Rollback (you've probably read about SET TRANSACTION) - SQL statements, execution plans (more an issue with bind variables) - Data blocks It sounds like you might be thinking of data blocks. You didn't mention your Oracle version, but from 8i on you can define 3 buffer pools. The normal one is DEFAULT. You can also define a KEEP and RECYCLE pool. Someone on this list (sorry I can't recall who) pointed out that there isn't anything magic about those labels. If your transaction uses different tables from the other transactions, you could create what is needed for those tables in one of those pools, assign the tables to that pool, and this would minimize the interference. If all the transactions hit pretty much the same tables, then Oracle is probably reusing the blocks anyway. Hope this responds to your question. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 1:40 PM To: Multiple recipients of list ORACLE-L I think I read this somewhere, but I cant find it. Is it possible to use the large pool for a specific transaction? We run alot of large batch DML statements over night. We have one that involves an 8GB table. The blocks from this table are being knocked out of the buffer cache by shorter and quicker batches. Id like to find to store this transaction in memory without having to worry about them getting knocked out of memory. Cache wont do it. It will stick get pushed out. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San
Re: Re: question about large pool
Not sure what you mean by I/O points. Whether or not multiple DB writer will be of benefit to you depends on a couple things. First of all, can you use async IO? I don't see any mention of what platform you are on. If you're using Windoze, it is async IO by default. Async IO is generally a better choice, and there is then no need for multiple DB writers. If aync is not used, then can use multiple DB writers. The number of IO cards is not directly related to whether multiple DB writers should be used, but rather, what is the bandwidth of the IO system. If you have 5 filesystems, each with a max throughput of 100 IO's per second, and your IO card has a throughput of 500 IO's per second, your system will underperform during periods of heavy activity if only 1 DB writer is available. I seem to recall Oracle docs saying that up to 2 DB writers per filesystem as a max value. This of course depends on your CPU and memory constraints as well. If I had my 'Oracle Tuning 101' from Gaja and Kirti in front of me, I would just quote from it, but it is unfortunately at work, and I'm not. Just do a little homework, say 'yes boss', and do what's best. And of course, just use async if possible, it's a lot easier. HTH Jared On Tuesday 03 June 2003 06:01, [EMAIL PROTECTED] wrote: you only want DBWR_IO_SLAVES or multiple DBWRn if you have datafiles spread over multiple I/O points correct? We are using 'Network Appliance' hard disk array that Im not all that familiar with. It looks like we have 3 I/O points and 5 mount points. my boss told me that striping data files and redo log files across the I/O points wotn help because there is only 1-2 I/O cards(forget the exact, I hope it isnt hard for anyone to figure out what Im referring to) on the server itself. This does not sound accurate. Since Ive read several books and all say to stripe the files? btw, thanks for the info on the large pool. I can free up about 300MB of memory we aer wasting on that and the java pool for other areas. From: Mladen Gogala [EMAIL PROTECTED] Date: 2003/06/03 Tue AM 07:39:41 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: question about large pool Well, I was talking about cursors, sort areas and hash areas. I probably did confuse GA stuff. On 2003.06.02 22:27 Tim Gorman wrote: Almost. It is the UGA areas (not the PGA) for Shared Server (a.k.a. multi-threaded server) that are re-located to the Large Pool, if it exists. Otherwise, they reside in the Shared Pool and all hell breaks loose, performance wise... Also, if DBWR_IO_SLAVES 0, then those IPC queues will reside in the Large Pool, if it is configured. Otherwise, these reside in the Shared Pool, and if you think having UGAs from Shared Server in the Shared Pool play hell with performance, then wait until you are pushing all of your I/O to the datafiles through the Shared Pool... :-) on 6/2/03 2:24 PM, Gogala, Mladen at [EMAIL PROTECTED] wrote: Nope, it's not accurate. PGA areas for shared server sessions are also allocated from the large pool. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 4:35 PM To: Multiple recipients of list ORACLE-L for some reason we have 100MB large pool. I dont think we need it at all. I read that its only used by RMAN or Parallel server. Is that accurate? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/06/02 Mon PM 03:39:42 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: question about large pool Use the large pool to store what? I can think of 3 aspects of a transaction: - Rollback (you've probably read about SET TRANSACTION) - SQL statements, execution plans (more an issue with bind variables) - Data blocks It sounds like you might be thinking of data blocks. You didn't mention your Oracle version, but from 8i on you can define 3 buffer pools. The normal one is DEFAULT. You can also define a KEEP and RECYCLE pool. Someone on this list (sorry I can't recall who) pointed out that there isn't anything magic about those labels. If your transaction uses different tables from the other -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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
RE: Re: question about large pool
My experience with NetApp says that it is easy to clog up your bandwidth to the device. I would worry more about that issue and finding just how much throughput you could get to the device. Usually the DBWR tuning is for the situation where you have lots of devices with separate paths to those devices. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, June 03, 2003 8:02 AM To: Multiple recipients of list ORACLE-L you only want DBWR_IO_SLAVES or multiple DBWRn if you have datafiles spread over multiple I/O points correct? We are using 'Network Appliance' hard disk array that Im not all that familiar with. It looks like we have 3 I/O points and 5 mount points. my boss told me that striping data files and redo log files across the I/O points wotn help because there is only 1-2 I/O cards(forget the exact, I hope it isnt hard for anyone to figure out what Im referring to) on the server itself. This does not sound accurate. Since Ive read several books and all say to stripe the files? btw, thanks for the info on the large pool. I can free up about 300MB of memory we aer wasting on that and the java pool for other areas. From: Mladen Gogala [EMAIL PROTECTED] Date: 2003/06/03 Tue AM 07:39:41 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: question about large pool Well, I was talking about cursors, sort areas and hash areas. I probably did confuse GA stuff. On 2003.06.02 22:27 Tim Gorman wrote: Almost. It is the UGA areas (not the PGA) for Shared Server (a.k.a. multi-threaded server) that are re-located to the Large Pool, if it exists. Otherwise, they reside in the Shared Pool and all hell breaks loose, performance wise... Also, if DBWR_IO_SLAVES 0, then those IPC queues will reside in the Large Pool, if it is configured. Otherwise, these reside in the Shared Pool, and if you think having UGAs from Shared Server in the Shared Pool play hell with performance, then wait until you are pushing all of your I/O to the datafiles through the Shared Pool... :-) on 6/2/03 2:24 PM, Gogala, Mladen at [EMAIL PROTECTED] wrote: Nope, it's not accurate. PGA areas for shared server sessions are also allocated from the large pool. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 4:35 PM To: Multiple recipients of list ORACLE-L for some reason we have 100MB large pool. I dont think we need it at all. I read that its only used by RMAN or Parallel server. Is that accurate? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/06/02 Mon PM 03:39:42 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: question about large pool Use the large pool to store what? I can think of 3 aspects of a transaction: - Rollback (you've probably read about SET TRANSACTION) - SQL statements, execution plans (more an issue with bind variables) - Data blocks It sounds like you might be thinking of data blocks. You didn't mention your Oracle version, but from 8i on you can define 3 buffer pools. The normal one is DEFAULT. You can also define a KEEP and RECYCLE pool. Someone on this list (sorry I can't recall who) pointed out that there isn't anything magic about those labels. If your transaction uses different tables from the other transactions, you could create what is needed for those tables in one of those pools, assign the tables to that pool, and this would minimize the interference. If all the transactions hit pretty much the same tables, then Oracle is probably reusing the blocks anyway. Hope this responds to your question. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 1:40 PM To: Multiple recipients of list ORACLE-L I think I read this somewhere, but I cant find it. Is it possible to use the large pool for a specific transaction? We run alot of large batch DML statements over night. We have one that involves an 8GB table. The blocks from this table are being knocked out of the buffer cache by shorter and quicker batches. Id like to find to store this transaction in memory without having to worry about them getting knocked out of memory. Cache wont do it. It will stick get pushed out. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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:
RE: Re: question about large pool
I would think that the situation where multiple DB writers would be useful on a single mount point would become more and more common. This is due to SAME. If you were to create 5 RAID 10's with 5 physical disks each, and striped a single volume across them, and you are not using async IO, you could definitely use multiple DB writers. The question of how many would depend on your environment. ( the usual disclaimer :) Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/03/2003 08:49 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Re: question about large pool My experience with NetApp says that it is easy to clog up your bandwidth to the device. I would worry more about that issue and finding just how much throughput you could get to the device. Usually the DBWR tuning is for the situation where you have lots of devices with separate paths to those devices. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, June 03, 2003 8:02 AM To: Multiple recipients of list ORACLE-L you only want DBWR_IO_SLAVES or multiple DBWRn if you have datafiles spread over multiple I/O points correct? We are using 'Network Appliance' hard disk array that Im not all that familiar with. It looks like we have 3 I/O points and 5 mount points. my boss told me that striping data files and redo log files across the I/O points wotn help because there is only 1-2 I/O cards(forget the exact, I hope it isnt hard for anyone to figure out what Im referring to) on the server itself. This does not sound accurate. Since Ive read several books and all say to stripe the files? btw, thanks for the info on the large pool. I can free up about 300MB of memory we aer wasting on that and the java pool for other areas. From: Mladen Gogala [EMAIL PROTECTED] Date: 2003/06/03 Tue AM 07:39:41 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: question about large pool Well, I was talking about cursors, sort areas and hash areas. I probably did confuse GA stuff. On 2003.06.02 22:27 Tim Gorman wrote: Almost. It is the UGA areas (not the PGA) for Shared Server (a.k.a. multi-threaded server) that are re-located to the Large Pool, if it exists. Otherwise, they reside in the Shared Pool and all hell breaks loose, performance wise... Also, if DBWR_IO_SLAVES 0, then those IPC queues will reside in the Large Pool, if it is configured. Otherwise, these reside in the Shared Pool, and if you think having UGAs from Shared Server in the Shared Pool play hell with performance, then wait until you are pushing all of your I/O to the datafiles through the Shared Pool... :-) on 6/2/03 2:24 PM, Gogala, Mladen at [EMAIL PROTECTED] wrote: Nope, it's not accurate. PGA areas for shared server sessions are also allocated from the large pool. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 4:35 PM To: Multiple recipients of list ORACLE-L for some reason we have 100MB large pool. I dont think we need it at all. I read that its only used by RMAN or Parallel server. Is that accurate? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/06/02 Mon PM 03:39:42 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: question about large pool Use the large pool to store what? I can think of 3 aspects of a transaction: - Rollback (you've probably read about SET TRANSACTION) - SQL statements, execution plans (more an issue with bind variables) - Data blocks It sounds like you might be thinking of data blocks. You didn't mention your Oracle version, but from 8i on you can define 3 buffer pools. The normal one is DEFAULT. You can also define a KEEP and RECYCLE pool. Someone on this list (sorry I can't recall who) pointed out that there isn't anything magic about those labels. If your transaction uses different tables from the other transactions, you could create what is needed for those tables in one of those pools, assign the tables to that pool, and this would minimize the interference. If all the transactions hit pretty much the same tables, then Oracle is probably reusing the blocks anyway. Hope this responds to your question. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 1:40 PM To: Multiple recipients of list ORACLE-L I think I read this somewhere, but I cant find it. Is it possible to use the large pool for a specific transaction? We run alot of large batch DML statements over night. We have one that involves an 8GB table. The blocks
RE: Re: question about large pool
Jared - You are probably right. I was just making the point that all your disk interaction is going over a network link which can be a lot more restrictive than the normal direct attached storage we are used to. When I see people configuring multiple DB writers I tend to think it will just help them fill that network link to capacity that much quicker. My way of working around the problem was to move hot files to direct attached storage. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, June 03, 2003 11:56 AM To: Multiple recipients of list ORACLE-L I would think that the situation where multiple DB writers would be useful on a single mount point would become more and more common. This is due to SAME. If you were to create 5 RAID 10's with 5 physical disks each, and striped a single volume across them, and you are not using async IO, you could definitely use multiple DB writers. The question of how many would depend on your environment. ( the usual disclaimer :) Jared DENNIS WILLIAMS [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/03/2003 08:49 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Re: question about large pool My experience with NetApp says that it is easy to clog up your bandwidth to the device. I would worry more about that issue and finding just how much throughput you could get to the device. Usually the DBWR tuning is for the situation where you have lots of devices with separate paths to those devices. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, June 03, 2003 8:02 AM To: Multiple recipients of list ORACLE-L you only want DBWR_IO_SLAVES or multiple DBWRn if you have datafiles spread over multiple I/O points correct? We are using 'Network Appliance' hard disk array that Im not all that familiar with. It looks like we have 3 I/O points and 5 mount points. my boss told me that striping data files and redo log files across the I/O points wotn help because there is only 1-2 I/O cards(forget the exact, I hope it isnt hard for anyone to figure out what Im referring to) on the server itself. This does not sound accurate. Since Ive read several books and all say to stripe the files? btw, thanks for the info on the large pool. I can free up about 300MB of memory we aer wasting on that and the java pool for other areas. From: Mladen Gogala [EMAIL PROTECTED] Date: 2003/06/03 Tue AM 07:39:41 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: question about large pool Well, I was talking about cursors, sort areas and hash areas. I probably did confuse GA stuff. On 2003.06.02 22:27 Tim Gorman wrote: Almost. It is the UGA areas (not the PGA) for Shared Server (a.k.a. multi-threaded server) that are re-located to the Large Pool, if it exists. Otherwise, they reside in the Shared Pool and all hell breaks loose, performance wise... Also, if DBWR_IO_SLAVES 0, then those IPC queues will reside in the Large Pool, if it is configured. Otherwise, these reside in the Shared Pool, and if you think having UGAs from Shared Server in the Shared Pool play hell with performance, then wait until you are pushing all of your I/O to the datafiles through the Shared Pool... :-) on 6/2/03 2:24 PM, Gogala, Mladen at [EMAIL PROTECTED] wrote: Nope, it's not accurate. PGA areas for shared server sessions are also allocated from the large pool. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 4:35 PM To: Multiple recipients of list ORACLE-L for some reason we have 100MB large pool. I dont think we need it at all. I read that its only used by RMAN or Parallel server. Is that accurate? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/06/02 Mon PM 03:39:42 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: question about large pool Use the large pool to store what? I can think of 3 aspects of a transaction: - Rollback (you've probably read about SET TRANSACTION) - SQL statements, execution plans (more an issue with bind variables) - Data blocks It sounds like you might be thinking of data blocks. You didn't mention your Oracle version, but from 8i on you can define 3 buffer pools. The normal one is DEFAULT. You can also define a KEEP and RECYCLE pool. Someone on this list (sorry I can't recall who) pointed out that there isn't anything magic about those labels. If your transaction uses different tables from the other transactions, you could create what is needed for those tables in one of those pools, assign the tables to that pool, and this would minimize
Re: RE: question about large pool
for some reason we have 100MB large pool. I dont think we need it at all. I read that its only used by RMAN or Parallel server. Is that accurate? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/06/02 Mon PM 03:39:42 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: question about large pool Use the large pool to store what? I can think of 3 aspects of a transaction: - Rollback (you've probably read about SET TRANSACTION) - SQL statements, execution plans (more an issue with bind variables) - Data blocks It sounds like you might be thinking of data blocks. You didn't mention your Oracle version, but from 8i on you can define 3 buffer pools. The normal one is DEFAULT. You can also define a KEEP and RECYCLE pool. Someone on this list (sorry I can't recall who) pointed out that there isn't anything magic about those labels. If your transaction uses different tables from the other transactions, you could create what is needed for those tables in one of those pools, assign the tables to that pool, and this would minimize the interference. If all the transactions hit pretty much the same tables, then Oracle is probably reusing the blocks anyway. Hope this responds to your question. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 1:40 PM To: Multiple recipients of list ORACLE-L I think I read this somewhere, but I cant find it. Is it possible to use the large pool for a specific transaction? We run alot of large batch DML statements over night. We have one that involves an 8GB table. The blocks from this table are being knocked out of the buffer cache by shorter and quicker batches. Id like to find to store this transaction in memory without having to worry about them getting knocked out of memory. Cache wont do it. It will stick get pushed out. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: DENNIS WILLIAMS 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: [EMAIL PROTECTED] 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: RE: question about large pool
That is pretty much my understanding. Here is a Web page that explains it pretty well: http://www.interealm.com/roby/technotes/tnlargepool.html Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 3:35 PM To: Multiple recipients of list ORACLE-L for some reason we have 100MB large pool. I dont think we need it at all. I read that its only used by RMAN or Parallel server. Is that accurate? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/06/02 Mon PM 03:39:42 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: question about large pool Use the large pool to store what? I can think of 3 aspects of a transaction: - Rollback (you've probably read about SET TRANSACTION) - SQL statements, execution plans (more an issue with bind variables) - Data blocks It sounds like you might be thinking of data blocks. You didn't mention your Oracle version, but from 8i on you can define 3 buffer pools. The normal one is DEFAULT. You can also define a KEEP and RECYCLE pool. Someone on this list (sorry I can't recall who) pointed out that there isn't anything magic about those labels. If your transaction uses different tables from the other transactions, you could create what is needed for those tables in one of those pools, assign the tables to that pool, and this would minimize the interference. If all the transactions hit pretty much the same tables, then Oracle is probably reusing the blocks anyway. Hope this responds to your question. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 1:40 PM To: Multiple recipients of list ORACLE-L I think I read this somewhere, but I cant find it. Is it possible to use the large pool for a specific transaction? We run alot of large batch DML statements over night. We have one that involves an 8GB table. The blocks from this table are being knocked out of the buffer cache by shorter and quicker batches. Id like to find to store this transaction in memory without having to worry about them getting knocked out of memory. Cache wont do it. It will stick get pushed out. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: DENNIS WILLIAMS 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: [EMAIL PROTECTED] 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: DENNIS WILLIAMS 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: RE: question about large pool
From the doc: LARGE_POOL_SIZE lets you specify the size (in bytes) of the large pool allocation heap. The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers. (Parallel execution allocates buffers out of the large pool only when PARALLEL_AUTOMATIC_TUNING is set to true.) Waleed -Original Message- Sent: Monday, June 02, 2003 4:35 PM To: Multiple recipients of list ORACLE-L for some reason we have 100MB large pool. I dont think we need it at all. I read that its only used by RMAN or Parallel server. Is that accurate? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/06/02 Mon PM 03:39:42 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: question about large pool Use the large pool to store what? I can think of 3 aspects of a transaction: - Rollback (you've probably read about SET TRANSACTION) - SQL statements, execution plans (more an issue with bind variables) - Data blocks It sounds like you might be thinking of data blocks. You didn't mention your Oracle version, but from 8i on you can define 3 buffer pools. The normal one is DEFAULT. You can also define a KEEP and RECYCLE pool. Someone on this list (sorry I can't recall who) pointed out that there isn't anything magic about those labels. If your transaction uses different tables from the other transactions, you could create what is needed for those tables in one of those pools, assign the tables to that pool, and this would minimize the interference. If all the transactions hit pretty much the same tables, then Oracle is probably reusing the blocks anyway. Hope this responds to your question. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 1:40 PM To: Multiple recipients of list ORACLE-L I think I read this somewhere, but I cant find it. Is it possible to use the large pool for a specific transaction? We run alot of large batch DML statements over night. We have one that involves an 8GB table. The blocks from this table are being knocked out of the buffer cache by shorter and quicker batches. Id like to find to store this transaction in memory without having to worry about them getting knocked out of memory. Cache wont do it. It will stick get pushed out. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: DENNIS WILLIAMS 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: [EMAIL PROTECTED] 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: Khedr, Waleed 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
RE: RE: question about large pool
Nope, it's not accurate. PGA areas for shared server sessions are also allocated from the large pool. Mladen Gogala Oracle DBA Phone:(203) 459-6855 Email:[EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 4:35 PM To: Multiple recipients of list ORACLE-L for some reason we have 100MB large pool. I dont think we need it at all. I read that its only used by RMAN or Parallel server. Is that accurate? From: DENNIS WILLIAMS [EMAIL PROTECTED] Date: 2003/06/02 Mon PM 03:39:42 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: question about large pool Use the large pool to store what? I can think of 3 aspects of a transaction: - Rollback (you've probably read about SET TRANSACTION) - SQL statements, execution plans (more an issue with bind variables) - Data blocks It sounds like you might be thinking of data blocks. You didn't mention your Oracle version, but from 8i on you can define 3 buffer pools. The normal one is DEFAULT. You can also define a KEEP and RECYCLE pool. Someone on this list (sorry I can't recall who) pointed out that there isn't anything magic about those labels. If your transaction uses different tables from the other transactions, you could create what is needed for those tables in one of those pools, assign the tables to that pool, and this would minimize the interference. If all the transactions hit pretty much the same tables, then Oracle is probably reusing the blocks anyway. Hope this responds to your question. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 1:40 PM To: Multiple recipients of list ORACLE-L I think I read this somewhere, but I cant find it. Is it possible to use the large pool for a specific transaction? We run alot of large batch DML statements over night. We have one that involves an 8GB table. The blocks from this table are being knocked out of the buffer cache by shorter and quicker batches. Id like to find to store this transaction in memory without having to worry about them getting knocked out of memory. Cache wont do it. It will stick get pushed out. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: DENNIS WILLIAMS 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: [EMAIL PROTECTED] 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: Gogala, Mladen 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).
