一键获取数据库整体信息脚本

一键获取数据库整体信息脚本
将脚本内容放spooldb.sql中,在sqlplus中执行,相关信息会自动生成5个文件,其中addm是最近一小时文件,ash是最近半小时文件,而awr文件是最近一小时和最近7天的两个文件。

SET markup html ON spool ON pre off entmap off

set term off
set heading on
set verify off
set feedback off

set linesize 2000
set pagesize 30000
set long 999999999
set longchunksize 999999

column index_name format a30
column table_name format a30
column num_rows format 999999999
column index_type format a24
column num_rows format 999999999
column status format a8
column clustering_factor format 999999999
column degree format a10
column blevel format 9
column distinct_keys format 9999999999
column leaf_blocks format   9999999
column last_analyzed    format a10
column column_name format a25
column column_position format 9
column temporary format a2
column partitioned format a5
column partitioning_type format a7
column partition_count format 999
column program  format a30
column spid  format a6
column pid  format 99999
column sid  format 99999
column serial# format 99999
column username  format a12
column osuser    format a12
column logon_time format  date
column event    format a32
column JOB_NAME        format a30
column PROGRAM_NAME    format a32
column STATE           format a10
column window_name           format a30
column repeat_interval       format a60
column machine format a30
column program format a30
column osuser format a15
column username format a15
column event format a50
column seconds format a10
column sqltext format a100

SET markup html off
column dbid new_value spool_dbid
column inst_num new_value spool_inst_num
select dbid from v$database where rownum = 1;
select instance_number as inst_num from v$instance where rownum = 1;
column spoolfile_name new_value spoolfile
select 'spool_'||(select name from v$database where rownum=1) ||'_'|| (select instance_number from v$instance where rownum=1)||'_'||to_char(sysdate,'yy-mm-dd_hh24.mi') as spoolfile_name from dual;
spool &&spoolfile..html

SET markup html off
set serveroutput on;
exec dbms_output.enable(9999999999);
exec dbms_output.put_line('<html>');
exec dbms_output.put_line('<style>');
exec dbms_output.put_line('th {font:bold 8pt Arial,Helvetica,Geneva,sans-serif; color:White; background:#0066CC;padding-left:4px; padding-right:4px;padding-bottom:2px}');
exec dbms_output.put_line('</style>');
exec dbms_output.put_line('<head>');
exec dbms_output.put_line('</head>');
exec dbms_output.put_line('<body>');

SET markup html on

prompt <p>VERSION
select * from v$version;
select * from dba_registry_history;

prompt <p>Last startup time, version, and whether RAC
select * from
 (select name db_name from v$database),
 (select instance_name from v$instance),
 (select archiver from v$instance),
 (select snap_interval awr_interval, retention awr_retention FROM DBA_HIST_WR_CONTROL),
 (select flashback_on from v$database),
 (select parallel from v$instance),
 (select startup_time from v$instance),
 (select decode(name,null,'NOT ASM','ASM') IS_ASM from (select null name from dual union all (select name IS_ASM from v$datafile where name like '+%' and rownum = 1))),
 (select max(end_time) rman_lastcompleted from v$rman_status where status = 'COMPLETED' and object_type like 'DB FULL')
;

prompt <p>CPU or wait for the longest in 30 minutes
select t.*, s.sid, s.serial#, s.machine, s.program, s.osuser
  from (select c.USERNAME,
               a.event,
               to_char(a.cnt) as seconds,
               a.sql_id
               --,dbms_lob.substr(b.sql_fulltext,50,1) sqltext
          from (select rownum rn, t.*
                  from (select decode(s.session_state,
                                      'WAITING',
                                      s.event,
                                      'Cpu + Wait For Cpu') Event,
                               s.sql_id,
                               s.user_id,
                               count(*) CNT
                          from v$active_session_history s
                         where sample_time > sysdate - 30 / 1440
                         group by s.user_id,
                                  decode(s.session_state,
                                         'WAITING',
                                         s.event,
                                         'Cpu + Wait For Cpu'),
                                  s.sql_id
                         order by CNT desc) t
                 where rownum < 20) a,
               v$sqlarea b,
               dba_users c
         where a.sql_id = b.sql_id
           and a.user_id = c.user_id
         order by CNT desc) t,
       v$session s
