-
Dear Gurus,
Today morning i use this command in my database following query:
select count(chain_cnt) from dba_tables;
count(CHAIN_CNT)
----------------
12210
And also i use following query:
SQL> select owner, table_name, num_rows, chain_cnt,
(chain_cnt*100/num_rows) pct
,
2 empty_blocks, blocks
3 from dba_tables
4 where chain_cnt > 0
5 and owner not in ('SYS','SYSTEM')
6 ;
OWNER TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
CHAIN_CNT PCT EMPTY_BLOCKS BLOCKS
---------- ---------- ------------ ----------
BIS BIS_TARGET_LEVELS 323
2 .619195046 5 15
HR FF_FORMULAS_F 18
1 5.55555556 240 25
APPLSYS FND_COMPILED_DESCRIPTIVE_FLEXS 3763
33 .876959872 480 555
OWNER TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
CHAIN_CNT PCT EMPTY_BLOCKS BLOCKS
---------- ---------- ------------ ----------
BEN BEN_EXT_FLD 799
86 10.7634543 0 20
I got following result. How to avoid this problem?
How to avoid the chaining in my database?
How to analyze my tables? beacause now people are accesing in the
database.
When i analyze it would be effect my table or database? This following
command is
correct:
execute dbms_stats.gather_table_stats (ownname => 'APPLSYS'
, tabname => 'FND_COMPILED_DESCRIPTIVE_FLEXS'
, partname=> null
, estimate_percent => 20
, degree => 5
, cascade => true);
Can u please explain step by step,
Awaiting ur reply,
Thanks in advance,
Iyyappan.M
-
Originally posted by iyyappan
select count(chain_cnt) from dba_tables;
count(CHAIN_CNT)
----------------
12210
That is very amusing query. It tells you absolutely nothing about your row chaining. What it realy tells you is how many tables in your database has been analysed, nothing else! If you wanted to find out how many chained/migrated rows you have in your etire database, you should use:
select SUM(chain_cnt) from dba_tables;
Regarding other three tables that realy have some chained/migrated records, you could rebuild them (ALTER TABLE MOVE) with increased PCTFREE parameter to prevent future row migration. But if you are expiriencing row chaining (not row migration) then your block size is too small, you need larger database block size to eventually avoid this.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|