RE: 10g Automatic Statistics Gathering

From: Vajrala, Madhusudana R. <Vajrala.Madhusudana_at_mayo.edu>
Date: Wed, 6 Jul 2005 15:19:22 -0500
Message-Id: <12DA0EDD076B1141BF31592CAB7BA05E0312C2D4@excsrv55.mayo.edu>

Below are the differences W or W/O stats enabled on the 10g Database:

And I think when you set statistics_level to TYPICAL/ALL, there will be some extra license fee.

#_______________________________________________________
col statistics_name      for a30 head "Statistics Name"
col session_status       for a10 head "Session|Status"
col system_status        for a10 head "System|Status"
col activation_level     for a10 head "Activation|Level"
col session_settable     for a10 head "Session|Settable"

SELECT STATISTICS_NAME,
       SESSION_STATUS,
       SYSTEM_STATUS,
       ACTIVATION_LEVEL,
       SESSION_SETTABLE

FROM v$statistics_level
ORDER BY 1
/

#_______________________________________________________

W/ STATISTICS_LEVEL=TYPICAL ( $$$$ )

#_______________________________________________________________________

                               Session    System     Activation Session
Statistics Name                Status     Status     Level      Settable
------------------------------ ---------- ---------- ----------
----------
Active Session History         ENABLED    ENABLED    TYPICAL    NO
Bind Data Capture              ENABLED    ENABLED    TYPICAL    NO
Buffer Cache Advice            ENABLED    ENABLED    TYPICAL    NO
Cache Stats Monitor            ENABLED    ENABLED    TYPICAL    NO
Global Cache Statistics        ENABLED    ENABLED    TYPICAL    NO
Longops Statistics             ENABLED    ENABLED    TYPICAL    NO
MTTR Advice                    ENABLED    ENABLED    TYPICAL    NO
Modification Monitoring        ENABLED    ENABLED    TYPICAL    NO
PGA Advice                     ENABLED    ENABLED    TYPICAL    NO
Plan Execution Statistics      DISABLED   DISABLED   ALL        YES
Segment Level Statistics       ENABLED    ENABLED    TYPICAL    NO
Shared Pool Advice             ENABLED    ENABLED    TYPICAL    NO
Threshold-based Alerts         ENABLED    ENABLED    TYPICAL    NO
Timed OS Statistics            DISABLED   DISABLED   ALL        YES
Timed Statistics               ENABLED    ENABLED    TYPICAL    YES
Ultrafast Latch Statistics     ENABLED    ENABLED    TYPICAL    NO

Undo Advisor, Alerts and Fast ENABLED ENABLED TYPICAL NO Ramp up

W/ STATISTICS_LEVEL=BASIC

#_______________________________________________________________________

Statistics Name                Status     Status     Level      Settable
------------------------------ ---------- ---------- ----------
----------
Active Session History         DISABLED   DISABLED   TYPICAL    NO
Bind Data Capture              DISABLED   DISABLED   TYPICAL    NO
Buffer Cache Advice            DISABLED   DISABLED   TYPICAL    NO
Cache Stats Monitor            DISABLED   DISABLED   TYPICAL    NO
Global Cache Statistics        DISABLED   DISABLED   TYPICAL    NO
Longops Statistics             DISABLED   DISABLED   TYPICAL    NO
MTTR Advice                    DISABLED   DISABLED   TYPICAL    NO
Modification Monitoring        DISABLED   DISABLED   TYPICAL    NO
PGA Advice                     DISABLED   DISABLED   TYPICAL    NO
Plan Execution Statistics      DISABLED   DISABLED   ALL        YES
Segment Level Statistics       DISABLED   DISABLED   TYPICAL    NO
Shared Pool Advice             DISABLED   DISABLED   TYPICAL    NO
Threshold-based Alerts         DISABLED   DISABLED   TYPICAL    NO
Timed OS Statistics            DISABLED   DISABLED   ALL        YES
Timed Statistics               ENABLED    ENABLED    TYPICAL    YES
Ultrafast Latch Statistics     DISABLED   DISABLED   TYPICAL    NO

Undo Advisor, Alerts and Fast DISABLED DISABLED TYPICAL NO Ramp up

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Barr, Stephen Sent: Tuesday, July 05, 2005 11:07 AM
To: Radoulov, Dimitre; Lex de Haan; Barr, Stephen; oracle-l_at_freelists.org Subject: RE: 10g Automatic Statistics Gathering

Thanks all! Much appreciated.

-----Original Message-----
From: Radoulov, Dimitre [mailto:cichomitiko_at_gmail.com] Sent: 05 July 2005 17:02
To: Lex de Haan; Stephen.Barr_at_BSkyB.com; oracle-l_at_freelists.org Subject: Re: 10g Automatic Statistics Gathering

Thanks Lex!
So in situations when you want to disable automatic statistics gathering, the most direct approach is to disable the GATHER_STATS_JOB as follows(Concepts Guide 10G r1):

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'); END;
/

Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including: Automatic Workload Repository (AWR) Snapshots Automatic Database Diagnostic Monitor (ADDM) All server-generated alerts Automatic SGA Memory Management Automatic optimizer statistics collection Object level statistics End to End Application Tracing (V$CLIENT_STATS) Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
Service level statistics
Buffer cache advisory
MTTR advisory
Shared pool sizing advisory
Segment level statistics
PGA Target advisory
Timed statistics
Monitoring of statistics

Note:
Oracle strongly recommends that you do not disable these important features and functionality.

Cheers
Dimitre Radoulov

ETNØTEAM

Information in this email may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 06 2005 - 15:21:42 CDT