幻灯片 1

Size: px

Start display at page:

Download "幻灯片 1"

谋绕隆
7 years ago
Views:

1 ORACLE 数据库 SQL 性能捕捉与调优杨银芳

2 SQL 性能捕捉与调优 SQL 性能调优背景 SQL 性能调优原理 SQL 性能捕捉 SQL 性能调优

3 SQL 性能调优背景应用程序不管通过什么样的接口方式访问数据库里的数据, 都要用到 SQL 语句业务的繁忙时, 调用的 SQL 语句量会更多如果此时有一些 SQL 语句编写的代码质量不高, 要运行很长的时间, 从而占用了较多的 CPU 资源, 影响其它程序的运行速度当系统的 CPU 的资源将达到饱和时, 系统上运行任何东西都很慢 ; 达到饱和 ( 所有 CPU 的资源用尽 ) 时, 最坏的情况就是系统被挂起, 无法做任何工作由于数据库是生产环境, 相应的业务都不能通过计算机的应用程序进行开展, 公司的利益受到严重的损害从而编写的 SQL 语句的性能问题不管是 DBA, 开发人员, 还是公司管理人员来说都非常的关注从而应运而生出来了一个 SQL 性能调优, 做为一门专门技术, 公司非常欢迎有此类技术的人员

4 SQL 性能调优原理应用程序调用 SQL 语句时, 会根据生成的执行计划首先会去内存中读取相应数据块中的数据进行分析, 最后把分析出来的结果返回给应用程序的输出界面如果在内存中发现全部数据或部分数据不存在, 则会去物理磁盘上读取相应的数据块到内存中 ( 内存空间足够情况下 ), 如内存不足时, 会把之前未用的内存中的数据先写入到物理磁盘的交换空间上, 然后把物理磁盘上的需读取的数据块读入到内存中 ( 覆盖最近从内存中写入到换页空间中的数据块 )

5 SQL 性能调优原理如果 SQL 代码质量不佳 ( 可用索引, 未用索引, 则进行全表扫描 ), 会从物理磁盘上读取更多的数据块, 同时也会在内存中读取更多的数据块, 需要更多的执行时间, 占用更多的 CPU 资源如果采用的是索引查询, 则会直接根据索引值得到相应的表 rowid, 然后根据 rowid, 直接读取相应的数据的数据块 ( 块量少 ), 无须读取表的所有数据块从而 SQL 执行的时间短, 占用的 CPU 资源少, 提高了数据库的性能, 间接提高了系统上应用程序的执行性能

6 SQL 性能捕捉某某听到用户说查询一些东西很慢, 或者手工运行一些脚本如存储过程很慢, 在不知道执行的是哪个脚本时, 怎样更快的时间查出问题了 ( 以下只针对 SQL 这块, 可能还有别的原因 < 如 IO 问题 >), 则可以通过以下的工具进行捕捉当前性能最差的 SQL 语句以及 SQL AREA 中某时段内运行过的 SQL 语句, 从而对执行计划中显示的相关参数, 统计的数据进行分行, 得出性能问题所在处 1.Oracle Enterprise Manager 中的 ADDM 工具 2.Quest SQL Optimizer For oracle 工具 3.Statspack 工具 ( 跟 2 类似都是第三软件, 不讲解 )

7 OEM 中的 ADDM 工具 SQL 性能捕捉模拟存在性能问题的 SQL 语句且进行捕捉操作步骤 : 1. 首先通过 sqlplus 登录到 orcl 数据库, 在数据库的 scott 用户下建一个 test1 表, 表里含有 a1,a2 两个字段, 都是 number 型

8 OEM 中的 ADDM 工具 SQL 性能捕捉 2. 在 scott 用户的 test1 表中插入 1 百行数据, 插入完数据后, 对其中 1 百万行的数据进行更新, 如下所示 :

9 OEM 中的 ADDM 工具 SQL 性能捕捉对其中 1 百万行的数据进行更新 :

