Larry, Don't want to preach to the Guru, but have you checked the values for 'table fetch continued row'?
Statistic Total 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 ** > -----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).
