如何查询Oracle数据库一周内每天的SQL执行次数

一、问题:

今天引入的问题是:oracle数据库怎么查询一周内,每天的查询次数?

正好这周在学数据库的调优工作,我记得数据库的AWR报告会记录SQL的执行情况,可以从DBA_HIST_SNAPSHOT里面找出7天的快照ID,在DBA_HIST_SQLSTAT收集一下。

先来了解一下相关视图

二、相关视图:

dba_hist_sqlstat 和dba_hist_snapshot视图是 Oracle AWR(Automatic Workload Repository)的一部分。其中,

1、DBA_HIST_SQLSTAT

dba_hist_sqlstat是Oracle数据库中的历史SQL统计信息视图,用于提供有关SQL语句执行的历史性能信息。它记录了SQL语句的执行计划、执行时间、消耗的资源等统计数据。dba_hist_sqlstat可以用于监控和分析数据库中的SQL性能问题。

常用字段:

SQL_ID:SQL语句的唯一标识符。

SNAP_ID:快照ID,表示采样的时间点。

DBID:数据库ID。

INSTANCE_NUMBER:实例编号。

PLAN_HASH_VALUE:SQL执行计划的哈希值。

2、DBA_HIST_SNAPSHOT

dba_hist_snapshot是Oracle数据库中的动态视图,用于提供有关历史性能快照的信息。它记录了数据库在不同时间点的性能指标和统计数据。dba_hist_snapshot可以用于分析数据库的性能变化和趋势,帮助管理员进行性能监控和故障排查。

常用字段:

SNAP_ID:快照的唯一标识符。

BEGIN_INTERVAL_TIME:快照的开始时间。

END_INTERVAL_TIME:快照的结束时间。

DBID:数据库的唯一标识符。

INSTANCE_NUMBER:实例的编号。

3、v$SQLTEXT

用于提供有关共享SQL区域中SQL语句文本的信息。它记录了数据库中执行过的SQL语句的文本。vsqltext可以用于查看和分析数据库中执行过的SQL语句的具体文本内容。

常用字段:

SQL_ID:SQL语句的唯一标识符。

SQL_TEXT:SQL语句的文本。

4、v$SQL⭐

用于提供有关SQL语句执行的统计信息和执行计划

SQL_TEXT:SQL语句的文本,最多1000个字符。

SQL_FULLTEXT:SQL语句的完整文本,以CLOB(Character Large Object)形式存储。SQL_ID:SQL语句的唯一标识符,最多13个字符。

SHARABLE_MEM:共享内存的大小,以字节为单位。

PERSISTENT_MEM:持久内存的大小,以字节为单位。

RUNTIME_MEM:运行时内存的大小,以字节为单位。

SORTS:排序操作的次数。

EXECUTIONS:SQL语句的执行次数。

PARSE_CALLS:解析调用的次数。

DISK_READS:磁盘读取的次数。

DIRECT_WRITES:直接写入的次数。

DIRECT_READS:直接读取的次数。

BUFFER_GETS:缓冲区获取的次数。

APPLICATION_WAIT_TIME:应用程序等待的时间。

CONCURRENCY_WAIT_TIME:并发等待的时间。

CLUSTER_WAIT_TIME:集群等待的时间。

USER_IO_WAIT_TIME:用户I/O等待的时间。

PLSQL_EXEC_TIME:PL/SQL执行的时间。

JAVA_EXEC_TIME:Java执行的时间。

ROWS_PROCESSED:处理的行数。

COMMAND_TYPE:命令类型的编号。

OPTIMIZER_MODE:优化器模式。

OPTIMIZER_COST:优化器成本。

OPTIMIZER_ENV:优化器环境。

三、SQL语句

将这两个视图通过快照 id join一下得到SQL,记录了系统七天内Oracle数据库的SQL执行次数

1、七天内Oracle数据库的SQL执行次数

