oracle优化14

22 、有关 connection 的相关信息 :
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 ）根据 v.sid 查看对应连接的资源占用等情况
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 ）根据 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= 8 );

根据 pid 查看 sql 语句 :

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

23、查询表空间使用情况:
SELECT A.TABLESPACE_NAME " 空间名称 ", 100 -ROUND((NVL(B.BYTES_FREE, 0 )/A.BYTES_ALLOC)* 100 , 2 ) " 占用率 (%) ", ROUND(A.BYTES_ALLOC/ 1024 / 1024 , 2 ) " 容量 (M) ",
ROUND(NVL(B.BYTES_FREE, 0 )/ 1024 / 1024 , 2 ) 空闲 (M) ",
ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE, 0 ))/ 1024 / 1024 , 2 ) " 使用 (M) ",
LARGEST " 最大扩展段 (M) ",TO_CHAR(SYSDATE , 'YYYY-MM-DD HH24:MI:SS' ) " 采样时间 " FROM (SELECT F.TABLESPACE_NAME,SUM (F.BYTES) BYTES_ALLOC,SUM (DECODE(F.AUTOEXTENSIBLE, 'YES' ,F.MAXBYTES, 'NO' ,F.BYTES)) MAXBYTES FROM DBA_DATA_FILES F GROUP BY TABLESPACE_NAME) A,(SELECT F.TABLESPACE_NAME,SUM (F.BYTES) BYTES_FREE FROM DBA_FREE_SPACE F GROUP BY TABLESPACE_NAME) B,(SELECT ROUND(MAX (FF.LENGTH)* 16 / 1024 , 2 ) LARGEST,TS.NAME TABLESPACE_NAME FROM SYS .FET$ FF, SYS .FILE $ TF,SYS .TS$ TS WHERE TS.TS#=FF.TS# AND FF.FILE #=TF.RELFILE# AND TS.TS#=TF.TS# GROUP BY TS.NAME , TF.BLOCKS) C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;