DBA常用脚本

DBA常用脚本

===========================================================

1.查询有enqueue等待的事件

SELECT b.SID, b.serial#, ername, machine, event, wait_time,

CHR (BITAND (p1, -16777216) / 16777215)

|| CHR (BITAND (p1, 16711680) / 65535) "Enqueue Type"

FROM v$session_wait a, v$session b

WHERE a.event NOT LIKE 'SQL*N%'

AND a.event NOT LIKE 'rdbms%'

AND a.SID = b.SID

AND b.SID > 8

AND a.event = 'enqueue'

ORDER BY username;

2

如何确定哪个表空间读写频繁?

select name,phyrds,phywrts,readtim,writetim

from v$filestat a,v$dbfile b

where a.file# = b.file#

order by readtim desc

3

Library Cache Pin/Lock Pile Up

SELECT s.sid, kglpnmod “Mode”, kglpnreq “Req”, SPID “OS Process”

FROM v$session_wait w, x$kglpn p, v$session s ,v$process o

WHERE p.kglpnuse=s.saddr

AND kglpnhdl=w.p1raw

and w.event like …%library cache pin%‟

and s.paddr=o.addr

4全表扫描的表

SQL>col name for a30

SQL>select name,value from v$sysstat

2 where name in (‟table scans(short tables)‟,'table scans(long tables)‟);

5

查询SQL语句执行时,硬语法分析的次数

select name,value

from v$sysstat

where name like …parse count%‟;

6

该项显示buffer cache大小是否合适。

公式：1-((physical reads-physical reads direct-physical reads direct (lob)) / session logical reads) 执行：

select 1-((a.value-b.value-c.value)/d.value)

from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat d

where =‟physical reads‟ and

=‟physical reads direct‟ and

=‟physical reads direct (lob)‟ and

=‟session logical reads‟;

7

该项显示buffer命中率。

公式：1-(physical reads/ (db block gets+consistent gets))

执行：

select 1 - (sum(decode(name, …physical reads‟, value, 0)) /

(sum(decode(name, …db block gets‟, value, 0)) +

sum(decode(name, …consistent gets‟, value, 0))))

“Buffer Hit Ratio”

from v$sysstat;

8

Soft parse ratio：这项将显示系统是否有太多硬解析。该值将会与原始统计数据对比以确保精确。例如，软解析率仅为0.2则表示硬解析率太高。不过，如果总解析量(parse count total)偏低，这项值可以被忽略。公式：1 - ( parse count (hard) / parse count (total) )

执行：

select 1-(a.value/b.value)

from v$sysstat a,v$sysstat b

Where =‟parse count (hard)‟ and =‟parse count (total)‟;

9

In-memory sort ratio：该项显示内存中完成的排序所占比例。最理想状态下，在OLTP系统中，大部分排序不仅小并且能够完全在内存里完成排序。

公式：sorts (memory) / ( sorts (memory) + sorts (disk) )

执行：

select a.value/(b.value+c.value)

from v$sysstat a,v$sysstat b,v$sysstat c

where =‟sorts (memory)‟ and

=‟sorts (memory)‟ and

=‟sorts (disk)‟;

10

Parse to execute ratio：在生产环境，最理想状态是一条sql语句一次解析多数运行。

公式：1 - (parse count/execute count)

执行：

select 1-(a.value/b.value)

from v$sysstat a,v$sysstat b

where =‟parse count (total)‟ and =‟execute count‟;

11

Parse CPU to total CPU ratio：该项显示总的CPU花费在执行及解析上的比率。如果这项比率较低，说明系统执行了太多的解析。

公式：1 - (parse time cpu / CPU used by this session)

执行：

select 1-(a.value/b.value)

from v$sysstat a,v$sysstat b