Oracle排查问题常用脚本

 

使用Oracle做数仓时，经常会查询一些数据库会话信息。 平时常用的查询SQL如下：

查询会话信息，注释部分如果打开，则显示undo的使用情况。

 SELECT DISTINCT S.INST_ID,
                S.MACHINE,
                S.BLOCKING_SESSION,
                S.SQL_ID,
                S.LAST_CALL_ET,
                S.SID,
                S.SERIAL#,
                S.EVENT,
                S.USERNAME,
                S.MODULE,
                Q.SQL_TEXT,
                P.SPID,
                'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# ||
                ''' IMMEDIATE; ' AS KILLSESSION/*,
                T.XIDUSN,
                T.UBAFIL,
                T.UBABLK,
                T.USED_UBLK,
                T.START_DATE,
                T.STATUS AS TRANS_STATUS,
                R.STATUS AS ROLL_STATUS,
                R.RSSIZE,
                RS.SEGMENT_NAME*/
  FROM GV$SESSION S
  JOIN GV$PROCESS P
    ON S.PADDR = P.ADDR
   AND S.INST_ID = P.INST_ID
  JOIN V$INSTANCE I
    ON S.INST_ID = I.INSTANCE_NUMBER
/*  LEFT JOIN V$TRANSACTION T
    ON S.SADDR = T.SES_ADDR
  LEFT JOIN V$ROLLSTAT R
    ON T.XIDUSN = R.USN
  LEFT JOIN DBA_ROLLBACK_SEGS RS
    ON RS.SEGMENT_ID = T.XIDUSN*/
  LEFT JOIN GV$SQL Q
    ON S.SQL_ID = Q.SQL_ID
   AND S.INST_ID = Q.INST_ID
 WHERE WAIT_CLASS <> 'Idle';

--查看执行计划
Select * From Table(dbms_xplan.display_cursor(''));

 

查询历史会话信息

历史会话信息就像v$session 的快照。 每秒都会记录。 用来定位已经退出的会话是否存在问题。

SELECT *
  FROM V$ACTIVE_SESSION_HISTORY A
 WHERE A.SESSION_ID = '1990'
   AND A.SAMPLE_TIME >=
       TO_DATE('2020-12-25 10:00:00', 'yyyy-mm-dd hh24:mi:ss');

 

如果想追溯很久之前的会话是否存在问题，也可以查看 dba_hist_active_sess_history ，这个是会话的历史信息， 会记录更长时间。 

 

如果碰到 temp表空间不足的情况，并且已经确定自己的SQL没问题，那么可以考虑看一下是否有其他会话正在占用临时表空间。

SELECT SESSION_ADDR, USERNAME, A.BLOCKS FROM V$SORT_USAGE A;

• session_addr 可以关联 v$session.saddr 
• username 用来确定是什么用户在占用临时表空间
• blocks 可以确定占用表空间的大小

查询表空间的SQL

SELECT TABLESPACETYPE,
       TABLESPACE_NAME,
       ROUND(SUM(FREE_SIZE_GB), 4) AS FREE_SIZE_GB,
       ROUND(SUM(TOTAL_SIZE_GB), 4) AS TOTAL_SIZE_GB,
       ROUND(SUM(EXTENDED_MAX_SIZE_GB), 4) AS EXTENDED_MAX_SIZE_GB,
       ROUND(SUM(FREE_SIZE_GB) / SUM(TOTAL_SIZE_GB), 4) * 100 AS CURRENT_FREE_PERCENTS
  FROM (SELECT 'DATA' AS TABLESPACETYPE,
               TABLESPACE_NAME,
               0 AS FREE_SIZE_GB,
               SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL_SIZE_GB,
               SUM(DECODE(A.AUTOEXTENSIBLE, 'YES', A.MAXBYTES, 'NO', A.BYTES)) / 1024 / 1024 / 1024 AS EXTENDED_MAX_SIZE_GB
          FROM DBA_DATA_FILES A
         GROUP BY TABLESPACE_NAME
        UNION ALL
        SELECT 'DATA' AS TABLESPACETYPE,
               TABLESPACE_NAME,
               SUM(BYTES) / 1024 / 1024 / 1024 AS FREE_SIZE_GB,
               0 AS TOTAL_SIZE_GB,
               0 AS EXTENDED_MAX_SIZE_GB
          FROM DBA_FREE_SPACE B
         GROUP BY TABLESPACE_NAME
        UNION ALL
        SELECT 'TEMP' AS TABLESPACETYPE,
               TABLESPACE_NAME,
               0 AS FREE_SIZE_GB,
               SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL_SIZE_GB,
               0 AS EXTENDED_MAX_SIZE_GB
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME
        UNION ALL
        SELECT 'TEMP' AS TABLESPACETYPE,
               TABLESPACE_NAME,
               SUM(FREE_SPACE) / 1024 / 1024 / 1024 AS FREE_SIZE_GB,
               0 AS TOTAL_SIZE_GB,
               0 AS EXTENDED_MAX_SIZE_GB
          FROM DBA_TEMP_FREE_SPACE
         GROUP BY TABLESPACE_NAME)
 GROUP BY TABLESPACETYPE, TABLESPACE_NAME;