d27m11y d27m11y
asked on
dbms_stats vs analyze table
This was an interview question asked:
What is the major difference between analyze table versus dbms_stats.gather table stats
I answered Oracle strongly recommends using dbms_stats for gathering statistics.
Scenario :
Suppose there are 100 million of records getting inserted in a table, and there is an index on a column. What would be your option for performance tuning, would it be analyzing table stats ... rebuild index or dbms_stats. gather table stats with Cascade = True..
Which is better, why?
Since there is an index on table, which one would actually be slower. Would setting cascade=True slow down the performance.
My answer: I said using dbms_stats.gather_table_st ats with setting Cascade= True would be better.
However, I am not sure about this. Could someone answer..
Quick response is appreciated
What is the major difference between analyze table versus dbms_stats.gather table stats
I answered Oracle strongly recommends using dbms_stats for gathering statistics.
Scenario :
Suppose there are 100 million of records getting inserted in a table, and there is an index on a column. What would be your option for performance tuning, would it be analyzing table stats ... rebuild index or dbms_stats. gather table stats with Cascade = True..
Which is better, why?
Since there is an index on table, which one would actually be slower. Would setting cascade=True slow down the performance.
My answer: I said using dbms_stats.gather_table_st
However, I am not sure about this. Could someone answer..
Quick response is appreciated
ASKER
Can you please answer my question when Indexes exist on a table
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If I understand correct, analyze index syntax can be used even after 11g to validate the index structure and to my question, you suggest to drop all indexes and rebuild them..
So what should be my steps..
1. Drop all indexes
2. Perform dml operation
3. Rebuild indexes
Do I have to use analyze table compute statistics rebuild indexes here.. am still confused once I follow the above steps.
Well, to be clear when should I use analyze table compute stats rebuild indexes ..
Am sorry, it is quite confusing for me..
So what should be my steps..
1. Drop all indexes
2. Perform dml operation
3. Rebuild indexes
Do I have to use analyze table compute statistics rebuild indexes here.. am still confused once I follow the above steps.
Well, to be clear when should I use analyze table compute stats rebuild indexes ..
Am sorry, it is quite confusing for me..
I think we're in synch. To be clear about the drop, however, my experience has been to drop the primary INDEX of a table. The remaining indexes, constraints and any triggers may simply be disabled. There's some chatter about this but it involves other Oracle products and I don't want to throw you.
However, you haven't yet defined how your site inserts a hundred million rows into a table. For example, is there content to which the new rows are added? Or is the existing data truncated and laid fresh each time? Is there partitioning? Are you running from a command prompt, or Grid Control? Are other users locked out during this process? See where I'm going? Lots and lots of variables.
The function I'll end up directing you to, is dbms_index_utl.build_table _indexes. That's getting into a new topic, however, and I want to ensure this one is resolved first.
However, you haven't yet defined how your site inserts a hundred million rows into a table. For example, is there content to which the new rows are added? Or is the existing data truncated and laid fresh each time? Is there partitioning? Are you running from a command prompt, or Grid Control? Are other users locked out during this process? See where I'm going? Lots and lots of variables.
The function I'll end up directing you to, is dbms_index_utl.build_table
ASKER
Helps me to resolve the issue
Thanks for the closure, and a good question. Since your "B" grade indicates there's room for improvement, what caused the downgrade please.
http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-
http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-
For another source see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1632603200346280697.