–当前执行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_FULLTEXT FROM 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_FULLTEXT FROM 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;
评论前必须登录!
注册