mysql查看占用_Mysql占用资源查询

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