透過案例學調優之--和 SHARED POOL 相關的主要 Latch

不一樣的天空w發表於2018-05-15

https://blog.csdn.net/lqx0405/article/details/44777601

3.1、和 SHARED POOL 相關的主要 Latch 有:
 Latch: shared pool
 Latch: library cache
我們知道 Oracle 透過 SHARED POOL 來實現 SQL 共享,減少硬解析等。而 SQL 的相關資訊, 如:SQL 語句文字,SQL 執行計劃等都存放在 SHARED POOL 的 Library Cache 部分。

3.2、其中 Library Cache 的結構如下圖:

      可以看到其結構和 BUFFER CACHE 類似,為了能夠在 Library Cache 中快速的查詢到對應的 SQL, 也是透過將不同的 SQL 語句透過 HASH 函式 HASH 後放置到對應 Hash Bucket 來儲存的。 

下面看看圖中***的塊(右上角標註著:Object Handle):
1) 這個塊也就是所謂的 Library Cache Object Handle,這個 Handle 描述 Library Cache 中物件的一些屬性,如名稱(Name),所屬的名稱空間(Namespace)、標記(Flags)、指向物件所處的記憶體地址的指標(Heap 0)等。對應 SQL 來說,這個可以算是父遊標。

2) Heap 0 用來存放與物件有直接關係的一些資訊,比如物件型別、物件相關的表、實際的執行計劃等。

3) 同一個 Hash Bucket 中的 Object Handle 相互連結形成一條 Chain。
關於 Library Cache 更詳細的可以查閱 Julian Dyke 的 Library Cache Internals.ppt。

Eygle 網站上也有一張簡潔的圖:

3.3下面先看SQL的的整個執行過程來,然後再看看執行過程中是怎麼用到SHARED POOL的相 關 Latch。

  1. 1)  當客戶端執行一條 SQL,這時候 Oracle 首先將 SQL 文字轉換成 ASCII 值,然後根據 HASH函式計算該 SQL 對應的 Hash Value。

  2. 2)  根據得到的 Hash Value 到 Library Cache 中查詢對應的 Bucket,然後查詢 Bucket 裡是否存

    在該 SQL?
    (Y)     如果存在,則接下來查詢對應的子游標,這個時候將一直持有 Library Cache Latch,直到找到對應的執行計劃。然後釋放 Latch。(軟解析)
    (N) 如果不存在,就要去 SHARE POOL 裡面獲得可用空間,來生生成對應的 Library Cache 物件。這個時候就要獲得 Shared Pool Latch 在 SHARE POOL 的 Free Lis(SHRAE POOL 透過 Free List 管理 Free Chunk)查詢可用的空間,之後釋放 Shared Pool Latch。 接下來就開始進行硬解析過程,將執行解析後的執行計劃等資訊記錄到 Library Cache 中,這個整個過程消耗大量的 CPU,同時將一直持有 Library Cache Latch,一 直到硬解析結束。(硬解析)

  3. 3)  根據獲得的執行計劃,開始執行 SQL,如:到 BUFFER CACHE 查詢資料等。

3.4 整個邏輯如下如:

3.5 當出現Latch競爭嚴重的時候:
3.5.1如果同時出現大量的 Share Pool Latch 和 Library Cache Latch 的話,根據上面的邏輯那說明數 據庫中存在大量的硬解析,這個時候就要查詢那些 SQL 沒有繫結變數。
3.5.2如果只是出現大量的 Library Cache Latch 的話,那麼可能有兩種情況:
1) 當持有 Library Cache Latch 查詢 Bucket 對應的 Chain 時候,發現存在高 Version 的 SQL,這個時候就要掃描這些對應的子游標,整個過程將一直持有 Latch,導致其他會話獲取不到 Latch 進行操作。

2) 大量的併發請求,而且不能實現 SQL 一次 Parse Call 多次 Execution。 


案例分析：

