Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: High consistent gets , 10046
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
-----Original Message-----
Sent: Thursday, March 13, 2003 12:19 PM
To: Multiple recipients of list ORACLE-L
If you can check it in real time, you will probably find that you have a very large number of CR copies of the few blocks that are the focus of the concurrent activity.
The excess time is likely to be down to a mixture of CPU as Oracle trawls through the chain looking for the correct copy, and latch contention because of the time the latch has to be held whilst the correct copy is being found.
'select for update ...' seems to be particularly prone to this problem - especially if you have an over large db_cache_size, that allows for lots of blocks in state 'FREE'. (even a reasonably size buffer can produce this effect if there is a process elsewhere which is dropping or truncating objects on a regular basis).
You may be able to reduce the impact of the problem by spreading out the rows that need to be updated - e.g. by increasing the number of freelists, or hash partitioning the table. If this is a relatively small, static sized, table moving it to a single table hash cluster may help.
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
> 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.
>
-- 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).Received on Thu Mar 13 2003 - 12:54:02 CST
![]() |
![]() |