Oracle DBA 日常维护 SQL 脚本

查询碎片程度高（实际使用率小于30%）的表

可以收缩的表条件为什么block>100，因为一些很小的表，只有几行数据实际大小很小，但是block一次性分配就是5个（11g开始默认一次性分配1M的block大小了，见create table storged的NEXT参数），5个block相对于几行小表数据来说就相差太大了。

算法中/0.9是因为块的pfree一般为10%，所以一个块最多只用了90%，而且一行数据大于8KB时容易产生行链接，把一行分片存储，一样的一个块连90%都用不满 ，AVG_ROW_LEN还是比较准的，比如个人实验情况一表6个字段，一个number，其他5个都是char(100)但是实际数据都是’1111111’7位，AVG_ROW_LEN显示依然为513 。

SELECT TABLE_NAME,(BLOCKS*8192/1024/1024)"理论大小M", 
(NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)"实际大小M", 
round((NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024),3)*100||'%' "实际使用率%"  
FROM USER_TABLES where blocks>100 and (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024)<0.3 
order by (NUM_ROWS*AVG_ROW_LEN/1024/1024/0.9)/(BLOCKS*8192/1024/1024) desc； 

查询索引碎片的比例

索引删除行数除以索引总行数的百分比>30%即认为索引碎片大，也就是需要重建的索引

select name,del_lf_rows,lf_rows, round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)||'%' frag_pct 
from index_stats where round(del_lf_rows/decode(lf_rows,0,1,lf_rows)*100,0)>30;

集群因子clustering_factor高的表

集群因子越接近块数越好，接近行数则说明索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描 ：

方法一

select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor, 
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数" 
from user_tables tab, user_indexes ind where tab.table_name=ind.table_name 
and tab.blocks>100 
and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3; 

方法二

select tab.owner,tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor, 
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数" 
from dba_tables tab, dba_indexes ind where tab.table_name=ind.table_name and tab.owner  
not in ('SYS','SYSTEM','WMSYS','DBSNMP','CTXSYS','XDB','ORDDATA','SYSMAN','CATALOG','APEX_030200','MDSYS','OLAPSYS','EXFSYS') 
and tab.blocks>100 
and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3; 

根据sid查spid或根据spid查sid

select s.sid,s.serial#,p.spid,s.terminal,s.LOGON_TIME,s.status,s.PROGRAM,s.CLIENT_IDENTIFIER,s.machine,s.action,s.MODULE,s.PROCESS "客户端机器进程号",s.osuser from v$session s,v$process p 
where  s.paddr=p.addr and s.sid=XX or p.spid=YY;

根据sid查看具体的sql语句，（不要加条件vsession.status=’ ACTIVE’，比如toad对同一数据库开两个连接会话，都执行了一些语句，其中一个窗口查询select * from vsession时会发现另一个窗口在vsession.status是INACTIVE，并不代表另一个窗口没有执行过sql语句，而当前窗口是active状态，对应的sql_id对应的语句就是select * from vsession而不是之前执行过的sql语句，ACTIVE表示当前正在执行sql。）

一个sid可能执行过很多个sql，所以有时需要的sql通过如下查不到是正常的，比如查询到某死锁源sid，通过如下查询可能只是个select语句，而真正引起死锁的sql却查不到，是因为可能这个sid持续了很长时间，这个sid之前执行的一些sql在v$sql可能已经被清除了。

方法一

select username,sid,SERIAL#,LOGON_TIME,status,PROGRAM,CLIENT_IDENTIFIER,machine,action,PROCESS "客户端机器进程号",osuser,sql_text from v$session a,v$sqltext_with_newlines b 
where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.sid=&sid order by piece; 

方法二

select username,sid,SERIAL#,LOGON_TIME,status,sql_fulltext,PROGRAM,CLIENT_IDENTIFIER,machine,a.action,PROCESS "客户端机器进程号",osuser from v$session a,v$sql b 
where  DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value and a.sid=&sid

如果上面语句执行太慢，则按如下两步

select  sql_hash_value, prev_hash_value, username,sid,SERIAL#,LOGON_TIME,status, PROGRAM,CLIENT_IDENTIFIER,
machine,action,PROCESS "客户端机器进程号",osuser from v$session where  sid=&sid
select  sql_fulltext from v$sql where  hash_value=XX

–XX为上面 sql_hash_value，如果 sql_hash_value为0，则XX为上面 prev_hash_value

根据spid查询具体的sql语句(不要加条件vsession.status=’ ACTIVE’,比如toad对同一数据库开两个连接会话，都执行了一些语句，其中一个窗口查询select * from vsession时会发现另一个窗口在vsession.status是INACTIVE，并不代表另一个窗口没有执行过sql语句，而当前窗口是active状态，对应的sql_id对应的语句就是select * from vsession而不是之前执行过的sql语句，ACTIVE表示当前正在执行sql。)

