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" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
