TIMED_STATISTICS overhead

TIMED_STATISTICS overhead

Post by Bruce Pihlama » Fri, 01 Nov 1996 04:00:00




> I recently turned on TIMED_STATISTICS to help me with tuning the
> database.  In particular, I'm looking at the number of recursive calls
> and the CPU time they use in V$SYSSTAT.  The manuals frequently point
> out that gathering timed statistics implies extra performance
> overhead.  This got me wondering:  do these statistics include that
> overhead?  If so, is there any way to determine to what extent they are
> "inflated"?

I've seen no noticeable degradation in performance.

All that TIMED_STATISTICS does is get the system time before and after
events.  While this may sound like a lot its only a few instructions on
a machine capable of millions of instructions per second.  I know, it
all adds up.

I remember several years ago that a knowledgeable Oracle internals
expert (few that there were back then) actually said that turning on
TIMED_STATISTICS under VMS actually improved performance as it avoided
some privilege level changes for the process (VMS code privilege
levels).

This was also discussed on oracle-l quite a while ago and I vaguely
remember someone having done load tests at their site and could not
identify any slowdown or overhead.  Their site was a fairly small VAX so
the overhead would have been visible.

Turning on SQL_TRACE is a totally different load situation.

Bruce...
--
  -----------------
   T T T T T T T T        Bruce Pihlamae

   I I I I I I I I        National Library of Australia
   T T T T T T T T        Phone: +616 262-1575
  -----------------       Fax:   +616 273-2116
 ===================
 "If you swallow a live frog first thing in the morning;
  nothing worse will happen to either of you that day."

 
 
 

TIMED_STATISTICS overhead

Post by nso.. » Fri, 01 Nov 1996 04:00:00




>> I recently turned on TIMED_STATISTICS to help me with tuning the
>> database.  In particular, I'm looking at the number of recursive calls
>snip....
>I've seen no noticeable degradation in performance.

>All that TIMED_STATISTICS does is get the system time before and after
>events.  While this may sound like a lot its only a few instructions on
>a machine capable of millions of instructions per second.  I know, it
>all adds up.

>I remember several years ago that a knowledgeable Oracle internals
>expert (few that there were back then) actually said that turning on
>TIMED_STATISTICS under VMS actually improved performance as it avoided
>some privilege level changes for the process (VMS code privilege
>levels).

>This was also discussed on oracle-l quite a while ago and I vaguely
>remember someone having done load tests at their site and could not
>identify any slowdown or overhead.  Their site was a fairly small VAX so
>the overhead would have been visible.

>Turning on SQL_TRACE is a totally different load situation.

>Bruce...
>--
>  -----------------
>   T T T T T T T T        Bruce Pihlamae

>   I I I I I I I I        National Library of Australia
>   T T T T T T T T        Phone: +616 262-1575
>  -----------------       Fax:   +616 273-2116
> ===================
> "If you swallow a live frog first thing in the morning;
>  nothing worse will happen to either of you that day."

Same here. For years I've heard people say Timed_stats is bad for performance.
My suspicion is that these are people who just read manuals and assume they have
all the truth. From many benchmarks I did in ORACLE with V6 and outside of
ORACLE with V7, I've never seen more than about 10% degradation in
performance with T/S on. This with UNIX, VMS and AOS/VS(!).

The important distinction is that, as you point out, one thing is TIMED STATISTICS,
the other is TRACE ON. The latter is the killer. Not the former.

Nuno Souto
Database Consultancy Services P/L

Isn't life wonderful?

 
 
 

TIMED_STATISTICS overhead

Post by Thomas J. Ky » Fri, 01 Nov 1996 04:00:00




>>Turning on SQL_TRACE is a totally different load situation.

>If you were benchmarking various statements - for example, trying to
>determine the threshold at which it's better to drop and recreate
>indexes versus updating them in place during a large update - which
>would you use? SQL_TRACE or TIMED_STATISTICS?
>--

Neither, I'd use a watch :)

SQL_TRACE is very useful for performance tuning an application with lots of sql.
Sort of like running unix prof on a c program to see what statements are eating
80% of my run time.

TIMED_STATISTICS is useful in conjunction with sql_trace, as it will give you
useful stuff in the trace file like how long did i spend parsing, executing,
fetching, etc.  It is also useful for utils like utlbstat/utlestat.

To figure out if the update vs drop and rebuild indexes where worth it, I would
think that the run times would be long enough (if they both ran in seconds you
would'nt care so they must be taking many minutes or even hours to complete)
that a simple wall clock time would tell you the difference.  If they weren't
measurably different using a wall clock, you would just use the method most
convienent.

Turning on sql_trace for a really really big operation like that could double
the time it takes to execute with all the extra junk being written to the trace
file.

I myself run all my databases with TIMED_STATISTICS turned on all the time.
This populates my v$ tables and lets me turn on sql_trace (and get meaningful
output from it) on a session by session basis. Any performance hit i take for
timed_statistics (and I really don't see any measurable hit) is more then offset
by the convience of always having the v$ tables around for tuning/answering
questions and being able to sql_trace without having to bounce the database....

>Chuck Hamilton

>This message delivered by electronic sled dogs. WOOF!

Thomas Kyte
Oracle Government

http://govt.us.oracle.com  

---- Check out Oracle Governments web site!  -----
     Follow the link to "Tech Center"
     and then downloadable Utilities for some free software...

-------------------
statements and opinions are mine and do not necessarily
reflect the opinions of Oracle Corporation

 
 
 

TIMED_STATISTICS overhead

Post by Chuck Hamilt » Fri, 01 Nov 1996 04:00:00



>Turning on SQL_TRACE is a totally different load situation.

If you were benchmarking various statements - for example, trying to
determine the threshold at which it's better to drop and recreate
indexes versus updating them in place during a large update - which
would you use? SQL_TRACE or TIMED_STATISTICS?
--
Chuck Hamilton

This message delivered by electronic sled dogs. WOOF!

 
 
 

1. Will setting timed_statistics=TRUE create much overhead?

An Oracle DB monitoring product I'm using (Spotlight on Oracle) wants
to set timed_statistics=TRUE. The product docs claim this won't create
significant DB overhead (this is for an ORCL 8.0.5 database, which will
be upgraded to 8.1.6 eventually).

What's the real answer? I know ANALYZE TABLE COMPUTE STATISTICS
statements can be a real drag on performance. What will happen if
timed_statistics is turned on?

Thanks very much.

 - Dana

Sent via Deja.com
http://www.deja.com/

2. Handling FieldDefs dynamically???

3. Performance penalty when turn on timed_statistics

4. Version 8 UNIX Char question

5. Which views become available when you SET TIMED_STATISTICS=TRUE

6. how do i change the ownership of tables in sqlserver?

7. Impact of Timed_statistics parameter

8. Don't read this

9. ORADEBUG & timed_statistics

10. timed_statistics anyone?

11. Timed_statistics

12. timed_statistics anyone?

13. bstat/estat & timed_statistics