High consistent gets , 10046

From: Gorbounov,Vadim <vadim.gorbounov_at_liberate.com>
Date: Thu, 13 Mar 2003 07:24:06 -0800
Message-ID: <F001.0056908C.20030313072406@fatcity.com>

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

Received on Thu Mar 13 2003 - 09:24:06 CST