10 OEM 中的 ADDM 工具 SQL 性能捕捉 3. 启动 OEM, 进入指导中心 SQL 优化指导顶级活动, 可查看到顶级 SQL, 即性能最差的前几条语句, 按降序一一列出来, 如下一些图片所示 : 性能最差的一条 sql 语句单击该选项会得到优化建议

11 OEM 中的 ADDM 工具 SQL 性能捕捉 * 点击上图中的第一行对应的 SQL ID, 可以查到执行的 SQL 语句, 相应执行的 SQL 语句如下图所示 : 单击该选项查看该 sql 语句的执行计划从上图中可以看出, 显示的 sql 语句就是刚才执行的那条 sql 语句

12 OEM 中的 ADDM 工具 SQL 性能捕捉 * 点击上图中计划选项, 查看到如下的执行计划, 发现影响该表更新数据慢的原因对表进行全表扫描, 未在 a2 列上建索引, 如下图所示 : 全表扫描占用了更多的 cpu 时间成本量为 6

13 Quest SQL Optimizer 工具 SQL 性能捕捉模拟存在性能问题的 SQL 语句且进行捕捉操作步骤 : 第 1,2 步操作跟 OEM 中的 ADDM 工具 SQL 性能捕捉的步骤一样, 建表, 插入 1 百万行数据, 然后对 1 百行的数据进行更新, 此处不在列具体操作, 请参考前面的步骤

14 Quest SQL Optimizer 工具 SQL 性能捕捉 3. 启动 Quest SQL Optimizer, 点击 SGA Inspector 项, 如想当前运行的 SQL 语句 () 可按读物理磁盘数等进行降序排序列出当前运行的 SQL 语句, 进行分析可得出性能最差的前几条语句如下一些图片所示 : A). 创建一个连接数据库的 group

15 Quest SQL Optimizer 工具 SQL 性能捕捉 1. 点击此处建组 2. 点击创建数据库连接 3. 输入相应连接数据库的信息 4. 点击创建数据库连接

16 Quest SQL Optimizer 工具 SQL 性能捕捉 B). 创建一个检查 SQL 语句的作业 : 把某一时间段内的 sql area 中运行的 sql 语句都显示出来输入 job 名后, 点击 NEXT 按钮只显示当前正在运行的 sql 语句

17 Quest SQL Optimizer 工具 SQL 性能捕捉选择哪些用户下的正在运行的 sql 语句要列出来

18 Quest SQL Optimizer 工具 SQL 性能捕捉选择哪些正在运行的 DML,DSL sql 语句要列出来

19 Quest SQL Optimizer 工具 SQL 性能捕捉选择要查看 sql 语句正在运行的时间段

20 Quest SQL Optimizer 工具 SQL 性能捕捉选择哪些参数来分析 sql 语句

21 Quest SQL Optimizer 工具 SQL 性能捕捉点击它, 从下拉项中选择 Inspect, 进行正在进行的 sql 语句根据前面要显示的参数进行分析检查

22 Quest SQL Optimizer 工具 SQL 性能捕捉 * 检查完后, 发现刚才模拟更新的那个语句排在首位, 看执行计划发现更新到条件中的 a2 列是全表扫描, 未用索引, 可以通过 cpu 读的时间, 执行计划的 cost 看出该语句存在性能问题, 检查完, 输出如下所示的界面 :

23 Quest SQL Optimizer 工具 SQL 性能捕捉

24 SQL 性能调优 SQL 性能调优以下以 autotrace,sql trace 来分析 sql 的性能 1. 基于未建索引调优 2. 取消 Hint 提示, 让代价优化器自动选择合适的索引 ( 或不使用索引 ) 3. 基于并行进程调优 4. 基于条件调优 5.autotrace,sql_trace 的使用 * 相应的诊断工具还有 : explain( 不会真正执行 sql 语句 ), sql trace( 采用 TKPROF 格式化.trc 输出 )

