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

63次阅读
没有评论

共计 6167 个字符,预计需要花费 16 分钟才能阅读完成。

这篇文章主要介绍了 oracle 如何查找一段时间内的非绑定变量 sql,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获,下面让丸趣 TV 小编带着大家一起了解一下。

查找一段时间内的非绑定变量 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 了。

感谢你能够认真阅读完这篇文章,希望丸趣 TV 小编分享的“oracle 如何查找一段时间内的非绑定变量 sql”这篇文章对大家有帮助,同时也希望大家多多支持丸趣 TV,关注丸趣 TV 行业资讯频道,更多相关知识等着你来学习!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-24发表,共计6167字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)