40个DBA日常维护的SQL脚本

yclegend

7961

12页

216次

2020-08-13

10墨值下载

--ORACLE

1、查询碎片程度高的表

条件为什么 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

2、查询索引碎片的比例

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;

3、集群因子 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

4、根据 sid 查 spid 或根据 spid 查 sid

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.sid=XX or p.spid=YY

5、根据 sid 查看具体的 sql 语句

select username,sql_text,machine,osuser from v$session a,v$sqltext_with_newlines

where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value

and a.sid=&sid order by piece;

6、根据 spid 查询具体的 sql 语句

select ss.SID, pr.SPID, ss.action, sa.SQL_FULLTEXT, ss.TERMINAL, ss.PROGRAM,

ss.SERIAL#, ss.USERNAME, ss.STATUS, ss.OSUSER, ss.last_call_et

from v$process pr, v$session ss, v$sqlarea sa

where ss.status='ACTIVE' and ss.username is not null and pr.ADDR = ss.PADDR

and ss.SQL_ADDRESS = sa.ADDRESS and ss.SQL_HASH_VALUE = sa.HASH_VALUE

and pr.spid = XX

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

(当然这是个误解，都是历史的了，怎么可能还查到 spid，其实查看 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 服务器

8、查询 DML 死锁会话 sid，及引起死锁的堵塞者会话 blocking_session

select sid, blocking_session,

LOGON_TIME,sql_id,status,event,seconds_in_wait,state, BLOCKING_SESSION_STATUS

from v$session where event like 'enq%' and state='WAITING' and

BLOCKING_SESSION_STATUS='VALID'

BLOCKING_SESSION:Session identifier of the blocking session. This column is

valid only if BLOCKING_SESSION_STATUS has the value VALID.

可以在 v$session.LOGON_TIME 上看到引起死锁的堵塞者会话比等待者要早

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

RAC 对应的实例上去执行

或如下也可以

select

(select username from v$session where sid=a.sid) blocker,

a.sid,

a.id1,

a.id2,

' is blocking ' "IS BLOCKING",

(select username from v$session where sid=b.sid) blockee,

b.sid

from v$lock a, v$lock b

where a.block = 1

and b.request > 0

and a.id1 = b.id1

and a.id2 = b.id2;

9、查询 DDL 锁的 sql

SELECT sid, event, p1raw, seconds_in_wait, wait_time

FROM sys.v_$session_wait

WHERE event like 'library cache %'

p1raw 结果为'0000000453992440'

of 12

10墨值下载

oracle 特别推荐

关注

评论