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
elkinsl_at_flash.net
214.954.1781

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

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: ListGuru_at_fatcity.com (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).