Implications of Setting Oracle9iR2's Statistics Collection Level - Page 3

Reset the timed_statistics parameter within SPFILE

Just by pure deduction, the fact that timed_statistics is TRUE and statistics_level is basic, you can infer that there is an entry in the spfile. In order for the timed_statistics parameter to be under the control of the STATISTICS_LEVEL parameter, you need to get rid of the parameter from within the SPFILE. To do this issue the following command:

ALTER SYSTEM RESET timed_statistics  SCOPE=SPFILE SID='*';

Bounce the Database

In order for the new setting of timed_statistics to take effect, you will need to bounce the database. This is not always the best thing to do and you are probably cringing as I would. Since timed_statistics is a dynamic parameter and your real intent is ultimately to have it set to false and then controlled by the STATISTICS_LEVEL parameter, just issue the following command to turn off timed_statistics:

ALTER SYSTEM SET timed_statistics=FALSE;

Verify that timed_statistics is under STATISTICS_LEVEL control

After the bounce of the database you can verify that timed_statistics is under the new STATISTICS_LEVEL parameter control by the following SQL:

SELECT STATISTICS_NAME,
       SYSTEM_STATUS
  FROM v$statistics_level
 ORDER BY 1;
                               System
Statistics Name                Status
------------------------------ ----------
Buffer Cache Advice            DISABLED
MTTR Advice                    DISABLED
PGA Advice                     DISABLED
Plan Execution Statistics      DISABLED
Segment Level Statistics       DISABLED
Shared Pool Advice             DISABLED
Timed OS Statistics            DISABLED
Timed Statistics               DISABLED

It's Better

Is this really better than setting individual parameters for statistical collection? It seems a bit easier, but also seems that we give up some more control. I have always liked controlling every small bit of information I could get out of Oracle. However, I am learning that sometimes, less control is better. I have said this once before in some other life of mine. When Oracle takes control from us as DBAs, to actively control information flow within the database, they are telling us that they can do it better and at a lower system cost. To me this is great news, just so long as their confidence in themselves pans out.

» See All Articles by Columnist James Koopmann