檢視Oracle基礎配置資訊和效能相關資訊的指令碼

1.基礎配置資訊：

--資料庫版本檢查
select banner from v$version;

--是否是RAC
select parameter, value
  from v$option
 where parameter = 'Real Application Clusters';

--資料庫資訊檢查
select dbid,
       name,
       log_mode,
       flashback_on,
       force_logging,
       open_mode,
       protection_mode,
       database_role,
       platform_name,
       dataguard_broker,
       guard_status
from gv$database;

--例項檢查
select instance_name,
       host_name,
       status,
       archiver,
       database_status,
       instance_role,
       active_state
  from gv$instance;

--ASM例項檢查
select group_number,
       disk_number,
       mount_status,
       total_mb,
       free_mb,
       name,
       failgroup,
       label,
       path,
       reads,
       writes,
       read_errs,
       write_errs,
       bytes_read,
       bytes_written
  from gv$asm_disk;
 
select group_number,
       name,
       allocation_unit_size,
       state,
       type,
       total_mb,
       free_mb
  from gv$asm_diskgroup;
 
select * from v$asm_diskgroup_stat;

--DataGuard檢查
select SEQUENCE#, STATUS from V$LOG;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
  FROM V$MANAGED_STANDBY;

SELECT MESSAGE FROM V$DATAGUARD_STATUS;
 
--作業系統檢查
select dbms_utility.port_string from dual;

--資料庫組建檢查
select comp_name,version,status from dba_registry;

