Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tkprof result reading

Re: Tkprof result reading

From: Bass Chorng <bchorng_at_yahoo.com>
Date: 26 Jun 2001 09:07:48 -0700
Message-ID: <bd9a9a76.0106260807.1ab498ed@posting.google.com>

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9h8q7g02cr6_at_drn.newsguy.com>...
> In article <bd9a9a76.0106251145.76ff5220_at_posting.google.com>, bchorng_at_yahoo.com

: 
: < lines deleted>
: 

>
> so, in my case, it means I was doing a select for update on an unindexed table
> -- the execute phase had to do it 100 times, the fetch phase once.
>
>
> Are you doing something similar?
>

Thomas, this SQL is a pure select. I did not write it, it came from our
application. Here is the complete information:


SELECT COUNT(*)
FROM
 SPDUAL WHERE EXISTS (SELECT R.ID FROM ROLEPRIVILEGE RP,ROLEMEMBERSHIP RM,
  ROLE R WHERE RM.PERSON_ID = :b1 AND R.ENTITY_TYPE = 'G' AND R.ID =
  RM.ROLE_ID AND RP.RESTRICTION_ID = 1 AND RM.ROLE_ID = RP.ROLE_ID AND
  RP.PRIVILEGE_ID = :b2 )

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------
Parse        1      0.02       0.00          0          0          0  
        0
Execute   3003      0.88       0.70          3      30030          0  
        0
Fetch     3003      0.13       0.20          2       3003      12012  
     3003

------- ------ -------- ---------- ---------- ---------- ----------
total     6007      1.03       0.90          5      33033      12012  
     3003

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 245 (TRAIN_ENCORE) (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      NESTED LOOPS
      0       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 
                  'PK_ROLEMEMBERSHIP' (UNIQUE)
      0       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                  'ROLE'
      0        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                   'SYS_C00215078' (UNIQUE)
      0      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                 'ROLEPRIVILEGE'
      0       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                  'PK_ROLEPRIVILEGE' (UNIQUE)

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

Another question I have is, why are the rows column all 0 in explain plan ? I exited out of the application before tkprofing.

By the way, this is 8.1.7 on Solaris 2.7

Thanks.

-Bass Chorng Received on Tue Jun 26 2001 - 11:07:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US