Re: Row Migration
again, thanks. I also have shelves full of books, but I can't read them cover to cover (and don't want to, I like having a life that isn't completely Oracle). Not even yours Jared, not even yours. I think the only ones I can say for certainty that I've read cover to cover are a)the ones I've written and b) the ones I've edited Other than that, I read the intros, the table of contents and the chapters that seem relevant. And TRY to go back and look at them again another time. --- Jared Still [EMAIL PROTECTED] wrote: Well, I can't speak for Rachel, but I certainly have your book, and very probably nearly every other Oracle book worth serious consideration, as well as an entire shelf full of Perl books staring me down every day, every one of them reminding me of how little I really know and how much I have to learn. And there's the recently printed 9iR2 concepts manual that I'm working through, as well as trying to finally learn how Perl tie's *really* work ( as opposed to bow ties. Dunno how they work either ) Oh, I *have* your book. :) Jared On Saturday 28 December 2002 05:13, Jonathan Lewis wrote: Woe is me ! Another person who hasn't got a copy of my book; published Dec 2000. P.227 - 230: Inline Updatable Views A brief discussion of the method and note about requirements. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 28 December 2002 12:44 it's pretty cool no matter who posts it! :) I ended up writing a quick and dirty pl/sql procedure one night at 3AM when we were in crisis mode and I couldn't remember how to write the darn thing! so this is going on ALL my computers -- 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 may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Row Migration
Thanks for giving insight as to how you would approach this. There were couple of interesting aspects that I hadn't considered monitoring. Also, the following got my attention though we aren't on 9iR2. Steve Adams recently dropped me a note pointing out that 9.2 has a clever little trick built into it that makes it very keen to add ITL entries to the target ITL list when a row is migrated into a block. In his example he managed to end up with 169 ITL entries in an 8K block even though MAXTRANS was set to 5. The purpose of the exercise, apparently, it to avoid an internal deadlock when using parallel DML. Are you talking about a single process using parallel DML? I guess I could imagine that even in a case such as a single parallel update setting a column to a constant. Even though the parallelism would have broken things up by block ranges, migration could occur into the same block for multiple processes. The purpose of the exercise, apparently, it to avoid an internal deadlock when using parallel DML. The upshot of the exercise in your pre-40% case might be to waste at least 25% of every block in the table. It is an unfortunate coincidence that if you write the update in the worst possible way (single row commits) you will probably suffer the least damage. Interesting. We are trying to get the developers away from cursor based processing, with some success. Most new stuff being developed, at least those where we have access to the developers, is written more sanely -- none of this 3 nested cursors to simply do an insert, or cursor update loops when a simple update will do. You know, some places resist it even when shown the performance penalty. I had a place like that a couple of years ago -- absolute refusal to consider it since people are used coding this way. What? 10 times more code, and more complex, so you can run 50 times slower? ;-) Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins 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: Row Migration
This is a single process executing PDML - As you guessed, the problem arises where an update causes a migration into the top block on the free list - every PX slave is updating a different range, but they might all migrate into the same free block. Except -- a) Oracle handles this differently on PDML updates to partitioned tables with global indexes: the degree of parallelism in this case is limited to the smallest value for INITRANS found on any of the global indexes. b) If a migration is treated as an insert, then for normal inserts, an absence of freelists results in the next block on the freelist being used anyway. So I can't see why Oracle Corp. has done it this way. (I'm not going to worry about it though, at least for the next couple of weeks - and I'm going to see Steve in Denmark soon, and he may have all the answers by then). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 29 December 2002 15:45 Are you talking about a single process using parallel DML? I guess I could imagine that even in a case such as a single parallel update setting a column to a constant. Even though the parallelism would have broken things up by block ranges, migration could occur into the same block for multiple processes. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Row Migration
An interesting philosophical question - How do you find out something that you ought to know, when you don't know that it exists to be found out about, and don't know that you need to know it ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 29 December 2002 03:13 oh I have your book, I just didn't remember where I had seen the code! blame too little caffeine, blame being woken at 3AM, blame a senior moment :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Row Migration
I read the list of course. Or post a question on how to do something. I have a quirky sort of memory that jogs me with I seem to recall having read something sort of along those lines when I have a problem. And I've found that the answer either has already been posted or I post the question and get the answer (usually multiple times) from the list I can't learn everything, certainly can't learn everything about Oracle. I recall a presentation you did, where you talked about how large the documentation set was, even if you only read the DBA related manuals (I think at the time you said there were something like 20,000 pages in the doc set and 9500 of them DBA related). And this was for 8i. It's gotten worse in 9i, and with Oracle dedicated to releasing new versions about every 18 months, the problem is only going to grow. I can't read and learn all the new stuff before something newer comes along. Case in point: while doing the updates for Oracle DBA 101 for 9i, we only had 9iR1 available, as R2 had not been released. We wrote about DataGuard and the 4 ways to set it up. By the time the book was ready to be printed, 9iR2 was out and Oracle had already changed DataGuard to have only 3 ways to configure it. We were lucky, we managed to catch it and change it after page proofs but before printing. --- Jonathan Lewis [EMAIL PROTECTED] wrote: An interesting philosophical question - How do you find out something that you ought to know, when you don't know that it exists to be found out about, and don't know that you need to know it ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 29 December 2002 03:13 oh I have your book, I just didn't remember where I had seen the code! blame too little caffeine, blame being woken at 3AM, blame a senior moment :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Row Migration
Larry is the SQL guru. Every time he drops a pearl of wisdom like we really want to make sure all the not-null columns are identified because it can affect the ability of the optimizer to transform a corelated IN subquery into an equi-join, and allow us to drop this index now that we won't need it for a correlated lookup, which will force all of our queries to rip through this table using hash joins I wonder how the heck he KNOWS all that stuff. I THINK it is just pure brains, although sometimes I suspect he has a deal with the devil ... or Larry Ellison. :) Jack don't feel too sheepish, I didn't know it either. Larry is the SQL guru and I bow to his knowledge. and had already saved off this email as this sort of update is something we do often and I ALWAYS have problems figuring out the correct SQL :) rachel --- Jared Still [EMAIL PROTECTED] wrote: Geez, I didn't know you could do that. Sheepishly, Jared On Friday 27 December 2002 03:38, Larry Elkins wrote: Someone asked in a back channel email if parallelism is used. The select portion of the update statement uses parallelism (though the updates themselves get serialized) through the use of an in-line join update (to avoid the second sub-query commonly used to constrain the rows being updated): Update (Select /*+ parallel hints */ From a,b Where a.key = b.key) Set a.col1 = b.col1, a.col2 = b.col2 . Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Larry Elkins Sent: Thursday, December 26, 2002 6:09 PM To: Multiple recipients of list ORACLE-L Subject: Row Migration Listers, 8.1.7.4 64 Bit Solaris Does row migration utilize DB File Sequential Reads on the table? Off the top of my head I would expect so, but I've never tested something like that before. -- 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 may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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). Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey 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: Row Migration
it's pretty cool no matter who posts it! :) I ended up writing a quick and dirty pl/sql procedure one night at 3AM when we were in crisis mode and I couldn't remember how to write the darn thing! so this is going on ALL my computers --- Larry Elkins [EMAIL PROTECTED] wrote: Actually, I first learned that trick from a Connor posting on this list (maybe around 2 or 3 years ago?) It has to conform to the same key preserved rules that updateable views do since that's what it is, just an in-line view as opposed to an actual physical view. So supposedly it's been available since 7.x when updateable views came along (and in-line views). There is an example in the Data Warehousing Guide (I think that's the one) in the 8i documentation, though the example is wrong (it omits the FROM clause). Anyway, I thought it was pretty cool the first time I saw Connor post it. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Rachel Carmichael Sent: Friday, December 27, 2002 10:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: Row Migration don't feel too sheepish, I didn't know it either. Larry is the SQL guru and I bow to his knowledge. and had already saved off this email as this sort of update is something we do often and I ALWAYS have problems figuring out the correct SQL :) rachel --- Jared Still [EMAIL PROTECTED] wrote: Geez, I didn't know you could do that. Sheepishly, Jared On Friday 27 December 2002 03:38, Larry Elkins wrote: Someone asked in a back channel email if parallelism is used. The select portion of the update statement uses parallelism (though the updates themselves get serialized) through the use of an in-line join update (to avoid the second sub-query commonly used to constrain the rows being updated): Update (Select /*+ parallel hints */ From a,b Where a.key = b.key) Set a.col1 = b.col1, a.col2 = b.col2 . -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Row Migration
Woe is me ! Another person who hasn't got a copy of my book; published Dec 2000. P.227 - 230: Inline Updatable Views A brief discussion of the method and note about requirements. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 28 December 2002 12:44 it's pretty cool no matter who posts it! :) I ended up writing a quick and dirty pl/sql procedure one night at 3AM when we were in crisis mode and I couldn't remember how to write the darn thing! so this is going on ALL my computers -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Row Migration
I managed to miss the point that you were concerned with work done and time lost on the update rather than the subsequent retrieval. Concerns about scatter are pretty irrelevant at this point. To address the issues of row migration (and strictly ignoring row chaining), I would start by building a very simple test case with a couple of blocks of data, rigged so that I could update one row without it chaining, and update another so it chains. In both cases I would dump the data blocks, the undo blocks and the redo log immediately after the update, whilst taking snapshots of session stats, undo stats, v$transaction, and latches. The primary issue (in the simplest, most naked case) is that instead of a single undo record and a single redo record being, you get three undo and three redo records (which means three calls on the redo latches). The sequence seems to be: lock row in original block insert migrated copy of row in next freelist block replace row data in original block with forward pointer This is ignoring all the costs of starting a transaction, and any index modifications, and any need to allocate a new block and move the HWM. But under these conditions, the cost of migration due to update is roughly three times the cost of a simple update in terms of undo, redo and latching. Given this as a basis for consideration, there are two extremes: If you already have a wreck of a batch application which runs a pl/sql loop to update and commit on each row, whilst updating half a dozen indexes, and every row gets several updates, of which an average of one per row will cause a migration, then you will hardly notice the difference. If you have an array-based update, or at least don't commit every row, and you aren't updating loads of indexes, you will notice a significant lift in undo and redo. You may experience extra buffer activity because of the extra UNDO action (more dirty blocks means faster ageing and therefore potential re-reading of useful cached blocks). Things still to test: What exactly happens to the indexes - I believe that indexes will ALWAYS point to the head rowid, even if a particular index entry comes into existence as a result of an update that causes a row to migrate. But I haven't checked that in detail for at least 3 years What happens if you have multiple concurrent updates running. I believe the quantity of migration can be exacerbated. When a row migrates, it creates more space in the block it is migrating from, which can be used by other rows in that block. However if a second process grows a row, it cannot use the space left by the migration of a row that has been updated by the first process until the first process commits. QED (Again - to be checked). What happens when you update the migrated row. If the update would cause it to migrate from the second block, it MAY migrate back to the first block if it now fits - but it may have to migrate to a completely different block. So for each scenario, how much excess undo and redo get generated. And, of course, there is the quantitative effect to look at, because you need to emulate your system. lock row in original block insert migrated copy of row in next freelist block replace row data in original block with forward pointer How many of the undo and redo records will be small, how many will be large ? It depends on the starting and finishing size of the row. BTW - one of the joys of block dumps: if you do this on Oracle 9, you may find that performance is affected quite severely. Steve Adams recently dropped me a note pointing out that 9.2 has a clever little trick built into it that makes it very keen to add ITL entries to the target ITL list when a row is migrated into a block. In his example he managed to end up with 169 ITL entries in an 8K block even though MAXTRANS was set to 5. The purpose of the exercise, apparently, it to avoid an internal deadlock when using parallel DML. The upshot of the exercise in your pre-40% case might be to waste at least 25% of every block in the table. It is an unfortunate coincidence that if you write the update in the worst possible way (single row commits) you will probably suffer the least damage. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 28 December 2002 01:49 So I'm doomed? ;-) Ok, so how am I going to know which block it went to, the first step towards seeing if
Re: Row Migration
oh I have your book, I just didn't remember where I had seen the code! blame too little caffeine, blame being woken at 3AM, blame a senior moment :) --- Jonathan Lewis [EMAIL PROTECTED] wrote: Woe is me ! Another person who hasn't got a copy of my book; published Dec 2000. P.227 - 230: Inline Updatable Views A brief discussion of the method and note about requirements. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 28 December 2002 12:44 it's pretty cool no matter who posts it! :) I ended up writing a quick and dirty pl/sql procedure one night at 3AM when we were in crisis mode and I couldn't remember how to write the darn thing! so this is going on ALL my computers -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Row Migration
Well, I can't speak for Rachel, but I certainly have your book, and very probably nearly every other Oracle book worth serious consideration, as well as an entire shelf full of Perl books staring me down every day, every one of them reminding me of how little I really know and how much I have to learn. And there's the recently printed 9iR2 concepts manual that I'm working through, as well as trying to finally learn how Perl tie's *really* work ( as opposed to bow ties. Dunno how they work either ) Oh, I *have* your book. :) Jared On Saturday 28 December 2002 05:13, Jonathan Lewis wrote: Woe is me ! Another person who hasn't got a copy of my book; published Dec 2000. P.227 - 230: Inline Updatable Views A brief discussion of the method and note about requirements. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 28 December 2002 12:44 it's pretty cool no matter who posts it! :) I ended up writing a quick and dirty pl/sql procedure one night at 3AM when we were in crisis mode and I couldn't remember how to write the darn thing! so this is going on ALL my computers -- 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 may also send the HELP command for other information (like subscribing).
Re: Row Migration
yes, row migration will degrade the performance.. - Original Message - From: Larry Elkins To: Multiple recipients of list ORACLE-L Sent: Friday, December 27, 2002 5:38 AM Subject: Row Migration Listers,8.1.7.4 64 Bit SolarisDoes row migration utilize DB File Sequential Reads on the table? Off thetop of my head I would expect so, but I've never tested something like thatbefore.Trying to figure out if row migration is the cause of the slowdown in apackage (well, it's probably slowing it down, just trying to gauge theimpact). PctFree is 10, and new feeds contain lots of elements that had beenempty before. As a result, a very large number of rows are being updatedwith the new info being applied, effectively doubling the row length. Wouldcertainly expect row migration to occur. When running, execution time hasquadrupled, and we see significant waits on DB File Sequential Reads, withthe file/block values and dba_extents indicating the table, not an index.The working idea at this point is that all those DB File Sequential Readwaits on the table are possibly related to rows being migrated. Anyonetested for this?We will be building a test case on Friday. One with PctFree 10 and thecolumns being updated having nulls. Will gather the waits, before and aftersesstat's, analyze list chained rows, both before and after, total blocks,rows per block, etc. Then rebuild the test having a PCTFREE of 50 and do thesame thing. Some wildcards -- with the blocks less tightly packed, we willhave to visit nearly double the number of blocks (maybe offset bymigration), contention, and various other things to take into account. Butthe main thing we are focusing in on is if we continue to see the db filesequential read waits on the table. I guess the fact that we are seeingwaits is indicative of some I/O contention, but trying to determine if, andhow much, of that I/O is due to row migration, in which case a largerPCTFREE could provide some more immediate relief. No FK/PK stuff, uniqueindex is there, but it should resolve uniqueness using the index, not thetable. Maybe have left some things out. This came up a few days ago, butjust really started thinking about it and digging into it. And the endresult is we don't want migrated rows, just looking to see if the rowmigration is the primary cause of the performance downturn.Regards,Larry G. Elkins[EMAIL PROTECTED]214.954.1781-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Larry Elkins INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Row Migration
Someone asked in a back channel email if parallelism is used. The select portion of the update statement uses parallelism (though the updates themselves get serialized) through the use of an in-line join update (to avoid the second sub-query commonly used to constrain the rows being updated): Update (Select /*+ parallel hints */ From a,b Where a.key = b.key) Set a.col1 = b.col1, a.col2 = b.col2 . Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Larry Elkins Sent: Thursday, December 26, 2002 6:09 PM To: Multiple recipients of list ORACLE-L Subject: Row Migration Listers, 8.1.7.4 64 Bit Solaris Does row migration utilize DB File Sequential Reads on the table? Off the top of my head I would expect so, but I've never tested something like that before. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins 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: Row Migration
Well, yes, Iwould agree with that ;-) What we are trying to determine here in this particular case is how much or what percentage of the slowdown in the process is due to the migration of rows. We aren't ready (until we do some testing) tomake a blanket statement that row migration *alone* is the cause of the significant slowdown. In other words, I'm not willing to make a statement to the powers that be that simply increasing the pctfree is going to make things normal again until we have a chance to do some more detailed monitoring and testing. Regards,Larry G. Elkins[EMAIL PROTECTED]214.954.1781 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar NSent: Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Row Migration yes, row migration will degrade the performance..
Re: Row Migration
Row migration means extra IO's. If IO is taking up any significant part of your response time, then you don't want extra IO, of course. And the IO will be single-block IO (sequential reads) because a stub is left in the originating block pointing to the new block where the row migrates to - and that requires a single-block IO. Your test with pctfree 10, then collect stats, etc., then repeat the test with pctfree 50 sounds fine to me. Good luck. Mogens Anand Kumar N wrote: yes, row migration will degrade the performance.. - Original Message - From: Larry Elkins To: Multiple recipients of list ORACLE-L Sent: Friday, December 27, 2002 5:38AM Subject: Row Migration Listers, 8.1.7.4 64 Bit Solaris Does rowmigration utilize DB File Sequential Reads on the table? Off the top of myhead I would expect so, but I've never tested something like that before. Trying to figure out if row migration is the cause ofthe slowdown in a package (well, it's probably slowing it down, just tryingto gauge the impact). PctFree is 10, and new feeds contain lots of elementsthat had been empty before. As a result, a very large number of rows arebeing updated with the new info being applied, effectively doubling the rowlength. Would certainly expect row migration to occur. When running,execution time has quadrupled, and we see significant waits on DB FileSequential Reads, with the file/block values and dba_extents indicating thetable, not an index. The working idea at this point is that all those DBFile Sequential Read waits on the table are possibly related to rows beingmigrated. Anyone tested for this? We will be building a test case onFriday. One with PctFree 10 and the columns being updated having nulls.Will gather the waits, before and after sesstat's, analyze list chainedrows, both before and after, total blocks, rows per block, etc. Thenrebuild the test having a PCTFREE of 50 and do the same thing. Somewildcards -- with the blocks less tightly packed, we will have to visitnearly double the number of blocks (maybe offset by migration), contention,and various other things to take into account. But the main thing we arefocusing in on is if we continue to see the db file sequential read waitson the table. I guess the fact that we are seeing waits is indicative ofsome I/O contention, but trying to determine if, and how much, of that I/Ois due to row migration, in which case a larger PCTFREE could provide somemore immediate relief. No FK/PK stuff, unique index is there, but it shouldresolve uniqueness using the index, not the table. Maybe have left somethings out. This came up a few days ago, but just really started thinkingabout it and digging into it. And the end result is we don't want migratedrows, just looking to see if the row migration is the primary cause of theperformance downturn. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: LarryElkins INET: [EMAIL PROTECTED] Fat City NetworkServices -- 858-538-5051 http://www.fatcity.com San Diego,California -- Mailing list and webhostingservices - ToREMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACTspelling of 'ListGuru') and in the message BODY, include a line containing:UNSUB ORACLE-L (or the name of mailing list you want to be removedfrom). You may also send the HELP command for other information (likesubscribing).
RE: Row Migration
We've done a few tests here with chained vs. unchained rows, and the impact is anywhere from 50-200% overhead. So if it took about 10 seconds to do a query it will now take 15 to 30 seconds. It seamed to depend most on which rows we were returning... not hitting the chained rows as much helped speed it up. For each row operation, Oracle must read the block that contains the data, and the last piece of information in each block contains a null/not null rowid pointer to the next row piece. In a spanned row (one inserted that is too big for a single DB_BLOCK) the pointer (usually)points to the next physical block in the DB, and it goes pretty fast. In a chained row (one where someone has done an update, and the new information put into the row does not fit into the rest of the block -- which sounds like your case) the pointer (usually) points to a block at the end of the physical table in the DB file that contains the rest of the information. And it goes very slowly. Chaining can really grow to be progressively worse, if you continually update a column who spans the two blocks, oracle will not update the first block or the last block and instead create another new block at the end of the table for those new characters. So a read of that column now takes in 3 blocks, potentially spanned over the entire datafile. In Oracle 9i we've seen some really strange behavior too... when doing an import, or direct load Oracle will actually chain a row inside of a block, and none of the analyze for chained row commands will pick it up.It still causes the slow down, but you cannot fix it. It's actually been a while since I've really been able to look at this stuff, so if anyone has any clarifications or things they want to add, please do so. Nick -Original Message-From: Larry Elkins [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 3:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Row Migration Well, yes, Iwould agree with that ;-) What we are trying to determine here in this particular case is how much or what percentage of the slowdown in the process is due to the migration of rows. We aren't ready (until we do some testing) tomake a blanket statement that row migration *alone* is the cause of the significant slowdown. In other words, I'm not willing to make a statement to the powers that be that simply increasing the pctfree is going to make things normal again until we have a chance to do some more detailed monitoring and testing. Regards,Larry G. Elkins[EMAIL PROTECTED]214.954.1781 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar NSent: Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Row Migration yes, row migration will degrade the performance..
RE: Row Migration
Larry, Don't want to preach to the Guru, but have you checked the values for 'table fetch continued row'? StatisticTotal per Secondper Trans - table fetch by rowid 577,820,727 40,129.2 61,248.8 table fetch continued row 137,202 9.5 14.5 This when coming out of V$SESSTAT could give a good indication of number of fetches by migrated as well as chained rows for that session. You could also look at V$SESSION.MAX_WAIT for 'db file sequential read' events... Let us know what you find! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Larry Elkins [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 26, 2002 4:09 PM To: Multiple recipients of list ORACLE-L Subject: Row Migration Listers, 8.1.7.4 64 Bit Solaris Does row migration utilize DB File Sequential Reads on the table? Off the top of my head I would expect so, but I've never tested something like that before. Trying to figure out if row migration is the cause of the slowdown in a package (well, it's probably slowing it down, just trying to gauge the impact). PctFree is 10, and new feeds contain lots of elements that had been empty before. As a result, a very large number of rows are being updated with the new info being applied, effectively doubling the row length. Would certainly expect row migration to occur. When running, execution time has quadrupled, and we see significant waits on DB File Sequential Reads, with the file/block values and dba_extents indicating the table, not an index. The working idea at this point is that all those DB File Sequential Read waits on the table are possibly related to rows being migrated. Anyone tested for this? We will be building a test case on Friday. One with PctFree 10 and the columns being updated having nulls. Will gather the waits, before and after sesstat's, analyze list chained rows, both before and after, total blocks, rows per block, etc. Then rebuild the test having a PCTFREE of 50 and do the same thing. Some wildcards -- with the blocks less tightly packed, we will have to visit nearly double the number of blocks (maybe offset by migration), contention, and various other things to take into account. But the main thing we are focusing in on is if we continue to see the db file sequential read waits on the table. I guess the fact that we are seeing waits is indicative of some I/O contention, but trying to determine if, and how much, of that I/O is due to row migration, in which case a larger PCTFREE could provide some more immediate relief. No FK/PK stuff, unique index is there, but it should resolve uniqueness using the index, not the table. Maybe have left some things out. This came up a few days ago, but just really started thinking about it and digging into it. And the end result is we don't want migrated rows, just looking to see if the row migration is the primary cause of the performance downturn. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins 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: John Kanagaraj 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: Row Migration
When you do your testing, don't forget to keep an eye on the change in dependent logical I/O and latching. Fetching a migrated row will require an extra buffer visit to find the row data. This MAY turn into an extra disk read but at the least it IS another buffer visit, which means another hit on the cache-buffers-chains latch, and may mean further work done getting another buffered block to the correct read-consistent state. I think you'll have to model your test very carefully - it wouldn't be too hard to produce two different models with totally contradictory results - one based on the migration going to a relatively nearby block, the other based on the update and migration taking place in a way that ensures maximum scatter of the migrated row piece. The former may hide I/O problems, the latter may exaggerate the I/O problems and hide the latch issues; and in either case you may fail to emulate the read-consistency issue properly. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 27 December 2002 12:25 Well, yes, I would agree with that ;-) What we are trying to determine here in this particular case is how much or what percentage of the slowdown in the process is due to the migration of rows. We aren't ready (until we do some testing) to make a blanket statement that row migration *alone* is the cause of the significant slowdown. In other words, I'm not willing to make a statement to the powers that be that simply increasing the pctfree is going to make things normal again until we have a chance to do some more detailed monitoring and testing. Regards, Larry G. Elkins -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Row Migration
Title: RE: Row Migration also, what version of Oracle and how many columns on the table? -Original Message- From: Nick Wagner [mailto:[EMAIL PROTECTED]] Sent: Friday, December 27, 2002 9:39 AM To: Multiple recipients of list ORACLE-L Subject: RE: Row Migration We've done a few tests here with chained vs. unchained rows, and the impact is anywhere from 50-200% overhead. So if it took about 10 seconds to do a query it will now take 15 to 30 seconds. It seamed to depend most on which rows we were returning... not hitting the chained rows as much helped speed it up. For each row operation, Oracle must read the block that contains the data, and the last piece of information in each block contains a null/not null rowid pointer to the next row piece. In a spanned row (one inserted that is too big for a single DB_BLOCK) the pointer (usually) points to the next physical block in the DB, and it goes pretty fast. In a chained row (one where someone has done an update, and the new information put into the row does not fit into the rest of the block -- which sounds like your case) the pointer (usually) points to a block at the end of the physical table in the DB file that contains the rest of the information. And it goes very slowly. Chaining can really grow to be progressively worse, if you continually update a column who spans the two blocks, oracle will not update the first block or the last block and instead create another new block at the end of the table for those new characters. So a read of that column now takes in 3 blocks, potentially spanned over the entire datafile. In Oracle 9i we've seen some really strange behavior too... when doing an import, or direct load Oracle will actually chain a row inside of a block, and none of the analyze for chained row commands will pick it up. It still causes the slow down, but you cannot fix it. It's actually been a while since I've really been able to look at this stuff, so if anyone has any clarifications or things they want to add, please do so. Nick -Original Message- From: Larry Elkins [mailto:[EMAIL PROTECTED]] Sent: Friday, December 27, 2002 3:19 AM To: Multiple recipients of list ORACLE-L Subject: RE: Row Migration Well, yes, I would agree with that ;-) What we are trying to determine here in this particular case is how much or what percentage of the slowdown in the process is due to the migration of rows. We aren't ready (until we do some testing) to make a blanket statement that row migration *alone* is the cause of the significant slowdown. In other words, I'm not willing to make a statement to the powers that be that simply increasing the pctfree is going to make things normal again until we have a chance to do some more detailed monitoring and testing. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar N Sent: Friday, December 27, 2002 2:09 AM To: Multiple recipients of list ORACLE-L Subject: Re: Row Migration yes, row migration will degrade the performance..
RE: Row Migration
Thanks for those comments, but that's a little down the road for what I'm looking at right now -- trying to determine the overhead associated with updates and the update causing a row to migrate. We don't intend to let the chaining actually make it into the DM. But it's good to see someone put some numbers on it, and something I would be interested in repeating at some time in the future should migration/chaining occur in the target table. Regards,Larry G. Elkins[EMAIL PROTECTED]214.954.1781 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Nick WagnerSent: Friday, December 27, 2002 11:39 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Row Migration We've done a few tests here with chained vs. unchained rows, and the impact is anywhere from 50-200% overhead. So if it took about 10 seconds to do a query it will now take 15 to 30 seconds. It seamed to depend most on which rows we were returning... not hitting the chained rows as much helped speed it up. For each row operation, Oracle must read the block that contains the data, and the last piece of information in each block contains a null/not null rowid pointer to the next row piece. In a spanned row (one inserted that is too big for a single DB_BLOCK) the pointer (usually)points to the next physical block in the DB, and it goes pretty fast. In a chained row (one where someone has done an update, and the new information put into the row does not fit into the rest of the block -- which sounds like your case) the pointer (usually) points to a block at the end of the physical table in the DB file that contains the rest of the information. And it goes very slowly. Chaining can really grow to be progressively worse, if you continually update a column who spans the two blocks, oracle will not update the first block or the last block and instead create another new block at the end of the table for those new characters. So a read of that column now takes in 3 blocks, potentially spanned over the entire datafile. In Oracle 9i we've seen some really strange behavior too... when doing an import, or direct load Oracle will actually chain a row inside of a block, and none of the analyze for chained row commands will pick it up.It still causes the slow down, but you cannot fix it. It's actually been a while since I've really been able to look at this stuff, so if anyone has any clarifications or things they want to add, please do so. Nick -Original Message-From: Larry Elkins [mailto:[EMAIL PROTECTED]]Sent: Friday, December 27, 2002 3:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Row Migration Well, yes, Iwould agree with that ;-) What we are trying to determine here in this particular case is how much or what percentage of the slowdown in the process is due to the migration of rows. We aren't ready (until we do some testing) tomake a blanket statement that row migration *alone* is the cause of the significant slowdown. In other words, I'm not willing to make a statement to the powers that be that simply increasing the pctfree is going to make things normal again until we have a chance to do some more detailed monitoring and testing. Regards,Larry G. Elkins[EMAIL PROTECTED]214.954.1781 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Anand Kumar NSent: Friday, December 27, 2002 2:09 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Row Migration yes, row migration will degrade the performance..
RE: Row Migration
So I'm doomed? ;-) Ok, so how am I going to know which block it went to, the first step towards seeing if it was relatively nearby or maximum scatter? I'm guessing I would have to dump a block and look at the placeholder or stub in the original location and see where it points (I'm assuming it has to)? Just conjecture and the first thing I would think of since I can't think of any DD view or X$ that would tell me where a row migrated from/to. And I'm not so much concerned about the extra LIO's and latching at this point since I'm focused on the impact of a row migrating during an update. And don't think we will allow migrated rows in the table (though one might make a case for eating a few migrated rows for the sake of a significantly reduced number of blocks). But over time, this sort of update *will* eventually happen to all the rows anyway, so we would be looking at the higher number of blocks somewhere down the road. But it's all irrelevant now anyway since both the staging table and it's real counterpart in the DM were both re-orged with a pctfree of 40 (found that out this morning). I'll still need to keep an eye on migrating rows, but I'm not going to allow a handful of them make us go overboard on pctfree and wasting a lot of space. Not that I'm asking you to do our work, but curious what are the things and considerations *you* would consider in building such a test case? Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jonathan Lewis Sent: Friday, December 27, 2002 2:59 PM To: Multiple recipients of list ORACLE-L Subject: Re: Row Migration When you do your testing, don't forget to keep an eye on the change in dependent logical I/O and latching. Fetching a migrated row will require an extra buffer visit to find the row data. This MAY turn into an extra disk read but at the least it IS another buffer visit, which means another hit on the cache-buffers-chains latch, and may mean further work done getting another buffered block to the correct read-consistent state. I think you'll have to model your test very carefully - it wouldn't be too hard to produce two different models with totally contradictory results - one based on the migration going to a relatively nearby block, the other based on the update and migration taking place in a way that ensures maximum scatter of the migrated row piece. The former may hide I/O problems, the latter may exaggerate the I/O problems and hide the latch issues; and in either case you may fail to emulate the read-consistency issue properly. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins 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: Row Migration
John, the $10 is on the way ;-) Right now I'm looking at the impact of rows migrating due to updates expanding the rows. So I was considering fetch row continued as opposed to analyze .. list chained rows (my first thought) before and after the update. To know how many rows migrated due to the updates, I could do a parallel fts prior to the update and record this number (or insert all the stats into a holding table). And then after the update do it again. The delta should give me the number of rows that migrated, and would probably be much faster than the analyze list chained rows (or a compute and looking at the chain_cnt) since I could use parallelism. And then was definitely looking at using this on both the staging version of the table and the production copy from a query perspective. But my entire test fell apart ;-) The table, both the staging and the real in the DM, without my knowing it was going to occur, was rebuilt with a pctfree of 40 overnight on Thursday. So I don't have a baseline to do a before and after comparison to gauge the impact of the rows migrating during the updates. It doesn't look like the process ran again after that. The person who wrote it is on vacation, and the person watching it is off on Fridays. And they are migrating that instance and domain to a new domain on a new machine this weekend, so I don't really see anything happening with this, at least not this weekend. But I did do a quick and dirty test. Slammed 1,000,000 rows into a two column table with pctfree of 0, with the second column null. Then updated the second column and got a timing on it (all the rows migrated). Then dropped, recreated, and repeated with a very high value (95) for pctfree. The update finished 4 times faster. Didn't do any detailed analysis or stats gathering -- just thought I would put together a quick and dirty. I would still like to put together a test that more realistically mimics the real case. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of John Kanagaraj Sent: Friday, December 27, 2002 12:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: Row Migration Larry, Don't want to preach to the Guru, but have you checked the values for 'table fetch continued row'? StatisticTotal per Second per Trans - table fetch by rowid 577,820,727 40,129.2 61,248.8 table fetch continued row 137,202 9.5 14.5 This when coming out of V$SESSTAT could give a good indication of number of fetches by migrated as well as chained rows for that session. You could also look at V$SESSION.MAX_WAIT for 'db file sequential read' events... Let us know what you find! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 What would you see if you were allowed to look back at your life at the end of your journey in this earth? ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins 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: Row Migration
Geez, I didn't know you could do that. Sheepishly, Jared On Friday 27 December 2002 03:38, Larry Elkins wrote: Someone asked in a back channel email if parallelism is used. The select portion of the update statement uses parallelism (though the updates themselves get serialized) through the use of an in-line join update (to avoid the second sub-query commonly used to constrain the rows being updated): Update (Select /*+ parallel hints */ From a,b Where a.key = b.key) Set a.col1 = b.col1, a.col2 = b.col2 . Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Larry Elkins Sent: Thursday, December 26, 2002 6:09 PM To: Multiple recipients of list ORACLE-L Subject: Row Migration Listers, 8.1.7.4 64 Bit Solaris Does row migration utilize DB File Sequential Reads on the table? Off the top of my head I would expect so, but I've never tested something like that before. -- 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 may also send the HELP command for other information (like subscribing).
RE: Row Migration
Title: RE: Row Migration Gaaa!! Neither did I!!! (I've been looking for a better way to do that query for years...) -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Friday, December 27, 2002 6:49 PM To: Multiple recipients of list ORACLE-L Subject: Re: Row Migration Geez, I didn't know you could do that. Sheepishly, Jared On Friday 27 December 2002 03:38, Larry Elkins wrote: Someone asked in a back channel email if parallelism is used. The select portion of the update statement uses parallelism (though the updates themselves get serialized) through the use of an in-line join update (to avoid the second sub-query commonly used to constrain the rows being updated): Update (Select /*+ parallel hints */ From a,b Where a.key = b.key) Set a.col1 = b.col1, a.col2 = b.col2 . Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Larry Elkins Sent: Thursday, December 26, 2002 6:09 PM To: Multiple recipients of list ORACLE-L Subject: Row Migration Listers, 8.1.7.4 64 Bit Solaris Does row migration utilize DB File Sequential Reads on the table? Off the top of my head I would expect so, but I've never tested something like that before. -- 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 may also send the HELP command for other information (like subscribing).
Re: Row Migration
don't feel too sheepish, I didn't know it either. Larry is the SQL guru and I bow to his knowledge. and had already saved off this email as this sort of update is something we do often and I ALWAYS have problems figuring out the correct SQL :) rachel --- Jared Still [EMAIL PROTECTED] wrote: Geez, I didn't know you could do that. Sheepishly, Jared On Friday 27 December 2002 03:38, Larry Elkins wrote: Someone asked in a back channel email if parallelism is used. The select portion of the update statement uses parallelism (though the updates themselves get serialized) through the use of an in-line join update (to avoid the second sub-query commonly used to constrain the rows being updated): Update (Select /*+ parallel hints */ From a,b Where a.key = b.key) Set a.col1 = b.col1, a.col2 = b.col2 . Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Larry Elkins Sent: Thursday, December 26, 2002 6:09 PM To: Multiple recipients of list ORACLE-L Subject: Row Migration Listers, 8.1.7.4 64 Bit Solaris Does row migration utilize DB File Sequential Reads on the table? Off the top of my head I would expect so, but I've never tested something like that before. -- 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 may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Row Migration
Actually, I first learned that trick from a Connor posting on this list (maybe around 2 or 3 years ago?) It has to conform to the same key preserved rules that updateable views do since that's what it is, just an in-line view as opposed to an actual physical view. So supposedly it's been available since 7.x when updateable views came along (and in-line views). There is an example in the Data Warehousing Guide (I think that's the one) in the 8i documentation, though the example is wrong (it omits the FROM clause). Anyway, I thought it was pretty cool the first time I saw Connor post it. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Rachel Carmichael Sent: Friday, December 27, 2002 10:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: Row Migration don't feel too sheepish, I didn't know it either. Larry is the SQL guru and I bow to his knowledge. and had already saved off this email as this sort of update is something we do often and I ALWAYS have problems figuring out the correct SQL :) rachel --- Jared Still [EMAIL PROTECTED] wrote: Geez, I didn't know you could do that. Sheepishly, Jared On Friday 27 December 2002 03:38, Larry Elkins wrote: Someone asked in a back channel email if parallelism is used. The select portion of the update statement uses parallelism (though the updates themselves get serialized) through the use of an in-line join update (to avoid the second sub-query commonly used to constrain the rows being updated): Update (Select /*+ parallel hints */ From a,b Where a.key = b.key) Set a.col1 = b.col1, a.col2 = b.col2 . -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins 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).