--語言地區字符集檢查
select property_name, property_value
  from database_properties
 where property_name in
       ('NLS_DATE_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

--使用者檢查
select username, account_status, default_tablespace, temporary_tablespace
  from dba_users
 where account_status = 'OPEN';

--許可權檢查
select distinct grantee
  from dba_role_privs
 where granted_role = 'DBA'
   and grantee not in ('SYS', 'SYSTEM','SYSMAN');

2.和效能相關資訊：

--引數檔案檢查
select name, to_char(value) value, isdefault
  from v$parameter2
 where name in
       ('control_files', 'cursor_sharing', 'db_file_multiblock_read_count',
        'lock_sga', 'pre_page_sga', 'open_cursors', 'session_cached_cursors',
        'processes', 'sessions', 'spfile', 'db_block_size',
        'undo_management', 'undo_retention', 'undo_tablespace',
        'workarea_size_policy')
union
select name, to_char(round(value / 1024 / 1024,2)) || ' (MB)' value, isdefault
  from v$parameter
 where name = 'sga_max_size'
union
select name, to_char(round(value / 1024 / 1024,2)) || ' (MB)' value, isdefault
  from v$parameter
 where name = 'sga_target'
union
select name, to_char(round(value / 1024 / 1024,2)) || ' (MB)' value, isdefault
  from v$parameter
 where name = 'log_buffer'
union
select name, to_char(round(value / 1024 / 1024,2)) || ' (MB)' value, isdefault
  from v$parameter
 where name = 'pga_aggregate_target';


--日誌檔案檢查
select group#, thread#, bytes / 1024 / 1024 "SIZE(MB)", members, status
  from v$log;

--表空間檢查
select t.tablespace_name,
       t.mbs "SIZE(MB)",
       tt.used "USED(MB)",
       (t.mbs - tt.used) "FREE(MB)",
       round((1-tt.used/t.mbs)*100,2) "FREE%",
       ttt.status,
       ttt.contents,
       ttt.logging,
       ttt.extent_management,
       ttt.allocation_type,
       ttt.segment_space_management,
       ttt.retention
  from (select d1.tablespace_name, sum(d1.bytes) / 1024 / 1024 mbs
          from dba_data_files d1
         group by d1.tablespace_name) t,
       (select tablespace_name, round(sum(bytes) / 1024 / 1024, 2) used
          from dba_segments
         group by tablespace_name) tt,
       (select tablespace_name,
               status,
               contents,
               logging,
               extent_management,
               allocation_type,
               segment_space_management,
               retention
          from dba_tablespaces) ttt
 where t.tablespace_name = tt.tablespace_name
   and t.tablespace_name = ttt.tablespace_name;

--資料檔案檢查
select file_name,
       tablespace_name,
       bytes / 1024 / 1024 "SIZE(MB)",
       status,
       autoextensible,
       round(maxbytes / 1024 / 1024, 2) "MAX_SIZE(MB)",
       (increment_by *
       (select value from v$parameter where name = 'db_block_size') / 1024 / 1024) "increment_by(MB)"
  from dba_data_files;
 

--臨時表空間檢查
select t.tablespace_name,
       t.bytes / 1024 / 1024 "SIZE(MB)",
       t.status,
       t.autoextensible,
       t.maxbytes / 1024 / 1024 "MAX_SIZE(MB)",
       (t.increment_by *
       (select value from v$parameter where name = 'db_block_size') / 1024 / 1024) "INCREMENT_BY(MB)",
       t.user_bytes / 1024 / 1024 "USED(MB)"
  from dba_temp_files t;
--歷史會話檢查
select sessions_current, sessions_highwater, cpu_count_current
  from v$license;

--top latch
select *
  from (select name, hash, gets, spin_gets
          from v$latch
         order by spin_gets desc)
 where rownum < 11;
 
--top version_count
select *
  from (select sql_text,
               sql_id,
               version_count,
               executions,
               sharable_mem,
               persistent_mem,
               disk_reads,
               direct_writes,
               buffer_gets,
               cpu_time,
               elapsed_time
          from v$sqlarea
         order by version_count desc)
 where rownum < 11;
 
 
 --top sql by v$sqlarea
 select *
   from (select sql_text,
                sql_id,
                sharable_mem,
                persistent_mem,
                version_count,
                executions,
                disk_reads,
                direct_writes,
                buffer_gets,
                cpu_time,
                elapsed_time
           from v$sqlarea
          order by elapsed_time desc)
  where rownum < 11;

--top system wait event
select *
  from (select *
          from v$system_event
         where wait_class <> 'Idle'
         order by total_waits desc)
 where rownum < 11;

--top session wait event
select *
  from (select *
          from v$session_event
         where wait_class <> 'Idle'
         order by total_waits desc)
 where rownum < 11;
 
--top current wait event
select v1.sid,
       v1.serial#,
       v1.username,
       v1.status,
       v1.osuser,
       v1.machine,
       v1.terminal,
       v1.program,
       v1.type,
       v1.sql_id,
       v2.sql_text,
       v3.blocks *
       (select value / 1024 from v$parameter where name = 'db_block_size') / 1024 "TEMP_USED(MB)",
       v1.event,
       v1.p1text,
       v1.p1,
       v1.p2text,
       v1.p2,
       v1.p3text,
       v1.p3
  from v$session v1, v$sqltext v2, v$tempseg_usage v3
 where v1.wait_class <> 'Idle'
   and v1.sql_id = v2.sql_id
   and v1.sql_id = v3.sql_id
 order by v1.wait_time desc;

--top sql by v$active_session_history.wait
select s1.sql_id,
       s2.sql_text,
       s1.event,
       s1.wait_class,
       s1.total_waits,
       s1.p1text,
       s1.p1,
       s1.p2text,
       s1.p2,
       s1.p3text,
       s1.p3
  from (select sql_id,
               event,
                wait_class,
               (time_waited+wait_time) total_waits,
               p1text,
               p1,
               p2text,
               p2,
               p3text,
               p3
          from v$active_session_history
         where wait_class is not null
           and wait_class <> 'Idle'
           and session_type <> 'BACKGROUND'
         order by 4 desc) s1,
       v$sql s2
 where s1.sql_id = s2.sql_id;

--top sql by wrh$_active_session_history
select v1.sql_id,
       v2.sql_text,
       (v1.time_waited + v1.wait_time) as total_waits,
       v1.program
  from sys.wrh$_active_session_history v1, v$sql v2
 where v1.sql_id = v2.sql_id
   and v1.sql_id is not null
   and v1.event_id not in
       (select event_id from v$event_name where wait_class = 'Idle')
 order by 3 desc;

--top wait event by wrh$_active_session_history
select *
  from (select (select name from v$event_name where event_id = v1.event_id) event_name,
               v1.sql_id,
               (select sql_text from v$sqlarea where sql_id = v1.sql_id) sql_text,
               (v1.time_waited+v1.wait_time) as total_waits,
               v1.program
          from sys.wrh$_active_session_history v1
         where v1.sql_id is not null
           and v1.event_id not in
               (select event_id from v$event_name where wait_class = 'Idle')
         order by 4 desc)
 where rownum < 11;