[ORACLE]管理方面的脚本收集

-----------------------------cryking原创------------------------------
-----------------------转载请注明出处,谢谢!------------------------

1.查询awr相关的视图名称

select table_name
  from dba_tables t
 where table_name like 'wrh$%'
       and not exists (select 'x'
          from dba_tab_columns c
         where c.column_name = 'snap_id'
               and c.table_name = t.table_name);

2. cpu耗时查询

with aasstat as (
           select
                 decode(n.wait_class,'user i/o','user i/o',
                                     'commit','commit',
                                     'wait')                               class,
                 sum(round(m.time_waited/m.intsize_csec,3))                aas,
                 begin_time ,
                 end_time
           from  v$waitclassmetric  m,
                 v$system_wait_class n
           where m.wait_class_id=n.wait_class_id
             and n.wait_class != 'idle'
           group by  decode(n.wait_class,'user i/o','user i/o', 'commit','commit', 'wait'), begin_time, end_time
          union
             select 'cpu_ora_consumed'                                     class,
                    round(value/100,3)                                     aas,
                 begin_time ,
                 end_time
             from v$sysmetric
             where metric_name='cpu usage per sec'
               and group_id=2
          union
            select 'cpu_os'                                                class ,
                    round((prcnt.busy*parameter.cpu_count)/100,3)          aas,
                 begin_time ,
                 end_time
            from
              ( select value busy, begin_time,end_time from v$sysmetric where metric_name='host cpu utilization (%)' and group_id=2 ) prcnt,
              ( select value cpu_count from v$parameter where name='cpu_count' )  parameter
          union
             select
               'cpu_ora_demand'                                            class,
               nvl(round( sum(decode(session_state,'on cpu',1,0))/60,2),0) aas,
               cast(min(sample_time) as date) begin_time ,
               cast(max(sample_time) as date) end_time
             from v$active_session_history ash
              where sample_time >= (select begin_time from v$sysmetric where metric_name='cpu usage per sec' and group_id=2 )
               and sample_time < (select end_time from v$sysmetric where metric_name='cpu usage per sec' and group_id=2 )
)
select
       to_char(begin_time,'hh:mi:ss') begin_time,
       to_char(end_time,'hh:mi:ss') end_time,
       ( decode(sign(cpu_os-cpu_ora_consumed), -1, 0, (cpu_os - cpu_ora_consumed )) +
       cpu_ora_consumed +
        decode(sign(cpu_ora_demand-cpu_ora_consumed), -1, 0, (cpu_ora_demand - cpu_ora_consumed ))) cpu_total,
       decode(sign(cpu_os-cpu_ora_consumed), -1, 0, (cpu_os - cpu_ora_consumed )) cpu_os,
       cpu_ora_consumed cpu_ora,
       decode(sign(cpu_ora_demand-cpu_ora_consumed), -1, 0, (cpu_ora_demand - cpu_ora_consumed )) cpu_ora_wait,
       commit,
       readio,
       wait
from (
select
       min(begin_time) begin_time,
       max(end_time) end_time,
       sum(decode(class,'cpu_ora_consumed',aas,0)) cpu_ora_consumed,
       sum(decode(class,'cpu_ora_demand'  ,aas,0)) cpu_ora_demand,
       sum(decode(class,'cpu_os'          ,aas,0)) cpu_os,
       sum(decode(class,'commit'          ,aas,0)) commit,
       sum(decode(class,'user i/o'        ,aas,0)) readio,
       sum(decode(class,'wait'            ,aas,0)) wait
from aasstat)

3. 等待事件信息

select 
    sid sw_sid, 
    case when state != 'waiting' then 'working'
         else 'waiting'
    end as state, 
    case when state != 'waiting' then 'on cpu / runqueue'
         else event
    end as sw_event, 
    seq#, 
    seconds_in_wait sec_in_wait, 
    case state when 'waiting' then nvl2(p1text,p1text||'= ',null)||case when p1 < 536870912 then to_char(p1) else '0x'||rawtohex(p1raw) end else null end sw_p1,
    case state when 'waiting' then nvl2(p2text,p2text||'= ',null)||case when p2 < 536870912 then to_char(p2) else '0x'||rawtohex(p2raw) end else null end sw_p2,
    case state when 'waiting' then nvl2(p3text,p3text||'= ',null)||case when p3 < 536870912 then to_char(p3) else '0x'||rawtohex(p3raw) end else null end sw_p3,
    case state when 'waiting' then 
        case 
            when event like 'cursor:%' then
                '0x'||trim(to_char(p1, 'xxxxxxxxxxxxxxxx'))
                    when (event like 'enq%' or event = 'dfs lock handle') and state = 'waiting' then 
                '0x'||trim(to_char(p1, 'xxxxxxxxxxxxxxxx'))||': '||
                chr(bitand(p1, -16777216)/16777215)||
                chr(bitand(p1,16711680)/65535)||
                ' mode '||bitand(p1, power(2,14)-1)
            when event like 'latch%' and state = 'waiting' then 
                  '0x'||trim(to_char(p1, 'xxxxxxxxxxxxxxxx'))||': '||(
                        select name||'[par' 
                            from v$latch_parent 
                            where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'x'))))
                        union all
                        select name||'[c'||child#||']' 
                            from v$latch_children 
                            where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'x'))))
                  )
            when event like 'library cache pin' then
                  '0x'||rawtohex(p1raw)
        else null end 
    else null end as sw_p1transl
