RE: High consistent gets , 10046

Thank you, Jonathan,

No need for apology, all you input is very valuable. Note about update/select for update just great, didn't realize this. About chache chains. Taking real-time snapshots revealed breathtaking

     FILE# DBABLK COUNT(*)
---------- ---------- ----------

         9      38644       2144
         9      77084         10
         9      68036          7

I remember, 9i's 6 block target length has been discussed recently. This is an index on weblogic's JMS store table. Really hot spot.

Have a good day
Vadim

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

Vadim,

Apologies, I answered the question
you didn't ask - viz why does it take
so long, rather than the 'what are the
CR gets'.

Your second suggestion is the correct
one. It seems unreasonable, but when
you do the "select for update", Oracle
seems to go through a load of read-
consistency work for the block to roll
back the changes made by other
transactions. The excess CR gets
are accesses to the UNDO blocks
need to build the CR image.

Strangely, if you just slam in the
'update', rather than 'select for update' this phenomenon does not occur.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now available One-day tutorials:
  Cost Based Optimisation
  Trouble-shooting and Tuning
  Indexing Strategies

(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd

____Denmark May 21-23rd

____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 13 March 2003 18:54

> Thank you, Jonathan,
>
> I'll continue looking for my options to cool down the hot spots. Not
sure if
> I can go for partitioning since Oracle charges $$$.
>
> Is it correct that oracle counts looking through the chain
> for the correct copy as many CR? Or the reason for these extra CR is
access
> to undo segments in attemt to reconstruct CR block aged out from
cache?
>
> Thanks
> Vadim
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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