RE: 10g Automatic Statistics Gathering
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 6 Jul 2005 16:44:34 -0400
Being that typical is the default I do not think you have to worry about an
extra licensing fee for setting the statistics gathering level. Some of the
products that use the statistics might be another story. But you can collect
them for the cost of overhead.
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Vajrala, Madhusudana R.
Sent: Wednesday, July 06, 2005 4:19 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: 10g Automatic Statistics Gathering
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Barr, Stephen
Sent: Tuesday, July 05, 2005 11:07 AM
To: Radoulov, Dimitre; Lex de Haan; Barr, Stephen; oracle-l@xxxxxxxxxxxxx
Subject: RE: 10g Automatic Statistics Gathering
Thanks all! Much appreciated.
-----Original Message-----
From: Radoulov, Dimitre [mailto:cichomitiko@xxxxxxxxx]
Sent: 05 July 2005 17:02
To: Lex de Haan; Stephen.Barr@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
