Help on understanding TKprof

From: Melgar <luis_melgar_at_yahoo.com>
Date: 6 Jan 2006 14:01:16 -0800
Message-ID: <1136584876.038803.114390@g49g2000cwa.googlegroups.com>

i have this query,
and i can notice that logical reads are by far larger that phisical ones, yet, the rows retrived are few.
Since the quere seems to be taking an acceptable execution plan (to me at least)

where can i start to find out whats going on?

Thx in advance.

select R.NIS_RAD ,R.SEC_NIS ,R.SEC_REC ,R.F_FACT ,R.COD_CLI ,R.SEC_CTA
,

  R.IMP_TOT_REC ,R.EST_ACT ,R.F_VCTO_FAC ,R.COD_UNICOM ,R.NUM_DIAS_REC
,

  NVL(REPLACE(R.COD_TAR,' '),' ') ,R.TIP_REC ,R.IMP_CTA ,R.TIP_CLI ,   R.IND_GESTION_CUENTA ,R.SIMBOLO_VAR ,R.NUM_IDENT_SIPO
,R.IND_CONVERSION

from
 RECIBOS R where (((R.NIS_RAD=:b0 and ((R.SEC_NIS=:b1 or (R.SEC_NIS<:b1 and
  R.IND_TRASPASO=1)) or exists (select 1 from CUOTAS_PL P ,MACUERDOS M where
  (((((((P.NIS_RAD=R.NIS_RAD and P.SEC_NIS=R.SEC_NIS) and P.F_FACT=R.F_FACT)
  and P.SEC_REC=R.SEC_REC) and M.NIS_RAD=P.NIS_RAD) and M.NUM_ACU=P.NUM_ACU)
  and P.SEC_NIS<:b1) and M.IND_TRASPASO=1)))) and R.EST_ACT in (select   EST_REC from GRUPO_EST where (CO_GRUPO=:b4 and EST_REC=R.EST_ACT))) and
  (((R.F_FACT<TO_DATE(:b5,'YYYYMMDD') and R.TIP_REC not in ('TR090','TR013',
  'TR091','TR092')) or (R.F_FACT=TO_DATE(:b5,'YYYYMMDD') and R.SEC_REC<=:b7))
  or (R.F_FACT=TO_DATE(:b5,'YYYYMMDD') and R.TIP_REC in ('TR030','TR092',
  'TR091','TR032')))) order by F_FACT,SEC_REC desc

sumarized for you:

Disk reads : 39474
Query : 1491555
rows Retrived: 4175

call count cpu elapsed disk query current

    rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        8      0.00       0.00          0          0          0
       0
Execute   3921      4.50       4.49          0          0          0
       0
Fetch     8097     51.37    1024.32     139474    1491555          0

    4175
------- ------ -------- ---------- ---------- ---------- ----------

---

total 12026 55.87 1028.81 139474 1491555 0

    4175

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 256 (BATCH)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
    487   SORT (ORDER BY)
      0    CONCATENATION
      0     NESTED LOOPS
      0      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                 'RECIBOS'
  30846       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PK_RECIBOS_NEW'
                  (UNIQUE)
      0        NESTED LOOPS
      0         TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                    'CUOTAS_PL'
  60798          INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                     'CUOTAS_PL_I03' (NON-UNIQUE)
      0         TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                    'MACUERDOS'
      0          INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
                     'PK_MACUERDOS' (UNIQUE)
      0      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GRUPO_EST'
                 (UNIQUE)
      0     NESTED LOOPS
    135      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                 'RECIBOS'
    615       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PK_RECIBOS_NEW'
                  (UNIQUE)
      0      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GRUPO_EST'
                 (UNIQUE)
    453     NESTED LOOPS
  30231      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                 'RECIBOS'
  30711       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PK_RECIBOS_NEW'
                  (UNIQUE)
  29840      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GRUPO_EST'
                 (UNIQUE)

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

Received on Fri Jan 06 2006 - 16:01:16 CST