以下脚本可以用于收集RAC性能诊断信息:
spool rac_diag.log
SELECT B1.INST_ID,
B2.VALUE blocks,
Round(B1.VALUE / 100) total_time,
round((B1.VALUE / B2.VALUE) * 10, 2) avg_time
FROM GV$SYSSTAT B1, GV$SYSSTAT B2
WHERE B1.NAME = 'gc cr block receive time'
AND B2.NAME = 'gc cr blocks received'
AND B1.INST_ID = B2.INST_ID
AND B2.VALUE > 0
Order by 1
/
select v.banner, i.instance_name
from gv$version v, gv$instance i
where v.inst_id = i.inst_id
and v.banner in
(select banner
from (select banner, count(*) cnt from gv$version group by banner)
where cnt <> 2)
order by 1, 2
/
SELECT INST_ID,
SND_Q_LEN,
SND_Q_MAX,
SND_Q_TOT,
TCKT_AVAIL,
TCKT_LIMIT,
TCKT_RCVD,
TCKT_WAIT
FROM GV$DLM_TRAFFIC_CONTROLLER
WHERE (SND_Q_LEN > 0)
OR ((TCKT_LIMIT - TCKT_AVAIL) >= (TCKT_LIMIT * 0.6))
OR TCKT_WAIT = 'YES'
/
SELECT A1.INST_ID,
A1.VALUE blocks_lost,
A2.VALUE blocks_corrupt
FROM GV$SYSSTAT A1,
GV$SYSSTAT A2
WHERE A1.NAME = 'gc blocks lost'
AND A2.NAME = 'gc blocks corrupt'
AND A1.INST_ID = A2.INST_ID
AND (a1.value > 0 or a2.value > 0)
/
select *
from (SELECT INST_ID,
OWNER#,
NAME,
KIND,
FILE#,
SUM(FORCED_READS) READS,
SUM(FORCED_WRITES) WRITES,
MAX(XNC) XNC
FROM GV$CACHE_TRANSFER
GROUP BY INST_ID, OWNER#, NAME, KIND, FILE#
ORDER BY 8 DESC)
where rownum <= 10
/
select o.parameter, o.value, i.instance_name
from gv$option o, gv$instance i
where o.inst_id = i.inst_id
and o.Parameter in (select Parameter
from (select Parameter, value, count(*) cnt
from gv$option
group by Parameter, value)
where cnt <> 2)
order by 1, 2
/
select p.name, p.value, i.instance_name
from gv$parameter p, gv$instance i
where p.inst_id = i.inst_id
and p.name in (select name
from (select name, value, count(*) cnt
from gv$parameter
where name in ('archive_lag_target',
'control_management_pack_access',
'diagnostic_dest',
'redo_transport_user',
'trace_enabled',
'license_max_users',
'log_archive_format',
'spfile',
'undo_retention')
group by name, value)
where cnt <> 2)
order by 1, 2
/
select p.name, p.value, i.instance_name
from gv$parameter p, gv$instance i
where p.inst_id = i.inst_id
and p.name in (select name
from (select name, value, count(*) cnt
from gv$parameter
where name in ('active_instance_count',
'cluster_database',
'cluster_database_instances',
'compatible',
'control_files',
'db_block_size',
'db_domain',
'db_files',
'db_name',
'db_recovery_file_dest',
'db_recovery_file_dest_size',
'db_unique_name',
'instance_type',
'max_parallel_servers',
'parallel_execution_message_size',
'dml_locks',
'remote_login_passwordfile',
'result_cache_max_size',
'undo_management')
and not ((name = 'dml_locks') and (value = '0'))
group by name, value)
where cnt <> 2)
order by 1, 2
/
select p.name, p.value, i.instance_name
from gv$parameter p, gv$instance i
where p.inst_id = i.inst_id
and p.name in
(select name
from (select name, value, count(*) cnt
from gv$parameter
where name in ('instance_name',
'instance_number',
'thread',
'rollback_segments',
'undo_tablespace')
and not ((name = 'rollback_segments') and (value = null))
and not ((name = 'instance_name') and (value = null))
group by name, value)
where cnt <> 1)
order by 1, 2
/
select s.inst_id,
s.blocks_served,
Round(1000000 * s.pin_time / s.blocks_served) / 1000 avg_pin_time,
Round(1000000 * s.flush_time / s.blocks_served) / 1000 avg_flush_time,
Round(1000000 * s.send_time / s.blocks_served) / 1000 avg_send_time,
Round((1000000 * (s.pin_time + s.flush_time + s.send_time)) /
s.blocks_served) / 1000 avg_service_time
from (select inst_id,
sum(decode(name, 'gc current block pin time', value, 0)) pin_time,
sum(decode(name, 'gc current block pin flush', value, 0)) flush_time,
sum(decode(name, 'gc current block pin send', value, 0)) send_time,
sum(decode(name, 'gc current block blocks served', value, 0)) blocks_served
from gv$sysstat
where name in ('gc current block pin time',
'gc current block pin flush',
'gc current block pin send',
'gc current block blocks served')
group by inst_id) s
where s.blocks_served > 0
/
spool off