Select ss.SID,ss.SERIAL#,ss.LOGON_TIME,pr.SPID,sa.SQL_FULLTEXT,ss.machine, ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER,ss.action,ss.PROCESS "客户端机器进程号", ss.STATUS, ss.OSUSER,ss.status,ss.last_call_et,sa.sql_text  
from v$process pr, v$session ss, v$sql sa  
where pr.ADDR = ss.PADDR  
and  DECODE(ss.sql_hash_value, 0, prev_hash_value, sql_hash_value)=sa.hash_value 
and pr.spid=&spid

查看历史session_id的SQL来自哪个IP

查看trace文件名就可以知道spid,trace文件里面有sid和具体sql，如果trace存在incident，那trace就看不到具体sql，但是可以在incident文件中看到具体的sql，如DW_ora_17751.trc中17751就是spid，里面有这样的内容Incident 115 created, dump file: /XX/incident/incdir_115/DW_ora_17751_i115.trc，那么在DW_ora_17751_i115.trc就可以看到具体的sql语句)

DB_ora_29349.trc中出现

*** SESSION ID:(5057.12807) 2016-10-26 14:45:52.726

通过表V$ACTIVE_SESSION_HISTORY来查

select a.sql_id,a.machine,a.* from V$ACTIVE_SESSION_HISTORY a 
where a.session_id=5057 and a.SESSION_SERIAL#=12807 

查询上面的machine的IP

select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from v$session s,v$process p 
where  s.paddr=p.addr and s.machine='localhost' 

通过上面的spid在oracle服务器上执行netstat -anp |grep spid即可

[oracle@dwdb trace]$ netstat -anp |grep 17630 
tcp      210      0 192.168.64.228:11095        192.168.21.16:1521          ESTABLISHED 17630/oracleDB 
tcp        0      0 ::ffff:192.168.64.228:1521  ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB

出现两个，说明来自220，连接了228数据库服务器，但是又通过228服务器的dblink去连接了16服务器

查询死锁堵塞的会话sid

最简单的一个SQL

select * from V$SESSION_BLOCKERS 
select * from dba_waiters

最常用的一个SQL

select sid,status,LOGON_TIME,sql_id,blocking_session "死锁直接源",FINAL_BLOCKING_SESSION "死锁最终源",event,seconds_in_wait "会话锁住时间_S",LAST_CALL_ET "会话STATUS持续时间_S" 
from v$session where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID' 

可以把两者SID放入v$session，发现LOGON_TIME字段FINAL_BLOCKING_SESSION比SID要早

BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID. 
FINAL_BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID. 

如果遇到RAC环境，一定要用gv$来查，并且执行alter system kill session 'sid,serial#'要到RAC对应的实例上去执行

把上面被堵塞会话的sid代入如下语句，可以发现锁住的对象和对象的哪一行(如果sid是堵塞源的会话，则 row_wait_obj#=-1，表示锁持有者，就是死锁源了 )

select s.sid,s.username,d.owner,d.object_name,s.row_wait_obj#,s.row_wait_row#,s.row_wait_file#,s.row_wait_block# 
from v$session s,dba_objects d where s.row_wait_obj#=d.object_id and s.sid  in(XX,XX) 

查询锁住的DDL对象

select d.session_id,s.SERIAL#,d.name 
from dba_ddl_locks d,v$session s where d.owner='MKLMIGEM' and d.SESSION_ID=s.sid

查询超过两个小时的不活动会话

select s.sid,s.serial#,p.spid,s.LOGON_TIME,s.LAST_CALL_ET,s.status,s.PROGRAM,s.CLIENT_IDENTIFIER,s.machine,s.terminal,s.action,s.PROCESS "客户端机器进程号",s.osuser from v$session s,v$process p  
where  s.paddr=p.addr and s.sid in (select sid from v$session where machine<>&DB服务器名称 and status='INACTIVE' and sql_id is null and LAST_CALL_ET>7200)

查询堵塞别的会话超过30分钟且自身是不活动的会话

select username,sid,serial#,status,seconds_in_wait,LAST_CALL_ET from v$session
 where sid in (select FINAL_BLOCKING_SESSION from v$session
  where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID') and status='INACTIVE' and sql_id is null and seconds_in_wait>1800 

查询可能存在连接池空闲初始配置过大的连接（来自同一台机器的同一个程序的状态为INACTIVE的连接非常多）

select count(ss.SID),ss.machine,ss.status,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER  
from v$session ss group by ss.machine,ss.status,ss.TERMINAL,ss.PROGRAM,ss.USERNAME,ss.CLIENT_IDENTIFIER having count(ss.SID)>10 

查询当前正在执行的sql

SELECT s.sid,s.serial#,s.username,spid,v$sql.sql_id,machine,s.terminal,s.program,sql_text  
FROM v$process,v$session s,v$sql   
WHERE addr=paddr and s.sql_id=v$sql.sql_id AND sql_hash_value=hash_value and s.STATUS='ACTIVE' 

查询正在执行的SCHEDULER_JOB

select owner,job_name,sid,b.SERIAL#,b.username,spid from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process 
 where session_id=sid and paddr=addr