做为一个DBA，你大概习惯了定期要抓取数据库中的非绑定变量SQL，这些SQL经常扮演着一箱苹果中蛀虫的角色。
看到下列SQL你必定觉得眼熟:
|
1
2
3
4
5
|
SELECT
substr(sql_text, 1, 80),
count
(1)
FROM
v$sql
GROUP
BY
substr(sql_text, 1, 80)
HAVING
count
(1) > 10
ORDER
BY
2
|
是的，以上这段抓取literal sql的脚本大约从8i时代就开始流行了，在那时它很popular也很休闲，使用它或许还会给你的雇主留下一丝神秘感。不过今天我要告诉你的是，它彻底过时了，落伍了，已经不是fashion master了。
10g以后v$SQL动态性能视图增加了FORCE_MATCHING_SIGNATURE列，其官方定义为”The signature used when the CURSOR_SHARING parameter is set to FORCE”，也就是Oracle通过将原SQL_TEXT转换为可能的FORCE模式后计算得到的一个SIGNATURE值。这么说可能不太形象，我们来具体看一下:
|
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
|
SQL>
create
table
YOUYUS (t1
int
);
Table
created.
SQL>
alter
system flush shared_pool;
System altered.
SQL>
select
/*test_matching_a*/ *
from
YOUYUS
where
t1=1;
no
rows
selected
SQL>
select
/*test_matching_a*/ *
from
YOUYUS
where
t1=2;
no
rows
selected
SQL>
select
/*test_matching_a*/ *
from
YOUYUS
where
t1=3;
no
rows
selected
SQL> col sql_text format a55;
SQL>
select
sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2
FROM
V$SQL
3
WHERE
sql_text
like
'%test_matching_a%'
4
and
sql_text
not
like
'%like%'
;
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
------------------------------------------------------- ------------------------ ------------------------
select
/*test_matching_a*/ *
from
YOUYUS
where
t1=2 4.59124694481197E18 1.00267830752731E19
select
/*test_matching_a*/ *
from
YOUYUS
where
t1=3 4.59124694481197E18 1.61270448861426E19
select
/*test_matching_a*/ *
from
YOUYUS
where
t1=1 4.59124694481197E18 1.36782048270058E18
/*以上将变量硬编码至SQL中的游标，FORCE_MATCHING_SIGNATURE值完全相同，而EXACT_MATCHING_SIGNATURE值各有不同。FORCE_MATCHING_SIGNATURE值相同说明在游标共享
FORCE
模式下，这些游标满足
CURSOR
SHARING的条件 */
SQL>
alter
system flush shared_pool;
System altered.
SQL>
alter
session
set
cursor_sharing=
FORCE
;
Session altered.
SQL>
select
/*test_matching_a*/ *
from
YOUYUS
where
t1=1;
no
rows
selected
SQL>
select
/*test_matching_a*/ *
from
YOUYUS
where
t1=2;
no
rows
selected
SQL>
select
/*test_matching_a*/ *
from
YOUYUS
where
t1=3;
no
rows
selected
SQL> col sql_text
for
a70
SQL>
select
sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2
FROM
V$SQL
3
WHERE
sql_text
like
'%test_matching_a%'
4
and
sql_text
not
like
'%like%'
;
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
---------------------------------------------------------------------- ------------------------ ------------------------
select
/*test_matching_a*/ *
from
YOUYUS
where
t1=:
"SYS_B_0"
4.59124694481197E18 4.59124694481197E18
/*
FORCE
模式下将SQL文本中的变量值转换成了:SYS_B形式，EXACT_MATCHING_SIGNATURE也随之等同于FORCE_MATCHING_SIGNATURE了*/
|
以上演示说明了FORCE_MATCHING_SIGNATURE列可以帮助我们找出那些潜在可以共享的游标(也包括了因非绑定问题造成的游标无法共享)，现在我们利用它来完善捕获非绑定变量SQL的脚本：
|
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
|
SQL>
alter
system flush shared_pool;
System altered.
SQL>
select
/*test_matching_b*/ *
from
YOUYUS
where
t1=1;
no
rows
selected
SQL>
select
/*test_matching_b*/ *
from
YOUYUS
where
t1=
'1'
; //我有引号，我与众不同！
no
rows
selected
SQL> col sql_text
for
a70
SQL>
select
sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2
FROM
V$SQL
3
WHERE
sql_text
like
'%test_matching_b%'
4
and
sql_text
not
like
'%like%'
;
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
---------------------------------------------------------------------- ------------------------ ------------------------
select
/*test_matching_b*/ *
from
YOUYUS
where
t1=
'1'
1.43666633406896E19 1.83327833675856E19
select
/*test_matching_b*/ *
from
YOUYUS
where
t1=1 1.43666633406896E19 8.05526057286178E18
/*多余的引号也会导致游标无法共享，此时的FORCE_MATCHING_SIGNATURE 也会是一致的*/
select
FORCE_MATCHING_SIGNATURE,
count
(1)
from
v$sql
where
FORCE_MATCHING_SIGNATURE > 0
and
FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group
by
FORCE_MATCHING_SIGNATURE
having
count
(1) > &a
order
by
2;
Enter value
for
a: 10
old 6:
having
count
(1) > &a
new 6:
having
count
(1) > 10
FORCE_MATCHING_SIGNATURE
COUNT
(1)
------------------------ ----------
8.81463386552502E18 12
So We find it!
|
在这里再推荐一种来自MOS，find Literal SQL的方法:
How to Find Literal SQL in Shared Pool
Applies to:
PL/SQL – Version: 8.1.7 to 10.2
Information in this document applies to any platform.Goal
There is no direct way to query the dictionary for literal SQL only.
However the following example will try to exclude all SQL statements in the
shared pool that do use bind variables.There still might be situations, with statements using subqueries, where the
example still will show SQL statements using bind variables.Solution
Create the following PL/SQL block:
|
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
|
[maclean@rh2 bin]$ cat find_literal.sql
set
serveroutput
on
set
linesize 120
--
-- This anonymous PL/SQL block must be executed as INTERNAL or SYS
-- Execute from : SQL*PLUS
-- CAUTION:
-- This sample program has been tested on Oracle Server - Enterprise Edition
-- However, there is no guarantee of effectiveness because of the possibility
-- of error in transmitting or implementing it. It is meant to be used as a
-- template, and it may require modification.
--
declare
b_myadr VARCHAR2(20);
b_myadr1 VARCHAR2(20);
qstring VARCHAR2(100);
b_anybind NUMBER;
cursor
my_statement
is
select
address
from
v$sql
group
by
address;
cursor
getsqlcode
is
select
substr(sql_text,1,60)
from
v$sql
where
address = b_myadr;
cursor
kglcur
is
select
kglhdadr
from
x$kglcursor
where
kglhdpar = b_myadr
and
kglhdpar != kglhdadr
and
kglobt09 = 0;
cursor
isthisliteral
is
select
kkscbndt
from
x$kksbv
where
kglhdadr = b_myadr1;
begin
dbms_output.enable(10000000);
open
my_statement;
loop
Fetch
my_statement
into
b_myadr;
open
kglcur;
fetch
kglcur
into
b_myadr1;
if kglcur%FOUND
Then
open
isthisliteral;
fetch
isthisliteral
into
b_anybind;
if isthisliteral%NOTFOUND
Then
open
getsqlcode;
fetch
getsqlcode
into
qstring;
dbms_output.put_line(
'Literal:'
||qstring||
' address: '
||b_myadr);
close
getsqlcode;
end
if;
close
isthisliteral;
end
if;
close
kglcur;
Exit
When
my_statement%NOTFOUND;
End
loop;
close
my_statement;
end
;
/
/*尝试执行*/
SQL> @find_literal
Literal:
select
inst_id, java_size, round(java_size / basejava_size, address: 00000000BC6E94E8
Literal:
select
reason_id, object_id, subobject_id, internal_instance address: 00000000BC5F1D60
Literal:
select
DBID,
NAME
, CREATED, RESETLOGS_CHANGE#, RESETLOGS_TI address: 00000000BC6000B0
Literal:
select
di.inst_id,di.didbi,di.didbn,to_date(di.dicts,
'MM/DD/ address: 00000000BC530DA8
Literal: declare vsn varchar2(20); begin address: 00000000BC85A9F8
Literal:SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIO address: 00000000BC829978
Literal:select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where address: 00000000BCA84D00
Literal:select SYS_CONTEXT('
USERENV
', '
SERVER_HOST
'), SYS_CONTEXT('
U address: 00000000BC771BF0
Literal:
select
sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_S address: 00000000BC4673A8
Literal:
select
streams_pool_size_for_estimate s, streams_p address: 00000000BCA58848
Literal:
select
open_mode
from
v$
database
address: 00000000BC5DF2D0
Literal:
select
FORCE_MATCHING_SIGNATURE,
count
(1)
from
v$sql wher address: 00000000BCA91628
Literal:
select
inst_id, tablespace_name, segment_file, segment_block address: 00000000BC66EF38
Literal:
select
sum
(used_blocks), ts.ts#
from
GV$SORT_SEGMENT gv, t address: 00000000BCAA01B0
Literal:
BEGIN
DBMS_OUTPUT.ENABLE(
NULL
);
END
; address: 00000000BC61D2D8
Literal:
select
value$
from
props$
where
name
=
'GLOBAL_DB_NAME'
address: 00000000BC570500
Literal:
select
count
(*)
from
sys.job$
where
(next_date > sysdate) an address: 00000000BC6C53F8
Literal:
select
java_pool_size_for_estimate s, java_pool_si address: 00000000BCA65070
Literal:
select
local_tran_id, global_tran_fmt, global_oracle_id, glo address: 00000000BC5900B8
Literal:
select
inst_id,kglnaobj,kglfnobj,kglobt03, kglobhs0+kglobhs1 address: 00000000BC921538
Literal:
select
o.owner#,o.
name
,o.namespace,o.remoteowner,o.linkname, address: 00000000BCA83E90
Literal:
SELECT
*
FROM
V$SQL address: 00000000BCA58BC0
Literal:
SELECT
ADDRESS
FROM
V$SQL
GROUP
BY
ADDRESS address: 00000000BC565BE8
Literal:
begin
dbms_rcvman.resetAll;
end
; address: 00000000BC759858
Literal:
declare
b_myadr VARCHAR2(20); b_myadr1 VARCHAR2(20); qstring address: 00000000BC928FF8
Literal:
select
/*+
rule
*/ bucket_cnt, row_cnt, cache_cnt, null_cnt, address: 00000000BC898BF8
Literal:
select
CONF#,
NAME
, VALUE
from
GV$RMAN_CONFIGURATION
where
i address: 00000000BC8CB7F8
Literal:
select
f.file#, f.block#, f.ts#, f.length
from
fet$ f, ts$ t address: 00000000BC8CDFE8
Literal:
select
u.
name
, o.
name
,
trigger
$.sys_evts,
trigger
$.type# fr address: 00000000BCA877B8
Literal:
select
id,
name
, block_size, advice_status, address: 00000000BC636B38
Literal:
select
incarnation#, resetlogs_change#, resetlogs_time, address: 00000000BCA94250
Literal:
select
INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSIO address: 00000000BC62A678
Literal:
select
ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksuxsver,ksuxst address: 00000000BC8E5440
Literal:
select
timestamp
, flags
from
fixed_obj$
where
obj#=:1 address: 00000000BC916C78
Literal:
select
size_for_estimate, size_factor * address: 00000000BCA5F830
Literal:
select
shared_pool_size_for_estimate s, shared_pool address: 00000000BCA5A350
Literal:
select
SQL_TEXT , SQL_FULLTEXT , SQL_ID, SHARABLE_MEM , PE address: 00000000BC76B3A0
Literal:lock
table
sys.col_usage$
in
exclusive mode nowait address: 00000000BCA05978
Literal:
select
'x'
from
dual address: 00000000BC583818
Literal:
select
name
, resetlogs_time, resetlogs_ch address: 00000000BCA9D430
Literal:
select
inst_id, sp_size, round(sp_size / basesp_size, 4), k address: 00000000BC65A9F0
Literal:
select
userenv(
'Instance'
), icrid, to_number(icrls), address: 00000000BC692260
Literal:
select
shared_pool_size_for_estimate, shared_pool_size_facto address: 00000000BCAE0750
Literal:
select
INST_ID, RMRNO, RMNAM, RMVAL
from
X$KCCRM
where
RMNAM address: 00000000BC8CD778
Literal:
select
metadata
from
kopm$
where
name
=
'DB_FDO'
address: 00000000BC9EBB98
Literal:
select
java_pool_size_for_estimate, java_pool_size_factor, address: 00000000BC5B27D0
Literal:
SELECT
INCARNATION#, INCARNATION#, RESETLOGS_CHANGE#, RESETL address: 00000000BC829C48
Literal:
select
file#
from
file$
where
ts#=:1 address: 00000000BC87CF18
Literal:
select
A.inst_id, A.bpid, B.bp_name, A.blksz, address: 00000000BC802248
Literal:lock
table
sys.mon_mods$
in
exclusive mode nowait address: 00000000BC5CBE68
Literal:lock
table
sys.mon_mods$
in
exclusive mode nowait address: 00000000BC5CBE68
|
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1278974
