如果想查看当前每个sql占用内存情况，可以按照以下命令逐步查询

1、查看SGA各组件当前内存分配情况

set line 300
SELECT component, current_size/1024/1024 "Size(MB)", 
       ROUND(current_size/(SELECT SUM(current_size) FROM v$sga_dynamic_components)*100,2) "Percentage(%)"
FROM v$sga_dynamic_components
WHERE current_size > 0
ORDER BY current_size DESC;

2、查看share pool 分配情况

SELECT pool, name, bytes/1024/1024 "Size(MB)",
       ROUND(bytes/(SELECT SUM(bytes) FROM v$sgastat WHERE pool = 'shared pool')*100,2) "Percentage(%)"
FROM v$sgastat
WHERE pool = 'shared pool' AND bytes > 1024*1024  -- 只显示大于1MB的条目
ORDER BY bytes DESC;

3、查看共享池中占用内存最多的SQL语句

set line 300
SELECT sql_id, executions, VERSION_COUNT,
       sharable_mem/1024/1024 "Sharable Mem(MB)",
       persistent_mem/1024/1024 "Persistent Mem(MB)",
       ROUND(sharable_mem/(SELECT SUM(sharable_mem) FROM v$sqlarea)*100,2) "Percentage(%)",
       SUBSTR(sql_text,1,100) "SQL Text"
FROM v$sqlarea
where rownum<20
ORDER BY sharable_mem DESC;

如果VERSION_COUNT指很大，说明sql很可能没有使用绑定变量，导致VERSION_COUNT子游标数量很多，会占用大量share pool，并进行迷你硬解析

4、如果altert提示share pool异常增长，查看看告警当时share pool的增长趋势数据

SELECT * FROM dba_hist_memory_resize_ops 
WHERE component = 'shared pool'
AND start_time <= TO_DATE('2025-05-16 03:00:46', 'YYYY-MM-DD HH24:MI:SS')
AND end_time >= TO_DATE('2025-05-16 03:55:46', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY start_time DESC;

dba_hist_memory_resize_ops 中的数据也不是无限期保存，默认保存7天，过期就被自动清理了。

5、查看占用内存最多的SQL_ID的子游标数量以及每个子游标大小

SELECT 
    sql_id, 
    child_number, 
    sharable_mem, 
    persistent_mem, 
    runtime_mem,
    sharable_mem + persistent_mem + runtime_mem AS total_mem
FROM 
    v$sql 
WHERE 
    sql_id = 'sql_id名称'
ORDER BY 
    child_number;

v$sql中每个子游标所占内存之和应该比v$sqlarea中sharable_mem要小的多，因为oracle会复用相同内容的内存空间，v$sqlarea中sharable_mem值是该sql_id所占内存的最小值，不能比这个值再小了。

6、查看库缓存(Library Cache)内存使用详情

SELECT namespace, COUNT(*) "Objects",
       SUM(sharable_mem)/1024/1024 "Sharable Mem(MB)",
       ROUND(SUM(sharable_mem)/(SELECT SUM(sharable_mem) FROM v$db_object_cache)*100,2) "Percentage(%)"
FROM v$db_object_cache
GROUP BY namespace
ORDER BY SUM(sharable_mem) DESC;

7、查看SGA建议大小(需要AWR数据支持)

SELECT * FROM v$sga_target_advice
ORDER BY sga_size;

8、查看SGA总体分配和使用情况

SELECT name, value/1024/1024 "Size(MB)",
       ROUND(value/(SELECT SUM(value) FROM v$sga WHERE name != 'Fixed SGA Size')*100,2) "Percentage(%)"
FROM v$sga
WHERE name != 'Fixed SGA Size'
ORDER BY value DESC;

9、sga,pga利用率

select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from 
(select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
(select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)
union
select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
(select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual);