[ORACLE]管理方面的脚本收集
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;
很多其它的DBA脚本见一老外的BLOG: http://www.idevelopment.info/cgi/ORACLE_dba_scripts.cgi
