分享
 
 
 

Oracle数据库维护常用SQL语句集合(3)

王朝oracle·作者佚名  2008-07-04
窄屏简体版  字體: |||超大  

性能相关内容

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');

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有