Re: CBC latch contention on index root block

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Sun, 20 Jan 2013 23:16:46 +0400
Message-ID: <CAOVevU5ZNYUHdPPcPPtPAM_+5EmxGF6Mq+SDicaWMySeDNO01A_at_mail.gmail.com>

So, i think, if you change last predicate( or third part of union as Jonathan Lewis suggested) to:

TAMRABS_PGM.CUST_NUM=(

     SELECT
        GLB_NUM
     FROM
        TAMRCUST_GLB_MAP
     WHERE
        TAMRCUST_GLB_MAP.TBL_NM='TAMRABS_PGM'
        AND TAMRCUST_GLB_MAP.CUST_NUM= 112805
        AND TAMRCUST_GLB_MAP.ATRB_TYP_CD = TAMRABS_PGM.ABS_PGM_ID

  )

you can reduce number of accessing to index of "TAMRCUST_GLB_MAP" because of scalar subquery caching.

On Sun, Jan 20, 2013 at 5:53 PM, oracledba <oracledba71_at_gmail.com> wrote:
> it returns 175 rows.The actual sql has bind variables.I just replaces them
> with the values for better understanding.
>
>
> On Sun, Jan 20, 2013 at 3:27 AM, Sayan Malakshinov <xt.and.r_at_gmail.com>
> wrote:
>>
>> Sorry, previous query was wrong, could you show result of:
>>
>> select count(distinct ABS_PGM_ID)
>> from TAMRABS_PGM
>> where CUST_NUM not in (-1,112805)
>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
Tel: +7 903 207-1576
--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jan 20 2013 - 20:16:46 CET