Re: latch contention

From: Alexey Ogol <lesha_at_crgu.com>
Date: Wed, 27 Jun 2001 14:39:26 +0300
Message-ID: <9hcgjr$ch3$1@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? Received on Wed Jun 27 2001 - 06:39:26 CDT