日常巡检OS操作系统1.查看文件系统使用率df –g(主要看存放数据文件的文件系统和归档的文件系统)2.查看系统负载情况 topas数据库DB查看警告日志里面是否包含ORA-600Bcsp 147sudo cat /oracle/admin/cdc/bdump/alert_cdc.log | grep ora-600查看alert日志里面有没有错误信息sudo cat /oracle/admin/cdc/bdump/alert_cdc.log数据库版本信息select * from v$version;数据库初始化参数Sql> show parameters spfileSelect * from v$parameters查询控制文件select * from v$controlfile;STATUS NAME BLOCK_SIZE------- ------------------------------------------------/datafs/cdc/control01.ctl 16384/datafs/cdc/control02.ctl 16384/datafs/cdc/control03.ctl 16384查询redoselect * from v$log;GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE#1 1 579893 52428800 1 YES INACTIVE 517039186492 1 579895 52428800 1 NO INACTIVE 517043526423 1 579891 52428800 1 YES INACTIVE 517036980624 1 579894 52428800 1 YES CURRENT 517041807405 1 579889 52428800 1 YES CURRENT 517035660316 1 579890 52428800 1 YES CURRENT 517036334417 1 579892 52428800 1 YES CURRENT 51703761803select * from v$logfile;GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE3 ONLINE /datafs/cdc/redo03.log NO2 ONLINE /datafs/cdc/redo02.log NO1 ONLINE /datafs/cdc/redo01.log NO4 ONLINE /datafs/cdc/redo04.log NO5 ONLINE /datafs/cdc/redo05.log NO6 ONLINE /datafs/cdc/redo06.log NO7 ONLINE /datafs/cdc/redo07.log NOUndo管理SQL> show parameters undoNAME TYPE VALUEundo_management string AUTOundo_tablespace string undotbs1select status, file_id, file_name, tablespace_name ts_name, autoextensible, blocks/128 cur_mb, maxblocks/128 max_mbfrom dba_data_files where tablespace_name=’UNDOTBS1’ order by file_name;查看redo切换频率和归档的切换频率//redo切换频率SELECT MAX (first_time) max_first_time,TO_CHAR (first_time, 'yyyy-mm-dd') DAY,COUNT (recid) count_number,COUNT (recid) * 50 size_mbFROM v$log_historyWHERE thread# = 3GROUP BY TO_CHAR (first_time, 'yyyy-mm-dd')ORDER BY 1;//归档切换频率SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day", COUNT (1) "Total",SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22", SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23", ROUND (COUNT (1) / 24, 2) "Avg"FROM gv$log_historyWHERE thread# = inst_idAND inst_id= 3AND first_time >= sysdate -7GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')ORDER BY 1;表空间使用率(包括临时表空间使用率)--查询表空间使用率包括临时表空间SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKSFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE FROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)UNION ALL --if have tempfileSELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",NVL(FREE_SPACE,0) "FREE_SPACE(M)"FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKSFROM DBA_TEMP_FILESGROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE, ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACEFROM V$TEMP_SPACE_HEADERGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)数据文件管理select distinct block_size from dba_tablespaces;8192set linesize 110 pagesize 50col file_id for 9999999col file_name for a48col ts_name for a18col cur_mb for 99999col max_mb for 99999select status, file_id, file_name, tablespace_name ts_name, autoextensible,blocks/128 cur_mb, maxblocks/128 max_mbfrom dba_data_files order by file_name;STATUS FILE_ID FILE_NAME TS_NAME AUTOEXTENSIBLE CUR_MB MAX_MB AVAILABLE 5 /datafs/cdc/csk_base01.dbf CSK_BASE NO 30720 0 AVAILABLE 68 /datafs/cdc/csk_bill_data01.dbf CSK_BILL_DATA NO 22528 0 AVAILABLE 69 /datafs/cdc/csk_bill_data02.dbf CSK_BILL_DATA NO 22528 0临时文件管理select status, file_id, file_name, tablespace_name ts_name, autoextensible,blocks/128 cur_mb, maxblocks/128 max_mbfrom dba_temp_files order by file_name;查询数据库无效对象无效对象统计select count(*) from dba_objects where status='INVALID';COUNT(*)168select owner,count(*) from dba_objects where status='INVALID' group by owner;OWNER COUNT(*)PUBLIC 19ZHCARD 72DSELL 4EISS 52EISSOS 21查询出用户下的无效对象select owner,object_name, replace (object_type,' ','') object_type,to_char(created,'yyyy-mm-dd') as created,to_char(last_ddl_time,'yyyy-mm-dd') as last_ddl_time,statusfrom dba_objects where status='INVALID' and owner='ZHCARD';OWNER OBJECT_NAME OBJECT_TYPEZHCARD PRC_BILLRULE_SMS PROCEDUREZHCARD PRC_HQT_JKA_YHS_DEAL PROCEDUREZHCARD PRC_ISSU_ALL_BASE_0050 PROCEDUREZHCARD PRC_ISSU_ALL_BASE_0033 PROCEDURE行迁移和行链接查询统计select owner, table_name, tablespace_name, chain_cnt from dba_tables where chain_cnt >0;查询索引深度索引深度越小,对数据库影响较小select OWNER||'.'||index_name as "OWNER.INDEX_NAME", blevel from dba_indexes where blevel>=4 order by 2 desc;安全性管理拥用SYSDBA权限的用户列表：select * from v$pwfile_users;USERNAME SYSDBA SYSOPERSYS TRUE TRUE拥用DBA权限的用户列表：col admin_option for a12col default_role for a12select * from dba_role_privs where granted_role='DBA';GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLESYSTEM DBA YES YESZHCARD DBA NO YESSYSMAN DBA NO YESSYS DBA YES YES比较消耗资源的sqlPhysical Readsselect ername 用户名,a.disk_reads 磁盘读取量,a.executions 执行时间,a.disk_reads/decode(a.executions,0,1,a.executions) 单位读取数,a.sql_text SQL语句from v$sqlarea a,dba_users bwhere a.parsing_user_id = er_idand a.disk_reads >10000order by disk_reads desc;Buffer Getsselect ername 用户名,a.buffer_gets buffer读取量,a.executions 执行时间,a.buffer_gets/decode(a.executions,0,1,a.executions) 单位读取数,a.sql_text SQL语句from v$sqlarea a,dba_users bwhere a.parsing_user_id = er_idand a.buffer_gets >10000order by buffer_gets desc;查看某个表空间下有多少表select * from all_tables where tablespace_name='TRAIN';查看某个表空间下,某个用户有多少表select * from all_tables where tablespace_name='TRAIN' and owner='TRAIN'; 数据库已经安装的产品信息select * from v$option;查最近一周每天的归档日志生成量select logtime,count(*),round(sum(blocks * block_size) / 1024 / 1024) mbsizefrom (select trunc(first_time, 'dd') as logtime, a.BLOCKS, a.BLOCK_SIZEfrom v$archived_log awhere a.DEST_ID = 1and a.FIRST_TIME > trunc(sysdate - 7))group by logtimeorder by logtime desc;查当天每小时的各个实例的归档日志生成量select THREAD#,logtime,count(*),round(sum(blocks * block_size) / 1024 / 1024) mbsizefrom (select a.THREAD#,trunc(first_time, 'hh') as logtime,a.BLOCKS,a.BLOCK_SIZEfrom v$archived_log awhere a.DEST_ID = 1and a.FIRST_TIME > trunc(sysdate))group by THREAD#, logtimeorder by THREAD#, logtime desc;查最近一周每天的各个实例的归档日志生成量Sql代码select THREAD#,logtime,count(*),round(sum(blocks * block_size) / 1024 / 1024) mbsizefrom (select THREAD#,trunc(first_time, 'dd') as logtime,a.BLOCKS,a.BLOCK_SIZEfrom v$archived_log awhere a.DEST_ID = 1and a.FIRST_TIME > trunc(sysdate - 7))group by THREAD#, logtimeorder by THREAD#, logtime desc;无效JOB (BroKen/FAILURES)情况统计SELECT JOB, WHAT, NEXT_DATE, BROKEN, FAILURESFROM DBA_JOBSWHERE BROKEN = 'Y'OR FAILURES > 0无效对象情况统计SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIMEFROM DBA_OBJECTSWHERE STATUS = 'INVALID' and LAST_DDL_TIME>to_date('20100101','yyyymmdd')无效索引情况统计set linesize 300col index_name format a30col owner format a10col table_name format a30col tablesapce_name format a20select index_name,owner,table_name,tablespace_name from dba_indexes where owner not in ('SYS','SYSTEM') and status !='VALID';无效约束情况统计SELECT owner,constraint_name,table_name,constraint_type,statusFROM dba_constraintsWHERE status = 'DISABLED'所有在线实例区情况统计SELECTinst_id, instance_number inst_no, instance_name inst_name, parallel, status, database_status db_status, active_state state, host_name hostFROM gv$instanceORDER BY inst_id数据库SID，创建时间，日志归档模式select name, created, log_mode from v$database;数据库总数据量情况统计select round(sum(space)) all_space_M from(select sum(bytes)/1024/1024 space from dba_data_filesunion allselect nvl(sum(bytes)/1024/1024,0) space from dba_temp_filesunion allselect sum(bytes)/1024/1024 space from v$log)表空间使用率情况统计(MB)SELECT A.TABLESPACE_NAME,FILENUM,TOTAL "TOTAL (MB)",F.FREE "FREE (MB)",TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%",TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",ROUND(MAXSIZES, 2) "MAX (MB)"FROM (SELECT TABLESPACE_NAME,COUNT(FILE_ID) FILENUM,SUM(BYTES / (1024 * 1024)) TOTAL,SUM(MAXBYTES) / 1024 / 1024 MAXSIZESFROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FWHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME查询当前有几个用户正在使用临时表空间select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;查询表空间的使用情况包括临时表空间的情况SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)" FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILESGROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACEFROM DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)UNION ALL --if have tempfileSELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)", NVL(FREE_SPACE,0) "FREE_SPACE(M)"FROM(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILESGROUP BY TABLESPACE_NAME) D,(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE, ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACEFROM V$TEMP_SPACE_HEADERGROUP BY TABLESPACE_NAME) FWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)检查临时文件情况set linesize 200column file_name format a55column tablespace_name format a20select a.tablespace_name,a.file_name,round(a.bytes/(1024*1024*1024),2) total_g,round(sum(nvl(b.bytes,0))/(1024*1024*1024),2) free_g,round((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)),2) used_g,round(((a.bytes/(1024*1024*1024) - sum(nvl(b.bytes,0))/(1024*1024*1024)))/a.bytes/(1024*1024*1024),2) free_gfrom dba_temp_files a,dba_free_space bwhere a.file_id = b.file_id(+)group by a.tablespace_name,a.file_name,a.bytesorder by a.tablespace_name;检查哪个会话正在使用临时表空间SELECT ERNAME,S.SID || ',' || S.SERIAL# SID_SERIAL,T.TABLESPACE,T.BLOCKS * TBS.BLOCK_SIZE/1024/1024 USED_Mb,sum(T.BLOCKS * TBS.BLOCK_SIZE/1024/1024) over() total_used_mb,s.machine,s.program,Q.SQL_ID,Q.ADDRESS,Q.HASH_VALUE,Q.SQL_TEXT,T.segtype,T.contentsFROM V$SORT_USAGE T, V$SESSION S, V$SQLAREA Q, DBA_TABLESPACES TBS WHERE T.SESSION_ADDR = S.SADDRAND s.SQL_ADDRESS = Q.ADDRESSand s.SQL_HASH_VALUE=q.HASH_VALUEAND T.TABLESPACE = TBS.TABLESPACE_NAMEORDER BY T.BLOCKS * TBS.BLOCK_SIZE/1024/ 1024 DESC;数据库总连接数情况统计数据库总连接数情况统计---当前连接数------------select count(*) from v$sessionprompt ---最大连接数------------select value from v$parameter where name='processes';Redo Log 情况统计col member for a56select f.member "member",f.group# "group",l.bytes/1024/1024 "size",l.statusfrom v$logfile f, v$log lwhere f.group#=l.group#order by f.group#,f.memberIO情况检查col file_name for a46select file_name,fs.phyrds reads,fs.phywrts writes,(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds)) readtime,(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts)) writetimefromv$datafile df,v$filestat fswhere df.file#=fs.file#order by Shared Pool Size命中率select round((sum(gets)-sum(reloads))/sum(gets)*100,1) "libiary cache hit ratio %" from v$librarycache where namespacein ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER')数据字典命中率select round((1-sum(getmisses)/sum(gets))*100,1) "data dictionary hit ratio %" from v$rowcache锁竞争select substr(,1,25) Name,l.gets, l.misses,100*(l.misses/l.gets) "% Ratio (STAY UNDER 1%)"from v$latch l, v$latchname lnwhere in ('cache buffers lru chain')and tch# = tch#;排序命中率select a.value "Sort(Disk)", b.value "Sort(Memory)",round(100*(a.value/decode((a.value+b.value), 0,1,(a.value+b.value))),2) "% Ratio (STAY UNDER 5%)"from v$sysstat a, v$sysstat bwhere = 'sorts (disk)'and = 'sorts (memory)'DATA BUFFER数据缓冲区命中率select round((1-(phy.value/(cur.value+con.value)))*100,1)||'%' ratiofrom v$sysstat phy,v$sysstat cur,v$sysstat conwhere ='physical reads' and ='db block gets' and ='consistent gets' Miss LRU Hit命中率情况统计column "Miss LRU Hit%" format 99.9999999;col name format a40select name, (sleeps/gets) "Miss LRU Hit%"from v$latch where name ='cache buffers lru chain'检查内存排序性能select , to_char(value)from v$statname a, v$sysstatwhere a.statistic# = v$sysstat.statistic#and in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)')Redo Log Buffer Retry Ratio 情况统计select to_char(r.value/e.value) "redo log buffer retry ratio"from v$sysstat r,v$sysstat ewhere ='redo buffer allocation retries'and ='redo entries'等待事件检查情况统计select count(*) total_in_wait from v$session_waitwhere event='log buffer space';select event,total_waits,time_waited,average_waitfrom v$system_eventwhere event like '%undo%';select sid,seq#,event,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%'and event not like 'rdbms%'查询Lock锁情况统计SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, typeFROM V$LOCKWHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)ORDER BY id1, request检查是否存在死锁set linesize 200column oracle_username for a16column os_user_name for a12column object_name for a30SELECT l.xidusn, l.object_id,l.oracle_username,l.os_user_name,l.process,l.session_id,s.serial#, l.locked_mode,o.object_nameFROM v$locked_object l,dba_objects o,v$session swhere l.object_id = o.object_id and s.sid = l.session_id;select ername||' '||t2.sid||' '||t2.serial#||' '||t2.logon_time||' '||t3.sql_textfrom v$locked_object t1,v$session t2,v$sqltext t3where t1.session_id=t2.sidand t2.sql_address=t3.addressorder by t2.logon_time检查是否存在enqueueselect eq_type "lock",total_req# "gets",total_wait# "waits",cum_wait_time from v$enqueue_stat where total_wait#>0检查是否存在大量长事务set linesize 200column name for a16column username for a10select ,b.xacts,c.sid,c.serial#,ername,d.sql_textfrom v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction ewhere n=nand n=e.XIDUSNand c.taddr=e.addrand c.sql_address=d.ADDRESSand c.sql_hash_value=d.hash_valueorder by ,c.sid,d.piece检查是否存在大事务select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork))*100 ratio,messagefrom v$session_longopswhere message like '%RMAN%';select sid,serial#,to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time,sofar,totalwork,(sofar/decode(totalwork,0,1,totalwork))*100 ratio,messagefrom v$session_longopswhere sofar <> totalwork and (sofar/totalwork)*100 < 100;检查是否执行时间过长的事务set linesize 120column begin_time for a26column end_time for a26select a.recid,to_char(a.first_time,'yyyy-mm-dd hh24:mi:ss') begin_time,b.recid,to_char(b.first_time,'yyyy-mm-dd hh24:mi:ss') end_time,round((b.first_time - a.first_time)*24*60,2) minutesfrom v$log_history a,v$log_history bwhere b.recid = a.recid+1order by minutes检查SQL读磁盘的频率select ername,b.disk_reads,b.executions,round((b.disk_reads/decode(b.executions,0,1,b.executions)),2) disk_read_ratio,b.sql_textfrom dba_users a,v$sqlarea bwhere er_id = b.parsing_user_idand disk_reads > 5000检查数据文件IO情况col tbs for a12;col name for a46;select c.tablespace_name tbs,,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,a.phyblkrd,a.phyblkwrtfrom v$filestat a,v$datafile b,dba_data_files cwhere b.file# = a.file#and b.file# = c.file_idorder by tablespace_name,a.file#select ,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetimfrom v$filestat fs,v$dbfile dfwhere fs.file#=df.file# order by select substr(a.file#,1,2) "#", substr(,1,30) "Name",a.status, a.bytes,b.phyrds, b.phywrtsfrom v$datafile a, v$filestat bwhere a.file# = b.file#检查磁盘的IO情况select substr(,1,13) disk,c.tablespace_name,a.phyblkrd+a.phyblkwrt Total,a.phyrds,a.phywrts,a.phyblkrd,a.phyblkwrt,((a.readtim/decode(a.phyrds,0,1,a.phyblkrd))/100) avg_rd_time, ((a.writetim/decode(a.phywrts,0,1,a.phyblkwrt))/100) avg_wrt_timefrom v$filestat a,v$datafile b,dba_data_files cwhere b.file# = a.file#and b.file# = c.file_idorder by disk,c.tablespace_name,a.file#检查SQL的Buffer读取情况select ername,round(b.buffer_gets/(1024*1024),2) buffer_gets_M,b.sql_textfrom dba_users a,v$sqlarea bwhere er_id = b.parsing_user_idand b.buffer_gets > 5000000检查数据库大表分区情况（表行数大于500万）col table_name format a30col def_tablespace_name format a30col owner format a10select table_name,owner,def_tablespace_name,partition_countfrom dba_part_tables awhere a.table_name='select table_name from all_tables where num_rows>10000;检查数据库大表情况（表行数大于500万）set linesize 200col table_name format a30col owner format a20col tablespace_name format a30select table_name,owner,tablespace_name,num_rows from all_tables where num_rows>5000000查看碎片程度高的表col segment_name format a30col table_name format a30select segment_name table_name , COUNT(*) extentsfrom dba_segmentswhere owner NOT IN ('SYS', 'SYSTEM')group by segment_nameHAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM dba_segments GROUP BY segment_name) 查看占用CPU最多的sessionset linesize 100col status format 10col prog fommat a10col terminal format a30col osuser format a20select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 valuefrom v$session a,v$process b,v$sesstat cwhere c.statistic#=12 andc.sid=a.sid anda.paddr=b.addrorder by value desc;检查系统连接响应情况select METRIC_NAME,VALUE from SYS.V_$SYSMETRICwhere METRIC_NAME IN ('Database CPU Time Ratio','Database Wait Time Ratio')AND INTSIZE_CSEC = (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);检查数据库最近1小时的总性能和响应情况select end_time,valuefrom sys.v_$sysmetric_historywhere metric_name = 'Database CPU Time Ratio'order by 1;prompt *******下面的查询包含了更多的详细的响应时间数据。
