Oracle数据库维护常用SQL语句集合--性能相关-CSDN博客

本文链接：https://blog.csdn.net/leishifei/article/details/6403580

Oracle数据库维护常用SQL语句集合--性能相关

性能相关

1、捕捉运行很久的SQL

column username format a12

column opname format a16

column progress format a8

SELECT username,

sid,

opname,

Round(sofar * 100 / totalwork, 0)

|| '%' AS progress,

time_remaining,

sql_text

FROM v$session_longops,

v$sql

WHERE time_remaining <> 0

AND sql_address = address

AND sql_hash_value = hash_value;

2、求DISK READ较多的SQL

SELECT st.sql_text

FROM v$sql s,

v$sqltext st

WHERE s.address = st.address

AND s.hash_value = st.hash_value

AND s.disk_reads > 300;

3、求DISK SORT严重的SQL

SELECT sess.username,

SQL.sql_text,

sort1.blocks

FROM v$session sess,

v$sqlarea SQL,

v$sort_usage sort1

WHERE sess.serial# = sort1.session_num

AND sort1.sqladdr = SQL.address

AND sort1.sqlhash = SQL.hash_value

AND sort1.blocks > 200;

4、监控索引是否使用

ALTER INDEX &index_name monitoring usage;

ALTER INDEX &index_name nomonitoring usage;

SELECT *

FROM v$object_usage

WHERE index_name = &index_name;

5、求数据文件的I/O分布

SELECT df.NAME,

phyrds,

phywrts,

phyblkrd,

phyblkwrt,

singleblkrds,

readtim,

writetim

FROM v$filestat fs,

v$dbfile df

WHERE fs.file# = df.file#

ORDER BY df.NAME;

6、查看还没提交的事务

SELECT *

FROM v$locked_object;

SELECT *

FROM v$transaction;

7、回滚段查看

SELECT ROWNUM,

sys.dba_rollback_segs.segment_name NAME,

v$rollstat.extents extents,

v$rollstat.rssize size_in_bytes,

v$rollstat.xacts xacts,

v$rollstat.gets gets,

v$rollstat.waits waits,

v$rollstat.writes writes,

sys.dba_rollback_segs.status status

FROM v$rollstat,

sys.dba_rollback_segs,

v$rollname

WHERE v$rollname.NAME(+) = sys.dba_rollback_segs.segment_name

AND v$rollstat.usn(+) = v$rollname.usn

ORDER BY ROWNUM

8、查看系统请求情况

SELECT Decode(NAME, 'summed dirty write queue length', VALUE) /

Decode(NAME, 'write requests', VALUE) "Write Request Length"

FROM v$sysstat

WHERE NAME IN ( 'summed dirty queue length', 'write requests' )

AND VALUE > 0;

9、计算data buffer 命中率

SELECT a.VALUE + b.VALUE "logical_reads",

c.VALUE "phys_reads",

Round(100 * ( ( a.VALUE + b.VALUE ) - c.VALUE ) / ( a.VALUE + b.VALUE ))

"BUFFER HIT RATIO"

FROM v$sysstat a,

v$sysstat b,

v$sysstat c

WHERE a.statistic# = 40

AND b.statistic# = 41

AND c.statistic# = 42;

SELECT NAME,

( 1 - ( physical_reads / ( db_block_gets + consistent_gets ) ) ) * 100

h_ratio

FROM v$buffer_pool_statistics;

10、查看内存使用情况

SELECT Least(MAX(b.VALUE) / ( 1024 * 1024 ), SUM(a.bytes) / ( 1024 * 1024 ))

shared_pool_used,

MAX(b.VALUE) / ( 1024 * 1024 ) shared_pool_size,

Greatest(MAX(b.VALUE) / ( 1024 * 1024 ), SUM(a.bytes) / ( 1024 * 1024 ))

- ( SUM

(a.bytes) / ( 1024 * 1024 ) ) shared_pool_avail,

( ( SUM(a.bytes) / ( 1024 * 1024 ) ) / ( MAX(b.VALUE) / ( 1024 * 1024 ) )

) *

100 avail_pool_pct

