DBA日常維護(hù)SQL整理
database 概況信息檢查
# 檢查 database 基本信息
select * from v$version;
select name ,open_mode,log_mode from v$database;
select instance_number,instance_name ,status from gv$instance;
show parameter cpu_count
show parameter block_size
select group#,thread#,members,bytes/1024/1024 from gv$log;
show sga
select count(*) from v$controlfile
select count(*) from v$tempfile;
select count(*) from v$datafile;
查看數(shù)據(jù)文件信息
# 檢查表空間數(shù)據(jù)文件信息
col tablespace_name for a30
select tablespace_name , sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name;
# 檢查表空間
SELECT
TABLESPACE_NAME,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE
_MANAGEMENT FROM DBA_TABLESPACES;
# 檢查數(shù)據(jù)文件狀態(tài)
select count(*),status from v$datafile group by status;
# 檢查表空間使用情況
select
f.tablespace_name,
a.total,
f.free,(a.total-f.free)/1024 "used SIZE(G)"
,round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by
tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free"
/
# 查詢臨時(shí) segment 使用情況
COL username FORMAT a10;
COL segtype FORMAT a10;
SELECT username, segtype, extents "Extents Allocated"
,blocks "Blocks Allocated"
FROM v$tempseg_usage;
# 查看數(shù)據(jù)文件信息 , 若文件較多可以根據(jù)需要字段進(jìn)行排序 輸出 top 10
col datafile for a60
SELECT fs.phyrds "Reads", fs.phywrts "Writes"
,fs.avgiotim "Average I/O Time", df.name "Datafile"
FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#;
# 查看所有數(shù)據(jù)文件 i/ o 情況 , 若文件太多 , 可以改寫(xiě) 為 top 10 select *( order by xx desc) where
rownum<=10。其中phyrds為物理讀的次數(shù)極為Reads，phywrts為物理寫(xiě)的次數(shù)極為Writes，phyblkrd為物理塊讀的次數(shù)即為br，phyblkwrt為物理寫(xiě)的次數(shù)即為bw。readtime為耗費(fèi)在物理讀上的總時(shí)間極為RTime，writetim為耗費(fèi)在物理寫(xiě)上的總時(shí)間即為WTime。這兩個(gè)值只有在參數(shù)timed_statistics參數(shù)為true時(shí)才有效。
COL ts FORMAT a10 HEADING "Tablespace";
COL reads FORMAT 999990;
COL writes FORMAT 999990;
COL br FORMAT 999990 HEADING "BlksRead";
COL bw FORMAT 999990 HEADING "BlksWrite";
COL rtime FORMAT 999990;
COL wtime FORMAT 999990;
SELECT ts.name AS ts, fs.phyrds "Reads", fs.phywrts "Writes"
,fs.phyblkrd AS br, fs.phyblkwrt AS bw
,fs.readtim "RTime", fs.writetim "WTime"
FROM v$tablespace ts, v$datafile df, v$filestat fs
WHERE ts.ts# = df.ts# AND df.file# = fs.file#
UNION
SELECT ts.name AS ts, ts.phyrds "Reads", ts.phywrts "Writes",ts.phyblkrd AS br, ts.phyblkwrt AS bw
,ts.readtim "RTime", ts.writetim "WTime"
FROM v$tablespace ts, v$tempfile tf, v$tempstat ts
WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;
# 獲取 top 10 熱 segment
set linesize 180
col object_name for a40
select * from
(select
ob.owner, ob.object_name, sum(b.tch) Touchs
from x$bh b , dba_objects ob
where b.obj = ob.data_object_id
and b.ts# > 0
group by ob.owner, ob.object_name
order by sum(tch) desc)
where rownum <=10
# 判斷物理讀最多的 object
select * from (select owner,object_name,value from v$segment_statistics where
statistic_name='physical reads' order by value desc) where rownum<=10
# 查看熱點(diǎn)數(shù)據(jù)文件 ( 從單塊讀取時(shí)間判斷 )
col FILE_NAME for a60
set linesize 180
SELECT t.file_name,
t.tablespace_name,
round(s.singleblkrdtim/s.singleblkrds, 2) AS CS,
s.READTIM,
s.WRITETIM
FROM v$filestat s, dba_data_files t
WHERE s.file# = t.file_id and s.singleblkrds <>0 and rownum<=10 order by cs desc
查看redo
# 檢查日志切換頻率
select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss')
firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from
v$log_history where first_time > sysdate - 1 order by first_time ,minutes;
# 檢查 lgwr i/o 性能 (time_waited/total_waits:表示平均lgwr寫(xiě)入完成時(shí)間若>1表示寫(xiě)入過(guò)慢 )
select total_waits,time_waited,average_wait,time_waited/total_waits as avg from
v$system_event where event = 'log file parallel write';
# 查詢 redo block size
select max(lebsz) from x$kccle;
# 查看 user commit 次數(shù)
select to_number(value,99999999999) from v$sysstat where name='user commits';
# 查看系統(tǒng)運(yùn)行時(shí)間
select (sysdate - startup_time)*24*60*60 as seconds from v$instance
# 計(jì)算出每秒用戶提交次數(shù)
select user_commit 次數(shù) / 系統(tǒng)運(yùn)行時(shí)間 from dual;
# 計(jì)算出每個(gè)事務(wù)平均處理多少個(gè) redo block
select value from v$sysstat where name = 'redo blocks written';
select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo
blocks written') a ,(select value trancount from v$sysstat where name='user commits') b
sga,pga, 命中率
# sga,pga, 命中率
# 檢查 sga
show sga
select * from v$sga;
# 查看buffer cache設(shè)置建議
select size_for_estimate, estd_physical_read_factor,
to_char(estd_physical_reads,99999999999999999999999) as"estd_physical_reads" from
v$db_cache_advice where name = 'DEFAULT';
COL pool FORMAT a10;
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'db_cache_size') "Current Cache(Mb)"
,name "Pool", size_for_estimate "Projected Cache(Mb)"
,ROUND(100-estd_physical_read_factor,0) "Cache Hit Ratio%"
FROM v$db_cache_advice
WHERE block_size = (SELECT value FROM v$parameter
WHERE name = 'db_block_size')
ORDER BY 3;
# 查看 pga
show parameter pga
# 查看 cache 池
show parameter cache
# 查看 buffer cache 中 defalut pool 命中率
select name,1-(physical_reads)/(consistent_gets+db_block_gets)
from v$buffer_pool_statistics;
# 檢查 shared pool
show parameter shared
# 檢查 shared pool 中 library cache
select namespace,pinhitratio from v$librarycache;
# 檢查整體命中率 (library cache)
select sum(pinhits)/sum(pins) from v$librarycache;
select sum(pins) "hits",
sum(reloads) "misses",
sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio"
from v$librarycache;
# 檢查 shared pool free space
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';
# 每個(gè)子shared pool 由單獨(dú)的 shared pool latch保護(hù)，查看他們的命中率 shared pool latch，用于shared pool空間回收分配使用的latch
col name format a15
select addr,name,gets,misses,1-misses/gets from v$latch_children where name='shared pool';
# 使用 v$shared_pool_advice 計(jì)算不同 shared pool 大小情況下，響應(yīng)時(shí)間， S 單位
SELECT 'Shared Pool' component,
shared_pool_size_for_estimate estd_sp_size,
estd_lc_time_saved_factor parse_time_factor,
CASE
WHEN current_parse_time_elapsed_s + adjustment_s < 0 THEN
0
ELSE
current_parse_time_elapsed_s + adjustment_s
END response_time
FROM (SELECT shared_pool_size_for_estimate,
shared_pool_size_factor,
estd_lc_time_saved_factor,
a.estd_lc_time_saved,
e.VALUE / 100 current_parse_time_elapsed_s,
c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s FROM v$shared_pool_advice a,
(SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e,
(SELECT estd_lc_time_saved FROM v$shared_pool_advice
WHERE shared_pool_size_factor = 1) c)
/
# 查看 shared pool 中 各種類型的 chunk 的大小數(shù)量
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG
SIzE"
FROM X$KSMSP GROUP BY KSMCHCLS;
# 查看是否有庫(kù)緩沖有關(guān)的等待事件
select sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state from v$session_wait where event like
'library%';
# 查詢 sga 中各個(gè) pool 情況
COL name FORMAT a32;
SELECT pool, name, bytes FROM v$sgastat
WHERE pool IS NULL
OR pool != 'shared pool' OR (pool = 'shared pool'
AND (name IN('dictionary cache','enqueue','library
cache','parameters',
'processes','sessions','free memory')))
ORDER BY pool DESC NULLS FIRST, name;
SELECT * FROM V$SGAINFO;
# 查看使用 shard_pool 保留池情況
SELECT request_misses, request_failures, free_space
FROM v$shared_pool_reserved;
Oracle 專門從共享池內(nèi)置出一塊區(qū)域來(lái)來(lái)分配內(nèi)存保持這些大塊。這個(gè)保留共享池的默認(rèn)大小是共享池的5%(_shared_pool_reserved_pct 5 控制 ) oracle 建設(shè)置為 10% 。大小通過(guò)參數(shù) SHARED_POOL_RESERVED_SIZE 改。它是從共享池中分配，不是直接從 SGA 中分配的，它是共享池的保留部分，專門用于存儲(chǔ)大塊段#shared pool 中內(nèi)存大于 _SHARED_POOL_RESERVED_MIN_ALLOC 將放入 shared pool 保留池 , 保留池維護(hù)一個(gè)單獨(dú)的 freelist,lru ，并且不會(huì)在 lru 列表存recreatable 類型 chunks ，普通 shared pool 的釋放與 shared pool 保留池?zé)o關(guān)。
# 關(guān)于設(shè)置 SHARED_POOL_RESERVED_SIZE
#1.如果系統(tǒng)出現(xiàn)ora-04031, 發(fā)現(xiàn)請(qǐng)求內(nèi)存都是大于 _SHARED_POOL_RESERVED_MIN_ALLOC (default 10GR2 4400) , 且v$shared_pool_reserved 中有大量 REQUEST_MISSES( 并且可以看下LAST_MISS_SIZE )表示 SHARED_POOL_RESERVED_SIZE 太小了需要大的內(nèi)存的請(qǐng)求失敗 , 那么需要加大SHARED_POOL_RESERVED_SIZE
#2. 如果 ora-04031 請(qǐng)求內(nèi)存出現(xiàn)在 4100-4400 并造成 shared pool lru 合并 , 老化換出內(nèi)存 , 可以調(diào)小 _SHARED_POOL_RESERVED_MIN_ALLOC 讓此部分內(nèi)存進(jìn)入shared reserved pool, 相應(yīng)的加大SHARED_POOL_RESERVED_SIZE
#3. 從 v$shared_pool_reserved 來(lái)判斷 , 如果 REQUEST_FAILURES>0( 出現(xiàn)過(guò) ora-04031) 且LAST_FAILURE_SIZE( 最后請(qǐng)求內(nèi)存大小 )>_SHARED_POOL_RESERVED_MIN_ALLOC表示 shared reserved pool 缺少連續(xù)內(nèi)存 , 可以加大 SHARED_POOL_RESERVED_SIZE, 減少 _SHARED_POOL_RESERVED_MIN_ALLOC 少放對(duì)象 , 并相對(duì)加大 shared_pool_size
# 要是反過(guò)來(lái) REQUEST_FAILURES>0( 出現(xiàn)過(guò) ora-04031) 且 LAST_FAILURE_SIZE( 最后請(qǐng)求內(nèi)存大小)<_SHARED_POOL_RESERVED_MIN_ALLOC, 表示 在 shared pool 中缺少連續(xù)內(nèi)存 , 可以加減少_SHARED_POOL_RESERVED_MIN_ALLOC 多放入一些對(duì)象 , 減少 sharedpool 壓力 , 適當(dāng)加大shared_pool_size,SHARED_POOL_RESERVED_SIZE
# 查詢還保留在 library cache 中，解析次數(shù)和執(zhí)行次數(shù)最多的 sql( 解析 * 執(zhí)行 )
COL sql_text FORMAT A38;
SELECT * FROM(
SELECT parse_calls*executions "Product", parse_calls
"Parses"
,executions "Execs", sql_text FROM v$sqlarea ORDER BY 1 DESC)
WHERE ROWNUM <= 10;
# 查看 pga
show parameters area_size
# 查看 pga
SELECT * FROM v$pgastat;
# 查看數(shù)據(jù)庫(kù) cache 或 keep 了哪些 object
COL table_name FORMAT A16
COL index_name FORMAT A16
SELECT table_name AS "Table", NULL, buffer_pool, cache FROM
user_tables
WHERE buffer_pool != 'DEFAULT' OR TRIM(cache)='Y'
UNION
SELECT table_name, index_name, NULL, buffer_pool FROM
user_indexes
WHERE buffer_pool != 'DEFAULT'
ORDER BY 1, 2 NULLS FIRST;
# 取消 cache 或 keep(keep pool)
ALTER TABLE XX NOCACHE;
SELECT 'ALTER INDEX '||index_name||' STORAGE(BUFFER_POOL DEFAULT);'
FROM USER_INDEXES WHERE BUFFER_POOL!='DEFAULT';
檢查undo
show parameter undo_
# 檢查 undo rollback segment 使用情況
select name ,rssize,extents,latch,xacts,writes,gets,waits from v$rollstat a,v$rollname b where
a.usn=b.usn order by waits desc;
select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo
blocks written') a ,(select value trancount from v$sysstat where name='user commits') b;
# 計(jì)算每秒鐘產(chǎn)生的 undoblk 數(shù)量
select sum(undoblks)/sum((end_time-begin_time)*24*60*60) from v$undostat;
#Undospace=UR*UPS*blocksize + overload(10%), 計(jì)算 undo tablespace 大小
show parameter block_size
show parameter undo_retention
#select undo_retention* 每 秒 產(chǎn) 生 undoblk 數(shù) 量 *block_size/1024/1024/1024+
(1+1undo_retention* 每秒產(chǎn)生 undoblk 數(shù)量 *block_size/1024/1024/1024*0.1) from dual;
# 查詢 undo 具體信息
COL undob FORMAT 99990;
COL trans FORMAT 99990;
COL snapshot2old FORMAT 9999999990;
SELECT undoblks "UndoB", txncount "Trans"
,maxquerylen "LongestQuery", maxconcurrency "MaxConcurrency"
,ssolderrcnt "Snapshot2Old", nospaceerrcnt "FreeSpaceWait"
FROM v$undostat;
# 在內(nèi)存中排序比率 ( 最優(yōu)排序 )
SELECT 'Sorts in Memory ' "Ratio"
, ROUND(
(SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'sorts (memory)')
/ (SELECT SUM(value) FROM V$SYSSTAT
WHERE name IN ('sorts (memory)', 'sorts (disk)')) * 100, 5)
||'%' "Percentage"
FROM DUAL;
查看對(duì)象
# 檢查數(shù)據(jù)庫(kù)中無(wú)效對(duì)象
SELECT owner, object_type,count(object_name) FROM dba_objects WHERE status= 'INVALID'group by owner,object_type;
# 檢查是否有禁用約束
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P'
# 檢查是否有禁用 trigger
col owner for a10
col taigger_name for a10
cok table_name for a30
col table_name for a30
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status =
'DISABLED';
# 在某個(gè)用戶下找所有的索引
col column_name for a12
set linesize 180
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
AND user_indexes.table_name='&tb_name'
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
查看當(dāng)前系統(tǒng)狀態(tài)
# 檢查系統(tǒng)中當(dāng)前等待事件
col event for a30
select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where
event not like 'SQL%' and event not like 'rdbms%';
# 查看經(jīng)常被使用而沒(méi)有pin在內(nèi)存中的對(duì)象
# 形成生成pin住共享池中當(dāng)前沒(méi)有被pin住的對(duì)象的sql語(yǔ)句。在執(zhí)行exec sys.DBMS_SHARED_POOL.keep('JXXXT.IN_GZ_LOGS','P');可能會(huì)報(bào)出未定義的錯(cuò)誤，需要在sqlplus下執(zhí)行腳本$ORACLE_HOME/rdbms/admin/dbmspool.sql
select 'exec sys.DBMS_SHARED_POOL.keep('||chr(39)||owner||'.'||NAME||chr(39)||','||chr(39)||'P'||chr(39)||');' as sql_to_run
from V$DB_OBJECT_CACHE where TYPE in ('PACKAGE','FUNCTION','PROCEDURE') and loads > 50 and kept='NO' and executions > 50;
# 查看使用了超過(guò)10MB內(nèi)存 而沒(méi)有pin的對(duì)象
SELECT owner,name,sharable_mem,kept FROM V$DB_OBJECT_CACHE
WHERE sharable_mem > 102400 AND kept = 'NO' ORDER BY sharable_mem DESC;
# 查看大的沒(méi)有被pin住的對(duì)象.
set linesize 150
col sz for a10
col name for a100
col keeped for a6
select to_char(sharable_mem / 1024,'999999') sz_in_K, decode(kept, 'yes','yes ','') keeped,
owner||','||name||lpad(' ',29 - (length(owner) + length(name))) || '(' ||type||')'name,
null extra, 0 iscur from v$db_object_cache v where sharable_mem > 1024*1000;
# 查看大的沒(méi)有被pin住的過(guò)程，包和函數(shù)
col type for a25
col name for a40
col owner for a25
select owner,name,type,round(sum(sharable_mem/1024),1) sharable_mem_K from v$db_object_cache where kept = 'NO'
and (type = 'PACKAGE' or type = 'FUNCTION' or type = 'PROCEDURE')
group by owner,name,type order by 4;
需要被pin入內(nèi)存中的對(duì)象主要有：常用的較大的存儲(chǔ)對(duì)象，如standard、diutil包；編譯的常用的triggers；sequences。
最好在開(kāi)機(jī)時(shí)就將其pin入內(nèi)存中。這樣，既是使用命令alter system flush shared_pool時(shí)，也不會(huì)講這些object flush掉。具體pin對(duì)象到內(nèi)存的方法使用DBMS_SHARED_POOL.keep存儲(chǔ)過(guò)程。可以用unkeep方法解除其pin狀態(tài)。
db_object_cache和碎片化
碎片化造成在共享池中雖然有許多小的碎片可以使用，但沒(méi)有足夠大的連續(xù)空間，這在共享池中是普遍的現(xiàn)象。消除共享池錯(cuò)誤的關(guān)鍵就是即將加載對(duì)象的大小是否可能會(huì)產(chǎn)生問(wèn)題。一旦知道了這個(gè)存在問(wèn)題的PL/SQL，那么就可以在數(shù)據(jù)庫(kù)啟動(dòng)時(shí)(這時(shí)共享池是完全連續(xù)的)就將這個(gè)代碼固定。這將確保在調(diào)用大型包時(shí)，它已經(jīng)在共享池里，而不是在共享池中搜索連續(xù)的碎片(在使用系統(tǒng)時(shí)，這些碎片可能就不復(fù)存在)?？梢圆樵僔$DB_OBJECT_CACHE視圖來(lái)判斷PL/SQL是否很大并且還沒(méi)有被標(biāo)識(shí)為"kept"的標(biāo)記。今后需要加載這些對(duì)象時(shí)，可能會(huì)產(chǎn)生問(wèn)題(因?yàn)樗鼈兊拇笮『托枰加么罅窟B續(xù)的內(nèi)存)。通過(guò)查詢V$DB_OBJECT_CACHE表，可以發(fā)現(xiàn)那些沒(méi)有固定，但由于所需空間太大而很有可能導(dǎo)致潛在問(wèn)題的對(duì)象。
# 查詢一下回滾段的使用情況，其中USED_UREC為undo記錄的使用條目數(shù)，USED_UBLK為undo塊的使用數(shù)目
set linesize 180
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
from v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
# 查看鎖住對(duì)象的會(huì)話信息，操作系統(tǒng)進(jìn)程信息
set linesize 180
select object_name,machine,s.sid,s.serial#,p.spid
from v$locked_object l,dba_objects o ,v$session s,v$process p
where l.object_id=o.object_id and l.session_id=s.sid
and s.paddr=p.addr
# 查看被鎖的表的被鎖時(shí)間
set linesize 180
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;
# 查看被鎖的對(duì)象和引起鎖的sql
select a.sid,a.username,d.object_name, b.sql_text
from v$session a,v$sql b, v$locked_object c,dba_objects d
where a.sql_hashvalue=b.hash_value
and a.sid = c.session_id
and d.object_id = c.object_id;
# 查看鎖定的會(huì)話信息
select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time
# 殺死相關(guān)會(huì)話
alter system kill session 'sid,serial#';
# 如果出現(xiàn)ora-00031錯(cuò)誤，則
alter system kill session 'sid,serial#' immediate；
# 亦可先查詢?cè)摃?huì)話相對(duì)應(yīng)的操作系統(tǒng)進(jìn)程，在操作系統(tǒng)上進(jìn)行kill
感謝$無(wú)為公子的幫助
參考至:http://mlxia.iteye.com/blog/741227
http://blog.csdn.net/soulcq/article/details/5418085
本文原創(chuàng)，轉(zhuǎn)載請(qǐng)注明出處，作者
如有錯(cuò)誤，歡迎指正
郵箱:czmcj@163.com
聯(lián)系客服