3.6 測試模擬為硬解析和 SQL 的 Version Count 高的情況。
3.6.1Oracle 10g 有方法可以讓 SQL 產生很多的子游標,必須具備下面幾種的條件:

1)cursor_sharing = similar
2)收集了列上的 histogram
3)SQL 中使用到了此列作為條件,並且條件是“等於”
4)這個 SQL 是沒有繫結變數的
這時候,Oracle 會認為每條 SQL 的 literal 變數都是 unsafe 的,因此就不重用以前的 cursor而新產生一個 version,也就會重新硬解析一次。
 

10:56:01 SCOTT@ prod >show parameter cursor
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      similar
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     50

1、session1：以scott建立測試表
11:44:26 SYS@ prod >conn scott/tiger
Connected.

11:01:41 SCOTT@ prod >select * from v$mystat where rownum=1;
       SID STATISTIC#      VALUE
---------- ---------- ----------
         1          0          0
         
10:56:09 SCOTT@ prod >create table test as select rownum as col1 ,rownum col2 from user_objects
10:58:38   2  ;
Table created.

2、建立測試表直方圖
10:58:51 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'TEST',method_opt=>'for columns col1 size 3');
PL/SQL procedure successfully completed.

10:59:36 SCOTT@ prod >select column_name,num_buckets,histogram from user_tab_col_statistics
11:00:43   2   where table_name='TEST';
COLUMN_NAME                    NUM_BUCKETS HISTOGRAM
------------------------------ ----------- ---------------
COL1                                     3 HEIGHT BALANCED

11:01:35 sys@ prod >ALTER SYSTem flush shared_pool;
System altered.

3、session 2:以scott建立另一個會話
11:03:44 SCOTT@ prod >select * from v$mystat where rownum=1;
       SID STATISTIC#      VALUE
---------- ---------- ----------
        44          0          0

11:04:01 SCOTT@ prod >create table test1 as select rownum as col1 ,rownum col2 from user_objects;
Table created.

11:04:36 SCOTT@ prod >exec dbms_stats.gather_table_stats(user,'TEST1',method_opt=>'for columns col1 size 3');
PL/SQL procedure successfully completed.

11:05:04 SCOTT@ prod >select column_name,num_buckets,histogram from user_tab_col_statistics
11:05:19   2  where table_name='TEST1';
COLUMN_NAME                    NUM_BUCKETS HISTOGRAM
------------------------------ ----------- ---------------
COL1                                     3 HEIGHT BALANCED

11:05:30 sys@ prod >ALTER SYSTem flush shared_pool;
System altered.

4、在session 1執行以下操作
11:02:42 SCOTT@ prod >begin
11:06:28   2  for i in 1..50000 loop
11:06:40   3  execute immediate 'select * from test where col1='||i;
11:07:08   4  end loop;
11:07:11   5  end;
11:07:13   6  /

   在session 2執行同樣地操作
11:07:57 SCOTT@ prod >begin
11:08:01   2  for i in 1..50000 loop
11:08:01   3    execute immediate 'select * from test1 where col1='||i;
11:08:01   4   end loop;
11:08:01   5   end;
11:08:02   6  /

5、檢視session event
11:11:36 sys@ prod > select sid,event,p1,p1text,p2,p2text from v$session where sid in (1,44)
       SID EVENT                                  P1 P1TEXT                                 P2 P2TEXT
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
         1 latch: shared pool              537557404 address                               293 number
        44 latch: shared pool              537557404 address                               293 number
Elapsed: 00:00:00.00
11:11:38 sys@ prod >/
       SID EVENT                                  P1 P1TEXT                                 P2 P2TEXT
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
         1 latch: shared pool              537557404 address                               293 number
        44 latch: row cache objects        828539960 address                               270 number
Elapsed: 00:00:00.00
11:11:39 sys@ prod >/
       SID EVENT                                  P1 P1TEXT                                 P2 P2TEXT
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
         1 latch: shared pool              537557404 address                               293 number
        44 latch: shared pool              537557404 address                               293 number
