數(shù)據(jù)庫(kù)各個(gè)表空間增長(zhǎng)情況的檢查： SQL >SELECT A.TABLESPACE_NAME,( 1 -(A.TOTAL)/B.TOTAL)* 100 USED_PERCENT FROM (SELECT TABLESPACE_NAME,SUM (BYTES) TOTAL FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME,SUM (BYTES) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME; SQL >SELECT UPPER(F.TABLESPACE_NAME) " 表空間名 ", D.TOT_GROOTTE_MB " 表空間大小 (M)", ?????? D.TOT_GROOTTE_MB - F.TOTAL_BYTES " 已使用空間 (M)" , TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100 , 2 ), '990.99' ) " 使用比 ",F.TOTAL_BYTES" 空閑空間 (M) ",? ?????? F.MAX_BYTES " 最大塊 (M) " FROM (SELECT TABLESPACE_NAME,? ROUND(SUM (BYTES) / ( 1024 * 1024 ), 2 ) TOTAL_BYTES, ROUND(MAX (BYTES) / ( 1024 * 1024 ), 2 ) MAX_BYTES? FROM SYS .DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,? ?????? (SELECT DD.TABLESPACE_NAME,ROUND(SUM (DD.BYTES) / ( 1024 * 1024 ), 2 ) TOT_GROOTTE_MB FROM SYS .DBA_DATA_FILES DD? GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME? ORDER BY 4 DESC ; 查看各個(gè)表空間占用磁盤情況 : SQL >COL TABLESPACE_NAME FORMAT A20;? SQL >SELECT B.FILE_ID FILE_ID,? B.TABLESPACE_NAME TABLESPACE_NAME,? B.BYTES BYTES,? (B.BYTES-SUM (NVL(A.BYTES, 0 ))) USED,? SUM (NVL(A.BYTES, 0 )) FREE, SUM (NVL(A.BYTES, 0 ))/(B.BYTES)* 100 PERCENT? ?? ? FROM DBA_FREE_SPACE A,DBA_DATA_FILES B? ???? WHERE A.FILE_ID=B.FILE_ID? ???? GROUP BY B.TABLESPACE_NAME,B.FILE_ID,B.BYTES? ???? ORDER BY B.FILE_ID; 數(shù)據(jù)庫(kù)對(duì)象下一擴(kuò)展與表空間的 free 擴(kuò)展值的檢查： SQL >SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME FROM ALL_TABLES A,(SELECT TABLESPACE_NAME, MAX (BYTES) AS BIG_CHUNK FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK UNION SELECT A.INDEX_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME FROM ALL_INDEXES A,(SELECT TABLESPACE_NAME, MAX (BYTES) AS BIG_CHUNK FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK; Disk Read 最高的 SQL 語(yǔ)句的獲?。? SQL >SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM <= 5 ; 查找前十條性能差的 sql SELECT * FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS, SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC )? WHERE ROWNUM < 10 ; 等待時(shí)間最多的 5 個(gè)系統(tǒng)等待事件的獲?。? SQL >SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC ) WHERE ROWNUM <= 5 ; 查看當(dāng)前等待事件的會(huì)話 : COL USERNAME FORMAT A10 SET LINE 120 COL EVENT FORMAT A30 SELECT SE.SID,S.USERNAME,SE.EVENT,SE.TOTAL_WAITS,SE.TIME_WAITED,SE.AVERAGE_WAIT FROM V$SESSION S,V$SESSION_EVENT SE WHERE S.USERNAME IS NOT NULL AND SE.SID=S.SID AND S.STATUS= 'ACTIVE' AND SE.EVENT NOT LIKE '%SQL*NET%' ; SELECT SID, EVENT, P1, P2, P3, WAIT_TIME, SECONDS_IN_WAIT, STATE FROM V$SESSION_WAIT WHERE EVENT NOT LIKE '%MESSAGE%' AND EVENT NOT LIKE 'SQL*NET%' AND EVENT NOT LIKE '%TIMER%' AND EVENT != 'WAKEUP TIME MANAGER' ; 找到與所連接的會(huì)話有關(guān)的當(dāng)前等待事件：
SELECT SW.SID,S.USERNAME,SW.EVENT,SW.WAIT_TIME,SW.STATE,SW.SECONDS_IN_WAIT SEC_IN_WAIT FROM V$SESSION S,V$SESSION_WAIT SW WHERE S.USERNAME IS NOT NULL AND SW.SID=S.SID AND SW.EVENT NOT LIKE '%SQL*NET%' ORDER BY SW.WAIT_TIME DESC ; Oracle 所有回滾段狀態(tài)的檢查：
SQL>SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,DBA_ROLLBACK_SEGS.STATUS FROM DBA_ROLLBACK_SEGS,V$DATAFILE WHERE FILE_ID=FILE #; Oracle 回滾段擴(kuò)展信息的檢查 : COL NAME FORMAT A10 SET LINESIZE 140 ??????? SELECT SUBSTR(NAME , 1 , 40 ) NAME ,EXTENTS ,RSSIZE,OPTSIZE,AVEACTIVE,EXTENDS,WRAPS,SHRINKS,HWMSIZE FROM V$ROLLNAME RN,V$ROLLSTAT RS WHERE (RN.USN=RS.USN); EXTENTS : 回滾段中的盤區(qū)數(shù)量。 Rssize: 以字節(jié)為單位的回滾段的尺寸。 optsize ：為 optimal 參數(shù)設(shè)定的值。 Aveactive: 從回滾段中刪除盤區(qū)時(shí)釋放的以字節(jié)為單位的平均空間的大小。 Extends: 系統(tǒng)為回滾段增加的盤區(qū)的次數(shù)。 Shrinks: 系統(tǒng)從回滾段中清除盤區(qū)（即回滾段收縮）的次數(shù)?；貪L段每次清除盤區(qū)時(shí)，系統(tǒng)可能會(huì)從這個(gè)回滾段中消除一個(gè)或多個(gè)盤區(qū)。 Hwmsize: 回滾段尺寸的上限，即回滾段曾經(jīng)達(dá)到的最大尺寸。 ( 如果回滾段平均尺寸接近 OPTIMAL 的值，那么說(shuō)明 OPTIMAL 的值設(shè)置正確，如果回滾段動(dòng)態(tài)增長(zhǎng)次數(shù)或收縮次數(shù)很高，那么需要提高 OPTIMAL 的值 )
查看回滾段的使用情況，哪個(gè)用戶正在使用回滾段的資源 : SELECT S.USERNAME, U.NAME FROM V$TRANSACTION T,V$ROLLSTAT R, V$ROLLNAME U,V$SESSION S WHERE S.TADDR=T.ADDR AND? T.XIDUSN=R.USN AND R.USN=U.USN ORDER BY S.USERNAME; 如何查看一下某個(gè) shared_server 正在忙什么 : SELECT A.USERNAME,A.MACHINE,A.PROGRAM,A.SID, A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C WHERE B.SPID= 13161 AND B.ADDR=A.PADDR AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE; 數(shù)據(jù)庫(kù)共享池性能檢查 : SELECT NAMESPACE,GETS,GETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONS FROM V$LIBRARYCACHE WHERE NAMESPACE IN ( 'SQLAREA' , 'TABLE/PROCEDURE' , 'BODY' , 'TRIGGER' ); 檢查數(shù)據(jù)重載比率 : SELECT SUM (RELOADS)/SUM (PINS)* 100 "RELOAD RATIO" FROM? V$LIBRARYCACHE; 檢查數(shù)據(jù)字典的命中率 : SELECT 1 -SUM (GETMISSES)/SUM (GETS) "DATA DICTIONARY HIT RATIO" FROM V$ROWCACHE; ( 對(duì)于 library cache, gethitratio 和 pinhitratio 應(yīng)該大于 90%, 對(duì)于數(shù)據(jù)重載比率 ,reload ratio 應(yīng)該小于 1%, 對(duì)于數(shù)據(jù)字典的命中率 ,data dictionary hit ratio 應(yīng)該大于 85%)
檢查共享內(nèi)存的剩余情況 : SELECT REQUEST_MISSES, REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED; ( 對(duì)于共享內(nèi)存的剩余情況 , request_misses 和 request_failures 應(yīng)該接近 0)
數(shù)據(jù)高速緩沖區(qū)性能檢查 : SELECT 1 -P.VALUE /(B.VALUE +C.VALUE ) "DB BUFFER CACHE HIT RATIO" FROM V$SYSSTAT P,V$SYSSTAT B,V$SYSSTAT C WHERE P.NAME = 'PHYSICAL READS' AND B.NAME = 'DB BLOCK GETS' AND C.NAME = 'CONSISTENT GETS' ; 檢查 buffer pool HIT_RATIO 執(zhí)行 SELECT NAME , (PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)) "MISS_HIT_RATIO" FROM V$BUFFER_POOL_STATISTICS WHERE (DB_BLOCK_GETS+ CONSISTENT_GETS)> 0 ; ( 正常時(shí) db buffer cache hit ratio 應(yīng)該大于 90%, 正常時(shí) buffer pool MISS_HIT_RATIO 應(yīng)該小于 10%)
數(shù)據(jù)庫(kù)回滾段性能檢查 : 檢查 Ratio 執(zhí)行 SELECT SUM (WAITS)* 100 /SUM (GETS) "RATIO", SUM (WAITS) "WAITS", SUM (GETS) "GETS" FROM V$ROLLSTAT; 檢查 count/value 執(zhí)行 : SELECT CLASS ,COUNT FROM V$WAITSTAT WHERE CLASS LIKE '%UNDO%' ; SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'CONSISTENT GETS' ; ( 兩者的 value 值相除 ) 檢查 average_wait 執(zhí)行 : SELECT EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT FROM V$SYSTEM_EVENT WHERE EVENT LIKE '%UNDO%' ; 檢查 RBS header get ratio 執(zhí)行 : SELECT N.NAME ,S.USN,S.WRAPS, DECODE(S.WAITS, 0 , 1 , 1 - S.WAITS/S.GETS)"RBS HEADER GET RATIO" FROM V$ROLLSTAT S,V$ROLLNAME N WHERE S.USN=N.USN; ( 正常時(shí) Ratio 應(yīng)該小于 1%, count/value 應(yīng)該小于 0.01%,average_wait 最好為 0 ，該值越小越好 ,RBS header get ratio 應(yīng)該大于 95%)
殺會(huì)話的腳本 : SELECT A.SID,B.SPID,A.SERIAL#,A.LOCKWAIT,A.USERNAME,A.OSUSER,A.LOGON_TIME,A.LAST_CALL_ET/ 3600 LAST_HOUR,A.STATUS, 'ORAKILL ' ||SID|| ' ' ||SPID HOST_COMMAND, 'ALTER SYSTEM KILL SESSION ''' ||A.SID|| ',' ||A.SERIAL#|| '''' SQL_COMMAND FROM V$SESSION A,V$PROCESS B WHERE A.PADDR=B.ADDR AND SID> 6 ; 查看排序段的性能 : SQL >SELECT NAME , VALUE FROM V$SYSSTAT WHERE NAME IN ( 'SORTS (MEMORY)' , 'SORTS (DISK)' ); 7 、查看數(shù)據(jù)庫(kù)庫(kù)對(duì)象 : SELECT OWNER, OBJECT_TYPE, STATUS, COUNT (*) COUNT # FROM ALL_OBJECTS GROUP BY OWNER, OBJECT_TYPE, STATUS; 8 、查看數(shù)據(jù)庫(kù)的版本 : SELECT * FROM V$VERSION; 9 、查看數(shù)據(jù)庫(kù)的創(chuàng)建日期和歸檔方式 : SELECT CREATED, LOG_MODE, LOG_MODE FROM V$DATABASE;
10 、捕捉運(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; 11 、查看數(shù)據(jù)表的參數(shù)信息 : SELECT PARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, TABLESPACE_NAME,PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT,NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE, FREELISTS ,FREELIST_GROUPS, LOGGING , BUFFER_POOL , NUM_ROWS, BLOCKS,EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE,LAST_ANALYZED FROM DBA_TAB_PARTITIONS --WHERE TABLE_NAME = :TNAME AND TABLE_OWNER = :TOWNER ORDER BY PARTITION_POSITION; 12 、查看還沒(méi)提交的事務(wù) : SELECT * FROM V$LOCKED_OBJECT; SELECT * FROM V$TRANSACTION; 13 、查找 object 為哪些進(jìn)程所用 : SELECT P.SPID,S.SID,S.SERIAL# SERIAL_NUM,S.USERNAME USER_NAME, A.TYPE OBJECT_TYPE,S.OSUSER OS_USER_NAME,A.OWNER,A.OBJECT OBJECT_NAME,DECODE(SIGN( 48 - COMMAND), 1 , TO_CHAR(COMMAND), 'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION, P.PROGRAM ORACLE_PROCESS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,S.STATUS SESSION_STATUS FROM V$SESSION S, V$ACCESS A, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.TYPE = 'USER' AND A.SID = S.SID? AND A.OBJECT = 'SUBSCRIBER_ATTR' ORDER BY S.USERNAME, S.OSUSER; 14 、查看回滾段 : SQL >COL NAME FORMAT A10 SQL >SET LINESIZE 100 SQL >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 ; 15 、耗資源的進(jìn)程 (top session): SELECT S.SCHEMANAME SCHEMA_NAME,DECODE(SIGN( 48 - COMMAND), 1 , TO_CHAR(COMMAND), 'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION,STATUS SESSION_STATUS,S.OSUSER OS_USER_NAME,S.SID,P.SPID,S.SERIAL# SERIAL_NUM,NVL(S.USERNAME, '[ORACLE PROCESS]' ) USER_NAME,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,ST.VALUE CRITERIA_VALUE FROM V$SESSTAT ST,V$SESSION S,V$PROCESS P WHERE ST.SID = S.SID AND ST.STATISTIC# = TO_NUMBER( '38' ) AND ( 'ALL' = 'ALL' OR S.STATUS = 'ALL' ) AND P.ADDR=S.PADDR ORDER BY ST.VALUE DESC ,P.SPID ASC ,S.USERNAME ASC ,S.OSUSER ASC ; 根據(jù) PID 查找相應(yīng)的語(yǔ)句 :
SELECT A.USERNAME, A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C WHERE B.SPID=SPID AND B.ADDR=A.PADDR AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;
|
根據(jù)
SID
找
ORACLE
的某個(gè)進(jìn)程
:
SQL > SELECT PRO.SPID FROM V$SESSION SES,V$PROCESS PRO WHERE SES.SID=
21
AND SES.PADDR=PRO.ADDR;
監(jiān)控當(dāng)前數(shù)據(jù)庫(kù)誰(shuí)在運(yùn)行什么
SQL
語(yǔ)句
:
SQL >SELECT OSUSER, USERNAME, SQL_TEXT FROM V$SESSION A, V$SQLTEXT B WHERE A.SQL_ADDRESS =B.ADDRESS ORDER BY ADDRESS, PIECE;
如何查看數(shù)據(jù)庫(kù)中某用戶，正在運(yùn)行什么
SQL
語(yǔ)句
SQL >SELECT SQL_TEXT FROM V$SQLTEXT T, V$SESSION S WHERE T.ADDRESS=S.SQL_ADDRESS AND T.HASH_VALUE=S.SQL_HASH_VALUE AND S.MACHINE=
'XXXXX'
OR USERNAME=
'WACOS'
;
如何查出前臺(tái)正在發(fā)出的
sql
語(yǔ)句
:
SQL > SELECT USER_NAME,SQL_TEXT FROM V$OPEN_CURSOR WHERE SID IN (SELECT SID FROM (SELECT SID,SERIAL# FROM V$SESSION WHERE STATUS=
'ACTIVE'
));
查詢當(dāng)前所執(zhí)行的
SQL
語(yǔ)句：
SQL > SELECT PROGRAM ,SQL_ADDRESS FROM V$SESSION WHERE PADDR IN (SELECT ADDR FROM V$PROCESS WHERE SPID=
3556
); PROGRAM
?????????????????????????????????????????
SQL_ADDRESS
------------------------------------------------ ----------------
SQLPLUS@CTC20 (TNS V1-V3)
??????????????????????
000000038
FCB1A90 SQL > SELECT SQL_TEXT FROM V$SQLAREA WHERE ADDRESS=
'000000038FCB1A90'
;
找出消耗
CPU
最高的進(jìn)程對(duì)應(yīng)的
SQL
語(yǔ)句：
SET LINE
240
SET VERIFY OFF? COLUMN SID FORMAT
999
COLUMN PID FORMAT
999
COLUMN S_# FORMAT
999
COLUMN USERNAME FORMAT A9 HEADING "ORA USER" COLUMN PROGRAM FORMAT A29 COLUMN SQL
?????
FORMAT A60 COLUMN OSNAME FORMAT A9 HEADING "OS USER" SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT,
1
,
80
)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE
'%&1%'
; ENTER VALUE FOR
1
: PID?
（這里輸入占用
CPU
最高的進(jìn)程對(duì)應(yīng)的
PID
）
SET TERMOUT OFF? SPOOL MAXCPU.TXT SELECT
'++'
||S.USERNAME USERNAME,RTRIM(REPLACE (A.SQL_TEXT,CHR(
10
),
''
))||
';'
FROM
V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE
'%&&1%'
;
Enter value for 1: PID
（這里輸入占用
CPU
最高的進(jìn)程對(duì)應(yīng)的
PID
）
spool off(
這句放在最后執(zhí)行
)
CPU 用率最高的 2 條 SQL 語(yǔ)句的獲取 執(zhí)行： top ，通過(guò) top 獲得 CPU 占用率最高的進(jìn)程的 pid 。 SQL >SELECT SQL_TEXT,SPID,V$SESSION.PROGRAM,PROCESS FROM V$SQLAREA,V$SESSION,V$PROCESS WHERE V$SQLAREA.ADDRESS=V$SESSION.SQL_ADDRESS AND V$SQLAREA.HASH_VALUE=V$SESSION.SQL_HASH_VALUE AND V$SESSION.PADDR=V$PROCESS.ADDR AND V$PROCESS.SPID IN (PID); COL MACHINE FORMAT A30? COL PROGRAM FORMAT A40? SET LINE 200 SQL >SELECT SID,SERIAL# ,USERNAME,OSUSER,MACHINE,PROGRAM,PROCESS,TO_CHAR(LOGON_TIME, 'YYYY/MM/DD HH24:MI:SS' ) FROM V$SESSION WHERE PADDR IN (SELECT ADDR FROM V$PROCESS WHERE SPID IN ([$SPID])); SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES? WHERE HASH_VALUE=(SELECT SQL_HASH_VALUE FROM V$SESSION WHERE SID=&SID)? ORDER BY PIECE; 16 、查看鎖 （ lock ） 情況 : SQL >SELECT /*+ RULE */ LS.OSUSER OS_USER_NAME, LS.USERNAME USER_NAME, DECODE(LS.TYPE , ??????? 'RW' , 'ROW WAIT ENQUEUE LOCK' , ??????? 'TM' , 'DML ENQUEUE LOCK' , ??????? 'TX' , 'TRANSACTION ENQUEUE LOCK' , ??????? 'UL' , 'USER SUPPLIED LOCK' ) LOCK_TYPE, O.OBJECT_NAME OBJECT , DECODE(LS.LMODE, ??????? 1 ,NULL , ??????? 2 , 'ROW SHARE' , ??????? 3 , 'ROW EXCLUSIVE' , ??????? 4 , 'SHARE' , ??????? 5 , 'SHARE ROW EXCLUSIVE' , ??????? 6 , 'EXCLUSIVE' , ??????? NULL ) LOCK_MODE, O.OWNER, LS.SID, LS.SERIAL# SERIAL_NUM, LS.ID1, LS.ID2 FROM SYS .DBA_OBJECTS O, ?????? (SELECT S.OSUSER, ?????????????? S.USERNAME, ?????????????? L.TYPE , ?????????????? L.LMODE, ?????????????? S.SID, ?????????????? S.SERIAL#, ?????????????? L.ID1, ?????????????? L.ID2 ????????? FROM V$SESSION S, V$LOCK L ???????? WHERE S.SID = L.SID) LS WHERE O.OBJECT_ID = LS.ID1 ?? AND O.OWNER <> 'SYS' ORDER BY O.OWNER, O.OBJECT_NAME; SQL >SELECT SYS .V_$SESSION.OSUSER, ?????? SYS .V_$SESSION.MACHINE, ?????? V$LOCK.SID, ?????? SYS .V_$SESSION.SERIAL#, ?????? DECODE(V$LOCK.TYPE , ????????????? 'MR' , 'MEDIA RECOVERY' , ????????????? 'RT' , 'REDO THREAD' , ????????????? 'UN' , 'USER NAME' , ????????????? 'TX' , 'TRANSACTION' , ????????????? 'TM' , 'DML' , ????????????? 'UL' , 'PL/SQL USER LOCK' , ????????????? 'DX' , 'DISTRIBUTED XACTION' , ????????????? 'CF' , 'CONTROL FILE' , ????????????? 'IS' , 'INSTANCE STATE' , ????????????? 'FS' , 'FILE SET' , ????????????? 'IR' , 'INSTANCE RECOVERY' , ????????????? 'ST' , 'DISK SPACE TRANSACTION' , ????????????? 'TS' , 'TEMP SEGMENT' , ????????????? 'IV' , 'LIBRARY CACHE INVALIDA-TION' , ????????????? 'LS' , 'LOG START OR SWITCH' , ????????????? 'RW' , 'ROW WAIT' , ????????????? 'SQ' , 'SEQUENCE NUMBER' , ????????????? 'TE' , 'EXTEND TABLE' , ????????????? 'TT' , 'TEMP TABLE' , ????????????? 'UNKNOWN' ) LOCKTYPE, ?????? RTRIM(OBJECT_TYPE) || ' ' || RTRIM(OWNER) || '.' || OBJECT_NAME OBJECT_NAME, ?????? DECODE(LMODE, ????????????? 0 , 'NONE' , ????????????? 1 , 'NULL' , ????????????? 2 , 'ROW-S' , ????????????? 3 , 'ROW-X' , ????????????? 4 , 'SHARE' , ????????????? 5 , 'S/ROW-X' , ????????????? 6 , 'EXCLUSIVE' , ????????????? 'UNKNOWN' ) LOCKMODE, ?????? DECODE(REQUEST, ????????????? 0 , 'NONE' , ????????????? 1 , 'NULL' , ????????????? 2 , 'ROW-S' , ????????????? 3 , 'ROW-X' , ????????????? 4 , 'SHARE' , ????????????? 5 , 'S/ROW-X' , ????????????? 6 , 'EXCLUSIVE' , ???????? ???? 'UNKNOWN' ) REQUESTMODE, ?????? CTIME, ?????? BLOCK B FROM V$LOCK, ALL_OBJECTS, SYS .V_$SESSION WHERE V$LOCK.SID > 6 ?? AND SYS .V_$SESSION.SID = V$LOCK.SID ?? AND V$LOCK.ID1 = ALL_OBJECTS.OBJECT_ID; 以 DBA 角色 , 查看當(dāng)前數(shù)據(jù)庫(kù)里鎖的情況可以用如下 SQL 語(yǔ)句： COL OWNER FOR A12? COL OBJECT_NAME FOR A16? SELECT B.OWNER,B.OBJECT_NAME,L.SESSION_ID,L.LOCKED_MODE? FROM V$LOCKED_OBJECT L, DBA_OBJECTS B? WHERE B.OBJECT_ID=L.OBJECT_ID; SQL >SELECT T2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIME FROM V$LOCKED_OBJECT T1,V$SESSION T2 WHERE T1.SESSION_ID=T2.SID ORDER BY T2.LOGON_TIME; SQL >SELECT SQL_ADDRESS FROM V$SESSION WHERE SID=; SQL >SELECT * FROM V$SQLTEXT WHERE ADDRESS=; SQL >SELECT COMMAND_TYPE,PIECE,SQL_TEXT FROM V$SQLTEXT WHERE ADDRESS=(SELECT SQL_ADDRESS FROM V$SESSION A WHERE SID= 18 ); ??? SQL >SELECT OBJECT_ID FROM V$LOCKED_OBJECT; SQL >SELECT OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_ID= '' ; 如果有長(zhǎng)期出現(xiàn)的一列，可能是沒(méi)有釋放的鎖。我們可以用下面 SQL 語(yǔ)句殺掉長(zhǎng)期沒(méi)有釋放非正常的鎖： SQL >ALTER SYSTEM KILL SESSION 'SID,SERIAL#' ; 17 、查看等待（ wait ）情況 : SQL >SELECT V$WAITSTAT.CLASS ,V$WAITSTAT.COUNT COUNT , SUM (V$SYSSTAT.VALUE ) SUM_VALUE FROM V$WAITSTAT,V$SYSSTAT WHERE V$SYSSTAT.NAME IN ( 'DB BLOCK GETS' , 'CONSISTENT GETS' ) GROUP BY V$WAITSTAT.CLASS ,V$WAITSTAT.COUNT ; 18 、查看 sga 情況 : SQL >SELECT NAME , BYTES FROM SYS .V_$SGASTAT ORDER BY NAME ASC ; 19 、查看 catched object: SQL >SELECT OWNER,NAME ,DB_LINK,NAMESPACE,TYPE ,SHARABLE_MEM,LOADS, EXECUTIONS,LOCKS,PINS,KEPT FROM V$DB_OBJECT_CACHE; 20 、查看 V$SQLAREA: SQL >SELECT SQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS, VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,? USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS, DISK_READS,BUFFER_GETS,ROWS_PROCESSED FROM V$SQLAREA; 21 、查看 object 分類數(shù)量 : SELECT DECODE(O.TYPE #, 1 , 'INDEX' , 2 , 'TABLE' , 3 , 'CLUSTER' , 4 , 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) OBJECT_TYPE , COUNT (*) QUANTITY FROM SYS .OBJ$ O WHERE O.TYPE # > 1 GROUP BY DECODE(O.TYPE #, 1 , 'INDEX' , 2 , 'TABLE' , 3 , 'CLUSTER' , 4 , 'VIEW' , 5 , 'SYNONYM' , 6 , 'SEQUENCE' , 'OTHER' ) UNION SELECT 'COLUMN' , COUNT (*) FROM SYS .COL$ UNION SELECT 'DB LINK' , COUNT (*) FROM ALL_OBJECTS; 22 、有關(guān) connection 的相關(guān)信息 : 1 ）查看有哪些用戶連接 SELECT S.OSUSER OS_USER_NAME,DECODE(SIGN( 48 - COMMAND), 1 ,TO_CHAR(COMMAND), 'ACTION CODE #' || TO_CHAR(COMMAND))ACTION,P.PROGRAM ORACLE_PROCESS, STATUS SESSION_STATUS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM, S.USERNAME USER_NAME,S.FIXED_TABLE_SEQUENCE ACTIVITY_METER, '' QUERY , 0 MEMORY, 0 MAX_MEMORY, 0 CPU_USAGE,S.SID,S.SERIAL# SERIAL_NUM FROM V$SESSION S,V$PROCESS P WHERE S.PADDR=P.ADDR AND S.TYPE = 'USER' ORDER BY S.USERNAME, S.OSUSER; 2 ）根據(jù) v.sid 查看對(duì)應(yīng)連接的資源占用等情況 SELECT N.NAME ,V.VALUE ,N.CLASS ,N.STATISTIC# FROM V$STATNAME N,V$SESSTAT V WHERE V.SID= 18 AND V.STATISTIC# = N.STATISTIC# ORDER BY N.CLASS , N.STATISTIC#; 3 ）根據(jù) sid 查看對(duì)應(yīng)連接正在運(yùn)行的 sql SELECT /*+ PUSH_SUBQ */ COMMAND_TYPE,SQL_TEXT,SHARABLE_MEM, PERSISTENT_MEM,RUNTIME_MEM,SORTS,VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS, USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,SYSDATE START_TIME,SYSDATE FINISH_TIME, '>' || ADDRESS SQL_ADDRESS, 'N' STATUS FROM V$SQLAREA WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID= 8 ); 根據(jù) pid 查看 sql 語(yǔ)句 : SELECT SQL_TEXT FROM V$SQL WHERE ADDRESS IN (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID IN (SELECT SID FROM V$SESSION WHERE PADDR IN (SELECT ADDR FROM V$PROCESS WHERE SPID=&PID))); |
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
