oracle查询语句内存占用,查询最占资源、CPU、内存、和执行最长的SQL语句

oracle查询语句内存占用,查询最占资源、CPU、内存、和执行最长的SQL语句

阿躿 2021-04-03 20:52:58  289  收藏 2

文章标签： oracle查询语句内存占用

 

--当前执行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.