Oracle大数据库性能监控语句汇总情况.docx
《Oracle大数据库性能监控语句汇总情况.docx》由会员分享,可在线阅读,更多相关《Oracle大数据库性能监控语句汇总情况.docx(24页珍藏版)》请在冰豆网上搜索。
Oracle大数据库性能监控语句汇总情况
Oracle数据库性能监控语句
写于2016年1月,所有语句经过测试
一、查看临时表空间使用情况
Select
f.tablespace_name
,sum(f.bytes_free+f.bytes_used)
/1024/1024/1024"totalGB"
,sum((f.bytes_free+f.bytes_used)-nvl(p.bytes_used,0))
/1024/1024/1024"FreeGB"
,sum(nvl(p.bytes_used,0))
/1024/1024/1024"UsedGB"
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
f.tablespace_name
二、查看使用临时表空间的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
fromdba_data_files
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
FROMv$session
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,
SUM(a.wait_time+a.time_waited)total_wait_time
FROMv$active_session_historya,dba_objectso,v$sessions
WHEREa.sample_timeBETWEENSYSDATE-30/2880ANDSYSDATE
ANDa.current_obj#=o.object_id
ANDa.session_id=s.SID
GROUPBYo.owner,
o.object_name,
o.object_type,
a.event,
a.session_id,
s.program,
s.machine,
s.osuser
ORDERBYtotal_wait_timeDESC;
十四、查看等待最多的SQL
SELECTa.program,a.session_id,a.user_id,d.username,s.sql_text,
SUM(a.wait_time+a.time_waited)total_wait_time
FROMv$active_session_historya,v$sqlareas,dba_usersd
WHEREa.sample_timeBETWEENSYSDATE-30/2880ANDSYSDATE
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#,/*会话的序号*/
SQ.OPTIMIZER_COSTASCOST_,/*COST值*/
SE.LAST_CALL_ETCONTINUE_TIME,/*执行时间可能是单个sql也可能是整个功能*/
SE.PREV_EXEC_START,/*SQLexecutionstartofthelastexecutedSQLstatement*/
SE.EVENT,/*等待事件*/
SE.LOCKWAIT,/*是否等待LOCK(SE,P)*/
SE.MACHINE,/*客户端的机器名。
(WORKGROUP\PC-5)*/
SQ.SQL_ID,/*SQL_ID*/
SE.USERNAME,/*创建该会话的用户名*/
SE.LOGON_TIME/*登陆时间*/
--SE.TERMINAL,/*客户端运行的终端名。
(PC-5)*/
--,SQ.HASH_VALUE,/*一个SQL产生的HASH值*/
--SQ.PLAN_HASH_VALUE/*执行SQL的HASH值(解析后HASH值),与SQL_ADDRESS关联查询其他SQL相关视图后即可查询会话当前正在执行的SQL语句*/
FROMGV$SESSIONSE,/*会话信息。
每一个连接到ORACLE数据库的会话都能在该视图中对应一条记录,根据该视图中的信息可以查询该会话使用的用户,正在执行或者刚刚执行的SQL语句*/
/*[GV$SQLAREA多节点]*/
GV$SQLAREASQ/*跟踪所有SHAREDPOOL中的共享CURSOR信息,包括执行次数,逻辑读,物理读等*/
WHERESE.SQL_HASH_VALUE=SQ.HASH_VALUE
ANDSE.STATUS='ACTIVE'
ANDSE.SQL_ID=SQ.SQL_ID
ANDSQ.INST_ID=SE.INST_ID
ANDSE.USERNAMEisnotnull;
--过滤条件
--ANDSE.USERNAME='FWSB'--用户名
--ANDSQ.COMMAND_TYPEIN(2,3,5,6,189)
--ANDSE.SID!
=USERENV('SID')/*rac集群环境误用*/
--ANDMACHINE!
='WORKGROUP\MHQ-PC';
十八、每天执行慢的SQL
SELECTS.SQL_TEXT,
S.SQL_FULLTEXT,
S.SQL_ID,
ROUND(ELAPSED_TIME/1000000/(CASE
WHEN(EXECUTIONS=0ORNVL(EXECUTIONS,1)=1)THEN
1
ELSE
EXECUTIONS
END),
2)"执行时间'S'",
S.EXECUTIONS"执行次数",
S.OPTIMIZER_COST"COST",
S.SORTS,
S.MODULE,--连接模式(JDBCTHINCLIENT:
程序)
--S.LOCKED_TOTAL,
S.PHYSICAL_READ_BYTES"物理读",
--S.PHYSICAL_READ_REQUESTS"物理读请求",
S.PHYSICAL_WRITE_REQUESTS"物理写",
--S.PHYSICAL_WRITE_BYTES"物理写请求",
S.ROWS_PROCESSED"返回行数",
S.DISK_READS"磁盘读",
S.DIRECT_WRITES"直接路径写",
S.PARSING_SCHEMA_NAME,
S.LAST_ACTIVE_TIME
FROMGV$SQLAREAS
WHEREROUND(ELAPSED_TIME/1000000/(CASE
WHEN(EXECUTIONS=0ORNVL(EXECUTIONS,1)=1)THEN
1
ELSE
EXECUTIONS
END),
2)>5--1000000微秒=1S
--ANDS.PARSING_SCHEMA_NAME=USER
ANDTO_CHAR(S.LAST_LOAD_TIME,'YYYY-MM-DD')=
TO_CHAR(SYSDATE,'YYYY-MM-DD')
ANDS.COMMAND_TYPEIN(2,3,5,6,189)/*值对应类型2:
INSERT、3:
SELECT、6:
UPDATE、7:
DELETE、189:
MERGE查询V$SQLCOMMAND*/
ANDMODULE='JDBCThinClient'
ORDERBY"执行时间'S'"DESC;
十九、查看非绑定变量的SQL
SELECTV.SQL_ID,
V.SQL_FULLTEXT,
V.PARSING_SCHEMA_NAME,
FM.EXECUTIONS_COUNT,
FM.ELAPSED_TIME
FROM(SELECTL.FORCE_MATCHING_SIGNATUREMATHCES,
MAX(L.SQL_ID||L.CHILD_NUMBER)MAX_SQL_CHILD,
DENSE_RANK()OVER(ORDERBYCOUNT(*)DESC)RANKING,
ROUND(SUM(ROUND(ELAPSED_TIME/1000000/(CASE
WHEN(EXECUTIONS=0ORNVL(EXECUTIONS,1)=1)THEN
1
ELSE
EXECUTIONS
END),
5)))ELAPSED_TIME,
SUM(L.EXECUTIONS)EXECUTIONS_COUNT
FROMV$SQLL
WHERETO_CHAR(TO_DATE(LAST_LOAD_TIME,'YYYY-MM-DDHH24:
MI:
SS'),
'YYYY-MM-DD')=TO_CHAR(SYSDATE-1,'YYYY-MM-DD')--当天LAST_LOAD_TIME(VARCHAR类型,LOADEDINTOTHELIBRARYCACHETIME)
ANDL.MODULELIKE'%JDBC%'--程序连接
ANDL.FORCE_MATCHING_SIGNATURE<>0
ANDL.PARSING_SCHEMA_NAME=UPPER('&USERNAME')--用户
ANDL.COMMAND_TYPEIN(2,3,5,6,189) --命令类型2:
INSERT、3:
SELECT、6:
UPDATE、7:
DELETE、189:
MERGE查询V$SQLCOMMAND
GROUPBYL.FORCE_MATCHING_SIGNATURE
HAVINGCOUNT(*)>5)FM,
V$SQLV
WHEREFM.MAX_SQL_CHILD=(V.SQL_ID||V.CHILD_NUMBER)
ANDEXECUTIONS_COUNT>=50--执行次数超过50次先筛选改写,后续慢慢在围小
ORDERBYFM.RANKING;
--V$SQL_BIND_CAPTURE--记录包含变量得表..包括ROWNUM<:
1变量
二十、查看LOG切换频率
selectb.SEQUENCE#,
b.FIRST_TIME,
a.SEQUENCE#,
a.FIRST_TIME,
round(((a.FIRST_TIME-b.FIRST_TIME)*24)*60,2)
fromv$log_historya,v$log_historyb
wherea.SEQUENCE#=b.SEQUENCE#+1
andb.THREAD#=1
orderbya.SEQUENCE#desc;
二十一、查看SQL执行进度
--显示运行时间超过6秒的数据库操作的状态
SELECTA.SID,
A.SERIAL#,
OPNAME,
TARGET,--对象
TO_CHAR(START_TIME,'YYYY-MM-DDHH24:
MI:
SS')START_TIME,--开始时间
(SOFAR/TOTALWORK)*100PROGRESS,--进度比
TIME_REMAINING,--估算剩余时间
ELAPSED_SECONDS,--运行时间‘S’
A.SQL_ID
FROMV$SESSION_LONGOPSA
WHERESID=;
***其中SID和SERIAL#是与V$SESSION中的匹配的,
***OPNAME:
指长时间执行的操作名.如:
TABLESCAN
***TARGET:
被操作的OBJECT_NAME.如:
TABLEA
***TARGET_DESC:
描述TARGET的容
***SOFAR:
这个是需要着重去关注的,表示已要完成的工作数,如扫描了多少个块。
***TOTALWORK:
指目标对象一共有多少数量(预计)。
如块的数量。
***START_TIME:
进程的开始时间
***LAST_UPDATE_TIM:
最后一次调用SET_SESSION_LONGOPS的时间
***TIME_REMAINING:
估计还需要多少时间完成,单位为秒
***ELAPSED_SECONDS:
指从开始操作时间到最后更新时间
***MESSAGE:
对于操作的完整描述,包括进度和操作容。
***USERNAME:
与V$SESSION中的一样。
***SQL_ADDRESS:
关联V$SQL
***SQL_HASH_VALUE:
关联V$SQL
***QCSID:
主要是并行查询一起使用。
二十二、查询外键字段在主键表中没有索引的
SELECTC.*,
C1.r_constraint_name,
c2.table_name,
T.NUM_ROWS,
'createindexidx_'||c.table_name||'_'||column_name||'on'||
c.table_name||'('||column_name||');'
FROMUSER_CONS_COLUMNSC
JOINUSER_CONSTRAINTSC1
ONC1.CONSTRAINT_NAME=C.CONSTRAINT_NAME
ANDC1.CONSTRAINT_TYPE='R'
AND(C.TABLE_NAME,C.COLUMN_NAME)NOTIN
(SELECTTABLE_NAME,COLUMN_NAMEFROMUSER_IND_COLUMNSI)
JOINUSER_TABLEST
ONT.TABLE_NAME=C.TABLE_NAME
joinUSER_CONSTRAINTSc2
onc1.r_constraint_name=c2.constraint_name;
博客:
为什么子表