十年網(wǎng)站開發(fā)經(jīng)驗 + 多家企業(yè)客戶 + 靠譜的建站團(tuán)隊
量身定制 + 運營維護(hù)+專業(yè)推廣+無憂售后,網(wǎng)站問題一站解決
背景:數(shù)據(jù)庫出現(xiàn)死鎖會話飆升的情況通過下列預(yù)計可以快速定位常見的鎖,快速干預(yù)處理,恢復(fù)數(shù)據(jù)庫性能。通過下列語句長期運維?T以上數(shù)據(jù)庫?個,屢試不爽。
為友誼等地區(qū)用戶提供了全套網(wǎng)頁設(shè)計制作服務(wù),及友誼網(wǎng)站建設(shè)行業(yè)解決方案。主營業(yè)務(wù)為成都網(wǎng)站制作、成都做網(wǎng)站、外貿(mào)營銷網(wǎng)站建設(shè)、友誼網(wǎng)站設(shè)計,以傳統(tǒng)方式定制建設(shè)網(wǎng)站,并提供域名空間備案等一條龍服務(wù),秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務(wù)。我們深信只要達(dá)到每一位用戶的要求,就會得到認(rèn)可,從而選擇與我們長期合作。這樣,我們也可以走得更遠(yuǎn)!
一、查詢出死鎖的SID等信息
SELECT l.session_id sid,s.serial#,l.locked_mode,l.oracle_username,l.os_user_name,
s.machine,s.terminal,o.object_name,s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id AND l.session_id = s.sid
ORDER BY sid, s.serial#;
二、根據(jù)SID定位阻塞語句
SELECT /+ PUSH_SUBQ /
Command_Type, Sql_Text, Sharable_Mem, Persistent_Mem, Runtime_Mem, Sorts,Version_Count, Loaded_Versions, Open_Versions, Users_Opening, Executions,Users_Executing, Loads, First_Load_Time, Invalidations, Parse_Calls,Disk_Reads, Buffer_Gets, Rows_Processed, SYSDATE Start_Time,
SYSDATE Finish_Time, '>' || Address Sql_Address, 'N' Status
FROM V$sqlarea
WHERE Address = (SELECT Sql_Address FROM V$session WHERE Sid = ? );
三、殺死鎖
--殺死鎖(數(shù)據(jù)庫層次--適合不太緊急場合)
select 'alter system kill session '||chr(39)||t2.sid||','||t2.serial#||chr(39)||'immediate;'
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time
--殺死鎖(操作系統(tǒng)層次--適合緊急場合)
select 'kill -9 '||t3.spid
from v$locked_object t1,v$session t2 , v$process t3
where t1.session_id=t2.sid And t2.paddr = t3.addr order by t2.logon_time
附日常會話查詢語句:
--所有會話信息
Select From v$session
Select Count() From v$session
--會話關(guān)鍵信息
Select USERNAME,status,state,MACHINE,logon_time From V$SESSION Order By username,MACHINE