25 基于未建索引调优 ( 案例 1) 案例 1: 缺乏合适的索引, 导致全表扫描 DELETE FROM REPA001 WHERE FLD04=0 AND FLD05=0 AND FLD06=0 AND FLD07=0 AND FLD08=0 AND FLD09=0

26 基于未建索引调优 ( 案例 1) 其执行计划为 :

27 基于未建索引调优 ( 案例 1)

28 基于未建索引调优 ( 案例 2) 案例 2: 缺乏合适的索引, 导致全表扫描

29 基于未建索引调优 ( 案例 2)

30 基于未建索引调优 ( 案例 2)

31 基于未建索引调优 ( 案例 2) 可见, 在为 notice_no 创建索引后, 其执行 1073 次, 累计的物理读为 2841, buffer gets 为 16769, 平均每次物理读为 2.64, 每次 buffer gets 较创建索引前平均每次个物理读, 物理读的量大大降低, 其 buffer gets 也大大降低, 减轻了系统的 IO 负担, 同时, 减少了 CPU 的占用率, 节省了 db cache 的空间, 使得同样大小的 db cache 中可存放更多的数据块, 从而进一步减少磁盘 IO 量, 提高系统的性能

32 取消 Hint 提示, 让代价优化器自动选择合适的索引案例 1: 取消 Hint 提示 ( 不走索引 ) delete /*+ index(a,ryt_usergroup_tmp_01)*/ from stat.t_ryt_usergroup_tmp01 a where exists ( select 1 from stat.t_ryt_user_group_tmp c where c.is_out= 0 and a.srvgnum=c.srvgnum ); 执行计划 DELETE STATEMENT, GOAL = CHOOSE DELETE Object owner=stat Object name=t_ryt_usergroup_tmp01 HASH JOIN SEMI TABLE ACCESS BY INDEX ROWID Object owner=stat Object name=t_ryt_usergroup_tmp01 INDEX FULL SCAN Object owner=stat Object name=ryt_usergroup_tmp_01 INDEX FAST FULL SCAN Object owner=stat Object name=i_ryt_user_group_tmp1 stat.t_ryt_usergroup_tmp01 有 49.8 万的样子历史上执行时间有超过 1 个半小时的情况

33 取消 Hint 提示, 让代价优化器自动选择合适的索引去掉不恰当的索引提示 delete from stat.t_ryt_usergroup_tmp01 a where exists ( select 1 from stat.t_ryt_user_group_tmp c where c.is_out= 0 and a.srvgnum=c.srvgnum ); 执行计划 DELETE STATEMENT, GOAL = CHOOSE DELETE Object owner=stat Object name=t_ryt_usergroup_tmp01 FILTER TABLE ACCESS FULL Object owner=stat Object name=t_ryt_usergroup_tmp01 INDEX RANGE SCAN Object owner=stat Object name=i_ryt_user_group_tmp1 结论 : 全表扫描虽然笨了点 ( 好像暂时没有找到更好的方法 ), 实际上加上索引提示时是根据索引进行了全表扫描, 更慢 ; 现在的执行计划也很简单 ( 有时候索引提示给优化器带来了不适当的指引 ) 现在用时不超过 10 分钟, 历史上有超过 1 个半小时的情况

34 基于并行进程调优案例 1: 批量从 info.t_ryt_userinfo 和 info.t_ryt_userinfo_his 导入 stat.t_ryt_user_group_tmp 需要 50 分钟左右分析 : 开始有点感觉无从下手, 后来查阅相关资料和结合批量销帐优化的经验发现可以使用并行, 还可以先失效索引, 再重建索引且可以并行重建索引

35 基于并行进程调优 * 代码未优化前的 sql 语句块批量从 info.t_ryt_userinfo 和 info.t_ryt_userinfo_his 导入 stat.t_ryt_user_group_tmp insert /*+ APPEND */ into stat.t_ryt_user_group_tmp NOLOGGING select a.marktid,a.spectype, from info.t_ryt_userinfo a; commit; -- 数据量 info.t_ryt_userinfo 大概 475 万用户 insert /*+ APPEND */ into stat.t_ryt_user_group_tmp NOLOGGING select a.marktid,a.spectype, from info.t_ryt_userinfo_his a where busistate in ('D'); commit; -- 数据量 info.t_ryt_userinfo_his 大概 600 万