FROM v$sgastat a,

v$parameter b

WHERE ( a.pool = 'shared pool'

AND a.NAME NOT IN ( 'free memory' ) )

AND b.NAME = 'shared_pool_size';

11、查看用户使用内存情况

SELECT username,

SUM(sharable_mem),

SUM(persistent_mem),

SUM(runtime_mem)

FROM sys.v_$sqlarea a,

dba_users b

WHERE a.parsing_user_id = b.user_id

GROUP BY username;

12、查看对象的缓存情况

SELECT owner,

namespace,

TYPE,

NAME,

sharable_mem,

loads,

executions,

locks,

pins,

kept

FROM v$db_object_cache

WHERE TYPE NOT IN ( 'NOT LOADED', 'NON-EXISTENT', 'VIEW', 'TABLE', 'SEQUENCE' )

AND executions > 0

AND loads > 1

AND kept = 'NO'

ORDER BY owner,

namespace,

TYPE,

executions DESC;

SELECT TYPE,

COUNT(*)

FROM v$db_object_cache

GROUP BY TYPE;

13、查看库缓存命中率

SELECT namespace,

gets,

gethitratio * 100 gethitratio,

pins,

pinhitratio * 100 pinhitratio,

reloads,

invalidations

FROM v$librarycache

14、查看某些用户的hash

SELECT a.username,

COUNT(b.hash_value) total_hash,

COUNT(b.hash_value) - COUNT(Unique(b.hash_value)) same_hash,

( COUNT(Unique(b.hash_value)) / COUNT(b.hash_value) ) * 100 u_hash_ratio

FROM dba_users a,

v$sqlarea b

WHERE a.user_id = b.parsing_user_id

GROUP BY a.username;

15、查看字典命中率

SELECT ( SUM(getmisses) / SUM(gets) ) ratio

FROM v$rowcache;

16、查看undo段的使用情况

SELECT d.segment_name,

extents,

optsize,

shrinks,

aveshrink,

aveactive,

d.status

FROM v$rollname n,

v$rollstat s,

dba_rollback_segs d

WHERE d.segment_id = n.usn(+)

AND d.segment_id = s.usn(+);

17、求归档日志的切换频率(生产系统可能时间会很长)

SELECT start_recid,

start_time,

end_recid,

end_time,

minutes

FROM (SELECT test.*,

ROWNUM AS rn

FROM (SELECT b.recid

start_recid,

To_char(b.first_time, 'yyyy-mm-dd hh24:mi:ss')

start_time,

a.recid

end_recid,

To_char(a.first_time, 'yyyy-mm-dd hh24:mi:ss')

end_time

Round(( ( a.first_time - b.first_time ) * 24 )

* 60, 2) minutes

FROM v$log_history a,

v$log_history b

WHERE a.recid = b.recid + 1

AND b.first_time > SYSDATE - 1

ORDER BY a.first_time DESC) test) y

WHERE y.rn < 30

18、求回滚段正在处理的事务

SELECT a.NAME,

b.xacts,

c.sid,

c.serial#,

d.sql_text

FROM v$rollname a,

v$rollstat b,

v$session c,

v$sqltext d,

v$transaction e

WHERE a.usn = b.usn

AND b.usn = e.xidusn

AND c.taddr = e.addr

AND c.sql_address = d.address

AND c.sql_hash_value = d.hash_value

ORDER BY a.NAME,

c.sid,

d.piece;

19、求某个事务的重做信息(bytes)

SELECT s.NAME,

m.VALUE

FROM v$mystat m,

v$statname s

WHERE m.statistic# = s.statistic#

AND s.NAME LIKE '%redo size%';

20、求cache中缓存超过其5%的对象

SELECT o.owner,

o.object_type,

o.object_name,

COUNT(b.objd)

FROM v$bh b,

dba_objects o

WHERE b.objd = o.object_id

GROUP BY o.owner,

o.object_type,

o.object_name

HAVING COUNT(b.objd) > (SELECT To_number(VALUE) * 0.05

FROM v$parameter

WHERE NAME = 'db_block_buffers');

