Question

如何查询当前执行过后最消耗内存的sql语句是那句？

因为服务器内存大概8G左右，总是不够用，但链接数据库的网站程序又很多，不知道是那个写的SQL不够好占用了太多的内存，如何查询当前执行过后最消耗内存的sql语句是那句？

或是查到那个数据库的那个表查询使用量最占内存？

求高人帮助，谢谢了！

Answer 1

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