前言:
当前我们对“oracle查询某个表每次都卡顿怎么办”大约比较关注,同学们都需要知道一些“oracle查询某个表每次都卡顿怎么办”的相关资讯。那么小编同时在网摘上搜集了一些关于“oracle查询某个表每次都卡顿怎么办””的相关文章,希望姐妹们能喜欢,大家快快来了解一下吧!概述
最近系统总是卡顿,因为老系统,也看不到代码,所以只能从数据库层面去分析了,下面记录下问题排查过程。
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 pWHERE (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--ats0x10k9m619select listagg(sql_text,' ') within group (order by piece) from v$sqltext where sql_id = 'ats0x10k9m619' group by sql_id3、问题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 desc4、获取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 ASBEGIN----转移 ORDER_RELEASE_STATUS最近2个月数据到ORDER_RELEASE_STATUS_DMP2insert 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、设置定时任务
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;/
9.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;
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~
版权声明:
本站文章均来自互联网搜集,如有侵犯您的权益,请联系我们删除,谢谢。