—————————–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;