RE: Re: max parallel query
Ryan NetApp is in another class of devices labeled NAS for Network Attached Storage. Because its connection with your server runs over a network connection, the performance is very much dependent on the speed and configuration of the network connection. As has been explained to me, and I very much stand ready to be corrected by others more knowledgeable than myself, there are 3 main classes of storage devices today. They are NAS, Direct-attached, and SAN. My understanding is that NAS tend to be the cheapest and lowest-performance and SAN are the most expensive and highest-performance. But that is just a blanket statement and probably doesn't hold in many specific situations. My personal experience with NetApp is dependent on our configuration and I can't claim that the configuration is perfect. I found the NetApp device to work really well for providing large amounts of storage at a low cost. However, I also discovered that it was really easy to overload the connection. Again, maybe you have a better network connection, I'm just judging by my experience. A standard recommendation for DBAs is to spread I/O among as many devices as possible. I found the performance of our NetApp to be much more acceptable if I could move some high I/O parts of the database to other devices. Redo logs would be a good example of something you might consider putting on any direct-attached disks you have available to you. That would relieve some of the contention over your network connection. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 18, 2003 6:30 PM To: Multiple recipients of list ORACLE-L i dont manage the netapp and am not a hardware person. could you explain a little better? Is netapp similiar to SAN? what is asynch I/O? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:24 PM Ryan You are probably bottlenecking on the NetApp. Probably your network link to it. If you have some regular (a.k.a. direct attached) disk available, consider using it for your redo logs. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 18, 2003 4:45 PM To: Multiple recipients of list ORACLE-L hmmm... when i run statspack during a big load. most of my waits are from redo log waits and read from a staging datafile. we have all of our datafiles on the same I/O mount. We are using a Network Appliance back end with asynch I/O. are you telling me that putting these files on seperate mount points will have no effect? I dont quite follow asynch I/O. I dont have much of a hard ware OS background. I cant find any docs or articles on how to tune Parallel Operations. All I see are basic syntax. Do you know of any? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 5:24 PM If you want rules of thumb, then take CPU_COUNT+1 up to CPU_COUNT *2. I think LIO PIO ratio is irrelevant, the most important is whether you are able to construct optimal parallel execution plan, e.g. avoid excessive parallel slave messaging waiting. This is mostly design and SQL issue. The number mount points is irrelevant novadays as well IMHO, especially when you're using async IO, SANs SAME like architecture. The only thing what comes into my mind where splitting into mount points (thus different file systems) helps performance wise, is that with more file systems you got more file system locks and you can spread contention for these locks that way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 9:34 PM i know there are no magic formulas, but im hoping for something better than trial and error. i would assume that parallel query helps most when: 1. are doing work off of multiple mount points. 2. Have alot more LIOs to perform than PIOs(such as sorts). am i close on this? From: M Rafiq [EMAIL PROTECTED] Date: 2003/09/18 Thu PM 01:34:44 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: max parallel query Tanel, You are right. There is a param parallel_automatic(I don't remember exact name as I don't have access to database) which should be set to true and let system decide how many pq slave to be used. For max_parallel_server default is 5 and can be set to higher number which memory and cpu can handle. It is faster because it uses all system resources at a given time. Rarallel process can be used for large batch jobs during off-peak time. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 18 Sep 2003
RE: Re: max parallel query
The real differences between NAS and SAN is how data is accessed - NAS is file-based (open this file, read that file, lock this other file) while SAN, like direct-attached, is block based (read block 45345 from disk 7, write block 2442 from disk 3). SAN runs over Fibre Channel, which is a network protocol that sits under SCSI, while NAS uses NFS (or CIFS, but for Oracle just NFS) over TCP/IP to talk to the storage. From a pricing standpoint, its generally true that NAS is cheaper than SAN, though I can show you a million-dollar NAS box and a 10k SAN. Ditto with performance - while SAN is often faster than NAS, your mileage can vary wildly. Most of the perceived performance gap between SAN and NAS is due to the fact that people have lower standards for their networks than they do their SANs. I've seen people/organizations who would never ever consider using an off-brand Fibre Channel card cheerfully put their performance-sensitive NAS traffic over a $50 Gigabit ethernet card. Intelligent design and careful tuning (plus sizing your storage properly) for your NAS will yield comparable performance to a SAN. Beyond that, management of NAS vs. SAN is totally different, though I can't get into that in detail here. Finally, the world just changed again with the introduction of iSCSI - SCSI over IP. It's block-based access over traditional IP networks...very exciting stuff. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of DENNIS WILLIAMS Sent: Friday, September 19, 2003 11:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: Re: max parallel query Ryan NetApp is in another class of devices labeled NAS for Network Attached Storage. Because its connection with your server runs over a network connection, the performance is very much dependent on the speed and configuration of the network connection. As has been explained to me, and I very much stand ready to be corrected by others more knowledgeable than myself, there are 3 main classes of storage devices today. They are NAS, Direct-attached, and SAN. My understanding is that NAS tend to be the cheapest and lowest-performance and SAN are the most expensive and highest-performance. But that is just a blanket statement and probably doesn't hold in many specific situations. My personal experience with NetApp is dependent on our configuration and I can't claim that the configuration is perfect. I found the NetApp device to work really well for providing large amounts of storage at a low cost. However, I also discovered that it was really easy to overload the connection. Again, maybe you have a better network connection, I'm just judging by my experience. A standard recommendation for DBAs is to spread I/O among as many devices as possible. I found the performance of our NetApp to be much more acceptable if I could move some high I/O parts of the database to other devices. Redo logs would be a good example of something you might consider putting on any direct-attached disks you have available to you. That would relieve some of the contention over your network connection. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito 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: max parallel query
Matt, Question: What else do you have running on your Fiber Channel? Answer: Nothing Question: What do you have running on your TCP/IP network? Answer: Everything. For this one can see that a SAN's fiber channel is dedicated to handling data from one server to it's storage. Sure you can attach part of your SAN to the network to act as a NAS file system, but the SAN switch handles that separately from the servers so that one does not get in the way of the other. Therefore when some lummox decides to download that 1GB MPG file from the internet, his traffic does not get in the way of your database working with it's files. Divide Conquer still has it's place. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, September 19, 2003 1:35 PM To: Multiple recipients of list ORACLE-L The real differences between NAS and SAN is how data is accessed - NAS is file-based (open this file, read that file, lock this other file) while SAN, like direct-attached, is block based (read block 45345 from disk 7, write block 2442 from disk 3). SAN runs over Fibre Channel, which is a network protocol that sits under SCSI, while NAS uses NFS (or CIFS, but for Oracle just NFS) over TCP/IP to talk to the storage. From a pricing standpoint, its generally true that NAS is cheaper than SAN, though I can show you a million-dollar NAS box and a 10k SAN. Ditto with performance - while SAN is often faster than NAS, your mileage can vary wildly. Most of the perceived performance gap between SAN and NAS is due to the fact that people have lower standards for their networks than they do their SANs. I've seen people/organizations who would never ever consider using an off-brand Fibre Channel card cheerfully put their performance-sensitive NAS traffic over a $50 Gigabit ethernet card. Intelligent design and careful tuning (plus sizing your storage properly) for your NAS will yield comparable performance to a SAN. Beyond that, management of NAS vs. SAN is totally different, though I can't get into that in detail here. Finally, the world just changed again with the introduction of iSCSI - SCSI over IP. It's block-based access over traditional IP networks...very exciting stuff. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of DENNIS WILLIAMS Sent: Friday, September 19, 2003 11:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: Re: max parallel query Ryan NetApp is in another class of devices labeled NAS for Network Attached Storage. Because its connection with your server runs over a network connection, the performance is very much dependent on the speed and configuration of the network connection. As has been explained to me, and I very much stand ready to be corrected by others more knowledgeable than myself, there are 3 main classes of storage devices today. They are NAS, Direct-attached, and SAN. My understanding is that NAS tend to be the cheapest and lowest-performance and SAN are the most expensive and highest-performance. But that is just a blanket statement and probably doesn't hold in many specific situations. My personal experience with NetApp is dependent on our configuration and I can't claim that the configuration is perfect. I found the NetApp device to work really well for providing large amounts of storage at a low cost. However, I also discovered that it was really easy to overload the connection. Again, maybe you have a better network connection, I'm just judging by my experience. A standard recommendation for DBAs is to spread I/O among as many devices as possible. I found the performance of our NetApp to be much more acceptable if I could move some high I/O parts of the database to other devices. Redo logs would be a good example of something you might consider putting on any direct-attached disks you have available to you. That would relieve some of the contention over your network connection. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito 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
RE: Re: max parallel query
To counter that... If our network admin had our NAS traffic on the same backbone as our internet/itranet traffic, they would very quickly be looking for employment elsewhere... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] Sent by: To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: .com Subject: RE: Re: max parallel query 09/19/2003 11:49 AM Please respond to ORACLE-L Matt, Question: What else do you have running on your Fiber Channel? Answer: Nothing Question: What do you have running on your TCP/IP network? Answer: Everything. For this one can see that a SAN's fiber channel is dedicated to handling data from one server to it's storage. Sure you can attach part of your SAN to the network to act as a NAS file system, but the SAN switch handles that separately from the servers so that one does not get in the way of the other. Therefore when some lummox decides to download that 1GB MPG file from the internet, his traffic does not get in the way of your database working with it's files. Divide Conquer still has it's place. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, September 19, 2003 1:35 PM To: Multiple recipients of list ORACLE-L The real differences between NAS and SAN is how data is accessed - NAS is file-based (open this file, read that file, lock this other file) while SAN, like direct-attached, is block based (read block 45345 from disk 7, write block 2442 from disk 3). SAN runs over Fibre Channel, which is a network protocol that sits under SCSI, while NAS uses NFS (or CIFS, but for Oracle just NFS) over TCP/IP to talk to the storage. From a pricing standpoint, its generally true that NAS is cheaper than SAN, though I can show you a million-dollar NAS box and a 10k SAN. Ditto with performance - while SAN is often faster than NAS, your mileage can vary wildly. Most of the perceived performance gap between SAN and NAS is due to the fact that people have lower standards for their networks than they do their SANs. I've seen people/organizations who would never ever consider using an off-brand Fibre Channel card cheerfully put their performance-sensitive NAS traffic over a $50 Gigabit ethernet card. Intelligent design and careful tuning (plus sizing your storage properly) for your NAS will yield comparable performance to a SAN. Beyond that, management of NAS vs. SAN is totally different, though I can't get into that in detail here. Finally, the world just changed again with the introduction of iSCSI - SCSI over IP. It's block-based access over traditional IP networks...very exciting stuff. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of DENNIS WILLIAMS Sent: Friday, September 19, 2003 11:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: Re: max parallel query Ryan NetApp is in another class of devices labeled NAS for Network Attached Storage. Because its connection with your
RE: Re: max parallel query
Ron I think you are correct. However, most network administrators might not understand Matthew's suggestion of gigabit + jumbo frames. In fact, I'm not sure every system vendor has gigabit network cards available, and even if they do, the price may be an issue. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, September 19, 2003 2:05 PM To: Multiple recipients of list ORACLE-L To counter that... If our network admin had our NAS traffic on the same backbone as our internet/itranet traffic, they would very quickly be looking for employment elsewhere... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] Sent by: To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: .com Subject: RE: Re: max parallel query 09/19/2003 11:49 AM Please respond to ORACLE-L Matt, Question: What else do you have running on your Fiber Channel? Answer: Nothing Question: What do you have running on your TCP/IP network? Answer: Everything. For this one can see that a SAN's fiber channel is dedicated to handling data from one server to it's storage. Sure you can attach part of your SAN to the network to act as a NAS file system, but the SAN switch handles that separately from the servers so that one does not get in the way of the other. Therefore when some lummox decides to download that 1GB MPG file from the internet, his traffic does not get in the way of your database working with it's files. Divide Conquer still has it's place. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Friday, September 19, 2003 1:35 PM To: Multiple recipients of list ORACLE-L The real differences between NAS and SAN is how data is accessed - NAS is file-based (open this file, read that file, lock this other file) while SAN, like direct-attached, is block based (read block 45345 from disk 7, write block 2442 from disk 3). SAN runs over Fibre Channel, which is a network protocol that sits under SCSI, while NAS uses NFS (or CIFS, but for Oracle just NFS) over TCP/IP to talk to the storage. From a pricing standpoint, its generally true that NAS is cheaper than SAN, though I can show you a million-dollar NAS box and a 10k SAN. Ditto with performance - while SAN is often faster than NAS, your mileage can vary wildly. Most of the perceived performance gap between SAN and NAS is due to the fact that people have lower standards for their networks than they do their SANs. I've seen people/organizations who would never ever consider using an off-brand Fibre Channel card cheerfully put their performance-sensitive NAS traffic over a $50 Gigabit ethernet card. Intelligent design and careful tuning (plus sizing your storage properly) for your NAS will yield comparable performance to a SAN. Beyond that, management of NAS vs. SAN is totally different, though I can't get into that in detail here. Finally, the world just changed again with the introduction of iSCSI - SCSI over IP. It's block-based access over traditional IP networks...very exciting stuff. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of DENNIS WILLIAMS Sent: Friday, September 19, 2003 11:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: Re: max parallel query Ryan NetApp is in another class of devices labeled NAS for Network Attached Storage. Because its connection with your server runs over a network connection, the performance is very much dependent on the speed and configuration of the network connection. As has been explained to me, and I very much stand ready to be corrected by others more knowledgeable than myself, there are 3 main classes of storage devices today. They are NAS, Direct-attached, and SAN. My understanding is that NAS tend to be the cheapest and lowest-performance and SAN are the most expensive and highest-performance. But that is just a blanket statement and probably doesn't hold in many specific situations. My personal experience with NetApp is dependent on our configuration and I can't claim that the configuration is perfect. I found the NetApp device to work really well for providing large amounts of storage at a low cost. However, I also discovered that it was really easy to overload the connection. Again, maybe you have a better network connection, I'm just judging by my experience. A standard recommendation
RE: Re: max parallel query
Well, it is certainly true that the advantage of dedicated infrastructures is that they're guaranteed to be useless for other tasks. :) However, the notion that because TCP/IP is used by many applications it is unsuitable for storage traffic is simply not true. Proper network and infrastructure design in general dictates that traffic is segmented based on business needs. A properly designed network infrastructure for a database generally includes two to four ethernet interfaces on the server. Two of these are dedicated for storage I/O (link aggregation strategies such as 802.3ad can be used if desired) and two are dedicated for host-database connectivity. With that configuration, properly implemented, there is no way that some idiot downloading a huge file will negatively impact the performance of your database. No way whatsoever. As an aside, there's no way to interconnect a Fibre SAN and a standard network without a conversion device - usually something like a NetApp, EMC Celerra, or even a standard UNIX box to handle the conversion from SCSI-over-Fibre to file-based NFS or CIFS. The real reasons to leverage IP networks for storage are as follows: -It's cheaper - Fibre channel today is roughly $800/port, and it isn't getting cheaper at an appreciable rate. Gigabit ports are on the order of $200/port, and that's assuming you're using host interfaces with intelligence built in for extra performance -It's more scalable - I worked on the design of the largest SAN in the world, which was only 1000 hosts, and it was pushing the limits of what is currently functional in a Fibre SAN. Whereas a 1000-host IP network is a commonplace thing to see, and doesn't even count as large -It's more mature - the behavior of IP networks under load and failure scenarios is well-documented. There are hundreds of network engineers that can in-detail describe how segmented networks converge, whereas I've only met a few storage folks who can explain how the equivalent process works in a SAN. -It's more stable - IP networks degrade. Fibre networks collapse. -It has more functionality - there currently is no concept of QoS in Fibre Channel, for example, and that's just the start of what's missing. The case for Fibre is getting less and less compelling all the time. The last holdout was those who are serious about block-based storage access (which is understandable), and iSCSI has effectively filled that gap. Fibre isn't going away anytime soon, but whenever it does, its not soon enough for me. Hrrrmmthe on-topic-ness of this has strayed far from Oracle. My apologies. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Goulet, Dick Sent: Friday, September 19, 2003 1:50 PM To: Multiple recipients of list ORACLE-L Subject: RE: Re: max parallel query Matt, Question: What else do you have running on your Fiber Channel? Answer: Nothing Question: What do you have running on your TCP/IP network? Answer: Everything. For this one can see that a SAN's fiber channel is dedicated to handling data from one server to it's storage. Sure you can attach part of your SAN to the network to act as a NAS file system, but the SAN switch handles that separately from the servers so that one does not get in the way of the other. Therefore when some lummox decides to download that 1GB MPG file from the internet, his traffic does not get in the way of your database working with it's files. Divide Conquer still has it's place. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito 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: max parallel query
The case for Fibre is getting less and less compelling all the time. The last holdout was those who are serious about block-based storage access (which is understandable), and iSCSI has effectively filled that gap. Fibre isn't going away anytime soon, but whenever it does, its not soon enough for me. Hrrrmmthe on-topic-ness of this has strayed far from Oracle. My apologies. It has been a very interesting topic, though. Tanel. Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Goulet, Dick Sent: Friday, September 19, 2003 1:50 PM To: Multiple recipients of list ORACLE-L Subject: RE: Re: max parallel query Matt, Question: What else do you have running on your Fiber Channel? Answer: Nothing Question: What do you have running on your TCP/IP network? Answer: Everything. For this one can see that a SAN's fiber channel is dedicated to handling data from one server to it's storage. Sure you can attach part of your SAN to the network to act as a NAS file system, but the SAN switch handles that separately from the servers so that one does not get in the way of the other. Therefore when some lummox decides to download that 1GB MPG file from the internet, his traffic does not get in the way of your database working with it's files. Divide Conquer still has it's place. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Matthew Zito 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: Tanel Poder 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: max parallel query
Hrrrmmthe on-topic-ness of this has strayed far from Oracle. My apologies. Not sure I agree with the last statement... I think it is on topic... Many of us DBA types are often involved in these kinds of discussions internally and the more informed we are the better off we are... It gives more credibility to our arguments and allows us to make more informed decisions... Thanks for the information! Tim Thanks, Matt -- Matthew Zito GridApp Systems Email: [EMAIL PROTECTED] Cell: 646-220-3551 Phone: 212-358-8211 x 359 http://www.gridapp.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johnston, Tim 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: max parallel query
i know there are no magic formulas, but im hoping for something better than trial and error. i would assume that parallel query helps most when: 1. are doing work off of multiple mount points. 2. Have alot more LIOs to perform than PIOs(such as sorts). am i close on this? From: M Rafiq [EMAIL PROTECTED] Date: 2003/09/18 Thu PM 01:34:44 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: max parallel query Tanel, You are right. There is a param parallel_automatic(I don't remember exact name as I don't have access to database) which should be set to true and let system decide how many pq slave to be used. For max_parallel_server default is 5 and can be set to higher number which memory and cpu can handle. It is faster because it uses all system resources at a given time. Rarallel process can be used for large batch jobs during off-peak time. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 18 Sep 2003 08:39:44 -0800 There is no simple magic formula how many parallel slaves you should have. It all depends on your CPU and IO utilization. Yes, utilization, because if your CPUs or IO are running at their limits already, you won't benefit from parallel execution at all. In fact PX may make the situation even worse, because it's designed to give you the results the fastest way, not the most efficient way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:15 PM We run multiple instances on the same server. My understanding is max_parallel_servers should be set to 4 x CPU. We have 4 CPUs which means 16. however, does this take into account multiple instances on the same server? -- 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: Tanel Poder 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). _ Send and receive larger attachments with Hotmail Extra Storage. http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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: max parallel query
If you want rules of thumb, then take CPU_COUNT+1 up to CPU_COUNT *2. I think LIO PIO ratio is irrelevant, the most important is whether you are able to construct optimal parallel execution plan, e.g. avoid excessive parallel slave messaging waiting. This is mostly design and SQL issue. The number mount points is irrelevant novadays as well IMHO, especially when you're using async IO, SANs SAME like architecture. The only thing what comes into my mind where splitting into mount points (thus different file systems) helps performance wise, is that with more file systems you got more file system locks and you can spread contention for these locks that way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 9:34 PM i know there are no magic formulas, but im hoping for something better than trial and error. i would assume that parallel query helps most when: 1. are doing work off of multiple mount points. 2. Have alot more LIOs to perform than PIOs(such as sorts). am i close on this? From: M Rafiq [EMAIL PROTECTED] Date: 2003/09/18 Thu PM 01:34:44 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: max parallel query Tanel, You are right. There is a param parallel_automatic(I don't remember exact name as I don't have access to database) which should be set to true and let system decide how many pq slave to be used. For max_parallel_server default is 5 and can be set to higher number which memory and cpu can handle. It is faster because it uses all system resources at a given time. Rarallel process can be used for large batch jobs during off-peak time. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 18 Sep 2003 08:39:44 -0800 There is no simple magic formula how many parallel slaves you should have. It all depends on your CPU and IO utilization. Yes, utilization, because if your CPUs or IO are running at their limits already, you won't benefit from parallel execution at all. In fact PX may make the situation even worse, because it's designed to give you the results the fastest way, not the most efficient way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:15 PM We run multiple instances on the same server. My understanding is max_parallel_servers should be set to 4 x CPU. We have 4 CPUs which means 16. however, does this take into account multiple instances on the same server? -- 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: Tanel Poder 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). _ Send and receive larger attachments with Hotmail Extra Storage. http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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
Re: Re: max parallel query
hmmm... when i run statspack during a big load. most of my waits are from redo log waits and read from a staging datafile. we have all of our datafiles on the same I/O mount. We are using a Network Appliance back end with asynch I/O. are you telling me that putting these files on seperate mount points will have no effect? I dont quite follow asynch I/O. I dont have much of a hard ware OS background. I cant find any docs or articles on how to tune Parallel Operations. All I see are basic syntax. Do you know of any? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 5:24 PM If you want rules of thumb, then take CPU_COUNT+1 up to CPU_COUNT *2. I think LIO PIO ratio is irrelevant, the most important is whether you are able to construct optimal parallel execution plan, e.g. avoid excessive parallel slave messaging waiting. This is mostly design and SQL issue. The number mount points is irrelevant novadays as well IMHO, especially when you're using async IO, SANs SAME like architecture. The only thing what comes into my mind where splitting into mount points (thus different file systems) helps performance wise, is that with more file systems you got more file system locks and you can spread contention for these locks that way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 9:34 PM i know there are no magic formulas, but im hoping for something better than trial and error. i would assume that parallel query helps most when: 1. are doing work off of multiple mount points. 2. Have alot more LIOs to perform than PIOs(such as sorts). am i close on this? From: M Rafiq [EMAIL PROTECTED] Date: 2003/09/18 Thu PM 01:34:44 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: max parallel query Tanel, You are right. There is a param parallel_automatic(I don't remember exact name as I don't have access to database) which should be set to true and let system decide how many pq slave to be used. For max_parallel_server default is 5 and can be set to higher number which memory and cpu can handle. It is faster because it uses all system resources at a given time. Rarallel process can be used for large batch jobs during off-peak time. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 18 Sep 2003 08:39:44 -0800 There is no simple magic formula how many parallel slaves you should have. It all depends on your CPU and IO utilization. Yes, utilization, because if your CPUs or IO are running at their limits already, you won't benefit from parallel execution at all. In fact PX may make the situation even worse, because it's designed to give you the results the fastest way, not the most efficient way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:15 PM We run multiple instances on the same server. My understanding is max_parallel_servers should be set to 4 x CPU. We have 4 CPUs which means 16. however, does this take into account multiple instances on the same server? -- 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: Tanel Poder 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). _ Send and receive larger attachments with Hotmail Extra Storage.
RE: Re: max parallel query
Ryan You are probably bottlenecking on the NetApp. Probably your network link to it. If you have some regular (a.k.a. direct attached) disk available, consider using it for your redo logs. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 18, 2003 4:45 PM To: Multiple recipients of list ORACLE-L hmmm... when i run statspack during a big load. most of my waits are from redo log waits and read from a staging datafile. we have all of our datafiles on the same I/O mount. We are using a Network Appliance back end with asynch I/O. are you telling me that putting these files on seperate mount points will have no effect? I dont quite follow asynch I/O. I dont have much of a hard ware OS background. I cant find any docs or articles on how to tune Parallel Operations. All I see are basic syntax. Do you know of any? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 5:24 PM If you want rules of thumb, then take CPU_COUNT+1 up to CPU_COUNT *2. I think LIO PIO ratio is irrelevant, the most important is whether you are able to construct optimal parallel execution plan, e.g. avoid excessive parallel slave messaging waiting. This is mostly design and SQL issue. The number mount points is irrelevant novadays as well IMHO, especially when you're using async IO, SANs SAME like architecture. The only thing what comes into my mind where splitting into mount points (thus different file systems) helps performance wise, is that with more file systems you got more file system locks and you can spread contention for these locks that way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 9:34 PM i know there are no magic formulas, but im hoping for something better than trial and error. i would assume that parallel query helps most when: 1. are doing work off of multiple mount points. 2. Have alot more LIOs to perform than PIOs(such as sorts). am i close on this? From: M Rafiq [EMAIL PROTECTED] Date: 2003/09/18 Thu PM 01:34:44 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: max parallel query Tanel, You are right. There is a param parallel_automatic(I don't remember exact name as I don't have access to database) which should be set to true and let system decide how many pq slave to be used. For max_parallel_server default is 5 and can be set to higher number which memory and cpu can handle. It is faster because it uses all system resources at a given time. Rarallel process can be used for large batch jobs during off-peak time. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 18 Sep 2003 08:39:44 -0800 There is no simple magic formula how many parallel slaves you should have. It all depends on your CPU and IO utilization. Yes, utilization, because if your CPUs or IO are running at their limits already, you won't benefit from parallel execution at all. In fact PX may make the situation even worse, because it's designed to give you the results the fastest way, not the most efficient way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:15 PM We run multiple instances on the same server. My understanding is max_parallel_servers should be set to 4 x CPU. We have 4 CPUs which means 16. however, does this take into account multiple instances on the same server? -- 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: Tanel Poder 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
Re: Re: max parallel query
If different mount point means different disk spindle group or different disk array, then of course, your performance will be improved, but if we talk about the same box, same number of disks just split to two or three, you probably won't get any performance increase. One disk spindle still remains one disk spindle, no matter how you split them. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 19, 2003 12:44 AM hmmm... when i run statspack during a big load. most of my waits are from redo log waits and read from a staging datafile. we have all of our datafiles on the same I/O mount. We are using a Network Appliance back end with asynch I/O. are you telling me that putting these files on seperate mount points will have no effect? I dont quite follow asynch I/O. I dont have much of a hard ware OS background. I cant find any docs or articles on how to tune Parallel Operations. All I see are basic syntax. Do you know of any? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 5:24 PM If you want rules of thumb, then take CPU_COUNT+1 up to CPU_COUNT *2. I think LIO PIO ratio is irrelevant, the most important is whether you are able to construct optimal parallel execution plan, e.g. avoid excessive parallel slave messaging waiting. This is mostly design and SQL issue. The number mount points is irrelevant novadays as well IMHO, especially when you're using async IO, SANs SAME like architecture. The only thing what comes into my mind where splitting into mount points (thus different file systems) helps performance wise, is that with more file systems you got more file system locks and you can spread contention for these locks that way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 9:34 PM i know there are no magic formulas, but im hoping for something better than trial and error. i would assume that parallel query helps most when: 1. are doing work off of multiple mount points. 2. Have alot more LIOs to perform than PIOs(such as sorts). am i close on this? From: M Rafiq [EMAIL PROTECTED] Date: 2003/09/18 Thu PM 01:34:44 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: max parallel query Tanel, You are right. There is a param parallel_automatic(I don't remember exact name as I don't have access to database) which should be set to true and let system decide how many pq slave to be used. For max_parallel_server default is 5 and can be set to higher number which memory and cpu can handle. It is faster because it uses all system resources at a given time. Rarallel process can be used for large batch jobs during off-peak time. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 18 Sep 2003 08:39:44 -0800 There is no simple magic formula how many parallel slaves you should have. It all depends on your CPU and IO utilization. Yes, utilization, because if your CPUs or IO are running at their limits already, you won't benefit from parallel execution at all. In fact PX may make the situation even worse, because it's designed to give you the results the fastest way, not the most efficient way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:15 PM We run multiple instances on the same server. My understanding is max_parallel_servers should be set to 4 x CPU. We have 4 CPUs which means 16. however, does this take into account multiple instances on the same server? -- 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
Re: Re: max parallel query
i dont manage the netapp and am not a hardware person. could you explain a little better? Is netapp similiar to SAN? what is asynch I/O? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:24 PM Ryan You are probably bottlenecking on the NetApp. Probably your network link to it. If you have some regular (a.k.a. direct attached) disk available, consider using it for your redo logs. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 18, 2003 4:45 PM To: Multiple recipients of list ORACLE-L hmmm... when i run statspack during a big load. most of my waits are from redo log waits and read from a staging datafile. we have all of our datafiles on the same I/O mount. We are using a Network Appliance back end with asynch I/O. are you telling me that putting these files on seperate mount points will have no effect? I dont quite follow asynch I/O. I dont have much of a hard ware OS background. I cant find any docs or articles on how to tune Parallel Operations. All I see are basic syntax. Do you know of any? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 5:24 PM If you want rules of thumb, then take CPU_COUNT+1 up to CPU_COUNT *2. I think LIO PIO ratio is irrelevant, the most important is whether you are able to construct optimal parallel execution plan, e.g. avoid excessive parallel slave messaging waiting. This is mostly design and SQL issue. The number mount points is irrelevant novadays as well IMHO, especially when you're using async IO, SANs SAME like architecture. The only thing what comes into my mind where splitting into mount points (thus different file systems) helps performance wise, is that with more file systems you got more file system locks and you can spread contention for these locks that way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 9:34 PM i know there are no magic formulas, but im hoping for something better than trial and error. i would assume that parallel query helps most when: 1. are doing work off of multiple mount points. 2. Have alot more LIOs to perform than PIOs(such as sorts). am i close on this? From: M Rafiq [EMAIL PROTECTED] Date: 2003/09/18 Thu PM 01:34:44 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: max parallel query Tanel, You are right. There is a param parallel_automatic(I don't remember exact name as I don't have access to database) which should be set to true and let system decide how many pq slave to be used. For max_parallel_server default is 5 and can be set to higher number which memory and cpu can handle. It is faster because it uses all system resources at a given time. Rarallel process can be used for large batch jobs during off-peak time. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 18 Sep 2003 08:39:44 -0800 There is no simple magic formula how many parallel slaves you should have. It all depends on your CPU and IO utilization. Yes, utilization, because if your CPUs or IO are running at their limits already, you won't benefit from parallel execution at all. In fact PX may make the situation even worse, because it's designed to give you the results the fastest way, not the most efficient way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:15 PM We run multiple instances on the same server. My understanding is max_parallel_servers should be set to 4 x CPU. We have 4 CPUs which means 16. however, does this take into account multiple instances on the same server? -- 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
Re: Re: max parallel query
Netapp is NAS afaik, that means a bunch of disks used over fast network. SAN is a bunch of disks used over SCSI or Fibre interface (EMC Clariion for example). I tend to trust and appreciate SAN more than NAS, but NAS can be more cost effective in small-to medium environments. What I meant, is that if you have let say 16 disks striped mirrored with SAME, it doesn't improve your performance if you split your stripe to two paritions (mount point) instead of just one partition. Async IO basically enables Oracle processes to deal with other useful stuff when waiting on writing or reading data to/from disk. Also, operating system kernel can often make some IO optimizations in case of async IO. Read more about it: http://www.ixora.com.au/tips/use_asynchronous_io.htm Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, September 19, 2003 2:29 AM i dont manage the netapp and am not a hardware person. could you explain a little better? Is netapp similiar to SAN? what is asynch I/O? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:24 PM Ryan You are probably bottlenecking on the NetApp. Probably your network link to it. If you have some regular (a.k.a. direct attached) disk available, consider using it for your redo logs. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 18, 2003 4:45 PM To: Multiple recipients of list ORACLE-L hmmm... when i run statspack during a big load. most of my waits are from redo log waits and read from a staging datafile. we have all of our datafiles on the same I/O mount. We are using a Network Appliance back end with asynch I/O. are you telling me that putting these files on seperate mount points will have no effect? I dont quite follow asynch I/O. I dont have much of a hard ware OS background. I cant find any docs or articles on how to tune Parallel Operations. All I see are basic syntax. Do you know of any? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 5:24 PM If you want rules of thumb, then take CPU_COUNT+1 up to CPU_COUNT *2. I think LIO PIO ratio is irrelevant, the most important is whether you are able to construct optimal parallel execution plan, e.g. avoid excessive parallel slave messaging waiting. This is mostly design and SQL issue. The number mount points is irrelevant novadays as well IMHO, especially when you're using async IO, SANs SAME like architecture. The only thing what comes into my mind where splitting into mount points (thus different file systems) helps performance wise, is that with more file systems you got more file system locks and you can spread contention for these locks that way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 9:34 PM i know there are no magic formulas, but im hoping for something better than trial and error. i would assume that parallel query helps most when: 1. are doing work off of multiple mount points. 2. Have alot more LIOs to perform than PIOs(such as sorts). am i close on this? From: M Rafiq [EMAIL PROTECTED] Date: 2003/09/18 Thu PM 01:34:44 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: max parallel query Tanel, You are right. There is a param parallel_automatic(I don't remember exact name as I don't have access to database) which should be set to true and let system decide how many pq slave to be used. For max_parallel_server default is 5 and can be set to higher number which memory and cpu can handle. It is faster because it uses all system resources at a given time. Rarallel process can be used for large batch jobs during off-peak time. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 18 Sep 2003 08:39:44 -0800 There is no simple magic formula how many parallel slaves you should have. It all depends on your CPU and IO utilization. Yes, utilization, because if your CPUs or IO are running at their limits already, you won't benefit from parallel execution at all. In fact PX may make the situation even worse, because it's designed to give you the results the fastest way, not the most efficient way. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, September 18, 2003 6:15 PM We run multiple instances on the same