SELECT
    SUM(ss.executions_delta) AS total_executions
FROM
    DBA_HIST_SQLSTAT ss
JOIN
    DBA_HIST_SNAPSHOT sn ON ss.snap_id = sn.snap_id
WHERE
    sn.begin_interval_time BETWEEN SYSDATE - 7 AND SYSDATE;

--结果
TOTAL_EXECUTIONS
----------------
	  122282

注意:

1、AWR收集:该查询依赖于AWR数据,因此AWR必须启用,需要确保系统快照的保留策略为7天以上。详情请看扩展1,平时如何管理AWR报告的收集时间,默认是1小时收集一次,保留策略怎么设置。

2、 executions_delta参数指的是该查询计算的过去7天内所有 SQL 语句的总执行次数变化。⭐

Oracle给出的定义是:自将此对象引入库缓存以来,对该对象执行的增量执行次数
参考文档:DBA_HIST_SQLSTAT

2、优化:按天分组按次数降序排列

现在想要了解在特定时间范围内 SQL 语句的执行频率。 所以更改一下SQL查询每天的SQL执行次数,通过TRUN 截断日期时间值 ,按日期分组并按照每天总执行次数降序排序

SELECT TRUNC(sn.begin_interval_time) AS query_date,
       SUM(ss.executions_delta) AS total_executions
  FROM DBA_HIST_SQLSTAT ss
  JOIN DBA_HIST_SNAPSHOT sn
    ON ss.snap_id = sn.snap_id
 WHERE sn.begin_interval_time BETWEEN SYSDATE - 7 AND SYSDATE
 GROUP BY TRUNC(sn.begin_interval_time)
 ORDER BY total_executions desc;


 QUERY_DATE	   TOTAL_EXECUTIONS
------------------ ----------------
16-JUL-24		      59989
18-JUL-24		      21884
17-JUL-24		      21523
12-JUL-24		      12903
15-JUL-24		      10264

在项目上大神开发的可视化界面首页应该就是用的这条SQL,可以很明显看出哪一天的SQL执行量,如果有异常,再去分析问题。

如果某一条SQL执行异常,需要做分析,怎么找出这条SQL文本。

3、根据sql_id查询SQL语句

SELECT SQL_TEXT FROM v$sqltext WHERE SQL_ID = 'cmhz88h821m04';

有时SQL文本过长v$sqltext中SQL_TEXT字段最多1000个字符,可能放不下,我们可以看v$sql中的SQL_FULLTEXT字段保留了SQL语句的完整文本,以CLOB形式存储。

4、根据sql_id查询完整的SQL

SELECT SQL_FULLTEXT FROM v$sql WHERE SQL_ID = 'cmhz88h821m04';

拿到SQL之后可以看看sql的执行计划分析问题

5、根据sql_id查看执行计划

select * from table(dbms_xplan.display_cursor('cmhz88h821m04',0,'ALLSTATS LAST'));

扩展2,查看执行计划的方法

可以先从v$sql中查出sql_id,再通过dbms_xplan_display_cursor( sql_id,child_number,format)查指定的sql执行计划

sql_id:指定位于库缓存执行计划中 SQL 语句的父游标。默认值为 null,表示最后一条语句的执行计划,可以换成想要查看SQL的sql_id。通过查询V$SQL 或V$SQLAREA的SQL_ID列来获得SQL语句的SQL_ID。

child_number:指定父游标下子游标的序号,默认值为 0,不返回子游标的执行计划,null则全部返回。

format:控制 SQL 语句执行计划的输出部分。常用的有:BASIC: 显示最少的信息、TYPICAL: 默认值。SERIAL、ALL: 显示最多的信息、IOSTATS、MEMSTATS、ALLSTATS等。

6、查询指定SQL语句的历史执行计划

SELECT SQL_ID, PLAN_HASH_VALUE, OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, COST, CARDINALITY, BYTES, PARTITION_START, PARTITION_STOP FROM dba_hist_sql_plan WHERE SQL_ID = 'cmhz88h821m04';

