电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁技術(shù)文章
文章詳情頁

Oracle數(shù)據(jù)庫維護(hù)常用SQL語句集合(3)

瀏覽:123日期:2023-11-25 15:36:13
性能相關(guān)內(nèi)容

1、捕捉運(yùn)行很久的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嚴(yán)重的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、監(jiān)控索引是否使用

alter index &index_name monitoring usage;

alter index &index_name nomonitoring usage;

select * from v$object_usage where index_name = &index_name;

5、求數(shù)據(jù)文件的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、查看還沒提交的事務(wù)

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、查看系統(tǒng)請(qǐng)求情況

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、計(jì)算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、查看內(nèi)存使用情況

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、查看用戶使用內(nèi)存情況

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、查看對(duì)象的緩存情況

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、求歸檔日志的切換頻率(生產(chǎn)系統(tǒng)可能時(shí)間會(huì)很長)

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、求回滾段正在處理的事務(wù)

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、求某個(gè)事務(wù)的重做信息(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%的對(duì)象

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、求等待中的對(duì)象

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、求當(dāng)前事務(wù)的重做尺寸

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去清除臨時(shí)段

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;

查看一下誰在使用那個(gè)可以得回滾段,或者查看一下某個(gè)可以得用戶在使用回滾段,

找出領(lǐng)回滾段不斷增長的事務(wù),再看看如何處理它,是否可以將它c(diǎn)ommit,再不行

就看看能否kill它,等等, 查看當(dāng)前正在使用的回滾段的用戶信息和回滾段信息:

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、查看內(nèi)存中存的使用

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

標(biāo)簽: Oracle 數(shù)據(jù)庫
主站蜘蛛池模板: 箱式破碎机_移动方箱式破碎机/价格/厂家_【华盛铭重工】 | 炭黑吸油计_测试仪,单颗粒子硬度仪_ASTM标准炭黑自销-上海贺纳斯仪器仪表有限公司(HITEC中国办事处) | 安徽免检低氮锅炉_合肥燃油锅炉_安徽蒸汽发生器_合肥燃气锅炉-合肥扬诺锅炉有限公司 | 高精度电阻回路测试仪-回路直流电阻测试仪-武汉特高压电力科技有限公司 | 一体化隔油提升设备-餐饮油水分离器-餐厨垃圾处理设备-隔油池-盐城金球环保产业发展有限公司 | 密封圈_泛塞封_格莱圈-[东莞市国昊密封圈科技有限公司]专注密封圈定制生产厂家 | 深圳公司注册-工商注册代理-注册公司流程和费用_护航财税 | 回转窑-水泥|石灰|冶金-巩义市瑞光金属制品有限责任公司 | 船用锚链|专业锚链生产厂家|安徽亚太锚链制造有限公司 | 注浆压力变送器-高温熔体传感器-矿用压力传感器|ZHYQ朝辉 | 铜镍-康铜-锰铜-电阻合金-NC003 - 杭州兴宇合金有限公司 | 超声波焊接机_超音波熔接机_超声波塑焊机十大品牌_塑料超声波焊接设备厂家 | 河南mpp电力管_mpp电力管生产厂家_mpp电力电缆保护管价格 - 河南晨翀实业 | 商用绞肉机-熟肉切片机-冻肉切丁机-猪肉开条机 - 广州市正盈机械设备有限公司 | 艺术涂料_进口艺术涂料_艺术涂料加盟_艺术涂料十大品牌 -英国蒙太奇艺术涂料 | 瓶盖扭矩测试仪-瓶盖扭力仪-全自动扭矩仪-济南三泉中石单品站 | 无锡装修装潢公司,口碑好的装饰装修公司-无锡索美装饰设计工程有限公司 | 荣事达手推洗地机_洗地机厂家_驾驶式扫地机_工业清洁设备 | 粒米特测控技术(上海)有限公司-测功机_减速机测试台_电机测试台 | 气动隔膜阀_气动隔膜阀厂家_卫生级隔膜阀价格_浙江浙控阀门有限公司 | PVC地板|PVC塑胶地板|PVC地板厂家|地板胶|防静电地板-无锡腾方装饰材料有限公司-咨询热线:4008-798-128 | 膜结构停车棚-自行车棚-膜结构汽车棚加工安装厂家幸福膜结构 | 陶瓷砂磨机,盘式砂磨机,棒销式砂磨机-无锡市少宏粉体科技有限公司 | 今日娱乐圈——影视剧集_八卦娱乐_明星八卦_最新娱乐八卦新闻 | 吨袋包装机|吨包秤|吨包机|集装袋包装机-烟台华恩科技 | 塑料撕碎机_编织袋撕碎机_废纸撕碎机_生活垃圾撕碎机_废铁破碎机_河南鑫世昌机械制造有限公司 | 净水器代理,净水器招商,净水器加盟-FineSky德国法兹全屋净水 | 噪声治理公司-噪音治理专业隔音降噪公司 | 硅胶管挤出机厂家_硅胶挤出机生产线_硅胶条挤出机_臣泽智能装备 贵州科比特-防雷公司厂家提供贵州防雷工程,防雷检测,防雷接地,防雷设备价格,防雷产品报价服务-贵州防雷检测公司 | 菲希尔X射线测厚仪-菲希尔库伦法测厚仪-无锡骏展仪器有限责任公司 | 彼得逊采泥器-定深式采泥器-电动土壤采样器-土壤样品风干机-常州索奥仪器制造有限公司 | 河南不锈钢水箱_地埋水箱_镀锌板水箱_消防水箱厂家-河南联固供水设备有限公司 | 逗网红-抖音网红-快手网红-各大平台网红物品导航 | 安徽净化工程设计_无尘净化车间工程_合肥净化实验室_安徽创世环境科技有限公司 | 筒瓦厂家-仿古瓦-寺庙-古建琉璃瓦-宜兴市古典园林建筑陶瓷厂有限公司 | 机房监控|动环监控|动力环境监控系统方案产品定制厂家 - 迈世OMARA | 杰福伦_磁致伸缩位移传感器_线性位移传感器-意大利GEFRAN杰福伦-河南赉威液压科技有限公司 | TPE塑胶原料-PPA|杜邦pom工程塑料、PPSU|PCTG材料、PC/PBT价格-悦诚塑胶 | 齿轮减速机电机一体机_齿轮减速箱加电机一体化-德国BOSERL蜗轮蜗杆减速机电机生产厂家 | 流量检测仪-气密性检测装置-密封性试验仪-东莞市奥图自动化科技有限公司 | 垃圾压缩设备_垃圾处理设备_智能移动式垃圾压缩设备--山东明莱环保设备有限公司 |