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

From:http://tw.wingwit.com/Article/program/Oracle/201311/17840.html