"Elapsed time" from statspack/sql_trace_tkprof_file different;

  • From: "Zhu,Chao" <zhuchao@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 21 Aug 2007 23:32:26 +0800

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
--
//www.freelists.org/webpage/oracle-l


Other related posts: