Oracle常用監控腳本

1.日常管理
1.1 RAC啟動與關閉
要想啟動或關閉crs服務必須首先切換到root用戶，如下
su - root
啟動crs 服務：
啟動CRS
#$CRS_HOME/crs/bin/crsctl start crs
查看CRS狀態
#$CRS_HOME/crs/bin/crsctl check crs
關閉CRS
#$CRS_HOME/crs/bin/crsctl stop crs
查看CRS內部各資源狀態
#$CRS_HOME/crs/bin/crs_stat –t
啟動資料庫服務
# srvctl start database -d instancename #instancename為資料庫名

1.2檢查alterSID.log
這個日誌文件位於參數BACKGROUND_DUMP_DEST指定的目錄，可能通過以下命令來查看。
SQL> SHOW PARAMETER background_dump_dest
檢查alterSID.log
Oracle 出錯信息可通過$grep ORA- alterSID.log查找
1.3環境確認
資料庫實例是否正常工作
SQL > select status from v$instance;
資料庫監聽器是否正常工作
$ lsnrctl status
是否存在故障表空間
select tablespace_name,status from dba_tablespace;
控制文件、日誌文件是否正常
SELECT * FROM V$CONTROLFILE;
SELECT * FROM V$LOG;
SELECT * FROM V$LOGFILE;
性能監測
每天按業務峰值情況，對資料庫性能數據進行定時採集
每天檢查資料庫的主要性能指標
每天檢查最消耗資源的SQL語句變化情況。
每天檢查是否有足夠的資源
檢查所有表空間的剩餘情況
識別出一些異常的增長
檢查CPU、記憶體、網路等是否異常
1.4文件備份
控制文件備份
Alter system backup controlfile to trace;
初始參數備份
Create pfile=$ORACEL_HOME/dbs/pfile20151212.ora’ from spfile;
其它
tnsnames.ora，listener.ora 使用cp。
2.常用SQL
2.1查看表空間物理文件的名稱及大小
select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) filesize
from dba_data_files
order by tablespace_name;
2.2查詢表空間使用情況
select a.tablespace_name "表空間名稱",
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2) "占用率(%)",
round(a.bytes_alloc / 1024 / 1024, 2) "容量(M)",
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) "空閑(M)",
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) "使用(M)",
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') "採樣時間"
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by 2 desc;
2.3查詢表空間的碎片程度
select tablespace_name, count(tablespace_name)
from dba_free_space
group by tablespace_name
having count(tablespace_name) > 10;

alter tablespace HS_USER_DATA coalesce;
alter table name deallocate unused;
2.4碎片程度
select tablespace_name, count(tablespace_name)
from dba_free_space
group by tablespace_name
having count(tablespace_name) > 10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name, block_id, bytes, blocks, segment_name
from dba_free_space
union all
select tablespace_name, block_id, bytes, blocks, segment_name
from dba_extents;
select * from ts_blocks_v;
select tablespace_name, sum(bytes), max(bytes), count(block_id)
from dba_free_space
group by tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name, COUNT(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYSTEM')
GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM dba_segments
GROUP BY segment_name);

2.5查看回滾段名稱及大小
select segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) InitialExtent,
(next_extent / 1024) NextExtent,
max_extents,
v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name;

2.6查看控制文件
select name from v$controlfile;
2.7查看日誌文件
select member from v$logfile;
2.8查看表空間的使用情況
select sum(bytes) / (1024 * 1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;

SELECT A.TABLESPACE_NAME,
A.BYTES TOTAL,
B.BYTES USED,
C.BYTES FREE,
(B.BYTES * 100) / A.BYTES "% USED",
(C.BYTES * 100) / A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

2.9查看資料庫對象
select owner, object_type, status, count(*) count#
from all_objects
group by owner, object_type, status;
2.10查看資料庫的版本
Select version
FROM Product_component_version
Where SUBSTR(PRODUCT, 1, 6) = 'Oracle';
2.11查看Oracle字元集
select * from sys.props$ where name = 'NLS_CHARACTERSET';
2.12在某個用戶下找所有的索引
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
order by user_indexes.table_type,
user_indexes.table_name,
user_indexes.index_name,
column_position;
2.13表、索引的存儲情況檢查
select segment_name, sum(bytes), count(*) ext_quan
from dba_extents
where tablespace_name = '&tablespace_name'
and segment_type = 'TABLE'
group by tablespace_name, segment_name;
select segment_name, count(*)
from dba_extents
where segment_type = 'INDEX'
and owner = '&owner'
group by segment_name;

2.14查看資料庫的創建日期和歸檔方式
Select Created, Log_Mode, Log_Mode From V$Database;

2.15顯示所有資料庫對象的類別和大小
select type,
count(name) num_instances,
sum(source_size) source_size,
sum(parsed_size) parsed_size,
sum(code_size) code_size,
sum(error_size) error_size,
sum(source_size) + sum(parsed_size) + sum(code_size) +
sum(error_size) size_required
from dba_object_size
group by type
order by 1;

2.16設置RAC為歸檔模式
步驟：
1.以SYSDBA身份登陸2個節點，執行
alter system set cluster_database=false scope =spfile sid=’*’;
設置歸檔路徑
alter system set log_archive_start=true scope=spfile;
2.2個節點
shutdown immediate
3.在一個節點上執行
startup mount
alter database archivelog;
shutdown immediate;
alter database open;
alter system set cluster_database=true scope =spfile sid=’*’;
shutdown immediate
4、分別啟動2個節點，修改完畢

2.17AWR報告
$sqlplus / as sysdba
生成斷點快照
SQL> exec dbms_workload_repository.create_snapshot();
生成報告
SQL> @ /rdbms/admin/awrrpt.sql

3.常用監控SQL
常用性能相關SQL，監控資料庫性能的SQL語句。
3.1監控事務的等待
select event,
sum(decode(wait_Time, 0, 0, 1)) "Prev",
sum(decode(wait_Time, 0, 1, 0)) "Curr",
count(*) "Totol"
from v$session_Wait
group by event
order by 4;
3.2查看一些等待信息：
select sid, event
from v$session_wait
where event not like 'SQL%'
and event not like '%ipc%';

查看是否存在下麵等常見的等待事件：
buffer busy waits，
free buffer waits，
db file sequential read,
db file scattered read,
enqueue,latch free,
log file parallel write,
log file sync
3.3查看等待(wait)情況
SELECT v$waitstat.class,
v$waitstat.count count,
SUM(v$sysstat.value) sum_value
FROM v$waitstat, v$sysstat
WHERE v$sysstat.name IN ('db block gets', 'consistent gets')
group by v$waitstat.class, v$waitstat.count;　　
3.4回滾段查看
select rownum,
sys.dba_rollback_segs.segment_name Name,
v$rollstat.extents Extents,
v$rollstat.rssize Size_in_Bytes,
v$rollstat.xacts XActs,
v$rollstat.gets Gets,
v$rollstat.waits Waits,
v$rollstat.writes Writes,
sys.dba_rollback_segs.status status
from v$rollstat, sys.dba_rollback_segs, v$rollname
where v$rollname.name(+) = sys.dba_rollback_segs.segment_name
and v$rollstat.usn(+) = v$rollname.usn
order by rownum;
3.5回滾段的爭用情況
select name, waits, gets, waits / gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
3.6監控表空間的 I/O 比例
select df.tablespace_name name,
df.file_name "file",
f.phyrds pyr,
f.phyblkrd pbr,
f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
3.7監控文件系統的 I/O 比例
select substr(a.file#, 1, 2) "#",
substr(a.name, 1, 30) "Name",
a.status,
a.bytes,
b.phyrds,
b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
3.8監控 SGA 的命中率
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value + b.value) - c.value) / (a.value + b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 -- physical read total multi block requests
and b.statistic# = 39 -- physical read total bytes
and c.statistic# = 40; -- physical write total IO requests
3.9監控 SGA 中字典緩衝區的命中率
select parameter,
gets,
Getmisses,
getmisses / (gets + getmisses) * 100 "miss ratio",
(1 - (sum(getmisses) / (sum(gets) + sum(getmisses)))) * 100 "Hit ratio"
from v$rowcache
where gets + getmisses <> 0
group by parameter, gets, getmisses;
3.10監控 SGA 中共用緩存區的命中率，應該小於1%
select sum(pins) "Total Pins",
sum(reloads) "Total Reloads",
sum(reloads) / sum(pins) libcache
from v$librarycache;
select sum(pinhits - reloads) / sum(pins) * 100 "hit radio",
sum(reloads) / sum(pins) "reload percent"
from v$librarycache;
3.11臨控 SGA 中重做日誌緩存區的命中率，應該小於1%
SELECT name,
gets,
misses,
immediate_gets,
immediate_misses,
Decode(gets, 0, 0, misses / gets * 100) ratio1,
Decode(immediate_gets + immediate_misses,
0,
0,
immediate_misses / (immediate_gets + immediate_misses) * 100) ratio2
FROM v$latch
WHERE name IN ('redo allocation', 'redo copy');
3.12監控記憶體和硬碟的排序比率，最好使它小於 0.10，增加 sort_area_size
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');
3.13監控當前資料庫誰在運行什麼SQL語句
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address = b.address
order by address, piece;
3.14監控字典緩衝區
SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",
(SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE"
FROM V$LIBRARYCACHE;

SELECT SUM(GETS) "DICTIONARY GETS",
SUM(GETMISSES) "DICTIONARY CACHE GET MISSES",
(SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE"
FROM V$ROWCACHE;
“LIB CACHE“與“ROW CACHE”越接近1.00超好，不要低於0.90。否則需要調大SGA的空間。
3.15查看Lock
select s.osuser,
l.sid,
s.serial#,
s.username,
s.terminal,
decode(l.type,
'TM',
'TM - DML Enqueue',
'TX',
'TX - Trans Enqueue',
'UL',
'UL - User',
l.type || ' - Other Type') LOCKTYPE,
substr(t.name, 1, 10) OBJECT,
u.name owner,
l.id1,
l.id2,
decode(l.lmode,
1,
'No Lock',
2,
'Row Share',
3,
'Row Exclusive',
4,
'Share',
5,
'Shr Row Excl',
6,
'Exclusive',
null) lmode,
decode(l.request,
1,
'No Lock',
2,
'Row Share',
3,
'Row Excl',
4,
'Share',
5,
'Shr Row Excl',
6,
'Exclusive',
null) request
from v$lock l, v$session s, sys.user$ u, sys.obj$ t
where l.sid = s.sid
and s.type != 'BACKGROUND'
and t.obj# = l.id1
and u.user# = t.owner#;
3.16捕捉運行很久的SQL
select username,
sid,
opname,
round(sofar * 100 / totalwork, 0) || '%' as progress,
time_remaining,
sql_text
from v$session_longops, v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value;　
3.17查看數據表的參數信息
SELECT partition_name,
table_name,
high_value,
high_value_length,
tablespace_name,
pct_free,
pct_used,
ini_trans,
max_trans,
initial_extent,
next_extent,
min_extent,
max_extent,
pct_increase,
FREELISTS,
freelist_groups,
LOGGING,
BUFFER_POOL,
num_rows,
blocks,
empty_blocks,
avg_space,
chain_cnt,
avg_row_len,
sample_size,
last_analyzed
FROM sys.dba_tab_partitions
WHERE table_owner = 'HS_HIS' AND table_name = 'HISBANKTRADE'
--WHERE table_name = :tname AND table_owner = :towner
ORDER BY partition_position,partition_name;　　
3.18查看還沒提交的事務
select * from v$locked_object;
select * from v$transaction;
3.19查找object為哪些進程所用
select p.spid,
s.sid,
s.serial# serial_num,
s.username user_name,
a.type object_type,
s.osuser os_user_name,
a.owner,
a.object object_name,
decode(sign(48 - command),
1,
to_char(command),
'Action Code #' || to_char(command)) action,
p.program oracle_process,
s.terminal terminal,
s.program program,
s.status session_status
from v$session s, v$access a, v$process p
where s.paddr = p.addr
and s.type = 'USER'
and a.sid = s.sid
and a.object = 'FUNDREAL'
order by s.username, s.osuser;
3.20查看catched object
SELECT owner,
name,
db_link,
namespace,
type,
sharable_mem,
loads,
executions,
locks,
pins,
kept
FROM v$db_object_cache where owner LIKE 'HS_%';
3.21查看V$SQLAREA
SELECT 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
FROM V$SQLAREA;

3.22有關connection的相關信息
查看有哪些用戶連接
select s.sid,
s.serial# serial_num,
s.osuser os_user_name,
decode(sign(48 - command),
1,
to_char(command),
'Action Code #' || to_char(command)) action,
p.program oracle_process,
status session_status,
s.terminal terminal,
s.program program,
s.username user_name,
s.fixed_table_sequence activity_meter
from v$session s, v$process p
where s.paddr = p.addr
and s.type = 'USER' order by s.username, s.osuser;

2)根據v.sid查看對應連接的資源占用等情況
select n.name, v.value, n.class, n.statistic#
from v$statname n, v$sesstat v
where v.sid = &sid
and v.statistic# = n.statistic#
order by n.class, n.statistic#;

3)根據sid查看對應連接正在運行的sql
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 = &sid);