这里可以再做一个扩展3,快照的管理,手工创建快照。再找出SQL之后,我们还可能会用到AWR和ADDM等工具帮助我们去分析问题,AWR报告默认是一个小时收集一次快照,我们可以在想要分析的SQL语句执行前后手工创建或者建一个基线,方面分析报告。

工作中往往会遇到紧急案例CPU、IO、内存飙升、甚至宕机,下一篇我可能会写关于紧急案例-CPU飙升如何快速定位到SQL语句,数据库hang住,杀会话等问题的处理。

下面扩展2应该是重点部分,之前在天津实习的时候在那用不到,只学到了备份恢复那一块,来河北刚进组就听什么慢SQL处理,性能优化,压力很大啊。后面可能会开一个优化专题,关注博主,哈哈!

扩展1:AWR性能报告收集的管理

1. 查看AWR的收集时间和保留时间

可以通过dba_hist_wr_control视图进行AWR的设置控制,包括快照间隔、保留时间和TOPNSQL设置。


select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL
---------- ---------------------------------------------------------------------------
RETENTION								    TOPNSQL
--------------------------------------------------------------------------- ----------
1696225019 +00000 01:00:00.0
+00008 00:00:00.0							    DEFAULT

DBID:数据库实例的唯一标识

SNAP_INTERVAL:快照间隔,每一小时收集一次快照。格式天,时分秒,

RETENTION:快照保留时间,这里为8天,所以可以完全可以收集七天内的信息。格式,天,时分秒

TOPNSQL:在每个AWR快照期间收集的SQL语句数量

2. 修改AWR收集时间和保留策略

awr 默认通过 mmon 及 mmnl 进程来每小自动收集一次,为了节省空间,采集的数据在在保留一定时间后自动清除。上面看到的是11g Oracle默认保留8天,10g为7天。

可以用PL/SQL中的dbms_workload_repository.modify_snapshot_settings设置

案例:设置AWR的收集时间为60min,保留时间为30day。

SYS@orcl> 
begin
  dbms_workload_repository.modify_snapshot_settings(interval => 60,
                                                    retention => 30*24*60);
  end;
/

PL/SQL procedure successfully completed.

SYS@orcl> 
SYS@orcl> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL
---------- ---------------------------------------------------------------------------
RETENTION								    TOPNSQL
--------------------------------------------------------------------------- ----------
1696225019 +00000 01:00:00.0
+00030 00:00:00.0	

扩展2:查看执行计划的方法

SQLPLUS AUTOTRACE --计划执行不真实

Explain Plan For SQL --计划执行不真实

使用 DBMS_XPLAN 包 --计划执行真实⭐

statistics_level=all; --计划执行真实

sql_trace 与 10046 --计划执行真实⭐

扩展3:快照的管理

1、场景:

Ⅰ、我不想要一个小时的,只想要看目标语句、性能测试、压力测试那一段的报告。

Ⅱ、数据库出现过异常,无法生成AWR,检查能否创建快照。

2、手工创建快照:

用 create_snapshot 存储过程手动创建快照:

begin
  dbms_workload_repository.create_snapshot();
   end;
/

3、查看快照信息

select * from dba_hist_snapshot;

4、手工删除快照

指定要删除的快照id范围

begin
  dbms_workload_repository.drop_snapshot_range(low_snap_id => 30,high_snap_id => 31);
  end;
/

如果有删不掉的快照,可能是创建了基线,需要先把极限删除,再删除快照。

5、查看基线的视图

select * from dba_hist_baseline;

6、删除基线及其快照

也可以在删除基线的时候连快照一起删掉:

begin
  dbms_workload_repository.drop_baseline(baseline_name => 'xxx',
                                        cascade => true);
  end;
/

cascade 默认false不删除快照,true删除快照。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

CodeBe@r

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值