from 
    v$session_wait 
order by
    state,
    sw_event,
    p1,
    p2,
    p3;

4. 查询当前监听的连接信息

select host_short || '.' || sid || '=
    (description =
    (address = (protocol = tcp)(host = ' || hostname || ')(port = ' || port || '))
    (connect_data =
      (server = dedicated)
      ' || case
         when instr(service_name, 'xdb') > 0 then
          null
         else
          '(service_name = ' || service_name || ')'
       end || '(sid = ' || sid || ')
    ))
  '
  from (select --target_name,
         upper(host_name) hostname
        ,upper(substr(t.host_name, 1, instr(t.host_name, '.') - 1)) host_short
        ,(substr(t.host_name
                ,instr(t.host_name, '.') + 1
                ,length(t.host_name))) domain
        ,(select p.property_value
            from mgmt$target_properties p
           where p.property_name = 'port'
                 and p.target_guid = t.target_guid) port
        ,'sys/anything@' || host_name || ':' ||
         (select p.property_value
            from mgmt$target_properties p
           where p.property_name = 'port'
                 and p.target_guid = t.target_guid) || '/' ||
         (select p.property_value
            from mgmt$target_properties p
           where p.property_name = 'servicename'
                 and p.target_guid = t.target_guid) || ' as sysdba' connection_string
        ,(select tp.property_value
            from mgmt$target_properties tp
           where tp.target_type = 'host'
                 and tp.property_name = 'ip_address'
                 and tp.target_name = t.host_name) ip
        ,(select p.property_value
            from mgmt$target_properties p
           where p.property_name = 'dbversion'
                 and p.target_guid = t.target_guid) db_version
        ,(select p.property_value
            from mgmt$target_properties p
           where p.property_name = 'oraclehome'
                 and p.target_guid = t.target_guid) oh
        ,(select p.property_value
            from mgmt$target_properties p
           where p.property_name = 'servicename'
                 and p.target_guid = t.target_guid) service_name
        ,(select p.property_value
            from mgmt$target_properties p
           where p.property_name = 'log_archive_mode'
                 and p.target_guid = t.target_guid) logmode
        ,upper((select p.property_value
                 from mgmt$target_properties p
                where p.property_name = 'sid'
                      and p.target_guid = t.target_guid)) sid
        ,(select p.property_value
            from mgmt$target_properties p
           where p.property_name = 'cpucount'
                 and p.target_guid = t.target_guid) cpu
        ,round(sysdate - to_date((select p.property_value
                                   from mgmt$target_properties p
                                  where p.property_name = 'starttime'
                                        and p.target_guid = t.target_guid)
                                ,'yyyy-mm-dd hh24:mi:ss')
              ,0) days_uptime
        ,(select p.property_value
            from mgmt$target_properties p
           where p.property_name = 'starttime'
                 and p.target_guid = t.target_guid) uptime
        ,(select p.property_value
            from mgmt$target_properties p
           where p.property_name = 'versioncategory'
                 and p.target_guid = t.target_guid) versioncategory
        ,(select p.property_value
            from mgmt$target_properties p
           where p.property_name = 'versionbanner'
                 and p.target_guid = t.target_guid) versionbanner
        ,case
           when (instr((select upper(p.property_value)
                         from mgmt$target_properties p
                        where p.property_name = 'versionbanner'
                              and p.target_guid = t.target_guid)
                      ,'enterprise')) > 0 then
            'enterprise'
           else
            'standard/standard one'
         end edition
        ,(select b.value
            from mgmt$ecm_visible_snapshots     a
                ,sysman.mgmt_db_init_params_ecm b
           where a.ecm_snapshot_id = b.ecm_snapshot_id
                 and a.target_type = 'oracle_database'
                 and b.name = 'control_file_record_keep_time'
                 and a.target_guid = t.target_guid) control_file_record_keep_time
        ,(select b.value
            from mgmt$ecm_visible_snapshots     a
                ,sysman.mgmt_db_init_params_ecm b
           where a.ecm_snapshot_id = b.ecm_snapshot_id
                 and a.target_type = 'oracle_database'
                 and b.name = 'optimizer_features_enable'
                 and a.target_guid = t.target_guid) optimizer_features_enable
        ,(select round(b.value / 1024 / 1024 / 1024, 2)
            from mgmt$ecm_visible_snapshots     a
                ,sysman.mgmt_db_init_params_ecm b
           where a.ecm_snapshot_id = b.ecm_snapshot_id
                 and a.target_type = 'oracle_database'
                 and b.name = 'memory_target'
                 and a.target_guid = t.target_guid) memory_target
        ,(select sessions_highwater
            from mgmt$ecm_visible_snapshots a
                ,sysman.mgmt_db_license_ecm b
           where a.ecm_snapshot_id = b.ecm_snapshot_id
                 and target_type = 'oracle_database'
                 and a.target_guid = t.target_guid) sessions_highwater
        ,(select sessions_current
            from mgmt$ecm_visible_snapshots a
                ,sysman.mgmt_db_license_ecm b
           where a.ecm_snapshot_id = b.ecm_snapshot_id
                 and target_type = 'oracle_database'
                 and a.target_guid = t.target_guid) sessions_current
          from mgmt$target t
         where t.target_type in ('oracle_database')) raw_data
 order by host_short
         ,sid;