oracle如何查找一段時間內的非綁定變量sql

這篇文章主要介紹了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/hh34:mi:ss') between
       to_date('20180816 00:00:00', 'yyyy-mm-dd/hh34:mi:ss') and
       to_date('20180816 01:00:00', 'yyyy-mm-dd/hh34: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了。

感謝你能夠認真閱讀完這篇文章，希望小編分享的“oracle如何查找一段時間內的非綁定變量sql”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!