This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Oct 15, 2012 2:00 PM by jgarry RSS

Analyze vs dbms_stats

BSalesRashid Explorer
Currently Being Moderated
Hi all,
I have a situation here:

We are using Oracle Rac with 3 nodes, database version 10.2.0.5 running on Oracle linux.

We have a function called func_ura, and it is called several times in a day; The query calls the function, and normally it finishes in 1 second or less.
But rarely it don't work so fast, taking about 35~40 seconds, when this happens we usually do a command:

* ANALYZE TABLE ISSR.CALENDARIO_CIERRES ESTIMATE STATISTICS SAMPLE 100 PERCENT; *

And then the function back to normal, running in a second.

It's known that Analyze stop being supported by oracle, and DBMS_STATS kind of replaced 'Analyze' command.

But daily the DBMS_STATS runs in the database and looks like it don't resolve the problem itself.

Any one know what could it be ? I thought that dbms_stats would do better then the analyze.

Thanks in advance.

Edited by: BSalesRashid on 27/09/2012 06:25
  • 1. Re: Analyze vs dbms_stats
    sb92075 Guru
    Currently Being Moderated
    BSalesRashid wrote:
    Hi all,
    I have a situation here:

    We have a function called func_ura, and it is called several times in a day; The query calls the function, and normally it finishes in 1 second or less.
    But rarely it don't work so fast, taking about 35~40 seconds, when this happens we usually do a command:

    * ANALYZE TABLE ISSR.CALENDARIO_CIERRES ESTIMATE STATISTICS SAMPLE 100 PERCENT; *

    And then the function back to normal, running in a second.

    It's known that Analyze stop being supported by oracle, and DBMS_STATS kind of replaced 'Analyze' command.

    But daily the DBMS_STATS runs in the database and looks like it don't resolve the problem itself.

    Any one know what could it be ? I thought that dbms_stats would do better then the analyze.

    Thanks in advance.
    what are actual statistic values before & after ANALYZE is run?
    In other words, which statistics change & by how much?
    What is happening to the data within ISSR.CALENDARIO_CIERRES on an hourly basis?
    AUDIT DML against ISSR.CALENDARIO_CIERRES
  • 2. Re: Analyze vs dbms_stats
    Taral Journeyer
    Currently Being Moderated
    What is your database version.
    Is your query plan changes
    what is the before and after stats look like
    How big is that table

    Without this it's not possible to answer.
  • 3. Re: Analyze vs dbms_stats
    Iordan Iotzov Expert
    Currently Being Moderated
    Another thought – it might be that your system gets better because ANALYZE invalidated the old cursor and forced a hard parse which resulted in a better execution plan. Usually this is related to bind variable peeking. It is possible that stats have nothing to do with the improvement …

    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 4. Re: Analyze vs dbms_stats
    BSalesRashid Explorer
    Currently Being Moderated
    Hi sb92075 ,
    Thanks for replying..

    How can i check the actual statistic values before and after the Analyze ?

    In the ISSR.CALENDARIO_CIERRES happens only select statements, is that what you want to know ?


    Regards,
  • 5. Re: Analyze vs dbms_stats
    BSalesRashid Explorer
    Currently Being Moderated
    Hi,

    Sorry about missing information:

    We are using Oracle Rac with 3 nodes, database version 10.2.0.5 running on Oracle linux.

    How can i check before and after stats?

    It's 76000 rows . Not a big table.

    Thanks for answering;
  • 6. Re: Analyze vs dbms_stats
    BSalesRashid Explorer
    Currently Being Moderated
    Hi,
    thanks for replying.

    How can i confirm this?

    Regards,
  • 7. Re: Analyze vs dbms_stats
    Justin Cave Oracle ACE
    Currently Being Moderated
    The most basic test would be to look at the LAST_ANALYZED and NUM_ROWS column from DBA_TABLES (there are a number of other columns that will also get populated when statistics are gathered that affect the resulting query plans as well but let's start with the most basic).

    Is the nightly job actually updating the statistics on this table (is the LAST_ANALYZED date changing)? You don't tell us what version of Oracle you are using nor do we know what parameters you are passing to DBMS_STATS so I'm guessing. If you are using a recent version of Oracle (say, 10.2 or 11.2) and you are using the default statistics gathering job without modification, you'll gather statistics on tables that the database expects are stale. That algorithm changes between versions but it's based on the fraction of rows that have changed. It is entirely possible that you have some tables that need statistics to be gathered more frequently than this simplistic algorithm expects.

    Justin
  • 8. Re: Analyze vs dbms_stats
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    It's known that Analyze stop being supported by oracle, and DBMS_STATS kind of replaced 'Analyze' command.
    ANALYZE is supported. But with this advise
    >
    Use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer:

    To use the VALIDATE or LIST CHAINED ROWS clauses

    To collect information on freelist blocks
    >
    http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_4005.htm
  • 9. Re: Analyze vs dbms_stats
    sb92075 Guru
    Currently Being Moderated
    post two EXPLAIN PLAN; one with good performance & one with bad performance
  • 10. Re: Analyze vs dbms_stats
    Mohamed Houri Pro
    Currently Being Moderated
    Hi,
    ANALYZE TABLE ISSR.CALENDARIO_CIERRES ESTIMATE STATISTICS SAMPLE 100 PERCENT; 
    Gathering statistics using analyze command instead of dbms_stats package will certainly lead to different results. Some statistics could be different leading to new execution plans and hence new response times.

    For example your queries might start using index fast full scan(IFFS) when statistics are gathered using dbms_stats because dbms_stats reports the number of leaf blocks having data in them while analyze command reports the number of leaf blocks currently in the index. And, as mentioned in Jonathan Lewis Cost Based Oracle Fundamentals book, as far as the cost of index fast full scan is dictated by the number of leaf blocks, you might suddenly start seeing the CBO preferring the use of IFFS which alter the response time.

    You should know also that the analyze command collects index statistics while the default call to dbms_stats.gather_table_stats will not gather index statistics. This is another possibility given to the CBO to choose another execution plan.

    The avg_col_length is calculated during the call to dbms_stats while it is not calculated using the analyze command. This might produce a change in an execution plan as far as this information is used by the CBO to measure the cost of sorts and hash joins.

    Another question to ask: is your table CALENDARIO_CIERRES partitioned? Things are also different in this case when using the analyze command instead of the dbms_stats package.

    It will certainly help if you post both explain plans.

    Best Regards

    Mohamed Houri
    www.hourim.wordpress.com
  • 11. Re: Analyze vs dbms_stats
    BSalesRashid Explorer
    Currently Being Moderated
    Hi, i just answered above, now i will edit the first post to don't cause this problem again.

    We are using Oracle Rac with 3 nodes, database version 10.2.0.5 running on Oracle linux.

    Well, i will have to wait the issue happen again to check this LAST_ANALYZED and NUM_ROWS columns before and after the Analyze.

    As soon the issue happens, i will check the columns you told me, do the Analyze and check after.

    Thanks!
  • 12. Re: Analyze vs dbms_stats
    BSalesRashid Explorer
    Currently Being Moderated
    Hi,
    As soon as the problem happens again, i will make sure to take 2 explain plan's. One for before the analyze and other after the analyze.

    Thanks.
  • 13. Re: Analyze vs dbms_stats
    BSalesRashid Explorer
    Currently Being Moderated
    Hi,
    The table ISSR.CALENDARIO_CIERRES isn't partitioned.

    As soon as the issue happens again i will take the 2 explain plan's.

    Thanks for the explanation, i will have to get some more information about this.
  • 14. Re: Analyze vs dbms_stats
    Iordan Iotzov Expert
    Currently Being Moderated
    When the slowness comes, try to flush the offending statement by using DBMS_SHARED_POOL.PURGE or by flushing the shared pool / a significantly more intrusive way/. If the offending SQL gets a better execution plan and the problem disappears, then the issue is not with the stats, but rather with bind peeking.

    Iordan Iotzov
    http://iiotzov.wordpress.com/
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points