Solved

dbms_stats vs analyze table

Posted on 2014-02-19
7
1,584 Views
Last Modified: 2014-04-08
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_stats with setting Cascade= True would be better.

However, I am not sure about this. Could someone answer..

Quick response is appreciated
0
Comment
Question by:d27m11y
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 23

Expert Comment

by:David
ID: 39871861
In 10g, ANALYZE offers value in data collection not related to the cost based optimizer (CBO), and DBMS_STATS is encouraged.  In 11g, ANALYZE is deprecated.

For another source see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1632603200346280697.
0
 

Author Comment

by:d27m11y
ID: 39910798
Can you please answer my question when Indexes exist on a table
0
 
LVL 23

Accepted Solution

by:
David earned 500 total points
ID: 39911164
Thanks for the follow-up.  I happened to learn something this afternoon about the CBO that I should have known -- and will try to use the next time I interview someone :)

I tend to get too literal about some things, so this may sound like I've missed the point, again.  But your answer is certainly affected by which RDBMS version is in question, as the command syntax changes.  ANALYZE in 11g is not used for the data dictionary (tables), but rather to validate the index structure.  According to my source, however, cascade may be either COMPLETE or FAST, but your example "TRUE" is illegal syntax.  

If you're thinking of DBMS_STATS, then the correct syntax seems to be ... no_invalidate=TRUE.  Again from the docs, "This parameter specifies when dependent cursors cached in the library cache will get invalidated when statistics are gathered on referenced tables or indexes....specify when to invalidate dependent cursors i.e. cursors cached in the library cache area of the shared pool which reference a table, index, column or fixed object whose statistics are modified by the procedure call."

Now, I'm likely to be doing this all wrong, but all the versions between 7 and 12 can run together in my memory.  Usually I'd simply disable indexing from the table(s) during such a data load, then rebuild the index(es) on completion.

If you cannot disable or drop the existing indexes, you might be loading these rows in archivelog mode, meaning you're generating boo-koodles of redo and archive logs.  

Lastly, which type of indexes, and their skew, are factors.  I hope this helps more than it confuses.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:d27m11y
ID: 39911354
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..
0
 
LVL 23

Expert Comment

by:David
ID: 39912669
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.
0
 

Author Closing Comment

by:d27m11y
ID: 39986944
Helps me to resolve the issue
0
 
LVL 23

Expert Comment

by:David
ID: 39987009
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-
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question