DBA日常维护信息——收集dba_hist_sysmetric_summary

1.创建表htbk.performance用于收集信息

create table htbk.performance as 
select max(end_time) time, 
snap_id, 
sysdate insertdate,    
trunc(sum(case metric_name when                 'Host CPU Utilization (%)' then average end),2) Host_CPU_util,
trunc(sum(case metric_name when                          'Current OS Load' then average end),2) OS_LOAD,  
trunc(sum(case metric_name when                 'Session Count' then average end),2) sessions,
trunc(sum(case metric_name when                    'Database Time Per Sec' then average end),2) Database_Time_Per_Sec,
trunc(sum(case metric_name when                 'User Transaction Per Sec' then average end),2) User_Txn_Per_Sec,
trunc(sum(case metric_name when                       'Executions Per Sec' then average end),2) Executions_ptx,
trunc(sum(case metric_name when                    'Response Time Per Txn' then average end),2) Response_Time_ptx,
trunc(sum(case metric_name when                   'Redo Generated Per Sec' then average end)/1024/1024,2) Redo_MBps,
trunc(sum(case metric_name when                      'Redo Writes Per Sec' then average end),2) PhyWts_redo_IOPS,
trunc(sum(case metric_name when  'Physical Read Total IO Requests Per Sec' then average end),2) PhyRead_IOPS,
trunc(sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then average end),2) Phywrite_IOPS,
trunc(sum(case metric_name when       'Physical Write Total Bytes Per Sec' then average end)/1024/1024,2) Phywrite_Tot_MBps,
trunc(sum(case metric_name when      'Physical Read Total Bytes Per Sec' then average end)/1024/1024,2) PhyRead_Tot_MBps,
trunc(sum(case metric_name when                           'Logons Per Sec' then average end),2) Logons_PS,
trunc(sum(case metric_name when                     'User Commits Per Sec' then average end),2) User_Commit_Per_Sec,
trunc(sum(case metric_name when                   'User Rollbacks Per Sec' then average end),2) User_Rollback_Per_Sec,
trunc(sum(case metric_name when                    'Logical Reads Per Txn' then average end),2) Logical_Rds_PTxn,
trunc(sum(case metric_name when                 'Hard Parse Count Per Sec' then average end),2) HardParse_Per_Sec,
trunc(sum(case metric_name when           'Network Traffic Volume Per Sec' then average end/1024/1024),2) Network_Mbps,
trunc(sum(case metric_name when                    'Enqueue Waits Per Sec' then average end),2) Enq_wait_Per_Sec,
trunc(sum(case metric_name when                 'DB Block Changes Per Sec' then average end),2) BLOCK_CHG_Per_Sec,
trunc(sum(case metric_name when                 'Leaf Node Splits Per Sec' then average end),2) Leaf_Node_Splits_ps,
trunc(sum(case metric_name when                          'Process Limit %' then average end),2) Process_Limit,
trunc(sum(case metric_name when                          'Session Limit %' then average end),2) Session_Limit,
trunc(sum(case metric_name when                       'Shared Pool Free %' then average end),2) Shared_Pool_Free_PCT,            
trunc(sum(case metric_name when                   'Buffer Cache Hit Ratio' then average end),2) Buffer_CACHE_HIT_RAT,
trunc(sum(case metric_name when                       'Memory Sorts Ratio' then average end),2) Memory_Sorts_Ratio,
trunc(sum(case metric_name when                'Redo Allocation Hit Ratio' then average end),2) Redo_Allocation_Hit_Rat,
trunc(sum(case metric_name when                  'Library Cache Hit Ratio' then average end),2) Library_Cache_Hit_Rat,
trunc(sum(case metric_name when                         'Soft Parse Ratio' then average end),2) Soft_Parse_Rat, 
trunc(sum(case metric_name when                   'Cursor Cache Hit Ratio' then average end),2) Cursor_Cache_Hit_Rat,
trunc(sum(case metric_name when                   'Leaf Node Splits Per Sec' then average end),2) Leaf_Node_Splits
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id
--Database Time Per Sec对应的Database_Time值的单位是百分一秒/每秒
--select metric_name,metric_unit from dba_hist_sysmetric_summary where metric_name='Database Time Per Sec'
--Database Time Per Sec   CentiSeconds Per Second


2.创建JOB每7天插入一次htbk.performance

grant select on dba_hist_sysmetric_summary to system
grant all on htbk.performance to system

declare  
job number;     
begin
sys.dbms_job.submit(job,'htbk_performance;',sysdate,'sysdate+7');
end;



