ORACLE常用SQL指令碼2

看書筆記. 共參考,

shared pool latch競爭和lock的解決

[@more@]

-----取得隱藏引數;
set linesize 132
column name format a30
column value format a25
select x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf, 7),
1,
'MODIFIED',
4,
'SYSTEM_MOD',
'FALSE') ismod,
decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj
from sys.x$ksppi x, sys.x$ksppcv y
where x.inst_id = userenv('Instance')
and y.inst_id = userenv('Instance')
and x.indx = y.indx
and x.ksppinm like '_&par%'
order by translate(x.ksppinm, '_', '');

----Server程式掃描LRU超過40%還沒能找到足夠的FREE BUFFER就會停止搜尋.通知DBWN執行寫資料;
select kvittag,kvitval,kvitdsc from x$kvit
where kvittag='kcbfsp';

----DBwn 主動掃描LRU List,將發現的Dirty Buffer移入Checkpoint queue,9i掃描比例:25%
select kvittag,kvitval,kvitdsc from x$kvit
where kvittag='kcbdsp';

----檢視DB BUFFER LATCH
select addr,latch#,name,gets,misses,immediate_gets,immediate_misses
from v$latch where name='cache buffers lru chain';

===========解決BUFFER LATCH競爭===========

----檢視DB BUFFER 各子LATCH使用情況:
select addr,child#,name,get,misses,immediate_gets,immediate_misses from v$latch_children where name ='cache buffers lur chain';
(子LATCH的數量由隱藏引數_db_block_lru_latches限定)

----當前資料庫最繁忙資料塊的BUFFER 和LATCH資訊
select b.addr,
a.ts#,
a.dbarfil,
a.dbablk,
a.tch,
b.gets,
b.misses,
b.sleeps
from (select *
from (select addr, ts#, file#, dbarfil, dbablk, tch,hladdr
from x$bh
order by tch desc)
where rownum < 11) a,
(select addr, gets, misses, sleeps
from v$latch_children
where name = 'cache buffers chains') b
where a.hladdr = b.addr

----最繁忙的BUFFER來自哪些物件;
select e.owner, e.segment_name, e.segment_type
from dba_extents e,
(select *
from (select addr, ts#, file#, dbarfil, dbablk, tch
from x$bh
order by tch desc)
where rownum < 11) b
where e.relative_fno = b.dbarfil
and e.block_id <= b.dbablk
and e.block_id + e.blocks > b.dbablk;

----最繁忙的BUFFER是哪些SQL在操作;
select /*+rule*/
hash_value, sql_text
from v$sqltext
where (hash_value, address) in
(select a.hash_value, a.address
from v$sqltext a,
(select distinct a.owner, a.segment_name, a.segment_type
from dba_extents a,
(select dbarfil, dbablk
from (select dbarfil, dbablk
from x$bh
order by tch desc)
where rownum < 11) b
where a.relative_fno = b.dbarfil
and a.block_id <= b.dbablk
and a.block_id + a.blocks > b.dbablk) b
where a.sql_text like '%' || b.segment_name || '%'
and b.segment_type = 'TABLE')
order by hash_value, address, piece;

===========解決Library Cache Pin等待===========

----獲得Library Cache Pin等待的物件;
select addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
from x$kglob
where kglhdadr in
(select p1raw from v$session_wait where event like 'library%')

----獲得以上持有等待物件的SESSION資訊
select a.sid,
a.username,
a.program,
b.addr,
b.kglpnadr,
b.kglpnuse,
b.kglpnses,
b.kglpnhdl,
b.kglpnlck,
b.kglpnmod,
b.kglpnreq
from v$session a, v$kglpn b
where a.saddr = b.kglpnuse
and b.kglpnmod <> 0
and b.kglpnhdl in
(select p1raw from v$session_wait where event like 'library%');

----獲得以上持有物件使用者執行的程式碼;

select sql_text
from v$sqlarea
where (v$sqlarea.address, v$sqlarea.hash_value) in
(select sql_address, sql_hash_value
from v$session
where sid in (select sid
from v$session a, v$kglpn b
where a.saddr = b.kglpnuse
and kglpnmod <> 0
and b.kglpnhdl in
(select p1raw
from v$session_wait
where event like 'library%')));

================Library Cache Lock 等待事件============
select * from v$session_wait where event = 'library cache lock';