Elapsed: 00:00:00.00
11:11:41 sys@ prod >/
       SID EVENT                                  P1 P1TEXT                                 P2 P2TEXT
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
         1 latch: shared pool              537557404 address                               293 number
        44 latch: row cache objects        828007508 address                               270 number
Elapsed: 00:00:00.00
11:11:42 sys@ prod >/
       SID EVENT                                  P1 P1TEXT                                 P2 P2TEXT
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
         1 latch: shared pool              537557404 address                               293 number
        44 latch: shared pool              537557404 address                               293 number
11:12:32 sys@ prod >/
       SID EVENT                                  P1 P1TEXT                                 P2 P2TEXT
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
         1 latch free                      821793596 address                               274 number
        44 latch: shared pool              537557404 address                               293 number
 
 sys@ prod >select sid,event,p1,p1text,p2,p2text from v$session where sid in (1,44)
       SID EVENT                                  P1 P1TEXT                                 P2 P2TEXT
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
         1 latch: shared pool              537557404 address                               293 number
        44 library cache: mutex X         1307903034 idn                                 65536 value
11:14:58 sys@ prod >select sid,event,p1,p1text,p2,p2text from v$session where sid in (1,44)
       SID EVENT                                  P1 P1TEXT                                 P2 P2TEXT
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
         1 library cache: mutex X         3413592168 idn                               2883584 value
        44 latch: row cache objects        828539960 address                               270 number
11:15:18 sys@ prod >select sid,event,p1,p1text,p2,p2text from v$session where sid in (1,44)
       SID EVENT                                  P1 P1TEXT                                 P2 P2TEXT
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
         1 SQL*Net message from client    1650815232 driver id                               1 #bytes
        44 SQL*Net message from client    1650815232 driver id                               1 #bytes					
從上面的過程可以看到,大量的硬解析將導致嚴重的 library cache latch（mutex） 和 shared pool latch競爭。

6、檢視Library cache中sql情況
sys@ prod >select *
  2           from (select sql_id,child_number,child_latch,executions,sql_text
  3                      from v$sql
  4                  where sql_text like '%select * from test1 where col1%'
  5                       and sql_text not like '%v$sql%'
  6                       and sql_text not like '%begin%'
  7                   order by child_number desc)
  8*      where rownum <10
SQL_ID        CHILD_NUMBER CHILD_LATCH EXECUTIONS SQL_TEXT
------------- ------------ ----------- ---------- --------------------------------------------------
6tsrjxza4gvur         1987           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         1988           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         1989           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         1990           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         1991           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         1992           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         1993           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         1994           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         1995           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         1996           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         1997           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         1998           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         1999           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         2000           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         2001           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         2002           0          1 select * from test1 where col1=:"SYS_B_0"
6tsrjxza4gvur         2003           0          1 select * from test1 where col1=:"SYS_B_0"

11:46:50 SYS@ prod >select sql_id,hash_value,address,version_count from v$sqlarea where sql_id='6tsrjxza4gvur';
SQL_ID        HASH_VALUE ADDRESS  VERSION_COUNT
------------- ---------- -------- -------------
6tsrjxza4gvur 3561484119 2E8CF368          3885

     可以看到 SQL 的 Version_Count 很高,而且 V$SQL 檢視裡面也能查到對應的子游標。


案例分析：

模擬高併發下,對 Version Count 高 SQL 查詢:

  • session 1： 

  • 11:42:41 SYS@ prod >conn scott/tiger
Connected.
12:05:10 SCOTT@ prod >select * from v$mystat where rownum=1;
       SID STATISTIC#      VALUE
---------- ---------- ----------
        38          0          0

12:06:03 SCOTT@ prod >begin
12:06:23   2  for i in 1..500000 loop
12:06:23   3   execute immediate 'select * from test1 where col1=1';
12:06:23   4    end loop;
12:06:23   5    end;
12:06:23   6   /
PL/SQL procedure successfully completed.
  • session 2：
