Testing a No-statistics Environment: Part II
Posted by David Aldridge on 2008-05-07
Following on from the previous post, a little glitch in the plans: the instance appeared to crash late last night during the load. Hopefully not some exotic bug associated with dynamic sampling.
I modified the delete-and-lock strategy yesterday after remembering that we have a number of indexes that are created with the “compute statistics” option. Since DBMS_STATS.LOCK_TABLE_STATS is intended to prevent the modification of any statistics associated with a table I ran a quick test to see if compute statistics can be specified for an index created against a statistics-locked table, and indeed it cannot.
SQL Error: ORA-38029: object statistics are locked
38029. 00000 - “object statistics are locked”
*Cause: An attept was made to modify optimizer statistics of the object.
*Action: Unlock statistics with the DBMS_STATS.UNLOCK_TABLE_STATS procedure
on base table(s). Retry the operation if it is okay to update statistics.
Well, it was probably safer not to anyway for a first run.
Now, whatever happened to that database instance?
