Re: latch contention

The two objects with a massive tch count - match them to dba_objects (can't remember whether its data_object_id or object_id) which tells you what they are.... and then we start pondering about why they are getting pounded..

hth
connor

--
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."
"Alexey Ogol" <lesha_at_crgu.com> wrote in message
news:9hcgjr$ch3$1_at_pandora.alkar.net...


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

> Yes,  I got it

> But there is one more thing.

> We have two DBs: one is production, where we see these problems, and

 another


> one is test database; this db we load as production db. There's identical

> application running on both databases, and there's no any latch contention

> on second, test db.

>

> >

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

> I executed following query

> select obj, dbarfil, dbablk, tch from

> (select * from (select * from v$latch_children where latch#=66 order by

> sleeps desc) where rownum=1) a,

> x$bh b

> where b.hladdr=a.addr

>

> It is to return an info from x$bh about a latch with max sleeps value

> and i got:

> OBJ        DBARFIL    DBABLK     TCH

> ---------- ---------- ---------- ----------

> 4294967295          3      10721          1

> 4294967295          3       5601         14

>      23729         10      10016          7

>      23729         10      26400          3

> 4294967295          3      12769          1

> 4294967295          3       3553          3

> 4294967295          3       4577          4

> 4294967295          3       6625         10

>      23604         10      24352        383

>      23731          8       7268          0

>      23521          8        100       1182

> 4294967295          3       1505          2

> 4294967295          3       9697          4

>      23666         10       6944         33

>      23593          8       1124      30415

>         66          1      22678         15

>      23606         10       1824      24254

>         68          1      11414          1

>         65          1       8342          1

>      23670          8       4196       2453

> 4294967295          3        481          2

> 4294967295          3       7649          2

> 4294967295          3      16865         39

>          0          0        538          0

>      23729         10       8992          2

>      23664         10      25376          6

>          0          0       1562          0

>          0          0       2586          0

> 4294967295          3      14817          3

>      23663          8       3172         90

> 30 rows selected.

>

> What should I do next?

>

>