RE: Too many rows reported for index in tkprof

  • From: "David Kurtz" <info2@xxxxxxxxxxxxxxx>
  • To: <ldutra@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 9 Aug 2005 23:17:16 +0100

It would be nice to see the query which generated this execution plan.  You
may think that what follows is pure guesswork, but I would rather say that
we are in the region where we balance probabilities and choose the most
likely.

It is obviously from PeopleSoft Financials because of tables
PS_PYMNT_VCHR_XREF and PS_VENDOR
PS_BOLETO_BRL does not appear in the vanilla application, so this is a
customisation.

The full scan on VENDOR is often caused by the user forgetting to join the
table on SETID (which is the first column in the unique index).  In many
companies there is only a single SETID, or a single SETID per legislature.
Hence users often forget to add the join in PS/Query.  So this is probably
an ad-hoc on-line query in a customised part of the application.

PS0BOLETO_BRL is the first 'alternate search key' index.  It is an index
automatically generated by Application Designer based upon the 'key'
attributes of the record defined in the PeopleTools data dictionary.  So the
index leads on a column other than those in the unique key, followed by the
unique key columns.  It is not very selective for this query.  17845208 rows
were returned from the 7249 look-ups of the index, but after filtering rows
from the table no rows at all remained.  So either you also need a better
index on this table, or more likely there is something wrong with the query,
probably a missing join condition.

The good news is that if the query does come from PS/Query then it should be
easy to amend.

The other point to make is that if you are to successfully administer a
PeopleSoft system, then you need to get a little familiarity with the
PeopleSoft development tools.  You don't need to be able to develop
application code, but you do need to be able to find out how things are
defined or coded and then get a developer to change them.

regards
_________________________
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
web: www.go-faster.co.uk
mailto:david.kurtz@xxxxxxxxxxxxxxx
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
Next Go-Faster Seminar: PeopleSoft for the DBA, London UK, October
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Leandro Guimaraes
> Faria C. Dutra
> Sent: 09 August 2005 21:16
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Too many rows reported for index in tkprof
>
>
>
> Hi all.
>
>         I'm fazed by a query I've been asked to optimize.
>
>         By creating an index, I've been able to lower EXPLAIN's cost
> estimate from nearly 5k to 36.  Problem is, this translated in an answer
> time reduced from 2min 30s to 2min 20s -- too small a reduction.
>
>         So i did autotrace:
>
>     1271  recursive calls
>    29005  db block gets
> 37156454  consistent gets
>  1371862  physical reads
>
>
>         Looking for a why, I did a tkprof:
>
> Rows     Execution Plan
> -------  ---------------------------------------------------
>       0  SELECT STATEMENT   GOAL: CHOOSE
>       0   NESTED LOOPS
>    7249    NESTED LOOPS
>    7249     NESTED LOOPS
>    7342      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
>                  'PS_PYMNT_VCHR_XREF'
>    7342       INDEX (RANGE SCAN) OF 'PSXPYMNT_VCHR_XREF' (NON-UNIQUE)
>   14589      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
>                  'PS_VOUCHER'
>   14682       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PS_VOUCHER'
>                   (UNIQUE)
>   14496     TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'PS_VENDOR'
>       0    TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
>                'PS_BOLETO_BRL'
>17845208     INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'PS0BOLETO_BRL'
>                 (NON-UNIQUE)
>
>
>         Oops!  Problem is, ps0boleto_brl is an index with less than 100k
> rows.
>
>         So what's wrong here?  How can such a small index give such a high
> row number in tkprof?
>
>         Ah, this is PeopleSoft 10 on Oracle 8.1.7.
>
>         Thanks in advance!
>
>
> --
> Leandro Guimarães Faria Corcete DUTRA
> Administrador de Bases de Dados      +55 (11) 4390 5383
> Toyota do Brasil Ltda              ldutra@xxxxxxxxxxxxx
> São Bernardo do Campo, SP                        BRASIL
>
>
> This message (including any attachments) is confidential and may
> be privileged and intended solely for the use of the
> person/entity to whom it is addressed. If you have received it by
> mistake please notify the sender by returning via e-mail as well
> as delete this message from your system. Any unauthorized use or
> dissemination of this message in whole or in part is prohibited.
> Please note that e-mails are susceptible to change. TOYOTA DO
> BRASIL LTDA (including its group companies) shall not be liable
> for the improper or incomplete transmission of the information
> contained in this communication, neither for personal,
> nonbusiness related information nor opinion sent through this
> email or even for any delay in its receipt or damage to your
> system. TOYOTA DO BRASIL LTDA (or its group companies) does not
> guarantee that the integrity of this communication has been kept
> nor that this communication is free of viruses, interceptions or
> interference.
> --
> //www.freelists.org/webpage/oracle-l
>


--
//www.freelists.org/webpage/oracle-l

Other related posts: