RE: Too many rows reported for index in tkprof

oracle-l-bounce_at_freelists.org gravou em 2005-08-09 19:17:16:

> It would be nice to see the query which generated this execution plan.

        I had refrained to do so because I wasn't concerned with the overall query but by the strange number, and others have already pointed me to the nested loop as a culprit. But since you asked, be prepared, it is long:

 SELECT

        p.business_unit,
        v.vendor_id,
        p.voucher_id,
        p.scheduled_pay_dt,
        p.pymnt_gross_amt,
        vn.name1,
        p.pymnt_modality_brl,
        p.pymnt_method,
        v.invoice_id,
        b.document_numb_brl,
        p.bank_cd,
        p.bank_acct_key,
        b.bol_seq_num_brl,
        b.gross_amt,
        0,
        0,
        p.pymnt_gross_amt - (b.gross_amt - 0 + 0),
        p.pymnt_cnt
   FROM
        sysadm.PS_PYMNT_VCHR_XREF       p,
        sysadm.PS_VOUCHER               v,
        sysadm.PS_VENDOR                vn,
        sysadm.PS_BOLETO_BRL            b
  WHERE
        p.BOL_SEQ_NUM_BRL       =  ' '
    AND p.PYMNT_SELCT_STATUS    =  'N'
    AND p.PYMNT_METHOD          =  'EFT'
    AND p.PYMNT_TYPE            <> 'W'
    AND p.PYMNT_ACTION          <> 'P'
    AND p.PYMNT_GROSS_AMT       >  0
    AND v.ENTRY_STATUS          <> 'X'
    AND v.IN_PROCESS_FLG        =  'N'
    AND b.tdb_associado_brl     =  'N'
    AND v.BUSINESS_UNIT         =  p.BUSINESS_UNIT
    AND v.VOUCHER_ID            =  p.VOUCHER_ID
    AND vn.vendor_id            =  v.vendor_id
    AND v.business_unit         =  b.business_unit
    AND p.remit_vendor          =  b.vendor_id
    AND SUBSTR (v.invoice_id, 1, 6)
                                =  SUBSTR (b.document_numb_brl, 1, 6)

> 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.

        Yep.

> 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.

        Dead right.

> 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.

        Good advice, thanks. If only I had not some dozens of Oracle, MS SQL Server, Sybase, Informix and DB2 instances to manage... and far worse apps than PeopleSoft (made for example by recent Clipper programmers), I'd certainly follow it. For now I'm left to trying to understand the database at hand.

        Investment in knowledge is always good advice, too bad employers like to understaff and overextend.

--
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