最近系統總是卡頓，因為老系統，也看不到代碼，所以只能從數據庫層面去分析了，下面記錄下問題排查過程。

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", 
'alter system kill session ''' ||s.SID||','||s.SERIAL#|| ''';' 
FROM v$session s, v$process p 
WHERE (s.status = 'ACTIVE' ) AND ((s.username IS NOT NULL) 
AND (NVL (s.osuser, 'x') <> 'SYSTEM') AND (s.TYPE <> 'BACKGROUND')) AND (p.addr(+) = s.paddr) 
--and s.username in ('CRMDB')  
and s.last_call_et > 10 
/*and s.sql_hash_value=880766746*/ 
ORDER BY s.last_call_et/60 desc,"USERNAME" ASC , ownerid, "USERNAME" ASC; 

2. 獲取具體sql

select sql_id from v$session where sid=1016 
--ats0x10k9m619 
select listagg(sql_text,' ') within group (order by piece) 
 from v$sqltext 
 where sql_id = 'ats0x10k9m619' 
 group by sql_id 

3. 問題sql

select o.order_release_gid, o.order_release_gid 
 from ORDER_RELEASE o, ORDER_RELEASE_TYPE ort 
 where (o.order_release_type_gid = ort.order_release_type_gid) 
 and (o.order_release_gid in 
 (select ors2.order_release_gid 
 from STATUS_VALUE sv2, ORDER_RELEASE_STATUS ors2 
 where (sv2.status_value_xid in (:1, :2, :3)) 
 and (ors2.status_value_gid = sv2.status_value_gid))) 
 and (o.order_release_gid in 
 (select ors1.ord er_release_gid 
 from STATUS_VALUE sv1, ORDER_RELEASE_STATUS ors1 
 where (sv1.status_value_xid = :4) 
 and (ors1.status_value_gid = sv1.status_value_gid))) 
 and (ort.order_release_type_xid in (:5)) 
 order by o.insert_date desc 

4. 獲取sql詳細信息

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

注：兩個sqlid其實都是同一條sql。

結果如下：

5. 執行計劃

執行計劃沒什么好入手的。

6. 各表數據量情況

觀察一下表的數據量，發現有一張表達到4千萬的數據，而ORDER_RELEASE_STATUS表只是記錄訂單狀態，業務確認是可以只保留2個月內數據

7. 大表索引情況

檢查下索引情況：

select SEGMENT_NAME, BYTES / 1024 / 1024 
 from dba_segments 
 where segment_name IN ('IX_ORS_STSVALGID', 'ORS_ORGID', 'IX_ORS_STSVGID', 
 'PK_ORDER_RELEASE_STATUS'); 

8. 定期歸檔ORDER_RELEASE_STATUS大表

這張表一個月差不多600萬的數據，最后跟業務確認只保留2個月數據

--創建歸檔表 
create table archive.ORDER_RELEASE_STATUS_DMP2 as select * from ORDER_RELEASE_STATUS where 1=2; 
--創建存儲過程： 
CREATE OR REPLACE PROCEDURE glogowner.p_archive_order_tables AS 
BEGIN 
----轉移 ORDER_RELEASE_STATUS最近2個月數據到ORDER_RELEASE_STATUS_DMP2 
insert into archive.ORDER_RELEASE_STATUS_DMP2 select * from ORDER_RELEASE_STATUS t where t.insert_date< trunc(sysdate-60);  
DELETE FROM ORDER_RELEASE_STATUS t where t.insert_date< trunc(sysdate-60); 
COMMIT; 
EXCEPTION WHEN OTHERS THEN 
 ROLLBACK; 
END p_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-2019 18:00:00', 'dd-mm-yyyy hh24:mi:ss'), 
 repeat_interval => 'freq=daily;byday=SUN;byhour=00;byminute=30;bysecond=0', 
 enabled => TRUE, 
 comments => '每周日12點30分歸檔訂單發放表'); 
end; 
/ 

(2) 查看定時job

select owner, 
 job_name, 
 job_type, 
 job_action, 
 comments, 
 enabled, 
 to_char(last_start_date, 'yyyy-mm-dd hh24:mi:ss'), 
 to_char(next_run_date, 'yyyy-mm-dd hh24:mi:ss') 
 from dba_scheduler_jobs;