ORACLE SQL调优案例一则-阿里云开发者社区

开发者社区> 潇湘隐者> 正文

ORACLE SQL调优案例一则

简介:
+关注继续查看

收到监控告警日志文件(Alert)的作业发出的告警邮件,表空间TEMPSCM2不能扩展临时段,说明临时表空间已经被用完了,TEMPSCM2表空间不够用了

Dear All:
 
  The Instance SCM2' alert log occured the ora errors ,please see the detail blow and take action for it. many thanks!

------------------------------------------- The errors is blow ------------------------------------------------------


193 |  | ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMPSCM2 
198 |  | ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMPSCM2 
200 |  | ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMPSCM2 
205 |  | ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMPSCM2 

--------------------------------------------end of errors-----------------------------------------------------------


Oracle Alert Services

同事在分析处理时,定位到临时表空间是被一个问题SQL语句给耗尽了。

SELECT B.TABLESPACE, B.SEGFILE#, B.SEGBLK#, B.BLOCKS, A.SID, A.SERIAL#, A.USERNAME, A.OSUSER, A.STATUS
FROM v$session A, v$tempseg_usage B, v$sqlarea C
WHERE A.saddr = B.session_addr
AND C.address= A.sql_address
AND C.hash_value = A.sql_hash_value
ORDER BY B.tablespace, B.blocks;

clip_image001

WORKLOAD REPOSITORY SQL Report显示,单个该SQL的HASH GROUP BY操作就要耗用临时表空间229M,他给的建议是不扩展TEMPSCM2表空间,而是去优化这个SQL语句,因为大部分时候,该数据库的临时表空间使用 率是非常低。我也同意他的分析结果。

clip_image002

从该SQL语句的执行计划,就能看出这个SQL语句有问题,例如SC_LOT、PO_HD全表扫描只是为了获取一小部分数据。

clip_image003

SELECT 'CEG'             AS FTY_CD, 
       2015              AS PD_Year, 
       2                 AS PD_Month, 
       a.po_no, 
       SUM(a.total_qty)  AS Order_Qty, 
       SUM(c.total_qty)  GO_QTY, 
       b.buyer_po_del_date, 
       b.status, 
       c.sam_group_cd, 
       c.style_chn_desc, 
       Max(e.short_name) AS CUSTOMER 
FROM   sc_lot a, 
       po_hd b, 
       sc_hd c, 
       gen_customer e, 
       (SELECT ct_no AS Job_order_no 
        FROM   mars_upload_temp 
        WHERE  fty_cd = 'CEG' 
               AND pd_yr = 2015 
               AND pd_mth = 2 
               AND date_type = '20150529 10:00:23881698737881698737') d 
WHERE  Upper(a.po_no) = Upper(b.po_no) 
       AND b.sc_no = c.sc_no 
       AND Upper(a.po_no) = Upper(d.job_order_no) 
       AND c.customer_cd = e.customer_cd(+) 
GROUP  BY b.buyer_po_del_date, 
          b.status, 
          c.sam_group_cd, 
          c.style_chn_desc, 
          a.sc_no, 
          a.po_no

了解了该语句的业务逻辑并和开发人员沟通后,发现WHERE语句的条件Upper函数根本没有必要,取消Upper函数后PO_HD、GEN_CUSTOMER表走索引扫描了

SELECT 'CEG'             AS FTY_CD, 
       2015              AS PD_Year, 
       2                 AS PD_Month, 
       a.po_no, 
       SUM(a.total_qty)  AS Order_Qty, 
       SUM(c.total_qty)  GO_QTY, 
       b.buyer_po_del_date, 
       b.status, 
       c.sam_group_cd, 
       c.style_chn_desc, 
       Max(e.short_name) AS CUSTOMER 
FROM   sc_lot a, 
       po_hd b, 
       sc_hd c, 
       gen_customer e, 
       (SELECT ct_no AS Job_order_no 
        FROM   mars_upload_temp 
        WHERE  fty_cd = 'CEG' 
               AND pd_yr = 2015 
               AND pd_mth = 2 
               AND date_type = '20150529 10:00:23881698737881698737') d 
WHERE  a.po_no= b.po_no
       AND b.sc_no = c.sc_no 
       AND a.po_no= d.job_order_no 
       AND c.customer_cd = e.customer_cd(+) 
GROUP  BY b.buyer_po_del_date, 
          b.status, 
          c.sam_group_cd, 
          c.style_chn_desc, 
          a.sc_no, 
          a.po_no

clip_image004

但是SC_LOT表还是走全表扫描,经过分析发现SC_LOT表的PO_NO列的区分度非常大,应该可以通过建立索引优化。如下所示,建立索引后,SC_LOT不走全表扫描了。

执行计划的代价(Cost)也从7014降为了254. 优化的效果非常显著(Cardinality变得非常大,是因为表MARS_UPLOAD_TEMP数据在我测试阶段发生了变化)

clip_image005

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL优化二(SQL性能调优)
一·、前言:这篇博文内容非原创,是我们公司的架构师给我们做技术培训的时候讲的内容,我稍微整理了下,借花献佛。这篇博文只是做一个大概的科普介绍,毕竟SQL优化的知识太大了,几乎可以用一本书来介绍。另外,博主对SQL优化也是刚刚接触,也有很多不了解的地方,说的不对的地方,还请大家指正,共勉!   二、oracle服务器,所谓oracle服务器指的是一个数据库管理系统,它包括一个oracle实例(动态)和一个oracle数据库(静态)。
1130 0
【sql调优】系统信息统计
SQL> begin  2  dbms_stats.gather_system_stats(gathering_mode =>'start');  3  end;  4  /PL/SQL procedure successfully completed.
499 0
博客链接—Oracle性能调优
001 AWR概述:http://blog.itpub.net/29067253/viewspace-1990437/ 002 硬解析和物理读取与软解析和逻辑读取 :http://blog.
589 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
4624 0
《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一2.5 执行计划中其他信息的含义
本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第2章,第2.5节,作者:黄玮,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
1407 0
Oracle数据库应用系统调优方法
从以下几个方面:数据库服务器、网络I/O、应用程序等对整个系统加以调整,充分发挥Oracle的效能,提高整个系统的性能   Oracle数据库广泛应用在社会的各个领域,特别是在Client/Server模式的应用,但是应用开发者往往碰到整个系统的性能随着数据量的增大显著下降的问题,为了解决这个问题,从以下几个方面:数据库服务器、网络I/O、应用程序等对整个系统加以调整,充分发挥Oracle的效能,提高整个系统的性能。
818 0
《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一第3章 查 询 转 换
本节书摘来自华章出版社《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一 书中的第3章,作者:黄玮,更多章节内容可以访问云栖社区“华章计算机”公众号查看。
853 0
+关注
潇湘隐者
网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
777
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载