[收集]Oracle性能监控-相关语句-第一篇

 

 

Oracle性能监控脚本

 

http://www.2cto.com/database/201109/102881.html

1. 监控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
2. 回滚段的争用情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
3. 监控表空间的 I/O 比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
4. 监控文件系统的 I/O 比例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
5.在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
6. 监控 SGA 的命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
7. 监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
8. 监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
9. 显示所有数据库对象的类别和大小
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

12. 监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
13. 监控字典缓冲区
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
14. 找ORACLE字符集
select * from sys.props$ where name='NLS_CHARACTERSET';
15. 监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5时，参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时，参数需加大
16. 碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
17. 表、索引的存储情况检查
select segment_name,sum(bytes) space,count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;
 
 1. 检测数据库中的事件和等待
       SELECT event, total_waits, total_timeouts,time_waited, average_wait
       FROM v$system_event
2. 查询会话中的事件和等待时间
       select sid, event, total_waits,average_wait
       from v$session_event where sid=10;    
3. 查询等待进程
       SELECT sid, seq#, event, wait_time, state
       FROM v$session_wait;
4. 监控全局区的性能
       select * from v$sgastat;
5. 查询命中率
       select gethitratio
       from v$librarycache
       where namespace = 'SQL AREA';
6. 当前 sql 语句
       select sql_text, users_executing,
       executions, loads
       from v$sqlarea;
7. 查询高速缓存中的命中率
       select sum(pins) "Executions", sum(reloads) "Cache Misses",
       sum(reloads)/sum(pins)
       from v$librarycache;
8. 查询全局字典中的有效装载次数
       select namespace,pins,reloads,invalidations
       from v$librarycache;
9. 回滚段的争用情况

    select name, waits, gets, waits/gets "Ratio"
    from v$rollstat a, v$rollname b
    where a.usn = b.usn;
10. 监控表空间的 I/O 比例
   select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
    f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
    from v$filestat f, dba_data_files df
    where f.file# = df.file_id
    order by df.tablespace_name;
11. 监控文件系统的 I/O 比例
   select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
    a.status, a.bytes, b.phyrds, b.phywrts
    from v$datafile a, v$filestat b
    where a.file# = b.file#;
12. 在某个用户下找所有的索引
    select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
    from user_ind_columns, user_indexes
    where user_ind_columns.index_name = user_indexes.index_name
    and user_ind_columns.table_name = user_indexes.table_name
    order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
