oracle优化7

10 、捕捉运行很久的 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 、查看数据表的参数信息 :
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 、查看还没提交的事务 :
SELECT * FROM V$LOCKED_OBJECT;
SELECT * FROM V$TRANSACTION;

13 、查找 object 为哪些进程所用 :
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 、耗资源的进程 (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 ;

根据 PID 查找相应的语句 :

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;