Re: SMP and 7.x performance questions?

On Mar 11, 6:37pm, Cheryl Kendricks wrote: > Subject: Re: SMP and 7.x performance questions? > Everyone keeps repeating Round Robin is not a Good Fragmentation Method. > Well in my Class I was told different. So, Before I start this process > could someone give me the Good, Bad and Ugly of using Round Robin. > > } Round Robin is almost never a good idea as it really doesn't really provide > } benefits just a whole host of performance problems. Well thats my opinion > } anyway! This statement might have been a little strong but generally I would still stand by it. Fernando's comments are valid but are limited to a very small group of users which are the large data warehouse systems with SMP/MPP systems and large amounts of data where the database designer has no real idea how the data is going to be analysed. Round robin does random distribution of your data. In an OLTP environment this is a disaster. To find a row with an internal index requires index search in every single fragment to find the one where the row was put. In addition if you want a set of rows based on an internal index the same scan has to be done and then you have a sort and merge process to go through before you can produce the result. As a database designer you should always be able to do better than this. Choose your most used index and use a fragment expression based on a reasonable segmentation of that index. This allows your regular queries to access the tables in order using an internal index that is faster because it has less index levels to go down to reach the row. (It happens also at the moment that external indexes are less efficient anyway in this their first incarnation). All other indexes are made external indexes if performance requires it. This avoids the index scanning, sort and merge which are very expensive. I have to say that, IMHO, this is almost always true of the Data Warehouse environment as well. In the subject areas that I have worked on there has always been some index that stands out as the one that the vast majority of queries would use. These tend to be your basic business keys to the data such as customer account id, stock id, etc. You still use the PDQ, SMP/MPP capabilities but these perform better when they can be working with organised sets of data. If you really don't know how your data is going to be accessed then you might start out with round robin. This would normally occur only if nobody is willing to stick their neck out and choose an index. In any case your data warehose software should keep track of which queries are used and how often and from that the DBA can work out which indexes are most used. At that point the DBA should expect to change the fragmentation policy. This is one of the ongoing, lifetime responsibilities of a DBA on a Data Warehouse. Alternatively you make all the indexes external. At that point round robin makes sense but then you loose the small benefit from the reduced index levels searching on a internal index based fragmentation scheme. In addition to reach the row requires access to a different dbspace than the last index read. This may decrease overall performance. Cheers - Jim -- ----------------------------------------------------------------------------- Jim Gordon DHL Airways Inc. jgordon@us.dhl.com ----------------------------------------------------------------------------- My opinions are my own. They may vary with time but they remain mine!