Oracle中常用的管理SQL整合

----检查失效的索引：

SELECT INDEX_NAME, TABLE_NAME, TABLESPACE_NAME, STATUS,OWNER FROM DBA_INDEXES WHERE OWNER LIKE '%BOSS%' AND STATUS <> 'VALID';

----height>=4或者DEL_LF_ROWS/LF_ROWS>0.2的场合，该索引考虑重建；

SELECT HEIGHT, DEL_LF_ROWS / LF_ROWS FROM INDEX_STATS;

----以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句：

SELECT B.OWNER,B.OBJECT_NAME,L.SESSION_ID,L.LOCKED_MODE,V.LOGON_TIME

FROM V$LOCKED_OBJECT L, DBA_OBJECTS B ,V$SESSION V

WHERE B.OBJECT_ID=L.OBJECT_ID AND L.SESSION_ID=V.SID

ORDER BY LOGON_TIME DESC;

----查询被谁锁了

SELECT L.SESSION_ID SID,S.SERIAL#,L.LOCKED_MODE,L.ORACLE_USERNAME,S.USER#,L.OS_USER_NAME,S.MACHINE,S.TERMINAL,A.SQL_TEXT,A.ACTION,

'ALTER SYSTEM KILL SESSION '''||s.sid||','||s.serial#||''';' Command FROM V$SQLAREA A, V$SESSION S, V$LOCKED_OBJECT L

WHERE L.SESSION_ID = S.SID AND S.PREV_SQL_ADDR = A.ADDRESS ORDER BY SID, S.SERIAL#;

----根据sid查看对应连接正在运行的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=3086);

----根据SID找ORACLE的某个进程:

SELECT PRO.SPID FROM V$SESSION SES,V$PROCESS PRO WHERE SES.SID=21 AND SES.PADDR=PRO.ADDR;

----根据v.sid查看对应连接的资源占用等情况

SELECT N.NAME,V.VALUE,N.CLASS,N.STATISTIC# FROM V$STATNAME N,V$SESSTAT V WHERE V.SID=2596 AND V.STATISTIC# = N.STATISTIC# ORDER BY N.CLASS, N.STATISTIC#;

----查看锁（lock）情况:

SELECT /*+ RULE */

LS.OSUSER OS_USER_NAME, LS.USERNAME USER_NAME, O.OBJECT_NAME OBJECT,O.OWNER, LS.SID, LS.SERIAL# SERIAL_NUM, LS.ID1, LS.ID2,

DECODE(LS.TYPE,'RW','ROW WAIT ENQUEUE LOCK','TM','DML ENQUEUE LOCK','TX','TRANSACTION ENQUEUE LOCK','UL','USER SUPPLIED LOCK') LOCK_TYPE,

DECODE(LS.LMODE, 1, NULL, 2, 'ROW SHARE', 3, 'ROW EXCLUSIVE', 4, 'SHARE', 5, 'SHARE ROW EXCLUSIVE', 6, 'EXCLUSIVE', NULL) LOCK_MODE

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;

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;

----查找前十条性能差的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;

----Disk Read最高的SQL语句的获取：

SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM<=5;

----捕捉运行很久的SQL:

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;

----查看占io较大的正在运行的session:

SELECT SE.SID,SE.SERIAL#,PR.SPID,SE.USERNAME,SE.STATUS,SE.TERMINAL,SE.PROGRAM, SE.MODULE,SE.SQL_ADDRESS,ST.EVENT,ST.P1TEXT,SI.PHYSICAL_READS,SI.BLOCK_CHANGES FROM V$SESSION SE,V$SESSION_WAIT ST,V$SESS_IO SI,V$PROCESS PR WHERE ST.SID=SE.SID AND ST.SID=SI.SID AND SE.PADDR=PR.ADDR AND SE.SID>6 AND ST.WAIT_TIME=0 AND ST.EVENT NOT LIKE '%SQL%' ORDER BY PHYSICAL_READS DESC;

----查看系统表中的用户索引（用来检查在system表空间内其他用户索引的存在）：

SELECT * FROM DBA_INDEXES WHERE TABLESPACE_NAME='SYSTEM' AND OWNER NOT IN('SYS','SYSTEM');

----查询有哪些数据库实例在运行:

select inst_name from v$active_instances;

----查看有哪些用户连接

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;

----查看V$SQLAREA:

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;

----查看等待（wait）情况:

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;

----CPU用率最高的2条SQL语句的获取,执行：top，通过top获得CPU占用率最高的进程的pid。

SELECT SQL_TEXT,SPID,V$SESSION.PROGRAM,PROCESS,'ALTER SYSTEM KILL SESSION '''||V$SESSION.sid||','||V$SESSION.serial#||''';' Command 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;

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;

----耗资源的进程(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;

----查出前台正在发出的sql语句:

SELECT * FROM (

SELECT USER_NAME,SQL_TEXT FROM V$OPEN_CURSOR WHERE SID IN(SELECT SID FROM (SELECT SID,SERIAL# FROM V$SESSION WHERE STATUS='ACTIVE'))) WHERE sql_text LIKE 'insert into%';

----查看数据库中某用户，正在运行什么SQL语句

SELECT S.USERNAME,S.SCHEMANAME,S.OSUSER,S.MACHINE,S.TERMINAL,S.PROCESS,S.PORT,S.TERMINAL,S.STATUS,C.SQL_TEXT

FROM V$SESSION S, V$OPEN_CURSOR C WHERE S.SADDR=C.SADDR AND S.SID=C.SID AND USERNAME='BOSS_CRM' AND C.SQL_TEXT LIKE 'insert%';

----通过SQL查进程

lsof -Pnl +M -i4 |grep LISTEN | grep 62300;

netstat -lnp |grep LISTEN |grep 62300;

----监控当前数据库谁在运行什么SQL语句:

SELECT OSUSER, USERNAME, SQL_TEXT FROM V$SESSION A, V$SQLTEXT B WHERE A.SQL_ADDRESS =B.ADDRESS ORDER BY ADDRESS, PIECE;

----检查数据字典的命中率:(对于library cache, gethitratio和pinhitratio应该大于90%,对于数据重载比率,reload ratio应该小于1%,对于数据字典的命中率,data dictionary hit ratio应该大于85%)

SELECT 1-SUM(GETMISSES)/SUM(GETS) "DATA DICTIONARY HIT RATIO" FROM V$ROWCACHE;

----检查共享内存的剩余情况:(对于共享内存的剩余情况, request_misses 和request_failures应该接近0)

SELECT REQUEST_MISSES, REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED;

----检查无效的数据库对象：

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS = 'INVALID';

----检查不起作用的约束：

SELECT OWNER, CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE, STATUS FROM DBA_CONSTRAINTS WHERE STATUS = 'DISABLED';

----高水位线

SELECT OWNER,TABLESPACE_NAME, TABLE_NAME 表名,NUM_ROWS, TRUNC((NUM_ROWS * AVG_ROW_LEN / 1024 / 8), 0) 使用的数据块,

BLOCKS 高水位线, TRUNC((NUM_ROWS * AVG_ROW_LEN / 1024 / 8 / BLOCKS), 2) AS 数据所占比例

FROM DBA_TABLES WHERE BLOCKS IS NOT NULL

AND (NUM_ROWS * AVG_ROW_LEN / 1024 / 8 / BLOCKS) < 0.5 AND BLOCKS > 10000;

----查询表空间使用情况---

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

D.TOT_GROOTTE_MB/1024 "表空间大小(G)",

(D.TOT_GROOTTE_MB - F.TOTAL_BYTES)/1024 "已使用空间(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 F.TABLESPACE_NAME DESC;