oracle查找一段时间内的非绑定变量的sql

查找一段时间内的非绑定变量sql的方法 v$SQL动态性能视图中的FORCE_MATCHING_SIGNATURE列，可以帮助我们快速定位到非绑定变量的sql。该列的含义是"The signature used when the CURSOR_SHARING parameter is set to FORCE".意思就是如果将CURSOR_SHARING参数设置为force，sql文本在该参数下计算得到一个signature值。具有相同signature值的sql，oracle认为是可以通过绑定变量的办法共享游标，减小硬解析的。 查询一段时间内的非绑定变量sql 当数据库出现硬解析的时候，也可以用下面的语句去抓问题sql 也可以把时间约束条件去掉，单纯的找数据库所有时段的非绑定变量sql。 select * from v$sql where FORCE_MATCHING_SIGNATURE in (select a.FORCE_MATCHING_SIGNATURE from ( select to_char(FORCE_MATCHING_SIGNATURE) FORCE_MATCHING_SIGNATURE,        count(1) counts   from v$sql  where FORCE_MATCHING_SIGNATURE > 0    and FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE    and EXECUTIONS <= 5    and to_date(last_load_time, 'yyyy-mm-dd/hh24:mi:ss') between        to_date('20180816 00:00:00', 'yyyy-mm-dd/hh24:mi:ss') and        to_date('20180816 01:00:00', 'yyyy-mm-dd/hh24:mi:ss')  group by FORCE_MATCHING_SIGNATURE having count(1) > 100  order by 2 desc) a) ; 可以用上面的sql换掉之前老旧的 SELECT substr(sql_text, 1, 60), count(1)   FROM v$sql  GROUP BY substr(sql_text, 1, 60) HAVING count(1) > 10  ORDER BY 2; 这条sql的思路基本就是截取where条件之前的sql文本，因为可共享的sql不同之处就在于where条件上。 还有一个脚本，但是那个脚本执行速度缓慢，执行结果可读性差，like this SQL> @find_literal.sql Literal:select null from optstat_hist_control$              where sn address: 0000000082C6AA78 Literal:select sd.inst_id, ts.tsnam, segment_file, segment_block, ex address: 0000000085ADE880 Literal:SELECT S.SCHEMA, S.QUEUE_ID, S.SUBSCRIBER_ID, S.QUEUE_NAME,  address: 0000000080198268 Literal:SELECT  /*+ qb_name(gv_ashv) no_merge ordered use_nl(s,a) */ address: 0000000085B690F0 Literal:select  NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, DEFAULT_V address: 000000007845B490 Literal:select count(*) from undo$ address: 00000000839B7D30 Literal:select inst_id,          kqlfxpl_phad,          kqlfxpl_hash address: 0000000070CB2458 Literal:select tsn, tsv from x$ktfbnstat where flag = 1 address: 0000000083FDFBC0 Literal:select FORCE_MATCHING_SIGNATURE, count(1)   from v$sql  wher address: 000000006AC3DD48 Literal:SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) * address: 0000000080E6E4E8 Literal:select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksuse address: 000000007AA44288 Literal:select /*test*/ * from t_flash where OBJECT_ID=23708 address: 0000000076DCE188 Literal:select max(FA#) from SYS_FBA_FA address: 0000000087A37A90 Literal:SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER(' address: 00000000792A3F20 Literal:select sid, db_sid, serial#, con_id from gv$xs_sessions  whe address: 00000000675FF790 Literal:select count(*) from SYS.chnf$_reg_queries address: 0000000087B8B7E8 Literal:SELECT DECODE('A','A','1','2') FROM SYS.DUAL address: 0000000077B63FE8 Literal:select sql_id,child_number,open_versions,PARSE_CALLS,IS_OBSO address: 0000000072F2B578 Literal:SELECT INST_ID, USERID, OBJID, ID_TYPE, NAME,         DECODE address: 0000000065CE9BB8 Literal:select /*test*/ * from t_flash where OBJECT_ID=23731 address: 00000000736ED5B0 Literal:select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdv address: 0000000069A7C248 Literal:select sum(used_blocks), ts.ts#   from GV$SORT_SEGMENT gv, t address: 0000000083FC9870 Literal:SELECT /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false')  address: 0000000082CEC6C0 Literal:SELECT DECODE(USER, 'XS$NULL',  XS_SYS_CONTEXT('XS$SESSION', address: 0000000065184210 Literal:select ts#, inc# from ts$ where online$=1 and bitand(flags,1 address: 0000000083FD0918 Literal:SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE F address: 000000006B90E120 Literal:select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SI address: 000000008784F698 Literal:select /*test*/ * from t_flash where OBJECT_ID='23731' address: 00000000619F2018 Literal:select max(scn) from smon_scn_time address: 000000008399B9C8 Literal:SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) * address: 0000000080F15FC8 Literal:select inst_id, sessid, dbsessnum, dbsernum, con_id from x$x address: 000000007BF4F150 Literal:SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) * address: 0000000080E25790 Literal:select sql_text from v$sql where FORCE_MATCHING_SIGNATURE in address: 00000000675B9180 Literal:select  decode(u.type#, 2, u.ext_username, u.name), o.name,  address: 00000000839D58F8 Literal:select ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksuxsver,ksuxst address: 000000007799CD88 Literal:select sql_id,sql_text,child_number,open_versions,PARSE_CALL address: 00000000709E5730 PL/SQL procedure successfully completed. 实验过程 SQL> conn ming/oracle@mingpdb1 Connected. SQL> sho user con_name USER is "MING" CON_NAME ------------------------------ MINGPDB1 alter system flush shared_pool; SQL> sho parameter cursor_shar NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ cursor_sharing                       string      EXACT 执行： select /*test*/ * from t_flash where OBJECT_ID=23731; select /*test*/ * from t_flash where OBJECT_ID=23708; select /*test*/ * from t_flash where OBJECT_ID='23731'; set line 300 col SQL_TEXT for a55 col FORCE_MATCHING_SIGNATURE for 999999999999999999999 col EXACT_MATCHING_SIGNATURE for 999999999999999999999 select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE   FROM V$SQL  WHERE sql_text like '%test%'    and sql_text not like '%like%'; SQL_TEXT                                                FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE ------------------------------------------------------- ------------------------ ------------------------ select /*test*/ * from t_flash where OBJECT_ID='23731'      13459100552049599574     16467051488950643767 select /*test*/ * from t_flash where OBJECT_ID=23731        13459100552049599574      5586102026751624810 select /*test*/ * from t_flash where OBJECT_ID=23708        13459100552049599574       636726165116306616 可以看到涉及到隐式转换以及不同的值的sql，oracle认为都是可以通过设置CURSOR_SHARING为force解决游标不能共享的问题的。 实际生产上，count(*)大于的数需要改的大一点。 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) > 1  order by 2; FORCE_MATCHING_SIGNATURE   COUNT(1) ------------------------ ----------     13389370700329599909          2     13459100552049599574          3 根据上面结果继续查找： SQL> select sql_text from v$sql where FORCE_MATCHING_SIGNATURE in ('13389370700329599909','13459100552049599574'); SQL_TEXT ------------------------------------------------------- SELECT SYS_CONTEXT('USERENV','cdb_name') FROM DUAL select /*test*/ * from t_flash where OBJECT_ID='23731' select /*test*/ * from t_flash where OBJECT_ID=23731 select /*test*/ * from t_flash where OBJECT_ID=23708 SELECT SYS_CONTEXT('USERENV','con_id') FROM DUAL 13459100552049599574对应的3，就是实验中发起的三条sql了。