一、问题:
今天引入的问题是: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删除快照。