找出没有绑定变量的引发硬解析的SQL

select name,value from v$sysstat where name like 'parse%';

我的方法找出多次沒有綁定變量的sql：
select count exe_count,module,SQL_TEXT from (
select ROW_NUMBER()over(partition by substr(SQL_TEXT,1,50) order by SQL_TEXT) num,a.* from (
select count(*)over(partition by substr(SQL_TEXT,1,50)) count ,SQL_TEXT,
   SHARABLE_MEM,
   PERSISTENT_MEM,
   RUNTIME_MEM,
   SORTS,
   LOADED_VERSIONS,
   OPEN_VERSIONS,
   USERS_OPENING,
   FETCHES,
   EXECUTIONS,
   USERS_EXECUTING,
   LOADS,
   FIRST_LOAD_TIME,
   INVALIDATIONS,
   PARSE_CALLS,
   DISK_READS,
   BUFFER_GETS,
   ROWS_PROCESSED,
   COMMAND_TYPE,
   OPTIMIZER_MODE,
   OPTIMIZER_COST,
   PARSING_USER_ID,
   PARSING_SCHEMA_ID,
   KEPT_VERSIONS,
   ADDRESS,
   TYPE_CHK_HEAP,
   HASH_VALUE,
   PLAN_HASH_VALUE,
   CHILD_NUMBER,
   MODULE,
   MODULE_HASH,
   ACTION,
   ACTION_HASH,
   SERIALIZABLE_ABORTS,
   OUTLINE_CATEGORY,
   CPU_TIME,
   ELAPSED_TIME,
   OUTLINE_SID,
   CHILD_ADDRESS,
   SQLTYPE,
   REMOTE,
   OBJECT_STATUS,
   LITERAL_HASH_VALUE,
   LAST_LOAD_TIME,
   IS_OBSOLETE,
   CHILD_LATCH from v$sql) a
   where count>20    order by count desc
) b
where num=1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25583515/viewspace-711024/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25583515/viewspace-711024/