RE: Row Migration/Rowchaining

From: <maheswara.rao_at_ubs.com>
Date: Fri, 11 Apr 2008 15:33:49 -0400
Message-ID: <2FCB63BD2F527141842CFB7A9F4C645303E904EF@NLNHC012PEX.ubsamericas.net>

Thank you Dan and Joe.

I checked dba_tables. The query result shows 101 rows.

From v$sysstat, I am getting 2,327,441.

Does v$sysstat contains other info when query for 'table fetch continued row'?

Please see below queries.

  1 SELECT owner, table_name, chain_cnt   2 FROM dba_tables
  3* WHERE chain_cnt > 0
SQL> /

OWNER                          TABLE_NAME                      CHAIN_CNT


------------------------------ ------------------------------ ----------

REPORTER                       REP_AUDIT_SEVERITY                     18
REPORTER                       REP_AUDIT_ACK                          83

• = = =

SQL> SELECT name, value

       FROM v$sysstat
       WHERE name = 'table fetch continued row';

NAME                               VALUE


---------------------------------- --------

table fetch continued row          2327441



________________________________________

From: Dan Norris [mailto:dannorris_at_dannorris.com] Sent: Friday, April 11, 2008 3:23 PM
To: TESTAJ3_at_nationwide.com
Cc: Rao, Maheswara; oracle-l_at_freelists.org Subject: Re: Row Migration/Rowchaining

For the purposes of statistics, chained and migrated rows are the same. So, chain_cnt would show a count of all chained rows which includes all migrated rows.

You could also do:

analyze table <tablename> list chained rows;

which would put the rows into the table INVALID_ROWS which is created by the OH/rdbms/admin/utlvalid.sql script (you'd have to run that script to create the table first).

I believe that you can do the analyze on any table and statistics will not be computed, but just the chained rows will be listed into the INVALID_ROWS table. Of course, a FTS will be required to find the chained rows, so beware of the I/O impact of running such a command.

Dan

TESTAJ3_at_nationwide.com wrote:

This will show you the table for chained rows, not sure if it covers migrated or not, anyone else?

 select owner, table_name, chain_cnt from dba_tables where chain_cnt > 0;

Please do not transmit orders or instructions regarding a UBS account by e-mail. The information provided in this e-mail or any attachments is not an official transaction confirmation or account statement. For your protection, do not include account numbers, Social Security numbers, credit card numbers, passwords or other non-public information in your e-mail. Because the information contained in this message may be privileged, confidential, proprietary or otherwise protected from disclosure, please notify us immediately by replying to this message and deleting it from your computer if you have received this communication in error. Thank you.

UBS Financial Services Inc.

UBS International Inc.

UBS Financial Services Incorporated of Puerto Rico
--

http://www.freelists.org/webpage/oracle-l Received on Fri Apr 11 2008 - 14:33:49 CDT