|
|
51CTO旗下网站
|
|
移动端

记一次生产数据库优化--定期归档大表

最近系统总是卡顿,因为老系统,也看不到代码,所以只能从数据库层面去分析了,下面记录下问题排查过程。

作者:波波说运维来源:今日头条|2019-08-19 01:34

最近系统总是卡顿,因为老系统,也看不到代码,所以只能从数据库层面去分析了,下面记录下问题排查过程。

记一次生产数据库优化--定期归档大表

1. 查看超过10s的sql

  1. SELECT 'kill -9 '||p.spid,/*p.spid,p.pid,*/s.sid,s.username,s.machine,s.sql_hash_value,s.last_call_et 秒,s.last_call_et/ 60 运行时间,s.client_info,p.program "OSProgram", 
  2. 'alter system kill session ''' ||s.SID||','||s.SERIAL#|| ''';' 
  3. FROM v$session s, v$process p 
  4. WHERE (s.status = 'ACTIVE' ) AND ((s.username IS NOT NULL) 
  5. AND (NVL (s.osuser, 'x') <> 'SYSTEM') AND (s.TYPE <> 'BACKGROUND')) AND (p.addr(+) = s.paddr) 
  6. --and s.username in ('CRMDB')  
  7. and s.last_call_et > 10 
  8. /*and s.sql_hash_value=880766746*/ 
  9. ORDER BY s.last_call_et/60 desc,"USERNAME" ASC , ownerid, "USERNAME" ASC; 

记一次生产数据库优化--定期归档大表

2. 获取具体sql

  1. select sql_id from v$session where sid=1016 
  2. --ats0x10k9m619 
  3. select listagg(sql_text,' ') within group (order by piece) 
  4.  from v$sqltext 
  5.  where sql_id = 'ats0x10k9m619' 
  6.  group by sql_id 

记一次生产数据库优化--定期归档大表

3. 问题sql

  1. select o.order_release_gid, o.order_release_gid 
  2.  from ORDER_RELEASE o, ORDER_RELEASE_TYPE ort 
  3.  where (o.order_release_type_gid = ort.order_release_type_gid) 
  4.  and (o.order_release_gid in 
  5.  (select ors2.order_release_gid 
  6.  from STATUS_VALUE sv2, ORDER_RELEASE_STATUS ors2 
  7.  where (sv2.status_value_xid in (:1, :2, :3)) 
  8.  and (ors2.status_value_gid = sv2.status_value_gid))) 
  9.  and (o.order_release_gid in 
  10.  (select ors1.ord er_release_gid 
  11.  from STATUS_VALUE sv1, ORDER_RELEASE_STATUS ors1 
  12.  where (sv1.status_value_xid = :4) 
  13.  and (ors1.status_value_gid = sv1.status_value_gid))) 
  14.  and (ort.order_release_type_xid in (:5)) 
  15.  order by o.insert_date desc 

4. 获取sql详细信息

  1. SQL> @/home/oracle/sql/spoolsql.sql 

注:两个sqlid其实都是同一条sql。

记一次生产数据库优化--定期归档大表

结果如下:

记一次生产数据库优化--定期归档大表

记一次生产数据库优化--定期归档大表

5. 执行计划

执行计划没什么好入手的。

记一次生产数据库优化--定期归档大表

6. 各表数据量情况

观察一下表的数据量,发现有一张表达到4千万的数据,而ORDER_RELEASE_STATUS表只是记录订单状态,业务确认是可以只保留2个月内数据

记一次生产数据库优化--定期归档大表

7. 大表索引情况

检查下索引情况:

  1. select SEGMENT_NAME, BYTES / 1024 / 1024 
  2.  from dba_segments 
  3.  where segment_name IN ('IX_ORS_STSVALGID', 'ORS_ORGID', 'IX_ORS_STSVGID', 
  4.  'PK_ORDER_RELEASE_STATUS'); 

记一次生产数据库优化--定期归档大表

记一次生产数据库优化--定期归档大表

8. 定期归档ORDER_RELEASE_STATUS大表

这张表一个月差不多600万的数据,最后跟业务确认只保留2个月数据

  1. --创建归档表 
  2. create table archive.ORDER_RELEASE_STATUS_DMP2 as select * from ORDER_RELEASE_STATUS where 1=2
  3. --创建存储过程: 
  4. CREATE OR REPLACE PROCEDURE glogowner.p_archive_order_tables AS 
  5. BEGIN 
  6. ----转移 ORDER_RELEASE_STATUS最近2个月数据到ORDER_RELEASE_STATUS_DMP2 
  7. insert into archive.ORDER_RELEASE_STATUS_DMP2 select * from ORDER_RELEASE_STATUS t where t.insert_date< trunc(sysdate-60);  
  8. DELETE FROM ORDER_RELEASE_STATUS t where t.insert_date< trunc(sysdate-60); 
  9. COMMIT; 
  10. EXCEPTION WHEN OTHERS THEN 
  11.  ROLLBACK; 
  12. END p_archive_order_tables; 

记一次生产数据库优化--定期归档大表

9. 设置定时任务

(1) 设置定时任务

  1. BEGIN 
  2.  dbms_scheduler.create_job(job_name => 'ARCHIVE_ORDER_TABLES', 
  3.  job_type => 'STORED_PROCEDURE', 
  4.  job_action => 'glogowner.p_archive_order_tables', 
  5.  start_date => to_date('13-08-2019 18:00:00', 'dd-mm-yyyy hh24:mi:ss'), 
  6.  repeat_interval => 'freq=daily;byday=SUN;byhour=00;byminute=30;bysecond=0', 
  7.  enabled => TRUE, 
  8.  comments => '每周日12点30分归档订单发放表'); 
  9. end; 

记一次生产数据库优化--定期归档大表

(2) 查看定时job

  1. select owner, 
  2.  job_name, 
  3.  job_type, 
  4.  job_action, 
  5.  comments, 
  6.  enabled, 
  7.  to_char(last_start_date, 'yyyy-mm-dd hh24:mi:ss'), 
  8.  to_char(next_run_date, 'yyyy-mm-dd hh24:mi:ss') 
  9.  from dba_scheduler_jobs; 

记一次生产数据库优化--定期归档大表

【编辑推荐】

  1. MySQL DBA面试常见问题总结:索引,事务,存储引擎,优化等
  2. 程序员必备!关系型数据库架构的超强总结
  3. 一文告诉你全世界头部的开发者都在使用什么数据库
  4. SQLite AUTOINCREMENT你需要懂得知识
  5. 分库分表 or NewSQL数据库?终于看懂应该怎么选!
【责任编辑:赵宁宁 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

这就是5G

这就是5G

5G那些事儿
共15章 | armmay

115人订阅学习

16招轻松掌握PPT技巧

16招轻松掌握PPT技巧

GET职场加薪技能
共16章 | 晒书包

371人订阅学习

20个局域网建设改造案例

20个局域网建设改造案例

网络搭建技巧
共20章 | 捷哥CCIE

758人订阅学习

读 书 +更多

C++编程你也行

本书是一本优秀的C++教材,内容包括:基础类型、操作符和简单变量,循环和决策,命名空间和C++标准库,用C++编写函数,行为、序列点和求值...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微