13. 监控 SGA 中字典缓冲区的命中率
    select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
    (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
    from v$rowcache
    where gets+getmisses <>0
    group by parameter, gets, getmisses;
14. 监控 SGA 中共享缓存区的命中率，应该小于 1%
    select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
    sum(reloads)/sum(pins) *100 libcache
    from v$librarycache;
    select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
    from v$librarycache;
15. 显示所有数据库对象的类别和大小
    select count(name) num_instances ,type ,sum(source_size) source_size ,
    sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
    sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
    from dba_object_size
    group by type order by 2;
16. 监控 SGA 中重做日志缓存区的命中率，应该小于 1%

    select name, gets, misses, immediate_gets, immediate_misses,
    Decode(gets,0,0,misses/gets*100) ratio1,
    Decode(immediate_gets+immediate_misses,0,0,
    immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
    FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
17. 监控内存和硬盘的排序比率，最好使它小于 .10 ，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
18. 监控字典缓冲区

select (sum(pins - reloads)) / sum(pins) "lib cache" from v$librarycache;
    select (sum(gets - getmisses - usage - fixed)) / sum(gets) "row cache" from v$rowcache;
select sum(pins) "executions", sum(reloads) "cache misses while executing" from v$librarycache;
后者除以前者 , 此比率小于 1%, 接近 0% 为好
select sum(gets) "dictionary gets",sum(getmisses) "dictionary cache get misses"
from v$rowcache
19. 找 ORACLE 字符集
   select * from sys.props$ where name='NLS_CHARACTERSET';
20. 监控 MTS
    select busy/(busy+idle) "shared servers busy" from v$dispatcher;
    此值大于 0.5 时，参数需加大
   select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
    select count(*) from v$dispatcher;
    select servers_highwater from v$mts;
    servers_highwater 接近 mts_max_servers 时，参数需加大
21. 碎片程度
   select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
    having count(tablespace_name)>10;
    alter tablespace name coalesce;
    alter table name deallocate unused;
    create or replace view ts_blocks_v as
    select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
    union all
    select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
    select * from ts_blocks_v;
    select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
    group by tablespace_name;
    查看碎片程度高的表
   SELECT segment_name table_name , COUNT(*) extents
    FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
    HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
22. 表、索引的存储情况检查
    select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
    select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
    group by segment_name;
23 、找使用 CPU 多的用户 session
    12 是 cpu used by this session
    select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,oSUSEr,value/60/100 value
    from v$session a,v$process b,v$sesstat c
    where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;

 

作者“anyonefeng”

 

 

ORACLE_日常监控

http://www.doc88.com/p-672405292216.html

 

 

oracle性能监控

http://blog.csdn.net/ahngzw77/article/details/7709215

希望整理一个监控oracle性能的常用sql集锦，希望大家不断更加：

1. 监控事例的等待

     select event,sum(decode(wait_Time,0,0,1)) "Prev", 
   sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" 
     from v$session_Wait 
     group by event order by 4;

2. 回滚段的争用情况

     select name, waits, gets, waits/gets "Ratio" 
     from v$rollstat a, v$rollname b 
     where a.usn = b.usn; 

3. 监控表空间的 I/O 比例

     select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
     f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
     from v$filestat f, dba_data_files df
     where f.file# = df.file_id
     order by df.tablespace_name;

4. 监控文件系统的 I/O 比例

     select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name", 
     a.status, a.bytes, b.phyrds, b.phywrts 
     from v$datafile a, v$filestat b 
     where a.file# = b.file#; 

5.在某个用户下找所有的索引

     select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
     from user_ind_columns, user_indexes
     where user_ind_columns.index_name = user_indexes.index_name
     and user_ind_columns.table_name = user_indexes.table_name 
     order by user_indexes.table_type, user_indexes.table_name,
     user_indexes.index_name, column_position;

6. 监控 SGA 的命中率

     select a.value + b.value "logical_reads", c.value "phys_reads",
     round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" 
     from v$sysstat a, v$sysstat b, v$sysstat c
     where a.statistic# = 38 and b.statistic# = 39 
     and c.statistic# = 40; 

7. 监控 SGA 中字典缓冲区的命中率

     select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
     (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
     from v$rowcache 
     where gets+getmisses <>0
     group by parameter, gets, getmisses; 

8. 监控 SGA 中共享缓存区的命中率，应该小于1%

     select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
     sum(reloads)/sum(pins) *100 libcache
     from v$librarycache;

     select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" 
     from v$librarycache;

9. 显示所有数据库对象的类别和大小

     select count(name) num_instances ,type ,sum(source_size) source_size ,
     sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
     sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required 
     from dba_object_size 
     group by type order by 2;

10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%

     SELECT name, gets, misses, immediate_gets, immediate_misses,
     Decode(gets,0,0,misses/gets*100) ratio1,
     Decode(immediate_gets+immediate_misses,0,0,
     immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
     FROM v$latch WHERE name IN ('redo allocation', 'redo copy'); 

11.   监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size 

     SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)'); 


12. 监控当前数据库谁在运行什么SQL语句

     SELECT osuser, username, sql_text from v$session a, v$sqltext b
     where a.sql_address =b.address order by address, piece;

13. 监控字典缓冲区

     SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
     SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
     SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; 

     后者除以前者,此比率小于1%,接近0%为好。

     SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
     FROM V$ROWCACHE

14. 找ORACLE字符集

     select * from sys.props$ where name='NLS_CHARACTERSET'; 

15. 监控 MTS

     select busy/(busy+idle) "shared servers busy" from v$dispatcher;

     此值大于0.5时，参数需加大

     select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
     select count(*) from v$dispatcher;
     select servers_highwater from v$mts;

     servers_highwater接近mts_max_servers时，参数需加大

16. 碎片程度

     select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name 
     having count(tablespace_name)>10;

     alter tablespace name coalesce;
     alter table name deallocate unused;

     create or replace view ts_blocks_v as
     select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
     union all
     select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;

     select * from ts_blocks_v;

     select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space 
     group by tablespace_name;

     查看碎片程度高的表

     SELECT segment_name table_name , COUNT(*) extents
     FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
     HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);

17. 表、索引的存储情况检查

     select segment_name,sum(bytes),count(*) ext_quan from dba_extents where 
     tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;

     select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
     group by segment_name;

18、找使用CPU多的用户session

     12是cpu used by this session

     select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
     from v$session a,v$process b,v$sesstat c
     where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;

一、数据库构架体系 

1、表空间的监控是一个重要的任务，我们必须时刻关心表空间的设置，是否满足现在应用的需求，以下的语句可以查询到表空间的详细信息 
SELECT TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,          
MAX_EXTENTS,PCT_INCREASE,MIN_EXTLEN,STATUS,CONTENTS,LOGGING,
EXTENT_MANAGEMENT,   -- Columns not available in v8.0.x
ALLOCATION_TYPE,     -- Remove these columns if running
PLUGGED_IN,           -- against a v8.0.x database
SEGMENT_SPACE_MANAGEMENT --use only in v9.2.x or later 
FROM
DBA_TABLESPACES   ORDER BY TABLESPACE_NAME;

2、对于某些数据文件没有设置为自动扩展的表空间来说，如果表空间满了，就将意味着数据库可能会因为没有空间而停止下来。
监控表空间，最主要的就是监控剩余空间的大小或者是使用率。以下是监控表空间使用率与剩余空间大小的语句 


SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",   ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "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   --if have tempfile   SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,   USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",   NVL(FREE_SPACE,0) "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(+)  


3、除了监控表空间的剩余空间，有时候我们也有必要了解一下该表空间是否具有自动扩展空间的能力，虽然我们建议在生产系统中预先分配空间。以下语句将完成这一功能 


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   ORDER BY TABLESPACE_NAME,FILE_NAME  


4、我相信使用字典管理的表空间的也不少吧，因为字典管理的表空间中，每个表的下一个区间的大小是不可以预料的，所以我们必须监控那些表在字典管理的表空间中的下一个区间的分配将会引起性能问题或由于是非扩展的表空间而导致系统停止。以下语句检查那些表的扩展将引起表空间的扩展。 


SELECT A.OWNER,A.TABLE_NAME,A.NEXT_EXTENT,A.TABLESPACE_NAME   FROM ALL_TABLES A,   (SELECT TABLESPACE_NAME, MAX(BYTES) BIG_CHUNK   FROM DBA_FREE_SPACE   GROUP BY TABLESPACE_NAME) F   WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME   AND A.NEXT_EXTENT > F.BIG_CHUNK  


5、段的占用空间与区间数也是很需要注意的一个问题，如果一个段的占用空间太大，或者跨越太多的区间（在字典管理的表空间中，将有严重的性能影响），如果段没有可以再分配的区间，将导致数据库错误。所以，段的大小与区间监控也是一个很重要的工作 


SELECT S.OWNER,S.SEGMENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME,   ROUND(BYTES/(1024*1024),2) "USED_SPACE(M)",   EXTENTS USED_EXTENTS,S.MAX_EXTENTS,S.BLOCKS ALLOCATED_BLOCKS,   S.BLOCKS USED_BOLCKS,S.PCT_INCREASE,S.NEXT_EXTENT/1024 "NEXT_EXTENT(K)"   FROM DBA_SEGMENTS S   WHERE S.OWNER NOT IN ('SYS','SYSTEM')   ORDER BY Used_Extents DESC  


6、对象的空间分配与空间利用情况，除了从各个方面的分析，如分析表，查询rowid等方法外，其实oracle提供了一个查询空间的包dbms_space，如果我们稍封装一下，将是非常好用的一个东西。 

   CREATE OR REPLACE PROCEDURE show_space         (p_segname in varchar2,           p_type in varchar2 default 'TABLE' ,           p_owner in varchar2 default user)   AS       v_segname varchar2(100);       v_type varchar2(10);       l_free_blks number;       l_total_blocks number;       l_total_bytes number;       l_unused_blocks number;       l_unused_bytes number;       l_LastUsedExtFileId number;       l_LastUsedExtBlockId number;       l_LAST_USED_BLOCK number;       PROCEDURE   p( p_label in varchar2, p_num in number )       IS       BEGIN       dbms_output.put_line( rpad(p_label,40,'.')|| p_num );       END;   BEGIN       v_segname := upper(p_segname);       v_type := p_type;       if (p_type = 'i' or p_type = 'I') then         v_type := 'INDEX';       end if;       if (p_type = 't' or p_type = 'T') then         v_type := 'TABLE';       end if;       if (p_type = 'c' or p_type = 'C') then         v_type := 'CLUSTER';       end if;       --以下部分不能用于ASSM       dbms_space.free_blocks       ( segment_owner => p_owner,       segment_name => v_segname,       segment_type => v_type,       freelist_group_id => 0,       free_blks => l_free_blks );       --以上部分不能用于ASSM       dbms_space.unused_space       ( segment_owner => p_owner,       segment_name => v_segname,       segment_type => v_type,       total_blocks => l_total_blocks,       total_bytes => l_total_bytes,       unused_blocks => l_unused_blocks,       unused_bytes => l_unused_bytes,       LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,       LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,       LAST_USED_BLOCK => l_LAST_USED_BLOCK );       --显示结果       p( 'Free Blocks', l_free_blks );       p( 'Total Blocks', l_total_blocks );       p( 'Total Bytes', l_total_bytes );       p( 'Unused Blocks', l_unused_blocks );       p( 'Unused Bytes', l_unused_bytes );       p( 'Last Used Ext FileId', l_LastUsedExtFileId );       p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );       p( 'Last Used Block', l_LAST_USED_BLOCK );   END;  

执行结果将如下所示 

   SQL> set serveroutput on;   SQL> exec show_space('test');   Free Blocks.............................1   Total Blocks............................8   Total Bytes.............................65536   Unused Blocks...........................6   Unused Bytes............................49152   Last Used Ext FileId....................1   Last Used Ext BlockId...................48521   Last Used Block.........................2   PL/SQL procedure successfully completed  


7、数据库的常规参数我就不说了，除了V$parameter中的常规参数外，ORACLE还有大量的隐含参数，下面的语句就可以查询到数据库的所有隐含参数以及其值与参数的描述。 

   SELECT NAME   ,VALUE   ,decode(isdefault, 'TRUE','Y','N') as "Default"   ,decode(ISEM,'TRUE','Y','N') as SesMod   ,decode(ISYM,'IMMEDIATE', 'I',   'DEFERRED', 'D',   'FALSE', 'N') as SysMod   ,decode(IMOD,'MODIFIED','U',   'SYS_MODIFIED','S','N') as Modified   ,decode(IADJ,'TRUE','Y','N') as Adjusted   ,description   FROM ( --GV$SYSTEM_PARAMETER   SELECT x.inst_id as instance   ,x.indx+1   ,ksppinm as NAME     ,ksppity   ,ksppstvl as VALUE     ,ksppstdf as isdefault   ,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM   ,decode(bitand(ksppiflg/65536,3),   1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM   ,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD   ,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ   ,ksppdesc as DESCRIPTION     FROM x$ksppi x   ,x$ksppsv y   WHERE x.indx = y.indx   AND substr(ksppinm,1,1) = '_'   AND x.inst_id = USERENV('Instance')   )   ORDER BY NAME  


8、数据库的索引如果有比较频繁的Delete操作，将可能导致索引产生很多碎片，所以，在有的时候，需要对所有的索引重新REBUILD，以便合并索引块，减少碎片，提高查询速度。 

   SQL> set heading off   SQL> set feedback off   SQL> spool d:\index.sql   SQL> SELECT 'alter index ' || index_name || ' rebuild '   ||'tablespace INDEXES storage(initial 256K next 256K pctincrease 0);'   FROM all_indexes   WHERE ( tablespace_name != 'INDEXES'   OR next_extent != ( 256 * 1024 )   )   AND owner = USER   SQL>spool off  

这个时候，我们打开spool出来的文件，就可以直接运行了。 

9、表的主键是必要的，没有主键的表可以说是不符合设计规范的，所以我们需要监控表是否有主键 


SELECT table_name   FROM all_tables   WHERE owner = USER   MINUS   SELECT table_name   FROM all_constraints   WHERE owner = USER   AND constraint_type = 'P'  


二、性能监控 

1、数据缓冲区的命中率已经不是性能调整中的主要问题了，但是，过低的命中率肯定是不可以的，在任何情况下，我们必须保证有一个大的data buffer和一个高的命中率。 
这个语句可以获得整体的数据缓冲命中率，越高越好 


   SELECT a.VALUE + b.VALUE logical_reads,   c.VALUE phys_reads,   round(100*(1-c.value/(a.value+b.value)),4) hit_ratio   FROM v$sysstat a,v$sysstat b,v$sysstat c   WHERE a.NAME='db block gets'   AND b.NAME='consistent gets'   AND c.NAME='physical reads'  


2、库缓冲说明了SQL语句的重载率，当然，一个SQL语句应当被执行的越多越好，如果重载率比较高，就考虑增加共享池大小或者是提高Bind变量的使用 
以下语句查询了Sql语句的重载率，越低越好 


SELECT SUM(pins) total_pins,SUM(reloads) total_reloads,   SUM(reloads)/SUM(pins)*100 libcache_reload_ratio   FROM   v$librarycache  


3、用户锁，数据库的锁有的时候是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。 
这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。 
可以通过alter system kill session ‘sid,serial#’来杀掉会话 

   SELECT s.username,   decode(l.type,'TM','TABLE LOCK',                 'TX','ROW LOCK',                 NULL) LOCK_LEVEL,   o.owner,o.object_name,o.object_type,   s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser   FROM v$session s,v$lock l,dba_objects o   WHERE l.sid = s.sid   AND l.id1 = o.object_id(+)   AND s.username is NOT NULL  


4、锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待 
以下的语句可以查询到谁锁了表，而谁在等待。 

   SELECT lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,         o.owner,o.object_name,o.object_type,s.sid,s.serial#   FROM v$locked_object l,dba_objects o,v$session s   WHERE l.object_id=o.object_id   AND l.session_id=s.sid   ORDER BY o.object_id,xidusn DESC  


以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN 

5、如果发生了事务或锁，想知道哪些回滚段正在被使用吗？其实通过事务表，我们可以详细的查询到事务与回滚段之间的关系。同时，如果关联会话表，我们则可以知道是哪个会话发动了这个事务。 

   SELECT s.USERNAME,s.SID,s.SERIAL#,t.UBAFIL "UBA filenum",   t.UBABLK "UBA Block number",t.USED_UBLK "Number os undo Blocks Used",   t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSN RollID,r.NAME RollName   FROM v$session s,v$transaction t,v$rollname r   WHERE s.SADDR=t.SES_ADDR   AND t.XIDUSN=r.usn  

6、想知道现在哪个用户正在利用临时段吗？这个语句将告诉你哪个用户正在利用临时段。 

   SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,   a.username, a.osuser, a.status,c.sql_text   FROM v$session a,v$sort_usage b, v$sql c   WHERE a.saddr = b.session_addr   AND a.sql_address = c.address(+)   ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;  


7、如果利用会话跟踪或者是想查看某个会话的跟踪文件，那么查询到OS上的进程或线程号是非常重要的，因为文件的令名中，就包含这个信息，以下的语句可以查询到进程或线程号，由此就可以找到对应的文件。 

   SELECT p1.value||'\'||p2.value||'_ora_'||p.spid filename       FROM           v$process p,           v$session s,           v$parameter p1,           v$parameter p2       WHERE p1.name = 'user_dump_dest'       AND p2.name = 'db_name'       AND p.addr = s.paddr       AND s.audsid = USERENV ('SESSIONID');  


8、在ORACLE 9i中，可以监控索引的使用，如果没有使用到的索引，完全可以删除掉，减少DML操作时的操作。 
以下就是开始索引监控与停止索引监控的脚本 


   set heading off   set echo off   set feedback off   set pages 10000   spool start_index_monitor.sql     SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'   FROM dba_indexes   WHERE owner = USER;     spool off   set heading on   set echo on   set feedback on   ------------------------------------------------   set heading off   set echo off   set feedback off   set pages 10000   spool stop_index_monitor.sql     SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'   FROM dba_indexes   WHERE owner = USER;     spool off   set heading on   set echo on   set feedback on

 

 

[ORACLE]监控oracle性能  

http://blog.163.com/zh_nicole/blog/static/140204526201041210100780/

1. 监控事例的等待

select event,sum(decode(wait_Time,0,0,1)) "Prev", sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" from v$session_Wait group by event order by 4;

2. 回滚段的争用情况

select name, waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn;

3. 监控表空间的I/O 比例

select df.tablespace_name name,df.file_name "file",f.phyrds pyr, f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, dba_data_files df where f.file# = df.file_id order by df.tablespace_name;

4. 监控文件系统的 I/O 比例

select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name", a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#;

5.在某个用户下找所有的索引

select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name = user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position;

6. 监控 SGA 的命中率

select a.value + b.value "logical_reads", c.value "phys_reads", round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;

7. 监控 SGA 中字典缓冲区的命中率

select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio", (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" from v$rowcache where gets+getmisses <>0 group by parameter, gets, getmisses;

8. 监控 SGA 中共享缓存区的命中率，应该小于1%

select sum(pins) "Total Pins", sum(reloads) "Total Reloads", sum(reloads)/sum(pins) *100 libcache from v$librarycache; select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache;

9. 显示所有数据库对象的类别和大小

select count(name) num_instances ,type ,sum(source_size) source_size , sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size, sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size group by type order by 2;

10. 监控 SGA 中重做日志缓存区的命中率，应该小于1%

SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size

SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

12. 监控当前数据库谁在运行什么SQL语句

SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;

13. 监控字典缓冲区

SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE; SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE; SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE; 后者除以前者,此比率小于1%,接近0%为好。 SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE

14. 找ORACLE字符集 select * from sys.props$ where name='NLS_CHARACTERSET';

15. 监控 MTS

select busy/(busy+idle) "shared servers busy" from v$dispatcher; 此值大于0.5时，参数需加大 select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher'; select count(*) from v$dispatcher; select servers_highwater from v$mts; servers_highwater接近mts_max_servers时，参数需加大

16. 碎片程度

select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>10; alter tablespace name coalesce; alter table name deallocate unused; create or replace view ts_blocks_v as select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space union all select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents; select * from ts_blocks_v; select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name;

查看碎片程度高的表

SELECT segment_name table_name , COUNT(*) extents FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);

17. 表、索引的存储情况检查

select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name; select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner' group by segment_name;

18、找使用CPU多的用户session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;