oracle 找出最耗資源的sql (dolphin_ygj)

=================================

一、要找出最耗資源的sql

我們可以首先使用top等工具，找到最好資源的程序（記住程序号），例如，作業系統程序号為2796，然後根據這個程序号(v$process.spid)在v$process中找到程序位址(v$process.addr)，然後根據這個位址在v$session中找到相應的sid(v$session.sid)，然後根據這個sid找到相應的hash alue（v$session. sql_hash_value），然後根據這個hash alue在v$sqltext，$sql，v$sqlarea等視圖中找到對應的sql語句（sql_text）。

　　select * from v$process where spid='2796';

　　select sql_hash_value,machine,username,program from v$session where PAddr='63B7A584';

　　select * from v$sqltext where hashvalue='833203018';

　　select * from v$sql where hashvalue='833203018';

　　select * from v$sqlarea where hashvalue='833203018';

　　SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid='2796' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece

 ============================================

 select sql_text

from v$sqltext

where (hash_value,sql_address) in

(

select decode(sql_hash_value,0,prev_hash_value,sql_hash_value),

          decode(sql_hash_value,0,prev_sql_addr,sql_address)

from v$session

where paddr = (select addr from v$process where spid='作業系統程序id')

)

=================================================

二、oracle 被鎖問題集及解決方案

 1.錯誤資訊:ORA-28000: the account is locked

原因分析: The user has entered wrong password consequently for maximum number of times specified by the user's profile parameter FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account

解決辦法: Wait for PASSWORD_LOCK_TIME or contact DBA

              2.視圖被鎖住的問題

解決的辦法：

1.找出等待事件的原因

select event,p1,p2,sid from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';

2.找出鎖住的程序

select object_id,session_id,locked_mode from v$locked_object;

3.找出鎖住的程序的作業系統程序

select spid from v$process where addr=(select paddr from v$session where sid=144);（這個19表示被鎖定的SID）

4.

4.在作業系統上删除程序

orakill eoffice 2768（第一個參資料是資料庫的SID  第二個參數是程序ID，這個數字就是上面取到的程序ID，這個是在Dos指令視窗下操作）

下面是一個操作執行個體

首先先Drop 相關的鎖定的對象如Drop View vw_wf_CritCondition，然後結束PL/SQL Dev 再開一次才能執行下面的操作。

SQL> select event,p1,p2,sid from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';

EVENT                                                                    P1         P2        SID

---------------------------------------------------------------- ---------- ---------- ----------

null event                                                       1413697536          1         18

null event                                                                1      95552         22

pmon timer                                                              300          0          1

smon timer                                                              300          0          5

library cache lock                                                763239564  792075180         23   （這裡表示有一個庫被鎖掉了。）

wakeup time manager                                                       0          0          8

6 rows selected

SQL> select object_id,session_id,locked_mode from v$locked_object;

 OBJECT_ID SESSION_ID LOCKED_MODE

---------- ---------- -----------

        77         22           3

        69         22           3

        70         22           3

       316         22           3

       314         22           3

       356         22           3

        68         22           3

        72         22           3

        73         22           3

        74         22           3

        75         22           3

11 rows selected

SQL> select spid from v$process where addr=(select paddr from v$session where sid=22);

SPID

------------

3348

在Dos操作，而且隻能在安裝資料的本機操作

orakill eoffice 3348

3.查出Oracle 資料庫中的鎖等待

首先，以 dba 身份（不一定為system）登入入資料庫中，建立三個基本表:my_session，my_lock, my_sqltext，并在将會進行查詢的列上建立相應的索引。語句如下： rem 從 v$session 視圖中取出關心的字段，建立 my_session 表,并在查詢要用到的字段上建立索引，以加快查詢速度

drop table my_session;

create table my_session

as

select a.username, a.sid, a.serial#,

a.lockwait, a.machine,a.status,

a.last_call_et,a.sql_hash_value,a.program

from v$session a

where 1=2 ;

create unique index my_session_u1 on my_session(sid);

create index my_session_n2 on my_session(lockwait);

create index my_session_n3 on my_session(sql_hash_value);

---- rem 從 v$lock 視圖中取出字段，建立 my_lock 表，并在查詢要用到的字段上建立索引，以加快查詢速度

drop table my_lock;

create table my_lock

select id1, kaddr, sid, request,type

from v$lock

where 1=2;

create index my_lock_n1 on my_lock(sid);

create index my_lock_n2 on my_lock(kaddr);

---- rem 從 v$sqltext 視圖中取出字段，建立 my_sqltext 表，并在查詢要用到的字段上建立索引，以加快查詢速度

drop table my_sqltext;

create table my_sqltext

select hash_value , sql_text

create index my_sqltext_n1 on my_sqltext ( hash_value);

---- 然後，建立一個 SQL 腳本檔案，以便需要時可從 SQL*Plus 中直接調用。其中，首先用 truncate table 表名指令将表中的記錄删除。之是以用 truncate 指令，而不是用delete 指令，是因為delete 指令執行時，将會産生重演記錄，速度較慢，而且索引所占的空間并未真正釋放，若反複做 insert及delete,則索引所占的空間會不斷增長，查詢速度也會變慢。而 truncate指令不産生重演記錄，速度執行較delete快，而且索引空間被相應地釋放出來。删除記錄後，再将三個視圖中的相關記錄插入自己建立的三個表中。最後，對其進行查詢，由于有索引，同時由于在插入時條件過濾後，記錄數相對來說較少，因而查詢速度很快，馬上可以看到其結果。

---- 此時，若發現該阻塞其它使用者程序的程序是正常操作中，則可通知該使用者對其進行送出，進而達到釋放鎖資源的目的；若為未正常操作，即，其狀态為"inactive"，且其last_call_et已為較多長時間，則可執行以下語句将該程序進行清除，系統會自動對其進行復原，進而釋放鎖住的資源。

alter system kill session 'sid, serial#';

---- SQL 腳本如下：

set echo off

set feedback off

prompt '删除舊記錄.....'

truncate table my_session;

truncate table my_lock;

truncate table my_sqltext;

prompt '擷取資料.....'

insert into my_session

where nvl(a.username,'NULL')< >'NULL;

insert into my_lock

from v$lock;

insert into my_sqltext

from v$sqltext s, my_session m

where s.hashvalue=m.sql_hash_value;

column username format a10

column machine format a15

column last_call_et format 99999 heading "Seconds"

column sid format 9999

prompt "正在等待别人的使用者"

select a.sid, a.serial#,

a.machine,a.last_call_et, a.username, b.id1

from my_session a, my_lock b

where a.lockwait = b.kaddr;

prompt "被等待的使用者"

a. machine, a.last_call_et,a.username,

b. b.type,a.status,b.id1

where b.id1 in

(select distinct e.id1

from my_session d, my_lock e

where d.lockwait = e.kaddr)

and a.sid = b.sid

and b.request=0;

prompt "查出其 sql "

b.id1, b.type, c.sql_text

from my_session a, my_lock b, my_sqltext c

and b.request=0

and c.hash_value =a.sql_hash_value;

---- 以上思路也可用于其它大型資料庫系統如 Informix, Sybase,DB2中。通過使用該腳本，可以極大地提高擷取系統中目前鎖等待的情況，進而及時解決資料庫應用系統中的鎖等待問題。而且，由于實際上已取出其 program 名及相應的 sql 語句，故可以在事後将其記錄下來，交給其開發人員進行分析并從根本上得到解決