运维常用的SQL

根据自己的工作经验，列出一些运维或者DBA常用的sql，用于分析SQL消耗情况，索引信息，kill session，用户权限查询等等信息

1、统计索引大小

SELECT A.SEGMENT_NAME, SUM(BYTES) / (1024 * 1024 * 1024) AS "size(G)"
  FROM DBA_SEGMENTS A, DBA_USERS B
 WHERE A.OWNER = B.USERNAME
   AND A.OWNER IN ('XXX_01') --索引所属owner
   AND B.ACCOUNT_STATUS = 'OPEN'
   AND A.SEGMENT_TYPE IN ('INDEX', 'INDEX PARTITION')
   AND A.SEGMENT_NAME IN ('XXX_02') --替换成你要查的索引名

2、查看sql某段时间内的消耗，执行计划信息

这是我最常用的sql，特别推荐

SELECT *
  FROM (SELECT TO_CHAR(BEGIN_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss') TIME,
               S.SQL_ID,
               S.PLAN_HASH_VALUE,
               (SELECT SQL_TEXT
                  FROM DBA_HIST_SQLTEXT ST
                 WHERE ST.SQL_ID = S.SQL_ID) SQL_TEXT,
               SUM(S.EXECUTIONS_DELTA) EXECUTIONS,
               ROUND((SUM(ELAPSED_TIME_DELTA) / SUM(S.EXECUTIONS_DELTA)) /
                     1000000,
                     2) PER_ELAPSED_TIME,
               ROUND((SUM(S.CPU_TIME_DELTA) / SUM(S.EXECUTIONS_DELTA)) /
                     1000000,
                     0) PER_CPU_TIME,
               ROUND(SUM(S.DISK_READS_DELTA) / SUM(S.EXECUTIONS_DELTA), 0) PER_DISK_READS,
               ROUND(SUM(S.BUFFER_GETS_DELTA) / SUM(S.EXECUTIONS_DELTA), 0) PER_BUFFER_GETS,
               ROUND(SUM(S.IOWAIT_DELTA) / SUM(S.EXECUTIONS_DELTA) / 1000000,
                     0) PER_IOWAIT,
               ROUND(SUM(ELAPSED_TIME_DELTA) / 1000000, 2) ELAPSED_TIME,
               ROUND(SUM(S.CPU_TIME_DELTA) / 1000000, 2) CPU_TIME,
               SUM(S.DISK_READS_DELTA) DISK_READS,
               SUM(S.BUFFER_GETS_DELTA) BUFFER_GETS,
               ROUND(SUM(S.IOWAIT_DELTA) / 1000000, 2) IOWAIT
          FROM DBA_HIST_SNAPSHOT SN, DBA_HIST_SQLSTAT S
         WHERE S.SNAP_ID = SN.SNAP_ID
              --and s.parsing_schema_name = 'XXX_01'
           AND S.SQL_ID = 'XXX_02' --替换成你要查询的sql_id
           AND S.EXECUTIONS_DELTA <> 0
           AND TO_DATE(TO_CHAR(BEGIN_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss'),
                       'yyyy-mm-dd hh24:mi:ss') >=
               TO_DATE('2017-05-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
           AND TO_DATE(TO_CHAR(BEGIN_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss'),
                       'yyyy-mm-dd hh24:mi:ss') < =
               TO_DATE('2017-05-17 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
         GROUP BY TO_CHAR(BEGIN_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss'),
                  S.SQL_ID,
                  S.PLAN_HASH_VALUE,
                  S.PARSING_SCHEMA_NAME
         ORDER BY S.SQL_ID,
                  TO_CHAR(BEGIN_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss'),
                  S.PLAN_HASH_VALUE,
                  S.PARSING_SCHEMA_NAME);

3、批量kill sql

当数据库不停的有高消耗sql进来的时候，可以用这个语句进行批量kill

DECLARE
  CURSOR C_MAIN IS
    SELECT *
      FROM V$SESSION
     WHERE USERNAME = 'XXX_01'  --执行用户
       AND STATUS = 'ACTIVE'
          --AND SQL_HASH_VALUE = ''
       AND SQL_ID = 'XXX_02'; --sql_id
  V_MAIN C_MAIN%ROWTYPE;
BEGIN
  OPEN C_MAIN;
  LOOP
    FETCH C_MAIN
      INTO V_MAIN;
    EXIT WHEN C_MAIN%NOTFOUND;
    PRC_KILL_SESSION(V_MAIN.USERNAME, V_MAIN.SID, V_MAIN.SERIAL#);
  END LOOP;
  CLOSE C_MAIN;
END;

4、取当前执行计划SQLPLAN

SELECT PLAN_HASH_VALUE,
       TIMESTAMP,
       ID,
       LPAD(' ', DEPTH * 4) || OPERATION OPERATION,
       OPTIONS,
       OBJECT_OWNER,
       OBJECT_NAME,
       OPTIMIZER,
       COST,
       CARDINALITY,
       BYTES,
       CPU_COST,
       IO_COST,
       TEMP_SPACE,
       ACCESS_PREDICATES,
       T.FILTER_PREDICATES
  FROM V$SQL_PLAN T
 WHERE SQL_ID = 'XXX_01' --替换成sql_id
 ORDER BY PLAN_HASH_VALUE, TIMESTAMP, ID;

5、取历史执行计划SQLPLAN

SELECT PLAN_HASH_VALUE,
       TIMESTAMP,
       ID,
       LPAD(' ', DEPTH * 4) || OPERATION OPERATION,
       OPTIONS,
       OBJECT_OWNER,
       OBJECT_NAME,
       OPTIMIZER,
       COST,
       CARDINALITY,
       BYTES,
       CPU_COST,
       IO_COST,
       TEMP_SPACE,
       ACCESS_PREDICATES
  FROM DBA_HIST_SQL_PLAN
 WHERE SQL_ID = 'XXX_01' --替换成sql_id
/* and plan_hash_value = '887817209'*/
 ORDER BY PLAN_HASH_VALUE, ID;

6、查看存储过程

SELECT * FROM DBA_SOURCE WHERE NAME=upper('MY_PACKAGE_DEMO');

7、获取绑定变量

SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = 'XXX_01';

--可获取历史sql绑定变量
SELECT SNAP_ID, NAME, POSITION, VALUE_STRING, LAST_CAPTURED, WAS_CAPTURED
  FROM DBA_HIST_SQLBIND
 WHERE SQL_ID = 'XXX_01'
--AND SNAP_ID = '133030'; --快照

8、查表占用的大小

SELECT T.SEGMENT_NAME, BYTES / 1024 / 1024 "Space(MB)"
  FROM DBA_SEGMENTS T
 WHERE SEGMENT_NAME = UPPER('TABLE_NAME');

9、表和索引的统计信息

--查看表的统计信息
SELECT A.OWNER,
       A.TABLE_NAME,
       A.NUM_ROWS,
       A.LAST_ANALYZED,
       A.STATTYPE_LOCKED
  FROM DBA_TAB_STATISTICS A
 WHERE A.TABLE_NAME IN ('YOUR_TABLE_01', 'YOUR_TABLE_02', 'YOUR_TABLE_03');

--查看索引的统计信息
SELECT T.OWNER,
       T.INDEX_NAME,
       T.TABLE_NAME,
       T.LAST_ANALYZED,
       T.STATTYPE_LOCKED
  FROM DBA_IND_STATISTICS T
 WHERE T.INDEX_NAME IN
       ('IX_INDEX_01', 'IX_INDEX_02', 'IX_INDEX_03', 'PK_INDEX_01');