运维常用的SQL

运维常用的SQL

2017年05月17日 17:02:36 moilbeauty 阅读数：274 标签： 运维sql 更多

个人分类： oracle

 

根据自己的工作经验，列出一些运维或者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');

转载于:https://www.cnblogs.com/carry00/p/10119266.html