先看下什么是v$sqlarea 表结构

SQL> desc v$sqlarea
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SQL_TEXT                                           VARCHAR2(1000)
 SQL_FULLTEXT                                       CLOB
 SQL_ID                                             VARCHAR2(13)
 SHARABLE_MEM                                       NUMBER
 PERSISTENT_MEM                                     NUMBER
 RUNTIME_MEM                                        NUMBER
 ... ...
 EXACT_MATCHING_SIGNATURE                           NUMBER  Signature used when the CURSOR_SHARING parameter is set to EXACT
 FORCE_MATCHING_SIGNATURE                           NUMBER  Signature used when the CURSOR_SHARING parameter is set to FORCE
 LAST_ACTIVE_TIME                                   DATE
 BIND_DATA                                          RAW(2000)
 CON_ID                                             NUMBER
 IS_REOPTIMIZABLE                                   VARCHAR2(1)
 IS_RESOLVED_ADAPTIVE_PLAN                          VARCHAR2(1)
SQL> 

做为一个DBA，你大概习惯了定期要抓取数据库中的非绑定变量SQL，这些SQL经常扮演着一箱苹果中蛀虫的角色。
看到下列SQL你必定觉得眼熟:

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值。这么说可能不太形象，我们来具体看一下:

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的脚本：

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!

今天的案例

如下awr，显示library cache:bucket mutex X很高

解析和硬解析时间都很高

看下绑定变量情况

通过设置cursor_sharing= force 临时解决

SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination      string      memory+disk
cursor_invalidation                  string      IMMEDIATE
cursor_sharing                       string      FORCE
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     1500
session_cached_cursors               integer     300