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