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

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

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

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

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; 

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

责任编辑:赵宁宁 来源: 今日头条
相关推荐

2019-11-18 13:42:55

MySQL数据库迁移

2018-12-06 16:25:39

数据库服务器线程池

2019-09-27 17:24:26

数据库优化sql

2019-11-22 08:05:01

数据库mysql分区

2019-12-12 10:38:10

mysql数据库nnodb

2019-12-16 07:18:42

数据库SQL代码

2019-07-25 08:30:58

数据库服务器故障

2019-01-21 11:17:13

CPU优化定位

2019-09-05 09:17:37

MySQL数据库线程

2019-09-08 17:52:10

数据库log file sy等待事件

2019-12-02 08:09:57

境数据库连接超时自动回收

2021-03-01 06:14:50

环境高并发延迟

2019-08-15 11:30:06

SQL数据库ASH

2018-07-18 15:37:24

数据库DB2故障处理

2020-09-25 07:57:42

生产事故系统

2020-11-03 07:34:12

Kafka后端工程师

2022-06-01 06:17:42

微服务Kafka

2021-10-14 10:53:20

数据库查询超时

2021-01-12 07:57:36

MySQLBinlog故障处理

2019-12-27 10:43:48

磁盘数据库死锁
点赞
收藏

51CTO技术栈公众号