Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: latch contention
Yes - 24 hours lost in 3 days on 1 CPU
is an issue. (24 hours over 8 CPUs in
a month would not be)
X$BH is visible only to the SYS user.
2 latches out of 1024 responsible for a
large fraction of the sleeps sounds
promising.
One point I haven't made - cache buffers
chains latching is inevitable, it means you
are using the database. However, if you
are making the database do more logical
I/O than is necessary you will start to see
contention on this latch. Be aware that a
side-effect of trying to eliminate physical
I/O and pushing up the 'buffer hit ratio', by
using unsuitable indexes and hints can
result in the problem you are seeing.
When you query X$BH, include the TCH
column in the select, this is the touch count,
and could give you a clue as to which block
is the busiest block on the latch.
-- Jonathan Lewis Host to The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases See http://www.jlcomp.demon.co.uk/book_rev.html Seminars on getting the best out of Oracle See http://www.jlcomp.demon.co.uk/seminar.html Alexey Ogol wrote in message <9hca03$2rpf$1_at_pandora.alkar.net>...Received on Wed Jun 27 2001 - 05:57:17 CDT
>
>> Lost time is about 24 hours since database startup -
>> I forgot to say that we needed to know how long the
>> database had been up to gauge whether this was
>> highly significant - and how many CPUs it was
>> spread across.
>Total number of cpu is 1
>Database is running about 3 days
>
>I tried to see latch_children for this latch
>There's two of them with sleep greater than 100000, total number of latches
>is 1024
>But when I try to select from x$bh, i got "table or view does not exist"
>
>