数据库

位置:IT落伍者 >> 数据库 >> 浏览文章

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


发布日期:2021年06月06日
 
Oracle数据库维护常用SQL语句集合(3)

性能相关内容

捕捉运行很久的SQL column username format a

column opname format a

column progress format a

SELECT Username Sid Opname

Round(Sofar * / Totalwork ) || % AS Progress Time_Remaining

Sql_Text

FROM V$session_Longops V$sql

WHERE Time_Remaining <>

AND Sql_Address = Address

AND Sql_Hash_Value = Hash_Value;

求DISK READ较多的SQL SELECT StSql_Text

FROM V$sql s V$sqltext St

WHERE sAddress = StAddress

AND sHash_Value = StHash_Value

AND sDisk_Reads > ;

求DISK SORT严重的SQL SELECT SessUsername SQLSql_Text SortBlocks

FROM V$session Sess V$sqlarea SQL V$sort_Usage Sort

WHERE SessSerial# = SortSession_Num

AND SortSqladdr = SQLAddress

AND SortSqlhash = SQLHash_Value

AND SortBlocks > ;

监控索引是否使用 alter index &index_name monitoring usage;

alter index &index_name nomonitoring usage;

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

求数据文件的I/O分布 SELECT DfNAME Phyrds Phywrts Phyblkrd Phyblkwrt Singleblkrds Readtim

Writetim

FROM V$filestat Fs V$dbfile Df

WHERE FsFile# = DfFile#

ORDER BY DfNAME;

查看还没提交的事务 select * from v$locked_object;

select * from v$transaction;

回滚段查看 SELECT Rownum SysDba_Rollback_SegsSegment_Name NAME

V$rollstatExtents Extents V$rollstatRssize Size_In_Bytes

V$rollstatXacts Xacts V$rollstatGets Gets V$rollstatWaits Waits

V$rollstatWrites Writes SysDba_Rollback_SegsStatus Status

FROM V$rollstat SysDba_Rollback_Segs V$rollname

WHERE V$rollnameNAME(+) = SysDba_Rollback_SegsSegment_Name

AND V$rollstatUsn(+) = V$rollnameUsn

ORDER BY Rownum

查看系统请求情况 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 > ;

计算data buffer 命中率 SELECT aVALUE + bVALUE logical_reads cVALUE phys_reads

Round( * ((aVALUE + bVALUE) cVALUE) / (aVALUE + bVALUE)) BUFFER HIT RATIO

FROM V$sysstat a V$sysstat b V$sysstat c

WHERE aStatistic# =

AND bStatistic# =

AND cStatistic# = ;

SELECT NAME

( (Physical_Reads / (Db_Block_Gets + Consistent_Gets))) * h_Ratio

FROM V$buffer_Pool_Statistics;

查看内存使用情况 SELECT Least(MAX(bVALUE) / ( * ) SUM(aBytes) / ( * )) Shared_Pool_Used

MAX(bVALUE) / ( * ) Shared_Pool_Size

Greatest(MAX(bVALUE) / ( * ) SUM(aBytes) / ( * ))

(SUM(aBytes) / ( * )) Shared_Pool_Avail

((SUM(aBytes) / ( * )) / (MAX(bVALUE) / ( * ))) * Avail_Pool_Pct

FROM V$sgastat a V$parameter b

WHERE (aPool = shared pool AND aNAME NOT IN (free memory))

AND bNAME = shared_pool_size;

查看用户使用内存情况

SELECT Username SUM(Sharable_Mem) SUM(Persistent_Mem) SUM(Runtime_Mem)

FROM Sysv_$sqlarea a Dba_Users b

WHERE aParsing_User_Id = bUser_Id

GROUP BY Username;

查看对象的缓存情况 SELECT Owner Namespace TYPE NAME Sharable_Mem Loads Executions Locks

Pins Kept

FROM V$db_Object_Cache

WHERE TYPE NOT IN

(NOT LOADED NONEXISTENT VIEW TABLE SEQUENCE)

AND Executions >

AND Loads >

AND Kept = NO

ORDER BY Owner Namespace TYPE Executions DESC;

SELECT TYPE COUNT(*)

FROM V$db_Object_Cache

GROUP BY TYPE;

查看库缓存命中率 SELECT Namespace Gets Gethitratio * Gethitratio Pins

Pinhitratio * Pinhitratio Reloads Invalidations

FROM V$librarycache

查看某些用户的hash SELECT aUsername COUNT(bHash_Value) Total_Hash

COUNT(bHash_Value) COUNT(UNIQUE(bHash_Value)) Same_Hash

(COUNT(UNIQUE(bHash_Value)) / COUNT(bHash_Value)) * u_Hash_Ratio

FROM Dba_Users a V$sqlarea b

WHERE aUser_Id = bParsing_User_Id

GROUP BY aUsername;

查看字典命中率 SELECT (SUM(Getmisses) / SUM(Gets)) Ratio

FROM V$rowcache;

查看undo段的使用情况 SELECT dSegment_Name Extents Optsize Shrinks Aveshrink Aveactive

dStatus

FROM V$rollname n V$rollstat s Dba_Rollback_Segs d

WHERE dSegment_Id = nUsn(+)

