RE: High consistent gets , 10046

Thank you, Anjo.
I want to provide more information about this case:

• In both cases binding is inside PL/SQL block, bind variable type is correct. this must eliminate reasons 1 and 2
• different bind variables - yes, almost for sure they are different, but this is PK, must make no difference.
• contention for cache chain latches is high

I'm looking at Cary's article "Why you should focus on LIO... " Looks like this count may only be caused by undo lookups. It still looks too high, since hot blocks must not age out too fast unless Oracle is too aggressive is keeping number of copies low. Not sure how Oracle counts access to different block versions im memory at 10046, assuming as one access. I'm heading to collect more information on cache chain latches and block counts in x$bh and send it out .

Thanks again,
Vadim

-----Original Message-----
Sent: Thursday, March 13, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L

I can think of three reasons:

1. You are binding with the wrong datatype and you are getting a full table access for the SELECT and then the rowid is remembered for the FOR UPDATE (results in 1 current get).
2. You are implicitly using array fetch in sqlplus, so the number of cr gets will be lower but given the fact that current gets is 1 in both cases, you can ignore this.
3. Different bind variable values.

Anjo.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, March 13, 2003 4:24 PM

> Dear listers,
>
> I'm hunting for top LIO consumers to give a relief to our DB cpu and found
> something that looks interesting.
>
> Many plain good queries show up way to high cr when executed in concurrent
> environment (50 threads) while perform as predicted when executed from
> SQL*PLUS.
>
> The example below is select by primary key, PK index height is 1.
>
> Trace taken in concurrent env shows cr=152
>
> =====================
> PARSING IN CURSOR #136 len=86 dep=1 uid=65 oct=3 lid=65
tim=1022957016971691
> hv=941708176 ad='61f780e8'
> SELECT samp_ver
> FROM sub_svc
> WHERE sub_svc_id = :b1
> FOR UPDATE
> END OF STMT
> PARSE

#136:c=0,e=133,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1022957016971679
> EXEC
> #136:c=0,e=2185,p=0,cr=152,cu=1,mis=0,r=0,dep=1,og=4,tim=1022957016974087
> FETCH

#136:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1022957016974208
>
>
> When tested from SQL*plus prompt (server is idle), is falls to resonable
> cr=3
>
> =====================
> PARSING IN CURSOR #3 len=77 dep=1 uid=65 oct=3 lid=65 tim=1023016395834410
> hv=3412082965 ad='6344f6cc'
> SELECT samp_ver
> FROM sub_svc
> WHERE sub_svc_id = :b1
> FOR UPDATE
> END OF STMT
> PARSE #3:c=0,e=626,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=1023016395834397
> EXEC #3:c=0,e=936,p=0,cr=3,cu=1,mis=0,r=0,dep=1,og=4,tim=1023016395835517
> FETCH #3:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,tim=1023016395835612
> =====================
>
> As you may see, the different is quite essential.
>
> Does anybody have an idea why is so?
>
> This is 9.2.0.2 on Solaris
>
> TIA
>
> Vadim G
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Gorbounov,Vadim
> INET: vadim.gorbounov_at_liberate.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gorbounov,Vadim
  INET: vadim.gorbounov_at_liberate.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).