Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 3, 2003

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

By James Koopmann

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:


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:

  FROM v$statistics_level
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

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM