监控oracle的运行状况

以下为在优化过程中需要监视的一些SQL和查看一些数据库状态的用例语句.

1:数据库链接的备份脚本.

 select '--'||CHR(10)||'-- Owner is ' || U.NAME||CHR(10)||'-- DB Link is'|| L.NAME||CHR(10)||'--'||CHR(10)||'create '||DECODE(U.NAME,'PUBLIC','public')||' database link '||CHR(10)||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)||'connect to ' || L.USERID || ' identified by'''||L.PASSWORD||''' using ''' || L.host || ''''||chr(10)||';' TEXT from sys.link$ L,sys.user$ U where L.OWNER# = U.USER#;

 

2:监控表空间的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

3.在某个用户下找所有的索引：
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;

4. 监控 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;

5. 监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;

select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;

6: 监控 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');

7. 监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;

8:获得客户端主机信息
SELECT
SYS_CONTEXT('USERENV','TERMINAL') TERMINAL,
SYS_CONTEXT('USERENV','HOST') HOST,
SYS_CONTEXT('USERENV','OS_USER') OS_USER,
SYS_CONTEXT('USERENV','IP_ADDRESS') IP_ADDRESS
FROM DUAL

9:通过这个查询语句来判断, 还需要多久, 该session才能够be killed(断开).
select a.sid sessionId,d.spid os_id,a.username,a.terminal,c.name,b.xidslot,b.xidsqn,b.used_ublk,b.used_urec,b.log_io,b.phy_io,b.cr_get,b.cr_change
from v$session a,v$transaction b,v$rollname c,v$process d
where a.taddr = b.addr
and b.xidusn = c.usn
and a.paddr = d.addr;

10:用v$session_longops跟踪DDL语句;
select * from v$session_longops来查看运行时间很长的DDL和DML语句的会话或一个特定的DDL语句已
经完成了多少以及任何运行时间很长的操作使用了多长的时间。

11、查询有无死锁
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object);

12、查询被死锁语句
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));

13、查询死锁进程
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

14、kill进程
alter system kill session 'sid,serial#';---（其中sid=l.session_id）

15、查找 SCN , Time对应关系：
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'), to_char(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) AS SCN from dual;

16、通过时间flashback query数据：
SELECT * FROM xzsp.t_wf_eng_ins AS OF TIMESTAMP TO_TIMESTAMP('2010-09-30 13:55:55', 'YYYY-MM-DD HH24:MI:SS');

17、通过SCN 闪回数据：
SELECT * FROM xzsp.t_wf_eng_ins AS OF SCN 11321795018821;