DBMS_STATS and frag prob

Hi

We are having 9idb and optimizer_mode is CHOOSE,

Question:1
-----------
To analyze the objects whether i have to uses analyze table/index ... compute statistics or DBMS_STATS. What is the difference.

Question 2
-----------

I have read some article, LMT tablespaces doesnt have fragmentation.However when i check in dba_free_space
using qry
SELECT tablespace_name,COUNT(*) ,SUM(bytes) as total,max(bytes) as largest
FROM dba_free_space
GROUP BY tablespace_name;

I am getting the following output.

Tablespace count sum(bytes) max(bytes)
INDEXM1 10 545193984 427753472
INDEXM2 3 2732982272 2174746624
INDM3 100 1779171328 144703488
INDM4 4 1255342080 973012992

can any one clear my doubts.

Thanks

use dbms_stats
you dont have fragmentation if you use LMT's

Hi

I have analyzed the all the index and tables thru dbms_stats by giving
EXEC DBMS_STATS.gather_index_stats and
EXEC DBMS_STATS.gather_table_stats.

As I just analyzed the index I wanted to know what is criteria for rebuild the index.

THanks

dont rebuild them, thats a good criteria

Quote:

---

Originally Posted by davey23uk

dont rebuild them, thats a good criteria

---

Agreed
BUT
If you have bitmap indexes on heavily updated tables you want to keep an eye on them. If they start growing out-of-control then rebuild them.

PAVB, I am not very experienced with the bitmap indexes, can you pls. explain why they starts growing?

Thanks

Not rebuilding as a blanket criteria is not a good idea. On paper it looks good but the reality, as PAVB pointed out is that heavily updated tables will see indexes grow out of control. I have 3 prod boxes that have this issue and when rebuilding saves 50% of the tablespace, well, it is hard to argue that you should not rebuild cause Tom says so.

Quote:

---

Originally Posted by Bore

PAVB, I am not very experienced with the bitmap indexes, can you pls. explain why they starts growing?

---

Let me quote Jonathan Lewis (http://jonathanlewis.wordpress.com/2...ing-in-action/) on this one.

"it is fairly common for bitmap indexes to grow dramatically when the underlying tables are subject to DML. A single bitmap index “chunk” can be as large as 3,900 bytes (in an 8K block), and changing the value of the indexed column for just one row of the table makes Oracle clone two such chunks - which can cause two leaf block splits, which sometimes causes two branch block splits."

Imagine this scenario, taken for a dw prod environment.

-- You have a partioned bitmap index bitmap_idx on table_a.
-- Before your daily ETL process you check the size of bitmap_idx:Mar2007 partition, it could be something like 15 Megs.
-- After your daily ETL process which is heavily hitting Mar2007 partitions you go back to check the size of bitmap_idx:Mar2007... don't be surprised if now is as big as 1.5 Gig
-- After rebuilding it goes back to 15 Megs.

We actually have a process after ETL looking for bitmap index partitions larger than 1 Gig and, rebuilding them as they are found.

Why you want to use bitmap indexes if they are such a pain? 'cause they are great for Oracle's query-rewrite functionality which gives you better performance in a dw environment. They are like antibiotics, use them just when actually needed.

Quote:

---

Originally Posted by dba_45

Hi

I have analyzed the all the index and tables thru dbms_stats by giving
EXEC DBMS_STATS.gather_index_stats and
EXEC DBMS_STATS.gather_table_stats.

As I just analyzed the index I wanted to know what is criteria for rebuild the index.

THanks

---

Please read my article:
http://www.oracleact.com/papers/inde...lock_frag.html