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


>

>> 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"

>

>