Home » RDBMS Server » Performance Tuning » Row Chaining issue
Row Chaining issue [message #232977] Tue, 24 April 2007 06:18 Go to next message
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.
Re: Row Chaining issue [message #232985 is a reply to message #232977] Tue, 24 April 2007 06:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: Row Chaining issue [message #232987 is a reply to message #232977] Tue, 24 April 2007 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
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


Re: Row Chaining issue [message #232990 is a reply to message #232977] Tue, 24 April 2007 06:36 Go to previous messageGo to next message
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.
Re: Row Chaining issue [message #232994 is a reply to message #232990] Tue, 24 April 2007 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
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
Re: Row Chaining issue [message #233006 is a reply to message #232977] Tue, 24 April 2007 06:53 Go to previous messageGo to next message
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.
Re: Row Chaining issue [message #233019 is a reply to message #233006] Tue, 24 April 2007 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
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
Re: Row Chaining issue [message #233166 is a reply to message #232977] Wed, 25 April 2007 01:33 Go to previous messageGo to next message
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.
Re: Row Chaining issue [message #233170 is a reply to message #233166] Wed, 25 April 2007 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And your version is? (4 figures)

Regards
Michel
Re: Row Chaining issue [message #233175 is a reply to message #233170] Wed, 25 April 2007 02:12 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Oracle Version - 9.2.0.7

Brayan.
Previous Topic: Oracle RAC performance
Next Topic: How to retrieve Explain Plan in Past
Goto Forum:
  


Current Time: Tue Dec 06 12:30:14 CST 2016

Total time taken to generate the page: 0.12629 seconds