如何查询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删除快照。