巡检脚本如下：
[oracle@oracle-db-19c check]$ ls -ltr
total 24
-rw-r--r--. 1 oracle oinstall 22753 Jan 5 16:12 ORAcheck.sql
[oracle@oracle-db-19c check]$ cat ORAcheck.sql
set heading off
select '一、数据库的基本情况' from dual;
set heading off
select '1、数据库版本' from dual;
set heading on
select * from v$version;
set heading off
select '2、查看数据库基本信息' from dual;
set heading on
set linesize 500
col host_name for a20
select dbid,name,instance_name,instance_name,version,parallel rac,host_name from v$database,v$instance;
set heading off
select '3、实例状态' from dual;
set heading on
select instance_number,instance_name ,status from gv$instance;
set heading off
select '4、数据库运行时间' from dual;
set heading on
select to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') 启动时间,
TRUNC(sysdate - (startup_time))||'天 '||TRUNC(24*((sysdate-startup_time) -TRUNC(sysdate-startup_time)))
||'小时 '||MOD(TRUNC(1440*((SYSDATE-startup_time)-
TRUNC(sysdate-startup_time))),60)
||'分 '||MOD(TRUNC(86400*((SYSDATE-STARTUP_TIME)-
TRUNC(SYSDATE-startup_time))),60)
||'秒' 运行时间
from v$instance;
set heading off
select '5、内存情况' from dual;
set heading on
select * from v$sgainfo;
set heading off
select '6、cpu情况' from dual;
set heading on
col STAT_NAME for a20
col COMMENTS for a50
Select stat_name,value,comments from v$osstat where stat_name in ('NUM_CPUS','IDLE_TIME','BUSY_TIME','USER_TIME','SYS_TIME','IOWAIT_TIME');
set heading off
select '二、检查Oracle对象状态' from dual;
set heading off
select '1、查看参数文件位置' from dual;
show parameter spfile
set heading off
col NAME for a50
select '2、查看控制文件' from dual;
set heading on
select status,name from v$controlfile;
set heading off
select '3、查看在线日志' from dual;
set heading on
col MEMBER for a50
select group#,status,type,member from v$logfile;
set heading off
select '4、检查日志切换频率' from dual;
set heading on
select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from v$log_history where first_time > sysdate - 1 order by first_time ,minutes;
set heading off
select '5、查看数据文件' from dual;
set heading on
col NAME for a50
select name,status from v$datafile;
set heading off
select '6、查看无效的对象' from dual;
set heading on
set linesize 500
select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';
set heading off
select '7、查看回滚段状态' from dual;
set heading on
select segment_name,status from dba_rollback_segs;
set heading off
select '8、检查是否有禁用约束' from dual;
set heading on
set linesize 1000
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P';
set heading off
select '9、检查是否有禁用触发器' from dual;
set heading on
col owner for a10
col taigger_name for a10
col table_name for a30
col table_name for a30
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';
set heading off
select '10、Oracle Job是否有失败' from dual;
set heading on
select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';
set heading off
select '11、检查失效的索引' from dual;
set heading on
select index_name,table_name,tablespace_name,status From dba_indexes Where owner='CTAIS2' And status<>'VALID';
set heading off
select '三、检查Oracle相关资源的使用情况' from dual;
set heading off
select '1、查看表空间的使用情况' from dual;
set heading on
set linesize 1000
SELECT UPPER(F.TABLESPACE_NAME) "tablespace_name",
D.TOT_GROOTTE_MB "tablesapce_size(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "used_tablespace_size(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "used%",
F.TOTAL_BYTES "free_size(M)",
F.MAX_BYTES "max_byte(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
set heading off
select '2、查看临时表空间使用情况' from dual;
set heading on
select tablespace_name , sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name;
set heading off
select '3、查看临时段使用的情况' from dual;
set heading on
COL username FORMAT a10;
COL segtype FORMAT a10;
SELECT username, segtype, extents "Extents Allocated"
,blocks "Blocks Allocated"
FROM v$tempseg_usage;
set heading off
select '4、查看所有数据文件i/o情况' from dual;
set heading on
SELECT ts.name AS ts, fs.phyrds "Reads", fs.phywrts "Writes"
,fs.phyblkrd AS br, fs.phyblkwrt AS bw
,fs.readtim "RTime", fs.writetim "WTime"
FROM v$tablespace ts, v$datafile df, v$filestat fs
WHERE ts.ts# = df.ts# AND df.file# = fs.file#
UNION
SELECT ts.name AS ts, ts.phyrds "Reads", ts.phywrts "Writes"
,ts.phyblkrd AS br, ts.phyblkwrt AS bw
,ts.readtim "RTime", ts.writetim "WTime"
FROM v$tablespace ts, v$tempfile tf, v$tempstat ts
WHERE ts.ts# = tf.ts# AND tf.file# = ts.file# ORDER BY 1;
set heading off
select '5、查看top 10 热segment' from dual;
set heading on
col objct_name for a30
col OWNER for a20
select * from
(select
ob.owner, ob.object_name, sum(b.tch) Touchs
from x$bh b , dba_objects ob
where b.obj = ob.data_object_id
and b.ts# > 0
group by ob.owner, ob.object_name
order by sum(tch) desc)
where rownum <=10;
set heading off
select '6、查看物理读最多的object' from dual;
set heading on
select * from (select owner,object_name,value from v$segment_statistics where statistic_name='physical reads' order by value desc) where rownum<=10;
set heading off
select '7、查看热点数据文件(从单块读取时间判断)' from dual;
set heading on
SELECT t.file_name,
t.tablespace_name,
round(s.singleblkrdtim / s.singleblkrds, 2) AS CS,
s.READTIM,
s.WRITETIM
FROM v$filestat s, dba_data_files t
WHERE s.file# = t.file_id and rownum<=10 order by cs desc;
set heading off
select '8、检查Oracle初始化文件中相关参数值' from dual;
set heading on
select resource_name,max_utilization,initial_allocation,
limit_value from v$resource_limit;
set heading off
select '注：若LIMIT_VALU - MAX_UTILIZATION<=5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。可以通过参数文件调整。' from dual;
set heading off
select '9、检查数据库连接情况' from dual;
set heading on
select sid,serial#,username,program,machine,status from v$session;
set heading off
select "(注：杀掉会话的语句alter system kill session 'SID,SERIAL#')" from dual;
set heading off
select '10、查看热点数据文件' from dual;
set heading on
SELECT t.file_name,
t.tablespace_name,
round(s.singleblkrdtim / s.singleblkrds, 2) AS CS,
s.READTIM,
s.WRITETIM
FROM v$filestat s, dba_data_files t
WHERE s.file# = t.file_id and rownum<=10 order by cs desc;
set heading off
select ' 11、检查一些扩展异常的对象 ' from dual;
set heading on
select Segment_Name, Segment_Type, TableSpace_Name,
(Extents/Max_extents)*100 Percent
From sys.DBA_Segments
Where Max_Extents != 0 and (Extents/Max_extents)*100>=95
order By Percent;
set heading off
select ' 12、检查system表空间内的内容 ' from dual;
set heading on
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM'
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM';
set heading off
select ' 13、检查对象的下一扩展与表空间的最大扩展值 ' from dual;
set heading on
select a.table_name, a.next_extent, a.tablespace_name
from all_tables a,
(select tablespace_name, max(bytes) as big_chunk
from dba_free_space
group by tablespace_name ) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk
union
select a.index_name, a.next_extent, a.tablespace_name
from all_indexes a,
(select tablespace_name, max(bytes) as big_chunk
from dba_free_space
group by tablespace_name ) f
where f.tablespace_name = a.tablespace_name
and a.next_extent > f.big_chunk;
set heading off
select '四、内存的具体查看' from dual;
set heading off
select ' 1、查看内存占用各个池子大小' from dual;
set heading on
COL name FORMAT a32;
SELECT pool, name, bytes FROM v$sgastat
WHERE pool IS NULL
OR pool != 'shared pool' OR (pool = 'shared pool'
AND (name IN('dictionary cache','enqueue','library cache','parameters','processes','sessions','free memory')))
ORDER BY pool DESC NULLS FIRST, name;
set heading off
select ' 2、检查shered pool free space ' from dual;
set heading on
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';
set heading off
select ' 3、检查shared pool中library cach ' from dual;
set heading on
select namespace,pinhitratio from v$librarycache;
set heading off
select ' 4、检查整体命中率(library cache)' from dual;
set heading on
select sum(pins) "hits",
sum(reloads) "misses",
sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio"
from v$librarycache;
set heading off
select ' 5、library cache中详细比率信息' from dual;
set heading on
SELECT 'Library Lock Requests' "Ratio"
, ROUND(AVG(gethitratio) * 100, 2)
||'%' "Percentage" FROM V$LIBRARYCACHE
UNION
SELECT 'Library Pin Requests' "Ratio"
,ROUND(AVG(pinhitratio)* 100, 2)
||'%' "Percentage" FROM V$LIBRARYCACHE
UNION
SELECT 'Library I/O Reloads' "Ratio"
, ROUND((SUM(reloads) / SUM(pins)) * 100, 2)
||'%' "Percentage" FROM V$LIBRARYCACHE
UNION
SELECT 'Library Reparses' "Ratio"
, ROUND((SUM(reloads) / SUM(pins)) * 100, 2)
||'%' "Percentage" FROM V$LIBRARYCACHE;
set heading off
select ' 6、检查数据字典的命中率' from dual;
set heading on
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
set heading off
select '注：row cache的命中率至少小于90%' from dual;
set heading off
select ' 7、每个子shared pool由 单独的shared pool latch保护 查看 他们的命中率 ' from dual;
set heading on
col name format a15
select addr,name,gets,misses,1-misses/gets from v$latch_children where name='shared pool';
set heading off
select ' 8、查看shared pool建议' from dual;
set heading on
column c1 heading 'Pool |Size(M)'
column c2 heading 'Size|Factor'
column c3 heading 'Est|LC(M) '
column c4 heading 'Est LC|Mem. Obj.'
column c5 heading 'Est|Time|Saved|(sec)'
column c6 heading 'Est|Parse|Saved|Factor'
column c7 heading 'Est|Object Hits' format 999,999,999
SELECT shared_pool_size_for_estimate c1,shared_pool_size_factor c2,
estd_lc_size c3,estd_lc_memory_objects c4,estd_lc_time_saved c5,
estd_lc_time_saved_factor c6,to_char(estd_lc_memory_object_hits,99999999999) c7 FROM V$SHARED_POOL_ADVICE;
set heading off
select ' 9、查看shared pool中 各种类型的chunk的大小数量' from dual;
set heading on
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIzE"
FROM X$KSMSP GROUP BY KSMCHCLS;
set heading off
select ' 10、查看使用shard_pool保留池情况' from dual;
set heading on
SELECT request_misses, request_failures, free_space
FROM v$shared_pool_reserved;
set heading off
select '11、 pga 建议' from dual;
set heading on
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'pga_aggregate_target') "Current Mb"
, ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb"
, ROUND(estd_pga_cache_hit_percentage) "%"
FROM v$pga_target_advice
ORDER BY 2;
set heading off
select ' 12、查看buffer cache 命中率' from dual;
set heading on
select 1-(sum(decode(name, 'physical reads', value, 0))/
(sum(decode(name, 'db block gets', value, 0))+
(sum(decode(name, 'consistent gets', value, 0))))) "Buffer Hit Ratio"
from v$sysstat;
set heading off
select ' 13、查看buffer cache设置大小建议' from dual;
set heading on
select size_for_estimate, estd_physical_read_factor, to_char(estd_physical_reads,99999999999999999999999) as"estd_physical_reads" from v$db_cache_advice where name = 'DEFAULT';
set heading off
select '14、查看buffer cache中defalut pool 命中率' from dual;
set heading on
select name,1-(physical_reads)/(consistent_gets+db_block_gets)
from v$buffer_pool_statistics;
set heading off
select '注：default池命中率至少要大于90%' from dual;
set heading off
select '15、检查lgwr i/o性能' from dual;
set heading on
select total_waits,time_waited,average_wait,time_waited/total_waits as avg from v$system_event where event = 'log file parallel write';
set heading off
select '16、检查与redo相关性能指标' from dual;
set heading on
set linesize 500
select name,value from v$sysstat where name like '%redo%';
set heading off
select ' 17、查询redo block size' from dual;
set heading on
select max(lebsz) from x$kccle;
set heading off
select '18、 计算出每个事务平均处理多少个redo block' from dual;
set heading on
select a.redoblocks/b.trancount from (select value redoblocks from v$sysstat where name='redo blocks written') a ,(select value trancount from v$sysstat where name='user commits') b;
set heading off
select ' 19、 检查undo rollback segment 使用情况' from dual;
set heading on
col name for a60
select name ,rssize,extents,latch,xacts,writes,gets,waits from v$rollstat a,v$rollname b where a.usn=b.usn order by waits desc;
set heading off
select ' 20、计算每秒钟产生的undoblk数量' from dual;
set heading on
select sum(undoblks)/sum((end_time-begin_time)*24*60*60) from v$undostat;
set heading off
select ' 21、查询undo具体信息' from dual;
set heading on
COL undob FORMAT 99990;
COL trans FORMAT 99990;
COL snapshot2old FORMAT 9999999990;
SELECT undoblks "UndoB", txncount "Trans"
,maxquerylen "LongestQuery", maxconcurrency "MaxConcurrency"
,ssolderrcnt "Snapshot2Old", nospaceerrcnt "FreeSpaceWait"
FROM v$undostat;
set heading off
select ' 22、查询rollback 段详细信息(收缩次数,扩展次数,平均活动事务等)' from dual;
set heading on
COL RBS FORMAT a50;
SELECT n.name "RBS", s.extends "Extends", s.shrinks "Shrinks"
,s.wraps "Wraps", s.aveshrink "AveShrink"
,s.aveactive "AveActive"
FROM v$rollname n JOIN v$rollstat s USING(usn)
WHERE n.name != 'SYSTEM';
set heading off
select ' 23、查询当前rollback segment使用情况' from dual;
set heading on
COL RBS FORMAT a50;
SELECT n.name "RBS", s.status, s.waits, s.gets, to_char(s.writes,'9999999999999')
,s.xacts "Active Trans"
FROM v$rollname n JOIN v$rollstat s USING(usn)
WHERE n.name != 'SYSTEM';
set heading off
select '24、查询使用rollback segment时等待比率' from dual;
set heading on
SELECT ROUND(SUM(waits/gets)*100,2)||'%' "Contention" FROM v$rollstat;
set heading off
select '25、查询使用rollback segment时等待比率及其平局活动事务数' from dual;
set heading on
COL contention FORMAT 9999999990;
SELECT AVG(xacts) "Trans per RBS"
,ROUND(SUM(waits/gets)*100,2)||'%' "Contention"
FROM v$rollstat;
set heading off
select '五、检查Oracle数据库性能' from dual;
set heading off
select '1、检查数据库的等待事件' from dual;
set heading on
set pages 80
set lines 120
col event for a40
select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like 'SQL%' and event not like 'rdbms%';
set heading off
select '2、查看与redo相关等待事件' from dual;
set heading on
col event format a40
select event,total_waits,total_timeouts,average_wait from v$system_event where upper(event) like'%REDO%';
set heading off
select '3、查看session redo event' from dual;
set heading on
select event,total_waits,total_timeouts,average_wait from v$session_event where upper(event) like'%REDO%';
set heading off
select '4、Disk Read最高的SQL语句的获取' from dual;
set heading on
SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM<=5 order by SQL_TEXT desc
set heading off
select '5、查找前十条性能差的sql' from dual;
set heading on
SELECT * FROM (SELECT PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC) WHERE ROWNUM<10 ;
set heading off
select '6、等待时间最多的5个系统等待事件的获取' from dual;
set heading on
SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
set heading off
select '7、检查运行很久的SQL' from dual;
set heading on
COLUMN USERNAME FORMAT A12
COLUMN OPNAME FORMAT A16
COLUMN PROGRESS FORMAT A8
SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) || '%' AS PROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING <> 0 AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE = HASH_VALUE;
set heading off
select '9、检查碎片程度高的表' from dual;
set heading on
SELECT segment_name table_name,COUNT(*) extents FROM dba_segments WHERE owner NOT IN
('SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT(*)=(SELECT MAX(COUNT(*))
FROM dba_segments GROUP BY segment_name);
set heading off
select '10、检查死锁及处理' from dual;
set heading on
col sid for 999999
col username for a10
col schemaname for a10
col osuser for a16
col machine for a16
col terminal for a20
col owner for a10
col object_name for a30
col object_type for a10
select sid,serial#,username,SCHEMANAME,osuser,MACHINE,
terminal,PROGRAM,owner,object_name,object_type,o.object_id
from dba_objects o,v$locked_object l,v$session s
where o.object_id=l.object_id and s.sid=l.session_id;
set heading off
select '11、查看数据库中行chain' from dual;
set heading on
SELECT 'Chained Rows ' "Ratio"
, ROUND((SELECT SUM(value) FROM V$SYSSTAT
WHERE name = 'table fetch continued row')/ (SELECT SUM(value) FROM V$SYSSTAT
WHERE name IN ('table scan rows gotten', 'table fetch by rowid'))* 100, 3)||'%' "Percentage"
FROM DUAL;
set heading off
select '12、查询解析比率' from dual;
set heading on
SELECT 'Soft Parses ' "Ratio", ROUND(((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (total)')- (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (hard)'))
/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count')* 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Hard Parses ' "Ratio"
, ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (hard)')/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'execute count') * 100, 2)||'%' "Percentage"
FROM DUAL
UNION
SELECT 'Parse Failures ' "Ratio"
, ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (failures)')/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'parse count (total)')* 100, 5)||'%' "Percentage"
FROM DUAL;
set heading off
select '13、查看与latch有关的event信息' from dual;
set heading on
COL event FORMAT a20;
COL waits FORMAT 9999990;
COL timeouts FORMAT 99999990;
COL average FORMAT 99999990;
SELECT event "Event", time_waited "Total Time", total_waits "Waits"
,average_wait "Average", total_timeouts "Timeouts"
FROM V$SYSTEM_EVENT
WHERE event = 'latch free'
ORDER BY EVENT;
set heading off
select '14、查看大表小表扫描对应的值' from dual;
set heading on
SELECT value, name FROM V$SYSSTAT WHERE name IN ('table fetch by rowid', 'table scans (short tables)', 'table scans (long tables)');
SELECT 'Short to Long Full Table Scans' "Ratio", ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table scans (short tables)')/
(SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scans (short tables)', 'table scans (long tables)'))* 100, 2)||'%' "Percentage" FROM DUAL
UNION
SELECT 'Short Table Scans ' "Ratio", ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table scans (short tables)')/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))* 100, 2)||'%' "Percentage" FROM DUAL
UNION
SELECT 'Long Table Scans ' "Ratio", ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table scans (long tables)')/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))* 100, 2)||'%' "Percentage" FROM DUAL
UNION
SELECT 'Table by Index ' "Ratio", ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'table fetch by rowid')/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))* 100, 2)||'%' "Percentage" FROM DUAL
UNION
SELECT 'Efficient Table Access ' "Ratio", ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name IN ('table scans (short tables)','table fetch by rowid'))/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
('table scans (short tables)', 'table scans (long tables)', 'table fetch by rowid'))* 100, 2)||'%' "Percentage" FROM DUAL;
set heading off
select '15、index使用比率' from dual;
set heading on
col name for a30
SELECT to_char(value,'999999999999999999999'), name FROM V$SYSSTAT WHERE name IN ('table fetch by rowid', 'table scans (short tables)', 'table scans (long tables)') OR name LIKE 'index fast full%' OR name = 'index fetch by key';
SELECT 'Index to Table Ratio ' "Ratio" , ROUND((SELECT SUM(value) FROM V$SYSSTAT WHERE name LIKE 'index fast full%' OR name = 'index fetch by key' OR name = 'table fetch by rowid')/ (SELECT SUM(value) FROM V$SYSSTAT WHERE name IN
('table scans (short tables)', 'table scans (long tables)')),0)||':1' "Result" FROM DUAL;
set heading off
select '16、等待class' from dual;
set heading on
col wait_class for a30
SELECT wait_class, COUNT(wait_class) FROM v$system_event
GROUP BY wait_class ORDER BY 1;
[oracle@oracle-db-19c check]$ 巡检结果如下：
oracle@oracle-db-19c check]$ pwd
/home/oracle/scripts/check
[oracle@oracle-db-19c check]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 5 16:31:38 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter session set container=PDB1;
Session altered.
SQL> @/home/oracle/scripts/check/ORAcheck.sql
一、数据库的基本情况
1、数据库版本
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
CON_ID
----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
0
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
CON_ID
----------
2、查看数据库基本信息
DBID NAME INSTANCE_NAME INSTANCE_NAME VERSION RAC HOST_NAME
---------- --------------------------- ------------------------------------------------ ------------------------------------------------ --------------------------------------------------- --------- --------------------
1093429351 CDB1 cdb1 cdb1 19.0.0.0.0 NO oracle-db-19c
3、实例状态
INSTANCE_NUMBER INSTANCE_NAME STATUS
--------------- ------------------------------------------------ ------------------------------------
1 cdb1 OPEN
4、数据库运行时间
启动时间
---------------------------------------------------------------------------------------
运行时间
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
03-JAN-2023 11:15:31
2天 5小时 16分 21秒
5、内存情况
NAME BYTES RESIZEABL CON_ID
------------------------------------------------------------------------------------------------ ---------- --------- ----------
Fixed SGA Size 9137968 No 0
Redo Buffers 16027648 No 0
Buffer Cache Size 306184192 Yes 0
In-Memory Area Size 0 No 0
Shared Pool Size 293601280 Yes 0
Large Pool Size 4194304 Yes 0
Java Pool Size 0 Yes 0
Streams Pool Size 0 Yes 0
Shared IO Pool Size 25165824 Yes 0
Data Transfer Cache Size 0 Yes 0
Granule Size 4194304 No 0
NAME BYTES RESIZEABL CON_ID
------------------------------------------------------------------------------------------------ ---------- --------- ----------
Maximum SGA Size 629145392 No 0
Startup overhead in Shared Pool 193465328 No 0
Free SGA Memory Available 0 0
14 rows selected.
6、cpu情况
STAT_NAME VALUE COMMENTS
-------------------- ---------- --------------------------------------------------
NUM_CPUS 4 Number of active CPUs
IDLE_TIME 25618235 Time (centi-secs) that CPUs have been in the idle
state
BUSY_TIME 240493 Time (centi-secs) that CPUs have been in the busy
state
USER_TIME 82238 Time (centi-secs) spent in user code
SYS_TIME 29393 Time (centi-secs) spent in the kernel
IOWAIT_TIME 106576 Time (centi-secs) spent waiting for IO
6 rows selected.
二、检查Oracle对象状态
1、查看参数文件位置
spfile string /u01/app/oracle/product/19.3.0
/dbhome_1/dbs/spfilecdb1.ora
2、查看控制文件
STATUS NAME
--------------------- --------------------------------------------------
/u02/oradata/CDB1/control01.ctl
/u02/oradata/CDB1/control02.ctl
3、查看在线日志
GROUP# STATUS TYPE MEMBER
---------- --------------------- --------------------- --------------------------------------------------
3 ONLINE /u02/oradata/CDB1/redo03.log
2 ONLINE /u02/oradata/CDB1/redo02.log
1 ONLINE /u02/oradata/CDB1/redo01.log
4、检查日志切换频率
SEQUENCE# FIRSTTIME MINUTES
---------- --------------------------------------------------- ----------
99 20230104_20:07:02
100 20230104_22:00:39 113.62
101 20230105_10:44:24 763.75
102 20230105_10:58:45 14.35
103 20230105_12:00:42 61.95
5、查看数据文件
NAME STATUS
-------------------------------------------------- ---------------------
/u02/oradata/CDB1/pdb1/system01.dbf SYSTEM
/u02/oradata/CDB1/pdb1/sysaux01.dbf ONLINE
/u02/oradata/CDB1/pdb1/undotbs01.dbf ONLINE
/u02/oradata/CDB1/pdb1/users01.dbf ONLINE
/u02/oradata/CDB1/pdb1/data01.dbf ONLINE
/u02/oradata/CDB1/pdb1/TBS_MAXWELL_01.dbf ONLINE
/u02/oradata/CDB1/pdb1/undotbs02.dbf ONLINE
7 rows selected.
6、查看无效的对象
OWNER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT_NAME OBJECT_TYPE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------
CO
CUSTOMER_ORDER_PRODUCTS VIEW
CO
STORE_ORDERS VIEW
CO
PRODUCT_ORDERS VIEW
OWNER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT_NAME OBJECT_TYPE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------
SCOTT
PROC3 PROCEDURE
SCOTT
PROC4 PROCEDURE
7、查看回滚段状态
SEGMENT_NAME STATUS
------------------------------------------------------------------------------------------ ------------------------------------------------
SYSTEM ONLINE
_SYSSMU1_3588498444$ ONLINE
_SYSSMU2_2971032042$ ONLINE
_SYSSMU3_3657342154$ ONLINE
_SYSSMU4_811969446$ ONLINE
_SYSSMU5_3018429039$ ONLINE
_SYSSMU6_442110264$ ONLINE
_SYSSMU7_2728255665$ ONLINE
_SYSSMU8_801938064$ ONLINE
_SYSSMU9_647420285$ ONLINE
_SYSSMU10_2262159254$ ONLINE
SEGMENT_NAME STATUS
------------------------------------------------------------------------------------------ ------------------------------------------------
_SYSSMU11_469748424$ OFFLINE
_SYSSMU12_4253293703$ OFFLINE
_SYSSMU13_1277683762$ OFFLINE
_SYSSMU14_3894550872$ OFFLINE
_SYSSMU15_459268231$ OFFLINE
_SYSSMU16_3099260925$ OFFLINE
_SYSSMU17_3739318309$ OFFLINE
_SYSSMU18_2688946954$ OFFLINE
_SYSSMU19_3392187082$ OFFLINE
_SYSSMU20_3044228317$ OFFLINE
21 rows selected.
8、检查是否有禁用约束
no rows selected
9、检查是否有禁用触发器
OWNER TRIGGER_NAME TABLE_NAME STATUS
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------ ------------------------
HR SECURE_EMPLOYEES EMPLOYEES DISABLED
SYS SYSLSBY_EDS_DDL_TRIG DISABLED
SYS LOGMNRGGC_TRIGGER DISABLED
WMSYS NO_VM_DDL DISABLED
WMSYS NO_VM_DROP_A DISABLED
MDSYS SDO_TOPO_DROP_FTBL DISABLED
LBACSYS LBAC$AFTER_DROP DISABLED
LBACSYS LBAC$BEFORE_ALTER DISABLED
LBACSYS LBAC$AFTER_CREATE DISABLED
9 rows selected.
10、Oracle Job是否有失败
no rows selected
11、检查失效的索引
no rows selected
三、检查Oracle相关资源的使用情况
1、查看表空间的使用情况
tablespace_name tablesapce_size(M) used_tablespace_size(M) used% free_size(M) max_byte(M)
------------------------------------------------------------------------------------------ ------------------ ----------------------- --------------------- ------------ -----------
SYSTEM 290 282.37 97.37 7.63 7
USERS 221.25 199.25 90.06 22 21.25
SYSAUX 440 393.75 89.49 46.25 27.75
UNDOTBS1 165 91.25 55.30 73.75 24
TBS_MAXWELL 100 13 13.00 87 87
UNDOTBS2 100 2.25 2.25 97.75 97.75
DATA01 50 1 2.00 49 49
7 rows selected.
2、查看临时表空间使用情况
TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------------------------------------------------------------------ --------------------
TEMP_MAXWELL 100
TEMP02 5
TEMP 36
3、查看临时段使用的情况
no rows selected
4、查看所有数据文件i/o情况
TS Reads Writes BR BW RTime WTime
------------------------------------------------------------------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
DATA01 157 0 19204 0 1529 0
SYSAUX 18550 2200 190868 2907 10938 743
SYSTEM 16313 2057 140263 3429 10952 592
TBS_MAXWELL 626 879 31697 12475 1988 25
TEMP 36 36 1034 1051 0 54
TEMP02 3 0 3 0 0 0
TEMP_MAXWELL 308 911 370 5626 10 312
UNDOTBS1 586 4017 63448 21566 3706 279
UNDOTBS2 206 17 25604 136 2296 1
USERS 809 293 84631 3808 2649 9
10 rows selected.
5、查看top 10 热segment
OWNER OBJECT_NAME TOUCHS
-------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------
MAXWELLPAN T 1446
SYS SMON_SCN_TO_TIME_AUX 396
SYS SMON_SCN_TIME 396
SYS WRI$_OPTSTAT_HISTGRM_HISTORY 231
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY 132
SYS I_WRI$_OPTSTAT_OPR_TASKS_STIME 74
SYS SCHEDULER$_EVENT_LOG 73
SYS STATS_TARGET$ 72
SYS I_WRI$_OPTSTAT_TAB_ST 52
SYS WRI$_OPTSTAT_OPR 46
10 rows selected.
6、查看物理读最多的object
OWNER OBJECT_NAME VALUE
-------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------
SYS TAB$ 5840
SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 4254
SYS HISTGRM$ 3543
SYS OBJ$ 3415
SYS I_OBJ2 3058
SYS I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST 2640
SYS I_WRI$_OPTSTAT_H_ST 2373
SYS I_WRI$_OPTSTAT_HH_ST 2197
SYS HIST_HEAD$ 1791
SYS SMON_SCN_TIME 1566
10 rows selected.
7、查看热点数据文件(从单块读取时间判断)
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME CS READTIM WRITETIM
------------------------------------------------------------------------------------------ ---------- ---------- ----------
/u02/oradata/CDB1/pdb1/undotbs01.dbf
UNDOTBS1 .51 3706 279
/u02/oradata/CDB1/pdb1/system01.dbf
SYSTEM .5 10952 592
/u02/oradata/CDB1/pdb1/sysaux01.dbf
SYSAUX .33 10938 743
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME CS READTIM WRITETIM
------------------------------------------------------------------------------------------ ---------- ---------- ----------
/u02/oradata/CDB1/pdb1/users01.dbf
USERS .1 2649 9
/u02/oradata/CDB1/pdb1/TBS_MAXWELL_01.dbf
TBS_MAXWELL .03 1988 25
/u02/oradata/CDB1/pdb1/data01.dbf
DATA01 0 1529 0
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME CS READTIM WRITETIM
------------------------------------------------------------------------------------------ ---------- ---------- ----------
/u02/oradata/CDB1/pdb1/undotbs02.dbf
UNDOTBS2 0 2296 1
7 rows selected.
8、检查Oracle初始化文件中相关参数值
no rows selected
注：若LIMIT_VALU - MAX_UTILIZATION<=5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。可以通过参数文件调整。
9、检查数据库连接情况
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---------- ---------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
1 29750 oracle@oracle-db-19c (PSP0) oracle-db-19c ACTIVE
2 35878 oracle@oracle-db-19c (TT00) oracle-db-19c ACTIVE
3 59608 SYS oracle@oracle-db-19c (OFSD) oracle-db-19c ACTIVE
4 8745 oracle@oracle-db-19c (SVCB) oracle-db-19c ACTIVE
5 38313 oracle@oracle-db-19c (LGWR) oracle-db-19c ACTIVE
6 36347 oracle@oracle-db-19c (LG01) oracle-db-19c ACTIVE
7 7077 oracle@oracle-db-19c (LREG) oracle-db-19c ACTIVE
8 53079 oracle@oracle-db-19c (MMON) oracle-db-19c ACTIVE
11 7662 oracle@oracle-db-19c (ARC1) oracle-db-19c ACTIVE
15 4302 oracle@oracle-db-19c (AQPC) oracle-db-19c ACTIVE
17 47699 oracle@oracle-db-19c (QM02) oracle-db-19c ACTIVE
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---------- ---------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
18 43785 SYS sqlplus@oracle-db-19c (TNS V1-V3) oracle-db-19c ACTIVE
21 23486 oracle@oracle-db-19c (W003) oracle-db-19c ACTIVE
127 48823 oracle@oracle-db-19c (VKTM) oracle-db-19c ACTIVE
128 10533 oracle@oracle-db-19c (GEN1) oracle-db-19c ACTIVE
129 25740 oracle@oracle-db-19c (SCMN) oracle-db-19c ACTIVE
130 11277 oracle@oracle-db-19c (PMAN) oracle-db-19c ACTIVE
131 12651 oracle@oracle-db-19c (CKPT) oracle-db-19c ACTIVE
132 12336 oracle@oracle-db-19c (SMCO) oracle-db-19c ACTIVE
135 18763 oracle@oracle-db-19c (MMNL) oracle-db-19c ACTIVE
136 31640 oracle@oracle-db-19c (Q003) oracle-db-19c ACTIVE
138 57758 oracle@oracle-db-19c (ARC2) oracle-db-19c ACTIVE
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---------- ---------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
140 44325 oracle@oracle-db-19c (W007) oracle-db-19c ACTIVE
146 5498 oracle@oracle-db-19c (Q001) oracle-db-19c ACTIVE
147 51143 oracle@oracle-db-19c (M000) oracle-db-19c ACTIVE
151 15680 oracle@oracle-db-19c (W001) oracle-db-19c ACTIVE
152 62718 oracle@oracle-db-19c (M002) oracle-db-19c ACTIVE
253 65512 oracle@oracle-db-19c (PMON) oracle-db-19c ACTIVE
254 51127 oracle@oracle-db-19c (GEN0) oracle-db-19c ACTIVE
255 15463 oracle@oracle-db-19c (SCMN) oracle-db-19c ACTIVE
256 2716 oracle@oracle-db-19c (DBRM) oracle-db-19c ACTIVE
257 36799 oracle@oracle-db-19c (DIA0) oracle-db-19c ACTIVE
258 41365 oracle@oracle-db-19c (LG00) oracle-db-19c ACTIVE
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---------- ---------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
259 1193 oracle@oracle-db-19c (RECO) oracle-db-19c ACTIVE
260 19373 oracle@oracle-db-19c (PXMN) oracle-db-19c ACTIVE
261 18301 oracle@oracle-db-19c (ARC0) oracle-db-19c ACTIVE
262 63688 oracle@oracle-db-19c (ARC3) oracle-db-19c ACTIVE
266 34589 oracle@oracle-db-19c (CJQ0) oracle-db-19c ACTIVE
267 65108 oracle@oracle-db-19c (M004) oracle-db-19c ACTIVE
268 32215 oracle@oracle-db-19c (W004) oracle-db-19c ACTIVE
272 55676 oracle@oracle-db-19c (W000) oracle-db-19c ACTIVE
279 58495 oracle@oracle-db-19c (M003) oracle-db-19c ACTIVE
379 15127 oracle@oracle-db-19c (TT01) oracle-db-19c ACTIVE
380 5790 oracle@oracle-db-19c (CLMN) oracle-db-19c ACTIVE
SID SERIAL# USERNAME PROGRAM MACHINE STATUS
---------- ---------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------
381 33762 oracle@oracle-db-19c (MMAN) oracle-db-19c ACTIVE
382 32093 oracle@oracle-db-19c (DIAG) oracle-db-19c ACTIVE
383 2250 oracle@oracle-db-19c (VKRM) oracle-db-19c ACTIVE
384 25003 oracle@oracle-db-19c (DBW0) oracle-db-19c ACTIVE
385 47308 oracle@oracle-db-19c (SMON) oracle-db-19c ACTIVE
386 59203 oracle@oracle-db-19c (W002) oracle-db-19c ACTIVE
388 54385 oracle@oracle-db-19c (TMON) oracle-db-19c ACTIVE
389 17391 oracle@oracle-db-19c (TT02) oracle-db-19c ACTIVE
395 6039 oracle@oracle-db-19c (W005) oracle-db-19c ACTIVE
399 56366 oracle@oracle-db-19c (W006) oracle-db-19c ACTIVE
54 rows selected.
select "(注：杀掉会话的语句alter system kill session 'SID,SERIAL#')" from dual
*
ERROR at line 1:
ORA-00904: "(注：杀掉会话的语句alter system kill session 'SID,SERIAL#')": invalid identifier
10、查看热点数据文件
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME CS READTIM WRITETIM
------------------------------------------------------------------------------------------ ---------- ---------- ----------
/u02/oradata/CDB1/pdb1/undotbs01.dbf
UNDOTBS1 .51 3706 279
/u02/oradata/CDB1/pdb1/system01.dbf
SYSTEM .5 10952 592
/u02/oradata/CDB1/pdb1/sysaux01.dbf
SYSAUX .33 10938 743
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME CS READTIM WRITETIM
------------------------------------------------------------------------------------------ ---------- ---------- ----------
/u02/oradata/CDB1/pdb1/users01.dbf
USERS .1 2649 9
/u02/oradata/CDB1/pdb1/TBS_MAXWELL_01.dbf
TBS_MAXWELL .03 1988 25
/u02/oradata/CDB1/pdb1/data01.dbf
DATA01 0 1529 0
FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLESPACE_NAME CS READTIM WRITETIM
------------------------------------------------------------------------------------------ ---------- ---------- ----------
/u02/oradata/CDB1/pdb1/undotbs02.dbf
UNDOTBS2 0 2296 1
7 rows selected.
11、检查一些扩展异常的对象
no rows selected
12、检查system表空间内的内容
OWNER
--------------------
GSMADMIN_INTERNAL
LBACSYS
OJVMSYS
ORDDATA
ORDSYS
OUTLN
SCOTT
7 rows selected.
13、检查对象的下一扩展与表空间的最大扩展值
no rows selected
四、内存的具体查看
1、查看内存占用各个池子大小
POOL NAME BYTES
------------------------------------------ -------------------------------- ----------
buffer_cache 281018368
fixed_sga 9137968
log_buffer 16027648
shared_io_pool 25165824
shared pool free memory 17798368
large pool free memory 3702784
6 rows selected.
2、检查shered pool free space
POOL NAME BYTES CON_ID
------------------------------------------ -------------------------------- ---------- ----------
shared pool free memory 17634856 0
3、检查shared pool中library cach
NAMESPACE PINHITRATIO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -----------
SQL AREA .873216801
TABLE/PROCEDURE .876346328
BODY .877026916
TRIGGER .666189795
INDEX .580197676
CLUSTER .979467704
DIRECTORY .28
QUEUE .12962963
RULESET 0
XML SCHEMA 1
SUBSCRIPTION 0
NAMESPACE PINHITRATIO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -----------
TEMPORARY TABLE 0
TEMPORARY INDEX 0
EDITION .866374269
DBLINK 1
OBJECT ID 1
SCHEMA 1
DBINSTANCE 1
SQL AREA STATS .030114136
ACCOUNT_STATUS 1
SQL AREA BUILD 1
PDB 1
NAMESPACE PINHITRATIO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -----------
AUDIT POLICY .950545068
USER PRIVILEGE .115384615
PDBOPER 1
FED APP .929759704
APP STATE 1
CMP .043243243
28 rows selected.
4、检查整体命中率(library cache)
hits misses Hits Ratio
---------- ---------- ----------
5205581 81257 .984630322
5、library cache中详细比率信息
Ratio Percentage
--------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------
Library I/O Reloads 1.56%
Library Lock Requests 50.52%
Library Pin Requests 64.99%
Library Reparses 1.56%
6、检查数据字典的命中率
ROW CACHE
----------
.899250492
注：row cache的命中率至少小于90%
7、每个子shared pool由 单独的shared pool latch保护 查看 他们的命中率
ADDR NAME GETS MISSES 1-MISSES/GETS
---------------- --------------- ---------- ---------- -------------
0000000060349618 shared pool 65004 0 1
0000000060349578 shared pool 65004 0 1
00000000603494D8 shared pool 65004 0 1
0000000060349438 shared pool 65004 0 1
0000000060349398 shared pool 65004 0 1
00000000603492F8 shared pool 65004 0 1
0000000060349258 shared pool 12640902 68251 .994600781
7 rows selected.
8、查看shared pool建议
Est Est
Time Parse
Pool Size Est Est LC Saved Saved Est
Size(M) Factor LC(M) Mem. Obj. (sec) Factor Object Hits
---------- ---------- ---------- ---------- ---------- ---------- ------------------------------------
280 1 26 1280 35958 1 3892570
284 1.0143 29 1481 35963 1.0001 3895122
288 1.0286 32 1644 35972 1.0004 3898068
292 1.0429 35 1801 35981 1.0006 3900576
296 1.0571 38 1994 35990 1.0009 3902870
300 1.0714 42 2079 36000 1.0012 3905343
304 1.0857 46 2164 36006 1.0013 3908229
308 1.1 50 2249 36012 1.0015 3911345
Est Est
Time Parse
Pool Size Est Est LC Saved Saved Est
Size(M) Factor LC(M) Mem. Obj. (sec) Factor Object Hits
---------- ---------- ---------- ---------- ---------- ---------- ------------------------------------
312 1.1143 54 2334 36021 1.0018 3914333
316 1.1286 57 2425 36028 1.0019 3917488
336 1.2 77 3335 36062 1.0029 3931153
364 1.3 104 4466 36092 1.0037 3941382
392 1.4 131 5442 36129 1.0048 3950645
420 1.5 159 6223 36147 1.0053 3957575
448 1.6 187 6955 36166 1.0058 3962361
476 1.7 215 7687 36179 1.0061 3966691
Est Est
Time Parse
Pool Size Est Est LC Saved Saved Est
Size(M) Factor LC(M) Mem. Obj. (sec) Factor Object Hits
---------- ---------- ---------- ---------- ---------- ---------- ------------------------------------
504 1.8 242 8535 36195 1.0066 3969937
532 1.9 269 9875 36213 1.0071 3972118
560 2 296 11026 36236 1.0077 3974155
19 rows selected.
9、查看shared pool中 各种类型的chunk的大小数量
no rows selected
10、查看使用shard_pool保留池情况
no rows selected
11、 pga 建议
Current Mb Projected Mb %
---------- ------------ ----------
200 25 96
200 50 96
200 100 96
200 150 96
200 200 100
200 240 100
200 280 100
200 320 100
200 360 100
200 400 100
200 600 100
Current Mb Projected Mb %
---------- ------------ ----------
200 800 100
200 1200 100
200 1600 100
14 rows selected.
12、查看buffer cache 命中率
Buffer Hit Ratio
----------------
.988227064
13、查看buffer cache设置大小建议
SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR estd_physical_reads
----------------- ------------------------- ------------------------------------------------------------------------
16 4.7163 704518
32 3.3259 496817
48 2.695 402580
64 2.2673 338688
80 1.9485 291060
96 1.632 243787
112 1.4187 211921
128 1.2884 192466
144 1.1708 174897
160 1.058 158037
172 1 149379
SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR estd_physical_reads
----------------- ------------------------- ------------------------------------------------------------------------
176 .9795 146314
192 .9188 137255
208 .8689 129797
224 .7854 117319
240 .7259 108431
256 .6691 99944
272 .6317 94363
288 .616 92018
304 .5992 89513
320 .5866 87626
8 1 0
SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR estd_physical_reads
----------------- ------------------------- ------------------------------------------------------------------------
16 1 0
24 1 0
32 1 0
40 1 0
48 1 0
56 1 0
64 1 0
72 1 0
80 1 0
88 1 0
96 1 0
SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR estd_physical_reads
----------------- ------------------------- ------------------------------------------------------------------------
100 1 0
104 1 0
112 1 0
120 1 0
128 1 0
136 1 0
144 1 0
152 1 0
160 1 0
42 rows selected.
14、查看buffer cache中defalut pool 命中率
NAME 1-(PHYSICAL_READS)/(CONSISTENT_GETS+DB_BLOCK_GETS)
--------------- --------------------------------------------------
DEFAULT .989934139
DEFAULT 1
注：default池命中率至少要大于90%
15、检查lgwr i/o性能
no rows selected
16、检查与redo相关性能指标
NAME VALUE
--------------- ----------
BPS redo wait 0
redo blocks rea 30568
d for recovery
redo k-bytes re 15284
ad for recovery
redo k-bytes re 0
ad for terminal
recovery
NAME VALUE
--------------- ----------
redo entries 757319
redo size 420901088
redo entries fo 0
r lost write de
tection
redo size for l 0
ost write detec
tion
redo size for d 25335124
NAME VALUE
--------------- ----------
irect writes
redo buffer all 13
ocation retries
redo wastage 0
redo write acti 0
ve strands
redo writes 0
redo writes (gr 0
NAME VALUE
--------------- ----------
oup 0)
redo writes (gr 0
oup 1)
redo writes (gr 0
oup 2)
redo writes (gr 0
oup 3)
NAME VALUE
--------------- ----------
redo writes (gr 0
oup 4)
redo writes (gr 0
oup 5)
redo writes (gr 0
oup 6)
redo writes (gr 0
oup 7)
NAME VALUE
--------------- ----------
redo writes ada 0
ptive all
redo writes ada 0
ptive worker
redo writes coa 0
lesced
redo blocks wri 0
NAME VALUE
--------------- ----------
tten
redo blocks wri 0
tten (group 0)
redo blocks wri 0
tten (group 1)
redo blocks wri 0
tten (group 2)
NAME VALUE
--------------- ----------
redo blocks wri 0
tten (group 3)
redo blocks wri 0
tten (group 4)
redo blocks wri 0
tten (group 5)
redo blocks wri 0
tten (group 6)
NAME VALUE
--------------- ----------
redo blocks wri 0
tten (group 7)
redo write size 0
count ( 4KB)
redo write size 0
count ( 8KB)
redo write size 0
NAME VALUE
--------------- ----------
count ( 16KB)
redo write size 0
count ( 32KB)
redo write size 0
count ( 64KB)
redo write size 0
count ( 128KB)
NAME VALUE
--------------- ----------
redo write size 0
count ( 256KB)
redo write size 0
count ( 512KB)
redo write size 0
count (1024KB)
redo write size 0
count (inf)
NAME VALUE
--------------- ----------
redo write time 0
redo write time 0
(usec)
redo write work 0
er delay (usec)
redo write work 0
er delay count
NAME VALUE
--------------- ----------
redo blocks che 0
cksummed by FG
(exclusive)
redo blocks che 0
cksummed by LGW
R
redo log space 14
requests
NAME VALUE
--------------- ----------
redo log space 3
wait time
redo ordering m 386
arks
redo subscn max 1351
counts
redo write broa 0
dcast ack time
NAME VALUE
--------------- ----------
redo write broa 0
dcast ack count
redo write broa 0
dcast lgwr post
count
redo synch time 174
redo synch time 1771081
(usec)
NAME VALUE
--------------- ----------
redo synch time 4286578
overhead (usec
)
redo synch time 154
overhead count
( 2ms)
redo synch time 9
overhead count
NAME VALUE
--------------- ----------
( 8ms)
redo synch time 6
overhead count
( 32ms)
redo synch time 6
overhead count
(128ms)
redo synch time 6
NAME VALUE
--------------- ----------
overhead count
(inf)
redo synch fast 0
sync all sleep
(usec)
redo synch fast 0
sync all sleep
count
NAME VALUE
--------------- ----------
redo synch fast 0
sync all sleep
(20us)
redo synch fast 0
sync all sleep
(40us)
redo synch fast 0
sync all sleep
(60us)
NAME VALUE
--------------- ----------
redo synch fast 0
sync all sleep
(80us)
redo synch fast 0
sync all sleep
(100us)
redo synch fast 0
sync all sleep
NAME VALUE
--------------- ----------
(120us)
redo synch fast 0
sync all sleep
(160us)
redo synch fast 0
sync all sleep
(240us)
redo synch fast 0
NAME VALUE
--------------- ----------
sync all sleep
(400us)
redo synch fast 0
sync all sleep
(720us)
redo synch fast 0
sync all sleep
(inf)
NAME VALUE
--------------- ----------
redo synch fast 0
sync sleep cou
nt
redo synch fast 0
sync sleep (us
ec)
redo synch fast 0
sync sleep o/h
(usec)
NAME VALUE
--------------- ----------
redo synch fast 0
sync spin coun
t
redo synch fast 0
sync spin (use
c)
redo synch fast 0
sync backoff c
NAME VALUE
--------------- ----------
ount
redo synch fast 0
sync backoff (
usec)
redo synch fast 0
sync backoff o
/h (usec)
redo synch writ 238
NAME VALUE
--------------- ----------
es
redo synch long 2
waits
redo write gath 0
er time
redo write sche 0
dule time
NAME VALUE
--------------- ----------
redo write issu 0
e time
redo write fini 0
sh time
redo write tota 0
l time
redo synch poll 0
writes
NAME VALUE
--------------- ----------
redo synch poll 0
s
redo write info 181
find
redo write info 0
find fail
redo KB read 16564
NAME VALUE
--------------- ----------
redo KB read (m 0
emory)
redo KB read fo 0
r transport
redo KB read (m 0
emory) for tran
sport
redo non-durabl 0
NAME VALUE
--------------- ----------
e records skipp
ed
txn cache redo 0
sync reads
IM ADG redo rec 0
ords seen
securefile inod 0
e lhb redo
NAME VALUE
--------------- ----------
securefile inod 0
e itree redo
105 rows selected.
17、查询redo block size
MAX(LEBSZ)
----------
512
18、 计算出每个事务平均处理多少个redo block
A.REDOBLOCKS/B.TRANCOUNT
------------------------
0
19、 检查undo rollback segment 使用情况
NAME RSSIZE EXTENTS LATCH XACTS WRITES GETS WAITS
------------------------------------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
_SYSSMU3_3657342154$ 34725888 35 3 0 36540940 67630 2
_SYSSMU10_2262159254$ 1171456 3 0 0 22691224 65720 1
_SYSSMU1_3588498444$ 2220032 4 1 0 6824628 64280 1
_SYSSMU6_442110264$ 17948672 19 1 0 22065812 66294 0
_SYSSMU7_2728255665$ 9560064 11 2 0 12342776 64787 0
_SYSSMU8_801938064$ 3268608 5 3 0 5757944 63614 0
_SYSSMU9_647420285$ 3268608 5 4 0 7281274 64551 0
_SYSSMU4_811969446$ 13754368 15 4 0 16258396 65369 0
_SYSSMU2_2971032042$ 5365760 7 2 0 11699968 65121 0
SYSTEM 450560 7 0 0 19888 375 0
_SYSSMU5_3018429039$ 3268608 5 0 0 7849356 64004 0
11 rows selected.
20、计算每秒钟产生的undoblk数量
SUM(UNDOBLKS)/SUM((END_TIME-BEGIN_TIME)*24*60*60)
-------------------------------------------------
.102353254
21、查询undo具体信息
UndoB Trans LongestQuery MaxConcurrency Snapshot2Old FreeSpaceWait
------ ------ ------------ -------------- ------------ -------------
6 119 0 2 0 0
0 0 0 0 0 0
0 58 0 0 0 0
2 10 0 1 0 0
0 0 0 0 0 0
0 56 0 0 0 0
7 69 0 2 0 0
0 0 0 0 0 0
1 51 0 1 0 0
1 11 0 1 0 0
0 0 0 0 0 0
UndoB Trans LongestQuery MaxConcurrency Snapshot2Old FreeSpaceWait
------ ------ ------------ -------------- ------------ -------------
1 52 0 1 0 0
4 79 0 2 0 0
0 0 0 0 0 0
6 265 0 1 0 0
652 1041 0 3 0 0
0 0 0 0 0 0
1 62 0 1 0 0
9 92 0 2 0 0
0 0 0 0 0 0
0 0 0 0 0 0
0 27 0 0 0 0
UndoB Trans LongestQuery MaxConcurrency Snapshot2Old FreeSpaceWait
------ ------ ------------ -------------- ------------ -------------
0 14 0 0 0 0
0 0 0 0 0 0
2 72 0 2 0 0
8 88 0 2 0 0
0 0 0 0 0 0
2 35 0 1 0 0
0 35 0 0 0 0
0 0 0 0 0 0
43 117 0 1 0 0
48 141 0 2 0 0
41 0 0 1 0 0
UndoB Trans LongestQuery MaxConcurrency Snapshot2Old FreeSpaceWait
------ ------ ------------ -------------- ------------ -------------
2042 193 0 2 0 0
1416 78 0 2 0 0
2696 1 0 1 0 0
12 134 0 4 0 0
17 319 0 4 0 0
8 31 0 6 0 0
0 0 0 0 0 0
0 0 0 1 0 0
2 99 3593 2 0 0
3 42 3593 2 0 0
0 0 3593 1 0 0
UndoB Trans LongestQuery MaxConcurrency Snapshot2Old FreeSpaceWait
------ ------ ------------ -------------- ------------ -------------
3 94 3593 3 0 0
1 4 3593 2 0 0
0 0 0 0 0 0
7 158 3593 6 0 0
1 14 0 5 0 0
0 0 0 0 0 0
3 46 0 2 0 0
5 74 71421 2 0 0
0 10 71421 1 0 0
0 67 71421 1 0 0
0 0 71421 1 0 0
UndoB Trans LongestQuery MaxConcurrency Snapshot2Old FreeSpaceWait
------ ------ ------------ -------------- ------------ -------------
1 8 71421 5 0 0
3 76 71421 3 0 0
6 176 71421 2 0 0
0 0 71421 1 0 0
9 62 71421 3 0 0
2 55 71421 2 0 0
1 10 71421 3 0 0
7 78 71421 3 0 0
0 0 71421 1 0 0
5 187 71421 2 0 0
1431 79 71421 2 0 0
UndoB Trans LongestQuery MaxConcurrency Snapshot2Old FreeSpaceWait
------ ------ ------------ -------------- ------------ -------------
2077 2 71421 1 0 0
15 213 71421 2 0 0
3 86 71421 2 0 0
0 28 71421 0 0 0
0 0 71421 0 0 0
3 48 71421 1 0 0
12 350 71421 1 0 0
1 13 71421 1 0 0
1067 3483 71421 5 0 0
0 0 0 0 0 0
4 75 71421 2 0 0
UndoB Trans LongestQuery MaxConcurrency Snapshot2Old FreeSpaceWait
------ ------ ------------ -------------- ------------ -------------
0 0 0 0 0 0
2 64 0 1 0 0
8 147 0 2 0 0
0 3 0 0 0 0
35 224 0 3 0 0
158 269 0 3 0 0
3 8 0 2 0 0
5 128 0 2 0 0
6 110 0 1 0 0
4 141 0 1 0 0
0 2 0 0 0 0
UndoB Trans LongestQuery MaxConcurrency Snapshot2Old FreeSpaceWait
------ ------ ------------ -------------- ------------ -------------
5 106 0 2 0 0
0 0 0 0 0 0
2 64 0 1 0 0
4 75 0 3 0 0
0 0 0 0 0 0
2 71 0 1 0 0
5 88 0 2 0 0
0 0 0 0 0 0
4 72 0 1 0 0
4 58 0 1 0 0
0 0 0 0 0 0
UndoB Trans LongestQuery MaxConcurrency Snapshot2Old FreeSpaceWait
------ ------ ------------ -------------- ------------ -------------
309 216 0 2 0 0
278 222 0 2 0 0
2 15 0 2 0 0
3 60 0 1 0 0
823 3552 0 3 0 0
0 0 0 0 0 0
40 239 0 2 0 0
884 292 0 2 0 0
1762 ###### 0 2 0 0
3 70 0 1 0 0
1 92 0 1 0 0
UndoB Trans LongestQuery MaxConcurrency Snapshot2Old FreeSpaceWait
------ ------ ------------ -------------- ------------ -------------
0 0 0 0 0 0
0 0 0 0 0 0
6 96 0 2 0 0
0 1 0 0 0 0
9 83 0 3 0 0
6 193 0 1 0 0
1757 ###### 0 2 0 0
5 162 0 1 0 0
1764 ###### 0 3 0 0
33 305 0 4 0 0
120 rows selected.
22、查询rollback 段详细信息(收缩次数,扩展次数,平均活动事务等)
RBS Extends Shrinks Wraps AveShrink AveActive
-------------------------------------------------- ---------- ---------- ---------- ---------- ----------
_SYSSMU1_3588498444$ 1 2 13 2097152 397329
_SYSSMU2_2971032042$ 3 2 19 6291456 795251
_SYSSMU3_3657342154$ 31 1 43 3145728 26447315
_SYSSMU4_811969446$ 10 0 21 0 5179365
_SYSSMU5_3018429039$ 1 1 14 10485760 474893
_SYSSMU6_442110264$ 15 4 28 6553600 10822445
_SYSSMU7_2728255665$ 7 0 16 0 3287355
_SYSSMU8_801938064$ 0 0 9 0 344093
_SYSSMU9_647420285$ 0 0 11 0 482124
_SYSSMU10_2262159254$ 30 6 59 3429717 7521752
10 rows selected.
23、查询当前rollback segment使用情况
RBS STATUS WAITS GETS TO_CHAR(S.WRITES,'9999999999999') Active Trans
-------------------------------------------------- --------------------------------------------- ---------- ---------- ------------------------------------------ ------------
_SYSSMU1_3588498444$ ONLINE 1 64282 6824628 0
_SYSSMU2_2971032042$ ONLINE 0 65123 11699968 0
_SYSSMU3_3657342154$ ONLINE 2 67632 36540940 0
_SYSSMU4_811969446$ ONLINE 0 65371 16258396 0
_SYSSMU5_3018429039$ ONLINE 0 64006 7849356 0
_SYSSMU6_442110264$ ONLINE 0 66296 22065812 0
_SYSSMU7_2728255665$ ONLINE 0 64789 12342776 0
_SYSSMU8_801938064$ ONLINE 0 63616 5757944 0
_SYSSMU9_647420285$ ONLINE 0 64553 7281274 0
_SYSSMU10_2262159254$ ONLINE 1 65722 22691224 0
10 rows selected.
24、查询使用rollback segment时等待比率
Contention
---------------------------------------------------------------------------------------------------------------------------
.01%
25、查询使用rollback segment时等待比率及其平局活动事务数
Trans per RBS Contention
------------- ---------------------------------------------------------------------------------------------------------------------------
0 .01%
五、检查Oracle数据库性能
1、检查数据库的等待事件
SID EVENT P1 P2 P3 WAIT_TIME SECONDS_IN_WAIT
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ---------------
2 Data Guard: Gap Manager 0 0 0 0 23
3 OFS idle 0 0 0 0 1
4 wait for unread message on broadcast cha 1745204944 1745061232 0 0 1
nnel
6 LGWR worker group idle 1 0 0 0 51
7 lreg timer 1 0 0 0 2
15 AQPC idle 0 0 0 0 26
17 Streams AQ: qmn coordinator idle wait 0 0 0 0 0
21 Space Manager: slave idle wait 3 0 0 0 5
127 VKTM Logical Idle Wait 0 0 0 0 65129
129 watchdog main loop 0 10 0 0 1
130 pman timer 0 0 0 0 1
136 Streams AQ: qmn slave idle wait 1 0 0 0 0
140 Space Manager: slave idle wait 7 0 0 0 1
146 Streams AQ: waiting for time management 0 0 0 0 18276
or cleanup tasks
147 class slave wait 0 0 0 0 51
151 Space Manager: slave idle wait 1 0 0 0 0
152 class slave wait 0 0 0 0 51
253 pmon timer 300 0 0 0 1
255 watchdog main loop 0 8 0 0 1
257 DIAG idle wait 3 1 0 0 1
258 LGWR worker group idle 0 0 0 0 9
267 class slave wait 0 0 0 0 51
268 Space Manager: slave idle wait 4 0 0 0 0
272 Space Manager: slave idle wait 0 0 0 0 3
279 class slave wait 0 0 0 0 51
379 Data Guard: Timer 0 0 0 0 20
380 pmon timer 300 0 0 0 1
382 DIAG idle wait 3 1 0 0 1
383 VKRM Idle 0 0 0 0 65129
385 smon timer 300 0 0 0 103
386 Space Manager: slave idle wait 2 0 0 0 0
389 heartbeat redo informer 0 0 0 0 1
395 Space Manager: slave idle wait 5 0 0 0 3
399 Space Manager: slave idle wait 6 0 0 0 0
34 rows selected.
2、查看与redo相关等待事件
EVENT TOTAL_WAITS TOTAL_TIMEOUTS AVERAGE_WAIT
---------------------------------------- ----------- -------------- ------------
latch: redo allocation 1 0 .04
3、查看session redo event
EVENT TOTAL_WAITS TOTAL_TIMEOUTS AVERAGE_WAIT
---------------------------------------- ----------- -------------- ------------
latch: redo writing 1 0 .01
heartbeat redo informer 64995 0 100.2
4、Disk Read最高的SQL语句的获取
5、查找前十条性能差的sql
PARSING_USER_ID EXECUTIONS SORTS COMMAND_TYPE DISK_READS
--------------- ---------- ---------- ------------ ----------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
0 121207 0 3 356
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval,
hival, density, col#, spare1, spare2, avgcln, minimum_enc, maximum_enc from hist_head$ where obj#=:1 and intcol#=:2
0 1019 1019 3 246
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(property,0),subname,type#,flags,d_attrs from d
ependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
0 1 0 3 154
SELECT owner, constraint_name, table_name, constraint_type, status FROM dba_constraints WHERE status ='DISABLE'
and constraint_type='P'
0 1 1 3 115
select Segment_Name, Segment_Type, TableSpace_Name, (Extents/Max_extents)*100 Percent From sys.DBA_Segments Where
Max_Extents != 0 and (Extents/Max_extents)*100>=95 order By Percent
0 5442 0 3 112
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2, spare3, signature, spare7, spare8, s
pare9, nvl(dflcollid, 16382), creappid, creverid, modappid, modverid, crepatchid, modpatchid from obj$ where owner#=:1 a
nd name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
0 7359 13809 3 73
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.le
afcnt,i.distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance
s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),nvl(i.spare1,i
.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,null,mod(trunc(i.pctthres$/256),256)),nvl(i.evaledition#,1)
,nvl(i.unusablebefore#,0),nvl(i.unusablebeginning#,0), ist.cachedblk,ist.cachehit,ist.logicalread from ind$ i, ind_stats
$ ist, (select enabled, min(intcols) unicols,min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+)
and i.bo#=:1 order by i.obj#
0 8235 8235 3 58
select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(scale,-127/*MAXSB1MINAL*/),178,scale,179,s
cale,180,scale,181,scale,182,scale,183,scale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper
ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0), nvl(evaledition#,1),nvl(unusablebefore#,0),nvl(unus
ablebeginning#,0), case when (type# in (1,8,9,96,112)) then nvl(collid, 16382) else 0 end case, nvl(collintcol#,0), nvl(
acdrrescol#, 0), nvl(spare7, 0), nvl(spare9, 0), nvl(spare10, 0) from col$ where obj#=:1 order by intcol#
0 1434 1434 3 48
select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw, ep_repeat_count, endpoint_enc from histgrm$ where obj#=
:1 and intcol#=:2 and row#=:3 order by bucket
0 30 0 3 44
SELECT xs.sid FROM sys.rxs$sessions xs
WHERE BITAND(xs.flag,32) <> 32 AND xs
.inactivetimeout <> 0 AND 365*30*24*60*(extract(year FROM systimestamp)
- extract(year FROM accesstime)) + 30*24*60*(e
xtract(month FROM systimestamp) - extract(month FROM accesstime)) +
24*60*(extract(day FROM systimestamp) - extract(day F
ROM accesstime)) + 60*(extract(hour FROM systimestamp) -
extract(hour FROM accesstime)) + (extract(minute FROM systimes
tamp) -
9 rows selected.
6、等待时间最多的5个系统等待事件的获取
EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO
---------------------------------------- ----------- -------------- ----------- ------------ -----------------
TOTAL_WAITS_FG TOTAL_TIMEOUTS_FG TIME_WAITED_FG AVERAGE_WAIT_FG TIME_WAITED_MICRO_FG EVENT_ID WAIT_CLASS_ID
-------------- ----------------- -------------- --------------- -------------------- ---------- -------------
WAIT_CLASS#
-----------
WAIT_CLASS
------------------------------------------------------------------------------------------------------------------------
CON_ID
----------
db file sequential read 31525 0 12596 .4 125963974
6138 0 1509 .25 15088073 2652584166 1740759767
8
User I/O
3
PGA memory operation 31136 0 32 0 316767
22355 0 23 0 232201 1800992443 1893977003
0
Other
3
control file sequential read 7936 0 11 0 109955
1310 0 5 0 51291 3213517201 4108307767
9
System I/O
3
control file parallel write 4333 0 399 .09 3985706
86 0 4 .05 40906 4078387448 4108307767
9
System I/O
3
db file scattered read 2370 0 1094 .46 10943503
277 0 48 .17 484296 506183215 1740759767
8
User I/O
3
7、检查运行很久的SQL
no rows selected
9、检查碎片程度高的表
TABLE_NAME EXTENTS
------------------------------ ----------
SALES_CHANNEL_BIX 16
SALES 16
SALES_CUST_BIX 16
SALES_PROMO_BIX 16
SALES_PROD_BIX 16
SALES_TIME_BIX 16
6 rows selected.
10、检查死锁及处理
no rows selected
11、查看数据库中行chain
Ratio
---------------------------------------
Percentage
------------------------------------------------------------------------------------------------------------------------
Chained Rows
.028%
12、查询解析比率
Ratio
---------------------------------------------
Percentage
------------------------------------------------------------------------------------------------------------------------
Hard Parses
10.03%
Parse Failures
.05383%
Soft Parses
20.45%
13、查看与latch有关的event信息
Event Total Time Waits Average Timeouts
-------------------- ---------- -------- --------- ---------
latch free 1 10 0 0
14、查看大表小表扫描对应的值
VALUE NAME
---------- ------------------------------------------------------------
160143 table scans (short tables)
19 table scans (long tables)
889360 table fetch by rowid
Ratio
------------------------------------------------------------------------------------------
Percentage
------------------------------------------------------------------------------------------------------------------------
Efficient Table Access
100%
Long Table Scans
0%
Short Table Scans
15.26%
Short to Long Full Table Scans
99.99%
Table by Index
84.74%
15、index使用比率
TO_CHAR(VALUE,'999999999999999999999') NAME
------------------------------------------------------------------ ------------------------------
160143 table scans (short tables)
19 table scans (long tables)
889360 table fetch by rowid
172 index fast full scans (full)
0 index fast full scans (rowid r
anges)
0 index fast full scans (direct
read)
694064 index fetch by key
7 rows selected.
Ratio
---------------------------------------------------------------
Result
------------------------------------------------------------------------------------------------------------------------
Index to Table Ratio
10:1
16、等待class
WAIT_CLASS COUNT(WAIT_CLASS)
------------------------------ -----------------
Administrative 1
Application 3
Commit 1
Concurrency 15
Configuration 6
Idle 9
Network 2
Other 28
System I/O 2
User I/O 13
10 rows selected.
SQL> 
1082
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
