溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
通過案例學調優之--和 SHARED POOL 相關的主要 Latch
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) 當客戶端執行一條 SQL,這時候 Oracle 首先將 SQL 文本轉換成 ASCII 值,然后根據 HASH函數計算該 SQL 對應的 Hash Value。
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) 根據獲得的執行計劃,開始執行 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）的競爭。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
