This content has been marked as final. Show 18 replies
BSalesRashid wrote:what are actual statistic values before & after ANALYZE is run?
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.
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
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 …
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.
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
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.
ANALYZE TABLE ISSR.CALENDARIO_CIERRES ESTIMATE STATISTICS SAMPLE 100 PERCENT;
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.
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.
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.