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