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'? > > 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 ** -- 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).
