oracle数据库优化常用命令
点击数：1434发布日期：2020-04-20 14:58:32 来源：老鹰
新闻摘要:awrrpt
--查看命中率的语句是
select physical_reads/(db_block_gets+consistent_gets) from v$buffer_pool_statistics;
--监控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
--回滚段的争用情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
--监控表空间的 I/O 比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
--监控文件系统的 I/O 比例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
--在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
--监控 SGA 的命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
--监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
--监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
--显示所有数据库对象的类别和大小
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
--监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
--监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
--监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
--监控字典缓冲区
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
--找ORACLE字符集
select * from sys.props$ where name='NLS_CHARACTERSET';
--监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5时，参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时，参数需加大
-- 碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;
--查看碎片程度高的表
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);
--表、索引的存储情况检查
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;
--找使用CPU多的用户session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
--查看表空间物理文件的名称及大小:
SQL> SELECT TABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;
--查看回滚段名称及大小:
SQL> SELECT SEGMENT_NAME,
TABLESPACE_NAME,
R.STATUS,
(INITIAL_EXTENT / 1024) INITIALEXTENT,
(NEXT_EXTENT / 1024) NEXTEXTENT,
MAX_EXTENTS,
V.CUREXT CUREXTENT
FROM DBA_ROLLBACK_SEGS R, V$ROLLSTAT V
WHERE R.SEGMENT_ID = V.USN(+)
ORDER BY SEGMENT_NAME;
--如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句:
SQL> SELECT D.SQL_TEXT, A.NAME
FROM V$ROLLNAME A, V$TRANSACTION B, V$SESSION C, V$SQLTEXT D
WHERE A.USN = B.XIDUSN
AND B.ADDR = C.TADDR
AND C.SQL_ADDRESS = D.ADDRESS
AND C.SQL_HASH_VALUE = D.HASH_VALUE
AND A.USN = 1;
(备注：你要看哪个，就把usn=?写成几就行了)
--如何查看当前SQL*PLUS用户的sid和serial#:
SQL>SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');
--怎么判断当前正在使用何种SQL优化方式:
用EXPLAIN PLAN產生EXPLAIN PLAN?檢查PLAN_TABLE中ID=0的POSITION列的值
SQL>SELECT DECODE(NVL(POSITION,-1),-1,'RBO',1,'CBO') FROM PLAN_TABLE WHERE ID=0;
如何查看系统当前最新的SCN号：
SQL>SELECT MAX(KTUXESCNW * POWER(2,32) + KTUXESCNB) FROM X$KTUXE;
--在ORACLE中查找TRACE文件的脚本:
SQL>SELECT U_DUMP.VALUE || '/' || INSTANCE.VALUE || '_ORA_' ||
V$PROCESS.SPID || NVL2(V$PROCESS.TRACEID, '_' || V$PROCESS.TRACEID, NULL ) || '.TRC'"TRACE FILE" FROM V$PARAMETER U_DUMP CROSS JOIN V$PARAMETER INSTANCE CROSS JOIN V$PROCESS JOIN V$SESSION ON V$PROCESS.ADDR = V$SESSION.PADDR WHERE U_DUMP.NAME = 'USER_DUMP_DEST' AND
INSTANCE.NAME = 'INSTANCE_NAME' AND V$SESSION.AUDSID=SYS_CONTEXT('USERENV','SESSIONID');
SQL>SELECT D.VALUE || '/ORA_' || P.SPID || '.TRC' TRACE_FILE_NAME
FROM (SELECT P.SPID FROM SYS.V_$MYSTAT M,SYS.V_$SESSION S,
SYS.V_$PROCESS P WHERE M.STATISTIC# = 1 AND
S.SID = M.SID AND P.ADDR = S.PADDR) P,(SELECT VALUE FROM SYS.V_$PARAMETER WHERE NAME ='USER_DUMP_DEST') D;
如何查看客户端登陆的IP地址:
SQL>SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL;
如何在生产数据库中创建一个追踪客户端IP地址的触发器：
SQL>CREATE OR REPLACE TRIGGER ON_LOGON_TRIGGER AFTER LOGON ON DATABASE
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;
REM 记录登陆信息的触发器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'
BEGIN
INSERT INTO SESSION_HISTORY SELECT USERNAME,SID,SERIAL#,AUDSID,OSUSER,ACTION,SYSDATE,NULL,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,MACHINE,PROGRAM FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID');
END;
--查询当前日期:
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD,HH24:MI:SS') FROM DUAL;
查看所有表空间对应的数据文件名：
SQL>SELECT DISTINCT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES;
查看表空间的使用情况:
SQL>SELECT SUM(BYTES)/(1024*1024) AS FREE_SPACE,TABLESPACE_NAME
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
SQL>SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
COLUMN TABLESPACE_NAME FORMAT A18;
COLUMN SUM_M FORMAT A12;
COLUMN USED_M FORMAT A12;
COLUMN FREE_M FORMAT A12;
COLUMN PTO_M FORMAT 9.99;
SELECT S.TABLESPACE_NAME,CEIL(SUM(S.BYTES/1024/1024))||'M' SUM_M,CEIL(SUM(S.USEDSPACE/1024/1024))||'M' USED_M,CEIL(SUM(S.FREESPACE/1024/1024))||'M' FREE_M, SUM(S.USEDSPACE)/SUM(S.BYTES) PTUSED FROM (SELECT B.FILE_ID,B.TABLESPACE_NAME,B.BYTES, (B.BYTES-SUM(NVL(A.BYTES,0))) USEDSPACE, SUM(NVL(A.BYTES,0)) FREESPACE,(SUM(NVL(A.BYTES,0))/(B.BYTES)) * 100 FREEPERCENTRATIO FROM SYS.DBA_FREE_SPACE A,SYS.DBA_DATA_FILES B WHERE A.FILE_ID(+)=B.FILE_ID GROUP BY B.FILE_ID,B.TABLESPACE_NAME,B.BYTES ORDER BY B.TABLESPACE_NAME) S GROUP BY S.TABLESPACE_NAME ORDER BY SUM(S.FREESPACE)/SUM(S.BYTES) DESC;
查看数据文件的hwm（可以resize的最小空间）和文件头大小:
SELECT V1.FILE_NAME,V1.FILE_ID,NUM1 TOTLE_SPACE,NUM3 FREE_SPACE,
NUM1-NUM3 "USED_SPACE(HWM)",NVL(NUM2,0) DATA_SPACE,NUM1-NUM3-NVL(NUM2,0) FILE_HEAD
FROM
(SELECT FILE_NAME,FILE_ID,SUM(BYTES) NUM1 FROM DBA_DATA_FILES GROUP BY FILE_NAME,FILE_ID) V1,
(SELECT FILE_ID,SUM(BYTES) NUM2 FROM DBA_EXTENTS GROUP BY FILE_ID) V2,
(SELECT FILE_ID,SUM(BYTES) NUM3 FROM DBA_FREE_SPACE GROUP BY FILE_ID) V3
WHERE V1.FILE_ID=V2.FILE_ID(+) AND V1.FILE_ID=V3.FILE_ID(+);
数据文件大小及头大小:
SELECT V1.FILE_NAME,V1.FILE_ID,
NUM1 TOTLE_SPACE,
NUM3 FREE_SPACE,
NUM1-NUM3 USED_SPACE,
NVL(NUM2,0) DATA_SPACE,
NUM1-NUM3-NVL(NUM2,0) FILE_HEAD
FROM
(SELECT FILE_NAME,FILE_ID,SUM(BYTES) NUM1 FROM DBA_DATA_FILES GROUP BY FILE_NAME,FILE_ID) V1,
(SELECT FILE_ID,SUM(BYTES) NUM2 FROM DBA_EXTENTS GROUP BY FILE_ID) V2,
(SELECT FILE_ID,SUM(BYTES) NUM3 FROM DBA_FREE_SPACE GROUP BY FILE_ID) V3
WHERE V1.FILE_ID=V2.FILE_ID(+)
AND V1.FILE_ID=V3.FILE_ID(+);
(运行以上查询，我们可以如下信息：
Totle_pace:该数据文件的总大小，字节为单位
Free_space:该数据文件的剩于大小，字节为单位
Used_space:该数据文件的已用空间，字节为单位
Data_space:该数据文件中段数据占用空间，也就是数据空间，字节为单位
File_Head:该数据文件头部占用空间，字节为单位)
--数据库各个表空间增长情况的检查：
SQL>SELECT A.TABLESPACE_NAME,(1-(A.TOTAL)/B.TOTAL)*100 USED_PERCENT
FROM (SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME;
SQL>SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",F.TOTAL_BYTES"空闲空间(M)",
F.MAX_BYTES "最大块(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;
--查看各个表空间占用磁盘情况:
COL TABLESPACE_NAME FORMAT A20;
SELECT B.FILE_ID FILE_ID,
B.TABLESPACE_NAME TABLESPACE_NAME,
B.BYTES BYTES,
(B.BYTES-SUM(NVL(A.BYTES,0))) USED,
SUM(NVL(A.BYTES,0)) FREE,
SUM(NVL(A.BYTES,0))/(B.BYTES)*100 PERCENT
FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
WHERE A.FILE_ID=B.FILE_ID
GROUP BY B.TABLESPACE_NAME,B.FILE_ID,B.BYTES
ORDER BY B.FILE_ID;
--数据库对象下一扩展与表空间的free扩展值的检查：
SQL>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;
--Disk Read最高的SQL语句的获取：
SQL>SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM<=5;
--查找前十条性能差的sql
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 ;
--等待时间最多的5个系统等待事件的获取：
SQL>SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
--查看当前等待事件的会话:
COL USERNAME FORMAT A10
SET LINE 120
COL EVENT FORMAT A30
SELECT SE.SID,S.USERNAME,SE.EVENT,SE.TOTAL_WAITS,SE.TIME_WAITED,SE.AVERAGE_WAIT
FROM V$SESSION S,V$SESSION_EVENT SE WHERE S.USERNAME IS NOT NULL AND SE.SID=S.SID
AND S.STATUS='ACTIVE' AND SE.EVENT NOT LIKE '%SQL*NET%';
SELECT SID, EVENT, P1, P2, P3, WAIT_TIME, SECONDS_IN_WAIT, STATE FROM V$SESSION_WAIT WHERE EVENT NOT LIKE '%MESSAGE%' AND EVENT NOT LIKE 'SQL*NET%' AND EVENT NOT LIKE '%TIMER%' AND EVENT != 'WAKEUP TIME MANAGER';
找到与所连接的会话有关的当前等待事件：
SELECT SW.SID,S.USERNAME,SW.EVENT,SW.WAIT_TIME,SW.STATE,SW.SECONDS_IN_WAIT SEC_IN_WAIT
FROM V$SESSION S,V$SESSION_WAIT SW WHERE S.USERNAME IS NOT NULL AND SW.SID=S.SID
AND SW.EVENT NOT LIKE '%SQL*NET%' ORDER BY SW.WAIT_TIME DESC;
Oracle所有回滚段状态的检查：
SQL>SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,DBA_ROLLBACK_SEGS.STATUS FROM DBA_ROLLBACK_SEGS,V$DATAFILE WHERE FILE_ID=FILE#;
Oracle回滚段扩展信息的检查:
COL NAME FORMAT A10
SET LINESIZE 140
SELECT SUBSTR(NAME,1,40) NAME,EXTENTS,RSSIZE,OPTSIZE,AVEACTIVE,EXTENDS,WRAPS,SHRINKS,HWMSIZE
FROM V$ROLLNAME RN,V$ROLLSTAT RS WHERE (RN.USN=RS.USN);
EXTENTS:回滚段中的盘区数量。
Rssize:以字节为单位的回滚段的尺寸。
optsize：为optimal参数设定的值。
Aveactive:从回滚段中删除盘区时释放的以字节为单位的平均空间的大小。
Extends:系统为回滚段增加的盘区的次数。
Shrinks:系统从回滚段中清除盘区（即回滚段收缩）的次数。回滚段每次清除盘区时，系统可能会从这个回滚段中消除一个或多个盘区。
Hwmsize:回滚段尺寸的上限，即回滚段曾经达到的最大尺寸。
(如果回滚段平均尺寸接近OPTIMAL的值，那么说明OPTIMAL的值设置正确，如果回滚段动态增长次数或收缩次数很高，那么需要提高OPTIMAL的值)
查看回滚段的使用情况，哪个用户正在使用回滚段的资源:
SELECT S.USERNAME, U.NAME FROM V$TRANSACTION T,V$ROLLSTAT R,
V$ROLLNAME U,V$SESSION S WHERE S.TADDR=T.ADDR AND
T.XIDUSN=R.USN AND R.USN=U.USN ORDER BY S.USERNAME;
如何查看一下某个shared_server正在忙什么:
SELECT A.USERNAME,A.MACHINE,A.PROGRAM,A.SID,
A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT
FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C
WHERE B.SPID=13161 AND B.ADDR=A.PADDR
AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;
数据库共享池性能检查:
SELECT NAMESPACE,GETS,GETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONS FROM V$LIBRARYCACHE WHERE NAMESPACE IN('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');
检查数据重载比率:
SELECT SUM(RELOADS)/SUM(PINS)*100 "RELOAD RATIO" FROM
V$LIBRARYCACHE;
检查数据字典的命中率:
SELECT 1-SUM(GETMISSES)/SUM(GETS) "DATA DICTIONARY HIT
RATIO" FROM V$ROWCACHE;
(对于library cache, gethitratio和pinhitratio应该大于90%,对于数据重载比率,reload ratio应该小于1%,对于数据字典的命中率,data dictionary hit ratio应该大于85%)
检查共享内存的剩余情况:
SELECT REQUEST_MISSES, REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED;
(对于共享内存的剩余情况, request_misses 和request_failures应该接近0)
数据高速缓冲区性能检查:
SELECT 1-P.VALUE/(B.VALUE+C.VALUE) "DB BUFFER CACHE HIT RATIO" FROM V$SYSSTAT P,V$SYSSTAT B,V$SYSSTAT C WHERE P.NAME='PHYSICAL READS' AND B.NAME='DB BLOCK GETS' AND C.NAME='CONSISTENT GETS';
检查buffer pool HIT_RATIO执行
SELECT NAME, (PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)) "MISS_HIT_RATIO" FROM V$BUFFER_POOL_STATISTICS WHERE (DB_BLOCK_GETS+ CONSISTENT_GETS)> 0;
(正常时db buffer cache hit ratio 应该大于90%,正常时buffer pool MISS_HIT_RATIO 应该小于10%)
数据库回滚段性能检查:
检查Ratio执行
SELECT SUM(WAITS)* 100 /SUM(GETS) "RATIO", SUM(WAITS) "WAITS", SUM(GETS) "GETS" FROM V$ROLLSTAT;
检查count/value执行:
SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS LIKE '%UNDO%';
SELECT VALUE FROM V$SYSSTAT WHERE NAME='CONSISTENT GETS';
(两者的value值相除)
检查average_wait执行:
SELECT EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT FROM V$SYSTEM_EVENT WHERE EVENT LIKE '%UNDO%';
检查RBS header get ratio执行:
SELECT N.NAME,S.USN,S.WRAPS, DECODE(S.WAITS,0,1,1- S.WAITS/S.GETS)"RBS HEADER GET RATIO" FROM V$ROLLSTAT S,V$ROLLNAME N WHERE S.USN=N.USN;
(正常时Ratio应该小于1%, count/value应该小于0.01%,average_wait最好为0，该值越小越好,RBS header get ratio应该大于95%)
查看排序段的性能:
SQL>SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('SORTS (MEMORY)', 'SORTS (DISK)');
7、查看数据库库对象:
SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*) COUNT# FROM ALL_OBJECTS GROUP BY OWNER, OBJECT_TYPE, STATUS;
8、查看数据库的版本:
SELECT * FROM V$VERSION;
9、查看数据库的创建日期和归档方式:
SELECT CREATED, LOG_MODE, LOG_MODE FROM V$DATABASE;
10、捕捉运行很久的SQL:
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;
11、查看数据表的参数信息:
SELECT PARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, TABLESPACE_NAME,PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT,NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE, FREELISTS,FREELIST_GROUPS, LOGGING, BUFFER_POOL, NUM_ROWS, BLOCKS,EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE,LAST_ANALYZED FROM DBA_TAB_PARTITIONS
--WHERE TABLE_NAME = :TNAME AND TABLE_OWNER = :TOWNER
ORDER BY PARTITION_POSITION;
f
12、查看还没提交的事务:
SELECT * FROM V$LOCKED_OBJECT;
SELECT * FROM V$TRANSACTION;
13、查找object为哪些进程所用:
SELECT P.SPID,S.SID,S.SERIAL# SERIAL_NUM,S.USERNAME USER_NAME,
A.TYPE OBJECT_TYPE,S.OSUSER OS_USER_NAME,A.OWNER,A.OBJECT OBJECT_NAME,DECODE(SIGN(48 - COMMAND),1,
TO_CHAR(COMMAND), 'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION,
P.PROGRAM ORACLE_PROCESS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,S.STATUS SESSION_STATUS FROM V$SESSION S, V$ACCESS A, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.TYPE = 'USER' AND A.SID = S.SID AND A.OBJECT='SUBSCRIBER_ATTR'ORDER BY S.USERNAME, S.OSUSER;
14、查看回滚段:
SQL>COL NAME FORMAT A10
SQL>SET LINESIZE 100
SQL>SELECT ROWNUM, SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAME NAME, V$ROLLSTAT.EXTENTS EXTENTS, V$ROLLSTAT.RSSIZE SIZE_IN_BYTES, V$ROLLSTAT.XACTS XACTS, V$ROLLSTAT.GETS GETS, V$ROLLSTAT.WAITS WAITS, V$ROLLSTAT.WRITES WRITES, SYS.DBA_ROLLBACK_SEGS.STATUS STATUS FROM V$ROLLSTAT, SYS.DBA_ROLLBACK_SEGS, V$ROLLNAME WHERE V$ROLLNAME.NAME(+) = SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAME AND V$ROLLSTAT.USN (+) = V$ROLLNAME.USN ORDER BY ROWNUM;
15、耗资源的进程(top session):
SELECT S.SCHEMANAME SCHEMA_NAME,DECODE(SIGN(48 - COMMAND), 1, TO_CHAR(COMMAND),
'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION,STATUS SESSION_STATUS,S.OSUSER OS_USER_NAME,
S.SID,P.SPID,S.SERIAL# SERIAL_NUM,NVL(S.USERNAME,'[ORACLE PROCESS]') USER_NAME,S.TERMINAL TERMINAL,
S.PROGRAM PROGRAM,ST.VALUE CRITERIA_VALUE
FROM V$SESSTAT ST,V$SESSION S,V$PROCESS P
WHERE ST.SID = S.SID AND ST.STATISTIC# = TO_NUMBER('38')
AND ('ALL'='ALL' OR S.STATUS ='ALL')
AND P.ADDR=S.PADDR ORDER BY ST.VALUE DESC,P.SPID ASC,S.USERNAME ASC,S.OSUSER ASC;
根据PID查找相应的语句:
SELECT A.USERNAME, A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT
FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C
WHERE B.SPID=SPID AND B.ADDR=A.PADDR AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;
//查询回滚段的 大小和优化参数
select * from DBA_SEGMENTS
where Segment_Type = ’ROLLBACK’;
select N.Name, /* rollback segment name */
S.OptSize /* rollback segment OPTIMAL size */
from V$ROLLNAME N, V$ROLLSTAT S
where N.USN=S.USN;
statspack
--statspack包和dbms_包的存储位值。
$ORACLE_HOME/RDBMS/ADMIN
--执行STATSPACK，建立性能快照表空间
create tablespace perfstat datafile '/u02/oradata/dbnms/perfstat.dbf' size 500M extent management local;
-- 安装STATSPACK
@$ORACLE_HOME/rdbms/admin/spcreate.sql;
-- 获取性能数据，可以生成多个快照
sqlplus perfstat
execute statspack.snap;
-- 生成性能快照的报表
sqlplus perfstat
select min(snap_id) snapid_min, max(snap_id) snapid_max from stats$snapshot;
@$ORACLE_HOME/rdbms/admin/spreport;
-- 该报告中有关于性能的重要信息，如前5位的等待事件、cache大小、各种内存结构的命中率、每秒及每事务逻辑、物理读写数据块数、性能最差的sql语句等
--确定性能瓶颈。从v$system_event、v$session_event、v$session_wait中获得等待事件，找出影响性能的对象和sql语句
-- 首先，利用v$system_event视图执行下面的查询查看数据库中某些常见的等待事件：
select * from v$system_event
where event in ('buffer busy waits',
'db file sequential read',
'db file scattered read',
'enqueue',
'free buffer waits',
'latch free',
'log file parallel write',
'log file sync');
-- 接着，利用下面对v$session_event和v$session视图进行的查询，研究具有对上面显示的内容有贡献的等待事件的会话：
select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
from v$session s,v$session_event se
where s.sid = se.sid
and se.event not like 'SQL*Net%'
and s.status = 'ACTIVE'
and s.username is not null;
-- 使用下面查询找到与所连接的会话有关的当前等待事件。这些信息是动态的，为了查看一个会话的等待最多的事件是什么，需要多次执行此查询。
select sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait SEC_IN_WAIT
from v$session s,v$session_wait sw
where s.sid = sw.sid
and sw.event not like 'SQL*Net%'
and s.username is not null
order by sw.wait_time desc;
-- 查询会话等待事件的详细信息
select sid,event,p1text,p1,p2text,p2,p3text,p3
from v$session_wait
where sid between &1 and &2
and event not like '%SQL%'
and event not like '%rdbms%';
-- 利用P1、P2的信息，找出等待事件的相关的段
select owner,segment_name,segment_type,tablespace_name
from dba_extents
where file_id = &fileid_in
and &blockid_in between block_id and block_id + blocks - 1;
-- 获得操作该段的sql语句：
select sid, getsqltxt(sql_hash_value,sql_address)
from v$session
where sid = &sid_in;
-- getsqltxt函数
create or replace
function GetSQLtxt (hashaddr_in in v$sqltext.hash_value%type,
addr_in in v$sqltext.address%type)
return varchar2
is
temp_sqltxt varchar2(32767);
cursor sqlpiece_cur
is
select piece,sql_text
from v$sqltext
where hash_value = hashaddr_in
and address = addr_in
order by piece;
begin
for sqlpiece_rec in sqlpiece_cur
loop
temp_sqltxt := temp_sqltxt || sqlpiece_rec.sql_text;
end loop;
return temp_sqltxt;
end GetSQLtxt;
-- 至此已经找到影响性能的对象和sql语句，可以有针对性地优化
4． 把等待事件记入跟踪文件
重点：如果在跟踪系统上的等待事件时，由于某种原因遇到了麻烦，则可以将这些等待事件记入一个跟踪文件。
方法：
-- 对于当前会话：
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 12';
-- 执行应用程序，然后在USER_DUMP_DEST指出的目录中找到跟踪文件。
-- 查看文件中以词WAIT开始的所有行。
-- 对于其它的会话
-- 确定会话的进程ID（SPID）。下面的查询识别出名称以A开始的所有用户的会话进程ID：
select S.Username, P.Spid from V$SESSION S, V$PROCESS P
where S.PADDR = P.ADDR and S.Username like 'A%';
-- 以 sysdba 进入sqlplus执行
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
oradebug setospid <SPID>
oradebug unlimit
oradebug event 10046 trace name context forever, level X /* Where X = (1,4,8,12) */
-- 跟踪某个时间间隔得会话应用程序。
-- 在USER_DUMP_DEST 的值指出的目录中利用SPID查看跟踪文件
-- 查看文件中以词WAIT开始的所有行。
--UNIX上的监控
使用通用性的工具，包括sar、iostat、cpustat、mpstat、netstat、top、osview等。
--CPU使用情况
sar -u 5 1000
%sys和%wio的数值应该小于百分之10到15
2） 设备使用情况
sar -d 5 1000
在%busy超过60%时，最佳设备利用率开始降低；在具有足够磁盘高速缓存的系统上，认为avserv为100毫秒的值非常高。
3） 虚拟内存使用情况
vmstat -S 5 1000
执行队列（r）应该明确的平均小于（2*CPU数目）
--sql_trace,10046事件
参数timed_statistics设为true
alter session set MAX_DUMP_FILE_SIZE=unlimited;
session级设跟踪：alter session set sql_trace=true;
alter session set sql_trace=false;
跟踪其它用户：desc dbms_system
select sid,serial#,username from v$session where username is not null;
exec dbms_system.set_sql_trace_in_session(sid,serial#,true)
exec dbms_system_set_sql_trace_in_session(sid,serial#,false)
对当前session设置：
alter session set events '10046 trace name context forever';
alter session set events '10046 trace name context forever, level 8';
alter session set events '10046 trace name context off';
对其他用户session设置：
desc dbms_system
select sid,serial#,username from v$session where username is not null;
exec dbms_system.set_ev(sid,serial#,10046,8,'username');
exec dbms_system.set_ev(sid,serial#,10046,0,'username');
获取跟踪文件：
SQL> set echo on
SQL> @gettrcnameunix
SQL> SELECT d.VALUE
2 || '/'
3 || LOWER (RTRIM (i.INSTANCE, CHR (0)))
4 || '_ora_'
5 || p.spid
6 || '.trc' trace_file_name
7 FROM (SELECT p.spid
8 FROM v$mystat m, v$session s, v$process p
9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
10 (SELECT t.INSTANCE
11 FROM v$thread t, v$parameter v
12 WHERE v.NAME = 'thread'
13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14 (SELECT VALUE
15 FROM v$parameter
16 WHERE NAME = 'user_dump_dest') d
17 /
------谁正在访问数据库?
Select c.sid, c.serial#,c.username,a.object_id,b.object_name,
c.program,c.status,d.name,c.osuser
from v$Locked_object a,
All_objects b,
v$session c,
audit_actions d
where a.object_id=b.object_id
and a.session_id =c.sid(+)
and c.command=d.action;
alter system kill session '&1,&2';
Select a.sid,a.serial#,a.username,a.status,a.program,b.name,a.osuser
from v$session a,audit_actions b
where a.command=b.action
And username='&1';
------谁被锁住?
Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.name
from v$session a,audit_actions b
where a.command=b.action
AND LOCKWAIT IS NOT NULL;
------谁在锁表?
Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.name
from v$session a,audit_actions b
where a.command=b.action
AND STATUS='INACTIVE';
Select sid, serial#, object_name, row_wait_block#,
row_wait_row#, row_wait_file#
from all_objects, v$session
where row_wait_obj#=object_id and type='USER'
and lockwait is not null ;
Select sl.username, sl.sid, sl.serial#
from v_$lock l1, v$session s1
where exists (select * from v_$lock l2, v$session s2
where l2.sid=s2.sid and l2.id1=l1
and s2.lockwait=l2.kaddr
and request=0
and l1.sid=s1.sid) ;
select count(*) from v$session;
select count(*) from sys.v_$process;
select count(*) from sys.v_$transaction;
ZYP_35.98
--查看哪些包要固定
COLUMN OWNER FORMAT A10
Select owner, name, type,
source_size+code_size+parsed_size+error_size BYPES
from dba_object_size
where type='PACKAGE BODY' ORDER BY 4 DESC ;
--查看一个用户拥有哪些表空间的实体信息:
Select tablespace_name, owner, segment_name,segment_type
from dba_segments
where owner-'SyS'
and segment_type_-'ROLLBACK'
order by tablespace_name, owner, segment_name ;
break on owner on segment_name
COLUMN segment_name FORMAT A15
cOLUMN tablespace_name FORMAT A15
COLUMN file_name FORMAT A20
SELECT A.owner, a.segment_name, b.tablespace_name, b.file_name,
sum(a.bytes) bytes
from dba_extents a, dba_data_files b
where a.file_id-b.file_id group by a.owner, a.segment_name,
b.tablespace_name, b.file_name ;
------看内存缓冲区使用效率的指数是命中率HITS:
Hits=Logical_reads/(logical_reads+physical_reads)
其中：logical_reads=db_block_gets+consistent_reads
select cur.value db, con.value con, phy.value phy,
(cur.value+con.value)/(cur.value+con.value+phy.value)*100 HITS
from v$sysstat cur, v$sysstat con, v$sysstat phy
where CUR.NAME='db block gets' AND
CON.NAME='consistent gets' AND
PHY.NAME='physical reads' ;
--查看有事务在哪几个回退段中：
COLUMN u FORMAT A15
COLUMN s FORMAT A15
COLUMN s FORMAT A80
select osuser o, username u, segment_name s, sa.sql_text
from v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa
where s.taddr=t.addr and t.sidusn=r.segmant_id(+)
and s.sql_address=sa.address(+) ;
--用系统管理员，查看当前数据库有几个用户连接：
select username,sid,serial# from v$session;
--如果要停某个连接用
SQL> alter system kill session 'sid,serial#';
如果这命令不行,找它UNIX的进程数
SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;
说明：21是某个连接的sid数
然后用 kill 命令杀此进程号。
--查找当前正在执行的语句
col processid format 999999999
col sid format 999999
col serial# format 999999
col waitevent format A25
col machine format A10
col osuser format A10
col username format A10
col status format A10
col sqltext format A35
select distinct
t2.spid ProcessID,
t1.sid SID,
t1.serial# Serial#,
t5.event WaitEvent,
t1.machine Machine,
t1.osuser OSUSER,
t1.username Username,
t1.status Status,
t3.sql_text SQLText
from v$session t1,
v$process t2,
v$sql t3,
v$session_wait t5
where t1.paddr = t2.addr and
t1.sql_hash_value = t3.hash_value and
t1.sql_address = t3.address and
t1.sid = t5.sid and
t1.username is not null and
t1.status = 'ACTIVE'
order by t1.sid;
--当前被lock住的对象信息
select distinct
t1.start_time,
t2.sid,
t2.serial#,
t1.ses_addr,
t1.xidusn,
t3.owner,
t3.object_name,
t4.os_user_name,
t4.oracle_username
from v$transaction t1,
v$session t2,
dba_objects t3,
v$locked_object t4
where t1.ses_addr = t2.saddr and
t4.session_id = t2.sid and
t4.object_id = t3.object_id and
t3.object_type = 'TABLE'
order by 1;
--定位TRANSACTION使用的是那个RBS。
COLUMN "Object Name" format A20
SELECT SUBSTR (a.os_user_name, 1, 8) "OS User",
SUBSTR (a.oracle_username, 1, 8) "DB User",
SUBSTR (b.owner, 1, 8) "Schema",
SUBSTR (b.object_name, 1, 20) "Object Name",
SUBSTR (b.object_type, 1, 10) "Type",
SUBSTR (c.segment_name, 1, 5) "RBS",
SUBSTR (d.used_urec, 1, 12) "# of Records"
FROM v$locked_object a,
dba_objects b,
dba_rollback_segs c,
v$transaction d,
v$session e
WHERE a.object_id = b.object_id
AND a.xidusn = c.segment_id
AND a.xidusn = d.xidusn
AND a.xidslot = d.xidslot
AND d.addr = e.taddr;
--生成一个systemdump，dump文件存放于udump目录下。
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';
注意：如果当前数据库的并发连接较多或事务较多，此命令可能会执行较慢，生成的dump文件较大（可能会到几百兆）,因此建议只有在数据库出现问题或挂起时才执行。如果为RAC环境可以执行下面的命令来dump多个节点的信息。
oradebug -g all dump systemstate 10
Dump结果需要提交给ORACLESUPPORT进行分析，最好通过metalink上的TAR来进行提交。
--awr报告
@?/rdbms/admin/awrrpt
--sql执行计划
--sql涉及的表大小
--涉及的索引的大小
--sql涉及的
--操作系统参数
--操作系统日志
--数据库参数
--show parameter
使用DBMS_SHARED_POOL.KEEP加载对象
exec dbms_shared_pool.keep('DBMS_SYS_SQL');
--keep常用小表
--
大表重组(可选)
--
60G的分区表BF_PAY_FEE_T重组
--
关键索引重建
ALTER INDEX UNITELE.I_HLR_CONTROL rebuild online;
select OWNER,SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024/1024,TABLESPACE_NAME
from dba_segments where segment_name = 'BF_PAY_FEE_OPER_I';
select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS from dba_ind_partitions where index_name = '&t';
首先,Oracle10g数据库运行的状况如何这个问题可以通过下面的查询来获得：
select METRIC_NAME,VALUE
from SYS.V_$SYSMETRIC
where METRIC_NAME IN ('Database CPU Time Ratio','Database Wait Time Ratio')
AND INTSIZE_CSEC = (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);
METRIC_NAME VALUE
------------------------------ ----------------------------------
Database Wait Time Ratio 31.3499111
Database CPU Time Ratio 68.6500888
select CASE METRIC_NAME
WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'
WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MINVAL/100),2)
WHEN 'Response Time Per Txn' then ROUND((MINVAL/100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MAXVAL/100),2)
WHEN 'Response Time Per Txn' then ROUND((MAXVAL/100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((AVERAGE/100),2)
WHEN 'Response Time Per Txn' then ROUND((AVERAGE/100),2)
ELSE AVERAGE
END AVERAGE
from SYS.V_$SYSMETRIC_SUMMARY
where METRIC_NAME in ('CPU Usage Per Sec',
'CPU Usage Per Txn',
'Database CPU Time Ratio',
'Database Wait Time Ratio',
'Executions Per Sec',
'Executions Per Txn',
'Response Time Per Txn',
'SQL Service Response Time',
'User Transaction Per Sec')
ORDER BY 1;
select physical_reads/(db_block_gets+consistent_gets) from v$buffer_pool_statistics;
--监控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
--回滚段的争用情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
--监控表空间的 I/O 比例
select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;
--监控文件系统的 I/O 比例
select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
--在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
--监控 SGA 的命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
--监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
--监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
--显示所有数据库对象的类别和大小
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
--监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
--监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
--监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
--监控字典缓冲区
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
--找ORACLE字符集
select * from sys.props$ where name='NLS_CHARACTERSET';
--监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5时，参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时，参数需加大
-- 碎片程度
select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;
alter tablespace name coalesce;
alter table name deallocate unused;
create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;
select * from ts_blocks_v;
select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;
--查看碎片程度高的表
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);
--表、索引的存储情况检查
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;
--找使用CPU多的用户session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
--查看表空间物理文件的名称及大小:
SQL> SELECT TABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;
--查看回滚段名称及大小:
SQL> SELECT SEGMENT_NAME,
TABLESPACE_NAME,
R.STATUS,
(INITIAL_EXTENT / 1024) INITIALEXTENT,
(NEXT_EXTENT / 1024) NEXTEXTENT,
MAX_EXTENTS,
V.CUREXT CUREXTENT
FROM DBA_ROLLBACK_SEGS R, V$ROLLSTAT V
WHERE R.SEGMENT_ID = V.USN(+)
ORDER BY SEGMENT_NAME;
--如何查看某个回滚段里面，跑的什么事物或者正在执行什么sql语句:
SQL> SELECT D.SQL_TEXT, A.NAME
FROM V$ROLLNAME A, V$TRANSACTION B, V$SESSION C, V$SQLTEXT D
WHERE A.USN = B.XIDUSN
AND B.ADDR = C.TADDR
AND C.SQL_ADDRESS = D.ADDRESS
AND C.SQL_HASH_VALUE = D.HASH_VALUE
AND A.USN = 1;
(备注：你要看哪个，就把usn=?写成几就行了)
--如何查看当前SQL*PLUS用户的sid和serial#:
SQL>SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE AUDSID=USERENV('SESSIONID');
--怎么判断当前正在使用何种SQL优化方式:
用EXPLAIN PLAN產生EXPLAIN PLAN?檢查PLAN_TABLE中ID=0的POSITION列的值
SQL>SELECT DECODE(NVL(POSITION,-1),-1,'RBO',1,'CBO') FROM PLAN_TABLE WHERE ID=0;
如何查看系统当前最新的SCN号：
SQL>SELECT MAX(KTUXESCNW * POWER(2,32) + KTUXESCNB) FROM X$KTUXE;
--在ORACLE中查找TRACE文件的脚本:
SQL>SELECT U_DUMP.VALUE || '/' || INSTANCE.VALUE || '_ORA_' ||
V$PROCESS.SPID || NVL2(V$PROCESS.TRACEID, '_' || V$PROCESS.TRACEID, NULL ) || '.TRC'"TRACE FILE" FROM V$PARAMETER U_DUMP CROSS JOIN V$PARAMETER INSTANCE CROSS JOIN V$PROCESS JOIN V$SESSION ON V$PROCESS.ADDR = V$SESSION.PADDR WHERE U_DUMP.NAME = 'USER_DUMP_DEST' AND
INSTANCE.NAME = 'INSTANCE_NAME' AND V$SESSION.AUDSID=SYS_CONTEXT('USERENV','SESSIONID');
SQL>SELECT D.VALUE || '/ORA_' || P.SPID || '.TRC' TRACE_FILE_NAME
FROM (SELECT P.SPID FROM SYS.V_$MYSTAT M,SYS.V_$SESSION S,
SYS.V_$PROCESS P WHERE M.STATISTIC# = 1 AND
S.SID = M.SID AND P.ADDR = S.PADDR) P,(SELECT VALUE FROM SYS.V_$PARAMETER WHERE NAME ='USER_DUMP_DEST') D;
如何查看客户端登陆的IP地址:
SQL>SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL;
如何在生产数据库中创建一个追踪客户端IP地址的触发器：
SQL>CREATE OR REPLACE TRIGGER ON_LOGON_TRIGGER AFTER LOGON ON DATABASE
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;
REM 记录登陆信息的触发器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'
BEGIN
INSERT INTO SESSION_HISTORY SELECT USERNAME,SID,SERIAL#,AUDSID,OSUSER,ACTION,SYSDATE,NULL,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,MACHINE,PROGRAM FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID');
END;
--查询当前日期:
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD,HH24:MI:SS') FROM DUAL;
查看所有表空间对应的数据文件名：
SQL>SELECT DISTINCT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES;
查看表空间的使用情况:
SQL>SELECT SUM(BYTES)/(1024*1024) AS FREE_SPACE,TABLESPACE_NAME
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
SQL>SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
COLUMN TABLESPACE_NAME FORMAT A18;
COLUMN SUM_M FORMAT A12;
COLUMN USED_M FORMAT A12;
COLUMN FREE_M FORMAT A12;
COLUMN PTO_M FORMAT 9.99;
SELECT S.TABLESPACE_NAME,CEIL(SUM(S.BYTES/1024/1024))||'M' SUM_M,CEIL(SUM(S.USEDSPACE/1024/1024))||'M' USED_M,CEIL(SUM(S.FREESPACE/1024/1024))||'M' FREE_M, SUM(S.USEDSPACE)/SUM(S.BYTES) PTUSED FROM (SELECT B.FILE_ID,B.TABLESPACE_NAME,B.BYTES, (B.BYTES-SUM(NVL(A.BYTES,0))) USEDSPACE, SUM(NVL(A.BYTES,0)) FREESPACE,(SUM(NVL(A.BYTES,0))/(B.BYTES)) * 100 FREEPERCENTRATIO FROM SYS.DBA_FREE_SPACE A,SYS.DBA_DATA_FILES B WHERE A.FILE_ID(+)=B.FILE_ID GROUP BY B.FILE_ID,B.TABLESPACE_NAME,B.BYTES ORDER BY B.TABLESPACE_NAME) S GROUP BY S.TABLESPACE_NAME ORDER BY SUM(S.FREESPACE)/SUM(S.BYTES) DESC;
查看数据文件的hwm（可以resize的最小空间）和文件头大小:
SELECT V1.FILE_NAME,V1.FILE_ID,NUM1 TOTLE_SPACE,NUM3 FREE_SPACE,
NUM1-NUM3 "USED_SPACE(HWM)",NVL(NUM2,0) DATA_SPACE,NUM1-NUM3-NVL(NUM2,0) FILE_HEAD
FROM
(SELECT FILE_NAME,FILE_ID,SUM(BYTES) NUM1 FROM DBA_DATA_FILES GROUP BY FILE_NAME,FILE_ID) V1,
(SELECT FILE_ID,SUM(BYTES) NUM2 FROM DBA_EXTENTS GROUP BY FILE_ID) V2,
(SELECT FILE_ID,SUM(BYTES) NUM3 FROM DBA_FREE_SPACE GROUP BY FILE_ID) V3
WHERE V1.FILE_ID=V2.FILE_ID(+) AND V1.FILE_ID=V3.FILE_ID(+);
数据文件大小及头大小:
SELECT V1.FILE_NAME,V1.FILE_ID,
NUM1 TOTLE_SPACE,
NUM3 FREE_SPACE,
NUM1-NUM3 USED_SPACE,
NVL(NUM2,0) DATA_SPACE,
NUM1-NUM3-NVL(NUM2,0) FILE_HEAD
FROM
(SELECT FILE_NAME,FILE_ID,SUM(BYTES) NUM1 FROM DBA_DATA_FILES GROUP BY FILE_NAME,FILE_ID) V1,
(SELECT FILE_ID,SUM(BYTES) NUM2 FROM DBA_EXTENTS GROUP BY FILE_ID) V2,
(SELECT FILE_ID,SUM(BYTES) NUM3 FROM DBA_FREE_SPACE GROUP BY FILE_ID) V3
WHERE V1.FILE_ID=V2.FILE_ID(+)
AND V1.FILE_ID=V3.FILE_ID(+);
(运行以上查询，我们可以如下信息：
Totle_pace:该数据文件的总大小，字节为单位
Free_space:该数据文件的剩于大小，字节为单位
Used_space:该数据文件的已用空间，字节为单位
Data_space:该数据文件中段数据占用空间，也就是数据空间，字节为单位
File_Head:该数据文件头部占用空间，字节为单位)
--数据库各个表空间增长情况的检查：
SQL>SELECT A.TABLESPACE_NAME,(1-(A.TOTAL)/B.TOTAL)*100 USED_PERCENT
FROM (SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME,SUM(BYTES) TOTAL FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) B WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME;
SQL>SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",F.TOTAL_BYTES"空闲空间(M)",
F.MAX_BYTES "最大块(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;
--查看各个表空间占用磁盘情况:
COL TABLESPACE_NAME FORMAT A20;
SELECT B.FILE_ID FILE_ID,
B.TABLESPACE_NAME TABLESPACE_NAME,
B.BYTES BYTES,
(B.BYTES-SUM(NVL(A.BYTES,0))) USED,
SUM(NVL(A.BYTES,0)) FREE,
SUM(NVL(A.BYTES,0))/(B.BYTES)*100 PERCENT
FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
WHERE A.FILE_ID=B.FILE_ID
GROUP BY B.TABLESPACE_NAME,B.FILE_ID,B.BYTES
ORDER BY B.FILE_ID;
--数据库对象下一扩展与表空间的free扩展值的检查：
SQL>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;
--Disk Read最高的SQL语句的获取：
SQL>SELECT SQL_TEXT FROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM<=5;
--查找前十条性能差的sql
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 ;
--等待时间最多的5个系统等待事件的获取：
SQL>SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%' ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
--查看当前等待事件的会话:
COL USERNAME FORMAT A10
SET LINE 120
COL EVENT FORMAT A30
SELECT SE.SID,S.USERNAME,SE.EVENT,SE.TOTAL_WAITS,SE.TIME_WAITED,SE.AVERAGE_WAIT
FROM V$SESSION S,V$SESSION_EVENT SE WHERE S.USERNAME IS NOT NULL AND SE.SID=S.SID
AND S.STATUS='ACTIVE' AND SE.EVENT NOT LIKE '%SQL*NET%';
SELECT SID, EVENT, P1, P2, P3, WAIT_TIME, SECONDS_IN_WAIT, STATE FROM V$SESSION_WAIT WHERE EVENT NOT LIKE '%MESSAGE%' AND EVENT NOT LIKE 'SQL*NET%' AND EVENT NOT LIKE '%TIMER%' AND EVENT != 'WAKEUP TIME MANAGER';
找到与所连接的会话有关的当前等待事件：
SELECT SW.SID,S.USERNAME,SW.EVENT,SW.WAIT_TIME,SW.STATE,SW.SECONDS_IN_WAIT SEC_IN_WAIT
FROM V$SESSION S,V$SESSION_WAIT SW WHERE S.USERNAME IS NOT NULL AND SW.SID=S.SID
AND SW.EVENT NOT LIKE '%SQL*NET%' ORDER BY SW.WAIT_TIME DESC;
Oracle所有回滚段状态的检查：
SQL>SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,DBA_ROLLBACK_SEGS.STATUS FROM DBA_ROLLBACK_SEGS,V$DATAFILE WHERE FILE_ID=FILE#;
Oracle回滚段扩展信息的检查:
COL NAME FORMAT A10
SET LINESIZE 140
SELECT SUBSTR(NAME,1,40) NAME,EXTENTS,RSSIZE,OPTSIZE,AVEACTIVE,EXTENDS,WRAPS,SHRINKS,HWMSIZE
FROM V$ROLLNAME RN,V$ROLLSTAT RS WHERE (RN.USN=RS.USN);
EXTENTS:回滚段中的盘区数量。
Rssize:以字节为单位的回滚段的尺寸。
optsize：为optimal参数设定的值。
Aveactive:从回滚段中删除盘区时释放的以字节为单位的平均空间的大小。
Extends:系统为回滚段增加的盘区的次数。
Shrinks:系统从回滚段中清除盘区（即回滚段收缩）的次数。回滚段每次清除盘区时，系统可能会从这个回滚段中消除一个或多个盘区。
Hwmsize:回滚段尺寸的上限，即回滚段曾经达到的最大尺寸。
(如果回滚段平均尺寸接近OPTIMAL的值，那么说明OPTIMAL的值设置正确，如果回滚段动态增长次数或收缩次数很高，那么需要提高OPTIMAL的值)
查看回滚段的使用情况，哪个用户正在使用回滚段的资源:
SELECT S.USERNAME, U.NAME FROM V$TRANSACTION T,V$ROLLSTAT R,
V$ROLLNAME U,V$SESSION S WHERE S.TADDR=T.ADDR AND
T.XIDUSN=R.USN AND R.USN=U.USN ORDER BY S.USERNAME;
如何查看一下某个shared_server正在忙什么:
SELECT A.USERNAME,A.MACHINE,A.PROGRAM,A.SID,
A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT
FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C
WHERE B.SPID=13161 AND B.ADDR=A.PADDR
AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;
数据库共享池性能检查:
SELECT NAMESPACE,GETS,GETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONS FROM V$LIBRARYCACHE WHERE NAMESPACE IN('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');
检查数据重载比率:
SELECT SUM(RELOADS)/SUM(PINS)*100 "RELOAD RATIO" FROM
V$LIBRARYCACHE;
检查数据字典的命中率:
SELECT 1-SUM(GETMISSES)/SUM(GETS) "DATA DICTIONARY HIT
RATIO" FROM V$ROWCACHE;
(对于library cache, gethitratio和pinhitratio应该大于90%,对于数据重载比率,reload ratio应该小于1%,对于数据字典的命中率,data dictionary hit ratio应该大于85%)
检查共享内存的剩余情况:
SELECT REQUEST_MISSES, REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED;
(对于共享内存的剩余情况, request_misses 和request_failures应该接近0)
数据高速缓冲区性能检查:
SELECT 1-P.VALUE/(B.VALUE+C.VALUE) "DB BUFFER CACHE HIT RATIO" FROM V$SYSSTAT P,V$SYSSTAT B,V$SYSSTAT C WHERE P.NAME='PHYSICAL READS' AND B.NAME='DB BLOCK GETS' AND C.NAME='CONSISTENT GETS';
检查buffer pool HIT_RATIO执行
SELECT NAME, (PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)) "MISS_HIT_RATIO" FROM V$BUFFER_POOL_STATISTICS WHERE (DB_BLOCK_GETS+ CONSISTENT_GETS)> 0;
(正常时db buffer cache hit ratio 应该大于90%,正常时buffer pool MISS_HIT_RATIO 应该小于10%)
数据库回滚段性能检查:
检查Ratio执行
SELECT SUM(WAITS)* 100 /SUM(GETS) "RATIO", SUM(WAITS) "WAITS", SUM(GETS) "GETS" FROM V$ROLLSTAT;
检查count/value执行:
SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS LIKE '%UNDO%';
SELECT VALUE FROM V$SYSSTAT WHERE NAME='CONSISTENT GETS';
(两者的value值相除)
检查average_wait执行:
SELECT EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT FROM V$SYSTEM_EVENT WHERE EVENT LIKE '%UNDO%';
检查RBS header get ratio执行:
SELECT N.NAME,S.USN,S.WRAPS, DECODE(S.WAITS,0,1,1- S.WAITS/S.GETS)"RBS HEADER GET RATIO" FROM V$ROLLSTAT S,V$ROLLNAME N WHERE S.USN=N.USN;
(正常时Ratio应该小于1%, count/value应该小于0.01%,average_wait最好为0，该值越小越好,RBS header get ratio应该大于95%)
查看排序段的性能:
SQL>SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('SORTS (MEMORY)', 'SORTS (DISK)');
7、查看数据库库对象:
SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*) COUNT# FROM ALL_OBJECTS GROUP BY OWNER, OBJECT_TYPE, STATUS;
8、查看数据库的版本:
SELECT * FROM V$VERSION;
9、查看数据库的创建日期和归档方式:
SELECT CREATED, LOG_MODE, LOG_MODE FROM V$DATABASE;
10、捕捉运行很久的SQL:
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;
11、查看数据表的参数信息:
SELECT PARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, TABLESPACE_NAME,PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT,NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE, FREELISTS,FREELIST_GROUPS, LOGGING, BUFFER_POOL, NUM_ROWS, BLOCKS,EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE,LAST_ANALYZED FROM DBA_TAB_PARTITIONS
--WHERE TABLE_NAME = :TNAME AND TABLE_OWNER = :TOWNER
ORDER BY PARTITION_POSITION;
f
12、查看还没提交的事务:
SELECT * FROM V$LOCKED_OBJECT;
SELECT * FROM V$TRANSACTION;
13、查找object为哪些进程所用:
SELECT P.SPID,S.SID,S.SERIAL# SERIAL_NUM,S.USERNAME USER_NAME,
A.TYPE OBJECT_TYPE,S.OSUSER OS_USER_NAME,A.OWNER,A.OBJECT OBJECT_NAME,DECODE(SIGN(48 - COMMAND),1,
TO_CHAR(COMMAND), 'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION,
P.PROGRAM ORACLE_PROCESS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,S.STATUS SESSION_STATUS FROM V$SESSION S, V$ACCESS A, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.TYPE = 'USER' AND A.SID = S.SID AND A.OBJECT='SUBSCRIBER_ATTR'ORDER BY S.USERNAME, S.OSUSER;
14、查看回滚段:
SQL>COL NAME FORMAT A10
SQL>SET LINESIZE 100
SQL>SELECT ROWNUM, SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAME NAME, V$ROLLSTAT.EXTENTS EXTENTS, V$ROLLSTAT.RSSIZE SIZE_IN_BYTES, V$ROLLSTAT.XACTS XACTS, V$ROLLSTAT.GETS GETS, V$ROLLSTAT.WAITS WAITS, V$ROLLSTAT.WRITES WRITES, SYS.DBA_ROLLBACK_SEGS.STATUS STATUS FROM V$ROLLSTAT, SYS.DBA_ROLLBACK_SEGS, V$ROLLNAME WHERE V$ROLLNAME.NAME(+) = SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAME AND V$ROLLSTAT.USN (+) = V$ROLLNAME.USN ORDER BY ROWNUM;
15、耗资源的进程(top session):
SELECT S.SCHEMANAME SCHEMA_NAME,DECODE(SIGN(48 - COMMAND), 1, TO_CHAR(COMMAND),
'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION,STATUS SESSION_STATUS,S.OSUSER OS_USER_NAME,
S.SID,P.SPID,S.SERIAL# SERIAL_NUM,NVL(S.USERNAME,'[ORACLE PROCESS]') USER_NAME,S.TERMINAL TERMINAL,
S.PROGRAM PROGRAM,ST.VALUE CRITERIA_VALUE
FROM V$SESSTAT ST,V$SESSION S,V$PROCESS P
WHERE ST.SID = S.SID AND ST.STATISTIC# = TO_NUMBER('38')
AND ('ALL'='ALL' OR S.STATUS ='ALL')
AND P.ADDR=S.PADDR ORDER BY ST.VALUE DESC,P.SPID ASC,S.USERNAME ASC,S.OSUSER ASC;
根据PID查找相应的语句:
SELECT A.USERNAME, A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT
FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C
WHERE B.SPID=SPID AND B.ADDR=A.PADDR AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;
//查询回滚段的 大小和优化参数
select * from DBA_SEGMENTS
where Segment_Type = ’ROLLBACK’;
select N.Name, /* rollback segment name */
S.OptSize /* rollback segment OPTIMAL size */
from V$ROLLNAME N, V$ROLLSTAT S
where N.USN=S.USN;
statspack
--statspack包和dbms_包的存储位值。
$ORACLE_HOME/RDBMS/ADMIN
--执行STATSPACK，建立性能快照表空间
create tablespace perfstat datafile '/u02/oradata/dbnms/perfstat.dbf' size 500M extent management local;
-- 安装STATSPACK
@$ORACLE_HOME/rdbms/admin/spcreate.sql;
-- 获取性能数据，可以生成多个快照
sqlplus perfstat
execute statspack.snap;
-- 生成性能快照的报表
sqlplus perfstat
select min(snap_id) snapid_min, max(snap_id) snapid_max from stats$snapshot;
@$ORACLE_HOME/rdbms/admin/spreport;
-- 该报告中有关于性能的重要信息，如前5位的等待事件、cache大小、各种内存结构的命中率、每秒及每事务逻辑、物理读写数据块数、性能最差的sql语句等
--确定性能瓶颈。从v$system_event、v$session_event、v$session_wait中获得等待事件，找出影响性能的对象和sql语句
-- 首先，利用v$system_event视图执行下面的查询查看数据库中某些常见的等待事件：
select * from v$system_event
where event in ('buffer busy waits',
'db file sequential read',
'db file scattered read',
'enqueue',
'free buffer waits',
'latch free',
'log file parallel write',
'log file sync');
-- 接着，利用下面对v$session_event和v$session视图进行的查询，研究具有对上面显示的内容有贡献的等待事件的会话：
select se.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
from v$session s,v$session_event se
where s.sid = se.sid
and se.event not like 'SQL*Net%'
and s.status = 'ACTIVE'
and s.username is not null;
-- 使用下面查询找到与所连接的会话有关的当前等待事件。这些信息是动态的，为了查看一个会话的等待最多的事件是什么，需要多次执行此查询。
select sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait SEC_IN_WAIT
from v$session s,v$session_wait sw
where s.sid = sw.sid
and sw.event not like 'SQL*Net%'
and s.username is not null
order by sw.wait_time desc;
-- 查询会话等待事件的详细信息
select sid,event,p1text,p1,p2text,p2,p3text,p3
from v$session_wait
where sid between &1 and &2
and event not like '%SQL%'
and event not like '%rdbms%';
-- 利用P1、P2的信息，找出等待事件的相关的段
select owner,segment_name,segment_type,tablespace_name
from dba_extents
where file_id = &fileid_in
and &blockid_in between block_id and block_id + blocks - 1;
-- 获得操作该段的sql语句：
select sid, getsqltxt(sql_hash_value,sql_address)
from v$session
where sid = &sid_in;
-- getsqltxt函数
create or replace
function GetSQLtxt (hashaddr_in in v$sqltext.hash_value%type,
addr_in in v$sqltext.address%type)
return varchar2
is
temp_sqltxt varchar2(32767);
cursor sqlpiece_cur
is
select piece,sql_text
from v$sqltext
where hash_value = hashaddr_in
and address = addr_in
order by piece;
begin
for sqlpiece_rec in sqlpiece_cur
loop
temp_sqltxt := temp_sqltxt || sqlpiece_rec.sql_text;
end loop;
return temp_sqltxt;
end GetSQLtxt;
-- 至此已经找到影响性能的对象和sql语句，可以有针对性地优化
4． 把等待事件记入跟踪文件
重点：如果在跟踪系统上的等待事件时，由于某种原因遇到了麻烦，则可以将这些等待事件记入一个跟踪文件。
方法：
-- 对于当前会话：
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever, level 12';
-- 执行应用程序，然后在USER_DUMP_DEST指出的目录中找到跟踪文件。
-- 查看文件中以词WAIT开始的所有行。
-- 对于其它的会话
-- 确定会话的进程ID（SPID）。下面的查询识别出名称以A开始的所有用户的会话进程ID：
select S.Username, P.Spid from V$SESSION S, V$PROCESS P
where S.PADDR = P.ADDR and S.Username like 'A%';
-- 以 sysdba 进入sqlplus执行
alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
oradebug setospid <SPID>
oradebug unlimit
oradebug event 10046 trace name context forever, level X /* Where X = (1,4,8,12) */
-- 跟踪某个时间间隔得会话应用程序。
-- 在USER_DUMP_DEST 的值指出的目录中利用SPID查看跟踪文件
-- 查看文件中以词WAIT开始的所有行。
--UNIX上的监控
使用通用性的工具，包括sar、iostat、cpustat、mpstat、netstat、top、osview等。
--CPU使用情况
sar -u 5 1000
%sys和%wio的数值应该小于百分之10到15
2） 设备使用情况
sar -d 5 1000
在%busy超过60%时，最佳设备利用率开始降低；在具有足够磁盘高速缓存的系统上，认为avserv为100毫秒的值非常高。
3） 虚拟内存使用情况
vmstat -S 5 1000
执行队列（r）应该明确的平均小于（2*CPU数目）
--sql_trace,10046事件
参数timed_statistics设为true
alter session set MAX_DUMP_FILE_SIZE=unlimited;
session级设跟踪：alter session set sql_trace=true;
alter session set sql_trace=false;
跟踪其它用户：desc dbms_system
select sid,serial#,username from v$session where username is not null;
exec dbms_system.set_sql_trace_in_session(sid,serial#,true)
exec dbms_system_set_sql_trace_in_session(sid,serial#,false)
对当前session设置：
alter session set events '10046 trace name context forever';
alter session set events '10046 trace name context forever, level 8';
alter session set events '10046 trace name context off';
对其他用户session设置：
desc dbms_system
select sid,serial#,username from v$session where username is not null;
exec dbms_system.set_ev(sid,serial#,10046,8,'username');
exec dbms_system.set_ev(sid,serial#,10046,0,'username');
获取跟踪文件：
SQL> set echo on
SQL> @gettrcnameunix
SQL> SELECT d.VALUE
2 || '/'
3 || LOWER (RTRIM (i.INSTANCE, CHR (0)))
4 || '_ora_'
5 || p.spid
6 || '.trc' trace_file_name
7 FROM (SELECT p.spid
8 FROM v$mystat m, v$session s, v$process p
9 WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
10 (SELECT t.INSTANCE
11 FROM v$thread t, v$parameter v
12 WHERE v.NAME = 'thread'
13 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14 (SELECT VALUE
15 FROM v$parameter
16 WHERE NAME = 'user_dump_dest') d
17 /
------谁正在访问数据库?
Select c.sid, c.serial#,c.username,a.object_id,b.object_name,
c.program,c.status,d.name,c.osuser
from v$Locked_object a,
All_objects b,
v$session c,
audit_actions d
where a.object_id=b.object_id
and a.session_id =c.sid(+)
and c.command=d.action;
alter system kill session '&1,&2';
Select a.sid,a.serial#,a.username,a.status,a.program,b.name,a.osuser
from v$session a,audit_actions b
where a.command=b.action
And username='&1';
------谁被锁住?
Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.name
from v$session a,audit_actions b
where a.command=b.action
AND LOCKWAIT IS NOT NULL;
------谁在锁表?
Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.name
from v$session a,audit_actions b
where a.command=b.action
AND STATUS='INACTIVE';
Select sid, serial#, object_name, row_wait_block#,
row_wait_row#, row_wait_file#
from all_objects, v$session
where row_wait_obj#=object_id and type='USER'
and lockwait is not null ;
Select sl.username, sl.sid, sl.serial#
from v_$lock l1, v$session s1
where exists (select * from v_$lock l2, v$session s2
where l2.sid=s2.sid and l2.id1=l1
and s2.lockwait=l2.kaddr
and request=0
and l1.sid=s1.sid) ;
select count(*) from v$session;
select count(*) from sys.v_$process;
select count(*) from sys.v_$transaction;
ZYP_35.98
--查看哪些包要固定
COLUMN OWNER FORMAT A10
Select owner, name, type,
source_size+code_size+parsed_size+error_size BYPES
from dba_object_size
where type='PACKAGE BODY' ORDER BY 4 DESC ;
--查看一个用户拥有哪些表空间的实体信息:
Select tablespace_name, owner, segment_name,segment_type
from dba_segments
where owner-'SyS'
and segment_type_-'ROLLBACK'
order by tablespace_name, owner, segment_name ;
break on owner on segment_name
COLUMN segment_name FORMAT A15
cOLUMN tablespace_name FORMAT A15
COLUMN file_name FORMAT A20
SELECT A.owner, a.segment_name, b.tablespace_name, b.file_name,
sum(a.bytes) bytes
from dba_extents a, dba_data_files b
where a.file_id-b.file_id group by a.owner, a.segment_name,
b.tablespace_name, b.file_name ;
------看内存缓冲区使用效率的指数是命中率HITS:
Hits=Logical_reads/(logical_reads+physical_reads)
其中：logical_reads=db_block_gets+consistent_reads
select cur.value db, con.value con, phy.value phy,
(cur.value+con.value)/(cur.value+con.value+phy.value)*100 HITS
from v$sysstat cur, v$sysstat con, v$sysstat phy
where CUR.NAME='db block gets' AND
CON.NAME='consistent gets' AND
PHY.NAME='physical reads' ;
--查看有事务在哪几个回退段中：
COLUMN u FORMAT A15
COLUMN s FORMAT A15
COLUMN s FORMAT A80
select osuser o, username u, segment_name s, sa.sql_text
from v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa
where s.taddr=t.addr and t.sidusn=r.segmant_id(+)
and s.sql_address=sa.address(+) ;
--用系统管理员，查看当前数据库有几个用户连接：
select username,sid,serial# from v$session;
--如果要停某个连接用
SQL> alter system kill session 'sid,serial#';
如果这命令不行,找它UNIX的进程数
SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;
说明：21是某个连接的sid数
然后用 kill 命令杀此进程号。
--查找当前正在执行的语句
col processid format 999999999
col sid format 999999
col serial# format 999999
col waitevent format A25
col machine format A10
col osuser format A10
col username format A10
col status format A10
col sqltext format A35
select distinct
t2.spid ProcessID,
t1.sid SID,
t1.serial# Serial#,
t5.event WaitEvent,
t1.machine Machine,
t1.osuser OSUSER,
t1.username Username,
t1.status Status,
t3.sql_text SQLText
from v$session t1,
v$process t2,
v$sql t3,
v$session_wait t5
where t1.paddr = t2.addr and
t1.sql_hash_value = t3.hash_value and
t1.sql_address = t3.address and
t1.sid = t5.sid and
t1.username is not null and
t1.status = 'ACTIVE'
order by t1.sid;
--当前被lock住的对象信息
select distinct
t1.start_time,
t2.sid,
t2.serial#,
t1.ses_addr,
t1.xidusn,
t3.owner,
t3.object_name,
t4.os_user_name,
t4.oracle_username
from v$transaction t1,
v$session t2,
dba_objects t3,
v$locked_object t4
where t1.ses_addr = t2.saddr and
t4.session_id = t2.sid and
t4.object_id = t3.object_id and
t3.object_type = 'TABLE'
order by 1;
--定位TRANSACTION使用的是那个RBS。
COLUMN "Object Name" format A20
SELECT SUBSTR (a.os_user_name, 1, 8) "OS User",
SUBSTR (a.oracle_username, 1, 8) "DB User",
SUBSTR (b.owner, 1, 8) "Schema",
SUBSTR (b.object_name, 1, 20) "Object Name",
SUBSTR (b.object_type, 1, 10) "Type",
SUBSTR (c.segment_name, 1, 5) "RBS",
SUBSTR (d.used_urec, 1, 12) "# of Records"
FROM v$locked_object a,
dba_objects b,
dba_rollback_segs c,
v$transaction d,
v$session e
WHERE a.object_id = b.object_id
AND a.xidusn = c.segment_id
AND a.xidusn = d.xidusn
AND a.xidslot = d.xidslot
AND d.addr = e.taddr;
--生成一个systemdump，dump文件存放于udump目录下。
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10';
注意：如果当前数据库的并发连接较多或事务较多，此命令可能会执行较慢，生成的dump文件较大（可能会到几百兆）,因此建议只有在数据库出现问题或挂起时才执行。如果为RAC环境可以执行下面的命令来dump多个节点的信息。
oradebug -g all dump systemstate 10
Dump结果需要提交给ORACLESUPPORT进行分析，最好通过metalink上的TAR来进行提交。
--awr报告
@?/rdbms/admin/awrrpt
--sql执行计划
--sql涉及的表大小
--涉及的索引的大小
--sql涉及的
--操作系统参数
--操作系统日志
--数据库参数
--show parameter
使用DBMS_SHARED_POOL.KEEP加载对象
exec dbms_shared_pool.keep('DBMS_SYS_SQL');
--keep常用小表
--
大表重组(可选)
--
60G的分区表BF_PAY_FEE_T重组
--
关键索引重建
ALTER INDEX UNITELE.I_HLR_CONTROL rebuild online;
select OWNER,SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024/1024,TABLESPACE_NAME
from dba_segments where segment_name = 'BF_PAY_FEE_OPER_I';
select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS from dba_ind_partitions where index_name = '&t';
首先,Oracle10g数据库运行的状况如何这个问题可以通过下面的查询来获得：
select METRIC_NAME,VALUE
from SYS.V_$SYSMETRIC
where METRIC_NAME IN ('Database CPU Time Ratio','Database Wait Time Ratio')
AND INTSIZE_CSEC = (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC);
METRIC_NAME VALUE
------------------------------ ----------------------------------
Database Wait Time Ratio 31.3499111
Database CPU Time Ratio 68.6500888
select CASE METRIC_NAME
WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'
WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MINVAL/100),2)
WHEN 'Response Time Per Txn' then ROUND((MINVAL/100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MAXVAL/100),2)
WHEN 'Response Time Per Txn' then ROUND((MAXVAL/100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((AVERAGE/100),2)
WHEN 'Response Time Per Txn' then ROUND((AVERAGE/100),2)
ELSE AVERAGE
END AVERAGE
from SYS.V_$SYSMETRIC_SUMMARY
where METRIC_NAME in ('CPU Usage Per Sec',
'CPU Usage Per Txn',
'Database CPU Time Ratio',
'Database Wait Time Ratio',
'Executions Per Sec',
'Executions Per Txn',
'Response Time Per Txn',
'SQL Service Response Time',
'User Transaction Per Sec')
ORDER BY 1;
图文推荐
序言
2020-04-19 查看：2271
第十五章：Oracle 12c介绍
2020-04-19 查看：2208
第十四章：常用Oracle工具
2020-04-19 查看：1934
第十三章：Oracle Golde...
2020-04-19 查看：2199
第十二章：DataGuard
2020-04-19 查看：1796