AND dSegment_Id = sUsn(+);

求归档日志的切换频率(生产系统可能时间会很长) SELECT Start_Recid Start_Time End_Recid End_Time Minutes

FROM (SELECT Test* Rownum AS Rn

FROM (SELECT bRecid Start_Recid

To_Char(bFirst_Time yyyymmdd hh:mi:ss) Start_Time

aRecid End_Recid

To_Char(aFirst_Time yyyymmdd hh:mi:ss) End_Time

Round(((aFirst_Time bFirst_Time) * ) * ) Minutes

FROM V$log_History a V$log_History b

WHERE aRecid = bRecid +

AND bFirst_Time > SYSDATE

ORDER BY aFirst_Time DESC) Test) y

WHERE yRn <

求回滚段正在处理的事务 SELECT aNAME bXacts cSid cSerial# dSql_Text

FROM V$rollname a V$rollstat b V$session c V$sqltext d V$transaction e

WHERE aUsn = bUsn

AND bUsn = eXidusn

AND cTaddr = eAddr

AND cSql_Address = dAddress

AND cSql_Hash_Value = dHash_Value

ORDER BY aNAME cSid dPiece;

求某个事务的重做信息(bytes) SELECT sNAME mVALUE

FROM V$mystat m V$statname s

WHERE mStatistic# = sStatistic#

AND sNAME LIKE %redo size%;

求cache中缓存超过其%的对象 SELECT oOwner oObject_Type oObject_Name COUNT(bObjd)

FROM V$bh b Dba_Objects o

WHERE bObjd = oObject_Id

GROUP BY oOwner oObject_Type oObject_Name

HAVING COUNT(bObjd) > (SELECT To_Number(VALUE) *

FROM V$parameter

WHERE NAME = db_block_buffers);

求buffer cache中的块信息

SELECT oObject_Type Substr(oObject_Name ) Objname bObjd bStatus

COUNT(bObjd)

FROM V$bh b Dba_Objects o

WHERE bObjd = oData_Object_Id

AND oOwner = &owner

GROUP BY oObject_Type oObject_Name bObjd bStatus;

求日志文件的空间使用 SELECT LeLeseq Current_Log_Sequence#

* CpCpodr_Bno / LeLesiz Percentage_Full

FROM X$kcccp Cp X$kccle Le

WHERE LeLeseq = CpCpodr_Seq;

求等待中的对象 SELECT /*+rule */

sSid sUsername wEvent oOwner oSegment_Name oSegment_Type

oPartition_Name wSeconds_In_Wait Seconds wState

FROM V$session_Wait w V$session s Dba_Extents o

WHERE wEvent IN (SELECT NAME

FROM V$event_Name

WHERE Parameter = file#

AND Parameter = block#

AND NAME NOT LIKE control%)

AND oOwner <> sys

AND wSid = sSid

AND wP = oFile_Id

AND wP >= oBlock_Id

AND wP < oBlock_Id + oBlocks

求当前事务的重做尺寸 SELECT V$statnameNAMEVALUE

FROM V$mystat V$statname

WHERE V$mystatStatistic# = V$statnameStatistic#

AND V$statnameNAME = redo size;

唤醒smon去清除临时段 column pid new_value Smon

set termout off

SELECT pPid

FROM Sysv_$bgprocess b Sysv_$process p

WHERE bNAME = SMON

AND pAddr = bPaddr;

SET Termout ON Oradebug Wakeup &Smon Undefine Smon

求回退率 SELECT bVALUE / (aVALUE + bVALUE) aVALUE bVALUE

FROM V$sysstat a V$sysstat b

WHERE aStatistic# =

AND bStatistic# = ;

求free memory SELECT *

FROM V$sgastat

WHERE NAME = free memory;

SELECT aNAME SUM(bVALUE)

FROM V$statname a V$sesstat b

WHERE aStatistic# = bStatistic#

GROUP BY aNAME;

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

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

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

SELECT rNAME ROLLBACK SEGMENT NAME lSid ORACLE PID

pSpid SYSTEM PID sUsername ORACLE USERNAME

FROM V$lock l V$process p V$rollname r V$session s

WHERE lSid = pPid(+)

AND sSid = lSid

AND Trunc(lId(+) / ) = rUsn

AND lTYPE(+) = TX

AND lLmode(+) =

ORDER BY rNAME;

查看用户的回滚段的信息 SELECT sUsername RnNAME

FROM V$session s V$transaction t V$rollstat r V$rollname Rn

WHERE sSaddr = tSes_Addr

AND tXidusn = rUsn

AND rUsn = RnUsn

查看内存中存的使用

SELECT Decode(Greatest(CLASS )

Decode(CLASS Data Sort Header To_Char(CLASS)) Rollback) Class

SUM(Decode(Bitand(Flag ) )) Not Dirty

SUM(Decode(Bitand(Flag ) )) Dirty

SUM(Dirty_Queue) On Dirty COUNT(*) Total

FROM X$bh

GROUP BY Decode(Greatest(CLASS )

Decode(CLASS Data Sort Header To_Char(CLASS)) Rollback);

上一篇:Oracleto

下一篇:Oracle JOB 用法小结