《收获,不止SQL优化》读书笔记

AWR、ASH、ADDM、AWRDD

整体分析调优工具

AWR:关注数据库的整体性能的报告;

ASH:数据库中的等待事件与哪些SQL具体对应的报告;

ADDM:oracle给出的一些建议

AWRDD:Oracle针对不同时段的性能对比报告

AWRSQRPT:oracle获取统计信息与执行计划

不同场景对应工具

局部分析调优工具:

explain plan for

set autotrace on

statistics_level=all

直接通过sql_id获取

10046 trace

awrrpt.sql

整体性能工具要点

AWR关注点:load profile、efficiency percentages、top 5 time events、SQL Statistics、segment_statistics

ASH关注点:等待事件与sql完美结合

ADDM:各种建议与对应SQL

AWRDD:不同时期 load profile的比较、不同时期等待事件的比较、不同时期TOP SQL的比较

AWRSQRPT:获取与关注点(统计信息与执行计划)

select output from table (dbms_workload_repository.awr_report_html(v_dbid,v_instance_number,v_min_snap_id,v_max_snap_id));

相关查询试图:

v$session (当前正在发生)

v$session_wait(当前正在等待)

v$session_wait_history (会话最近的10次等待事件)

v$active_session_history (内存中的ASH采集信息,理论为1小时)

wrh$_active_session_history (写入AWR库中的ASH信息,理论为1小时以上)

dba_hist_active_sess_history (根据wrh$_active_session_history生成的视图)

执行计划

获取执行计划的方法:

(1) explain plan for
步骤:

1:explain plan for 你的SQL;

2:select * from table (dbms_xplan. display()) ;

优点:不需要真的执行,快捷方便

缺点:没有输出运行时的统计信息(逻辑读、递归调用,物理读),因为没有真正执行,所以不能看到执行了多少行、表被访问了多少次等等

(2) set autotrace on

sqlplus登录:

用户名/密码@主机名称:1521/数据库名

步骤:

1:set sutoatrace on

2:在此次执行你的sql;

优点:可以看到运行时的统计信息(逻辑读、递归调用,物理读)

缺点:不能看到表被访问了多少次,也需要等sql执行完成才能看

(3) statistics_level=all
步骤:

1:alter session set statistics_level=all;

2:在此处执行你的SQL;

3:select * from table(dbms_xplan.display_cursor(null , null,'allstats last'));

假如使用了Hint语法: /+ gather_plan_statistics /,就可以省略步骤1,直接执行步骤2和3,获取执行计划

关键字解读:

Starts:该SQL执行的次数

E-Rows:为执行计划预计的行数

A-Rows:实际返回的行数,E-Rows和A-Rows作比较,就可以看出具体那一步执行计划出问题了

A-Time:每一步实际执行的时间,可以看出耗时的SQL

Buffers:每一步实际执行的逻辑读或一致性读

Reads:物理读

OMem:当前操作完成所有内存工作区操作总使用私有内存工作区(PGA)的大小

lMem:当工作区大小无法符满足操作需求的大小时,需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作,就称一次通过,One-Pass;否则为多次通过,Multi-Pass)。改数据为语句最后一次执行中,单次写磁盘所需要的内存大小,这个是由优化器统计数据以及前一次执行的性能数据估算得出的

Used-Mem:语句最后一次执行中,当前操作所使用的内存工作区大小,括号里面为(发生磁盘交换的次数,1次即为One-Pass,大于一次则为Mullti-Pass,如果没有使用磁盘,则显示为OPTI1MAL)
OMem、lMem为执行所需要的内存评估值,OMem为最优执行模式所需要内存的评估值,Used-Mem为消耗的内存

优点:

可以从STARTS得出表被访问多少次;

可以清晰地从E-ROWS和A-ROWS中分别得出预测的行数和真实的行数
缺点:

必须等到语句真正执行完成后,才可以得出结果

无法控制记录打屏输出,不想aututrace有traceonly命令

没有专门的输出统计信息,看不到递归调用的次数,看不出物理读具体数值,不过有逻辑读,逻辑读才是重点

(4) dbms_xplan.display_cursor获取
步骤
从共享池获取

//${SQL_ID}参数可以从共享池拿 select * from table(dbms_xplan.display_cursor(${SQL_ID}));

还可以从AWR性能视图里获取

select * from table(dbms_xplan.display_awr(${SQL_ID}));

多个执行计划的情况,可以用类似方法查出

select * from table(dbms_xplan.display_cursor(${SQL_ID},0)); select * from table(dbms_xplan.display_cursor(${SQL_ID},1));

优点:

和explain一样不需要真正执行,知道sql_id就好

缺点:

不能判断处理了多少行

无法判断表被访问了多少次

没有输出运行时的相关统计信息(逻辑读、递归调用、物理读)

(5) 事件10046 trace跟踪
步骤:

1:alter session set events '10046 trace name context forever,level 12';//开启跟踪 2:执行你的语句 3:alter session set events '10046 trace name context off';//关闭跟踪 4:找到跟踪产生的文件 5:tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela(格式化命令)

优点:

可以看出SQL语句对应的等待事件

可以列出sql语句中的函数调用的

可以看出解析事件和执行事件

可以跟踪整个程序包

可以看出处理的行数,产生的逻辑读
缺点:

步骤比较繁琐

无法判断表被访问了多少次

执行计划中的谓词部分不能清晰地显示出来

(6) awrsqrpt.sql
步骤:

1:@?/rdbms/admin/awrsqrpt.sql 具体可以参考我之前的博客:https://smilenicky.blog.csdn.net/article/details/89429989

解释经典执行计划的方法

可以分为两种类型:单独型和联合型

联合型分为:关联的联合型和非关联的联合型

【单独型】

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/wpxgdz.html