40个DBA日常维护的SQL脚本

yclegend

8065

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'

SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", s.LOGON_TIME

FROM x$kglpn p, v$session s

WHERE p.kglpnuse=s.saddr

AND kglpnhdl='0000000453992440';

结果为 671 0 3 2011-11-1 12:00:00

525 2 0 2011-11-4 12:00:00

10、查询锁住的 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

11、查询当前正在执行的 sql

SELECT

s.sid,s.serial#,s.username,spid,v$sql.sql_id,machine,s.terminal,s.program,sql_te

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

12、查询正在执行的 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

13、查询正在执行的 dbms_job

select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$session

b,v$process where a.sid=b.sid and paddr=addr

14、查询一个会话 session、process 平均消耗多少内存，查看下面 avg_used_M 值

select round(sum(pga_used_mem)/1024/1024,0) total_used_M,

round(sum(pga_used_mem)/count(1)/1024/1024,0) avg_used_M,

round(sum(pga_alloc_mem)/1024/1024,0) total_alloc_M,

round(sum(pga_alloc_mem)/count(1)/1024/1024,0) avg_alloc_M from v$process;

15、TOP 10 执行次数排序

select *

from (select executions,username,PARSING_USER_ID,sql_id,sql_text

from v$sql,dba_users where user_id=PARSING_USER_ID order by executions desc)

where rownum <=5;

16、TOP 10 物理读排序

select *

from (select

DISK_READS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text

from v$sql,dba_users where user_id=PARSING_USER_ID order by DISK_READS desc)

where rownum <=5;

（不要使用 DISK_READS/ EXECUTIONS 来排序，因为任何一条语句不管执行几次都会耗逻辑读和 cpu，可

能不会耗物理读（遇到 LRU 还会耗物理读，LRU 规则是执行最不频繁的且最后一次执行时间距离现在最久远

的就会被交互出 buffer cache），是因为 buffer cache 存放的是数据块，去数据块里找行一定会消耗

cpu 和逻辑读的。Shared pool 执行存放 sql 的解析结果，sql 执行的时候只是去 share pool 中找 hash

value，如果有匹配的就是软解析。所以物理读逻辑读是在 buffer cache 中，软解析硬解析是在 shared

pool）

17、TOP 10 逻辑读排序

select *

from (select

BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text

from v$sql,dba_users where user_id=PARSING_USER_ID order by BUFFER_GETS desc)

where rownum <=5;

of 12

10墨值下载

oracle 特别推荐

相关文档

评论