36 基于并行进程调优 * 代码优化后的 sql 语句块批量从 info.t_ryt_userinfo 和 info.t_ryt_userinfo_his 导入 stat.t_ryt_user_group_tmp v_sql := ' alter index I_RYT_USER_GROUP_TMP1 unusable '; execute immediate v_sql; v_sql := ' alter index RYT_USERGROUP_TMP_99 unusable '; execute immediate v_sql; v_sql := ' alter session set skip_unusable_indexes=true '; execute immediate v_sql; 加入 /*+PARALLEL(a,8)*/ 从两个表批量导入数据 v_sql := ' alter index I_RYT_USER_GROUP_TMP1 rebuild nologging parallel 4 '; execute immediate v_sql; v_sql := ' alter index RYT_USERGROUP_TMP_99 rebuild nologging parallel 4 '; execute immediate v_sql; 结论 : 现在正式库上费时 18 分钟左右, 以前需要 50 分钟左右

37 基于条件调优 1.Where 条件中存在于 NOT IN 或 IN 的子查询时, 使用 NOT EXISTS 或 EXISTS 子查询来代替 ; 2. Where 条件中某日期型字段建了索引, 查询条件右边传过来的是字符型, 切勿写成如下的型式 :where to_char(op_date, yyyy-mm-dd hh24:mi:ss ) = :11:34), 因为此时索引用不上, 需在右边使用 to_date 函数将字符型数据转换为日期型

38 Autotrace,sql trace 使用听到此时, 可能会有人会问, 前面的执行计划是怎样显示出来的呢, 还有就是物理读取的块数, 执行时间是怎样得到的, 这些就是下面要说的东西啦

39 Autotrace 使用 1. 建立角色 2. 授予角色给用户 plustrace GRANT PLUSTRACE TO scott; 3. 建立 plan_table %oracle_home%\rdbms\admin\utlxplan 4. 在 session 中打开 sqltrace set autotrace on 5. 打开统计时间, 查看 sql 语句执行的总共时间, 打开 timing set timing on 6. 执行要分析与跟踪的 sql 语句, 得出执行结果, 执行计划, 统计数据, sql 执行时间 7. 不用跟踪分析 sql 语句时关闭 autotrace,timing set autotrace off set timing off

40 Autotrace 使用 * 打开 autotrace 后, 执行 sql 语句得到如下分析数据 :

41 Autotrace 使用

42 Sql trace 使用方法 1. 一般情况下, 仅在测试环境与开发环境中进行 sql trace, 且仅在需要性能优化时才打开 sql trace, 并在获得所需信息后关闭 sql trace 2. 执行 Sql trace 前,TIMED_STATISTICS 参数应设置为 true 3. Sql trace 的方法 : ALTER SESSION SET SQL_TRACE = true / false; DBMS_SESSION.SET_SQL_TRACE (true/false)

43 Sql trace 使用方法 4. 在用户跟踪文件目录下查看应生成的跟踪文件, 然用用 TKPROF 格式化格式文件, 格化成成我们可以看懂的文件如 c:\>tkprof c:\xtdb\udump\ora_901.trc c:\xtdb\trcnew\run2.txt sys=no 查看格式化的 trc 文件, 从中可以看到读取的物理磁盘数, 执行实际时间数, 提取的行数等相关用来分析 sql 的信息

44 谢谢

PowerPoint Presentation

PowerPoint Presentation 数据库培训项目研究 Oracle 索引探究 B*tree 索引与位图索引的特点作者 : 赵超 2008 年 12 月 18 日实验环境 Windows-server2003 内存 :2G Oracle 10.2.0 ORACLE_SID=orcl 索引类型 B*tree 索引 ( 默认方式 ) 位图索引 (bitmap) 反