"Elapsed time" from statspack/sql_trace_tkprof_file different;

From: Zhu,Chao <zhuchao_at_gmail.com>
Date: Tue, 21 Aug 2007 23:32:26 +0800
Message-ID: <962cf44b0708210832n1980be76ue92c7c916a67260f@mail.gmail.com>

hi, all

   While checking some performance issue on a database (9205/solaris8), i noticed the SQL performance from statspack/sql_trace_tkprof_file are different; sql_trace file shows each execution is about 5.3 buffer_gets and 2ms. but statspack shows each execution elapsed time is about 0.5ms (too fast, ah)...

   Anyone has similar finding like it? and what might be causing the difference? SQL too fast to be captured by oracle ?   here are some details;
update TBL_OWNER."STATUS_TABLE" t set "FLAGS04"=:V001,   "FLAGS05"=:V002,"FLAGS06"=:V003,"FLAGS07"=:V004,"FLAGS08"=
:V005

where
 "FLAGS04"=:V006 and "FLAGS05"=:V007 and "FLAGS06"=
:V008 and "FLAGS07"=:V009 and "FLAGS08"=:V00A and "ID"=:V00B

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        0      0.00       0.00          0          0          0           0
Execute  32945     55.52      60.49        671     134208      41604       32945
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    32945     55.52      60.49        671     134208      41604       32945

But statspack shows each execution is about 0.48ms(rounded to 0.5);
                                                    CPU      Elapsd

  Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ----------

      5,008,886 960,368 5.2 30.9 349.27 461.47 1975398672

-- 
Regards
Zhu Chao
www.cnoug.org
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 21 2007 - 10:32:26 CDT