副问题[/!--empirenews.page--]
最近体系老是卡顿,由于老体系,也看不到代码,以是只能从数据库层面去说明白,下面记录下题目排查进程。
![记一次出产数据库优化--按期归档大表](http://img25.aspzz.cn/uploads/allimg/c190819/15B15b4123560-1G58.jpg)
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;
![记一次出产数据库优化--按期归档大表](http://img25.aspzz.cn/uploads/allimg/c190819/15B15b414150-22Q9.jpg)
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
![记一次出产数据库优化--按期归档大表](http://img25.aspzz.cn/uploads/allimg/c190819/15B15b41EL0-3QI.jpg)
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。
![记一次出产数据库优化--按期归档大表](http://img25.aspzz.cn/uploads/allimg/c190819/15B15b4191X0-4R23.jpg)
功效如下:
![记一次出产数据库优化--按期归档大表](http://img25.aspzz.cn/uploads/allimg/c190819/15B15b4210Y0-5a31.jpg)
![记一次出产数据库优化--按期归档大表](http://img25.aspzz.cn/uploads/allimg/c190819/15B15b4230240-BN1.jpg)
5. 执行打算
执行打算没什么好入手的。
![记一次出产数据库优化--按期归档大表](http://img25.aspzz.cn/uploads/allimg/c190819/15B15b4249350-K034.jpg)
6. 各表数据量环境
调查一下表的数据量,发明有一张表到达4万万的数据,而ORDER_RELEASE_STATUS表只是记录订单状态,营业确认是可以只保存2个月内数据
![记一次出产数据库优化--按期归档大表](http://img25.aspzz.cn/uploads/allimg/c190819/15B15b42F3F-U394.jpg)
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');
![记一次出产数据库优化--按期归档大表](http://img25.aspzz.cn/uploads/allimg/c190819/15B15b42T530-91495.jpg)
![记一次出产数据库优化--按期归档大表](http://img25.aspzz.cn/uploads/allimg/c190819/15B15b42c610-10U07.jpg)
8. 按期归档ORDER_RELEASE_STATUS大表
(编辑:河北网)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|