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;