数据库 sql DBA Oracle自动化巡检脚本出炉

巡检脚本如下：

[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

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

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

Version 19.3.0.0.0

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

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

CUSTOMER_ORDER_PRODUCTS VIEW

STORE_ORDERS VIEW

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

(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

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

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

redo synch fast 0

sync spin (use

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

--------------- ----------

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

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

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

------------------------

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 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

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

User I/O

PGA memory operation 31136 0 32 0 316767

22355 0 23 0 232201 1800992443 1893977003

Other

control file sequential read 7936 0 11 0 109955

1310 0 5 0 51291 3213517201 4108307767

System I/O

control file parallel write 4333 0 399 .09 3985706

86 0 4 .05 40906 4078387448 4108307767

System I/O

db file scattered read 2370 0 1094 .46 10943503

277 0 48 .17 484296 506183215 1740759767

User I/O

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

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>

推荐链接

评论可见，请评论后查看内容，谢谢！！！

您阅读本篇文章共花了：

金钥匙

数据库 sql DBA Oracle自动化巡检脚本出炉

数据库 dba database MySQL之binlog

UI开发界面控件 c++ 「Qt Widget中文示例指南」如何实现一个日历？（二）

发表评论取消回复

金钥匙

数据库 sql DBA Oracle自动化巡检脚本出炉

数据库 dba database MySQL之binlog

UI开发 界面控件 c++ 「Qt Widget中文示例指南」如何实现一个日历？（二）

相关文章

发表评论取消回复

UI开发界面控件 c++ 「Qt Widget中文示例指南」如何实现一个日历？（二）