| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Too many rows reported for index in tkprof
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
tel: +44 (0)7771 760660 fax: +44 (0)7092 348865 web: www.go-faster.co.uk
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Leandro Guimaraes
> Faria C. Dutra
> Sent: 09 August 2005 21:16
> To: oracle-l_at_freelists.org
> 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_at_toyota.com.br
> 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.
> --
> http://www.freelists.org/webpage/oracle-l
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 09 2005 - 17:19:19 CDT
 |
 |