oracle 性能监测脚本

--索引占用空间

select segment_name,sum(bytes)/1024/1024 from user_segments where segment_type='INDEX' and segment_name = 'XPK_M_CJZB_QJ_M' group by segment_name;

--查对象大小

select segment_name,sum(bytes)/1024/1024 from user_segments group by segment_name;

--查表空间大小

select t.tablespace_name,d.file_name,d.autoextensible,d.bytes,d.maxbytes,d.status 
from dba_tablespaces t,dba_data_files d where t.tablespace_name = d.tablespace_name and t.tablespace_name='FDMTBS';

--查空闲空间

select t.name, --表空间名称
       free_space,  --剩余大小
       (total_space - free_space) used_space, --被使用大小
       total_space --总大小
  from (select tablespace_name, sum(bytes / 1024 / 1024 / 1024) free_space
          from sys.dba_free_space
         group by tablespace_name) free,
       (select b.name, sum(bytes / 1024 / 1024) total_space
          from sys.v_$datafile a, sys.v_$tablespace b
         where a.ts# = b.ts#
         group by b.name) t
 where free.tablespace_name = t.name;

--查表空间使用情况

select b.file_name,b.tablespace_name,(b.bytes/1024/1024/1024) as 总空间,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024 as 已用,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) as 空间使用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_name;

--改表空间

alter tablespace FDMTBS add datafile '+DATA/hlgssdb/datafile/oradataFDMTBS02.DBF' size 30G autoextend on;

--查索引字段

select inde.table_name,ind.column_name from user_ind_columns ind inner join user_indexes inde on ind.index_name=inde.index_name and ind.table_name=inde.table_name;

select segment_name,segment_type,tablespace_name,extents from dba_segments where user='ZFETL' and segment_type='INDEX';

--分页查询索引信息
select * from(select A.*,rownum rn from(SELECT b.index_name,b.table_name,b.column_name,a.uniqueness,a.owner FROM all_indexes a,all_ind_columns b WHERE a.index_name = b.index_name)A where rownum <5) where rn >1;

--查看连接

select count(*) from v$process; --查看当前的连接数
select value from v$parameter where name = 'processes'; --查看数据库允许的最大连接数

select count(*) from v$session; --查看当前的连接数
select username,schemaname,osuser,machine,count(1) from v$session group by username,schemaname,osuser,machine; --查看哪些用户正在连接
select schemaname,osuser,count(1) from v$session group by schemaname,osuser order by schemaname; --查看哪些用户正在连接
select count(1) from v$session where schemaname like '%DEV'; --查看哪些用户正在连接
--修改最大连接数:
alter system set processes = 500 scope = spfile;

--查询总连接数
select value from v$parameter where name = 'processes';
--查询当前会话连接数
select count(*) from v$session;
--查询当前连接用户(需补充分页)
select username,schemaname,osuser,machine,count(1),sys_context('userenv', 'ip_address') as ipadd from v$session group by username,schemaname,osuser,machine;

--统计更新

