is this a bug, or a tkprof misunderatnding?

From: Doug Cha <dcha_at_shell2.ba.best.com>
Date: 27 Oct 1998 08:56:03 GMT
Message-ID: <36358aa3$0$29751@nntp1.ba.best.com>

I have been doing some tuning and am somewhat surprised by the results of a ceratin set of tkprof reports. There are indexes on certain tables of x size (say 150k rows) and index range scans of it return something like 36million in the tkprof rows column. Am I reading this wrong?

Output from tkprof follows.

SELECT A.ACCOUNT,SUM(A.POSTED_TOTAL_AMT) FROM
PS_A_LED_RPTG_VW A, PSTREESELECT05 L, PSTREESELECT08 L1 WHERE A.OPRID='VP1' AND A.LEDGER='ACTUALSNLG' AND A.FISCAL_YEAR=1997 AND A.ACCOUNTING_PERIOD BETWEEN 0 AND 12 AND A.BUSINESS_UNIT='309' AND A.ACCOUNT IN ('08010010',
'08030010','08040010','08060010','08070010','08090010','08170010',
'08180010','20000000','22000000','33000000','33360000','35000000',
'35380000') AND L.SELECTOR_NUM=4624 AND A.DIS>= L.RANGE_FROM_05 AND A.DIS
<= L.RANGE_TO_05 AND L1.SELECTOR_NUM=4625 AND A.PRODUCT>= L1.RANGE_FROM_08 AND A.PRODUCT <= L1.RANGE_TO_08 AND A.CURRENCY_CD='NLG' AND A.STATISTICS_CODE=' ' GROUP BY A.ACCOUNT call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.13       0.17          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1    440.15     441.98          2     412706          2           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    440.28     442.15          2     412706          2           0

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

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE

229 SORT (GROUP BY NOSORT)
229 NESTED LOOPS

    230     NESTED LOOPS
    230      NESTED LOOPS
    230       TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'PS_LEDGER'
  11912        INDEX (RANGE SCAN) OF 'PS_LEDGER' (UNIQUE)
    230       VIEW OF 'PS_A_OPR_FA_BU_VW'
      1        SORT (UNIQUE)
      1         NESTED LOOPS
      1          NESTED LOOPS
    241           TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                      'PS_A_OPR_INQGRP'
      2           INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                      'PS_A_INQGRP_RULES' (UNIQUE)
      1          INDEX (UNIQUE SCAN) OF 'PS_A_INQGRP_FA_BU' (UNIQUE)

   4124      INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PS_PSTREESELECT05'
                 (UNIQUE)
36330207     INDEX (RANGE SCAN) OF 'PSBPSTREESELECT08' (NON-UNIQUE)

--
Doug Cha (dcha_at_best.com) Received on Tue Oct 27 1998 - 02:56:03 CST