linux查看mysql内存_查看数据库最占资源或内存的查询SQL

1.使用下面查询修正数据库中最占资源的查询

select b.username, a.DISK_READS reads, a.EXECUTIONS exec, a.disk_reads /decode(a.executions,0,1,a.executions) rds_exec_ratio, a.sql_text Statement

from v$sqlarea a,dba_users b where a.PARSING_USER_ID = b.user_id and a.DISK_READS > 100000 order by a.DISK_READS desc;

select * from

(select sql_text,address,

rank() over(order by buffer_gets desc) as rank_bufgets,

to_char(100*ratio_to_report(buffer_gets) over(), '999.99') pct_bufgets

from v$sql )

where rank_bufgets < 11;

2.使用下面查询修正数据库中最占内存的查询

select b.username, a.buffer_gets buffergets, a.EXECUTIONS exec, a.buffer_gets /decode(a.executions,0,1,a.executions) rds_exec_ratio, a.sql_text Statement

from v$sqlarea a,dba_users b where a.PARSING_USER_ID = b.user_id and a.buffer_gets > 100000 order by a.buffer_gets desc;