oracle消耗资源,oracle中资源消耗查看

查看会话内存占用情况

SELECT server "连接类型",

s.username,

OSUSEr,

NAME,

VALUE/1024/1024 "占用内存MB",

s.SID "会话ID",

s.serial#,

spid "操作系统进程ID",

p.PGA_USED_MEM,

p.PGA_ALLOC_MEM,

p.PGA_FREEABLE_MEM,

p.PGA_MAX_MEM

FROM v$session s, v$sesstat st, v$statname sn, v$process p

WHERE st.SID = s.SID

AND st.statistic# = sn.statistic#

AND sn.NAME LIKE 'session pga memory'

AND p.addr = s.paddr

ORDER BY VALUE DESC

如果想杀死指定会话alter system kill session 'sid,serial#';,内存没有释放执行如下步骤；

select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status='KILLED' ;

查看killed状态的会话，

windows下：orakill.exe orcl spid，其中orcl是数据库的sid，spid是上面查询出来的

linux下：kill -9 spid；

SQL消耗资源情况查询：

从V$SQLAREA中查询最占用资源的查询

select b.username 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;

用buffer_gets列来替换disk_reads列可以得到占用最多内存的sql语句的相关信息。

V$SQL是内存共享SQL区域中已经解析的SQL语句。

列出使用频率最高的5个查询

select sql_text,executions

from (select sql_text,executions,

rank() over

(order by executions desc) exec_rank

from v$sql)

where exec_rank <=5;

消耗磁盘读取最多的sql top5

select disk_reads,sql_text

from (select sql_text,disk_reads,

dense_rank() over

(order by disk_reads desc) disk_reads_rank

from v$sql)

where disk_reads_rank <=5;

找出需要大量缓冲读取(逻辑读)操作的查询

select buffer_gets,sql_text

from (select sql_text,buffer_gets,

dense_rank() over

(order by buffer_gets desc) buffer_gets_rank

from v$sql)

where buffer_gets_rank<=5;

时间占用多的：

SELECT S.SQL_TEXT,

S.SQL_FULLTEXT,

S.SQL_ID,

ROUND(ELAPSED_TIME / 1000000 / (CASE

WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1) = 1) THEN

1

ELSE

EXECUTIONS

END),

2) "执行时间'S'",

S.EXECUTIONS "执行次数",

S.OPTIMIZER_COST "COST",

S.SORTS,

S.MODULE, --连接模式(JDBC THIN CLIENT：程序)

-- S.LOCKED_TOTAL,

--S.PHYSICAL_READ_BYTES "物理读",

-- S.PHYSICAL_READ_REQUESTS "物理读请求",

--S.PHYSICAL_WRITE_REQUESTS "物理写",

-- S.PHYSICAL_WRITE_BYTES "物理写请求",

S.ROWS_PROCESSED "返回行数",

S.DISK_READS "磁盘读",

S.DIRECT_WRITES "直接路径写",

S.PARSING_SCHEMA_NAME,

S.LAST_ACTIVE_TIME

FROM GV$SQLAREA S

WHERE ROUND(ELAPSED_TIME / 1000000 / (CASE

WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1) = 1) THEN

1

ELSE

EXECUTIONS

END),

2) > 5 --100 0000微秒=1S

AND S.PARSING_SCHEMA_NAME = USER

AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD') =

TO_CHAR(SYSDATE, 'YYYY-MM-DD')

AND S.COMMAND_TYPE IN (2, 3, 5, 6, 189)

ORDER BY "执行时间'S'" DESC;