性能相关内容
捕捉运行很久的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);