近期客户需求巡检自己编写整理的SQL

--根据DBA_HIST_SQLSTAT查询DML前10的DML语句
--对于快照半小时一次的执行次数超过360000即tps达到200
--对于快照一小时一次的执行次数超过720000即tps达到200
select sql_id, snap_id, EXECUTIONS, sql_text
  from (select b.*, upper(dbms_lob.substr(d.sql_text, 1000, 1)) as sql_text
          from (select *
                  from (select a.*,
                               row_number() over(partition by sql_id order by EXECUTIONS desc) rn
                          from (SELECT M.SQL_ID,
                                       SNAP_ID,
                                       SUM(M.EXECUTIONS_DELTA) EXECUTIONS
                                  FROM DBA_HIST_SQLSTAT M
                                 GROUP BY M.SQL_ID, SNAP_ID) a)
                 where rn = 1) b,
               dba_hist_sqltext d
         where b.EXECUTIONS is not null
           and b.sql_id = d.sql_id
           and (upper(sql_text) like 'DELETE%' or
               upper(sql_text) like 'INSERT%' or
               upper(sql_text) like 'UPDATE%' OR
               upper(sql_text) like 'MERGE%')
         order by 3 desc) c
 where rownum <= 10;
 
--失效索引
select index_name name,'No Partition' partition,'No Subpartition' Subpartition,status
from all_indexes where status not in('VALID','USABLE','N/A')
union
select index_name name,partition_name partition,'No Subpartition' Subpartition,status
from all_ind_partitions where status not in('VALID','USABLE','N/A')
union
select index_name name,partition_name partition,subpartition_name Subpartition,status
from all_ind_subpartitions where status not in('VALID','USABLE','N/A');
 

--索引过多的表
select *
  from (select owner, table_name, count(1) cn
          from dba_indexes
         where owner not in ('SYS', 'SYSTEM')
         group by owner, table_name)
 where cn > 10;


--前10大小的索引
select *
  from (select *
          from (select x.owner,
                       x.segment_name,
                       sum(x.BYTES / 1024 / 1024 / 1024) ds
                  from dba_segments x
                 where segment_type LIKE 'INDEX%'
                   and owner not in ('SYS', 'SYSTEM')
                 group by x.owner, x.segment_name)
         order by 3 desc)
 where rownum <= 10;

--大于1G的表且索引占比表大于0.5
--非分区索引
select a.owner,
       a.segment_name as table_name,
       a.tab_dx,
       b.segment_name as idx_name,
       b.idx_dx
  from (select m.owner,
               m.segment_name,
               sum(m.bytes / 1024 / 1024 / 1024) tab_dx
          from dba_segments m
         where owner not in ('SYS', 'SYSTEM')
           and segment_type like 'TABLE%'
         group by m.owner, m.segment_name) a,
       (select m.owner,
               m.segment_name,
               m.bytes / 1024 / 1024 / 1024 idx_dx,
               n.table_name
          from dba_segments m, dba_indexes n
         where m.owner not in ('SYS', 'SYSTEM')
           and m.segment_type = 'INDEX'
           and m.owner = n.owner
           and m.segment_name = n.index_name) b
 where a.owner = b.owner
   and a.segment_name = b.table_name
   and idx_dx / tab_dx > 0.5
   and tab_dx > 1
union all
--分区索引
select a.owner, a.segment_name, a.tab_dx, b.segment_name, b.idx_dx
  from (select m.owner,
               m.segment_name,
               sum(m.bytes / 1024 / 1024 / 1024) tab_dx
          from dba_segments m
         where owner not in ('SYS', 'SYSTEM')
           and segment_type like 'TABLE%'
         group by m.owner, m.segment_name) a,
       (select m.owner, m.segment_name, idx_dx, n.table_name
          from (select m.owner,
                       m.segment_name,
                       sum(m.bytes / 1024 / 1024 / 1024) idx_dx
                  from dba_segments m
                 where owner not in ('SYS', 'SYSTEM')
                   and m.segment_type = 'INDEX PARTITION'
                 group by m.owner, m.segment_name) m,
               dba_indexes n
         where m.owner = n.owner
           and m.segment_name = n.index_name) b
 where a.owner = b.owner
   and a.segment_name = b.table_name
   and idx_dx / tab_dx > 0.5
   and tab_dx > 1;


