oracle优化13

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= '' ;
如果有长期出现的一列，可能是没有释放的锁。我们可以用下面 SQL 语句杀掉长期没有释放非正常的锁： 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 分类数量 :
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;