Re: SQL_TRACE Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 06 Jun 1998 15:04:58 GMT
Message-ID: <357a5994.5988050@192.86.155.100>

A copy of this was sent to Umar FArooq <umar.farooq_at_cressoft.com.pk> (if that email address didn't require changing) On Sat, 06 Jun 1998 15:51:20 +0500, you wrote:

>Im currently working on SQL_TRACE. I view the .trc file using tkprof. In
>the output file the number of rows is indicated for each
>parse/fetch/execute operation. What does teh row column signify? How is
>it different from the row count indicated alongside each execution step
>in the explain plan statistics.
>
>Thx.
>
>Umar.

consider:

---

select *
from
 emp, dept

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

Parse        1      0.00       0.00          2          0          2           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.00          2         12         15          56
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.00          4         12         17          56

Misses in library cache during parse: 1 Optimizer goal: CHOOSE
Parsing user id: 20 (SCOTT)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     56   NESTED LOOPS
      4    TABLE ACCESS (FULL) OF 'DEPT'
     56    TABLE ACCESS (FULL) OF 'EMP'
=========================================================================

the rows column signifies the total number of rows processed by the statemt. In this case, 56 rows were returned. It took 5 fetches to do it (affected by arraysize settings -- sqlplus fetches more then 1 row per fetch).

It is different from the row count in the execution plan as the row count in the execute plan shows the number of rows flowing through each step of the plan, while the rows in the Fetch line shows the number of rows that eventually got returned. The execution plan shows that 4 rows were accessed in the DEPT table and a total of 56 rows (i had 14 rows in emp, 4*14 = 56) from the emp table were accessed.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jun 06 1998 - 10:04:58 CDT