--查询出带@的语句是否使用dblink，检查时长较长的语句是否有风险
--一般禁止dblink进行表关联
select *
  from (select x.*,
               row_number() over(partition by sql_id order by time_s desc) rn
          from (select a.sql_id,
                       dbms_lob.substr(a.sql_text, 1000, 1) sql_text,
                       decode(EXECUTIONS_DELTA,
                              0,
                              ELAPSED_TIME_DELTA,
                              round(ELAPSED_TIME_DELTA / EXECUTIONS_DELTA / 1000 / 1000,
                                    2)) time_s
                  from dba_hist_sqltext a, DBA_HIST_SQLSTAT b
                 where a.sql_text like '%@%'
                   and upper(a.sql_text) not like 'BEGIN%'
                   and a.sql_id = b.sql_id) x)
 where rn = 1
 order by 3 desc



--查询dml语句及平均时长及一次操作平均行数，检查耗时长操作行数多的语句
--对于dml语句如果执行时间太长或者操作行数过多都存在风险
select *
  from (select x.*,
               row_number() over(partition by sql_id order by time_s desc) rn
          from (select a.sql_id,
                       dbms_lob.substr(a.sql_text, 1000, 1) sql_text,
                       decode(EXECUTIONS_DELTA,
                              0,
                              ELAPSED_TIME_DELTA,
                              round(ELAPSED_TIME_DELTA / EXECUTIONS_DELTA / 1000 / 1000,
                                    2)) time_s,
                       decode(EXECUTIONS_DELTA,
                              0,
                              ROWS_PROCESSED_DELTA,
                              round(ROWS_PROCESSED_DELTA / EXECUTIONS_DELTA, 3)) per_rows
                  from dba_hist_sqltext a, DBA_HIST_SQLSTAT b
                 where (upper(sql_text) like 'DELETE%' or
                       upper(sql_text) like 'INSERT%' or
                       upper(sql_text) like 'UPDATE%' OR
                       upper(sql_text) like 'MERGE%')
                   and a.sql_id = b.sql_id
                 order by 3 desc nulls last) x)
 where rn = 1
 order by 3 desc



--表高水位
WITH hwm_a AS
 (SELECT a.table_name,
         a.owner,
         a.partitioned,
         a.LAST_ANALYZED,
         ROUND(a.num_rows * a.avg_row_len * 1.1 * (1 - a.pct_free / 100) / 1024 / 1024,
               0) row_sum_mb,
         ROUND(a.blocks *
               (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024,
               0) blocks_sum_mb
    FROM dba_tables a
   where num_rows is not null
     and partitioned = 'NO')
SELECT hwm_a.owner,
       hwm_a.table_name,
       hwm_a.partitioned,
       hwm_a.LAST_ANALYZED,
       hwm_a.row_sum_mb,
       hwm_a.blocks_sum_mb,
       hwm_b.segment_sum_mb,
       hwm_a.row_sum_mb / hwm_b.segment_sum_mb AS hwm_percent
  FROM hwm_a,
       (SELECT round(bytes / 1024 / 1024) segment_sum_mb,
               owner,
               segment_name
          FROM dba_segments
         where round(bytes / 1024 / 1024) > 500
           and PARTITION_NAME is null) hwm_b
 WHERE hwm_a.owner = hwm_b.owner
   AND hwm_a.table_name = hwm_b.segment_name
   AND hwm_a.row_sum_mb / hwm_b.segment_sum_mb < 0.5  --剔除计算出的大小占实际大小的一半以下 
 ORDER BY hwm_percent;