user628400 wrote:In Oracle simply updating the statistics doesn't resolve the issue if the corresponding execution plan didn't change. It's very likely that by gathering the statistics the execution plan changed and therefore the execution was more efficient.
Gather stats is run by Oracle every night. Everyone knows that this is default job. Now today only thing that changed was that we got around 4000 more records in the table that the query was slow against. When we ran the explain on that query we saw it taking Index path but was still slow. So we ran update statistics and that resolved the issue.
Now our DBA recommended that we turn off that default oracle GATHER stats job because that caused Oracle to generate some new stats and because of those stats query was slow (not really convinced with this though). Our DBA recommended using profiler instead, which I don't really understand how that will resolve the issue.The explanation of your DBA is not sound, gathering new statistics seems to have solved the issue (although you might have other side effects like that the execution plan was still cached in the shared pool and got now invalidated by updating the statistics, because Oracle didn't update the statistics in the night if the table was not determined to be "stale"). So the question remains what was the actual root cause of your issue.
So what I remember seeing is that even though explain plan showed the query taking index path, it still was slow until we ran update stats. Only reason I can think of is that it has to do lot of read aheads in absense of additional Btree nodes.It's a pity that you don't have the EXPLAIN PLAN from before and after updating the statistics, and as already mentioned, the EXPLAIN PLAN doesn't necessarily reflect the actual execution plan used.
user628400 wrote:- Are you sure that the plan you've looked at corresponds to the actual execution?
We had this problem again today. We use DISPLAY AWR to get the query plan and the query plan looked ok.
tag before and after the output to format it in
. Use the "Quote" button in the message editor to see how I used the \
But the buffer gets were very very high. I don't completely understand why buffer gets would be high when plan shows that it's using index.Which "buffer gets" are you referring to? The overall statistics from an AWR report?
We ran update stats and flushed shared pool and performance got back to normal.If you were required to flush the shared pool, then the "bad" performing version of the statement should still have been cached in the shared pool. Why did then use DISPLAY_AWR? DISPLAY_CURSOR should be able to show you the cached execution plan.