Fragmentation in Rule Based Optimizer !!

[dbasupuser]

Can someone tell me how to check for Fragmented Tables/Indexes for RULE Based Optimizer. Even though Oracle Tells that Locally managed tables/indexes doesn't get fragmented I don't agree. I have had situations when I see some query doesn't perform well and after I perform either move or export/import etc.. on the specific table or Index it works fine. Since my Database is in RULE based as per the requirement from the Application I have not been able to diagnose which table/index is fragmented. Can someone help me with a procedure/process or script to distinguish Fragmented objects.

[slimdave]

Maybe because the move/rebuild has cached the blocks that the query is going to read?

[davey23uk]

or your pctfree settings are not so good so you are migrating a number of rows increasing your I/O.

Whats RULE got to do with finding fragmented objects anyway?

[dbasupuser]

Originally posted by davey23uk
Whats RULE got to do with finding fragmented objects anyway?

For COST Based Optimizer.

1.) analyze index index_name validate structure
2.) select name NAME,
(br_rows_len*100)/(br_blk_len*br_blks) BRANCH_UTILIZATION,
((lf_rows_len - del_lf_rows_len)*100)/ (lf_blk_len*lf_blks) LEAF_UTILIZATI,
decode (sign(ceil(log(br_blk_len/(br_rows_len/br_rows),
lf_blk_len/((lf_rows_len - del_lf_rows_len)/(lf_rows - del_lf_rows)
+1 - height), -1,'YES','NO') CAN_REDUCE_LEVEL
from index_stats;


This will give you the candidate index for defragmentation.

[jmodic]

Originally posted by dbasupuser
For COST Based Optimizer.

1.) analyze index index_name validate structure
2.) select name NAME,
(br_rows_len*100)/(br_blk_len*br_blks) BRANCH_UTILIZATION,
((lf_rows_len - del_lf_rows_len)*100)/ (lf_blk_len*lf_blks) LEAF_UTILIZATI,
decode (sign(ceil(log(br_blk_len/(br_rows_len/br_rows),
lf_blk_len/((lf_rows_len - del_lf_rows_len)/(lf_rows - del_lf_rows)
+1 - height), -1,'YES','NO') CAN_REDUCE_LEVEL
from index_stats;


This will give you the candidate index for defragmentation.

And what has all this to do with COST/RULE based optimizer?

Morover, I suggest you to search the web for some decent whitepapers dealing with "Oracle fragmentation mith". You'll learn a lot from them and you'll never again ask such a silly question about segment fragmentation again.

[ngdh]

Why don't you run a statspack for the bad and the good and then compare to see why you're getting the "ugly".

No point in blaming fragmentation if you can't prove it.