tkprof - why is the column rows all 0 ?

From: Bass Chorng <bchorng_at_yahoo.com>
Date: 7 Nov 2001 09:28:33 -0800
Message-ID: <bd9a9a76.0111070928.34d3c357@posting.google.com>

Does anybody know why in the same trace file, some queries have values in the column "rows" in the explain plan section, but some queries have
all zeros ?

The tkprof was executed after the session has exited out ( I understand
if you perform tkprof pre-maturely, some statistics may not be complete ).

Thanks in advance for any help.

-Bass Chorng

Here is one example: ( If I interpret it correctly, this query processed 1114787 buffer cache blocks and returned 2930 rows, so the rows column
can't be zero. )

---

select count(*)
from
 dual where exists (select * from roleprivilege rp where rp.privilege_id =
  :1 AND exists (SELECT rm.role_id FROM rolemembership rm, role ro WHERE
  rp.role_id = ro.id and rm.role_id = ro.id AND rm.person_id = :2 AND
  ro.entity_type in ('B', 'G') ) AND rp.restriction_id = 1)

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.00          0          0          0  
        0
Execute      3      0.01       0.01          0        956          0  
        0
Fetch        3      0.00       0.00          0          2          8  
        3

------- ------ -------- ---------- ---------- ---------- ----------

---

total        7      0.01       0.01          0        958          8  
        3
Execute   2930    143.51     144.61          1    1114787      11720  
        0
Fetch     2930      0.05       0.02          0          0          0  
     2930

------- ------ -------- ---------- ---------- ---------- ----------

---

total     5861    143.56     144.63          1    1114787      11720  
     2930

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 175 (recursive depth: 1)

Rows Execution Plan
------- ---------------------------------------------------

      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (AGGREGATE)
      0    FILTER
      0     TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'SPDUAL'
      0     NESTED LOOPS
      0      VIEW
      0       FILTER
      0        CONNECT BY
      0         INDEX (FAST FULL SCAN) OF 'TREENODE_INDEX1' (UNIQUE)

      0         TABLE ACCESS   GOAL: ANALYZED (BY USER ROWID) OF
                    'TREENODE'
      0         TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                    'TREENODE'
      0          INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
                     'PKEY_TREENODE' (UNIQUE)
      0      INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                 'PERSONPRIVILEGE_INDEX2' (NON-UNIQUE)

********************************************************************************

Received on Wed Nov 07 2001 - 11:28:33 CST