11:30:32 SYS@ prod >conn scott/tiger
Connected.
12:05:15 SCOTT@ prod >select * from v$mystat where rownum=1;
       SID STATISTIC#      VALUE
---------- ---------- ----------
        39          0          0

12:06:11 SCOTT@ prod >begin
12:06:29   2  for i in 1..500000 loop
12:06:29   3   execute immediate 'select * from test1 where col1=1';
12:06:29   4    end loop;
12:06:29   5    end;
12:06:29   6   /
PL/SQL procedure successfully completed.
  • 12:07:17 SYS@ prod >col event for a30
12:07:27 SYS@ prod >col p1text for a20
12:07:33 SYS@ prod >col p2text for a20
12:07:38 SYS@ prod >select sid,event,p1,p1text,p2,p2text from v$session where sid in (38,39)
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 library cache: mutex X         3561484119 idn                     2621440 value
        39 cursor: mutex S                3561484119 idn                     2490369 value
Elapsed: 00:00:00.00
12:07:38 SYS@ prod >/
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 library cache: mutex X         3561484119 idn                     2621440 value
        39 library cache: mutex X         3561484119 idn                     2490368 value
Elapsed: 00:00:00.00
12:07:43 SYS@ prod >/
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 library cache: mutex X         3561484119 idn                     2555904 value
        39 cursor: mutex S                3561484119 idn                     2490368 value
Elapsed: 00:00:00.00
12:07:45 SYS@ prod >/
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 latch: shared pool              537557404 address                     293 number
        39 library cache: mutex X         3561484119 idn                     2490368 value
Elapsed: 00:00:00.00
12:07:46 SYS@ prod >/
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 library cache: mutex X         3561484119 idn                     2621440 value
        39 library cache: mutex X         3561484119 idn                     2621440 value
Elapsed: 00:00:00.00
12:07:47 SYS@ prod >/
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 library cache: mutex X         3561484119 idn                     2621440 value
        39 cursor: mutex S                3561484119 idn                     2490368 value
Elapsed: 00:00:00.00
12:07:49 SYS@ prod >/
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 library cache: mutex X         3561484119 idn                     2621440 value
        39 library cache: mutex X         3561484119 idn                     2621440 value
Elapsed: 00:00:00.00
12:07:50 SYS@ prod >/
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 library cache: mutex X         3561484119 idn                     2621440 value
        39 library cache: mutex X         3561484119 idn                     2490368 value
Elapsed: 00:00:00.00
12:07:51 SYS@ prod >/
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 library cache: mutex X         3561484119 idn                     2555904 value
        39 cursor: mutex S                3561484119 idn                     2490368 value
Elapsed: 00:00:00.01
12:08:11 SYS@ prod >/
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 cursor: pin S                  3561484119 idn                     2555905 value
        39 library cache: mutex X         3561484119 idn                     2490368 value
Elapsed: 00:00:00.00
12:08:15 SYS@ prod >/
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 library cache: mutex X         3561484119 idn                     2621440 value
        39 library cache: mutex X              64028 idn                     2490368 value
12:09:04 SYS@ prod >/
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 cursor: mutex S                3561484119 idn                     2555904 value
        39 cursor: pin S                  3561484119 idn                     2490369 value
12:09:05 SYS@ prod >/
       SID EVENT                                  P1 P1TEXT                       P2 P2TEXT
---------- ------------------------------ ---------- -------------------- ---------- --------------------
        38 SQL*Net message from client    1650815232 driver id                     1 #bytes
        39 SQL*Net message from client    1650815232 driver id                     1 #bytes

  • 可以檢視到，在sql執行期間有大量的Library Cache latch（mutex）的競爭。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2154499/，如需轉載，請註明出處，否則將追究法律責任。

相關文章