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;
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
但是SC_LOT表还是走全表扫描,经过分析发现SC_LOT表的PO_NO列的区分度非常大,应该可以通过建立索引优化。如下所示,建立索引后,SC_LOT不走全表扫描了。
执行计划的代价(Cost)也从7014降为了254. 优化的效果非常显著(Cardinality变得非常大,是因为表MARS_UPLOAD_TEMP数据在我测试阶段发生了变化)
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
相关文章
- ORA-13802: failed to purge SQL Tuning Base entry from sql$ ORACLE 报错 故障修复 远程处理
- 实现成功:使用SQL登录Oracle数据库(sql登录oracle)
- 掌握Oracle动态SQL拼接技巧,实现查询性能优化(oracle动态sql拼接)
- Oracle 函数:超越定义的力量(oracle函数定义)
- 郑州:快来参加Oracle 培训!(郑州oracle培训)
- 掌握Oracle命令语句,实现SQL高效率编程(oracle命令语句)
- 优化优化Oracle数据库:改善参数配置(oracle数据库参数)
- 安装Oracle客户端并使用PL/SQL(oracle客户端plsql)
- 子句使用Oracle中的using子句简化SQL语句(oracle中using)
- 解决Oracle性能问题的有效优化技术(oracle优化案例)
- 深入了解Oracle触发器类型及其应用(oracle触发器类型)
- Oracle表空间管理:精通SQL(oracle表空间sql)
- Efficient PL/SQL Login Process for Oracle Database Access(plsql登录oracle)
- 解决Oracle日志文件过大的简单方法(oracle日志太大)
- Oracle数据库服务收费模式案例分析(oracle 收费方式)
- 查找Oracle中日期月份之间的差值(oracle 日期月份差)
- Oracle通过SQL脚本实现快速操作(oracle执行sql脚本)
- Oracle几秒轻松完成SQL执行(oracle几秒执行语句)
- Oracle数据库中万能全局搜索语句的实用技巧(oracle全局搜索语句)
- sql使用cxoracle写出优化的SQL(cx_oracle写死)
- Oracle JDBC教程让您快速上手(jdbc教程oracle)
- 信息技术利用Oracle助推企业发展(infor oracle)
- 从零开始Oracle数据库的BDF恢复(oracle从bdf恢复)
- Oracle事务处理出现故障排查与解决方案(oracle事务产生问题)
- 英文吗在Oracle中,中文能否查询英文(oracle中文可以查询)
- Oracle中如何修改列表名(oracle中修改列表名)
- 借助Oracle SQL轻松实现除法运算(oracle sql除以)
- 使用Oracle SQL实现数据脱敏(oracle sql脱敏)
- Oracle SQL 超越普通,开启新时代(oracle sql大于)
- Oracle S7 2L槽位填满,成就持久省心(oracle s7 2l)
- Oracle CUUG激发力量,构建数据库新未来(oracle cuug)