CREATE OR REPLACE procedure SYSTEM.htbk_performance 
as
begin
     insert into  htbk.performance 
select * from (
select max(end_time) time, 
snap_id, 
sysdate insertdate,    
trunc(sum(case metric_name when                 'Host CPU Utilization (%)' then average end),2) Host_CPU_util,
trunc(sum(case metric_name when                          'Current OS Load' then average end),2) OS_LOAD,  
trunc(sum(case metric_name when                 'Session Count' then average end),2) sessions,
trunc(sum(case metric_name when                    'Database Time Per Sec' then average end),2) Database_Time_Per_Sec,
trunc(sum(case metric_name when                 'User Transaction Per Sec' then average end),2) User_Txn_Per_Sec,
trunc(sum(case metric_name when                       'Executions Per Sec' then average end),2) Executions_ptx,
trunc(sum(case metric_name when                    'Response Time Per Txn' then average end),2) Response_Time_ptx,
trunc(sum(case metric_name when                   'Redo Generated Per Sec' then average end)/1024/1024,2) Redo_MBps,
trunc(sum(case metric_name when                      'Redo Writes Per Sec' then average end),2) PhyWts_redo_IOPS,
trunc(sum(case metric_name when  'Physical Read Total IO Requests Per Sec' then average end),2) PhyRead_IOPS,
trunc(sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then average end),2) Phywrite_IOPS,
trunc(sum(case metric_name when       'Physical Write Total Bytes Per Sec' then average end)/1024/1024,2) Phywrite_Tot_MBps,
trunc(sum(case metric_name when      'Physical Read Total Bytes Per Sec' then average end)/1024/1024,2) PhyRead_Tot_MBps,
trunc(sum(case metric_name when                           'Logons Per Sec' then average end),2) Logons_PS,
trunc(sum(case metric_name when                     'User Commits Per Sec' then average end),2) User_Commit_Per_Sec,
trunc(sum(case metric_name when                   'User Rollbacks Per Sec' then average end),2) User_Rollback_Per_Sec,
trunc(sum(case metric_name when                    'Logical Reads Per Txn' then average end),2) Logical_Rds_PTxn,
trunc(sum(case metric_name when                 'Hard Parse Count Per Sec' then average end),2) HardParse_Per_Sec,
trunc(sum(case metric_name when           'Network Traffic Volume Per Sec' then average end/1024/1024),2) Network_Mbps,
trunc(sum(case metric_name when                    'Enqueue Waits Per Sec' then average end),2) Enq_wait_Per_Sec,
trunc(sum(case metric_name when                 'DB Block Changes Per Sec' then average end),2) BLOCK_CHG_Per_Sec,
trunc(sum(case metric_name when                 'Leaf Node Splits Per Sec' then average end),2) Leaf_Node_Splits_ps,
trunc(sum(case metric_name when                          'Process Limit %' then average end),2) Process_Limit,
trunc(sum(case metric_name when                          'Session Limit %' then average end),2) Session_Limit,
trunc(sum(case metric_name when                       'Shared Pool Free %' then average end),2) Shared_Pool_Free_PCT,            
trunc(sum(case metric_name when                   'Buffer Cache Hit Ratio' then average end),2) Buffer_CACHE_HIT_RAT,
trunc(sum(case metric_name when                       'Memory Sorts Ratio' then average end),2) Memory_Sorts_Ratio,
trunc(sum(case metric_name when                'Redo Allocation Hit Ratio' then average end),2) Redo_Allocation_Hit_Rat,
trunc(sum(case metric_name when                  'Library Cache Hit Ratio' then average end),2) Library_Cache_Hit_Rat,
trunc(sum(case metric_name when                         'Soft Parse Ratio' then average end),2) Soft_Parse_Rat, 
trunc(sum(case metric_name when                   'Cursor Cache Hit Ratio' then average end),2) Cursor_Cache_Hit_Rat,
trunc(sum(case metric_name when                   'Leaf Node Splits Per Sec' then average end),2) Leaf_Node_Splits
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id) where snap_id not in (select snap_id from  htbk.performance);
commit;
end;

RAC的话使用如下
CREATE OR REPLACE procedure DBA_MONITOR.pro_performance 
as
begin
     insert into  PERFORMANCE_DETAILS_RAC
