0

Here's what happens:

1) I run some query that is not optimized very well - it executes in 16 seconds (averaged stable number after 10 test runs); the execution plan says - dynamic sampling used for this statement (level=2), so it does not have stats for this query to use.

2) I run exec DBMS_STATS.GATHER_DATABASE_STATS

3) I run the same query again - it executes in 46 seconds (again average number) and doesn't get any lower; this time the execution plan does not say - dynamic sampling used for this statement (level=2), so it has stats for this query to use.

4) I pretend to freak out because the query is performing longer than without the stats, so I try to revert the issue by running exec DBMS_STATS.DELETE_DATABASE_STATS; exec DBMS_STATS.DELETE_DATABASE_STATS(NULL, NULL, NULL, DBMS_STATS.AUTO_INVALIDATE, 'ALL', TRUE); or for older versions of Oracle SQL server: exec DBMS_STATS.DELETE_DATABASE_STATS(NULL, NULL, NULL, DBMS_STATS.AUTO_INVALIDATE, TRUE);

5) I run the same query again - no luck. Although the execution plan says - dynamic sampling used for this statement'(level=2) again, still the query executes in 46 seconds and no less. The execution plan differs both from 1 and 3; so clearly Oracle hasn't cleared some stats away.

6) I give up and run Oracle's Restore Database tool. After that, I'm at point 1. again and can reproduce this scenario reliably again and again.

All the tests are done through Oracle SQL Developer connected to a local Oracle Database 11gR2 Express Edition for Windows x64 (on Windows 10) instance, running on SSD, i7 CPU, 16GB RAM. There are no other systems using this database during the tests.

The question is: how to fully revert the Oracle Database from the harm done by recklessly executing exec DBMS_STATS.GATHER_DATABASE_STATS, if I don't want to fully restore the database from a backup?

Am I missing some parameters for DELETE_DATABASE_STATS or is there something else going on and the database gets somehow rearranged after running the stats once, so the execution planner cannot revert to the very first plan any more?

JustAMartin
  • 13,165
  • 18
  • 99
  • 183
  • A few things: 1. The presence of dynamic sampling does not mean there are no stats. 2. In order to tune this query, you need to determine the root cause of the problem. The best tool for this is a SQL Monitor report. – BobC Nov 01 '18 at 02:16
  • @BobC - 1. I was afraid that this might be the case. Then how to correctly destroy all the stats and get back to the initial performance? 2. Tuning the query is not relevant for this case - for now, I just want to have some way to start fresh without using backup. – JustAMartin Nov 01 '18 at 07:26
  • You are making an assumption that destroying the stats will get you back to where you were. I would not make that assumption. Take a look in dba_tab_stats_history to see what stats were in place at the time. Also not that you are gathering *database* stats - ie *all* the tables in the database, not just the ones used in your query. – BobC Nov 02 '18 at 21:53
  • @BobC - it is a local instance of Oracle XE and this specific query is the only one executed during the scenario I described. No other connections exist. The average query execution time stays around 16 seconds, no matter how often and at what time I execute it, until I run the stats gathering, after which I cannot get back to the initial state unless I do full restore from backup. So, the stats is the only thing that has changed between 1) and 3), that's why I assumed that reverting the stats would get me back to 1). – JustAMartin Nov 03 '18 at 19:10
  • None of those things negate my previous comment. You may still have had other stats in place previously. By default, stats are gathered during the maintenance window. So there are a number of variables in play. As I said above, dba_tab_stats_history will help to see what the stats were like previously. Having said that, I would be curious to understand why current stats are causing a regression. – BobC Nov 04 '18 at 04:24
  • Thanks, I will look into dba_tab_stats_history. Not sure about the maintenance window - if it would kick in at some time, I should have seen the slowdown just by waiting for some time after 1). But I always was repeating this experiment rapidly, in a few minutes or so, and it always yielded the same results of "exec DBMS_STATS.GATHER_DATABASE_STATS" seemingly causing regression. As to why this happens - it might be that in 1) this query is being run with automatic OR expansion but after the exec stats call, it reverts to using OR because there was an index missing to avoid full table scan. – JustAMartin Nov 04 '18 at 18:46

0 Answers0