检查数据库和online logfile的大小
—执行一下这3个SQL，把结果贴出来，看看数据库大小和log的切换频率。
检查数据文件大小
set pagesize 0; select tablespace_name ,sum(bytes)/1024/1024/1024 as GB from dba_data_files group by tablespace_name; select sum(bytes)/1024/1024/1024 as GB from dba_segments; select bytes/1024/1024 as MB from v$log;
检查BCT：
select * FROM V$BLOCK_CHANGE_TRACKING;
检查异步IO
COL NAME FORMAT A50 SELECT NAME,ASYNCH_IO FROM V$DATAFILE F,V$IOSTAT_FILE I WHERE F.FILE#=I.FILE_NO AND FILETYPE_NAME='Data File'; 2 3 NAME ASYNCH_IO -------------------------------------------------- --------- /u01/app/oracle/oradata/yaoy11G/system01.dbf ASYNC_ON /u01/app/oracle/oradata/yaoy11G/sysaux01.dbf ASYNC_ON /u01/app/oracle/oradata/yaoy11G/undotbs01.dbf ASYNC_ON /u01/app/oracle/oradata/yaoy11G/users01.dbf ASYNC_ON /u01/app/oracle/oradata/yaoy11G/example01.dbf ASYNC_ON
检查当前会话的等待事件
set pages 100; set lines 150; col sid format 999999; col event format a40; col state format a15; col waiting head "Waiting(in seconds)" format 9,999.99; col waited head "Waited" format 9,999.99; select sid, state, event, seconds_in_wait waiting, wait_time/100 waited, p1,p2,p3, BLOCKING_SESSION "Blocker" from v$session where event not in ( 'SQL*Net message from client', 'SQL*Net message to client', 'rdbms ipc message' ) and state = 'WAITING' and username not in ('SYS','SYSTEM','SYSMAN','DBSNMP');
检查数据库一天的日志的切换频率
set lines 120; set pages 999; SELECT to_char(first_time,'YYYY-MON-DD') day, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06", to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07", to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0", to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09", to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10", to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11", to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12", to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13", to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14", to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15", to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16", to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17", to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18", to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19", to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20", to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21", to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" from v$log_history where first_time > sysdate - 7 GROUP by to_char(first_time,'YYYY-MON-DD');
进行数据库的自身诊断
SQL> @?/rdbms/admin/addmrpt
进行Oracle健康检查
调用Oracle内置健康检查脚本，生产健康检查报告。
SQL> @?/rdbms/admin/awrrpt
