Oracle数据库性能监控语句汇总Word下载.docx
《Oracle数据库性能监控语句汇总Word下载.docx》由会员分享,可在线阅读,更多相关《Oracle数据库性能监控语句汇总Word下载.docx(26页珍藏版)》请在冰豆网上搜索。
fromsys.v_$temp_space_headerf,dba_temp_filesd,sys.v_$temp_extent_poolp
wheref.tablespace_name(+)=d.tablespace_name
andf.file_id(+)=d.file_id
andp.file_id(+)=d.file_id
groupby
二、查看使用临时表空间的SQL
Selectse.username,
se.sid,
su.extents,
su.blocks*to_number(rtrim(p.value))asSpace,
tablespace,
segtype,
sql_text
fromv$sort_usagesu,v$parameterp,v$sessionse,v$sqls
wherep.name='
db_block_size'
andsu.session_addr=se.saddr
ands.hash_value=su.sqlhash
ands.address=su.sqladdr
orderbyse.username,se.sid
三、收缩临时表空间
altertablespacetempshrinkspace;
altertablespacetempshrinktempfile'
'
四、重建索引
alterindexPK_CROSSRELATIONrebuild;
五、查看表空间使用情况
SELECTUPPER(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(SELECTTABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,
ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTES
FROMSYS.DBA_FREE_SPACE
GROUPBYTABLESPACE_NAME)F,
(SELECTDD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MB
FROMSYS.DBA_DATA_FILESDD
GROUPBYDD.TABLESPACE_NAME)D
WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME
ORDERBY1;
六、查询表空间的总容量
selecttablespace_name,sum(bytes)/1024/1024asMB
fromdba_data_files
groupbytablespace_name;
七、查询表空间使用率
selecttotal.tablespace_name,
round(total.MB,2)asTotal_MB,考试大论坛
round(total.MB-free.MB,2)asUsed_MB,
round((1-free.MB/total.MB)*100,2)||'
asUsed_Pct
from(selecttablespace_name,sum(bytes)/1024/1024asMB
fromdba_free_space
groupbytablespace_name)free,
(selecttablespace_name,sum(bytes)/1024/1024asMB
groupbytablespace_name)total
wherefree.tablespace_name=total.tablespace_name;
八、查找当前表级锁
selectsess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
fromv$locked_objectlo,
dba_objectsao,
v$sessionsess
whereao.object_id=lo.object_idandlo.session_id=sess.sid;
杀掉锁表进程:
altersystemkillsession'
436,35123'
;
九、监控当前数据库谁在运行什么SQL语句
selectosuser,username,sql_text
fromv$sessiona,v$sqltextb
wherea.sql_address=b.addressorderbyaddress,piece;
十、找使用CPU多的用户session
selecta.sid,spid,status,substr(a.program,1,40)prog,a.terminal,osuser,value/60/100value
fromv$sessiona,v$processb,v$sesstatc
wherec.statistic#=12and
c.sid=a.sidand
a.paddr=b.addr
orderbyvaluedesc;
十一、查看死锁信息
SELECT(SELECTusername
FROMv$session
WHERESID=a.SID)blocker,a.SID,'
isblocking'
(SELECTusername
WHERESID=b.SID)blockee,b.SID
FROMv$locka,v$lockb
WHEREa.BLOCK=1ANDb.request>
0ANDa.id1=b.id1ANDa.id2=b.id2;
十二、具有最高等待的对象
SELECTo.OWNER,o.object_name,o.object_type,a.event,
SUM(a.wait_time+a.time_waited)total_wait_time
FROMv$active_session_historya,dba_objectso
WHEREa.sample_timeBETWEENSYSDATE-30/2880ANDSYSDATE
ANDa.current_obj#=o.object_id
GROUPBYo.OWNER,o.object_name,o.object_type,a.event
ORDERBYtotal_wait_timeDESC;
十三、查看具有最高等待的对象
SELECTa.session_id,s.osuser,s.machine,s.program,o.owner,o.object_name,
o.object_type,a.event,
FROMv$active_session_historya,dba_objectso,v$sessions
ANDa.session_id=s.SID
GROUPBYo.owner,
o.object_name,
o.object_type,
a.event,
a.session_id,
s.program,
s.machine,
s.osuser
十四、查看等待最多的SQL
SELECTa.program,a.session_id,a.user_id,d.username,s.sql_text,
FROMv$active_session_historya,v$sqlareas,dba_usersd
ANDa.sql_id=s.sql_id
ANDa.user_id=d.user_id
GROUPBYa.program,a.session_id,a.user_id,s.sql_text,d.username;
十五、显示正在等待锁的所有会话
SELECT*FROMDBA_WAITERS;
十七、查数据库中正在执行的SQL
SELECTSE.INST_ID,--实例
SQ.SQL_TEXT,/*SQL文本*/
SQ.SQL_FULLTEXT,/*SQL全部文本*/
SE.SID,/*会话的唯一标识,通常要对某个会话进行分析前,首先就需要获得该会话的SID。
*/
--SE.SERIAL#,/*会