orastar

6年DBA经验,10GOCP 11G OCM QQ:634025070  Oracle数据库性能优化、故障处理、12C新技术研究、Python自动化工具开发

  • 博客访问: 4562
  • 博文数量: 21
  • 用 户 组: 普通用户
  • 注册时间: 2017-03-08 15:52
  • 认证徽章:
ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(21)

文章存档

2017年(21)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Oracle

1        相遇是缘


star童鞋最喜欢的西安美食之一“三秦套餐刚上来,突然电话响了,某核心BOSS系统数据库异常缓慢,做为专业小’DBA’er,第一时间赶赴现场解决问题。。。。。。

赶到现场时SQLPLUS已无法登陆,数据库hang住,客户反应部分业务已受影响。

通过top命令查看资源使用情况,有2oracle 客户端进程CPU使用率100%,经与客户确认,结束这2个进程后数据库恢复正常。

通过查看数据库故障时间历史会话视图,进行问题定位与分析,

select * from dba_hist_active_sess_historyy where y.sample_time >= trunc(sysdate)+9/24 and y.sample_time <=trunc(sysdate)+13/24

统计分析在线活动进程:


 通过分析历史会话表中出现异常的数据库进程信息,

步骤1 sid: 118,event:enq: US – contention锁定批量数据库进程

步骤2:查看进程118,发现118进程被sid:170event: DFSlock handle锁定

步骤3Sid: 170进各没有被阻塞

步骤4:在170运行前,sid:97号进程,运行语句:1234567896u1长时间未结束,造成数据库批量row cache lock,数据库运行环境恶化

步骤5:最终170成为触发条件,造成批量进程互锁,数据库HANG。。。。

锁定异常语句1234567896u1,相遇是缘。

 

2        初识SQL君


查看数据库AWR报告

sql语句:1234567896u1每执时一次7413秒,2个小时!!!


查看执行计划

Coat: 1445,从执行计划看该语句,未发现严重性能问题,语句运行不应该如此慢。


 

    

   

        

3        山穷水尽


查看语句:

--类似以下语句,由于安全问题,源语句较复杂无法提供,以下语句仅供参考

SELECTCOUNT(*) d, 0 q

  FROM aaa A, bbb B, ccc C

 WHERE A.aa = B.b

   ANDSUBSTR(B.bb, 6) = C.cc

   AND A.sj BETWEEN to_date('20170301','yyyymmdd'AND to_date('20170331','yyyymmdd')  AND C.c IN (123,4567)

  AND A.a = 'ddd'

该语句逻辑较为简单,由于未使用绑定变量,在sqlplus中再次运行,运行结果0.01秒就出来。

怀疑1、是否由于缓存软解析的原因,修改日期再次运行

SELECTCOUNT(*) d, 0 q

  FROM aaa A, bbb B, ccc C

 WHERE A.aa = B.b

   ANDSUBSTR(B.bb, 6) = C.cc

   AND A.sj BETWEEN to_date('20170301','yyyymmdd'AND to_date('20170331','yyyymmdd')  AND C.c IN (123,4567)

  AND A.a = 'ddd'

运行结果还是0.01秒出来。

怀疑2、是否该语句在运行时有大批量数据变化

通过查看AWRSQL、查看存储过程与业务沟通均未发现异常。

怀疑3、是否为OracleBug

非常简单的语句,客户端执行很快,程序执行异常缓慢,是否为Oracle Bug!!!

有点山穷水尽的感觉。

4  峰回路转


决定细致化深入分析AWRSQL,发现存在 TABLE ACCESS FULL等待事件,说明 b表存在全表扫描,查看A 、B、C表数分布情况

SQL> set line999

SQL> selectt.OWNER, t.TABLE_NAME, t.NUM_ROWS, t.LAST_ANALYZED, t.PARTITIONED

  from dba_tables t where t.TABLE_NAME in ('a','b', 'c')


查看A表数据分布情况,

SELECTCOUNT(*) d, 0 q

  FROM aaa A, bbb B, ccc C

 WHERE A.aa = B.b

   ANDSUBSTR(B.bb, 6) = C.cc

   AND A.sj BETWEEN to_date('20170301','yyyymmdd'AND to_date('20170331','yyyymmdd')

   AND C.c IN (1234567)

  AND A.a = 'ddd'

结合数据量分析sql语句,A表数据量40W,A表有两个条件sj日期类型、a字段字符类型,查看SQL语句对字段sja数据的抽取率,

A数据总量42W,

SQL> selectcount(1) from a.a;

 

  COUNT(1)

----------

    420474

 

     字段sj日期类型,抽取数据1827

    SQL> select count(1) from a.a a where A.aBETWEEN to_date('20170301','yyyymmdd')ANDto_date('20170331','yyyymmdd');

 

 COUNT(1)

----------

     1827

统计数据抽取比例0.43%,数据抽取比例较好,但需要抽取1827

SQL> selectround(1827/420474,4) from dual;

 

ROUND(1827/420474,4)

--------------------

               .0043

查看2个字段同时使用,数据抽取比例,只抽取4条记录

SQL> selectcount(1) from aaa.a a where A.a BETWEEN to_date('20170301','yyyymmdd')ANDto_date('20170331','yyyymmdd')and  A.b = '111';

 

  COUNT(1)

----------

         4

峰回路转是否可以通过添加索引优化。

5        柳岸花明


   为表A创建联合索引优化后执行计划

 

索引优化前cost:1445


 

  索引优化后cost: 59


,

查看执行计划全表扫描变为索引扫描,数据库评估性能提升20倍以上,实际优化效果还有待进一步测试。

预估实际运行效果性能提升200倍以上,欲知实际运行效果,请见后续更新。

阅读(572) | 评论(2) | 转发(1) |
给主人留下些什么吧!~~

ping_72017-03-16 11:46:27

lpwebnet:楼下狂顶楼主1000次,成为刚烈的汉子

这楼主我认识

回复 | 举报

fengwuying1212017-03-16 10:59:48

非常不错,一步一步找出问题的原因。t

lpwebnet2017-03-16 09:42:13

楼下狂顶楼主1000次,成为刚烈的汉子

评论热议
请登录后评论。

登录 注册