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

1. 查看超过10s的sql

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",
'altersystemkillsession'''||s.SID||','||s.SERIAL#||''';'
FROMv$sessions,v$processp
WHERE(s.status='ACTIVE')AND((s.usernameISNOTNULL)
AND(NVL(s.osuser,'x')<>'SYSTEM')AND(s.TYPE<>'BACKGROUND'))AND(p.addr(+)=s.paddr)
--ands.usernamein('CRMDB')
ands.last_call_et>10
/*ands.sql_hash_value=880766746*/
ORDERBYs.last_call_et/60desc,"USERNAME"ASC,ownerid,"USERNAME"ASC;

2. 获取具体sql

selectsql_idfromv$sessionwheresid=1016
--ats0x10k9m619
selectlistagg(sql_text,'')withingroup(orderbypiece)
fromv$sqltext
wheresql_id='ats0x10k9m619'
groupbysql_id

3. 问题sql

selecto.order_release_gid,o.order_release_gid
fromORDER_RELEASEo,ORDER_RELEASE_TYPEort
where(o.order_release_type_gid=ort.order_release_type_gid)
and(o.order_release_gidin
(selectors2.order_release_gid
fromSTATUS_VALUEsv2,ORDER_RELEASE_STATUSors2
where(sv2.status_value_xidin(:1,:2,:3))
and(ors2.status_value_gid=sv2.status_value_gid)))
and(o.order_release_gidin
(selectors1.order_release_gid
fromSTATUS_VALUEsv1,ORDER_RELEASE_STATUSors1
where(sv1.status_value_xid=:4)
and(ors1.status_value_gid=sv1.status_value_gid)))
and(ort.order_release_type_xidin(:5))
orderbyo.insert_datedesc

4. 获取sql详细信息

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

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

结果如下：

5. 执行计划

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

6. 各表数据量情况

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

7. 大表索引情况

检查下索引情况：

selectSEGMENT_NAME,BYTES/1024/1024
fromdba_segments
wheresegment_nameIN('IX_ORS_STSVALGID','ORS_ORGID','IX_ORS_STSVGID',
'PK_ORDER_RELEASE_STATUS');

8. 定期归档ORDER_RELEASE_STATUS大表

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

--创建归档表
createtablearchive.ORDER_RELEASE_STATUS_DMP2asselect*fromORDER_RELEASE_STATUSwhere1=2;
--创建存储过程：
CREATEORREPLACEPROCEDUREglogowner.p_archive_order_tablesAS
BEGIN
----转移ORDER_RELEASE_STATUS最近2个月数据到ORDER_RELEASE_STATUS_DMP2
insertintoarchive.ORDER_RELEASE_STATUS_DMP2select*fromORDER_RELEASE_STATUStwheret.insert_date<trunc(sysdate-60);
DELETEFROMORDER_RELEASE_STATUStwheret.insert_date<trunc(sysdate-60);
COMMIT;
EXCEPTIONWHENOTHERSTHEN
ROLLBACK;
ENDp_archive_order_tables;
/

9. 设置定时任务

(1) 设置定时任务

BEGIN
dbms_scheduler.create_job(job_name=>'ARCHIVE_ORDER_TABLES',
job_type=>'STORED_PROCEDURE',
job_action=>'glogowner.p_archive_order_tables',
start_date=>to_date('13-08-201918:00:00','dd-mm-yyyyhh24:mi:ss'),
repeat_interval=>'freq=daily;byday=SUN;byhour=00;byminute=30;bysecond=0',
enabled=>TRUE,
comments=>'每周日12点30分归档订单发放表');
end;
/

(2) 查看定时job

selectowner,
job_name,
job_type,
job_action,
comments,
enabled,
to_char(last_start_date,'yyyy-mm-ddhh24:mi:ss'),
to_char(next_run_date,'yyyy-mm-ddhh24:mi:ss')
fromdba_scheduler_jobs;