查询未使用绑定变量的SQL，硬解析SQL语句

查询未使用绑定变量的SQL，硬解析SQL语句

-- 查看oracle中未使用绑定变量的sql语句 
-- 原理：利用V$SQL 视图的 FORCE_MATCHING_SIGNATURE 字段可以识别可能从绑定变量或 CURSOR_SHARING 获益的 SQL 语句。
-- 如果 SQL 已使用绑定变量或者 CURSOR_SHARING ，那么 FORCE_MATCHING_SIGNATURE 在对其进行标识时将给出同样的签名。
-- 换句话说，如果两个 SQL语句除了字面量的值之外都是相同的，它们将拥有相同的 FORCE_MATCHING_SIGNATURE ，
-- 这意味着如果为它们提供了绑定变量或者CURSOR_SHARING ，它们就成了完全相同的语句。
-- 所以，使用 FORCE_MATCHING_SIGNATURE 字段可以识别没有使用绑定变来的 SQL 语句。

-- Oracle在解析SQL语句的时候，如果在共享池中发现匹配的SQL语句，就可以避免掉解析的大部分开销。
-- 在共享池中找到匹配的SQL语句所对应的解析被称为软解析(soft parse)。如果没有找到匹配的SQL语句，则必须进行硬解析（hard parse）。
-- 硬解析不仅耗费CPU时间，在有大量会话想要同时缓存SQL语句到共享池时还会造成争用。通过使用绑定变量，可以最小化解析的代价。

-- 1.CURSOR_SHARING参数
-- 该参数转换SQL语句中的字面值到绑定变量。转换值提高了游标共享，且可能会影响SQL语句的执行计划。优化器是基于绑定变量的存在生成执行计划，而不是实际字面量值。
-- CURSOR_SHARING决定什么类型的SQL语句可以共享相同的游标。CURSOR_SHARING参数有三个值：

-- FORCE：只要有可能，字面量就会被替换为绑定变量。
-- SIMILAR：只有当替换不会影响到执行计划时，才会将字面量替换为绑定变量
-- EXACT：这是默认值。不将字面量替换为绑定变量。
-- 注意：不推荐修改CURSOR_SHARING参数的默认值。如果实在无法修改现有应用的代码，可以通过设置CURSOR_SHARING参数来指示Oracle透明地将字面量替换为绑定变量。
复制

set line 300 pages 90
col SQL_TEXT for a90
col PARSING_SCHEMA_NAME for a10
set numw 19
col RANKING for 99999


with force_mathces as
 (select l.force_matching_signature mathces,
          max(l.sql_id || l.child_number) max_sql_child,
          dense_rank() over(order by count(*) desc) ranking,count(*) a 
     from v$sql l
    where l.force_matching_signature <> 0
      and l.parsing_schema_name <> 'SYS'
    group by l.force_matching_signature
   having count(*) > 10)
 select v.sql_id, v.sql_text, v.parsing_schema_name, fm.mathces, fm.ranking,v.force_matching_signature,fm.a
   from force_mathces fm, v$sql v
  where fm.max_sql_child = (v.sql_id || v.child_number)
    and fm.ranking <= 200
  order by fm.ranking;
复制