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

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
http://jkanagaraj.wordpress.com (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 **

--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 24 2007 - 12:15:11 CDT