21、求buffer cache中的块信息

SELECT o.object_type,

Substr(o.object_name, 1, 10) objname,

b.objd,

b.status,

COUNT(b.objd)

FROM v$bh b,

dba_objects o

WHERE b.objd = o.data_object_id

AND o.owner = '&owner'

GROUP BY o.object_type,

o.object_name,

b.objd,

b.status;

22、求日志文件的空间使用

SELECT le.leseq current_log_sequence#,

100 * cp.cpodr_bno / le.lesiz percentage_full

FROM x$kcccp cp,

x$kccle le

WHERE le.leseq = cp.cpodr_seq;

23、求等待中的对象

SELECT /*+rule */ s.sid,

s.username,

w.event,

o.owner,

o.segment_name,

o.segment_type,

o.partition_name,

w.seconds_in_wait seconds,

w.state

FROM v$session_wait w,

v$session s,

dba_extents o

WHERE w.event IN (SELECT NAME

FROM v$event_name

WHERE parameter1 = 'file#'

AND parameter2 = 'block#'

AND NAME NOT LIKE 'control%')

AND o.owner <> 'sys'

AND w.sid = s.sid

AND w.p1 = o.file_id

AND w.p2 >= o.block_id

AND w.p2 < o.block_id + o.blocks

24、求当前事务的重做尺寸

SELECT v$statname.NAME,

VALUE

FROM v$mystat,

v$statname

WHERE v$mystat.statistic# = v$statname.statistic#

AND v$statname.NAME = 'redo size';

25、唤醒smon去清除临时段

column pid new_value smon

set termout off

SELECT p.pid

FROM sys.v_$bgprocess b,

sys.v_$process p

WHERE b.NAME = 'SMON'

AND p.addr = b.paddr;

SET termout ON oradebug wakeup &smon undefine smon

26、求回退率

SELECT b.VALUE / ( a.VALUE + b.VALUE ),

a.VALUE,

b.VALUE

FROM v$sysstat a,

v$sysstat b

WHERE a.statistic# = 4

AND b.statistic# = 5;

27、求free memory

SELECT *

FROM v$sgastat

WHERE NAME = 'free memory';

SELECT a.NAME,

SUM(b.VALUE)

FROM v$statname a,

v$sesstat b

WHERE a.statistic# = b.statistic#

GROUP BY a.NAME;

查看一下谁在使用那个可以得回滚段，或者查看一下某个可以得用户在使用回滚段，

找出领回滚段不断增长的事务，再看看如何处理它，是否可以将它commit，再不行

就看看能否kill它，等等, 查看当前正在使用的回滚段的用户信息和回滚段信息:

set linesize 121

SELECT r.NAME "ROLLBACK SEGMENT NAME ",

l.sid "ORACLE PID",

p.spid "SYSTEM PID ",

s.username "ORACLE USERNAME"

FROM v$lock l,

v$process p,

v$rollname r,

v$session s

WHERE l.sid = p.pid(+)

AND s.sid = l.sid

AND Trunc(l.id1(+) / 65536) = r.usn

AND l.TYPE(+) = 'TX'

AND l.lmode(+) = 6

ORDER BY r.NAME;

28、查看用户的回滚段的信息

SELECT s.username,

rn.NAME

FROM v$session s,

v$transaction t,

v$rollstat r,

v$rollname rn

WHERE s.saddr = t.ses_addr

AND t.xidusn = r.usn

AND r.usn = rn.usn

29、查看内存中存的使用

SELECT Decode(Greatest(class, 10), 10, Decode(class, 1, 'Data',

2, 'Sort',

4, 'Header',

To_char(class)),

'Rollback') "Class",

SUM(Decode(Bitand(flag, 1), 1, 0,

1)) "Not Dirty",

SUM(Decode(Bitand(flag, 1), 1, 1,

0)) "Dirty",

SUM(dirty_queue) "On Dirty",

COUNT(*) "Total"

FROM x$bh

GROUP BY Decode(Greatest(class, 10), 10, Decode(class, 1, 'Data',

2, 'Sort',

4, 'Header',

To_char(class)),

'Rollback');