--当前执行sql语句
SELECT a.SID ,
a.SERIAL# , a.USERNAME , b.PARSE_CALLS , b.PARSING_SCHEMA_NAME , b.CPU_TIME / 1000000 , b.ELAPSED_TIME / 1000000 , b.DISK_READS , b.DIRECT_WRITES , b.BUFFER_GETS , a.event , b.sql_text , b.SQL_FULLTEXTFROM v$session a INNER JOIN v$sqlarea b ON a.SQL_HASH_VALUE = b.hash_value AND b.PARSING_SCHEMA_NAME = UPPER('smsdb')--物理读最高sql语句
SELECT a.USERNAME ,
a.USER_ID , b.PARSE_CALLS , b.PARSING_SCHEMA_NAME , b.CPU_TIME / 1000000 , b.ELAPSED_TIME / 1000000 , b.DISK_READS , b.DIRECT_WRITES , b.BUFFER_GETS , b.sql_text , b.SQL_FULLTEXTFROM dba_users a INNER JOIN v$sqlarea b ON a.USER_ID = b.PARSING_USER_ID AND b.PARSING_SCHEMA_NAME = UPPER('smsdb') AND disk_reads > 1000000--查询前10名执行最多次数SQL语句
SELECT sql_text 'SQL语句' ,
executions '执行次数'FROM ( SELECT sql_text , executions , RANK() OVER ( ORDER BY executions DESC ) exec_rank FROM v$sqlarea )WHERE exec_rank <= 10;--查询前10名占用CPU最高的SQL语句
select sql_text 'SQL语句',
c_t 'SQL执行时间(秒)',executions '执行次数',cs '每次执行时间(秒)' from (select sql_text,
cpu_time /1000000 c_t,executions,ceil(executions/(cpu_time/1000000))cs,
rank() over(order by cpu_time desc) top_time
from v$sqlarea) where top_time <= 10
--查询前10名执行时间最长SQL语句
SELECT sql_text 'SQL语句' ,
c_t '处理时间(秒)' , executions '执行次数' , cs '每次执行时间(秒)'FROM ( SELECT sql_text , ELAPSED_TIME / 1000000 c_t , executions , ceil(executions / ( ELAPSED_TIME / 1000000 )) cs , RANK() OVER ( ORDER BY ELAPSED_TIME DESC ) top_time FROM v$sqlarea )WHERE top_time <= 10--查询前10名最耗资源SQL语句
SELECT sql_text 'SQL语句' ,
DISK_READS '物理读次数' , cs '每次执行时间(秒)'FROM ( SELECT sql_text , ELAPSED_TIME / 1000000 c_t , executions , ceil(executions / ( ELAPSED_TIME / 1000000 )) cs , DISK_READS , RANK() OVER ( ORDER BY DISK_READS DESC ) top_disk FROM v$sqlarea )WHERE top_disk <= 10--查询前10名最耗内存SQL语句
select sql_text 'SQL语句',
BUFFER_GETS '内存读次数',cs '每次执行时间(秒)'
from (select sql_text,
ELAPSED_TIME / 1000000 c_t,executions,ceil(executions/(ELAPSED_TIME/1000000))cs,BUFFER_GETS,
rank() over(order by BUFFER_GETS desc) top_mem
from v$sqlarea) where top_mem <= 10
--查看锁表语句
Select
c.sid,
c.serial#,
d.name,
b.object_name,
c.username,
c.program,
c.osuser
from gv$Locked_object a, All_objects b, gv$session c, audit_actions d
where a.object_id = b.object_id
and a.inst_id = c.inst_id(+)
and a.session_id = c.sid(+)
and c.command = d.action;