Hello,
We are running Oracle 10.2.0.4 and I have not been able to find any documentation describing how DBMS_STATS.GATHER_TABLE_STATS is supposed to behave when it is invoked from multiple sessions at the same time for the same table or partition. I have run a few experiments and am getting some conflicting feedback. Can anyone point me to any documentation on this topic or shed some more light on it for me?
-----
Experiment #1:
Kicked off the same stats gather for the same table partition in 4 different sessions at approximately the same time. All 4 completed successfully which I interpreted as meaning stats were gathered 4 times but when I review DBA_TAB_STATS_HISTORY I only see 1 new row for the table partition. So were stats gathered 4 times and the recording of this in DBA_TAB_STATS_HISTORY has a bug or did Oracle dismiss/ignore 3 of the stats gathering requests because it recognized a stats gather was already in progress for this table partition? The stats gather I ran in all sessions looked like this:
DBMS_STATS.GATHER_TABLE_STATS (
ownname => '<schema name>',
tabname => '<table name>',
partname => '<part name>',
granularity => 'PARTITION',
estimate_percent => 1,
degree => 1,
cascade => TRUE,
no_invalidate => FALSE
);
-----
Experiment #2:
Kicked off a stats gather in session #1 forcing estimate_percent to 100 as below:
DBMS_STATS.GATHER_TABLE_STATS (
ownname => '<schema name>',
tabname => '<table name>',
partname => '<part name>',
granularity => 'PARTITION',
estimate_percent => 100,
degree => 1,
cascade => TRUE,
no_invalidate => FALSE
);
In session #2 I deleted 1 row from the partition and then committed this change and then kicked off the same stats gather but gave it a degree of 4 so it would run faster than session #1 as seen below:
DBMS_STATS.GATHER_TABLE_STATS (
ownname => '<schema name>',
tabname => '<table name>',
partname => '<part name>',
granularity => 'PARTITION',
estimate_percent => 100,
degree => 4,
cascade => TRUE,
no_invalidate => FALSE
);
After session #2 completed (session #1 was still running) DBA_TAB_STATISTICS showed an updated LAST_ANALYZED time and NUM_ROWS reflecting the deleted row as expected. I also saw 1 new row in DBA_TAB_STATS_HISTORY as I would expect.
After session #1 completed DBA_TAB_STATISTICS showed the LAST_ANALYZED time was updated again and NUM_ROWS no longer reflected the deleted row. Strangely there were no new rows in DBA_TAB_STATS_HISTORY.
My interpretation of this result was that:
- Oracle is not ignoring my stat gather requests
- Oracle is not serializing them
- Oracle seems to be honoring them based on a "last out" wins basis
- There seems to be bug in recording to DBA_TAB_STATS_HISTORY
-----
Experiment #3:
Same as #1 but I forced the stats gather to run serially via calls to DBMS_LOCK. When I ran it this way DBA_TAB_STATS_HISTORY showed 4 new rows as expected.
-----
In case you are wondering why I have been investigating this behavior it is because I have set up manual stats gathering to run just after bulk loads to a table partition and just prior to using that table partition in a subsequent query. This technique works fine 99% of the time but 1% of the time 2 bulk loads running in different sessions are loaded into the same table partition at approximately the same time. A stats gather runs after each of these bulk loads but sometimes key statistics (e.g., histograms) seem to be "lost" and the subsequent query hangs because of a poor execution plan based on bad stats.
Based on my experiments I can only think of the following options...none of which are all that appealing:
1) Force the stats gathers to serialize via DBMS_LOCK
2) Consider removing histograms from the table all together and hope Oracle can get a good plan without them under the assumption those are the only key stats I am losing in this particular scenario.
3) Completely freeze a working set of stats and use/copy those to all future partitions. Only change frozen stats via future releases (i.e. treat a stats change like a code release)
4) Note I cannot just copy the EOD stats from the previous day's partition forward because the data can change significantly from day to day (e.g., holidays).
If you have read this far thanks for looking and if nothing else I think it does present an interesting scenario.
Edited by: user10168028 on Nov 10, 2010 8:08 AM