-
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
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
I remember when this place was cool.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
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
|