prompt
prompt -----------逻辑读多的SQL,消耗CPU资源严重-----------------------
prompt
SELECT sql_text FROM v$sqlarea
WHERE buffer_gets/1024/1024/1024 > 0.01 AND EXECUTIONS>1000;
prompt
prompt ----------某时刻正在连接的用户，及数目----------------------
prompt
select user_id,count(*) from v$active_session_history a
where to_char(a.sample_time,‘yyyy-mm-dd hh24:mi:ss’) like to_char(sysdate,‘yyyy-mm-dd hh24:mi%’)
group by user_id order by 2;
prompt
prompt -------------------找ORACLE字符集---------------------------
prompt
select VALUE$ from sys.props$ where name=‘NLS_CHARACTERSET’;
prompt
prompt ------------------------查看DB_CACHE_ADVICE的建议值----------------------------
prompt
SELECT size_for_estimate, buffers_for_estimate,
estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = ‘DEFAULT’
AND block_size =
(SELECT value FROM V$PARAMETER
WHERE name = ‘db_block_size’)
AND advice_status = ‘ON’;
prompt
prompt -------------------------查看PGA的建议值-----------------------------
prompt
select PGA_TARGET_FOR_ESTIMATE “pga size”,
PGA_TARGET_FACTOR “pga factor”,
BYTES_PROCESSED “bytes proc”,
ESTD_EXTRA_BYTES_RW “estd byte RW”,
ESTD_PGA_CACHE_HIT_PERCENTAGE “setd pga hit”,
ESTD_OVERALLOC_COUNT “estd overalloc cnt”
from v$pga_target_advice;
prompt
prompt ------------------------查看SHARED_POOL的建议值---------------------------
prompt
select SHARED_POOL_SIZE_FOR_ESTIMATE “pool size”,
SHARED_POOL_SIZE_FACTOR “pool size factor”,
ESTD_LC_SIZE “estd lc size”,
ESTD_LC_MEMORY_OBJECTS “estd lc mem obj”,
ESTD_LC_TIME_SAVED “time saved”,
ESTD_LC_TIME_SAVED_FACTOR “time saved factor”,
ESTD_LC_MEMORY_OBJECT_HITS “mem obj hits”
from v$shared_pool_advice;
prompt
prompt -------------------------- temp表空间的空间情况 ----------------------------
prompt
select tablespace_name “Tablespace”,
sum(bytes)/1024/1024 “Total(MB)”
from v$temp_extent_map
group by tablespace_name ;
SELECT p.tablespace_name,
ROUND(SUM(p.bytes_cached) / 1024 / 1024) BYTES_CACHED,
ROUND(SUM(p.bytes_used) / 1024 / 1024) BYTES_USED
FROM v$temp_extent_pool p
GROUP BY p.tablespace_name;
prompt
prompt --------------------------- 表的空间使用情况 -------------------------------
prompt
SELECT df.tablespace_name “Tablespace”,
df.totalspace “Total(MB)”,
(df.totalspace - nvl(fs.freespace, 0)) “Used(MB)”,
nvl(fs.freespace, 0) “Free(MB)”,
round(100 * ( nvl(fs.freespace, 0) / df.totalspace), 2) “Pct. Free(%)”
FROM (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, round(SUM(bytes) / 1048576) FreeSpace
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY “Pct. Free(%)” ;
prompt
prompt ------------------------------ 表的扩展情况 --------------------------------
prompt
select owner,segment_name,EXTENTS,MAX_EXTENTS,TABLESPACE_NAME
from dba_segments
where EXTENTS > 1000
AND TABLESPACE_NAME NOT IN (‘SYSAUX’, ‘SYSTEM’,‘USERS’)
order by owner,segment_name;
select owner,segment_name,EXTENTS,MAX_EXTENTS,TABLESPACE_NAME
from dba_segments
where ( MAX_EXTENTS - EXTENTS ) < 5
AND TABLESPACE_NAME NOT IN (‘SYSAUX’, ‘SYSTEM’,‘USERS’)
order by owner,segment_name;
prompt
prompt ------------------------- 长时间未提交会话的情况 ---------------------------
prompt
SELECT A.ADDR,A.XIDUSN,B.NAME,A.START_TIME,A.START_UEXT,A.USED_UBLK,A.USED_UREC
FROM V$TRANSACTION A,V$ROLLNAME B
WHERE (SYSDATE - TO_DATE(A.START_TIME,‘MM/DD/YY HH24:MI:SS’) ) > ( 30.0/(24*60))
AND A.XIDUSN = B.USN;
select SQL_ADDRESS,SQL_HASH_VALUE,TADDR,SID ,SERIAL# ,PADDR ,USERNAME,STATUS ,STATUS,LOGON_TIME,
SCHEMANAME,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM,MODULE
from v$session
where taddr In (
SELECT A.ADDR
FROM V$TRANSACTION A
WHERE (SYSDATE - TO_DATE(A.START_TIME,‘MM/DD/YY HH24:MI:SS’) ) > ( 30.0/(24*60)))
ORDER BY SQL_ADDRESS,SQL_HASH_VALUE;
SELECT ADDRESS,HASH_VALUE,SQL_TEXT
FROM V$SQLTEXT
WHERE (ADDRESS,HASH_VALUE ) IN
( SELECT SQL_ADDRESS,SQL_HASH_VALUE
from v$session
where taddr In (
SELECT A.ADDR
FROM V$TRANSACTION A
WHERE (SYSDATE - TO_DATE(A.START_TIME,‘MM/DD/YY HH24:MI:SS’) ) > ( 30.0/(24*60))))
ORDER BY ADDRESS,HASH_VALUE,PIECE ;
prompt
prompt ----------------------Roll back segments Statistics-------------------------
prompt
select a.usn,b.name,a.extents,a.rssize,a.writes,a.xacts ,a.shrinks,a.status,a.curblk
from v$rollstat a,v$rollname b
where a.usn=b.usn
–and a.usn !=0
order by RSSIZE desc;
prompt
prompt ------------------------------ 物化视图的情况-------------------------------
prompt
col job format 99999
col log_user format a20
col time format a20
col what format a70
select broken,job,log_user,to_char(last_date,‘yyyy-mm-dd hh24:mi:ss’) time ,what from dba_jobs;
prompt
prompt --------------------------Buffer Cache statistics---------------------------
prompt
SELECT 100 * SUM(gets - getmisses - usage - fixed) / SUM(gets) 行缓冲区命中率
FROM v$rowcache;
SELECT 100 * SUM(pins - reloads) / SUM(pins) 库缓冲区命中率
FROM v$librarycache;
SELECT (1 - (a.VALUE / (b.VALUE + c.VALUE))) * 100 “Buffer Cache Hit ratio”
FROM v$sysstat a, v$sysstat b, v$sysstat c
WHERE a.NAME = ‘physical reads’
AND b.NAME = ‘consistent gets’
AND c.NAME = ‘db block gets’;
col NAME format a20 heading “Buffer Pool Name”
SELECT NAME, free_buffer_wait, write_complete_wait, buffer_busy_wait,
db_block_gets, consistent_gets, physical_reads, physical_writes
FROM v$buffer_pool_statistics;
prompt
prompt --------------------------Shared Pool Statistics----------------------------
prompt
col namespace format a22
SELECT namespace, gets, gethits, ROUND(gethitratio, 2) gethitratio, pins,
pinhits, ROUND(pinhitratio, 2) pinhitratio, reloads, invalidations
FROM v$librarycache;
select * from v$sgastat;
prompt
prompt --------------------------the number of session-----------------------------
prompt
select username,machine,count() from v$session group by username,machine;
select count() “Total session num” from v$session;
prompt
prompt --------------------Top 10 Events and System Statistics---------------------
prompt
col event format a40 heading “Event Name”
col Stat format a40 heading “Stat Name”
col value format 999999999999999
select * from ( select name “Stat”,value/1024/1024/1024/1024 from v$sysstat order by value desc ) where rownum <= 10 ;
select * from ( select event,total_waits from v$system_event order by total_waits desc ) where rownum <=10;
prompt
prompt --------------------查看那些数据库对象被修改过---------------------
prompt
SELECT OBJECT_NAME,OBJECT_TYPE,TO_CHAR(CREATED) CREATE_TIME,
TO_CHAR(LAST_DDL_TIME) MOD_TIME,
TIMESTAMP,STATUS FROM USER_OBJECTS WHERE to_char(LAST_DDL_TIME)>sysdate-7 and OBJECT_NAME =‘GET_ACC_MODE_BY_PROD_FOR_ZHJH’;
prompt
prompt --------------------查看最近一天内表哪些被修改过---------------------
prompt
SELECT a.table_name,a.inserts,a.updates,a.deletes,a.timestamp,a.truncated FROM USER_TAB_MODIFICATIONS a
where timestamp>sysdate-1;
prompt
prompt --------------------查看大表---------------------
prompt
select * from (select segment_name,segment_type,ROUND(bytes/1024/1024/1024,2) “G”
from dba_segments where owner=‘NEWCLASS’ order by 3 desc) WHERE ROWNUM<30;
prompt
prompt ------------------------查出有blob字段的表---------------------
prompt
select table_name,column_name,TABLESPACE_NAME from user_lobs WHERE TABLE_NAME NOT LIKE ‘%SYS%’;
prompt
prompt -------------------------------High CPU Usage-------------------------------
prompt
prompt
prompt High CPU Usage:
select a.sid,b.sql_text,a.program,
c.buffer_gets, c.executions, c.buffer_gets/c.executions “Gets/Exec”,c.disk_reads,
a.username,a.osuser,a.machine,a.module
from v$session a,v$sqltext b,v$sqlarea c
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sql_address = c.address
and a.sql_hash_value = c.hash_value
and c.buffer_gets > 50000
and c.executions>0
and c.buffer_gets/c.executions>1000
and ROWNUM < 20
ORDER BY c.buffer_gets/c.executions ,a.sid,b.piece;
prompt
prompt ---------------------------Locating IO heavy SQL----------------------------
prompt
SELECT a.sid,b.sql_text,a.program,
c.disk_reads, c.executions, c.disk_reads/c.executions “Reads/Exec”,
a.username,a.osuser,a.machine,a.module
FROM v$session a,v$sqltext b,v$sqlarea c
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sql_address = c.address
and a.sql_hash_value = c.hash_value
and c.disk_reads > 500
and c.executions>0
and c.disk_reads/c.executions>500
and ROWNUM < 20
ORDER BY c.disk_reads/c.executions ,a.sid,b.piece;
prompt
prompt ---------------------------open cursor---------------------------
prompt
select count() as cou,sql_text from v$open_cursor group by rollup(sql_text) having(count()) >20 order by cou desc;
prompt
prompt ---------------------------会话等待的累计次数和累计时间---------------------------
prompt
column event format A32
select e.event,type, sum(total_waits) “Waits”,
sum(time_waited) “SumTime”,count(*) “Count”
from sys.v_$session s, sys.v_$session_event e
where s.sid = e.sid
group by e.event,type
order by type ;
prompt
prompt ---------------------------数据库文件的碎片统计状态,可用Alter tablespace xxx coalesce;整理---------------------------
prompt
select tablespace_name,
Sqrt(max(blocks)/ sum(blocks)) * (100/Sqrt(Sqrt(count(blocks)))) FSFI,
count(blocks),sum(blocks),max(blocks)
from sys.dba_free_space group by tablespace_name;
prompt
prompt ---------------------------查询dmp路径---------------------------
prompt
select a.directory_name,directory_path from dba_directories a;
prompt -----------------------------查看具体的碎片-------------------------------
prompt
select * from dba_free_space
where bytes <= ( select min(next_extent) from dba_segments where tablespace_name = dba_free_space.tablespace_name)
and tablespace_name not in (‘TEMP’,‘UNDOTBS2’,‘UNDOTBS1’,‘SYSTEM’);
prompt
prompt ----------------------------看看哪个语句打开的游标最多。---------------------------
prompt
select count(a.sid) as cou,b.SQL_TEXT from v$open_cursor a,v$sqlarea b where a.SQL_ID =b.SQL_ID
and rownum<10
group by b.SQL_TEXT order by cou desc ;
prompt
prompt ---------------------------锁检查。---------------------------
prompt
set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading ‘Kill String’ format a13
column res heading ‘Resource Type’ format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading ‘Lock Held’ format a20
column request heading ‘Lock Requested’ format a20
column serial# format 99999
column username format a10 heading “Username”
column terminal heading Term format a6
column tab format a35 heading “Table Name”
column owner format a9
column Address format a18
select nvl(S.USERNAME,‘Internal’) username,
nvl(S.TERMINAL,‘None’) terminal,
L.SID||’,’||S.SERIAL# Kill,
U1.NAME||’.’||substr(T1.NAME,1,20) tab,
decode(L.LMODE,1,‘No Lock’,
2,‘Row Share’,
3,‘Row Exclusive’,
4,‘Share’,
5,‘Share Row Exclusive’,
6,‘Exclusive’,null) lmode,
decode(L.REQUEST,1,‘No Lock’,
2,‘Row Share’,
3,‘Row Exclusive’,
4,‘Share’,
5,‘Share Row Exclusive’,
6,‘Exclusive’,null) request
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != ‘BACKGROUND’
order by 1,2,5 ;
prompt
prompt ------------------确定应该被钉在共享池中的对象 如果reloads过大需要钉住 -----------------------
prompt –exec SYS.DBMS_SHARED_POOL.KEEP(OBJECTNAME,OBJECT_TYPE)---------------------
prompt
select type,NAME,count() objects,sum(decode(KEPT,‘YES’,1,0)) KEPT,SUM(loads)-count() reloads
from v$db_object_cache
WHERE TYPE NOT LIKE ‘NOT LOADED’
AND loads >1000
group by type,NAME
order by objects desc;
prompt
prompt ------------------ 内存分配情况:---------------------------**
prompt
COL Name for a40
COL Values for a40
select name, ROUND(value/1024/1024,2) “M”
from v$parameter
where name in (‘db_block_buffers’,‘db_cache_size’,‘db_block_size’,‘shared_pool_size’,‘sort_area_size’);
prompt
prompt ----------------- 监视会话内存及磁盘活动情况---------------------------**
prompt
select a.sid,A.SERIAL#, a.username, b.block_gets, b.consistent_gets,
b.physical_reads
from v$session a, v$sess_io b
where a.sid = b.sid
AND CONSISTENT_GETS >100000
AND USERNAME IS NOT NULL
order by a.username;
prompt
prompt ---------------------------查看占io较大的正在运行的session---------------------------
prompt
SELECT se.sid,
se.serial#,
pr.SPID,
se.username,
se.status,
se.terminal,
se.program,
se.MODULE,
se.sql_address,
st.event,
st.p1text,
si.physical_reads,
si.block_changes
FROM v$session se,
v$session_wait st,
v$sess_io si,
v$process pr
WHERE st.sid=se.sid
AND st.sid=si.sid
AND se.PADDR=pr.ADDR
AND se.sid>6
AND st.wait_time=0
AND st.event NOT LIKE ‘%SQL%’
ORDER BY physical_reads DESC;
prompt
prompt ----------------------查看shared pool/large pool 的使用情况 --------------------------
prompt
select s.pool Name,
Round(to_number(p.value)/1024/1024,2)||‘M’ “Size”,
Round(s.bytes/1024/1024,2)||‘M’ “Free” ,
Round((s.bytes/p.value),4)*100 "percentfree(%) "
from v$sgastat s,
v$parameter p
where s.name=‘free memory’ and
p.name=‘shared_pool_size’ and
s.pool=‘shared pool’
union
select s.pool Name,
Round(to_number(p.value)/1024/1024,2)||‘M’ “Size”,
Round(s.bytes/1024/1024,2)||‘M’ “Free”,
Round((s.bytes/p.value),4)*100 "percentfree(%) "
from v$sgastat s,
v$parameter p
where s.name=‘free memory’ and
p.name=‘large_pool_size’ and
s.pool=‘large pool’ ;
prompt
prompt ----------------------查看正在被执行的SQL脚本---------------------------
prompt
select a.program,
a.machine,
a.sid,
c.sql_text,
to_char(a.LOGON_TIME,‘yyyy-mm-dd hh24:mi:ss’)
from v$session a,
v$process b,
v$sqlarea c
where a.paddr=b.addr
and a.PREV_HASH_VALUE=c.hash_value
and a.PREV_SQL_ADDR=c.address
and a.username in (‘NEWCLASS’,‘RSS’,‘BJCNC’,‘VNET’)
and rownum <10;
prompt
prompt **--------监测在数据库中存在过多的分析SQL语句,如果连续出现增长，则库缓存出现了问题-------------------------
prompt
select STATISTIC#,NAME,VALUE from v$sysstat WHERE name LIKE ‘parse count%’;
prompt
prompt --------- 统计恢复日志有无竞争的情况: 如果immediate_contention > 1，则存在竞争 -----------------------**
prompt
select name,ROUND((immediate_misses/
decode((immediate_gets+immediate_misses),0,-1,
(immediate_gets+immediate_misses)))100,4) immediate_contwention,
(misses/decode((gets - misses),0,-1))100 wait_contention
from v$latch where name in(‘redo copy’,‘redo allocation’);
prompt
prompt ------------------------ SGA使用是否合理的情况信息 :--------------------------
prompt
set serveroutput on
set feedback off
DECLARE
libcac number(9,4);
rowcac number(9,4);
bufcac number(9,4);
redlog number(9,4);
spsize number;
blkbuf number;
logbuf number;
BEGIN
select value into redlog from v$sysstat
where name = ‘redo log space requests’ ;
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac
from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac
from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,
v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = ‘db block gets’
and con.statistic# = nco.statistic#
and nco.name = ‘consistent gets’
and phys.statistic# = nph.statistic#
and nph.name = ‘physical reads’;
select value into spsize
from v$parameter where name = ‘shared_pool_size’;
select value into blkbuf
from v$parameter where name = ‘db_block_buffers’;
select value into logbuf
from v$parameter where name = ‘log_buffer’;
dbms_output.put_line(’> SGA CACHE 统计:’);
dbms_output.put_line(’> ********************’);
dbms_output.put_line(’> SQL Cache Hit rate = ‘||libcac);
dbms_output.put_line(’> Dict Cache Hit rate = ‘||rowcac);
dbms_output.put_line(’> Buffer Cache Hit rate = ‘||bufcac);
dbms_output.put_line(’> Redo Log space requests = ‘||redlog);
dbms_output.put_line(’> ‘);
dbms_output.put_line(’> 目前INITsid.ORA(或Spfile) 设置’);
dbms_output.put_line(’> *****************************’);
dbms_output.put_line(’> Shared Pool Size = ‘||spsize||’ Bytes’);
dbms_output.put_line(’> DB Block Buffer = ‘||blkbuf||’ Blocks’);
dbms_output.put_line(’> Log Buffer = ‘||logbuf||’ Bytes’);
dbms_output.put_line(’> ');
if libcac < 99 then dbms_output.put_line(’*** 提示: 库高速缓存过低! 增加 Shared Pool 参数’); END IF;
if rowcac < 85 then dbms_output.put_line(’***提示: 行高速缓存过低! 增加 Shared Pool 参数’); END IF;
if bufcac < 90 then dbms_output.put_line(’***提示: 数据缓冲区过低! 增加 DB Block Buffer 参数’); END IF;
if redlog > 100 then dbms_output.put_line(’***提示: 日志缓冲区较低!’); END IF;
END;
/
exit;
