Re: High consistent gets , 10046

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