Re: "Elapsed time" from statspack/sql_trace_tkprof_file different;

  • From: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
  • To: zhuchao@xxxxxxxxx
  • Date: Fri, 24 Aug 2007 10:15:11 -0700

Zhu Chao,

STATSPACK scans V$SQL for high-load SQL based on a certain set of defaulted
lower limits, such as on number of logical and physical I/Os per stored SQL
statement. Thus, it will capture SQL that occurred prior to, and thus
outside of, the snapshot period. If previous executions were faster (or
slower), then the V$SQL snapshot does not really paint an accurate picture
and your average is screwed. (Snapshot level and threshold information that
is used as the lower limits for scanning V$SQL is stored in the
STATS$STATSPACK_PARAMETER table. This can be changed using the
modify_statspack_parameter procedure.)

In other words, STATSPACK does NOT report the difference in SQL executions
as it does with the other V$SYSTEM_EVENT/V$SYSSTAT snapshots. Thus, the
whole point of capturing and using V$SQL in STATSPACK is flawed. Have a look
at Tim G's scripts on STATSPACK - he has an SQL that can report the
difference in executions of a particular SQL that repeats across snapshots,
and you will see what I mean...

John Kanagaraj <><
DB Soft Inc (Sorry - not an Oracle blog!)
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

Other related posts: