請教關于 父與子 latch 的理解.....

Yong Huang

最初由 zhang_yong88 发布
[B]steve 關于 latch 的文檔中的如下的描述......
...In general,
both the parent and child latches may be taken. In practice, however, the library
cache parent latch is the only parent latch you are likely to see being taken
...
==============
這里講到 library cache latch 只有 parent latch 會取到。[我理解有誤嗎﹖]
[/B]

Run
select name from v$latch_parent where gets > 0 and latch# in (select latch# from v$latch_children);

in 8i, 9i and 10g. You'll see there's 1, 6 and 12 latches, respectively. So Steve's statement is valid for 8i.

[B]
這里講到的solitary latches 要怎么理解﹐是否可這樣表述﹕
select * from v$latch_parent where latch# not in (select latch# from v$latch_children)... 如果是這樣的﹐那大部份的 latch 都是這種類型..
[/B]

Correct.

In line with the name v$latch_children, v$latch_parent should only have the rows where latch#'s are in v$latch_children. You don't call an adult that doesn't have kids a parent, do you?

Yong Huang

zhang_yong88

感謝兩位!

但還有是以下疑問﹕

最初由 Yong Huang 发布
[B]

Run
select name from v$latch_parent where gets > 0 and latch# in (select latch# from v$latch_children);

in 8i, 9i and 10g. You'll see there's 1, 6 and 12 latches, respectively. So Steve's statement is valid for 8i.


Yong Huang [/B]

Yong Huang ﹐您所述說的是否可理解為﹕8i中﹐ library cache latch 是不會用到 children latch 。

所以以您所提供的 sql 來查詢應只有一條記錄對嗎﹖

但我在几台 linux as 2 + oracle8.1.7.4上查看都會有 children library cache latch .....

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE        8.1.7.0.0        Production

TNS for Linux: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL> select name,latch#,level,gets,sleeps from v$latch_children where name like 'librar%';

NAME                                                                 LATCH#      LEVEL       GETS     SLEEPS
---------------------------------------------------------------- ---------- ---------- ---------- ----------
library cache                                                           106          0    8360229        678
library cache                                                           106          0   11950301        880
library cache                                                           106          0   10554052        654
library cache                                                           106          0   18044267       1053
library cache                                                           106          0   12502162        873

SQL> select name from v$latch_parent where gets > 0 and latch# in (select latch# from v$latch_children);

NAME
----------------------------------------------------------------
enqueue hash chains
library cache

SQL> conn sys/d@t21
Connected to Oracle8i Enterprise Edition Release 8.1.7.4.0
Connected as sys

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE        8.1.7.0.0        Production

TNS for Linux: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL> select name from v$latch_parent where gets > 0 and latch# in (select latch# from v$latch_children);

NAME
----------------------------------------------------------------
enqueue hash chains
library cache

SQL> select name,latch#,level,gets,sleeps from v$latch_children where name like 'librar%';

NAME                                                                 LATCH#      LEVEL       GETS     SLEEPS
---------------------------------------------------------------- ---------- ---------- ---------- ----------
library cache                                                           106          0  301861443      42160
library cache                                                           106          0  252452047      32745
library cache                                                           106          0  338738085      37718
library cache                                                           106          0 1323625365      39167
library cache                                                           106          0  259257045      29388
library cache                                                           106          0  599792810      60388
library cache                                                           106          0  797024763      35100
library cache                                                           106          0  172451607      31295
library cache                                                           106          0 1491996871      40031
library cache                                                           106          0  502841375      70756
library cache                                                           106          0  511555187      76416

11 rows selected

zhang_yong88

所以我會認哦 library cache latch 會隨著系統壓力而會自動的擴增﹐下面一台服務器的負載比較
重﹐所以會有 11 個 child latch 之多。

而上面的那一台較為輕松﹐所以只有 5 個...

我在另一台生產資料庫查看﹐因為是只一個小型廠所用﹐所以事務量不大。
所以只看到如下:

SQL> conn sys/dd@t7245
Connected to Oracle8i Enterprise Edition Release 8.1.7.4.0
Connected as sys

SQL> select name from v$latch_parent where gets > 0 and latch# in (select latch# from v$latch_children);

NAME
----------------------------------------------------------------
library cache

SQL> select name,latch#,level,gets,sleeps from v$latch_children where name like 'librar%';

NAME                                                                 LATCH#      LEVEL       GETS     SLEEPS
---------------------------------------------------------------- ---------- ---------- ---------- ----------
library cache                                                           106          0    8009136        489


所以請問上述的結果 怎么理解才好 .....

Yong Huang

> 您所述說的是否可理解為﹕8i中﹐ library
> cache latch 是不會用到 children latch 。
>
> 所以以您所提供的 sql 來查詢應只有一
> 條記錄對嗎﹖

Not at all. Just the opposite. What I was saying is that in 8i, only library cache latch can have some non-zero stats on the parent latch. A monor correction on my part, though. That seems to be valid for 8.1.7.0 only. In 8.1.7.4, two latches, library cache latch and enqueue hash chains latch, have this characteristic.

> 所以我會認哦 library cache latch 會隨著
> 系統壓力而會自動的擴增

I think the number of child library cache latches is controlled by _kgl_latch_count, which is a prime number greater than the number of CPUs, unless cpu_count is 1, in which case, _kgl_latch_count should be 1. When you see 11 and 5 library cache latches, do they have CPU's 8 and 4, respectively?

Yong Huang

zhang_yong88

謝謝 Yong Huang ﹗﹗﹗

是否這樣理解﹕

steve 所言的是﹕只有 library cache 的 parent latch 能像 children latch 那樣被 取得﹐

所以您所提供的 sql 是 找出 v$latch_parent.gets >0 。那樣就僅只能顯示出一條記錄..

因為 steve 所說的是 8i 的早些的版本﹐所以在 8.1.7.4中就有多出
enqueue hash chains  latch 。它的 parent latch 也會被取得..

..............................

對﹐您提醒了我。
library cache children latch 的數量與 _kgl_latch_count參數有關。如果沒有對它進行明確的設置時它所顯示的值是為 0。

但它的默認值是為大于 cpu_count  的最小質數。

前不久剛做完測試﹐ library cache latch 的數量是與 cpu_count  有關的。所以我們也可能通過修改 cpu_count 參數的值來改動

library cache latch 的數量。只有滿足了大于 cpu_count 的最小質數后再來看  _kgl_latch_count的值是否大于這個質數﹐如大于則用
_kgl_latch_count.......

cc59

from metalink:


v$latch_children:

This view contains statistics about child latches. This view includes all columns of V$LATCH plus the CHILD# column. Note that child latches have the same parent if their LATCH# columns match each other.

Use the following SQL to see the view definition of the related GV$ view:

      SELECT view_definition FROM v$fixed_view_definition
       WHERE view_name='GV$LATCH_CHILDREN';

  Useful Join Columns:
   

  Support Notes:
    See Also: <View:V$LATCH>
              <View:V$LATCH_PARENT>
              <View:V$LATCH_MISSES>

    This view shows latch children for latches having a set of children.
    Eg: The "library cache" latches can be seen in this view for
        which each child latch protects a set of hash buckets in the library
        cache.

    You normally want to look at this view to get finer detail on any problem
    latches showing up in <View:V$LATCH> if the latch has children. For such
    latches the problem could be focussed around a couple of child latches
    or may be spread generally across them all. You also need to look
    at <View:V$LATCH_PARENT> in case all the activity is on the parent latch.

Yong Huang

最初由 cc59 发布
[B]v$latch_children:

This view contains statistics about child latches. This view includes all columns of V$LATCH plus the CHILD# column. Note that child latches have the same parent if their LATCH# columns match each other.

Use the following SQL to see the view definition of the related GV$ view:
...[/B]

Did you write those words? If not, here's a friendly reminder: you need to point out the source. It's a matter of copyright and liability.

Yong Huang

cc59

最初由 Yong Huang 发布
[B]

Did you write those words? If not, here's a friendly reminder: you need to point out the source. It's a matter of copyright and liability.

Yong Huang [/B]

谢谢YONG提醒.这来自METALINK

我将编辑一下我的帖子.

zhang_yong88

關于 latch 的 level 想繼續請教  yong huang.....

oracle 將 latch 置為 1..15 間不等。以下是 8.1.7.4的查詢...
SQL> select level#,count(*) from v$latch group by level#;

    LEVEL#   COUNT(*)
---------- ----------
         0         18
         1         16
         2          9
         3         23
         4         11
         5         24
         6         11
         7          6
         8         30
         9          1
        11          1
        12          1
        13          1

13 rows selected


有下面几個疑問﹖

1﹑oracle 是以什么方式來將 latch 置上不同的 level 的﹐是不可知的或是 按對 oracle 執行一個 動作 時順序所需要的 latch ....

2﹑這里的 level 是否有優先級的成份在里面....

3﹑在以 willing_to_wait 的方式去請求 latch 時﹐進程會先檢查是否已獲得 同一級或更高一級的 latch ﹐如果有則不能。。

這樣做是為了防止死鎖的發生﹐請問﹐1）。為何要限制是 同一級或更高級。 2）。為何要這樣來偵測死鎖﹐因為同一級的 latch 有多個﹐并不是兩個相互等待的  latch ﹐只是可能在同一級這種情況應是有的吧。顯然將死鎖的預測擴大了......

以上謝謝.....

Yong Huang

最初由 zhang_yong88 发布
[B]
1﹑oracle 是以什么方式來將 latch 置上不同的 level 的﹐是不可知的或是 按對 oracle 執行一個 動作 時順序所需要的 latch ....

2﹑這里的 level 是否有優先級的成份在里面....

3﹑在以 willing_to_wait 的方式去請求 latch 時﹐進程會先檢查是否已獲得 同一級或更高一級的 latch ﹐如果有則不能。。

這樣做是為了防止死鎖的發生﹐請問﹐1）。為何要限制是 同一級或更高級。 2）。為何要這樣來偵測死鎖﹐因為同一級的 latch 有多個﹐并不是兩個相互等待的  latch ﹐只是可能在同一級這種情況應是有的吧。顯然將死鎖的預測擴大了......
[/B]

I have not done much research on latch levels. Others may have better input. If your first question is about how Oracle determines what level a given latch should be on, well, we probably have to ask their kernel developers. They hard code level#'s with different latches. For instance, in 9i, library cache latches are at level 5 and shared pool latches are at 7, etc. I suppose they think a shared pool latch is more critical and should be held for a shorter time than a library cache latch. Yes, levels imply priority. As documentation says, a process, if it's holding a latch, cannot get a lower (or even equal) level latch. We can also say it your way: if the process wants to get a latch at level x, it checks to see if it already holds a latch >= x. If so, the attempt is aborted. Your last question is why >=x? Why not >x and allow latch gets at levels =x? Let's imagine this case:

i. Session 1 gets latch A
ii. Session 2 gets latch B
iii. 1 tries to get B
iv. 2 tries to get A

where A and B are at the same level x. If your rule only prohibits >x latch gets instead of >=x, then steps i through iv will all go through and a deadlock will occur. But if processes cannot hold latches A and B at the same time, i and ii work but steps iii and iv won't work (to be more correct, they won't work for willing to wait gets, not immediate gets).

On the other hand, if A's level is lower than B's, i through iii works but iv fails. A deadlock is also avoided.

Yong Huang