通过案例学调优之--和 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 CacheLatch,直到找到对应的执行计划。然后释放 Latch。(软解析)
(N) 如果不存在,就要去 SHARE POOL 里面获得可用空间,来生生成对应的 LibraryCache 对象。这个时候就要获得 Shared Pool Latch 在 SHARE POOL 的 Free Lis(SHRAEPOOL 通过 Free List 管理 Free Chunk)查找可用的空间,之后释放 Shared Pool Latch。接下来就开始进行硬解析过程,将执行解析后的执行计划等信息记录到 LibraryCache 中,这个整个过程消耗大量的 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,也就会重新硬解析一次。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
|
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：
-
1234567891011121314
11:42:41SYS@ prod >conn scott/tigerConnected.12:05:10SCOTT@ prod >select *fromv$mystatwhererownum=1;SID STATISTIC# VALUE---------- ---------- ----------380012:06:03SCOTT@ prod >begin12:06:232foriin1..500000loop12:06:233execute immediate'select * from test1 where col1=1';12:06:234end loop;12:06:235end;12:06:236/PL/SQL procedure successfully completed. -
123456789101112131415
session2：11:30:32SYS@ prod >conn scott/tigerConnected.12:05:15SCOTT@ prod >select *fromv$mystatwhererownum=1;SID STATISTIC# VALUE---------- ---------- ----------390012:06:11SCOTT@ prod >begin12:06:292foriin1..500000loop12:06:293execute immediate'select * from test1 where col1=1';12:06:294end loop;12:06:295end;12:06:296/PL/SQL procedure successfully completed. -
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778
12:07:17SYS@ prod >col eventfora3012:07:27SYS@ prod >col p1textfora2012:07:33SYS@ prod >col p2textfora2012:07:38SYS@ prod >select sid,event,p1,p1text,p2,p2textfromv$sessionwheresidin(38,39)SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38library cache: mutex X3561484119idn2621440value39cursor: mutex S3561484119idn2490369valueElapsed:00:00:00.0012:07:38SYS@ prod >/SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38library cache: mutex X3561484119idn2621440value39library cache: mutex X3561484119idn2490368valueElapsed:00:00:00.0012:07:43SYS@ prod >/SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38library cache: mutex X3561484119idn2555904value39cursor: mutex S3561484119idn2490368valueElapsed:00:00:00.0012:07:45SYS@ prod >/SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38latch: shared pool537557404address293number39library cache: mutex X3561484119idn2490368valueElapsed:00:00:00.0012:07:46SYS@ prod >/SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38library cache: mutex X3561484119idn2621440value39library cache: mutex X3561484119idn2621440valueElapsed:00:00:00.0012:07:47SYS@ prod >/SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38library cache: mutex X3561484119idn2621440value39cursor: mutex S3561484119idn2490368valueElapsed:00:00:00.0012:07:49SYS@ prod >/SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38library cache: mutex X3561484119idn2621440value39library cache: mutex X3561484119idn2621440valueElapsed:00:00:00.0012:07:50SYS@ prod >/SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38library cache: mutex X3561484119idn2621440value39library cache: mutex X3561484119idn2490368valueElapsed:00:00:00.0012:07:51SYS@ prod >/SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38library cache: mutex X3561484119idn2555904value39cursor: mutex S3561484119idn2490368valueElapsed:00:00:00.0112:08:11SYS@ prod >/SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38cursor: pin S3561484119idn2555905value39library cache: mutex X3561484119idn2490368valueElapsed:00:00:00.0012:08:15SYS@ prod >/SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38library cache: mutex X3561484119idn2621440value39library cache: mutex X64028idn2490368value12:09:04SYS@ prod >/SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38cursor: mutex S3561484119idn2555904value39cursor: pin S3561484119idn2490369value12:09:05SYS@ prod >/SID EVENT P1 P1TEXT P2 P2TEXT---------- ------------------------------ ---------- -------------------- ---------- --------------------38SQL*Net messagefromclient1650815232driver id1#bytes39SQL*Net messagefromclient1650815232driver id1#bytes -
可以查看到，在sql运行期间有大量的Library Cache latch（mutex）的竞争。
本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1575964
扫一扫
1116
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
