如何查找sql运行的次数、涉及的记录行数、绑定变量的值等

最新推荐文章于 2024-08-27 09:40:52 发布

liuzhilongDBA

最新推荐文章于 2024-08-27 09:40:52 发布

阅读量3k

点赞数 1

分类专栏： ORACLE 文章标签：查找sql执行次数查找sql删除行数查找sql的绑定变量如何找到删除记录的所有sql 如何找到执行sql的用户

无版权，随便玩儿

本文链接：https://blog.csdn.net/qq_40687433/article/details/79246638

版权

ORACLE 专栏收录该内容

175 篇文章 4 订阅

订阅专栏

在查找sql运行信息之前，需要理解和区分几个sql视图和他们之间的关系：

1.v$sql:因为定义语句中没有group by子句，1个child cursor就是一条记录，所以一条sql可能会有多条记录

2.v$sqlarea:v$sqlarea 没有dba_hist_sqlarea视图,v$sqlarea存放了sql的基本信息和部分运行信息.针对sql语句研究,使用v$sqlarea比v$sql更直观
3.v$sqlstats:v$sqlstats视图展示了仍然在内存中保留的sql， v$sqlstats保留了sql的运行信息，比如逻辑读，物理读，cpu_time,elapse_time等等
4.dba_hist_sqlstat（没有s）:没有在内存中的sql的历史运行信息保存在dba_hist_sqlstats中， v$sqlstats没有bind_data字段，dba_hist_sqlstat,v$sqlarea有bind_data字段
上述视图字段较多，v$sqlarea在12c中就有87个字段，所以需要理解视图的构成目的和视图之间的关系，想要熟练使用就需要大量的练习

5.v$sqltext,dba_hist_sqltext:视图字段较少，但是只有sql_text字段，没有sql_fulltext字段，sql_text只记录部分sql语句（VARCHAR2(64)）,v$sqlarea记录了sql_fulltext(clob);

6.v$sqlcommand:查看命令类型，如select * from v$sqlcommand where command_name='DELETE';

COMMAND_TYPE COMMAND_NAME
------------ ----------------------------------------------------------------
7 DELETE

查找在2018年1月31日后在表table1上执行delete操作的sql有多少条

1.需要查询v$sqlarea视图

2.字段LAST_ACTIVE_TIME和LAST_LOAD_TIME是有区别的。

LAST_ACTIVE_TIME：相当于是sql最后一次执行时间

LAST_LOAD_TIME：sql最后一次载入内存的时间

3.PARSING_SCHEMA_NAME字段为sql解析schema，可以group by找到用户

SQL> select PARSING_SCHEMA_NAME,count(*) from v$sqlarea where to_char(LAST_ACTIVE_TIME,'yyyymmdd')>='20180131' and sql_fulltext like '%TABLE1%' and command_type=7 group by PARSING_SCHEMA_NAME;

PARSING_SCHEMA_NAME COUNT(*)
------------------------------ ----------
UOP_AAA 97

查找在2018年1月31日后在表table1上执行delete操作的语句是哪些

SQL> select sql_id,sql_text from v$sqlarea where to_char(LAST_ACTIVE_TIME,'yyyymmdd')>='20180131' and sql_fulltext like '%TABLE1%' and command_type=7 and rownum<=2 ;

SQL_ID SQL_TEXT
------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
b7kc5asdfms008 DELETE FROM TABLE1 WHERE EVENT_CODE IN (xxxx)
52zasdfv3s1s01d DELETE FROM TABLE1 WHERE EVENT_CODE IN (yyyy)

查找2018年1月31日后在表table1上delete的语句执行了多少次

executions字段表示sql执行次数

SQL> select sum(EXECUTIONS),max(EXECUTIONS) from v$sqlarea where to_char(LAST_LOAD_TIME,'yyyymmdd')>='20180131' and sql_fulltext like'%TI_F_MDBADD_TRADE%' and command_type=7;

SUM(EXECUTIONS) MAX(EXECUTIONS)
--------------- ---------------
8279 2

查找2018年1月31日后在表table1上delete的记录行数

select sum(ROWS_PROCESSED),max(ROWS_PROCESSED) from v$sqlarea where to_char(LAST_ACTIVE_TIME,'yyyymmdd')>='20180131' and sql_fulltext like '%TI_F_MDBADD_TRADE%' and command_type=7 group by PARSING_SCHEMA_NAME;

SUM(ROWS_PROCESSED) MAX(ROWS_PROCESSED)
------------------- -------------------
33503 78

查找2018年1月31日后在表table1上delete的语句在每个snap，session中的执行次数

1.提前通过dba_hist_snapshot找到snap_id缩小范围，优化查询语句，减少执行时间

SQL> select snap_id,begin_interval_time from dba_hist_snapshot where to_char(begin_interval_time,'yyyymmdd hh24mi')='20180131 0000'
2 /

SNAP_ID BEGIN_INTERVAL_TIME
---------- ---------------------------------------------------------------------------
27650 31-JAN-18 12.00.21.624 AM
27650 31-JAN-18 12.00.21.818 AM
27650 31-JAN-18 12.00.21.763 AM

2.在where条件中加入snap_id

SQL > select snap_id,session_id,count(*) from dba_hist_active_sess_history where snap_id>=27650 and sql_id in (select sql_id from v$sqlarea where to_char(LAST_ACTIVE_TIME,'yyyymmdd')>='20180131' and sql_fulltext like '%TABLE1%' and command_type=7)
group by snap_id,session_id order by 1,2 desc;

查看sql的绑定变量值

select sql_id,
dbms_sqltune.extract_bind(bind_data,1).value_string bind1,
dbms_sqltune.extract_bind(bind_data,2).value_string bind2
from v$sqlarea where sql_id='99d6puxf9000g';

SQL_ID BIND1 BIND2
------------- ---------- ----------
99d6puxf9000g 188330

也可以把v$sqlarea替换成dba_hist_sqlstat来查看历史sql的绑定变量

还有很多可以查询的信息，比如elapsed time，consistent read就经常使用，在此就列举到这。理解了这些视图以后，列举出字段，就可以找到我们想要的数据。

其实以上查询运行情况的sql并不严谨，因为在v$sqlarea查询的数据是仍然保存在内存中的sql，如果有sql恰好被唤出，那么我们这种查询方式就查不到了。

问题的根本原因是我们需要使用v$sqlarea中的fulltext字段，比如找到delete表的表名，dba_hist_sqlstat和dba_hist_sqltext中都没有fulltext，这必然会导致我们查询的遗漏。除非我们不关心fulltext，直接从dba_hist_sqlstat中就可以找到我们想要的。

如果需要严谨的查询sql的运行，最好的方式是去挖日志。