Script:收集RAC性能诊断信息
以下脚本可以用于收集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
posted on 2010-06-10 02:52 Oracle和MySQL 阅读(198) 评论(0) 编辑 收藏 举报