OraFAQ Forum: Performance Tuning » Row Chaining issue

Home » RDBMS Server » Performance Tuning » Row Chaining issue

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Row Chaining issue [message #232977]

Tue, 24 April 2007 06:18

Brayan
Messages: 315
Registered: June 2002

Senior Member

Hi,

Whenever I run the following command it populates records into CHAINED_ROWS.

ANALYZE table MIRROR_BOI_CREDIT_LINES LIST CHAINED ROWS INTO CHAINED_ROWS;

But, When I run the following USER_TABLES.CHAIN_CNT remains as 0.

GATHER_TABLE_STATS(ownname=>'OWB_TARGET',
		tabname=>'MIRROR_BOI_CREDIT_LINES',
		cascade=>TRUE,
		granularity => 'DEFAULT'

Can anybody explain why this mismatch, or I'm doing something wrong.

Brayan.

Report message to a moderator

Re: Row Chaining issue [message #232985 is a reply to message #232977]

Tue, 24 April 2007 06:28

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Did you run these in the same sequence? (First analyze and then dbms_stats ?).
DBMS_STATS will not update the chain_cnt. Only ANALAYZE will.

Report message to a moderator

Re: Row Chaining issue [message #232987 is a reply to message #232977]

Tue, 24 April 2007 06:29

Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

GATHER_TABLE_STATS does not check chained rows as it is useless for optimizer.

Regards
Michel

Report message to a moderator

Re: Row Chaining issue [message #232990 is a reply to message #232977]

Tue, 24 April 2007 06:36

Brayan
Messages: 315
Registered: June 2002

Senior Member

Hi,

Thanks michel.

That means ANALYZE is not entirely modified with GATHER_..STATS.
And, if I want to check row_chaining/Migration, I have to use "ALTER TABLE...COMPUTE STATISTICS"

Regards,
Brayan.

Report message to a moderator

Re: Row Chaining issue [message #232994 is a reply to message #232990]

Tue, 24 April 2007 06:41

Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No you have to use the statement you posted in your first message (list chained rows).
Don't use "compute statistics" it will mess up the statistics gathered with dbms_stats.

Regards
Michel

Report message to a moderator

Re: Row Chaining issue [message #233006 is a reply to message #232977]

Tue, 24 April 2007 06:53

Brayan
Messages: 315
Registered: June 2002

Senior Member

Michel,

But,
ANALYZE table ....LIST CHAINED ROWS INTO CHAINED_ROWS;

will not update USER_TABLES.CHAIN_CNT.

Then, CHAIN_CNT is just for backward compatibility.

Brayan.

Report message to a moderator

Re: Row Chaining issue [message #233019 is a reply to message #233006]

Tue, 24 April 2007 07:07

Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Remove "into chained_rows" if you just want CHAIN_CNT to be updated.

I don't understand your last part. CHAIN_CNT is not just for backward compatibility.

Regards
Michel

Report message to a moderator

Re: Row Chaining issue [message #233166 is a reply to message #232977]

Wed, 25 April 2007 01:33

Brayan
Messages: 315
Registered: June 2002

Senior Member

Hi Michel,

SQL> select count(*) from chained_rows where table_name = 'MIRROR_BOI_CREDIT_LINES';

  COUNT(*)
----------
         0

SQL> select chain_cnt from user_tables where table_name = 'MIRROR_BOI_CREDIT_LINES';

 CHAIN_CNT
----------
         0

SQL> ANALYZE table MIRROR_BOI_CREDIT_LINES LIST CHAINED ROWS;

Table analyzed.

SQL> select count(*) from chained_rows where table_name = 'MIRROR_BOI_CREDIT_LINES';

  COUNT(*)
----------
      4571

SQL> select chain_cnt from user_tables where table_name = 'MIRROR_BOI_CREDIT_LINES';

 CHAIN_CNT
----------
         0

SQL>

Even after ignoring clause "INTO CHAINED_ROWS", USER_TABLES.CHAIN_CNT is not populated.

Brayan.

Report message to a moderator