Lyall,
I think the issue is not with how Kirsten issued the command,
but it could be that her version of Oracle does not support the
timed_statistics parameter to be modified without setting it in
the init.ora and bouncing the database. More recent versions of
Oracle (7.3.0 and above I think), does support timed_statistics
to be modified at the "instance level" using the ALTER SYSTEM
command, without having to bounce the database.
Although, there has been much debate on this topic lately, in my
experience across many versions of Oracle and across many
platforms, I have not seen any significant (measurable)
degradation. The degradation on HP-UX 10.20 needs to be
qualified with the exact version of Oracle with the installed
O-S patches for 10.20, where such a performance degradation was
experienced. Other factors such as System CPU bottlenecks
should also be considered while measuring the "degradation".
The down-side of not having timed_statistics turned on is pretty
huge, as performance-related dynamic performance views, such as
v$session_wait will have bogus information. Well why go that
far, the CPU and Elapsed numbers coming out of your tkprof
outputs will be 0 and hence useless. I am sure there are
platform-specific bugs related to this parameter, on specific
hardware platforms with specific versions of Oracle and with
specific O/S patch levels.
The necessary "bug" research needs be done on this before
implementing this or any other parameter that is "kernel
intrusive". However, I'd urge us as a group not to make
"blanket statements" about this or similar issues, as there are
too many variables out there (hardware platforms, O/S versions,
O/S patch levels, Oracle versions, System Bottlenecks etc.)
Best Regards,
Gaja
- Lyall Barbour <lbarbour_at_stanford.edu> wrote:
> I believe if you want to set timed_statistics on for everyone,
> you have to
> do that through the initialization parameters. But you can
> set it for your
> session.
>
> ALTER SESSION SET TIMED_STATISTICS=FALSE;
>
> should do the trick.
>
> Good luck,
> Lyall Barbour
>
> At 03:07 AM 8/22/00 -0800, you wrote:
> >Hi,
> >
> >I just did a :
> >
> >alter system set timed_statistics = false ;
> >
> >and got the following error :
> >
> >alter system set timed_statistics = false
> > *
> >ERROR at line 1:
> >ORA-02065: illegal option for ALTER SYSTEM
> >
> >Running Oracle 7.2.3 on HP-UX 10.20
> >Is this not a dynamic option ???
> >
> >Greets,
> >
> >Kirsten
> >
> > > -----Original Message-----
> > > From: Rachel Carmichael [SMTP:carmichr_at_hotmail.com]
> > > Sent: Tuesday, August 22, 2000 11:08 AM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: RE: Timed_statistics, True or False
> > >
> > > You lose some statistical information, useful in finding
> performance and
> > > other problems. So.... if you turn it off (and you should
> on HP-UX), and
> > > you
> > > have a problem, you would turn it on, wait for the problem
> to re-occur
> > > (and
> > > do bstat/estat and investigation) and turn it off.
> > >
> > > So in effect, if you have problems, it will take you
> somewhat longer
> > > (possibly) to resolve them
> > >
> > > In your case, I would turn it off.
> > >
> > >
> > > Rachel
> > >
> > >
> > > >From: "Weerd Kirsten, E.C. de"
> <Kirsten.deWeerd_at_Oranjewoud.nl>
> > > >Reply-To: ORACLE-L_at_fatcity.com
> > > >To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> > > >Subject: RE: Timed_statistics, True or False
> > > >Date: Mon, 21 Aug 2000 23:36:38 -0800
> > > >
> > > >I'm running Oracle 7.2.3 on HP-UX 10.20 and frankly don't
> like the idea
> > > of
> > > >a
> > > >possible performance degradation of 12% because of one
> parameter set
> > > TRUE.
> > > >
> > > >But, new as I still am, can anyone tell me : What exqctly
> do I loose when
> > > >turning this timed_statistics to FALSE ???
> > > >
> > > >Thanx for the info.
> > > >
> > > >Greets,
> > > >
> > > >
> > > >Kirsten
> > > >
> > > > > -----Original Message-----
> > > > >
> > > > > Winnie,
> > > > >
> > > > > With one notable exception you are right. For some
> reason, on HP-UX
> > > > > systems,
> > > > >
> > > > > turning on timed_statistics can cause a performance
> degradation that
> > > is
> > > > > noticeable (I've heard up to 12%)
> > > > >
> > > > > Rachel
> > > > >
> > > >--
> > > >Author: Weerd Kirsten, E.C. de
> > > > INET: Kirsten.deWeerd_at_Oranjewoud.nl
> > > >
> > > >Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> > > >San Diego, California -- Public Internet access /
> Mailing Lists
> > >
>
>--------------------------------------------------------------------
> > > >To REMOVE yourself from this mailing list, send an E-Mail
> message
> > > >to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> > > >the message BODY, include a line containing: UNSUB
> ORACLE-L
> > > >(or the name of mailing list you want to be removed
> from). You may
> > > >also send the HELP command for other information (like
> subscribing).
> > >
> > >
>
> > > Get Your Private, Free E-mail from MSN Hotmail at
> http://www.hotmail.com
> > >
> > > --
> > > Author: Rachel Carmichael
> > > INET: carmichr_at_hotmail.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> > > San Diego, California -- Public Internet access /
> Mailing Lists
> > >
>
> > > To REMOVE yourself from this mailing list, send an E-Mail
> message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB
> ORACLE-L
> > > (or the name of mailing list you want to be removed from).
> You may
> > > also send the HELP command for other information (like
> subscribing).
> >--
> >Author: Weerd Kirsten, E.C. de
> > INET: Kirsten.deWeerd_at_Oranjewoud.nl
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858)
> 538-5051
> >San Diego, California -- Public Internet access /
> Mailing Lists
>
>--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail
> message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from).
> You may
> >also send the HELP command for other information (like
> subscribing).
>
Gaja Krishna Vaidyanatha
Director, Storage Management Products
Quest Software Inc.
(972)-304-1170
gajav_at_yahoo.com
Received on Tue Aug 22 2000 - 11:08:32 CDT