--统计更新
exec DBMS_STATS.gather_schema_stats('hfmsspe',dbms_stats.AUTO_SAMPLE_SIZE);
--单表统计更新
exec DBMS_STATS.gather_table_stats('hfmsdev','ev_retloansign');
analyze table ev_dtlpers compute statistics for table for all indexes for all columns;
call dbms_stats.gather_table_stats(user, #tablename#);
--查看最后统计更新时间
select owner,table_name,last_analyzed from user_tab_statistics where table_name ='EV_RETLOANSIGN'; 
--查看表的统计信息
select owner,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,last_analyzed from user_tables where table_name = 'EV_RETLOANSIGN';

--查看当前有哪些用户正在使用数据

SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine  
from v$session a, v$sqlarea b;
SELECT osuser, a.username,machine,count(1)  
from v$session a, v$sqlarea b group by osuser, a.username,machine;

--锁

--查询锁
SELECT A.OWNER,A.OBJECT_NAME,B.XIDUSN,B.XIDSLOT,B.XIDSQN,B.SESSION_ID,
       B.ORACLE_USERNAME,B.OS_USER_NAME,B.PROCESS,B.LOCKED_MODE,C.MACHINE,
       C.STATUS,C.SERVER,C.PROGRAM,C.SID,C.SERIAL#,C.ACTION,C.MODULE,
       'ALTER SYSTEM KILL SESSION ''' || C.SID || ',' || C.SERIAL# || ''';' SQL
  FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C
 WHERE (A.OBJECT_ID = B.OBJECT_ID)
   AND (B.PROCESS = C.PROCESS)
 ORDER BY 1, 2;
 
 --分页查询锁信息
select * from (select A.*,rownum rn from (SELECT A.OWNER, A.OBJECT_NAME, B.SESSION_ID, B.ORACLE_USERNAME, B.LOCKED_MODE, C.STATUS, C.SID, C.SERIAL# FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C WHERE (A.OBJECT_ID = B.OBJECT_ID) AND (B.PROCESS = C.PROCESS) ORDER BY 1, 2) A where rownum <5 ) where rn > 1;
 
  --查看引起锁的sql
 select l.session_id sid, 
       s.serial#, l.locked_mode, 
       l.oracle_username, s.user#, 
       l.os_user_name, s.machine, 
       s.terminal, a.sql_text, a.action 
  from v$sqlarea a, v$session s, v$locked_object l 
where l.session_id = '838' ;

--查看正在执行sql

SELECT b.sid oracleID,
       b.username 登录Oracle用户名,
       b.serial#,
       spid 操作系统ID,
       paddr,
       sql_text 正在执行的SQL,
       b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value;
   
--查看正在执行sql的发起者的发放程序
SELECT OSUSER 电脑登录身份,
       PROGRAM 发起请求的程序,
       USERNAME 登录系统的用户名,
       SCHEMANAME,
       B.Cpu_Time 花费cpu的时间,
       STATUS,
       B.SQL_TEXT 执行的sql
FROM V$SESSION A
LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
                   AND A.SQL_HASH_VALUE = B.HASH_VALUE
ORDER BY b.cpu_time DESC; 

--查询事务

select * from v$transaction;

--查找前十条性能差的sql

SELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS, 
COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea 
order BY disk_reads DESC )where ROWNUM<100; 

--查某段时间执行的sql

select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_ID,b.ELAPSED_TIME,b.LAST_LOAD_TIME,b.SQL_FULLTEXT
  from v$sqlarea b
where b.FIRST_LOAD_TIME between '2017-05-22/19:24:47' and
       '2017-05-22/23:32:47' order by b.FIRST_LOAD_TIME;

--表空间

--查询oralce表空间大小信息
SELECT D.TABLESPACE_NAME,  
       SPACE || 'M' "SUM_SPACE(M)",  
       SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",  
       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' 
          "USED_RATE(%)",  
       FREE_SPACE || 'M' "FREE_SPACE(M)" 
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_DATA_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE  
            FROM DBA_FREE_SPACE  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  
UNION ALL                                                           --如果有临时表空间
SELECT D.TABLESPACE_NAME,  
       SPACE || 'M' "SUM_SPACE(M)",  
       USED_SPACE || 'M' "USED_SPACE(M)",  
       ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",  
       NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" 
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_TEMP_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,  
                 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE  
            FROM V$TEMP_SPACE_HEADER  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  
ORDER BY 1;

--查询表占用空间大小(需补充分页)
select A.TABLE_NAME ,A.owner,A.num_rows,A.b_MB,A.TABLESPACE_NAME from 
(select TABLE_NAME ,owner,num_rows,round(num_rows*avg_row_len/1024/1024,5) b_MB,TABLESPACE_NAME  from ALL_tables) A where A.B_MB is not null order by b_MB desc

--查询索引占用空间大小(需补充分页)
SELECT owner,
　　segment_name,
　　SUM(bytes)/1024/1024,
    tablespace_name     
　　FROM dba_segments
　　--WHERE owner='MYUSER'
　　--And segment_name='MYSEG'
    --where segment_name = 'AA'
　　GROUP BY owner,segment_name,tablespace_name
　　ORDER BY 3 DESC;

--总空间大小
select T1.sum+T2.sum from 
(SELECT 
       sum(SPACE) as sum
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_DATA_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE  
            FROM DBA_FREE_SPACE  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ) T1,       --如果有临时表空间
 (SELECT 
       sum(SPACE) as sum
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_TEMP_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,  
                 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE  
            FROM V$TEMP_SPACE_HEADER  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ) T2
 
 --总空闲空间
select T1.sum+T2.sum from 
(SELECT
       sum(FREE_SPACE) as sum
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_DATA_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE  
            FROM DBA_FREE_SPACE  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ) T1,      --如果有临时表空间
 (SELECT 
       NVL (FREE_SPACE, 0) as sum
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_TEMP_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,  
                 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE  
            FROM V$TEMP_SPACE_HEADER  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  ) T2
 
 --查询表空间总数
select T1.count+T2.count from
(SELECT count(*) as count
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_DATA_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE  
            FROM DBA_FREE_SPACE  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  ) T1,      --如果有临时表空间
 (SELECT count(*) as count
  FROM (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
                 SUM (BLOCKS) BLOCKS  
            FROM DBA_TEMP_FILES  
        GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
                 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,  
                 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE  
            FROM V$TEMP_SPACE_HEADER  
        GROUP BY TABLESPACE_NAME) F  
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  ) T2
 
 --计算索引占用总空间
select sum(T1.space) from
(SELECT 
　　SUM(bytes)/1024/1024 as space   
　　FROM dba_segments
　　GROUP BY owner,segment_name,tablespace_name) T1

--计算表占用总空间
select sum(T1.space) from
(select 
A.b_MB as space
from 
(select TABLE_NAME ,owner,num_rows,round(num_rows*avg_row_len/1024/1024,5) b_MB  
from ALL_tables) A where A.B_MB is not null order by b_MB desc) T1