4.常見問題解決
4.1常用參數設置參考
參數 說明 參考設置
db_writer_processes /配置為CPU內核數的一半 /4
open_cursors /根據AS數量及AS連接數，配置為3000 ~ 5000 /5000
optimizer_mode /RULE /RULE
pga_aggregate_target /同樣，也與根據AS數量及AS連接數相關，一般為1500 ~ 3000。一般1G ~ 3G /3G
processes /進程數，即可以對外提供服務的數據。同樣，也與根據AS數量及AS連接數相關，一般為1500 ~ 3000 /2000
session_cached_cursors /300
sessions /設置processes後自動調整。 /2205
sga_max_size /記憶體的一半，如果存儲使用的是裸設備，可以提高到60% /10G
sga_target /同上；若設置為小於等sga_max_size，可動態修改；否則則需要先設置sga_max_size /10G
shared_pool_size /不超過800M，一般300M到500M就足夠了 /800M
註意：基於Linux平安的RAC不可設sga太大，否則可能會有問題。
SQL> sqlplus / as sysdba
當前參數值查看方法如下(open_cursors)：
SQL> show parameter open_cursors;
參數設置方法如下：
SQL> alter system set open_cursors=4000; --非初始參數
SQL> alter system set db_writer_processes=2 scope=spfile; --初始參數，設置後需要重啟資料庫
註：1、以上參考設置在資料庫主機為4CPU雙核，16G記憶體時的配置
2、標紅的為初始參數，修改後需要重啟資料庫。
4.2殺死僵死連接
有時候由於客戶端的異常退出，會出現一些連接的進程一直在運行，從而不能釋放資源，影響系統的性能。可用以下方法實現把僵死的連接清除。
查詢資料庫死鎖
select distinct *
from (select t2.username, t2.sid, t2.serial#, t2.logon_time
from v$locked_object t1, v$session t2
where t1.session_id = t2.sid)
order by logon_time;
查詢出來的結果就是有死鎖的session了，
alter system kill session 'sid,serial#';
一般情況可以解決資料庫存在的死鎖了，或通過session id 查到對應的操作系統進程，在unix中殺掉操作系統的進程。
SELECT a.username, c.spid AS os_process_id, c.pid AS oracle_process_id
FROM v$session a, v$process c
WHERE c.addr = a.paddr
and a.sid = m
and a.serial# = n;
採用kill （unix/linux）
kill -9 os_process_id
4.3統計信息失效導致執行計划走全表掃瞄
基於Schema的統計信息收集
begin
dbms_stats.gather_schema_stats(ownname => 'TAB_NAME',
estimate_percent => 20,
method_opt => 'for all indexed columns size 10',
cascade => true,
degree => 7);
end;

基於表的統計信息收集
Analyze table hs_secu.entrust compute statistics;