where t.sql_id = s.sql_id(+)
;

prompt <p>Recent load status (based on AWR snapshot
select s.snap_date,
       decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
       to_char(round(s.seconds/60,2)) "elapse(min)",
       round(t.db_time / 1000000 / 60, 2) "DB time(min)",
       s.redosize redo,
       round(s.redosize / s.seconds, 2) "redo/s",
       s.logicalreads logical,
       round(s.logicalreads / s.seconds, 2) "logical/s",
       physicalreads physical,
       round(s.physicalreads / s.seconds, 2) "phy/s",
       s.executes execs,
       round(s.executes / s.seconds, 2) "execs/s",
       s.parse,
       round(s.parse / s.seconds, 2) "parse/s",
       s.hardparse,
       round(s.hardparse / s.seconds, 2) "hardparse/s",
       s.transactions trans,
       round(s.transactions / s.seconds, 2) "trans/s"
  from (select curr_redo - last_redo redosize,
               curr_logicalreads - last_logicalreads logicalreads,
               curr_physicalreads - last_physicalreads physicalreads,
               curr_executes - last_executes executes,
               curr_parse - last_parse parse,
               curr_hardparse - last_hardparse hardparse,
               curr_transactions - last_transactions transactions,
               round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
               to_char(currtime, 'yy/mm/dd') snap_date,
               to_char(currtime, 'hh24:mi') currtime,
               currsnap_id endsnap_id,
               to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
          from (select a.redo last_redo,
                       a.logicalreads last_logicalreads,
                       a.physicalreads last_physicalreads,
                       a.executes last_executes,
                       a.parse last_parse,
                       a.hardparse last_hardparse,
                       a.transactions last_transactions,
                       lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
                       lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
                       lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
                       lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
                       lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
                       lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
                       lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
                       b.end_interval_time lasttime,
                       lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
                       lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
                       b.startup_time
                  from (select snap_id,
                               dbid,
                               instance_number,
                               sum(decode(stat_name, 'redo size', value, 0)) redo,
                               sum(decode(stat_name,
                                          'session logical reads',
                                          value,
                                          0)) logicalreads,
                               sum(decode(stat_name,
                                          'physical reads',
                                          value,
                                          0)) physicalreads,
                               sum(decode(stat_name, 'execute count', value, 0)) executes,
                               sum(decode(stat_name,
                                          'parse count (total)',
                                          value,
                                          0)) parse,
                               sum(decode(stat_name,
                                          'parse count (hard)',
                                          value,
                                          0)) hardparse,
                               sum(decode(stat_name,
                                          'user rollbacks',
                                          value,
                                          'user commits',
                                          value,
                                          0)) transactions
                          from dba_hist_sysstat
                         where stat_name in
                               ('redo size',
                                'session logical reads',
                                'physical reads',
                                'execute count',
                                'user rollbacks',
                                'user commits',
                                'parse count (hard)',
                                'parse count (total)')
                         group by snap_id, dbid, instance_number) a,
                       dba_hist_snapshot b
                 where a.snap_id = b.snap_id
                   and a.dbid = b.dbid
                   and a.instance_number = b.instance_number
                   and a.dbid = &&spool_dbid
                   and a.instance_number = &&spool_inst_num
                 order by end_interval_time)) s,
       (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
               lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id
          from dba_hist_sys_time_model a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.dbid = b.dbid
           and a.instance_number = b.instance_number
           and a.stat_name = 'DB time'
           and a.dbid = &&spool_dbid
           and a.instance_number = &&spool_inst_num) t
 where s.endsnap_id = t.endsnap_id
order by s.snap_date desc ,time asc
;

prompt <p>逻辑读最多
select *
  from (select sql_id,
               s.EXECUTIONS,
               s.LAST_LOAD_TIME,
               s.FIRST_LOAD_TIME,
               s.DISK_READS,
               s.BUFFER_GETS
          from v$sql s
         where s.buffer_gets > 300
         order by buffer_gets desc)
where rownum <= 10
;

prompt <p>Logical read most
select * from (select sql_id,
       s.EXECUTIONS,
       s.LAST_LOAD_TIME,
       s.FIRST_LOAD_TIME,
       s.DISK_READS,
       s.BUFFER_GETS,
       s.PARSE_CALLS
  from v$sql s
where s.disk_reads > 300
order by disk_reads desc)
where rownum<=10
;

prompt <p>Maximum number of executions
select *
  from (select sql_id,
               s.EXECUTIONS,
               s.LAST_LOAD_TIME,
               s.FIRST_LOAD_TIME,
               s.DISK_READS,
               s.BUFFER_GETS,
               s.PARSE_CALLS
          from v$sql s
         order by s.EXECUTIONS desc)
where rownum <= 10
;

prompt <p>SQL parsing most
select *
  from (select sql_id,
               s.EXECUTIONS,
               s.LAST_LOAD_TIME,
               s.FIRST_LOAD_TIME,
               s.DISK_READS,
               s.BUFFER_GETS,
               s.PARSE_CALLS
          from v$sql s
         order by s.PARSE_CALLS desc)
where rownum <= 10
;

prompt <p>The disk is sorted most
select sess.username, sql.address, sort1.blocks
  from v$session sess, v$sqlarea sql, v$sort_usage sort1
where sess.serial# = sort1.session_num
   and sort1.sqladdr = sql.address
   and sort1.sqlhash = sql.hash_value
   and sort1.blocks > 200
order by sort1.blocks desc
;

prompt <p>Commits more than 10,000 session
select t1.sid, t1.value, t2.name
  from v$sesstat t1, v$statname t2
 where t2.name like '%user commits%'
   and t1.STATISTIC# = t2.STATISTIC#
   and value >= 10000
 order by value desc
;

prompt <p>SQL with a length of more than 100
SELECT SQL_ID, COUNT(*) line_count
  FROM V$SQLTEXT
 GROUP BY SQL_ID
HAVING COUNT(*) >= 100
 ORDER BY COUNT(*) DESC
;

prompt <p>Query shared memory share
select count(*),round(sum(sharable_mem)/1024/1024,2)
  from v$db_object_cache a
;

prompt <p>Table with parallelism
select t.owner, t.table_name, degree
  from dba_tables t
where  trim(t.degree) <>'1'
   and trim(t.degree)<>'0'
   and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
   and owner not like 'FLOWS%'
   and owner not like 'WK%'
;

prompt <p>Index with parallelism
select t.owner, t.table_name, index_name, degree, status
  from dba_indexes t
where  trim(t.degree) <>'1'
   and trim(t.degree)<>'0'
   and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
   and owner not like 'FLOWS%'
   and owner not like 'WK%'
;

prompt <p>Invalid index
select t.index_name,
       t.table_name,
       blevel,
       t.num_rows,
       t.leaf_blocks,
       t.distinct_keys
  from dba_indexes t
  where status = 'UNUSABLE'
  and  table_owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB')
  and  table_owner not like 'FLOWS%'
;
select t2.owner,
       t1.blevel,
       t1.leaf_blocks,
       t1.INDEX_NAME,
       t2.table_name,
       t1.PARTITION_NAME,
       t1.STATUS
  from dba_ind_partitions t1, dba_indexes t2
where t1.index_name = t2.index_name
   and t1.STATUS = 'UNUSABLE'
;

prompt <p>Invalid object
select t.owner,
       t.object_type,
       t.object_name
  from dba_objects t
 where STATUS='INVALID'
order by 1, 2
;

prompt <p>Bitmap index and function index, reverse key index
select t.owner,
       t.table_name,
       t.index_name,
       t.index_type,
       t.status,
       t.blevel,
       t.leaf_blocks
  from dba_indexes t
 where index_type in ('BITMAP', 'FUNCTION-BASED NORMAL', 'NORMAL/REV')
   and owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
   and owner not like 'FLOWS%'
   and owner not like 'WK%'
;

prompt <p>The combination of index columns is more than four
select table_owner,table_name, index_name, count(*)
  from dba_ind_columns
where  table_owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
   and table_owner not like 'FLOWS%'
   and table_owner not like 'WK%'
 group by table_owner,table_name, index_name
having count(*) >= 4
 order by count(*) desc
;

prompt <p>Indexed more than 5 numbers
select owner,table_name, count(*) cnt
  from dba_indexes
where  owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
   and owner not like 'FLOWS%'
   and owner not like 'WK%'
 group by owner,table_name
having count(*) >= 5
order by cnt desc
;

prompt <p>Never indexed a large table
select segment_name,
       bytes / 1024 / 1024 / 1024 "GB",
       blocks,
       tablespace_name
  from dba_segments
 where segment_type = 'TABLE'
   and segment_name not in (select table_name from dba_indexes)
   and bytes / 1024 / 1024 / 1024 >= 0.5
 order by GB desc
;
select segment_name, sum(bytes) / 1024 / 1024 / 1024 "GB", sum(blocks)
  from dba_segments
 where segment_type = 'TABLE PARTITION'
   and segment_name not in (select table_name from dba_indexes)
 group by segment_name
having sum(bytes) / 1024 / 1024 / 1024 >= 0.5
 order by GB desc
;

prompt <p>There is a cross between the combined index of the table and the single column index

select table_name, trunc(count(distinct(column_name)) / count(*),2) cross_idx_rate
  from dba_ind_columns
 where table_owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
   and table_owner not like 'FLOWS%'
   and table_owner not like 'WK%'
 group by table_name
having count(distinct(column_name)) / count(*) < 1 
order by cross_idx_rate desc
;

prompt <p>Object is built on the system tablespace
select * from (
select owner, segment_name, tablespace_name, count(*) num
  from dba_segments
 where tablespace_name in('SYSTEM','SYSAUX')
group by owner, segment_name, tablespace_name)
where  owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB','ORDSYS','DBSNMP','OUTLN','TSMSYS')
   and owner not like 'FLOWS%'
   and owner not like 'WK%'
;

prompt <p>Check whether the statistics are collected
select t.job_name,t.program_name,t.state,t.enabled
  from dba_scheduler_jobs t
where job_name = 'GATHER_STATS_JOB'
;
select client_name,status
  from dba_autotask_client
;
select window_next_time,autotask_status
  from DBA_AUTOTASK_WINDOW_CLIENTS
;

prompt <p>Check the object that was not collected or collected for a long time
select owner, count(*)
  from dba_tab_statistics t
where (t.last_analyzed is null or t.last_analyzed < sysdate - 100)
   and table_name not like 'BIN$%'
group by owner
order by owner
;

prompt <p>Temporary table for collecting statistical information
select owner, table_name, t.last_analyzed, t.num_rows, t.blocks
  from dba_tables t
where t.temporary = 'Y'
   and last_analyzed is not null
;

prompt <p>Log switching frequency analysis
select *
  from (select thread#, sequence#, to_char(first_time, 'MM/DD/RR HH24:MI:SS')
          from v$log_history
         order by first_time desc)
 where rownum <= 50
;

prompt <p>The amount of log switch every day in the last 10 days
SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 ,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
       COUNT(*) TOTAL
FROM v$log_history  a
   where first_time>=to_char(sysdate-11)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC
;

prompt <p>Log group size
select group#,bytes,status
  from v$log
;

prompt <p>show recovery_file_dest usage
 select substr(name, 1, 30) name,
        space_limit as quota,
        space_used as used,
        space_reclaimable as reclaimable,
        number_of_files as files
   from v$recovery_file_dest
;
select * from V$FLASH_RECOVERY_AREA_USAGE
;

prompt <p>Check if the sequence is less than 20
select sequence_owner,
       count(*) CNT,
       sum(case when t.cache_size <= 20 then 1 else 0 end ) CNT_LESS_20,
       sum(case when t.cache_size > 20 then 1 else 0 end ) CNT_MORE_20
  from dba_sequences t
 group by sequence_owner
;

prompt <p>Table space usage
set markup html off
prompt <p>
declare
  type NUMBER_ARRAY is table of number(15) index by varchar2(30);
  ts_free_mb NUMBER_ARRAY;
  cursor c1 is
    select tablespace_name, sum(free_mb) + sum(expired_mb) free_mb
      from (SELECT tablespace_name,
                   round(sum(nvl(bytes, 0)) / 1024 / 1024, 2) free_mb,
                   0 expired_mb
              FROM dba_free_space
             GROUP BY tablespace_name
            union all
            select tablespace_name,
                   0 free_mb,
                   round(sum(nvl(bytes, 0)) / 1024 / 1024, 2) expired_mb
              from dba_undo_extents d
             where tablespace_name =
                   (select value
                      from v$parameter
                     where name = 'undo_tablespace')
               and status = 'EXPIRED'
             group by tablespace_name)
     group by tablespace_name;
  cursor c2 is
    SELECT /*+ rule */
     tablespace_name, round(sum(bytes) / 1024 / 1024, 2) total_mb
      FROM dba_data_files
     where tablespace_name not in
           (select tablespace_name
              from dba_data_files
             where upper(AUTOEXTENSIBLE) = 'YES')
     GROUP BY tablespace_name;
  ts_name  varchar2(30);
  ts_total number(15);
  ts_used  number(15);
  ts_free  number(15);
  ts_rate  varchar2(5);
begin
  for rec1 in c1 loop
    ts_free_mb(rec1.tablespace_name) := rec1.free_mb;
  end loop;
  dbms_output.put_line('<table border="1" width="90%" align="center" summary="Script output">');
  dbms_output.put_line('<tr><th>ts_name</th><th>ts_total</th><th>ts_used</th><th>ts_free</th><th>ts_rate</th></tr>');
  for rec2 in c2 loop
    ts_name  := null;
    ts_total := null;
    ts_used  := null;
    ts_free  := null;
    ts_rate  := null;
    ts_name  := rec2.tablespace_name;
    ts_total := rec2.total_mb;
    ts_free  := nvl(ts_free_mb(ts_name), 0);
    ts_used  := nvl(ts_total - ts_free, 0);
    ts_rate  := to_char(round((ts_total - ts_free) / ts_total * 100, 2),
                        'fm990.99');
    dbms_output.put_line('<tr><td>' || ts_name || '</td><td>' || ts_total ||
                         '</td><td>' || ts_used || '</td><td>' || ts_free ||
                         '</td><td>' || ts_rate || '</td></tr>');
  end loop;
  dbms_output.put_line('</table>');
end;
/
prompt <p>
set markup html on

prompt <p>The size of the entire database
select owner, round(sum(bytes) / 1024 / 1024 / 1024, 2) "GB"
  from dba_segments
 group by owner
 order by 2 desc
;

prompt <p>Object size TOP10
select *
  from (select owner,
               segment_name,
               segment_type,
               round(sum(bytes) / 1024 / 1024) object_size
          from DBA_segments
         group by owner, segment_name, segment_type
         order by object_size desc)
where rownum <= 10
;

prompt <p>Recycle Bin situation (size and quantity)
select *
  from (select SUM(BYTES) / 1024 / 1024 / 1024 as recyb_size
          from DBA_SEGMENTS
         WHERE SEGMENT_NAME LIKE 'BIN$%') a,
       (select count(*) as recyb_cnt from dba_recyclebin)
;

prompt <p>Check who took up the undo tablespace

SELECT r.name "roll_segment_name", rssize/1024/1024/1024 "RSSize(G)",
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.sql_hash_value,
  s.SQL_ADDRESS,
  s.MACHINE,
  s.MODULE,
  substr(s.program, 1, 78) program,
  r.usn,
  hwmsize/1024/1024/1024, shrinks ,xacts
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r, v$rollstat rs
WHERE t.addr = s.taddr and t.xidusn = r.usn and r.usn=rs.USN
Order by rssize desc
;

prompt <p>Check who took up the temp tablespace

select sql.sql_id,
       t.Blocks * 16 / 1024 / 1024,
       s.USERNAME,
       s.SCHEMANAME,
       t.tablespace,
       t.segtype,
       t.extents,
       s.PROGRAM,
       s.OSUSER,
       s.TERMINAL,
       s.sid,
       s.SERIAL#
  from v$sort_usage t, v$session s , v$sql sql
where t.SESSION_ADDR = s.SADDR
  and t.SQLADDR=sql.ADDRESS
  and t.SQLHASH=sql.HASH_VALUE
;

prompt <p>Observe the rollback segment, the temporary segment and the general segment No is automatically expanded
select t2.contents, t1.*
  from (select file_name, tablespace_name, bytes, maxbytes, autoextensible
          from dba_temp_files
        union all
        select file_name, tablespace_name, bytes, maxbytes, autoextensible
          from dba_data_files) t1,
       dba_tablespaces t2
 where t1.tablespace_name = t2.tablespace_name
;

prompt <p>Table size of more than 10GB did not build the partition table
select owner,
       segment_name,
       segment_type,
       round(sum(bytes) / 1024 / 1024 / 1024,2) object_size
  from dba_segments
where segment_type = 'TABLE'
  and bytes > 10*1024*1024*1024
group by owner, segment_name, segment_type
order by object_size desc
;

prompt <p>The top 10 partitions are the most
select *
  from (select table_owner, table_name, count(*) cnt
          from dba_tab_partitions
         group by table_owner, table_name
         order by cnt desc)
where rownum <= 10
;

prompt <p>Partitioned uneven table
select *
  from (select table_owner,
               table_name,
               max(num_rows) max_num_rows,
               trunc(avg(num_rows), 0) avg_num_rows,
               sum(num_rows) sum_num_rows,
               case
                 when sum(num_rows) = 0 then
                  0
                 else
                  trunc(max(num_rows) / trunc(avg(num_rows), 0), 2)
               end rate,
               count(*) part_count
          from dba_tab_partitions
         group by table_owner, table_name)
 where rate > 5;

prompt <p>A table with a column larger than 100 or less than 2
select *
  from (select owner, table_name, count(*) col_count
          from dba_tab_cols
         group by owner, table_name)
 where col_count > 100
    or col_count <= 2
 and  owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB')
 and  owner not like 'FLOWS%'
;

prompt <p>The table property is NOLOGGING
select owner, table_name, tablespace_name, logging
  from dba_tables
 where logging = 'NO'
 and  owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB')
 and  owner not like 'FLOWS%'
 ;

prompt <p>Table properties, containing COMPRESSION
select owner, table_name, tablespace_name, COMPRESSION
  from dba_tables
 where COMPRESSION = 'ENABLED'
 and  owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB')
 and  owner not like 'FLOWS%'
;

prompt <p>The index property contains COMPRESSION
select owner, index_name, table_name, COMPRESSION
  from dba_indexes
 where COMPRESSION = 'ENABLED'
 and  owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB')
 and  owner not like 'FLOWS%'
;

prompt <p>Trigger
select OWNER, TRIGGER_NAME, TABLE_NAME, STATUS
  from dba_triggers
 where owner not in ('SYSTEM','SYSMAN','SYS','CTXSYS','MDSYS','OLAPSYS','WMSYS','EXFSYS','LBACSYS','WKSYS','XDB')
 and  owner not like 'FLOWS%';
 
prompt <p>The foreign key is not indexed
select *
  from (select pk.owner PK_OWNER,
               pk.constraint_name PK_NAME,
               pk.table_name PK_TABLE_NAME,
               fk.owner FK_OWNER,
               fk.constraint_name FK_NAME,
               fk.table_name FK_TABLE_NAME,
               fk.delete_rule FK_DELETE_RULE,
               ind_col.INDEX_NAME FK_INDEX_NAME,
               ind.index_type FK_INDEX_TYPE,
               con_col.COLUMN_NAME FK_INDEX_COLUMN_NAME,
               con_col.POSITION FK_INDEX_COLUMN_POSITION,
               decode(ind_col.INDEX_NAME,
                      NULL,
                      NULL,
                      NVL(x_ind.status, 'VALID')) IS_IND_VALID,
               decode(ind_col.INDEX_NAME,
                      NULL,
                      NULL,
                      NVL(x_ind_part.status, 'VALID')) IS_IND_PART_VALID
          from (select * from dba_constraints where constraint_type = 'R') fk,
               (select * from dba_constraints where constraint_type = 'P') pk,
               dba_cons_columns con_col,
               dba_ind_columns ind_col,
               dba_indexes ind,
               (select index_owner, index_name, status
                  from dba_ind_partitions
                 where status <> 'VALID') x_ind_part,
               (select owner as index_owner, index_name, status
                  from dba_indexes
                 where status <> 'VALID') x_ind
         where fk.r_constraint_name = pk.constraint_name
           and pk.owner = fk.owner
           and fk.owner = con_col.owner
           and fk.table_name = con_col.table_name
           and fk.constraint_name = con_col.CONSTRAINT_NAME
           and con_col.owner = ind_col.TABLE_OWNER(+)
           and con_col.TABLE_NAME = ind_col.TABLE_NAME(+)
           and con_col.COLUMN_NAME = ind_col.COLUMN_NAME(+)
           and ind_col.INDEX_OWNER = ind.owner(+)
           and ind_col.INDEX_NAME = ind.index_name(+)
           and ind_col.INDEX_OWNER = x_ind.index_owner(+)
           and ind_col.INDEX_NAME = x_ind.index_name(+)
           and ind_col.INDEX_OWNER = x_ind_part.index_owner(+)
           and ind_col.INDEX_NAME = x_ind_part.index_name(+))
 where FK_INDEX_NAME is null
 order by FK_OWNER ASC
;

/* Bad performance
prompt <p>Hot block (summary)
SELECT *+ rule *
         e.owner, e.segment_name, e.segment_type, sum(b.tch) tch
          FROM dba_extents e,
               (SELECT *
                  FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
                          FROM x$bh
                         ORDER BY tch DESC)
                 WHERE ROWNUM <= 10) b
         WHERE e.relative_fno = b.dbarfil
           AND e.block_id <= b.dbablk
           AND e.block_id + e.blocks > b.dbablk
           group by e.owner, e.segment_name, e.segment_type
order by tch desc
;
prompt <p>Hot block (unfolded, not summary)
SELECT *+ rule *
        distinct e.owner, e.segment_name, e.segment_type, dbablk,b.tch
          FROM dba_extents e,
               (SELECT *
                  FROM (SELECT addr, ts#, file#, dbarfil, dbablk, tch
                          FROM x$bh
                         ORDER BY tch DESC)
                 WHERE ROWNUM <= 10) b
         WHERE e.relative_fno = b.dbarfil
           AND e.block_id <= b.dbablk
           AND e.block_id + e.blocks > b.dbablk
order by tch desc
;
*/

prompt <p>Appendix: check the parameter settings of session_cached_cursors and increase the parameter value if the usage rate is 100%
SELECT 'session_cached_cursors' PARAMETER,  
         LPAD(VALUE, 5) VALUE,  
         DECODE(VALUE, 0, ' n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE  
   FROM (SELECT MAX(S.VALUE) USED  
            FROM V$STATNAME N, V$SESSTAT S  
           WHERE N.NAME = 'session cursor cache count'  
             AND S.STATISTIC# = N.STATISTIC#),  
         (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors')  
  UNION ALL  
SELECT 'open_cursors',  
         LPAD(VALUE, 5),  
         TO_CHAR(100 * USED / VALUE, '990') || '%'  
   FROM (SELECT MAX(SUM(S.VALUE)) USED  
            FROM V$STATNAME N, V$SESSTAT S  
           WHERE N.NAME IN  
                 ('opened cursors current', 'session cursor cache count')  
             AND S.STATISTIC# = N.STATISTIC#  
           GROUP BY S.SID),  
         (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors');  


prompt <p>Appendix: all parameters of Oracle for reference
show parameter

set markup html off
exec dbms_output.put_line('</body>');
exec dbms_output.put_line('</html>');

spool off


/* Obtain awr、addm、ash */
--HTML tags are not used below
SET markup html off spool ON pre off entmap off

set trim on
set trimspool on
set heading off

--select dbid、instance_number
column dbid new_value awr_dbid
column instance_number new_value awr_inst_num
select dbid from v$database;
select instance_number from v$instance;

--Ash report in half an hour
column ashbegintime new_value ashbegin_str
column ashendtime new_value ashend_str
select to_char(sysdate-3/144,'yyyymmddhh24miss') as ashbegintime, to_char(sysdate,'yyyymmddhh24miss') as ashendtime from dual;

column ashfile_name new_value ashfile
select 'ashrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&ashbegin_str) || '_' || to_char(&&ashend_str) ashfile_name from dual;
spool &&ashfile..html
select * from table(dbms_workload_repository.ash_report_html(to_char(&&awr_dbid),to_char(&&awr_inst_num),to_date(to_char(&&ashbegin_str),'yyyymmddhh24miss'),to_date(to_char(&&ashend_str),'yyyymmddhh24miss')));
spool off;

--Create AWR snapshot
column begin_snap new_value awr_begin_snap
column end_snap new_value awr_end_snap
select max(snap_id) begin_snap
  from dba_hist_snapshot
 where snap_id < (select max(snap_id) from dba_hist_snapshot);
select max(snap_id) end_snap from dba_hist_snapshot;
declare
  snap_maxtime date;
  snap_mintime date;
begin
  select max(end_interval_time) + 0
    into snap_maxtime
    from dba_hist_snapshot
   where snap_id = to_number(&&awr_end_snap);
  select max(end_interval_time) + 0
    into snap_mintime
    from dba_hist_snapshot
   where snap_id = to_number(&&awr_begin_snap);
  if sysdate - snap_maxtime > 10/1445 then
    dbms_workload_repository.create_snapshot();
  end if;
end;
/

--The latest two AWR reports between snap_id
column begin_snap new_value awr_begin_snap
column end_snap new_value awr_end_snap
select max(snap_id) begin_snap
  from dba_hist_snapshot
 where snap_id < (select max(snap_id) from dba_hist_snapshot);
select max(snap_id) end_snap from dba_hist_snapshot;
column awrfile_name new_value awrfile
select 'awrrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&awr_begin_snap) || '_' || to_char(&&awr_end_snap) awrfile_name from dual;

spool &&awrfile..html
select output from table(dbms_workload_repository.awr_report_html(&&awr_dbid,&&awr_inst_num,&&awr_begin_snap,&&awr_end_snap));
spool off;


--The longest AWR report available (all analyses since a week)
column begin_snap new_value awr_begin_snap
column end_snap new_value awr_end_snap
select a.begin_snap, a.end_snap
  from (select startup_time, min(snap_id) begin_snap, max(snap_id) end_snap
          from dba_hist_snapshot
         group by startup_time) a,
       (select max(startup_time) startup_time from dba_hist_snapshot) b
 where a.startup_time = b.startup_time
   and rownum = 1;
column awrfile_name new_value awrfile
select 'awrrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&awr_begin_snap) || '_' || to_char(&&awr_end_snap) ||'_all' awrfile_name from dual;

spool &&awrfile..html
select output from table(dbms_workload_repository.awr_report_html(&&awr_dbid,&&awr_inst_num,&&awr_begin_snap,&&awr_end_snap));
spool off;


--Latest ADDM Report
column addmfile_name new_value addmfile
select 'addmrpt_' || to_char(&&awr_inst_num) || '_' || to_char(&&awr_begin_snap) || '_' || to_char(&&awr_end_snap) addmfile_name from dual;
set serveroutput on
spool &&addmfile..txt
declare
  id          number;
  name        varchar2(200) := '';
  descr       varchar2(500) := '';
  addmrpt     clob;
  v_ErrorCode number;
BEGIN
  name := '&&addmfile';
  begin
    dbms_advisor.create_task('ADDM', id, name, descr, null);
    dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', &&awr_begin_snap);
    dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', &&awr_end_snap);
    dbms_advisor.set_task_parameter(name, 'INSTANCE', &&awr_inst_num);
    dbms_advisor.set_task_parameter(name, 'DB_ID', &&awr_dbid);
    dbms_advisor.execute_task(name);
  exception
    when others then
      null;
  end;
  select dbms_advisor.get_task_report(name, 'TEXT', 'TYPICAL')
    into addmrpt
    from sys.dual;
  dbms_output.enable(20000000000);
  for i in 1 .. (DBMS_LOB.GETLENGTH(addmrpt) / 2000 + 1) loop
    dbms_output.put_line(substr(addmrpt, 1900 * (i - 1) + 1, 1900));
  end loop;
  dbms_output.put_line('');
  begin
    dbms_advisor.delete_task(name);
  exception
    when others then
      null;
  end;
end;
/
spool off;

exit;

文章目录