select * from (
select max(end_time) time, 
INSTANCE_NUMBER,
snap_id, 
sysdate insertdate,    
trunc(sum(case metric_name when                 'Host CPU Utilization (%)' then average end),2) Host_CPU_util,
trunc(sum(case metric_name when                          'Current OS Load' then average end),2) OS_LOAD,  
trunc(sum(case metric_name when                 'Session Count' then average end),2) sessions,
trunc(sum(case metric_name when                    'Database Time Per Sec' then average end),2) Database_Time_per_sec,
trunc(sum(case metric_name when                 'User Transaction Per Sec' then average end),2) User_Txn_Per_Sec,
trunc(sum(case metric_name when                       'Executions Per Sec' then average end),2) Executions_ptx,
trunc(sum(case metric_name when                    'Response Time Per Txn' then average end),2) Response_Time_ptx,
trunc(sum(case metric_name when                   'Redo Generated Per Sec' then average end)/1024/1024,2) Redo_MBps,
trunc(sum(case metric_name when                      'Redo Writes Per Sec' then average end),2) PhyWts_redo_IOPS,
trunc(sum(case metric_name when  'Physical Read Total IO Requests Per Sec' then average end),2) PhyRead_IOPS,
trunc(sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then average end),2) Phywrite_IOPS,
trunc(sum(case metric_name when       'Physical Write Total Bytes Per Sec' then average end)/1024/1024,2) Phywrite_Tot_MBps,
trunc(sum(case metric_name when      'Physical Read Total Bytes Per Sec' then average end)/1024/1024,2) PhyRead_Tot_MBps,
trunc(sum(case metric_name when                           'Logons Per Sec' then average end),2) Logons_PS,
trunc(sum(case metric_name when                     'User Commits Per Sec' then average end),2) User_Commit_Per_Sec,
trunc(sum(case metric_name when                   'User Rollbacks Per Sec' then average end),2) User_Rollback_Per_Sec,
trunc(sum(case metric_name when                    'Logical Reads Per Txn' then average end),2) Logical_Rds_PTxn,
trunc(sum(case metric_name when                 'Hard Parse Count Per Sec' then average end),2) HardParse_Per_Sec,
trunc(sum(case metric_name when           'Network Traffic Volume Per Sec' then average end/1024/1024),2) Network_Mbps,
trunc(sum(case metric_name when                    'Enqueue Waits Per Sec' then average end),2) Enq_wait_Per_Sec,
trunc(sum(case metric_name when                 'DB Block Changes Per Sec' then average end),2) BLOCK_CHG_Per_Sec,
trunc(sum(case metric_name when                 'Leaf Node Splits Per Sec' then average end),2) Leaf_Node_Splits_ps,
trunc(sum(case metric_name when                          'Process Limit %' then average end),2) Process_Limit,
trunc(sum(case metric_name when                          'Session Limit %' then average end),2) Session_Limit,
trunc(sum(case metric_name when                       'Shared Pool Free %' then average end),2) Shared_Pool_Free_PCT,            
trunc(sum(case metric_name when                   'Buffer Cache Hit Ratio' then average end),2) Buffer_CACHE_HIT_RAT,
trunc(sum(case metric_name when                       'Memory Sorts Ratio' then average end),2) Memory_Sorts_Ratio,
trunc(sum(case metric_name when                'Redo Allocation Hit Ratio' then average end),2) Redo_Allocation_Hit_Rat,
trunc(sum(case metric_name when                  'Library Cache Hit Ratio' then average end),2) Library_Cache_Hit_Rat,
trunc(sum(case metric_name when                         'Soft Parse Ratio' then average end),2) Soft_Parse_Rat, 
trunc(sum(case metric_name when                   'Cursor Cache Hit Ratio' then average end),2) Cursor_Cache_Hit_Rat,
trunc(sum(case metric_name when                   'Leaf Node Splits Per Sec' then average end),2) Leaf_Node_Splits
from dba_hist_sysmetric_summary
group by INSTANCE_NUMBER,snap_id
order by snap_id) where snap_id not in (select snap_id from  PERFORMANCE_DETAILS_RAC);
commit;
end;
最后对 htbk.performance.snap_id创建主键




这样以后直接查询htbk.performance表就可以得出数据库的一些性能报表
select to_char(time,'yyyy-mm-dd HH24:-mi'),snap_id,OS_LOAD,sessions,
database_time_per_sec,User_Txn_Per_Sec,
RESPONSE_TIME_PTX,PhyRead_IOPS+Phywrite_IOPS IOPS,Phywrite_Tot_MBps+PhyRead_Tot_MBps IO_MBPS
from htbk.performance order by 2
如上SQL查询结果为7列，以这7列为基准，以第一列为横轴，三列至七列为纵轴分别可以得到负载曲线图、session并发曲线图、dbtime曲线图、事务量曲线图、事务等待时间曲线图、IOPS曲线图、IO吞吐量曲线图

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2135337/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2135337/