Re: Odd behavior of function-based index after DB upgrade

From: Sidney Chen <huanshengchen_at_gmail.com>
Date: Thu, 6 Oct 2011 11:24:24 +0800
Message-ID: <CAM_ddu-05swB947Luj3x-wBLidQ_vYfJMsWLwQzewYM_SVkvvQ_at_mail.gmail.com>

*10053 event will help to check why CBO makes the decision*
alter session set events '10053 trace name context forever, level 1';

SELECT LEGACY_VENDOR_NO FROM TXRIOH0_INV_OUTBOUND_HDRS_ALL WHERE LPAD(NVL(VOUCHER_NO,0),7,'0') = :B2 AND NVL(SYSTEM,1) = NVL(:B1 ,1) AND
BATCH_STATUS NOT IN ('CONFIRMED','PAID','RECEIVED') alter session set events '10053 trace name context off';

*get the tracefile*

col tracefile for A120

select value ||'/'||(select instance_name from v$instance) ||'_ora_'||

       (select spid||case when traceid is not null then '_'||traceid else null end

             from v$process where addr = (select paddr from v$session
                                         where sid = (select sid from
v$mystat
                                                    where rownum = 1
                                               )
                                    )
       ) || '.trc' tracefile

from v$parameter where name = 'user_dump_dest';

-- 
Regards
Sidney Chen


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

Received on Wed Oct 05 2011 - 22:24:24 CDT