cpu time and query column in tkprof output

Hi,  

I want to ask two questions about the following 2 sql statements. The below tkprof output shows 2 executes for each of them although i executed each once. Is it because of the select statements inside the main select?  

My main question is; for the second query it shows far less buffer gets than the first one, but the second one has more cpu time and elapsed time. Is this meaningful? I am looking at the buffer gets and saying that the second query is better but it shows more elapsed time?

How should i comment on the following output?    

SELECT COUNT (*)   FROM faxutil.t_kfs_jobs j, faxutil.t_kfs_actions a

 WHERE j.NO = a.jobno

   AND a.pool_id = :poolname

   AND (a.jobno, a.iteration) IN (SELECT jobno, MAX (iteration)

                                      FROM faxutil.t_kfs_actions

                                     WHERE pool_id = :poolname

                                  GROUP BY jobno)

   AND EXISTS (           SELECT pool_status, job_status

            FROM faxutil.t_kfs_tabstatuses

           WHERE tabno = :ptabno

             AND pool_status = a.status

             AND job_status = j.status)  

call count cpu elapsed disk query current rows

• ------ -------- ---------- ---------- ---------- ----------

  ---

Parse 1 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 2 0.18 0.31 14 11677 58 1

• ------ -------- ---------- ---------- ---------- ----------

  ---

total 5 0.18 0.31 14 11677 58 1  

Misses in library cache during parse: 0

Optimizer goal: CHOOSE  

Misses in library cache during parse: 0

Optimizer goal: CHOOSE

Parsing user id: 46  

Rows Row Source Operation

• ---------------------------------------------------

      1 SORT AGGREGATE      14 FILTER    2768 NESTED LOOPS    2768 HASH JOIN    2767 VIEW VW_NSO_1    2767 SORT GROUP BY    2875 INDEX RANGE SCAN (object id 34317)

   2875 INDEX RANGE SCAN (object id 34317)

   5534 TABLE ACCESS BY INDEX ROWID T_KFS_JOBS    5534 INDEX UNIQUE SCAN (object id 31338)

     14 TABLE ACCESS FULL T_KFS_TABSTATUSES     select /*+ ordered */

  count(*)

 from

  faxutil.t_kfs_tabstatuses c,

  faxutil.t_kfs_jobs j,

  faxutil.t_kfs_actions a,

  ( select jobno, max(iteration) iteration

  from faxutil.t_kfs_actions

  where pool_id = :poolname

  group by jobno ) sqry1

 where

  j.no = a.jobno

  and a.pool_id = :poolname

  and a.jobno = sqry1.jobno

  and a.iteration = sqry1.iteration

 and c.tabno = :ptabno

 and c.pool_status = a.status

 and c.job_status = j.status  

call count cpu elapsed disk query current rows

• ------ -------- ---------- ---------- ---------- ----------

  ---

Parse 1 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 2 0.82 1.44 31 4565 58 1

• ------ -------- ---------- ---------- ---------- ----------

  ---

total 5 0.82 1.44 31 4565 58 1  

Misses in library cache during parse: 0

Optimizer goal: CHOOSE

Parsing user id: 46  

Rows Row Source Operation

• ---------------------------------------------------

      1 SORT AGGREGATE      14 HASH JOIN      14 HASH JOIN    2875 INDEX RANGE SCAN (object id 34317)

   8282 NESTED LOOPS      14 TABLE ACCESS FULL T_KFS_TABSTATUSES    8282 INDEX FAST FULL SCAN (object id 34320)

   2767 VIEW    2767 SORT GROUP BY    2875 INDEX RANGE SCAN (object id 34317)  

--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 02 2005 - 05:26:33 CST