当前位置:网站首页>DBA常用SQL语句(2)— SGA和PGA
DBA常用SQL语句(2)— SGA和PGA
2022-04-23 09:52:00 【Grainger】
检查 sga
show sga
select * from v$sga;
查看 buffer cache 设置建议
select
size_for_estimate,
estd_physical_read_factor,
to_char(estd_physical_reads,99999999999999999999999) as"estd_physical_reads"
from v$db_cache_advice where name = 'DEFAULT';
COL pool FORMAT a10;
SELECT
(
SELECT ROUND(value/1024/1024,0)
FROM v$parameter
WHERE name = 'db_cache_size'
) "Current Cache(Mb)",
name "Pool",
size_for_estimate "Projected Cache(Mb)",
ROUND(100-estd_physical_read_factor,0) "Cache Hit Ratio%"
FROM v$db_cache_advice
WHERE block_size = (SELECT value FROM v$parameter
WHERE name = 'db_block_size')
ORDER BY 3;
查看 cache 池
show parameter cache
查看 buffer cache 中 defalut pool 命中率
select name,1-(physical_reads)/(consistent_gets+db_block_gets)
from v$buffer_pool_statistics;
检查 shared pool
show parameter shared
检查 shared pool 中 library cache
select namespace,pinhitratio from v$librarycache;
检查整体命中率 (library cache)
select sum(pinhits)/sum(pins) from v$librarycache;
select sum(pins) "hits",
sum(reloads) "misses",
sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio"
from v$librarycache;
检查 shared pool free space
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';
每个子 shared pool 由单独的 shared pool latch 保护,查看他们的命中率 shared pool latch,用于 shared pool 空间回收分配使用的 latch
col name format a15
select
addr,name,gets,misses,1-misses/gets
from v$latch_children
where name='shared pool';
使用 v$shared_pool_advice 计算不同 shared pool 大小情况下,响应时间, S 单位
SELECT
'Shared Pool' component,
shared_pool_size_for_estimate estd_sp_size,
estd_lc_time_saved_factor parse_time_factor,
CASE
WHEN
current_parse_time_elapsed_s + adjustment_s < 0 THEN 0
ELSE
current_parse_time_elapsed_s + adjustment_s
END response_time
FROM (
SELECT
shared_pool_size_for_estimate,
shared_pool_size_factor,
estd_lc_time_saved_factor,
a.estd_lc_time_saved,
e.VALUE / 100 current_parse_time_elapsed_s,
c.estd_lc_time_saved - a.estd_lc_time_saved adjustment_s FROM v$shared_pool_advice
a,
(SELECT * FROM v$sysstat WHERE NAME = 'parse time elapsed') e,
(SELECT estd_lc_time_saved FROM v$shared_pool_advice
WHERE shared_pool_size_factor = 1) c)
/
查看 shared pool 中 各种类型的 chunk 的大小数量
SELECT
KSMCHCLS CLASS,
COUNT(KSMCHCLS) NUM,
SUM(KSMCHSIZ) SIZ,
To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIzE"
FROM X$KSMSP GROUP BY KSMCHCLS;
查看是否有库缓冲有关的等待事件
select
sid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state
from v$session_wait
where event like 'library%';
查询 sga 中各个 pool 情况
COL name FORMAT a32;
SELECT
pool, name, bytes
FROM v$sgastat
WHERE pool IS NULL
OR pool != 'shared pool'
OR (pool = 'shared pool'
AND (name IN('dictionary cache','enqueue','library
cache','parameters',
'processes','sessions','free memory')))
ORDER BY pool DESC NULLS FIRST, name;
SELECT * FROM V$SGAINFO;
查看使用 shard_pool 保留池情况
SELECT request_misses, request_failures, free_space
FROM v$shared_pool_reserved;
Oracle 专门从共享池内置出一块区域来来分配内存保持这些大块。这个保留共享池的默认大小是共享池的 5%(_shared_pool_reserved_pct 5 控制 ) oracle 建设置为 10% 。大小通过参数 SHARED_POOL_RESERVED_SIZE 改。它是从共享池中分配,不是直接从 SGA 中分配的,它是共享池的保留部分,专门用于存储大块段#shared pool 中内存大于_SHARED_POOL_RESERVED_MIN_ALLOC 将放入 shared pool 保留池 , 保留池维护一个单独的 freelist,lru ,并且不会在 lru 列表存recreatable 类型 chunks ,普通 shared pool 的释放与 shared pool 保留池无关。
关于设置 SHARED_POOL_RESERVED_SIZE
- 如果系统出现 ora-04031, 发现请求内存都是大于_SHARED_POOL_RESERVED_MIN_ALLOC (default 10GR2 4400) , 且v$shared_pool_reserved 中有大量 REQUEST_MISSES( 并且可以看下LAST_MISS_SIZE )表示 SHARED_POOL_RESERVED_SIZE 太小了需要大的内存的请求失败 , 那么需要加大 SHARED_POOL_RESERVED_SIZE
- 如果 ora-04031 请求内存出现在 4100-4400 并造成 shared pool lru 合并 , 老化换出内存 , 可以调小_SHARED_POOL_RESERVED_MIN_ALLOC 让此部分内存进入 shared reserved pool, 相应的加大 SHARED_POOL_RESERVED_SIZE
- 从v$shared_pool_reserved 来判断 , 如果 REQUEST_FAILURES>0(出现过ora-04031) 且LAST_FAILURE_SIZE( 最后请求内存大小)>_SHARED_POOL_RESERVED_MIN_ALLOC 表示 shared reserved pool 缺少连续内存 , 可以加大 SHARED_POOL_RESERVED_SIZE, 减少_SHARED_POOL_RESERVED_MIN_ALLOC 少放对象 , 并相对加大shared_pool_size。要是反过来 REQUEST_FAILURES>0(出现过 ora-04031) 且LAST_FAILURE_SIZE(最后请求内存大小)<_SHARED_POOL_RESERVED_MIN_ALLOC,表示 在 shared pool 中缺少连续内存 , 可以加减少_SHARED_POOL_RESERVED_MIN_ALLOC 多放入一些对象 , 减少 sharedpool 压力 , 适当加大shared_pool_size,SHARED_POOL_RESERVED_SIZE
查询还保留在 library cache 中,解析次数和执行次数最多的 sql( 解析 * 执行 )
COL sql_text FORMAT A38;
SELECT * FROM(
SELECT parse_calls*executions "Product", parse_calls
"Parses"
,executions "Execs", sql_text FROM v$sqlarea ORDER BY 1 DESC)
WHERE ROWNUM <= 10;
查看 pga
show parameters area_size
SELECT * FROM v$pgastat;
查看 pga 建议
SELECT (SELECT ROUND(value/1024/1024,0) FROM v$parameter
WHERE name = 'pga_aggregate_target') "Current Mb"
, ROUND(pga_target_for_estimate/1024/1024,0) "Projected Mb"
, ROUND(estd_pga_cache_hit_percentage) "%"
FROM v$pga_target_advice
ORDER BY 2;
版权声明
本文为[Grainger]所创,转载请带上原文链接,感谢
https://cloud.tencent.com/developer/article/1986058
边栏推荐
- 云身份过于宽松,为攻击者打开了大门
- 个人主页软件Fenrus
- The central control learning infrared remote control module supports network and serial port control
- 论文阅读《Integrity Monitoring Techniques for Vision Navigation Systems》
- 实践六 Windows操作系统安全攻防
- Pyqt5与通信
- 1D / 1D dynamic programming learning summary
- MacOS下使用CLion编译调试MySQL8.x
- SAP excel has completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
- failureForwardUrl与failureUrl
猜你喜欢
ABAP 7.4 SQL Window Expression
ARM调试(1):两种在keil中实现printf重定向到串口的方法
Where is int a = 1 stored
Alibaba cloud architects interpret the four mainstream game architectures
中控学习型红外遥控模块支持网络和串口控制
High paid programmer & interview question series 91 limit 20000 loading is very slow. How to solve it? How to locate slow SQL?
How to use SQL statement union to get another column of another table when the content of a column in a table is empty
构建元宇宙时代敏捷制造的九种能力
MapReduce核心和基础Demo
NEC红外遥控编码说明
随机推荐
一文读懂PlatoFarm新经济模型以及生态进展
杰理之系统事件有哪些【篇】
Simple understanding of arguments in JS
SAP salv14 background output salv data can directly save files and send emails (with sorting, hyperlink and filtering format)
JS node operation, why learn node operation
Compile and debug mysql8 with clion under MacOS x
SAP pi / PO function operation status monitoring and inspection
杰理之有时候定位到对应地址的函数不准确怎么办?【篇】
Setnx command execution failed due to full redis memory
自定义登录失败处理
kernel-pwn学习(4)--Double Fetch&&0CTF2018-baby
Easy to understand subset DP
PHP notes (I): development environment configuration
[COCI] Vje š TICA (subset DP)
AI上推荐 之 MMOE(多任务yyds)
Three ways to create objects in JS
Function realization of printing page
MacOS下使用CLion编译调试MySQL8.x
Odoo 服务器搭建备忘
Example of data object mask used by SAP translate